docs/package/pip/sql.md

   1 # SQL Package Installation Plugin
   2
   3 Execute SQL instructions using a MySQL-flavored syntax.
   4
   5 !!! 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."
   6
   7
   8 ## Expected Value
   9
  10 The `sql` package installation plugin expects a relative path to a `.sql` file.
  11
  12
  13 ## Features
  14
  15 ### Logging
  16
  17 WoltLab Suite Core uses a SQL parser to extract queries and log certain actions.
  18 This allows WoltLab Suite Core to revert some of the changes you apply upon package uninstallation.
  19
  20 The logged changes are:
  21
  22 - `CREATE TABLE`
  23 - `ALTER TABLE … ADD COLUMN`
  24 - `ALTER TABLE … ADD … KEY`
  25
  26 ### Instance Number
  27
  28 It is possible to use different instance numbers, e.g. two separate WoltLab Suite Core installations within one database.
  29 WoltLab Suite Core requires you to always use `wcf1_<tableName>` or `<app>1_<tableName>` (e.g. `blog1_blog` in WoltLab Suite Blog), the number (`1`) will be automatically replaced prior to execution.
  30 If you every use anything other but `1`, you will eventually break things, thus always use `1`!
  31
  32 ### Table Type
  33
  34 WoltLab Suite Core will determine the type of database tables on its own:
  35 If the table contains a `FULLTEXT` index, it uses `MyISAM`, otherwise `InnoDB` is used.
  36
  37
  38 ## Limitations
  39
  40 ### Logging
  41
  42 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.
  43 Additionally, WoltLab Suite Core does not track regular SQL queries such as `DELETE` or `UPDATE`.
  44
  45 ### Triggers
  46
  47 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.
  48 If you really need triggers, you should consider adding them by custom SQL queries using a [script](script.md).
  49
  50
  51 ## Example
  52
  53 `package.xml`:
  54
  55 ```xml
  56 <instruction type="sql">install.sql</instruction>
  57 ```
  58
  59 Example content:
  60
  61 {jinja{ codebox(
  62   title="install.sql",
  63   language="sql",
  64   filepath="package/pip/install.sql"
  65 ) }}