plugin:sqlite
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
plugin:sqlite [2016-11-15 12:29] – andi | plugin: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 Belle, Weatherwax, Binky, Ponder Stibbons, Hrun, Detritus, Elenor Of Tsort | + | compatible : Hogfather, Igor, Jack Jackrum, Kaos |
depends | depends | ||
conflicts | conflicts | ||
Line 18: | Line 18: | ||
---- | ---- | ||
- | [[http:// | + | [[https:// |
+ | 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. | + | This plugin needs SQLite |
- | **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 " | ||
- | * Pick a sqlite2 data base | ||
- | * Click the " | ||
===== Download and Installation ===== | ===== Download and Installation ===== | ||
- | Install | + | Search and install |
=== Changes === | === Changes === | ||
- | |||
- | [[https:// | ||
{{rss> | {{rss> | ||
Line 44: | Line 37: | ||
===== Admin Interface ===== | ===== Admin Interface ===== | ||
- | {{ http:// | ||
- | The plugin comes with a simple admin interface | + | The plugin comes with a simple admin interface |
- | Available databases ('' | + | |
+ | * run predefined queries | ||
+ | * export | ||
+ | | ||
- | ===== Developer Information ===== | + | To use any of its functionalities, |
- | 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 | + | :!: Be sure you know what you're doing. This interface provides no safety nets against deleting or corrupting |
- | <code php> | + | {{ https://i.imgur.com/VTpa13C.png | Admin Interface Screenshot }} |
- | // load the helper plugin | + | |
- | /** @var helper_plugin_sqlite $sqlite */ | + | |
- | $sqlite = plugin_load(' | + | |
- | if(!$sqlite){ | + | |
- | msg(' | + | |
- | return; | + | |
- | } | + | |
- | // initialize the database connection | + | |
- | if(!$sqlite-> | + | |
- | return; | + | |
- | } | + | |
- | // use the plugin | + | |
- | $res = $sqlite-> | + | |
- | $arr = $sqlite-> | + | |
- | print_r($arr); | + | |
- | </ | + | |
- | ==== Functions ==== | ||
- | === init === | + | ===== Developer Documentation ===== |
- | Initializes and opens the database. | + | This plugin provides you with all the mechanisms needed to use a sqlite |
- | * $dbname | + | It is recommended to lazy load the class if needed, for example in a [[devel: |
- | * $updatedir is a path where update SQL files are located | + | |
- | 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-> | + | <?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) | + | { |
+ | | ||
- | Returns false or result object | + | /** |
+ | * Get SQLiteDB instance | ||
+ | * | ||
+ | * @return SQLiteDB|null | ||
+ | */ | ||
+ | public function getDB() | ||
+ | { | ||
+ | if ($this-> | ||
+ | try { | ||
+ | $this-> | ||
+ | } catch (\Exception $exception) { | ||
+ | if (defined(' | ||
+ | ErrorHandler:: | ||
+ | msg(' | ||
+ | return null; | ||
+ | } | ||
+ | } | ||
+ | return $this-> | ||
+ | } | ||
- | <code php> | + | /** |
- | $res = $plugin->storeEntry($table, $entry) | + | * @param string |
+ | * @return array | ||
+ | */ | ||
+ | public function getAllBazForFoo($foo) | ||
+ | { | ||
+ | $db = $this->getDB(); | ||
+ | if (!$db) return []; | ||
+ | |||
+ | $result = $db-> | ||
+ | return $result; | ||
+ | } | ||
+ | |||
+ | } | ||
</ | </ | ||
- | === query === | + | ==== Database Setup and Migration ==== |
- | Execute a query with the given parameters. | + | As you've seen above, |
- | 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 '' |
- | * $sql - the statement | + | The second parameter defines |
- | * 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-> | + | |
- | </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 |
- | Count the number | + | |
- | Don't work after a SELECT statement | + | 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. |
- | * $res - a query result | + | The number of the most recent version has to be stored in a file called '' |
- | 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, |
- | <code php> | + | The sqlite |
- | $count = $plugin-> | + | |
- | </ | + | |
- | === 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 [[# |
- | 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 '' |
- | 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:// |
- | <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-> | + | |
- | </ | + | |
- | === res2arr | + | === getPdo() |
- | Returns a complete result set as array | + | This method returns the underlying [[https:// |
- | * $res - a query result | + | This is useful to manage transactions or register your own functions, for example. |
- | * $assoc - associated | + | |
+ | |||
+ | === query() === | ||
+ | |||
+ | Basic query execution. | ||
+ | |||
+ | Placeholder in the query will be replaced by the given replacements. You can either use positional ''?'' | ||
+ | |||
+ | This method returns a [[https:// | ||
- | Returns false or array with result | ||
<code php> | <code php> | ||
- | $rows = $plugin->res2arr($res[,$assoc]){ | + | $res = $sqlite->query(' |
+ | $row = $res-> | ||
+ | $res-> | ||
</ | </ | ||
- | Returns default an associated array, with column names as indexes. If '' | + | === 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-> | ||
+ | echo "new ID is $id"; | ||
+ | </ | ||
- | * $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(' |
+ | foreach($rows as $row) { | ||
+ | foreach($row as $col => $val) { | ||
+ | echo "$col: $val\n"; | ||
+ | } | ||
+ | echo " | ||
+ | } | ||
</ | </ | ||
- | === res_fetch_array | + | === queryRecord() |
- | Fetch the next row as zero indexed array | + | Convenience method to execute a query and return a single |
- | * $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(' |
+ | foreach($row as $col => $val) { | ||
+ | echo "$col: $val\n"; | ||
+ | } | ||
</ | </ | ||
- | === res_fetch_assoc | + | === saveRecord() |
- | Fetch the next row as assocative | + | 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 a query | + | If the given data violates |
- | Returns false or array with column names as index | + | Note: this method only provides '' |
<code php> | <code php> | ||
- | $row = $plugin->res_fetch_assoc($res) | + | $data = [ |
+ | ' | ||
+ | ' | ||
+ | ]; | ||
+ | $new = $sqlite->saveRecord(' | ||
+ | var_dump($new); | ||
</ | </ | ||
- | === res2single === | ||
- | Return the first value from the next row | + | === queryValue() === |
- | * $res - the result | + | Convenience method to query a single value from the database. It will return the first value in the first row of the result |
- | Returns false or string | + | <code php> |
+ | $foo = $sqlite-> | ||
+ | echo $foo; | ||
+ | </ | ||
+ | |||
+ | === queryKeyValueList() === | ||
+ | |||
+ | Convenience method to query a key-value list from the database. The first value in each result | ||
<code php> | <code php> | ||
- | $value = $plugin->res2single($res){ | + | $res = $sqlite->queryKeyValueList(' |
+ | foreach($res as $key => $val) { | ||
+ | echo "$key: $val\n"; | ||
+ | } | ||
</ | </ | ||
- | === 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 |
- | * $vals - values to join | + | Check the Functions class for details: [[https:// |
- | * $sep - separator char | + | |
- | Returns joined | + | Your plugin can register it's own functions using the '' |
- | <code php> | ||
- | $string = $plugin-> | ||
- | </ | ||
- | === quote string | + | === GETACCESSLEVEL |
- | Escape | + | Returns |
+ | |||
+ | Note: this function will also return AUTH_NONE for hidden pages. | ||
<code php> | <code php> | ||
- | $string = $plugin->quote_string($string); | + | $sqlite->queryAll( |
+ | ' | ||
+ | [AUTH_READ] | ||
+ | ); | ||
</ | </ | ||
- | === Escape string | + | === PAGEEXISTS |
- | Escape string | + | |
+ | Checks if the given pagename exists. Returns '' | ||
<code php> | <code php> | ||
- | $string = $plugin->escape_string($string); | + | $sqlite->queryAll( |
+ | ' | ||
+ | ); | ||
</ | </ | ||
- | === 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 ''/'' |
- | If you're doing SELECT | + | <code php> |
+ | $sqlite-> | ||
+ | | ||
+ | ); | ||
+ | </ | ||
- | Also required when not all rows of a result are fetched | + | === RESOLVEPAGE === |
- | * $res - the result from a query | + | Resolves |
<code php> | <code php> | ||
- | $succeed = $plugin->res_close($res); | + | $sqlite->queryAll( |
+ | ' | ||
+ | [$context] | ||
+ | ); | ||
</ | </ | ||
- | === create_function ==== | + | === GROUP_CONCAT_DISTINCT |
- | Registers | + | |
+ | This is an aggregate functions that works like the built-in [[https:// | ||
+ | |||
+ | Note: whenever possible you should use the native method. Unfortunately the native method does not support the DISTINCT keyword when using a custom separator. Only if other options are not feasible, | ||
- | * $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( |
+ | ' | ||
+ | [$context] | ||
+ | ); | ||
</ | </ | ||
- | ==== DB Schema Setup/ | ||
- | 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 '' | ||
- | The number of the most recent version has to be stored in a file called '' | ||
- | The update mechanism will wrap the execution of each update in a transaction, | + | ==== Events ==== |
- | The sqlite | + | The plugin |
- | ==== SQL Extensions ==== | + | === PLUGIN_SQLITE_DATABASE_UPGRADE |
- | The plugin provides a few additional features over the standard [[http:// | + | 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. |
- | === ALTER TABLE === | + | The provided data looks like this: |
- | The plugin supports a simplified | + | <code php> |
+ | $data = [ | ||
+ | | ||
+ | ' | ||
+ | 'to' => 6, // version this migration migrates to | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ]; | ||
+ | </ | ||
- | <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 | ||
- | </ | ||
- | Documentation [[http:// | ||
- | Examples: | + | Here's an example to get you started: |
- | < | + | < |
- | ALTER TABLE employees ADD first_name, ADD 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-> |
+ | return; | ||
+ | } | ||
+ | |||
+ | // code to handle update to version 7 | ||
+ | if($data[' | ||
+ | $data[' | ||
+ | $event-> | ||
+ | } | ||
+ | } | ||
</ | </ | ||
- | ==Using SQLite' | ||
- | To use the native ALTER TABLE syntax of SQLite: | + | The '' |
- | <code php> | + | |
- | $plugin-> | + | |
- | </ | + | |
- | 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 |
- | === GROUP_CONCAT === | + | The '' |
- | This GROUP_CONCAT function returns | + | The '' |
- | <code bnf> | + | [[codesearch>PLUGIN_SQLITE_DATABASE_UPGRADE|Code related to this event]] used in any files of plugins and templates |
- | GROUP_CONCAT(expr, | + | |
- | </code> | + | |
- | Example: | + | === PLUGIN_SQLITE_QUERY_SAVE === |
- | <code sql> | ||
- | SELECT student_name, | ||
- | FROM student | ||
- | GROUP BY student_name; | ||
- | </ | ||
- | First argument is just a [[http:// | ||
- | 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 a query is saved in the admin interface. |
- | Returns the integer DokuWiki permission level of a 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) | + | |
- | </ | + | |
- | Example: | ||
<code php> | <code php> | ||
- | // comments | + | $event-> |
- | $query | + | ' |
- | FROM entries A, comments B | + | ' |
- | WHERE A.pid = B.pid | + | ' |
- | AND GETACCESSLEVEL(A.page) | + | ' |
+ | ] | ||
</ | </ | ||
- | Available permission levels are defined in [[xref>inc/ | + | [[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) | + | |
- | </ | + | |
- | Example: | + | You can prevent the deletion in the BEFORE event, or modify the name (this would delete |
- | <code sql> | + | |
- | SELECT value,pageid | + | |
- | FROM tablename | + | |
- | WHERE PAGEEXISTS(pageid) = 1 | + | |
- | </ | + | |
- | + | ||
- | ==== Events ==== | + | |
- | + | ||
- | === PLUGIN_SQLITE_DATABASE_UPGRADE === | + | |
- | + | ||
- | This event is triggered when a 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: | + | |
<code php> | <code php> | ||
- | $data = array( | + | $event->data = [ |
- | 'from' => 5, // previous version | + | 'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the '' |
- | 'to' => 6, // version this migration migrates to | + | 'upstream' => 'example', // the name of the selected database, for which the query is stored |
- | | + | 'name' => &$name, // The name the user picked |
- | 'sqlite' => $this // the initialized sqlite plugin instance | + | ] |
- | ); | + | |
</ | </ | ||
+ | [[codesearch> | ||
- | The '' | + | ==== Slow Query Log ==== |
- | When an event prevents the default, the proper '' | + | When debug logging is enabled (see [[config: |
- | The '' | + | ==== Changes from earlier Releases ==== |
- | The '' | + | In July 2023 a completely refactored version was released that introduced the '' |
+ | |||
+ | Discussions in the appropriate [[https:// | ||
+ | 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 [[# | ||
- | ===== Bugs, Feature Requests and Patches ===== | + | Plugin developers are advised to update their plugin to make use of the newer, cleaner SQLiteDB interface. The helper is now deprecated. |
- | Please submit bugs and feature requests in the **[[https:// |
plugin/sqlite.1479209388.txt.gz · Last modified: 2016-11-15 12:29 by andi