Learn about DokuWiki
Advanced Use
Corporate Use
Our Community
Follow us on Facebook, Twitter and other social networks.
Learn about DokuWiki
Advanced Use
Corporate Use
Our Community
Follow us on Facebook, Twitter and other social networks.
Compatible with DokuWiki
Needed for addressbook, approve, bez, blogtng, combo, data, data-au, datagraph, dataloop, datatemplate, davcal, davcard, do, dwcommits, extendpage, ireadit, issuelinks, judge, labeled, notification, rating, starred, struct, structnotification, swarmwebhook, tagging, telleveryone, timetrack, top, watchcycle, webdavclient
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 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:
Search and install the plugin using the Extension Manager. Refer to Plugins on how to install plugins manually.
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.
Also you can list the tables and the indexes, further you can export and import the entire database. When importing, from the existing database a backup should be made as e.g. dbname.copy<number>.sqlite3
.
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);
Initializes and opens the database. Needs to be called right after loading this helper plugin
Returns boolean whether it succeed.
$succeed = $plugin->init($dbname,$updatedir);
Convenience function to run an INSERT OR REPLACE operation
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.
Returns false or result object
$res = $plugin->storeEntry($table, $entry)
Execute a query with the given parameters.
Takes care of escaping
Returns false or result object
$res = $plugin->query($sql[,arg1[,arg2[...]]]);
Count the number of records changed last time
Don't work after a SELECT statement
Returns integer count
$count = $plugin->countChanges($res);
Count the number of records in result
This function is really inperformant in PDO and should be avoided!
Returns integer count
$count = $plugin->res2count($res);
Returns a complete result set as array
Returns false or array with result
$rows = $plugin->res2arr($res[,$assoc]){
Returns default an associated array, with column names as indexes. If $assoc = false
it returns an array indexed by column number (0-indexed).
Return the next row from a given result set as associative array
Returns false or array with result
$row = $plugin->res2row($res[, $rownum=0])
Fetch the next row as zero indexed array
Returns false or zero indexed array
$row = $plugin->res_fetch_array($res)
Fetch the next row as assocative array
Returns false or array with column names as index
$row = $plugin->res_fetch_assoc($res)
Return the first value from the next row
Returns false or string result value
$value = $plugin->res2single($res){
Join the given values and quote them for SQL insertion
Returns joined and quoted string
$string = $plugin->quote_and_join($vals,$sep=',');
Escape the given string and surround it with quotes
$string = $plugin->quote_string($string);
Escape string for sql
$string = $plugin->escape_string($string);
Closes the result set (and it's cursors)
If you're doing SELECT queries inside a TRANSACTION, be sure to call this function on all your results sets, before COMMITing the transaction.
Also required when not all rows of a result are fetched
$succeed = $plugin->res_close($res);
Registers a User Defined Function for use in SQL statements
Returns true on succes, false on failure.
$succeed = $plugin->create_function($function_name, $callback, $num_args)
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.
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 provides a few additional features over the standard SQLite syntax.
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
Documentation create table
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
To use the native ALTER TABLE syntax of SQLite:
$plugin->getAdapter()->setUseNativeAlter(true);
The simple support in the sqlite plugin for the ALTER TABLE syntax was to overcome previous limited implementation in PHP's SQLite drivers.
This GROUP_CONCAT function returns a string result with the concatenated unique values from a group.
GROUP_CONCAT(expr, str_val)
Example:
SELECT student_name, GROUP_CONCAT(test_score, ',') FROM student GROUP BY student_name;
First argument is just a 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 ''
.
Returns the integer DokuWiki permission level of a given resolved and cleaned pageid.
GETACCESSLEVEL(str_val)
Example:
// comments for page with read access $query = "SELECT A.pid as pid, page, title, cid FROM entries A, comments B WHERE A.pid = B.pid AND GETACCESSLEVEL(A.page) >= ".AUTH_READ
Available permission levels are defined in inc/auth.php. Hidden pages return also the AUTH_NONE permission level.
Checks whether a page of given pageid exists. Returns 0 or 1.
PAGEEXISTS(str_val)
Example:
SELECT VALUE,pageid FROM tablename WHERE PAGEEXISTS(pageid) = 1
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.
The provided data looks like this:
$data = array( 'from' => 5, // previous version 'to' => 6, // version this migration migrates to 'file' => /some/path/to/plugin/db/update0006.sql, // the associated update file 'sqlite' => $this // the initialized sqlite plugin instance for this database );
You can verify the name of the database being upgraded like that:
public function handle_migrations(Doku_Event $event, $param) { // replace PLUGINNAME with name of your plugin if ($event->data['sqlite']->getAdapter()->getDbname() !== 'PLUGINNAME') { return; } // ... your code }
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.
The BEFORE
event can optionally change the file
attribute of the data to load a different file.
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.
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.