DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:sqlite

sqlite Plugin

Compatible with DokuWiki

  • 2024-02-06 "Kaos" yes
  • 2023-04-04 "Jack Jackrum" yes
  • 2022-07-31 "Igor" yes
  • 2020-07-29 "Hogfather" yes

plugin A helper plugin to easily access a SQLite database

Last updated on
2024-03-05
Provides
Helper
Repository
Source
Conflicts with
searchtext

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

This plugin needs SQLite and PDO support in your PHP installation. Be sure the pdo-sqlite extension is installed and loaded.

Download and Installation

Search and install the plugin using the Extension Manager. Refer to Plugins on how to install plugins manually.

Changes

Admin Interface

The plugin comes with a simple admin interface that gives you low level access to any of the available databases. This includes:

  • executing arbitrary queries
  • run predefined queries
  • export and import database contents
  • save and recall self-defined queries

To use any of its functionalities, first select the database you want to work on from the Table of Contents.

:!: Be sure you know what you're doing. This interface provides no safety nets against deleting or corrupting your database contents!

 Admin Interface Screenshot

Developer Documentation

This plugin provides you with all the mechanisms needed to use a sqlite database in your plugin. All functionality is provided through the \dokuwiki\plugin\sqlite\SQLiteDB class.

It is recommended to lazy load the class if needed, for example in a helper component of your plugin. Since your helper is (usually) a singleton, it will ensure that all your code parts use the same database connection. And using a lazy loading function ensures the connection is only established (and initialized) when actually required.

Below you find an example of a helper component that should get you started.

<?php
 
use dokuwiki\ErrorHandler;
use dokuwiki\Extension\Plugin;
use dokuwiki\plugin\sqlite\SQLiteDB;
 
 
class helper_plugin_example extends Plugin
{
    protected $db;
 
    /**
     * 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;
    }
 
    /**
     * @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;
    }
 
}

Database Setup and Migration

As you've seen above, the constructor of the SQLiteDB class requires two parameters. A database name and a migration directory.

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.

The second parameter defines the directory where the database migration files are to be found. Most plugins use a db directory in their plugin structure. Use the DOKU_PLUGIN or DIR constants to create the appropriate absolute path.

Migration directory example

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.

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 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.

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!

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.

Public API

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.

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: SQLiteDB.php.

Always use the provided parameter replacement mechanisms when passing user provided data into queries to avoid SQL injection attacks! See query() method.

getPdo()

This method returns the underlying PDO object of the connection. You can use it to call all methods defined by the PDO interface.

This is useful to manage transactions or register your own functions, for example.

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 PDOStatement object. Be sure to close it's cursor when you're done. You usually want to use one of the convenience methods below.

$res = $sqlite->query('SELECT foo FROM example WHERE baz = ?', [$baz]);
$row = $res->fetch();
$res->closeCursor();

exec()

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.

$id = $sqlite->exec('INSERT INTO example (bar) VALUES (?)', [$bar]);
echo "new ID is $id";

queryAll()

Convenience method to execute a query and return all rows as associative arrays. Keep memory limits in mind when querying large data sets!

$rows = $sqlite->queryAll('SELECT * FROM example WHERE bar = ?', [$bar]);
foreach($rows as $row) {
    foreach($row as $col => $val) {
        echo "$col: $val\n";
    }
    echo "------\n";
}

queryRecord()

Convenience method to execute a query and return a single row only.

$row = $sqlite->queryRecord('SELECT * FROM example WHERE bar = ?', [$bar]);
foreach($row as $col => $val) {
    echo "$col: $val\n";
}

saveRecord()

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.

If the given data violates a constraint, it is either ignored or the existing data is replaced.

Note: this method only provides INSERT OR IGNORE and INSERT OR REPLACE functionality. If you need UPSERT functionality, build your own query and use exec().

$data = [
    'foo' => 'Florb',
    'baz' => 12932
];
$new = $sqlite->saveRecord('example', $data);
var_dump($new);

queryValue()

Convenience method to query a single value from the database. It will return the first value in the first row of the result set.

$foo = $sqlite->queryValue('SELECT foo FROM example WHERE bar = ?', [$bar]);
echo $foo;

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.

$res = $sqlite->queryKeyValueList('SELECT foo,baz FROM example WHERE bar = ?', [$bar]);
foreach($res as $key => $val) {
    echo "$key: $val\n";
}

SQL Extensions

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.

Check the Functions class for details: Functions.php.

Your plugin can register it's own functions using the getPDO()->sqliteCreateFunction() and getPDO()->sqliteCreateAggregate().

GETACCESSLEVEL

Returns the integer DokuWiki permission level of a given pageid.

Note: this function will also return AUTH_NONE for hidden pages.

$sqlite->queryAll(
  'SELECT page, foo, bar FROM example WHERE GETACCESSLEVEL(page) > ?',
  [AUTH_READ]
);

PAGEEXISTS

Checks if the given pagename exists. Returns 1 for existing pages, 0 otherwise.

$sqlite->queryAll(
  'SELECT page, foo, bar FROM example WHERE PAGEEXISTS(page) = 1'
);

REGEXP

Matches a value against a given regular expression. The expression is used with / delimiters and the u Unicode modifier.

$sqlite->queryAll(
  'SELECT page, foo, bar FROM example WHERE REGEXP("^wiki:.*", page)'
);

RESOLVEPAGE

Resolves a given relative page against a given context page.

$sqlite->queryAll(
  'SELECT RESOLVEPAGE(page, ?), foo, bar FROM example',
  [$context]
);

GROUP_CONCAT_DISTINCT

This is an aggregate functions that works like the built-in 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 a custom separator. Only if other options are not feasible, use this registered function.

$sqlite->queryAll(
  'SELECT foo, GROUP_CONCAT_DISTINCT(bar,' ') FROM example GROUP BY foo',
  [$context]
);

Events

The plugin will trigger the following events that your plugin can register for.

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.

The provided data looks like this:

$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 helper, do not use
    'adapter' => SQLiteDB // instance of the SQLiteDB class
];

:!: 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:

public function handle_migrations(Doku_Event $event, $param) {
    // replace example with name of your plugin
    if ($event->data['database'] !== 'example') {
        return;
    }
 
    // code to handle update to version 7
    if($data['to'] === 7) {
        $data['adapter']->exec('....');
        $event->result = true;
    }
}

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.

Code related to this event used in any files of plugins and templates

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.

$event->data = [
    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database.
    'upstream' => 'example', // the name of the selected database, for which the query is stored
    'name' => &$name, // The name the user picked for this query
    'query' => &$query, // The SQL for this query
]

Code related to this event used in any files of plugins and templates

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).

$event->data = [
    'sqlitedb' => SQLiteDB, // an instance of the SQLiteDB class for access to the ''sqlite'' database.
    'upstream' => 'example', // the name of the selected database, for which the query is stored
    'name' => &$name, // The name the user picked for this query
]

Code related to this event used in any files of plugins and templates

Slow Query Log

When debug logging is enabled (see dontlog option), queries that take longer than 0.2 seconds will be logged as slow. Use this to optimize and debug your queries.

Changes from earlier Releases

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 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 documentation above and adjust your plugin if needed.

Plugin developers are advised to update their plugin to make use of the newer, cleaner SQLiteDB interface. The helper is now deprecated.

plugin/sqlite.txt · Last modified: 2024-03-05 23:51 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