SQLite problems

[simon-r-white]

Running Dokuwiki: Release 2014-09-29d "Hrun"
Strata plugin 'master' branch

Running the strata plugin with the latest sqlite it requested to change the filename from .sqlite to .sqlite3, to use version 3.

However, from the Admin-sqlite interface I get errors when trying to look inside the strata database.

Specifically:

HY000 1 no such table: opts:
SELECT val FROM opts WHERE opt = 'dbversion'

SQLite: no DB version found. 'strata' DB probably broken.

[bwanders]

That's strange.

To help diagnose the problem, can you tell us what other plugins do you use in combination with strata? (And the version of sqlite you're using? As far as I know it shouldn't matter how the file is called...)

[simon-r-white]

sqlite plugin version 2015-05-04 (just upgraded)
Other plugins:

• clearer
• csv
• dir
• directorylist
• include
• searchindex
• snippets
• puresvg
• templatepagename
• wrap

On the command line I can look inside the strata.sqlite3 database and the problem seems to be that there is not table 'opts'

For a comparison, I also installed the data plugin, and there is a table called 'opts'.

So for some reason my strata.sqlite3 doesn't have the 'opts' table with the 'dbversion' entry, this seems to be expected by the sqlite-plugin. Searching through the strata-plugin code I can find no reference to a dbversion.

The sqlite-plugin init() code failed to create the 'opts' table. On the command line I manually added the table and inserted a value of dbversion=1 and this stops the error on the SQLite Access page:

host ~/path/to/data/meta$ sqlite3 strata.sqlite3
SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
sqlite> CREATE TABLE opts (opt,val);
sqlite> CREATE UNIQUE INDEX idx_opt ON opts(opt);
sqlite> INSERT INTO opts (val,opt) VALUES (1,'dbversion');
sqlite> SELECT * FROM opts;
dbversion|1

[bwanders]

Ok, I did some investigating to get to the bottom of this.

As it turns out, the sqlite plugin assumes control of all files called *.sqlite and *.sqlite3. It does so even if the plugin that uses those files has nothing to do with the sqlite plugin. This creates a tug-of-war between the strata plugin and the sqlite plugin over those files... This is also the reason that the strata.sqlite file does not contain a table called 'opts': strata does not depend on the sqlite plugin, so no special tables are present.

There two good solutions to resolve the problem:

1. The easiest solution would be to not use the sqlite plugin. It is not necessary, since strata is perfectly capable of managing its own database. However, if you use other plugins that do require the sqlite plugin this options is not possible.
2. The second solution would be to change the strata configuration. By changing the default database source (called default_dsn in the configuration manager) to sqlite:@METADIR@/strata.triples the conflict is resolved. This way the strata plugin and the sqlite plugin can peacefully coexist.

Since it appears that none of your other plugins depend on the sqlite plugin, I suggest going with the first option, and simply uninstall the sqlite plugin since it is not necessary. If there's a reason to keep it around, the second options will

Let me know how it works out, or if you need any help!

(Note: The strata plugin uses only the file it is configured for, so if the sqlite plugin renames that to strata.sqlite3, the file strata.sqlite will simply be recreated as an empty database. So, regardless of which option you pick, it is a good idea to check the meta dir to clean up any old database files lying around...)

[simon-r-white]

Thanks for looking into this.

I assumed that strata-plugin required the sqlite-plugin.

I quite like the ability to run SQL queries from within dokuwiki using the sqlite-plugin, so actually I'll keep them together, so I'm going to skip your (1) and (2) suggestions and stick with my hack/fix of manually creating the 'opts' table inside strata.sqlite3.

P.S. Really love the strata-plugin, it's really great. Could do with a slightly longer tutorial/guide/examples, but thanks for all your work.

[bwanders]

Note that unless you change the configuration, strata will continue using strata.sqlite... Even to the point of creating that file next to the strata.sqlite3 file that was created by the sqlite plugin.

If this is left like that, updated data from new or changed pages is not going to be present in the strata.sqlite3 database. So be sure to change the configuration of the strata plugin to use the strata.sqlite3 file.

[Klap-in]

What is the default database file that the Strata Plugin will create? stata.sqlite?
A solution is to add an exception in the sqlite2 check of the Sqlite Plugin, which skips strata.sqlite.

[bwanders]

Ideally, a neater solution would be to have some mechanism in the sqlite plugin that other plugins use to register their database for management. It's a bit strange how the sqlite plugin simply assumes control over all files called *.sqlite... However, I recognize that this will force many more plugins to change how they work -- so that solution, even though it is nice, is not going to work. (Note: Maybe I have missed a 'do not manage' flag that I can set in the database to get the sqlite plugin to ignore strata. If someone knows something like this, I would love to hear it!)

The second best would be to adapt the strata plugin to use a different file name. Unfortunately, this would force all current users of strata to change their configuration, or to manually move the file around... I feel that simply moving the file strata.sqlite to something else automatically is undesirable.

I am not sure how to tackle this, and will probably have to look at the sqlite plugin some more to see if there's a clean way to handle this conflict. Ideas would be greatly appreciated!

[Klap-in]

So the default database file name for a sqlite strata database is strata.sqlite?
Then an hardcode check in the sqlite plugin will improve the compatibility at least.

(I did some development on the sqlite plugin. So just ask your questions.)

[Klap-in]

This two checks are performed at the selected .sqlite and the.sqlite3` databases to decide if convert/rename actions are required.

https://github.com/cosmocode/sqlite/blob/master/helper.php#L63
EDIT: pardon wrong pointer.

[bwanders]

Yes, the default name is strata.sqlite. But as @simon-r-white pointed out earlier, doing the hardcoded check would disable the use of the sqlite admin interface... I have no real problem with this, but others might want to use the admin interface instead of the console to directly query the database.

I do not really want to inconvenience the sqlite plugin, yet I would also like for the two plugins to peacefully co-exist. What are the minimal necessities that I would need to change on my side to cooperate with the sqlite plugin without actually depending on it? As far as I gather right now, it is the existence of an opts table, and the file extensions sqlite3. Am I missing anything?

For now, I'll have a think on how to tackle this incompatibility.

[Klap-in]

Check if the selected database is sqlite3 is by: https://github.com/cosmocode/sqlite/blob/master/classes/adapter.php#L83

For these indeed a .sqlite extension is required by the plugin, currently.

And the opts table is used for storing the database version.

for example the Do Plugin database looks like:

query:
SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name;
result:

name            sql
------------------------------------------------------------------------------------
opts            CREATE TABLE opts (opt,val)
task_assignees  CREATE TABLE task_assignees (page, md5, user)
task_status     CREATE TABLE task_status ( page, md5, status , closedby , msg)
tasks           CREATE TABLE tasks ( page, md5, date, text , creator , pos)

query:
SELECT * from opts;
result:

opt             val
------------------------------------------------------------------------------------
dbversion       7

I rise now an issue at the sqlite tracker for discussion this.

• I wondering if at least sqlite should ignore everything
• or should support access to all databases, so also to these not created by Sqlite Plugin

I tend to the latest option, but with a warning that it is not fitting to the default sqlite-plugin scheme.

[Klap-in]

Maybe auto-update mechanisms can still trigger some nifty things, if you just add an opt table. So when not really not using this, adding this is probably not a good solution.