DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:sqlite

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
plugin:sqlite [2018-04-25 23:32] – version upped andiplugin:sqlite [2024-03-05 23:51] (current) – version upped andi
Line 6: Line 6:
 email      : dokuwiki@cosmocode.de  email      : dokuwiki@cosmocode.de 
 type       : helper type       : helper
-lastupdate : 2018-03-27 +lastupdate : 2024-03-05 
-compatible : Adora BelleWeatherwaxBinkyPonder Stibbons, Hrun, Detritus, Elenor Of Tsort+compatible : HogfatherIgorJack JackrumKaos
 depends    :  depends    : 
 conflicts  conflicts 
Line 18: Line 18:
 ---- ----
  
-[[http://www.cosmocode.de/en/open-source/dokuwiki-plugins/|{{ http://cosmocode.de/static/img/dokuwiki/dwplugins.png?recache|A CosmoCode Plugin}}]]+[[https://www.cosmocode.de/en/open-source/dokuwiki-plugins/|{{ https://www.cosmocode.de/static/img/dokuwiki/dwplugins.png?recache|A CosmoCode Plugin}}]]
  
 +This plugin is not useful on its own. Instead it provides mechanisms for other plugins to create, manage and access a SQLite database. You only need to install it if another plugin requires it.
  
 ===== Requirements ===== ===== Requirements =====
  
-This plugin needs SQLite support in your PHP installation. It can work either with the [[http://php.net/sqlite|sqlite]] extension to access and create SQLite Version 2 databases or the [[http://www.php.net/manual/en/ref.pdo-sqlite.php|pdo-sqlite]] extension for accessing and creating SQLite Version 3 databases. If both extensions are available, pdo-sqlite will be used.+This plugin needs SQLite and PDO support in your PHP installation. Be sure the [[http://www.php.net/manual/en/ref.pdo-sqlite.php|pdo-sqlite]] extension is installed and loaded.
  
-**Important Upgrade Info:** older versions of this plugin supported sqlite2 only. If your system has both extensions installed, the plugin will switch to sqlite3 and your databases need to be converted to the new format. Here's how to do that: 
- 
-  * Go to the Admin screen 
-  * Select "SQLite Access" 
-  * Pick a sqlite2 data base 
-  * Click the "convert format" button 
  
 ===== Download and Installation ===== ===== Download and Installation =====
  
-Install the plugin using the [[plugin:plugin|Plugin Manager]] and the download URL above, which points to latest version of the plugin. Refer to [[:Plugins]] on how to install plugins manually.+Search and install the plugin using the [[plugin:extension|Extension Manager]]. Refer to [[:Plugins]] on how to install plugins manually.
  
 === Changes === === Changes ===
- 
-[[https://travis-ci.org/cosmocode/sqlite|{{  https://travis-ci.org/cosmocode/sqlite.png?nocache|Build Status}}]] 
  
 {{rss>https://github.com/cosmocode/sqlite/commits/master.atom date}} {{rss>https://github.com/cosmocode/sqlite/commits/master.atom date}}
Line 44: Line 37:
 ===== Admin Interface ===== ===== Admin Interface =====
  
-{{ http://img33.imageshack.us/img33/7122/20100127110355668x453sc.png?300|Admin Interface}} 
  
-The plugin comes with a simple admin interface where you can run your own SQL queries against any of the available databases.+The plugin comes with a simple admin interface that gives you low level access to any of the available databases. This includes:
  
-Available databases (''data/meta/*.sqlite'' and ''data/meta/*.sqlite3'') are shown in the Table of Contents. Select one and you can submit your own queries.+  executing arbitrary queries 
 +  * run predefined queries 
 +  * export and import database contents 
 +  save and recall self-defined queries
  
-===== Developer Information =====+To use any of its functionalities, first select the database you want to work on from the Table of Contents.
  
-To use this helper plugin in your own plugins, load it and call the init() function to connect to the database. Make sure you check if the plugin was loaded correctly and if the init() function ran without error, before executing your own code.+:!: Be sure you know what you're doing. This interface provides no safety nets against deleting or corrupting your database contents!
  
-<code php> +{{ https://i.imgur.com/VTpa13C.png | Admin Interface Screenshot }}
-// load the helper plugin +
-/** @var helper_plugin_sqlite $sqlite */ +
-$sqlite = plugin_load('helper', 'sqlite'); +
-if(!$sqlite){ +
-    msg('This plugin requires the sqlite pluginPlease install it', -1); +
-    return; +
-+
-// initialize the database connection +
-if(!$sqlite->init('myplugin',DOKU_PLUGIN.'myplugin/db/')){ +
-    return; +
-} +
-// use the plugin +
-$res = $sqlite->query("SELECT * FROM mytable"); +
-$arr = $sqlite->res2arr($res); +
-print_r($arr); +
-</code>+
  
-==== Functions ==== 
  
-=== init ===+===== Developer Documentation =====
  
-Initializes and opens the database. Needs to be called right after loading this helper plugin+This plugin provides you with all the mechanisms needed to use a sqlite database in your pluginAll functionality is provided through the ''\dokuwiki\plugin\sqlite\SQLiteDB'' class.
  
-  * $dbname is the name of the database. +It is recommended to lazy load the class if needed, for example in a [[devel:helper_plugins|helper component]] of your plugin. Since your helper is (usually) a singleton, it will ensure that all your code parts use the same database connectionAnd using lazy loading function ensures the connection is only established (and initializedwhen actually required.
-  * $updatedir is path where update SQL files are located (see below)+
  
-Returns boolean whether it succeed.+Below you find an example of a helper component that should get you started.
  
 <code php> <code php>
-$succeed = $plugin->init($dbname,$updatedir); +<?php
-</code>+
  
-=== storeEntry === +use dokuwiki\ErrorHandler; 
-Convenience function to run an INSERT OR REPLACE operation+use dokuwiki\Extension\Plugin; 
 +use dokuwiki\plugin\sqlite\SQLiteDB;
  
-The function takes a key-value array with the column names in the key and the actual value in the value, build the appropriate query and executes it. 
  
-     * $table - the table the entry should be saved to (will not be escaped) +class helper_plugin_example extends Plugin 
-     * $entry - A simple key-value pair array (only values will be escaped)+{ 
 +    protected $db;
  
-Returns false or result object+    /** 
 +     * Get SQLiteDB instance 
 +     * 
 +     * @return SQLiteDB|null 
 +     */ 
 +    public function getDB() 
 +    { 
 +        if ($this->db === null) { 
 +            try { 
 +                $this->db = new SQLiteDB('example', DOKU_PLUGIN . 'example/db/'); 
 +            } catch (\Exception $exception) { 
 +                if (defined('DOKU_UNITTEST')) throw new \RuntimeException('Could not load SQLite', 0, $exception); 
 +                ErrorHandler::logException($exception); 
 +                msg('Couldn\'t load sqlite.', -1); 
 +                return null; 
 +            } 
 +        } 
 +        return $this->db; 
 +    }
  
-<code php> +    /** 
-$res = $plugin->storeEntry($table, $entry+     * @param string $foo 
-</code>+     * @return array 
 +     */ 
 +    public function getAllBazForFoo($foo) 
 +    { 
 +        $db = $this->getDB(); 
 +        if (!$db) return [];
  
-=== query ===+        $result $db->queryAll('SELECT id, meta, baz FROM example WHERE foo ?', [$foo]); 
 +        return $result; 
 +    }
  
-Execute a query with the given parameters. +}
- +
-Takes care of escaping +
- +
-  * $sql - the statement +
-  * arguments... +
-    * Arguments can also be given as a single array +
- +
-Returns false or result object +
-<code php> +
-$res = $plugin->query($sql[,arg1[,arg2[...]]]);+
 </code> </code>
  
-=== countChanges === +==== Database Setup and Migration ====
-Count the number of records changed last time+
  
-Don't work after SELECT statement+As you've seen above, the **constructor** of the ''SQLiteDB'' class requires two parameters. A **database name** and **migration directory**.
  
-  * $res - query result+The database name is the file name your SQLite database. Unless you have very good reason, this should be the name of your plugin. In the example above, the name is ''example'', resulting in a database file ''data/meta/example.sqlite3'' to be created.
  
-Returns integer count+The second parameter defines the directory where the database migration files are to be found. Most plugins use a ''db'' directory in their plugin structure. Use the ''DOKU_PLUGIN'' or ''__DIR__'' constants to create the appropriate absolute path.
  
-<code php> +{{ https://i.imgur.com/W4Xl6TY.png|Migration directory example}}
-$count = $plugin->countChanges($res); +
-</code>+
  
-=== res2count ===+So what is the migration directory? Your plugin will need to create a database schema and maybe fill it with some initial data. When you release new versions of your plugin you might need to update the schema. This is done by migration files within the specified directory. It is all handled automatically when the constructor is called.
  
-Count the number of records in result+Each file correspondents to a certain database version. Version 1 is the very first setup that is done when the database is created. Each subsequent version is then applied above the previous version.
  
-This function is really inperformant in PDO and should be avoided!+The number of the most recent version has to be stored in a file called ''latest.version''. The update files it self have to be named ''updateXXXX.sql'' where XXXX is a zero-padded 4 digit number, starting with ''0001''.
  
-     * $res - query result+The update mechanism will execute all SQL queries found in the migration files automatically. The execution of each update is wrapped in transaction, you should not to do that yourself. If an update fails, the transaction is rolled back and the update is aborted.
  
-Returns integer count+The sqlite plugin keeps track of the current version of the database using a table called ''opts''. You may not have a table named like that!
  
-<code php> +Note: sometimes migrations are more complex than what can be done with simple SQL queries. You can write your own migration code using an action component and handling the [[#PLUGIN_SQLITE_DATABASE_UPGRADE]] event.
-$count = $plugin->res2count($res); +
-</code>+
  
-=== res2arr === 
  
-Returns a complete result set as array+==== Public API ====
  
-  * $res - a query result +The following methods are exposed by the ''SQLiteDB'' class and can be used in your code. Please note that ''PDO::ERRMODE_EXCEPTION'' option of PDO is enabled and all errors will throw Exceptions. Be sure to handle them in your code where necessary.
-  * $assoc - associated or numbered index array+
  
-Returns false or array with result +The list below should give you an rough overview on the available methods. For details, please refer to the source code and comments of the class itself: [[https://github.com/cosmocode/sqlite/blob/master/SQLiteDB.php|SQLiteDB.php]].
-<code php> +
-$rows = $plugin->res2arr($res[,$assoc]){ +
-</code>+
  
-Returns default an associated array, with column names as indexes. If ''$assoc = false'' it returns an array indexed by column number (0-indexed).+> Always use the provided parameter replacement mechanisms when passing user provided data into queries to avoid SQL injection attacks! See [[#query()]] method.
  
-=== res2row ===+=== getPdo() ===
  
-Return the next row from a given result set as associative array+This method returns the underlying [[https://www.php.net/manual/en/class.pdo.php|PDO object]] of the connection. You can use it to call all methods defined by the PDO interface.
  
-  * $res - the result from a query +This is useful to manage transactions or register your own functions, for example.
-  * //$numrow - wanted row number of result set// (Not supported by current backends)+
  
-Returns false or array with result 
  
-<code php> +=== query(===
-$row $plugin->res2row($res[, $rownum=0]) +
-</code>+
  
-=== res_fetch_array ===+Basic query execution.
  
-Fetch the next row as zero indexed array+Placeholder in the query will be replaced by the given replacements. You can either use positional ''?'' or named '':'' placeholders. This mechanism is the same in all other query methods below.
  
-  * $res - the result from query+This method returns [[https://www.php.net/manual/en/class.pdostatement.php|PDOStatement]] object. Be sure to close it's cursor when you're done. You usually want to use one of the convenience methods below.
  
-Returns false or zero indexed array 
 <code php> <code php>
-$row = $plugin->res_fetch_array($res)+$res = $sqlite->query('SELECT foo FROM example WHERE baz = ?', [$baz]); 
 +$row = $res->fetch(); 
 +$res->closeCursor();
 </code> </code>
  
-=== res_fetch_assoc ===+=== exec() ===
  
-Fetch the next row as assocative array +Similar to query, but used to run data modifying queries. Will return the last insert ID on INSERT statements or the number of affected rows otherwise.
- +
-  * $res - the result from a query +
- +
-Returns false or array with column names as index+
  
 <code php> <code php>
-$row = $plugin->res_fetch_assoc($res)+$id = $sqlite->exec('INSERT INTO example (bar) VALUES (?)', [$bar])
 +echo "new ID is $id";
 </code> </code>
  
-=== res2single ===+=== queryAll() ===
  
-Return the first value from the next row +Convenience method to execute a query and return all rows as associative arrays. Keep memory limits in mind when querying large data sets!
- +
-  * $res - the result from a query +
- +
-Returns false or string result value+
  
 <code php> <code php>
-$value = $plugin->res2single($res){+$rows = $sqlite->queryAll('SELECT * FROM example WHERE bar = ?', [$bar]); 
 +foreach($rows as $row) { 
 +    foreach($row as $col => $val) { 
 +        echo "$col: $val\n"; 
 +    } 
 +    echo "------\n"; 
 +}
 </code> </code>
  
-=== quote and join === +=== queryRecord() ===
- +
-Join the given values and quote them for SQL insertion+
  
-  * $vals - values to join +Convenience method to execute a query and return a single row only.
-  * $sep - separator char+
  
-Returns joined and quoted string 
  
 <code php> <code php>
-$string = $plugin->quote_and_join($vals,$sep=',');+$row = $sqlite->queryRecord('SELECT * FROM example WHERE bar ?', [$bar]); 
 +foreach($row as $col => $val) { 
 +    echo "$col: $val\n"; 
 +}
 </code> </code>
  
-=== quote string ===+=== saveRecord() ===
  
-Escape the given string and surround it with quotes+Convenience method to insert or replace a single entry in a table. The function expects an associative array for the $data parameter, where the array keys correspond to the column names.
  
-<code php> +If the given data violates a constraint, it is either ignored or the existing data is replaced.
-$string = $plugin->quote_string($string); +
-</code>+
  
-=== Escape string === +Note: this method only provides ''INSERT OR IGNORE'' and ''INSERT OR REPLACE'' functionality. If you need UPSERT functionality, build your [[https://www.sqlite.org/lang_UPSERT.html|own query]] and use [[#exec()]].
-Escape string for sql+
  
 <code php> <code php>
-$string = $plugin->escape_string($string);+$data 
 +    'foo' => 'Florb', 
 +    'baz' => 12932 
 +]; 
 +$new = $sqlite->saveRecord('example', $data); 
 +var_dump($new);
 </code> </code>
  
-=== close result set=== 
  
-Closes the result set (and it's cursors)+=== queryValue() ===
  
-If you're doing SELECT queries inside a TRANSACTION, be sure to call this function on all your results sets, before COMMITing the transaction. +Convenience method to query a single value from the databaseIt will return the first value in the first row of the result set.
- +
-Also required when not all rows of a result are fetched +
- +
-  * $res - the result from a query+
  
 <code php> <code php>
-$succeed = $plugin->res_close($res);+$foo = $sqlite->queryValue('SELECT foo FROM example WHERE bar = ?', [$bar])
 +echo $foo;
 </code> </code>
  
-=== create_function ===+=== queryKeyValueList() ===
-Registers a User Defined Function for use in SQL statements+
  
-   * $function_name name of the function used in SQL statements. +Convenience method to query a key-value list from the database. The first value in each result row becomes the key, the second value the valueYou need to take care of key uniqueness yourself, otherwise later values overwrite earlier ones.
-   * $callback - callback function to handle the defined SQL function(Should return a type understood by SQLite (i.e. scalar type)) +
-   * $num_args - Hint to the SQLite parser if the callback function accepts a predetermined number of arguments.+
  
-Returns true on succes, false on failure. 
 <code php> <code php>
-$succeed = $plugin->create_function($function_name$callback, $num_args)+$res = $sqlite->queryKeyValueList('SELECT foo,baz FROM example WHERE bar = ?'[$bar])
 +foreach($res as $key => $val) { 
 +    echo "$key: $val\n"; 
 +}
 </code> </code>
  
-==== DB Schema Setup/Update Mechanism ====+==== SQL Extensions ====
  
-Your plugin will need to create a database schema and maybe fill it with some initial data. When you release new versions of your plugin you might need to update the schema. The sqlite plugin provides simple mechanism to do so.+SQLite has the unique feature that PHP code can be registered as native SQL functions that then can be used within your queries. The sqlite plugin registers few custom functions described below.
  
-This is all handled within the ''init()'' functionThe second parameter has to point to a directory where your SQL files are locatedEach file correspondents to a certain database versionVersion 1 is the very first setup that is done when the database is created. Each subsequent version is then applied above the previous version.+Check the Functions class for details: [[https://github.com/cosmocode/sqlite/blob/master/Functions.php|Functions.php]].
  
-The number of the most recent version has to be stored in a file called ''latest.version''. The update files it self have to be named ''updateXXXX.sql'' where XXXX is a zero-padded 4 digit number, starting with ''0001''.+Your plugin can register it's own functions using the ''%%getPDO()->sqliteCreateFunction()%%'' and ''%%getPDO()->sqliteCreateAggregate()%%''.
  
-The update mechanism will wrap the execution of each update in a transaction, you need not to do that yourself. If an update fails, the transaction is rolled back and the update is aborted. 
  
-The sqlite plugin keeps track of the version a database is in currently using a table called ''opts''. You may not have a table named like that!+=== GETACCESSLEVEL ===
  
-==== SQL Extensions ====+Returns the integer DokuWiki permission level of a given pageid.
  
-The plugin provides a few additional features over the standard [[http://sqlite.org/lang.html|SQLite syntax]].+Notethis function will also return AUTH_NONE for hidden pages.
  
-=== ALTER TABLE ===+<code php> 
 +$sqlite->queryAll( 
 +  'SELECT page, foo, bar FROM example WHERE GETACCESSLEVEL(page) > ?', 
 +  [AUTH_READ] 
 +); 
 +</code>
  
-The plugin supports a simplified ''ALTER TABLE'' syntax. This is probably most useful in the update mechanism. The plugin emulates the alter table call by copying the affected data to a temporary table and dropping, recreating and refilling the original table. When used outside the update mechanism, it is recommended to wrap the call in a transaction. +=== PAGEEXISTS ===
- +
-<code bnf> +
-ALTER TABLE tbl_name alter_specification [, alter_specification] ...+
  
-alter_specification: +Checks if the given pagename exists. Returns ''1'' for existing pages, ''0'' otherwise.
-    ADD column_definition +
-  | DROP column_definition +
-  | CHANGE old_col_name column_definition+
  
-column_definition: +<code php> 
-    same as for create table statements+$sqlite->queryAll( 
 +  'SELECT page, foo, bar FROM example WHERE PAGEEXISTS(page) = 1' 
 +);
 </code> </code>
-Documentation [[http://sqlite.org/lang_createtable.html|create table]] 
  
-Examples:+=== REGEXP ===
  
-<code sql> +Matches a value against a given regular expression. The expression is used with ''/'' delimiters and the ''u'' Unicode modifier.
-ALTER TABLE employees ADD first_name, ADD last_name +
-ALTER TABLE invoices ADD note text, CHANGE idate invoice_date DATETIME +
-ALTER TABLE foo DROP bar, ADD bar2 INTEGER +
-</code>+
  
-==Using SQLite's native ALTER TABLE syntax== 
- 
-To use the native ALTER TABLE syntax of SQLite: 
 <code php> <code php>
-$plugin->getAdapter()->setUseNativeAlter(true);+$sqlite->queryAll( 
 +  'SELECT page, foo, bar FROM example WHERE REGEXP("^wiki:.*", page)
 +);
 </code> </code>
  
-The simple support in the sqlite plugin for the ALTER TABLE syntax was to overcome previous limited implementation in PHP's SQLite drivers.+=== RESOLVEPAGE ===
  
-=== GROUP_CONCAT ===+Resolves a given relative page against a given context page.
  
-This GROUP_CONCAT function returns a string result with the concatenated unique values from a group. +<code php
- +$sqlite->queryAll( 
-<code bnf+  'SELECT RESOLVEPAGE(page?), foo, bar FROM example', 
-GROUP_CONCAT(exprstr_val)+  [$context] 
 +);
 </code> </code>
  
-Example:+=== GROUP_CONCAT_DISTINCT ===
  
-<code sql> +This is an aggregate functions that works like the built-in [[https://www.sqlite.org/lang_aggfunc.html#group_concat|GROUP_CONCAT]] functionHowever it will ensure that only unique values are returned.
-SELECT student_name, GROUP_CONCAT(test_score, ','+
-FROM student +
-GROUP BY student_name; +
-</code> +
-First argument is just a [[http://sqlite.org/syntax/expr.html|expression]].  +
-With the second argument you specifies the string value that as the separator should be inserted between group values. To eliminate the separator altogether, specify empty string ''%%''%%''.+
  
-=== GETACCESSLEVEL === +Note: whenever possible you should use the native method. Unfortunately the native method does not support the DISTINCT keyword when using custom separator. Only if other options are not feasible, use this registered function.
-Returns the integer DokuWiki permission level of given resolved and cleaned pageid.+
  
-<code bnf> 
-GETACCESSLEVEL(str_val) 
-</code> 
  
-Example: 
 <code php> <code php>
-// comments for page with read access +$sqlite->queryAll( 
-$query = "SELECT A.pid as pidpage, titlecid +  'SELECT fooGROUP_CONCAT_DISTINCT(bar,' 'FROM example GROUP BY foo'
-            FROM entries Acomments B +  [$context] 
-           WHERE A.pid = B.pid +);
-             AND GETACCESSLEVEL(A.page>= ".AUTH_READ+
 </code> </code>
  
-Available permission levels are defined in [[xref>inc/auth.php]]. Hidden pages return also the AUTH_NONE permission level. 
  
-=== PAGEEXISTS === 
-Checks whether a page of given pageid exists. Returns 0 or 1. 
  
-<code bnf> 
-PAGEEXISTS(str_val) 
-</code> 
  
-Example: 
-<code sql> 
-SELECT value,pageid 
-  FROM tablename 
- WHERE PAGEEXISTS(pageid) = 1 
-</code>                        
  
 ==== Events ==== ==== Events ====
 +
 +The plugin will trigger the following events that your plugin can register for.
  
 === PLUGIN_SQLITE_DATABASE_UPGRADE === === PLUGIN_SQLITE_DATABASE_UPGRADE ===
  
-This event is triggered when any new migration (see above) is applied. This makes it possible to do more complex migrations that require more than just SQL. Also check that you are only handling your own database upgrade.+This event is triggered when any new migration (see above) is applied. This makes it possible to do more complex migrations that require more than just SQL.
  
 The provided data looks like this:  The provided data looks like this: 
  
 <code php> <code php>
-$data = array(+$data = 
 +    'database' => 'example', // the database name
     'from' => 5, // previous version     'from' => 5, // previous version
     'to' => 6, // version this migration migrates to     'to' => 6, // version this migration migrates to
-    'file' => /some/path/to/plugin/db/update0006.sql, // the associated update file +    'file' => /some/path/lib/plugin/example/db/update0006.sql, // the associated update file 
-    'sqlite' => $this // the initialized sqlite plugin instance for this database +    'sqlite' => [object] // deprecated instance of the old helper, do not use 
-);+    'adapter' => SQLiteDB // instance of the SQLiteDB class 
 +];
 </code> </code>
  
-You can verify the name of the database being upgraded like that:+ 
 +:!: When handling this event, be sure to check that you are only handling your own database upgrade. 
 + 
 + 
 +Here's an example to get you started: 
 <code php> <code php>
 public function handle_migrations(Doku_Event $event, $param) { public function handle_migrations(Doku_Event $event, $param) {
-    // replace PLUGINNAME with name of your plugin +    // replace example with name of your plugin 
-    if ($event->data['sqlite']->getAdapter()->getDbname() !== 'PLUGINNAME') {+    if ($event->data['database'] !== 'example') {
         return;         return;
     }     }
          
-    // ... your code+    // code to handle update to version 7 
 +    if($data['to'] === 7) { 
 +        $data['adapter']->exec('....'); 
 +        $event->result = true; 
 +    }
 } }
 </code> </code>
Line 391: Line 349:
 The ''BEFORE'' event is executed before the migration file is applied. The default action is to apply that file and increase the ''dbversion'' field in the ''opts'' table. The ''BEFORE'' event is executed before the migration file is applied. The default action is to apply that file and increase the ''dbversion'' field in the ''opts'' table.
  
-When an event prevents the default, the proper ''$event->result'' has to be set! When the result is true, the ''dbversion'' is increased and any additional migrations are run. When the result is false, the migration process is aborted.+When an event prevents the default, the proper ''%%$event->result%%'' has to be set! When the result is true, the ''dbversion'' is increased and any additional migrations are run. When the result is false, the migration process is aborted.
  
 The ''BEFORE'' event can optionally change the ''file'' attribute of the data to load a different file. The ''BEFORE'' event can optionally change the ''file'' attribute of the data to load a different file.
Line 397: Line 355:
 The ''AFTER'' event is only triggered after the migration file has been applied or the ''BEFORE'' event set a truish result. At the ''AFTER'' time, the ''dbversion'' has already been increased. The ''AFTER'' event is only triggered after the migration file has been applied or the ''BEFORE'' event set a truish result. At the ''AFTER'' time, the ''dbversion'' has already been increased.
  
 +[[codesearch>PLUGIN_SQLITE_DATABASE_UPGRADE|Code related to this event]] used in any files of plugins and templates
 +
 +=== PLUGIN_SQLITE_QUERY_SAVE ===
 +
 +
 +This event is triggered when a query is saved in the admin interface.
 +
 +You can prevent the saving in the BEFORE event, or modify the name or query.
 +
 +<code php>
 +$event->data = [
 +    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database.
 +    'upstream' => 'example', // the name of the selected database, for which the query is stored
 +    'name' => &$name, // The name the user picked for this query
 +    'query' => &$query, // The SQL for this query
 +]
 +</code>
 +
 +[[codesearch>PLUGIN_SQLITE_QUERY_SAVE|Code related to this event]] used in any files of plugins and templates
 +
 +=== PLUGIN_SQLITE_QUERY_DELETE ===
 +
 +This event is triggered when a query is deleted in the admin interface.
 +
 +You can prevent the deletion in the BEFORE event, or modify the name (this would delete a different query).
 +
 +<code php>
 +$event->data = [
 +    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database.
 +    'upstream' => 'example', // the name of the selected database, for which the query is stored
 +    'name' => &$name, // The name the user picked for this query
 +]
 +</code>
 +
 +[[codesearch>PLUGIN_SQLITE_QUERY_DELETE|Code related to this event]] used in any files of plugins and templates
 +
 +==== Slow Query Log ====
 +
 +When debug logging is enabled (see [[config:dontlog]] option), queries that take longer than 0.2 seconds will be logged as slow. Use this to optimize and debug your queries.
 +
 +==== Changes from earlier Releases ====
 +
 +In July 2023 a completely refactored version was released that introduced the ''SQLiteDB'' class in favor of an ''helper_plugin_sqlite''. The latter is still provided and internally uses the ''SQLiteDB'' class. This means the new plugin should be fully backwards compatible with older plugins.
 +
 +Discussions in the appropriate [[https://github.com/cosmocode/sqlite/pull/73|Pull Request]] might be instructive.
 +
 +However minor things might work slightly differently. Most important is that the previously custom registered GROUP_CONCAT method was renamed to GROUP_CONCAT_DISTINCT. Please see it's [[#GROUP_CONCAT_DISTINCT|documentation]] above and adjust your plugin if needed.
  
-===== BugsFeature Requests and Patches =====+Plugin developers are advised to update their plugin to make use of the newercleaner SQLiteDB interface. The helper is now deprecated.
  
-Please submit bugs and feature requests in the **[[https://github.com/cosmocode/sqlite/issues|issue tracker]]**. Patches should be sent unified diff format or as git patches. Or even better: fork the repository at github and send a merge request. 
plugin/sqlite.1524691934.txt.gz · Last modified: 2018-04-25 23:32 by andi

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki