[GitHub/WoltLab/woltlab.github.io.git] / 5.3 / package_pip_sql.html

<!DOCTYPE html>
<head>
    <meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="keywords" content=" ">
<title>SQL Package Installation Plugin | WoltLab Suite 5.3 Documentation</title>

<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/syntax.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:400,300,600">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/font-awesome.min.css">
<!--<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">-->
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/modern-business.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/lavish-bootstrap.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/customstyles.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/theme-blue.css?v=3">

<script src="https://docs.woltlab.com/5.3/js/jquery.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/jquery.cookie.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/jquery.navgoco.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/bootstrap.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/anchor.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/toc.js"></script>
<script src="https://docs.woltlab.com/5.3/js/customscripts.js"></script>

<link rel="shortcut icon" href="https://docs.woltlab.com/5.3/images/favicon.ico">

<link rel="alternate" type="application/rss+xml" title="woltlab.github.io" href="https://docs.woltlab.com/5.3feed.xml">

    <script>
        $(document).ready(function() {
            // Initialize navgoco with default options
            $("#mysidebar").navgoco({
                caretHtml: '',
                accordion: true,
                openClass: 'active', // open
                save: false, // leave false or nav highlighting doesn't work right
                cookie: {
                    name: 'navgoco',
                    expires: false,
                    path: '/'
                },
                slide: {
                    duration: 400,
                    easing: 'swing'
                }
            });

            $("#collapseAll").click(function(e) {
                e.preventDefault();
                $("#mysidebar").navgoco('toggle', false);
            });

            $("#expandAll").click(function(e) {
                e.preventDefault();
                $("#mysidebar").navgoco('toggle', true);
            });

        });

    </script>
    <script>
        $(function () {
            $('[data-toggle="tooltip"]').tooltip()
        })
    </script>
    

</head>
<body>
  <!-- Navigation -->
<nav class="navbar navbar-inverse navbar-fixed-top">
    <div class="container topnavlinks">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
                <span class="sr-only">Toggle navigation</span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
            </button>
            <a class="fa fa-home fa-lg navbar-brand" href="index.html">&nbsp;<span class="projectTitle"> WoltLab Suite 5.3 Documentation</span></a>
        </div>
        <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
            <ul class="nav navbar-nav navbar-right">
                <!-- entries without drop-downs appear here -->
                
                
                <li><a href="https://www.woltlab.com" target="_blank">woltlab.com</a></li>
                
                
                <li><a href="https://github.com/WoltLab/WCF/" target="_blank">Code on github.com</a></li>
                
                
                <!-- entries with drop-downs appear here -->
                <!-- conditional logic to control which topnav appears for the audience defined in the configuration file.-->
                
                
                <!--comment out this block if you want to hide search-->
                <li>
                    <!--start search-->
                    <div id="search-demo-container">
                        <input type="text" id="search-input" placeholder="search...">
                        <ul id="results-container"></ul>
                    </div>
                    <script src="https://docs.woltlab.com/5.3/js/jekyll-search.js" type="text/javascript"></script>
                    <script type="text/javascript">
                            SimpleJekyllSearch.init({
                                searchInput: document.getElementById('search-input'),
                                resultsContainer: document.getElementById('results-container'),
                                dataSource: 'https://docs.woltlab.com/5.3/search.json',
                                searchResultTemplate: '<li><a href="{url}" title="SQL Package Installation Plugin">{title}</a></li>',
                    noResultsText: 'No results found.',
                            limit: 10,
                            fuzzy: true,
                    })
                    </script>
                    <!--end search-->
                </li>
            </ul>
        </div>
        </div>
        <!-- /.container -->
</nav>


  <div class="container">
    <div class="col-lg-12">&nbsp;</div>

    <div class="row">
      <div class="col-md-3">
        

<ul id="mysidebar" class="nav">
    <li class="sidebarTitle">WoltLab Suite 5.3</li>

    
            <li>
                <a href="#">Getting Started</a>
                <ul>
                    
                        
                                <li data-identifier="index"><a href="index.html">Introduction</a></li>
                            
                        
                                <li data-identifier="getting-started_quick-start"><a href="getting-started_quick-start.html">Quick Start</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">PHP API</a>
                <ul>
                    
                        
                                <li data-identifier="php_pages"><a href="php_pages.html">Pages</a></li>
                            
                        
                                <li data-identifier="php_database-objects"><a href="php_database-objects.html">Database Objects</a></li>
                            
                        
                                <li data-identifier="php_database-access"><a href="php_database-access.html">Database Access</a></li>
                            
                        
                                <li data-identifier="php_exceptions"><a href="php_exceptions.html">Exceptions</a></li>
                            
                        
                            <li class="subfolders">
                                <a href="#">API</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="php_api_caches"><a href="php_api_caches.html">Caches</a></li>
                                        
                                    
                                            <li data-identifier="php_api_comments"><a href="php_api_comments.html">Comments</a></li>
                                        
                                    
                                            <li data-identifier="php_api_cronjobs"><a href="php_api_cronjobs.html">Cronjobs</a></li>
                                        
                                    
                                            <li data-identifier="php_api_events"><a href="php_api_events.html">Events</a></li>
                                        
                                    
                                            <li data-identifier="php_api_form_builder"><a href="php_api_form_builder.html">Form Builder</a></li>
                                        
                                    
                                            <li data-identifier="php_api_package_installation_plugins"><a href="php_api_package_installation_plugins.html">Package Installation Plugins</a></li>
                                        
                                    
                                            <li data-identifier="php_api_user_activity_points"><a href="php_api_user_activity_points.html">User Activity Points</a></li>
                                        
                                    
                                            <li data-identifier="php_api_user_notifications"><a href="php_api_user_notifications.html">User Notifications</a></li>
                                        
                                    
                                            <li data-identifier="php_api_sitemaps"><a href="php_api_sitemaps.html">Sitemaps</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                                <li data-identifier="php_code-style"><a href="php_code-style.html">Code Style</a></li>
                            
                        
                                <li data-identifier="php_apps"><a href="php_apps.html">Apps</a></li>
                            
                        
                                <li data-identifier="php_gdpr"><a href="php_gdpr.html">GDPR</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Languages, Templates & CSS</a>
                <ul>
                    
                        
                                <li data-identifier="view_languages"><a href="view_languages.html">Languages</a></li>
                            
                        
                                <li data-identifier="view_templates"><a href="view_templates.html">Templates</a></li>
                            
                        
                                <li data-identifier="view_css"><a href="view_css.html">CSS</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">JavaScript API</a>
                <ul>
                    
                        
                                <li data-identifier="javascript_general-usage"><a href="javascript_general-usage.html">General Usage</a></li>
                            
                        
                            <li class="subfolders">
                                <a href="#">New API</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="javascript_new-api_writing-a-module"><a href="javascript_new-api_writing-a-module.html">Writing a module</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_data-structures"><a href="javascript_new-api_data-structures.html">Data Structures</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_core"><a href="javascript_new-api_core.html">Core Functions</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_dom"><a href="javascript_new-api_dom.html">DOM</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_events"><a href="javascript_new-api_events.html">Event Handling</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_ajax"><a href="javascript_new-api_ajax.html">Ajax</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_dialogs"><a href="javascript_new-api_dialogs.html">Dialogs</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_browser"><a href="javascript_new-api_browser.html">Browser and Screen Sizes</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_ui"><a href="javascript_new-api_ui.html">User Interface</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                                <li data-identifier="javascript_legacy-api"><a href="javascript_legacy-api.html">Legacy API</a></li>
                            
                        
                                <li data-identifier="javascript_helper-functions"><a href="javascript_helper-functions.html">Helper Functions</a></li>
                            
                        
                                <li data-identifier="javascript_code-snippets"><a href="javascript_code-snippets.html">Code Snippets</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Package Components</a>
                <ul>
                    
                        
                                <li data-identifier="package_package-xml"><a href="package_package-xml.html">package.xml</a></li>
                            
                        
                                <li data-identifier="package_pip"><a href="package_pip.html">PIPs</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Migration</a>
                <ul>
                    
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 5.2</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-52_php"><a href="migration_wsc-52_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-52_templates"><a href="migration_wsc-52_templates.html">Templates and Languages</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-52_libraries"><a href="migration_wsc-52_libraries.html">Third Party Libraries</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 3.1</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-31_php"><a href="migration_wsc-31_php.html">PHP API</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 3.0</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-30_php"><a href="migration_wsc-30_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_javascript"><a href="migration_wsc-30_javascript.html">JavaScript API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_templates"><a href="migration_wsc-30_templates.html">Templates</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_css"><a href="migration_wsc-30_css.html">CSS</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_package"><a href="migration_wsc-30_package.html">Package Components</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WCF 2.1</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wcf-21_php"><a href="migration_wcf-21_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_templates"><a href="migration_wcf-21_templates.html">Templates</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_css"><a href="migration_wcf-21_css.html">CSS</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_package"><a href="migration_wcf-21_package.html">Package Components</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                </ul>
            </li>
        
            <li>
                <a href="#">Tutorials</a>
                <ul>
                    
                        
                                <li data-identifier="tutorial_tutorial-series"><a href="tutorial_tutorial-series.html">Tutorial Series</a></li>
                            
                        
                </ul>
            </li>
        
    
</ul>

<script>
(function() {
    var sidebar = $('#mysidebar');
    var item = sidebar.find('.active');
    if (item.length === 0) {
        var parent = 'package_pip';
        if (parent) {
            sidebar.find('li[data-identifier="' + parent + '"]').addClass('active');
        }
    }

    sidebar.find(".active").parents('li').toggleClass("active");
})();
</script>

      </div>

      <div class="col-md-9">
        <div class="post-header">
   <h1 class="post-title-main">SQL Package Installation Plugin</h1>
</div>


<div class="post-content">

   
<!-- this handles the automatic toc. use ## for subheads to auto-generate the on-page minitoc. if you use html tags, you must supply an ID for the heading element in order for it to appear in the minitoc. -->
<script>
$( document ).ready(function() {
  // Handler for .ready() called.

$('#toc').toc({ minimumHeaders: 0, listType: 'ul', showSpeed: 0, headers: 'h2,h3,h4' });

/* this offset helps account for the space taken up by the floating toolbar. */
$('#toc').on('click', 'a', function() {
  var target = $(this.getAttribute('href'))
    , scroll_target = target.offset().top

  $(window).scrollTop(scroll_target - 10);
  return false
})
  
});
</script>

<div id="toc"></div>

    
  <p>Execute SQL instructions using a MySQL-flavored syntax.</p>

<div class="bs-callout bs-callout-warning">This file is parsed by WoltLab Suite Core to allow reverting of certain changes, but not every syntax MySQL supports is recognized by the parser. To avoid any troubles, you should always use statements relying on the SQL standard.</div>

<h2 id="expected-value">Expected Value</h2>

<p>The <code class="language-plaintext highlighter-rouge">sql</code> package installation plugin expects a relative path to a <code class="language-plaintext highlighter-rouge">.sql</code> file.</p>

<h2 id="features">Features</h2>

<h3 id="logging">Logging</h3>

<p>WoltLab Suite Core uses a SQL parser to extract queries and log certain actions.
This allows WoltLab Suite Core to revert some of the changes you apply upon package uninstallation.</p>

<p>The logged changes are:</p>

<ul>
  <li><code class="language-plaintext highlighter-rouge">CREATE TABLE</code></li>
  <li><code class="language-plaintext highlighter-rouge">ALTER TABLE … ADD COLUMN</code></li>
  <li><code class="language-plaintext highlighter-rouge">ALTER TABLE … ADD … KEY</code></li>
</ul>

<h3 id="instance-number">Instance Number</h3>

<p>It is possible to use different instance numbers, e.g. two separate WoltLab Suite Core installations within one database.
WoltLab Suite Core requires you to always use <code class="language-plaintext highlighter-rouge">wcf1_&lt;tableName&gt;</code> or <code class="language-plaintext highlighter-rouge">&lt;app&gt;1_&lt;tableName&gt;</code> (e.g. <code class="language-plaintext highlighter-rouge">blog1_blog</code> in WoltLab Suite Blog), the number (<code class="language-plaintext highlighter-rouge">1</code>) will be automatically replaced prior to execution.
If you every use anything other but <code class="language-plaintext highlighter-rouge">1</code>, you will eventually break things, thus always use <code class="language-plaintext highlighter-rouge">1</code>!</p>

<h3 id="table-type">Table Type</h3>

<p>WoltLab Suite Core will determine the type of database tables on its own:
If the table contains a <code class="language-plaintext highlighter-rouge">FULLTEXT</code> index, it uses <code class="language-plaintext highlighter-rouge">MyISAM</code>, otherwise <code class="language-plaintext highlighter-rouge">InnoDB</code> is used.</p>

<h2 id="limitations">Limitations</h2>

<h3 id="logging-1">Logging</h3>

<p>WoltLab Suite Core cannot revert changes to the database structure which would cause to the data to be either changed or new data to be incompatible with the original format.
Additionally, WoltLab Suite Core does not track regular SQL queries such as <code class="language-plaintext highlighter-rouge">DELETE</code> or <code class="language-plaintext highlighter-rouge">UPDATE</code>.</p>

<h3 id="triggers">Triggers</h3>

<p>WoltLab Suite Core does not support trigger since MySQL does not support execution of triggers if the event was fired by a cascading foreign key action.
If you really need triggers, you should consider adding them by custom SQL queries using a <a href="package_pip_script.html">script</a>.</p>

<h2 id="example">Example</h2>

<p><code class="language-plaintext highlighter-rouge">package.xml</code>:</p>

<div class="language-xml highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="nt">&lt;instruction</span> <span class="na">type=</span><span class="s">"sql"</span><span class="nt">&gt;</span>install.sql<span class="nt">&lt;/instruction&gt;</span>
</code></pre></div></div>

<p>Example content:</p>

<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">wcf1_foo_bar</span> <span class="p">(</span>
	<span class="n">fooID</span> <span class="nb">INT</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="n">AUTO_INCREMENT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
	<span class="n">packageID</span> <span class="nb">INT</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">,</span>
	<span class="n">bar</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">255</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span> <span class="s1">''</span><span class="p">,</span>
	<span class="n">foobar</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span> <span class="s1">''</span><span class="p">,</span>
	
	<span class="k">UNIQUE</span> <span class="k">KEY</span> <span class="n">baz</span> <span class="p">(</span><span class="n">bar</span><span class="p">,</span> <span class="n">foobar</span><span class="p">)</span>
<span class="p">);</span>

<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">wcf1_foo_bar</span> <span class="k">ADD</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">packageID</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">wcf1_package</span> <span class="p">(</span><span class="n">packageID</span><span class="p">)</span> <span class="k">ON</span> <span class="k">DELETE</span> <span class="k">CASCADE</span><span class="p">;</span>
</code></pre></div></div>


    <div class="tags">
        
    </div>

    
</div>

      </div>
    </div>
  </div>

  <div class="footerBox">
    <div class="container">
      <div class="footerBoxLeft">
        
        <a target="_blank" href="https://github.com/woltlab/woltlab.github.io/blob/master/pages/package/pip/package_pip_sql.md" class="btn btn-default githubEditButton no_icon" role="button"><i class="fa fa-github fa-lg"></i> Edit on GitHub</a>
        <p>Site last generated: Mar 5, 2021</p>
      </div>
      <div class="footerBoxRight">
        <a class="no_icon" href="https://www.woltlab.com"><img src="https://docs.woltlab.com/5.3/images/woltlab-black.png" srcset="https://docs.woltlab.com/5.3/images/woltlab-black@2x.png 2x" height="40" width="204" alt=""></a>
      </div>
    </div>
  </div>

  <div class="pageFooter">
    <div class="container">
      &copy; 2001 ‐ 2021 <a class="no_icon" href="https://www.woltlab.com">WoltLab GmbH</a>. All rights reserved. | <a class="no_icon" href="https://www.woltlab.com/legal-notice/">Legal Notice</a> | <a class="no_icon" href="https://www.woltlab.com/privacy-policy/">Privacy Policy</a>
    </div>
  </div>
</body>

</html>
Commit	Line	Data
d9cdc0cc TD	1	<!DOCTYPE html>
	2	<head>
	3	<meta charset="utf-8">
	4	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	5	<meta name="viewport" content="width=device-width, initial-scale=1">
	6	<meta name="description" content="">
	7	<meta name="keywords" content=" ">
	8	<title>SQL Package Installation Plugin \| WoltLab Suite 5.3 Documentation</title>
	9
e2f8eee7	10	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/syntax.css">
d9cdc0cc	11	<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:400,300,600">
e2f8eee7	12	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/font-awesome.min.css">
d9cdc0cc	13	<!--<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">-->
e2f8eee7 TD	14	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/modern-business.css">
	15	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/lavish-bootstrap.css">
	16	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/customstyles.css">
	17	<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/theme-blue.css?v=3">
d9cdc0cc	18
e2f8eee7 TD	19	<script src="https://docs.woltlab.com/5.3/js/jquery.min.js"></script>
	20	<script src="https://docs.woltlab.com/5.3/js/jquery.cookie.min.js"></script>
	21	<script src="https://docs.woltlab.com/5.3/js/jquery.navgoco.min.js"></script>
	22	<script src="https://docs.woltlab.com/5.3/js/bootstrap.min.js"></script>
	23	<script src="https://docs.woltlab.com/5.3/js/anchor.min.js"></script>
	24	<script src="https://docs.woltlab.com/5.3/js/toc.js"></script>
	25	<script src="https://docs.woltlab.com/5.3/js/customscripts.js"></script>
d9cdc0cc	26
e2f8eee7	27	<link rel="shortcut icon" href="https://docs.woltlab.com/5.3/images/favicon.ico">
d9cdc0cc	28
e2f8eee7	29	<link rel="alternate" type="application/rss+xml" title="woltlab.github.io" href="https://docs.woltlab.com/5.3feed.xml">
d9cdc0cc TD	30
	31	<script>
	32	$(document).ready(function() {
	33	// Initialize navgoco with default options
	34	$("#mysidebar").navgoco({
	35	caretHtml: '',
	36	accordion: true,
	37	openClass: 'active', // open
	38	save: false, // leave false or nav highlighting doesn't work right
	39	cookie: {
	40	name: 'navgoco',
	41	expires: false,
	42	path: '/'
	43	},
	44	slide: {
	45	duration: 400,
	46	easing: 'swing'
	47	}
	48	});
	49
	50	$("#collapseAll").click(function(e) {
	51	e.preventDefault();
	52	$("#mysidebar").navgoco('toggle', false);
	53	});
	54
	55	$("#expandAll").click(function(e) {
	56	e.preventDefault();
	57	$("#mysidebar").navgoco('toggle', true);
	58	});
	59
	60	});
	61
	62	</script>
	63	<script>
	64	$(function () {
	65	$('[data-toggle="tooltip"]').tooltip()
	66	})
	67	</script>
	68
	69
	70	</head>
	71	<body>
	72	<!-- Navigation -->
	73	<nav class="navbar navbar-inverse navbar-fixed-top">
	74	<div class="container topnavlinks">
	75	<div class="navbar-header">
	76	<button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
	77	<span class="sr-only">Toggle navigation</span>
	78	<span class="icon-bar"></span>
	79	<span class="icon-bar"></span>
	80	<span class="icon-bar"></span>
	81	</button>
	82	<a class="fa fa-home fa-lg navbar-brand" href="index.html"> <span class="projectTitle"> WoltLab Suite 5.3 Documentation</span></a>
	83	</div>
	84	<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
	85	<ul class="nav navbar-nav navbar-right">
	86	<!-- entries without drop-downs appear here -->
	87
	88
	89
	90	<li><a href="https://www.woltlab.com" target="_blank">woltlab.com</a></li>
	91
	92
	93
94
95
96	<li><a href="https://github.com/WoltLab/WCF/" target="_blank">Code on github.com</a></li>
97
98
99
100	<!-- entries with drop-downs appear here -->
101	<!-- conditional logic to control which topnav appears for the audience defined in the configuration file.-->
102
103
104	<!--comment out this block if you want to hide search-->
105	<li>
106	<!--start search-->
107	<div id="search-demo-container">
108	<input type="text" id="search-input" placeholder="search...">
109	<ul id="results-container"></ul>
110	</div>
e2f8eee7	111	<script src="https://docs.woltlab.com/5.3/js/jekyll-search.js" type="text/javascript"></script>
d9cdc0cc TD	112	<script type="text/javascript">
	113	SimpleJekyllSearch.init({
	114	searchInput: document.getElementById('search-input'),
	115	resultsContainer: document.getElementById('results-container'),
e2f8eee7	116	dataSource: 'https://docs.woltlab.com/5.3/search.json',
d9cdc0cc TD	117	searchResultTemplate: '<li><a href="{url}" title="SQL Package Installation Plugin">{title}</a></li>',
	118	noResultsText: 'No results found.',
	119	limit: 10,
	120	fuzzy: true,
	121	})
	122	</script>
	123	<!--end search-->
	124	</li>
	125	</ul>
	126	</div>
	127	</div>
	128	<!-- /.container -->
	129	</nav>
	130
	131
	132	<div class="container">
	133	<div class="col-lg-12"> </div>
	134
	135	<div class="row">
	136	<div class="col-md-3">
	137
	138
	139
	140	<ul id="mysidebar" class="nav">
	141	<li class="sidebarTitle">WoltLab Suite 5.3</li>
	142
	143
	144
	145	<li>
	146	<a href="#">Getting Started</a>
	147	<ul>
	148
	149
	150
	151
	152	<li data-identifier="index"><a href="index.html">Introduction</a></li>
	153
	154
	155
	156
	157
	158
	159
	160
	161	<li data-identifier="getting-started_quick-start"><a href="getting-started_quick-start.html">Quick Start</a></li>
	162
	163
	164
	165
	166
	167	</ul>
	168	</li>
	169
	170	<li>
	171	<a href="#">PHP API</a>
	172	<ul>
	173
	174
	175
	176
	177	<li data-identifier="php_pages"><a href="php_pages.html">Pages</a></li>
	178
	179
	180
181
182
183
184
185
186	<li data-identifier="php_database-objects"><a href="php_database-objects.html">Database Objects</a></li>
187
188
189
190
191
192
193
194
195	<li data-identifier="php_database-access"><a href="php_database-access.html">Database Access</a></li>
196
197
198
199
200
201
202
203
204	<li data-identifier="php_exceptions"><a href="php_exceptions.html">Exceptions</a></li>
205
206
207
208
209
210
211
212
213	<li class="subfolders">
214	<a href="#">API</a>
215	<ul>
216
217
218
219	<li data-identifier="php_api_caches"><a href="php_api_caches.html">Caches</a></li>
220
221
222
223
224	<li data-identifier="php_api_comments"><a href="php_api_comments.html">Comments</a></li>
225
226
227
228
229	<li data-identifier="php_api_cronjobs"><a href="php_api_cronjobs.html">Cronjobs</a></li>
230
231
232
233
234	<li data-identifier="php_api_events"><a href="php_api_events.html">Events</a></li>
235
236
237
238
239	<li data-identifier="php_api_form_builder"><a href="php_api_form_builder.html">Form Builder</a></li>
240
241
242
243
244	<li data-identifier="php_api_package_installation_plugins"><a href="php_api_package_installation_plugins.html">Package Installation Plugins</a></li>
245
246
247
248
249	<li data-identifier="php_api_user_activity_points"><a href="php_api_user_activity_points.html">User Activity Points</a></li>
250
251
252
253
254	<li data-identifier="php_api_user_notifications"><a href="php_api_user_notifications.html">User Notifications</a></li>
255
256
257
258
259	<li data-identifier="php_api_sitemaps"><a href="php_api_sitemaps.html">Sitemaps</a></li>
260
261
262	</ul>
263	</li>
264
265
266
267
268
269	<li data-identifier="php_code-style"><a href="php_code-style.html">Code Style</a></li>
270
271
272
273
274
275
276
277
278	<li data-identifier="php_apps"><a href="php_apps.html">Apps</a></li>
279
280
281
282
283
284
285
286
287	<li data-identifier="php_gdpr"><a href="php_gdpr.html">GDPR</a></li>
288
289
290
291
292
293	</ul>
294	</li>
295
296	<li>
297	<a href="#">Languages, Templates & CSS</a>
298	<ul>
299
300
301
302
303	<li data-identifier="view_languages"><a href="view_languages.html">Languages</a></li>
304
305
306
307
308
309
310
311
312	<li data-identifier="view_templates"><a href="view_templates.html">Templates</a></li>
313
314
315
316
317
318
319
320
321	<li data-identifier="view_css"><a href="view_css.html">CSS</a></li>
322
323
324
325
326
327	</ul>
328	</li>
329
330	<li>
331	<a href="#">JavaScript API</a>
332	<ul>
333
334
335
336
337	<li data-identifier="javascript_general-usage"><a href="javascript_general-usage.html">General Usage</a></li>
338
339
340
341
342
343
344
345
346	<li class="subfolders">
347	<a href="#">New API</a>
348	<ul>
349
350
351
352	<li data-identifier="javascript_new-api_writing-a-module"><a href="javascript_new-api_writing-a-module.html">Writing a module</a></li>
353
354
355
356
357	<li data-identifier="javascript_new-api_data-structures"><a href="javascript_new-api_data-structures.html">Data Structures</a></li>
358
359
360
361
362	<li data-identifier="javascript_new-api_core"><a href="javascript_new-api_core.html">Core Functions</a></li>
363
364
365
366
367	<li data-identifier="javascript_new-api_dom"><a href="javascript_new-api_dom.html">DOM</a></li>
368
369
370
371
372	<li data-identifier="javascript_new-api_events"><a href="javascript_new-api_events.html">Event Handling</a></li>
373
374
375
376
377	<li data-identifier="javascript_new-api_ajax"><a href="javascript_new-api_ajax.html">Ajax</a></li>
378
379
380
381
382	<li data-identifier="javascript_new-api_dialogs"><a href="javascript_new-api_dialogs.html">Dialogs</a></li>
383
384
385
386
387	<li data-identifier="javascript_new-api_browser"><a href="javascript_new-api_browser.html">Browser and Screen Sizes</a></li>
388
389
390
391
392	<li data-identifier="javascript_new-api_ui"><a href="javascript_new-api_ui.html">User Interface</a></li>
393
394
395	</ul>
396	</li>
397
398
399
400
401
402	<li data-identifier="javascript_legacy-api"><a href="javascript_legacy-api.html">Legacy API</a></li>
403
404
405
406
407
408
409
410
411	<li data-identifier="javascript_helper-functions"><a href="javascript_helper-functions.html">Helper Functions</a></li>
412
413
414
415
416
417
418
419
420	<li data-identifier="javascript_code-snippets"><a href="javascript_code-snippets.html">Code Snippets</a></li>
421
422
423
424
425
426	</ul>
427	</li>
428
429	<li>
430	<a href="#">Package Components</a>
431	<ul>
432
433
434
435
436	<li data-identifier="package_package-xml"><a href="package_package-xml.html">package.xml</a></li>
437
438
439
440
441
442
443
444
445	<li data-identifier="package_pip"><a href="package_pip.html">PIPs</a></li>
446
447
448
449
450
451	</ul>
452	</li>
453
454	<li>
455	<a href="#">Migration</a>
456	<ul>
457
458
459
460
461	<li class="subfolders">
462	<a href="#">Migrating from WSC 5.2</a>
463	<ul>
464
465
466
467	<li data-identifier="migration_wsc-52_php"><a href="migration_wsc-52_php.html">PHP API</a></li>
468
469
470
471
472	<li data-identifier="migration_wsc-52_templates"><a href="migration_wsc-52_templates.html">Templates and Languages</a></li>
473
474
475
476
477	<li data-identifier="migration_wsc-52_libraries"><a href="migration_wsc-52_libraries.html">Third Party Libraries</a></li>
478
479
480	</ul>
481	</li>
482
483	<li class="subfolders">
484	<a href="#">Migrating from WSC 3.1</a>
485	<ul>
486
487
488
489	<li data-identifier="migration_wsc-31_php"><a href="migration_wsc-31_php.html">PHP API</a></li>
490
491
492	</ul>
493	</li>
494
495	<li class="subfolders">
496	<a href="#">Migrating from WSC 3.0</a>
497	<ul>
498
499
500
501	<li data-identifier="migration_wsc-30_php"><a href="migration_wsc-30_php.html">PHP API</a></li>
502
503
504
505
506	<li data-identifier="migration_wsc-30_javascript"><a href="migration_wsc-30_javascript.html">JavaScript API</a></li>
507
508
509
510
511	<li data-identifier="migration_wsc-30_templates"><a href="migration_wsc-30_templates.html">Templates</a></li>
512
513
514
515
516	<li data-identifier="migration_wsc-30_css"><a href="migration_wsc-30_css.html">CSS</a></li>
517
518
519
520
521	<li data-identifier="migration_wsc-30_package"><a href="migration_wsc-30_package.html">Package Components</a></li>
522
523
524	</ul>
525	</li>
526
527	<li class="subfolders">
528	<a href="#">Migrating from WCF 2.1</a>
529	<ul>
530
531
532
533	<li data-identifier="migration_wcf-21_php"><a href="migration_wcf-21_php.html">PHP API</a></li>
534
535
536
537
538	<li data-identifier="migration_wcf-21_templates"><a href="migration_wcf-21_templates.html">Templates</a></li>
539
540
541
542
543	<li data-identifier="migration_wcf-21_css"><a href="migration_wcf-21_css.html">CSS</a></li>
544
545
546
547
548	<li data-identifier="migration_wcf-21_package"><a href="migration_wcf-21_package.html">Package Components</a></li>
549
550
551	</ul>
552	</li>
553
554
555	</ul>
556	</li>
557
558	<li>
559	<a href="#">Tutorials</a>
560	<ul>
561
562
563
564
565	<li data-identifier="tutorial_tutorial-series"><a href="tutorial_tutorial-series.html">Tutorial Series</a></li>
566
567
568
569
570
571	</ul>
572	</li>
573
574
575	</ul>
576
577	<script>
578	(function() {
579	var sidebar = $('#mysidebar');
580	var item = sidebar.find('.active');
581	if (item.length === 0) {
582	var parent = 'package_pip';
583	if (parent) {
584	sidebar.find('li[data-identifier="' + parent + '"]').addClass('active');
585	}
586	}
587
588	sidebar.find(".active").parents('li').toggleClass("active");
589	})();
590	</script>
591
592	</div>
593
594	<div class="col-md-9">
595	<div class="post-header">
596	<h1 class="post-title-main">SQL Package Installation Plugin</h1>
597	</div>
598
599
600
601	<div class="post-content">
602
603
604
605
606
607	<!-- this handles the automatic toc. use ## for subheads to auto-generate the on-page minitoc. if you use html tags, you must supply an ID for the heading element in order for it to appear in the minitoc. -->
608	<script>
609	$( document ).ready(function() {
610	// Handler for .ready() called.
611
612	$('#toc').toc({ minimumHeaders: 0, listType: 'ul', showSpeed: 0, headers: 'h2,h3,h4' });
613
614	/* this offset helps account for the space taken up by the floating toolbar. */
615	$('#toc').on('click', 'a', function() {
616	var target = $(this.getAttribute('href'))
617	, scroll_target = target.offset().top
618
619	$(window).scrollTop(scroll_target - 10);
620	return false
621	})
622
623	});
624	</script>
625
626	<div id="toc"></div>
627
628
629
630	<p>Execute SQL instructions using a MySQL-flavored syntax.</p>
631
632	<div class="bs-callout bs-callout-warning">This file is parsed by WoltLab Suite Core to allow reverting of certain changes, but not every syntax MySQL supports is recognized by the parser. To avoid any troubles, you should always use statements relying on the SQL standard.</div>
633
634	<h2 id="expected-value">Expected Value</h2>
635
636	<p>The <code class="language-plaintext highlighter-rouge">sql</code> package installation plugin expects a relative path to a <code class="language-plaintext highlighter-rouge">.sql</code> file.</p>
637
638	<h2 id="features">Features</h2>
639
640	<h3 id="logging">Logging</h3>
641
642	<p>WoltLab Suite Core uses a SQL parser to extract queries and log certain actions.
643	This allows WoltLab Suite Core to revert some of the changes you apply upon package uninstallation.</p>
644
645	<p>The logged changes are:</p>
646
647	<ul>
648	<li><code class="language-plaintext highlighter-rouge">CREATE TABLE</code></li>
649	<li><code class="language-plaintext highlighter-rouge">ALTER TABLE … ADD COLUMN</code></li>
650	<li><code class="language-plaintext highlighter-rouge">ALTER TABLE … ADD … KEY</code></li>
651	</ul>
652
653	<h3 id="instance-number">Instance Number</h3>
654
655	<p>It is possible to use different instance numbers, e.g. two separate WoltLab Suite Core installations within one database.
656	WoltLab Suite Core requires you to always use <code class="language-plaintext highlighter-rouge">wcf1_<tableName></code> or <code class="language-plaintext highlighter-rouge"><app>1_<tableName></code> (e.g. <code class="language-plaintext highlighter-rouge">blog1_blog</code> in WoltLab Suite Blog), the number (<code class="language-plaintext highlighter-rouge">1</code>) will be automatically replaced prior to execution.
657	If you every use anything other but <code class="language-plaintext highlighter-rouge">1</code>, you will eventually break things, thus always use <code class="language-plaintext highlighter-rouge">1</code>!</p>
658
659	<h3 id="table-type">Table Type</h3>
660
661	<p>WoltLab Suite Core will determine the type of database tables on its own:
662	If the table contains a <code class="language-plaintext highlighter-rouge">FULLTEXT</code> index, it uses <code class="language-plaintext highlighter-rouge">MyISAM</code>, otherwise <code class="language-plaintext highlighter-rouge">InnoDB</code> is used.</p>
663
664	<h2 id="limitations">Limitations</h2>
665
666	<h3 id="logging-1">Logging</h3>
667
668	<p>WoltLab Suite Core cannot revert changes to the database structure which would cause to the data to be either changed or new data to be incompatible with the original format.
669	Additionally, WoltLab Suite Core does not track regular SQL queries such as <code class="language-plaintext highlighter-rouge">DELETE</code> or <code class="language-plaintext highlighter-rouge">UPDATE</code>.</p>
670
671	<h3 id="triggers">Triggers</h3>
672
673	<p>WoltLab Suite Core does not support trigger since MySQL does not support execution of triggers if the event was fired by a cascading foreign key action.
674	If you really need triggers, you should consider adding them by custom SQL queries using a <a href="package_pip_script.html">script</a>.</p>
675
676	<h2 id="example">Example</h2>
677
678	<p><code class="language-plaintext highlighter-rouge">package.xml</code>:</p>
679
680	<div class="language-xml highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="nt"><instruction</span> <span class="na">type=</span><span class="s">"sql"</span><span class="nt">></span>install.sql<span class="nt"></instruction></span>
681	</code></pre></div></div>
682
683	<p>Example content:</p>
684
685	<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">wcf1_foo_bar</span> <span class="p">(</span>
686	<span class="n">fooID</span> <span class="nb">INT</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="n">AUTO_INCREMENT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
687	<span class="n">packageID</span> <span class="nb">INT</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">,</span>
688	<span class="n">bar</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">255</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span> <span class="s1">''</span><span class="p">,</span>
689	<span class="n">foobar</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span> <span class="s1">''</span><span class="p">,</span>
690
691	<span class="k">UNIQUE</span> <span class="k">KEY</span> <span class="n">baz</span> <span class="p">(</span><span class="n">bar</span><span class="p">,</span> <span class="n">foobar</span><span class="p">)</span>
692	<span class="p">);</span>
693
694	<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">wcf1_foo_bar</span> <span class="k">ADD</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">packageID</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">wcf1_package</span> <span class="p">(</span><span class="n">packageID</span><span class="p">)</span> <span class="k">ON</span> <span class="k">DELETE</span> <span class="k">CASCADE</span><span class="p">;</span>
695	</code></pre></div></div>
696
697
698	<div class="tags">
699
700	</div>
701
702
703
704	</div>
705
706	</div>
707	</div>
708	</div>
709
710	<div class="footerBox">
711	<div class="container">
712	<div class="footerBoxLeft">
713
714	<a target="_blank" href="https://github.com/woltlab/woltlab.github.io/blob/master/pages/package/pip/package_pip_sql.md" class="btn btn-default githubEditButton no_icon" role="button"><i class="fa fa-github fa-lg"></i> Edit on GitHub</a>
e2f8eee7	715	<p>Site last generated: Mar 5, 2021</p>
d9cdc0cc TD	716	</div>
d9cdc0cc TD	717	<div class="footerBoxRight">
e2f8eee7	718	<a class="no_icon" href="https://www.woltlab.com"><img src="https://docs.woltlab.com/5.3/images/woltlab-black.png" srcset="https://docs.woltlab.com/5.3/images/woltlab-black@2x.png 2x" height="40" width="204" alt=""></a>
d9cdc0cc TD	719	</div>
	720	</div>
	721	</div>
	722
	723	<div class="pageFooter">
	724	<div class="container">
	725	© 2001 ‐ 2021 <a class="no_icon" href="https://www.woltlab.com">WoltLab GmbH</a>. All rights reserved. \| <a class="no_icon" href="https://www.woltlab.com/legal-notice/">Legal Notice</a> \| <a class="no_icon" href="https://www.woltlab.com/privacy-policy/">Privacy Policy</a>
	726	</div>
	727	</div>
	728	</body>
	729
	730	</html>