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 [2016-11-15 12:29] 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 : 2016-11-15 +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 
 +     * @return array 
 +     */ 
 +    public function getAllBazForFoo($foo) 
 +    { 
 +        $db = $this->getDB(); 
 +        if (!$db) return []; 
 + 
 +        $result = $db->queryAll('SELECT id, meta, baz FROM example WHERE foo = ?'[$foo])
 +        return $result; 
 +    } 
 + 
 +}
 </code> </code>
  
-=== query ===+==== Database Setup and Migration ====
  
-Execute a query with the given parameters.+As you've seen above, the **constructor** of the ''SQLiteDB'' class requires two parameters. A **database name** and a **migration directory**.
  
-Takes care of escaping+The database name is the file name your SQLite database. Unless you have a 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.
  
-  * $sql - the statement +The second parameter defines the directory where the database migration files are to be foundMost plugins use a ''db'' directory in their plugin structureUse the ''DOKU_PLUGIN'' or ''__DIR__'' constants to create the appropriate absolute path.
-  * arguments... +
-    * Arguments can also be given as a single array+
  
-Returns false or result object +{{ https://i.imgur.com/W4Xl6TY.png|Migration directory example}}
-<code php> +
-$res = $plugin->query($sql[,arg1[,arg2[...]]]); +
-</code>+
  
-=== countChanges === +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 changed last time+
  
-Don't work after SELECT statement+Each file correspondents to 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.
  
-  * $res - query result+The number of the most recent version has to be stored in 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''.
  
-Returns integer count+The update mechanism will execute all SQL queries found in the migration files automatically. The execution of each update is wrapped in a transaction, you should not to do that yourself. If an update fails, the transaction is rolled back and the update is aborted.
  
-<code php> +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!
-$count = $plugin->countChanges($res); +
-</code>+
  
-=== res2count ===+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 the number of records in result 
  
-This function is really inperformant in PDO and should be avoided!+==== 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.
  
-Returns integer count+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> +Always use the provided parameter replacement mechanisms when passing user provided data into queries to avoid SQL injection attacks! See [[#query()]] method.
-$count = $plugin->res2count($res)+
-</code>+
  
-=== res2arr ===+=== getPdo() ===
  
-Returns a complete result set as 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 - a query result +This is useful to manage transactions or register your own functions, for example. 
-  * $assoc - associated or numbered index array+ 
 + 
 +=== query() === 
 + 
 +Basic query execution. 
 + 
 +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. 
 + 
 +This method returns a [[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 array with result 
 <code php> <code php>
-$rows = $plugin->res2arr($res[,$assoc]){+$res = $sqlite->query('SELECT foo FROM example WHERE baz = ?'[$baz])
 +$row = $res->fetch(); 
 +$res->closeCursor();
 </code> </code>
  
-Returns default an associated array, with column names as indexes. If ''$assoc false'' it returns an array indexed by column number (0-indexed).+=== exec() ===
  
-=== res2row ===+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.
  
-Return the next row from a given result set as associative array+<code php> 
 +$id = $sqlite->exec('INSERT INTO example (bar) VALUES (?)', [$bar]); 
 +echo "new ID is $id"; 
 +</code>
  
-  * $res - the result from a query +=== queryAll() ===
-  * //$numrow - wanted row number of result set// (Not supported by current backends)+
  
-Returns false or array with result+Convenience method to execute a query and return all rows as associative arrays. Keep memory limits in mind when querying large data sets!
  
 <code php> <code php>
-$row = $plugin->res2row($res[, $rownum=0])+$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>
  
-=== res_fetch_array ===+=== queryRecord() ===
  
-Fetch the next row as zero indexed array+Convenience method to execute a query and return a single row only.
  
-  * $res - the result from a query 
  
-Returns false or zero indexed array 
 <code php> <code php>
-$row = $plugin->res_fetch_array($res)+$row = $sqlite->queryRecord('SELECT * FROM example WHERE bar = ?', [$bar])
 +foreach($row as $col => $val) { 
 +    echo "$col: $val\n"; 
 +}
 </code> </code>
  
-=== res_fetch_assoc ===+=== saveRecord() ===
  
-Fetch the next row as assocative array+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.
  
-  * $res - the result from query+If the given data violates constraint, it is either ignored or the existing data is replaced.
  
-Returns false or array with column names as index+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()]].
  
 <code php> <code php>
-$row = $plugin->res_fetch_assoc($res)+$data 
 +    'foo' => 'Florb', 
 +    'baz' => 12932 
 +]; 
 +$new = $sqlite->saveRecord('example', $data); 
 +var_dump($new);
 </code> </code>
  
-=== res2single === 
  
-Return the first value from the next row+=== queryValue() ===
  
-  * $res - the result from a query+Convenience method to query a single value from the database. It will return the first value in the first row of the result set.
  
-Returns false or string result value+<code php> 
 +$foo = $sqlite->queryValue('SELECT foo FROM example WHERE bar = ?', [$bar]); 
 +echo $foo; 
 +</code> 
 + 
 +=== queryKeyValueList() === 
 + 
 +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 value. You need to take care of key uniqueness yourself, otherwise later values overwrite earlier ones.
  
 <code php> <code php>
-$value = $plugin->res2single($res){+$res = $sqlite->queryKeyValueList('SELECT foo,baz FROM example WHERE bar = ?', [$bar]); 
 +foreach($res as $key => $val) { 
 +    echo "$key: $val\n"; 
 +}
 </code> </code>
  
-=== quote and join ===+==== SQL Extensions ====
  
-Join the given values and quote them for SQL insertion+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 a few custom functions described below.
  
-  * $vals - values to join +Check the Functions class for details: [[https://github.com/cosmocode/sqlite/blob/master/Functions.php|Functions.php]].
-  * $sep - separator char+
  
-Returns joined and quoted string+Your plugin can register it's own functions using the ''%%getPDO()->sqliteCreateFunction()%%'' and ''%%getPDO()->sqliteCreateAggregate()%%''.
  
-<code php> 
-$string = $plugin->quote_and_join($vals,$sep=','); 
-</code> 
  
-=== quote string ===+=== GETACCESSLEVEL ===
  
-Escape the given string and surround it with quotes+Returns the integer DokuWiki permission level of a given pageid. 
 + 
 +Note: this function will also return AUTH_NONE for hidden pages.
  
 <code php> <code php>
-$string = $plugin->quote_string($string);+$sqlite->queryAll( 
 +  'SELECT page, foo, bar FROM example WHERE GETACCESSLEVEL(page) > ?', 
 +  [AUTH_READ] 
 +);
 </code> </code>
  
-=== Escape string === +=== PAGEEXISTS === 
-Escape string for sql+ 
 +Checks if the given pagename exists. Returns ''1'' for existing pages, ''0'' otherwise.
  
 <code php> <code php>
-$string = $plugin->escape_string($string);+$sqlite->queryAll( 
 +  'SELECT page, foo, bar FROM example WHERE PAGEEXISTS(page) = 1' 
 +);
 </code> </code>
  
-=== close result set===+=== REGEXP ===
  
-Closes the result set (and it's cursors)+Matches a value against a given regular expression. The expression is used with ''/'' delimiters and the ''u'Unicode modifier.
  
-If you're doing SELECT queries inside a TRANSACTIONbe sure to call this function on all your results setsbefore COMMITing the transaction.+<code php> 
 +$sqlite->queryAll( 
 +  'SELECT pagefoobar FROM example WHERE REGEXP("^wiki:.*", page)' 
 +); 
 +</code>
  
-Also required when not all rows of a result are fetched+=== RESOLVEPAGE ===
  
-  * $res - the result from query+Resolves given relative page against a given context page.
  
 <code php> <code php>
-$succeed = $plugin->res_close($res);+$sqlite->queryAll( 
 +  'SELECT RESOLVEPAGE(page, ?), foo, bar FROM example', 
 +  [$context] 
 +);
 </code> </code>
  
-=== create_function ==== +=== GROUP_CONCAT_DISTINCT === 
-Registers User Defined Function for use in SQL statements+ 
 +This is an aggregate functions that works like the built-in [[https://www.sqlite.org/lang_aggfunc.html#group_concat|GROUP_CONCAT]] function. However it will ensure that only unique values are returned. 
 + 
 +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.
  
-   * $function_name - name of the function used in SQL statements. 
-   * $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)+$sqlite->queryAll( 
 +  'SELECT fooGROUP_CONCAT_DISTINCT(bar,' ') FROM example GROUP BY foo', 
 +  [$context] 
 +);
 </code> </code>
  
-==== DB Schema Setup/Update Mechanism ==== 
  
-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 a simple mechanism to do so. 
  
-This is all handled within the ''init()'' function. The second parameter has to point to a directory where your SQL files are located. 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. 
  
-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''. 
  
-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.+==== Events ====
  
-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!+The plugin will trigger the following events that your plugin can register for.
  
-==== SQL Extensions ====+=== PLUGIN_SQLITE_DATABASE_UPGRADE ===
  
-The plugin provides a few additional features over the standard [[http://sqlite.org/lang.html|SQLite syntax]].+This event is triggered when any new migration (see above) is appliedThis makes it possible to do more complex migrations that require more than just SQL.
  
-=== ALTER TABLE ===+The provided data looks like this: 
  
-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 droppingrecreating and refilling the original tableWhen used outside the update mechanismit is recommended to wrap the call in a transaction.+<code php> 
 +$data = [ 
 +    'database=> 'example', // the database name 
 +    'from' => 5, // previous version 
 +    'to' => 6// version this migration migrates to 
 +    'file' => /some/path/lib/plugin/example/db/update0006.sql, // the associated update file 
 +    'sqlite' => [object] // deprecated instance of the old helperdo not use 
 +    'adapter' => SQLiteDB // instance of the SQLiteDB class 
 +]; 
 +</code>
  
-<code bnf> 
-ALTER TABLE tbl_name alter_specification [, alter_specification] ... 
  
-alter_specification: +:!: When handling this event, be sure to check that you are only handling your own database upgrade.
-    ADD column_definition +
-  | DROP column_definition +
-  | CHANGE old_col_name column_definition+
  
-column_definition: 
-    same as for create table statements 
-</code> 
-Documentation [[http://sqlite.org/lang_createtable.html|create table]] 
  
-Examples:+Here's an example to get you started:
  
-<code sql+<code php
-ALTER TABLE employees ADD first_nameADD last_name +public function handle_migrations(Doku_Event $event$param) { 
-ALTER TABLE invoices ADD note text, CHANGE idate invoice_date DATETIME +    // replace example with name of your plugin 
-ALTER TABLE foo DROP bar, ADD bar2 INTEGER+    if ($event->data['database'] !== 'example') { 
 +        return; 
 +    } 
 +     
 +    // code to handle update to version 7 
 +    if($data['to'] === 7) { 
 +        $data['adapter']->exec('....'); 
 +        $event->result = true; 
 +    } 
 +}
 </code> </code>
  
-==Using SQLite's native ALTER TABLE syntax== 
  
-To use the native ALTER TABLE syntax of SQLite: +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.
-<code php> +
-$plugin->getAdapter()->setUseNativeAlter(true); +
-</code>+
  
-The simple support in the sqlite plugin for the ALTER TABLE syntax was to overcome previous limited implementation in PHP's SQLite drivers.+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.
  
-=== GROUP_CONCAT ===+The ''BEFORE'' event can optionally change the ''file'' attribute of the data to load a different file.
  
-This GROUP_CONCAT function returns string result with the concatenated unique values from a group.+The ''AFTER'' event is only triggered after the migration file has been applied or the ''BEFORE'' event set truish result. At the ''AFTER'' time, the ''dbversion'' has already been increased.
  
-<code bnf> +[[codesearch>PLUGIN_SQLITE_DATABASE_UPGRADE|Code related to this event]] used in any files of plugins and templates
-GROUP_CONCAT(expr, str_val) +
-</code>+
  
-Example:+=== PLUGIN_SQLITE_QUERY_SAVE ===
  
-<code sql> 
-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 === +This event is triggered when query is saved in the admin interface.
-Returns the integer DokuWiki permission level of given resolved and cleaned pageid.+
  
-<code bnf> +You can prevent the saving in the BEFORE event, or modify the name or query.
-GETACCESSLEVEL(str_val) +
-</code>+
  
-Example: 
 <code php> <code php>
-// comments for page with read access +$event->data = [ 
-$query "SELECT A.pid as pidpage, titlecid +    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database. 
-            FROM entries Acomments B +    'upstream' => 'example'// the name of the selected databasefor which the query is stored 
-           WHERE A.pid B.pid +    'name' => &$name// The name the user picked for this query 
-             AND GETACCESSLEVEL(A.page) >= ".AUTH_READ+    'query' => &$query, // The SQL for this query 
 +]
 </code> </code>
  
-Available permission levels are defined in [[xref>inc/auth.php]]. Hidden pages return also the AUTH_NONE permission level.+[[codesearch>PLUGIN_SQLITE_QUERY_SAVE|Code related to this event]] used in any files of plugins and templates
  
-=== PAGEEXISTS === +=== PLUGIN_SQLITE_QUERY_DELETE ===
-Checks whether a page of given pageid exists. Returns 0 or 1.+
  
-<code bnf> +This event is triggered when a query is deleted in the admin interface.
-PAGEEXISTS(str_val) +
-</code>+
  
-Example: +You can prevent the deletion in the BEFORE eventor modify the name (this would delete different query).
-<code sql> +
-SELECT value,pageid +
-  FROM tablename +
- WHERE PAGEEXISTS(pageid) = 1 +
-</code>                        +
- +
-==== Events ==== +
- +
-=== PLUGIN_SQLITE_DATABASE_UPGRADE === +
- +
-This event is triggered when new migration (see aboveis appliedThis makes it possible to do more complex migrations that require more than just SQL. +
- +
-The provided data looks like this: +
  
 <code php> <code php>
-$data = array( +$event->data = [ 
-    'from' => 5, // previous version +    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database. 
-    'to' => 6, // version this migration migrates to +    'upstream' => 'example', // the name of the selected database, for which the query is stored 
-    'file=> /some/path/to/plugin/db/update0006.sql, // the associated update file +    'name' => &$name, // The name the user picked for this query 
-    'sqlite' => $this // the initialized sqlite plugin instance for this database +]
-);+
 </code> </code>
  
 +[[codesearch>PLUGIN_SQLITE_QUERY_DELETE|Code related to this event]] used in any files of plugins and templates
  
-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.+==== Slow Query Log ====
  
-When an event prevents the defaultthe 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 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.
  
-The ''BEFORE'' event can optionally change the ''file'' attribute of the data to load a different file.+==== Changes from earlier Releases ====
  
-The ''AFTER'' event is only triggered after the migration file has been applied or the ''BEFORE'' event set a truish resultAt the ''AFTER'' time, the ''dbversion'' has already been increased.+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.1479209388.txt.gz · Last modified: 2016-11-15 12:29 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