plugin:sqlite
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
plugin:sqlite [2018-04-25 23:32] – version upped 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 : 2018-03-27 | + | 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 []; | ||
- | === query === | + | $result |
+ | 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-> | + | |
</ | </ | ||
- | === countChanges | + | ==== Database Setup and Migration ==== |
- | Count the number of records changed last time | + | |
- | Don't work after a SELECT statement | + | As you've seen above, the **constructor** of the '' |
- | * $res - a query result | + | 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 '' |
- | Returns integer count | + | The second parameter defines the directory where the database migration files are to be found. Most plugins use a '' |
- | <code php> | + | {{ https:// |
- | $count = $plugin-> | + | |
- | </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 | + | The number of the most recent version has to be stored |
- | * $res - a query result | + | The update mechanism will execute all SQL queries found in the migration files automatically. The execution of each update is wrapped in a transaction, |
- | Returns integer count | + | The sqlite plugin keeps track of the current version of the database using a table called '' |
- | <code php> | + | Note: sometimes migrations are more complex than what can be done with simple SQL queries. You can write your own migration |
- | $count = $plugin-> | + | |
- | </ | + | |
- | === res2arr === | ||
- | Returns a complete result set as array | + | ==== Public API ==== |
- | * $res - a query result | + | The following methods are exposed by the '' |
- | * $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 php> | + | |
- | $rows = $plugin-> | + | |
- | </code> | + | |
- | Returns default an associated array, with column names as indexes. If '' | + | > 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 | + | This method returns |
- | * $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-> | + | |
- | </ | + | |
- | === 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 ''?'' |
- | * $res - the result from a query | + | This method returns |
- | Returns false or zero indexed array | ||
<code php> | <code php> | ||
- | $row = $plugin->res_fetch_array($res) | + | $res = $sqlite-> |
+ | $row = $res->fetch(); | ||
+ | $res-> | ||
</ | </ | ||
- | === 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 |
- | + | ||
- | * $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(' |
+ | echo "new ID is $id"; | ||
</ | </ | ||
- | === res2single | + | === queryAll() |
- | Return the first value from the next row | + | Convenience method to execute |
- | + | ||
- | * $res - the result from a query | + | |
- | + | ||
- | Returns false or string result value | + | |
<code php> | <code php> | ||
- | $value = $plugin->res2single($res){ | + | $rows = $sqlite->queryAll(' |
+ | foreach($rows as $row) { | ||
+ | foreach($row as $col => $val) { | ||
+ | echo "$col: $val\n"; | ||
+ | } | ||
+ | echo " | ||
+ | } | ||
</ | </ | ||
- | === quote and join === | + | === queryRecord() |
- | + | ||
- | Join the given values and quote them for SQL insertion | + | |
- | * $vals - values | + | Convenience method |
- | * $sep - separator char | + | |
- | Returns joined and quoted string | ||
<code php> | <code php> | ||
- | $string | + | $row = $sqlite->queryRecord(' |
+ | foreach($row as $col => $val) { | ||
+ | echo "$col: $val\n"; | ||
+ | } | ||
</ | </ | ||
- | === quote string | + | === saveRecord() |
- | Escape | + | 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-> | + | |
- | </ | + | |
- | === Escape string === | + | Note: this method only provides '' |
- | Escape string for sql | + | |
<code php> | <code php> | ||
- | $string | + | $data = [ |
+ | ' | ||
+ | ' | ||
+ | ]; | ||
+ | $new = $sqlite->saveRecord(' | ||
+ | var_dump($new); | ||
</ | </ | ||
- | === close result set=== | ||
- | Closes the result set (and it's cursors) | + | === queryValue() === |
- | If you're doing SELECT queries inside a TRANSACTION, | + | Convenience method |
- | + | ||
- | Also required when not all rows of a result are fetched | + | |
- | + | ||
- | * $res - the result | + | |
<code php> | <code php> | ||
- | $succeed | + | $foo = $sqlite->queryValue(' |
+ | echo $foo; | ||
</ | </ | ||
- | === create_function | + | === queryKeyValueList() |
- | Registers a User Defined Function for use in SQL statements | + | |
- | * $function_name | + | Convenience method to query a key-value list from the database. The first value in each result row becomes |
- | * $callback - callback function to handle | + | |
- | * $num_args - Hint to the SQLite parser if the callback function accepts a predetermined number | + | |
- | Returns true on succes, false on failure. | ||
<code php> | <code php> | ||
- | $succeed | + | $res = $sqlite->queryKeyValueList(' |
+ | foreach($res as $key => $val) { | ||
+ | echo "$key: $val\n"; | ||
+ | } | ||
</ | </ | ||
- | ==== DB Schema Setup/ | + | ==== 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 | + | SQLite has the unique feature that PHP code can be registered as native SQL functions that then can be used within |
- | This is all handled within | + | Check the Functions class for details: [[https:// |
- | The number of the most recent version has to be stored in a file called | + | Your plugin can register it's own functions using the '' |
- | The update mechanism will wrap the execution of each update in a transaction, | ||
- | The sqlite plugin keeps track of the version a database is in currently using a table called '' | + | === GETACCESSLEVEL === |
- | ==== SQL Extensions ==== | + | Returns the integer DokuWiki permission level of a given pageid. |
- | The plugin provides a few additional features over the standard [[http:// | + | Note: this function will also return AUTH_NONE for hidden pages. |
- | === ALTER TABLE === | + | <code php> |
+ | $sqlite-> | ||
+ | ' | ||
+ | [AUTH_READ] | ||
+ | ); | ||
+ | </ | ||
- | The plugin supports a simplified '' | + | === PAGEEXISTS === |
- | + | ||
- | <code bnf> | + | |
- | ALTER TABLE tbl_name alter_specification [, alter_specification] ... | + | |
- | alter_specification: | + | Checks if the given pagename exists. Returns '' |
- | ADD column_definition | + | |
- | | DROP column_definition | + | |
- | | CHANGE old_col_name column_definition | + | |
- | column_definition: | + | <code php> |
- | same as for create table statements | + | $sqlite-> |
+ | ' | ||
+ | ); | ||
</ | </ | ||
- | Documentation [[http:// | ||
- | Examples: | + | === REGEXP === |
- | <code sql> | + | Matches a value against a given regular expression. The expression is used with '' |
- | 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' | ||
- | |||
- | To use the native ALTER TABLE syntax of SQLite: | ||
<code php> | <code php> | ||
- | $plugin->getAdapter()-> | + | $sqlite->queryAll( |
+ | ' | ||
+ | ); | ||
</ | </ | ||
- | 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. | + | < |
- | + | $sqlite-> | |
- | < | + | ' |
- | GROUP_CONCAT(expr, str_val) | + | [$context] |
+ | ); | ||
</ | </ | ||
- | Example: | + | === GROUP_CONCAT_DISTINCT === |
- | <code sql> | + | This is an aggregate functions that works like the built-in |
- | SELECT student_name, | + | |
- | FROM student | + | |
- | GROUP BY student_name; | + | |
- | </ | + | |
- | First argument | + | |
- | 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 a custom separator. Only if other options are not feasible, use this registered function. |
- | Returns | + | |
- | <code bnf> | ||
- | GETACCESSLEVEL(str_val) | ||
- | </ | ||
- | Example: | ||
<code php> | <code php> | ||
- | // comments for page with read access | + | $sqlite-> |
- | $query = "SELECT | + | 'SELECT |
- | | + | |
- | WHERE A.pid = B.pid | + | ); |
- | AND GETACCESSLEVEL(A.page) >= " | + | |
</ | </ | ||
- | Available permission levels are defined in [[xref> | ||
- | === PAGEEXISTS === | ||
- | Checks whether a page of given pageid exists. Returns 0 or 1. | ||
- | <code bnf> | ||
- | PAGEEXISTS(str_val) | ||
- | </ | ||
- | Example: | ||
- | <code sql> | ||
- | SELECT value, | ||
- | FROM tablename | ||
- | WHERE PAGEEXISTS(pageid) = 1 | ||
- | </ | ||
==== 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 = [ |
+ | ' | ||
' | ' | ||
' | ' | ||
- | ' | + | ' |
- | ' | + | ' |
- | ); | + | ' |
+ | ]; | ||
</ | </ | ||
- | 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 | + | // replace |
- | if ($event-> | + | if ($event-> |
return; | return; | ||
} | } | ||
| | ||
- | // ... your code | + | // code to handle update to version 7 |
+ | if($data[' | ||
+ | $data[' | ||
+ | $event-> | ||
+ | } | ||
} | } | ||
</ | </ | ||
Line 391: | Line 349: | ||
The '' | The '' | ||
- | When an event prevents the default, the proper '' | + | When an event prevents the default, the proper '' |
The '' | The '' | ||
Line 397: | Line 355: | ||
The '' | The '' | ||
+ | [[codesearch> | ||
+ | |||
+ | === 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-> | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ] | ||
+ | </ | ||
+ | |||
+ | [[codesearch> | ||
+ | |||
+ | === 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-> | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ] | ||
+ | </ | ||
+ | |||
+ | [[codesearch> | ||
+ | |||
+ | ==== Slow Query Log ==== | ||
+ | |||
+ | When debug logging is enabled (see [[config: | ||
+ | |||
+ | ==== Changes from earlier Releases ==== | ||
+ | |||
+ | 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.1524691934.txt.gz · Last modified: 2018-04-25 23:32 by andi