Feature save SQL queries

[solewniczak]

This branch extends the sqlite plugin with two important contributions:

1. Saving SQL queries for latter use.
2. Displaying saved queries results in wiki pages.

The main idea of this extension is to provide the most general way to overcome query limitations of plugins that use sqlite (with primarily struct in mind). The two important use cases where I find this plugin useful are advanced join operations and group by queries which cannot be performed using standard struct syntax.

While the first contribution is quite straightforward, the second one have some security implications - every user that has write permission and knows (or can guess) the query's name, can see the query results. However for wikis where there are not strict security rules, this feature can be very useful. One way to make it a little bit safer is add the config option that will enable usage of named queries in wikicode (which can be turn off by default).

In order to display a results of a named query, we can use the syntax: <sqlite>dbname.query_name</sqlite>. The <sqlite> tag takes optional parameters. Firstly, we can define the parser used on the raw query results for each individual column. The parsers are classes from struct types and are available only when the sturct plugin is installed in wiki:

<sqlite parser_(nr of column in sequence)="ParserName(optional config in json format)>dbname.query_name</sqlite>

Secondly we can pass the arguments for the query (separated by comas), which will replace the quotation marks in the SQL:

<sqlite args="first arg,second arg">dbname.query_name</sqlite>

The arguments can be static strings or special variables: $ID$, $NS$, $PAGE$, $USER$, $TODAY$.

Examples:

Query: SELECT id, original_poster, content FROM task WHERE original_poster=?;
<sqlite parser_1="User" parser_2="Wiki" args="$USER$">b3p.task</sqlite>

Query: SELECT json_array(data_certs.pid, titles.title) AS "title" FROM data_certs LEFT JOIN titles ON data_certs.pid = titles.pid;
<sqlite parser_0='Page({"usetitles": true})'>struct.certs</sqlite>

[splitbrain]

I like the idea to be able to save queries for reuse in the sqlite admin interface itself. I would make a nice addition.

For having stored queries with results that can be embedded into wiki pages I would prefer to have it in a separate plugin. Actually the the dbquery plugin already does it. It's also database agnostic. To be a proper alternative to struct aggregations additional features like dynamic sorting, filtering and paging would be nice. But again I see that as potential features for the dbquery plugin.

[solewniczak]

I removed the syntax component from the commit, keeping only the query saving feature. Do you think you can merge it now?

[solewniczak]

I've introduced several improvements to the plugin's admin interface:

1. I've switched from $_REQUESTS to $INPUT
2. I've removed the action "sqlite_convert" for converting from sqlite2 to sqlite3. According to my knowledge, the current versions of PHP don't support sqlite2 anymore so in fact the code cannot be used. Additionally, the code itself has some TODOs and depends on the old Doku_Form class.
3. I've switched to Form class in all places.
4. I've moved the query save and delete actions handling to the handler() and considered the security token.
5. I've simplified the db helper. The previous helper was just taken from the struct plugin, so I've removed the code that is not used in the context of the sqlite plugin.

[splitbrain]

Looks good. One general question. Would it make sense to save these per database? Because most select would be very specific to a certain database, right?

[solewniczak]

Do you mean to save the queries in individual databases, not in an additional "sqlite.sqlite3" database? It can make sense indeed since we already have the special table: "opts" and we can create another one. But I was a little bit scared to update existing databases so I decided to keep the saved queries in a separate database.