This is an old revision of the document!
Table of Contents
sqlite Plugin
Compatible with DokuWiki
Adora Belle, Weatherwax, Binky, Ponder Stibbons, Hrun
This extension has not been updated in over 2 years. It may no longer be maintained or supported and may have compatibility issues.
Needed for addressbook, aichat, approve, bez, blogtng, combo, data, data-au, datagraph, dataloop, datatemplate, davcal, davcard, do, dwcommits, extendpage, ireadit, issuelinks, judge, labeled, notification, randomtables, rating, sql2wiki, starred, struct, structnotification, structtasks, swarmwebhook, tagging, telleveryone, timetrack, top, watchcycle, webdavclient
Requirements
This plugin needs SQLite support in your PHP installation. It can work either with the sqlite extension to access and create SQLite Version 2 databases or the the pdo-sqlite extension for accessing and creating SQLite Version 3 databases. If both extensions are available, pdo-sqlite will be used.
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
Install the plugin using the Plugin Manager and the download URL above, which points to latest version of the plugin. Refer to Plugins on how to install plugins manually.
Changes
- Version upped (2024-03-05 23:51)
- Merge pull request #88 from cosmocode/bot/autofix (2024-03-05 08:57)
- 🤖 Automatic code style fixes (2024-03-05 08:40)
- Merge pull request #90 from dokuwiki-translate/lang_update_832_170962… (2024-03-05 08:39)
- translation update (2024-03-05 08:01)
- Version upped (2024-01-25 23:51)
- Merge pull request #89 from cosmocode/disabled (2024-01-25 15:44)
- Throw an exception if contructor gets called on a disabled plugin (2024-01-25 15:40)
Admin Interface
The plugin comes with a simple admin interface where you can run your own SQL queries against any of the available databases.
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.
Developer Information
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.
// load the helper plugin /** @var helper_plugin_sqlite $sqlite */ $sqlite = plugin_load('helper', 'sqlite'); if(!$sqlite){ msg('This plugin requires the sqlite plugin. Please 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);
Functions
init
Initializes and opens the database. Needs to be called right after loading this helper plugin
- $dbname is the name of the database.
- $updatedir is a path where update SQL files are located (see below)
$plugin->init($dbname,$updatedir);
query
Execute a query with the given parameters.
Takes care of escaping
- $sql - the statement
- arguments…
- Arguments can also be given as a single array
$plugin->query($sql[,arg1[,arg2[...]]]);
res2arr
Returns a complete result set as array
- $res - a query result
$plugin->res2arr($res){
res2row
Return the wanted row from a given result set as associative array
- $res - the result from a query
- $numrow - number of results
$plugin->res2row($res,$rownum=0){
quote and join
Join the given values and quote them for SQL insertion
- $vals - values to join
- $sep - separator char
$plugin->quote_and_join($vals,$sep=',');
quote string
Run sqlite_escape_string on the given string and surround it with quotes
$plugin->quote_string($string);
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 zeropadded 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.
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!
SQL Extensions
The plugin provides a few additional features over the standard SQLite syntax.
ALTER TABLE
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.
ALTER TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD column_definition | DROP column_definition | CHANGE old_col_name column_definition column_definition: same as for create table statements
Examples:
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
GROUP_CONCAT
The GROUP_CONCAT function is a copy of the same function as defined in MySQL. add more info.
Bugs, Feature Requests and Patches
Please submit bugs and feature requests in the 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.