Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
database2 [2017/08/01 14:58]
wikiadmin gelöscht
— (aktuell)
Zeile 1: Zeile 1:
-{{tag>​dokuwiki plugin}} 
-====== DokuWiki-Plugin "​database2"​ ====== 
- 
-  * [[https://​github.com/​soletan/​database2]] 
-  * [[https://​nihilum.de/​software:​database2:​talk.html]] 
- 
-====== database2 — a DokuWiki plugin ====== 
- 
-In February 2009 I started to develop my own plugin for DokuWiki providing integration of database tables, e.g. to render their content or edit them in context of a wiki page. This plugin is called database2((Since there is an existing plugin called database doing something very similar the "​2"​ has been added to indicate this relationship as an alternative.)). 
- 
-* State: in development 
- 
-===== Credits ===== 
- 
-Thanks to 
- 
-* Jason Keltz for testing and bugfixing and his suggestions for improvements 
- 
-* Dean Murray for his suggestions 
- 
-* Mario Jung for pointing me on a lacking support for UTF-8 encoding 
- 
-* Tom Trenker for reporting bug and eligible feature request 
- 
-* Fabrizio Zacché for his suggestions and bug reports 
- 
-* Discussion guests NIk, Devon and Michael for their suggestions and eligible feature requests 
- 
-* Jesse Alama for bug reports 
- 
-* Anders Westerberg for bug reports and interesting feature requests 
- 
-* William Kyngesburye for bug reports and feature requests 
- 
-* Pieter Voogt for bug report 
- 
-* Alex Kachmar for bug report and related code provision 
- 
-* Frederic Boyer for bug report and related code provision 
- 
-* Michael Würtz for bug report 
- 
-* Stefan Bellon for bug report 
- 
-* Discussion guest Joost for bug report 
- 
-* Discussion guest Damon for (accidentally) revealing severe vulnerability fixed in 0.4.0, bug reports and feature requests 
- 
-* //Jeffrey Sacco for UI-improving feature requests and supporting donation// 
- 
-* Markus Brozio for feature request extending usability 
- 
-===== About ===== 
- 
-database2 is a plugin to DokuWiki, an online wiki application freely available from [[http://​www.dokuwiki.org|www.dokuwiki.org]] providing integration of database-driven tables into pages of a wiki. 
- 
-==== Current Features ==== 
- 
-Here is a short list of features included with this plugin: 
- 
-* local and remote database support 
- 
-* local databases are stored in current page's meta folder using SQLite((Recently they were stored in "​namespace"​ and thus available for sharing by all pages in same namespace, however this association with namespaces was inconsistent and so changed to association with page. Sharing is still available by using explicit database selection in table option ''​database''​.)) 
- 
-* using PHP's PDO several remote database systems are basically supported (**tested: MySQL**) 
- 
-* integrate one table per tag from connected database. 
- 
-* list all records in a table featuring 
- 
-* limited number of records per page supporting flipping and page selection 
- 
-* sorting by single column ascendingly or descendingly 
- 
-* selecting subset of columns listed 
- 
-* selecting virtual fields aliasing computed data 
- 
-* filter listed records 
- 
-* multi-component filter supporting intersection and union 
- 
-* hard-coded definition for (initial) filter 
- 
-* optional filter controls - always available or requiring authorization to become available 
- 
-* select single record for detail view containing all available columns 
- 
-* included actions((available if table has a single integer column as primary key)) 
- 
-* adding new records 
- 
-* editing existing records 
- 
-* delete existing records 
- 
-* copy existing records 
- 
-* several value types (internally mapped into SQL types) having effect on record editor elements 
- 
-* text fields 
- 
-* checkboxes 
- 
-* enumeration selectors 
- 
-* image/file upload 
- 
-* "​foreign keys" 
- 
-* single-record locking enabling several users concurrently work on a single table 
- 
-* table-, row- and column-related authorization rules for controlling access on supported actions 
- 
-* any performed action causing a change of data is logged in a separate table 
- 
-* provides extended access on data 
- 
-* exporting all or filtered records to CSV file 
- 
-* export table'​s change log to CSV for inspecting when someone has recently changed what in table 
- 
-* provides special print version listing all matching records in a vanilla document 
- 
-* read-only views using custom SELECT-statement 
- 
-* read-only integration of computed SQL terms 
- 
-* optionally supports DokuWiki markup in text values 
- 
-* console for administrative operations on locally managed SQLite databases 
- 
-==== Planned Features ==== 
- 
-* <​del>​Searching / Filtering</​del>​ 
- 
-* <​del>​Exporting to CSV</​del>​ 
- 
-* Hashing text input (e.g. for entering passwords) 
- 
-* Adding missing columns/​fields to a table 
- 
-* <​del>​Providing text edit box for entering custom SQL statements</​del>​ 
- 
-* <​del>​Support DokuWiki markup in text fields</​del>​ 
- 
-* <​del>​Add Log Viewer for inspecting recent changes to a table</​del>​ 
- 
-* Always revise integration with DokuWiki where possible 
- 
-==== Known Vulnerabilities ==== 
- 
-<note warning>​Plugins like ''​discussion''​ are used to enable unauthenticated users to post comments. As such posts may include any sort of Wiki markup. This way it's possible for commenters to embed database2 tags with public write access to SQLite database files into pages.</​note>​ 
- 
-Prior to release 0.4.0 there is no useful opportunity to protect your Wiki site when running ''​database2''​ **and** ''​discussion''​ or similar. **Thus you shouldn'​t install both plugins.** 
- 
-Starting with 0.4.0 support for database2 must be enabled explicitly for selected pages of your Wiki. Now, it's possible to install both plugins. **However you shouldn'​t have discussions on pages enabled for database2.** 
- 
-==== Demonstration ==== 
- 
-You may test this plugin using provided [[.:​database2:​demo|demonstration page]]. Database files might be dropped once a day at 2 AM CE(S)T, so expect some strange behaviour while testing the tool at this time. 
- 
-===== Download & Installation ===== 
- 
-Copy and paste the following address into your Plugin Manager or click the link to download the plugin. 
- 
-<​code>​http://​wiki.nihilum.de/​myfiles/​database2-latest.tar.gz</​code>​ 
- 
-Link for download: [[http://​wiki.nihilum.de/​myfiles/​database2-latest.tar.gz|.TAR.GZ]] 
- 
-<note information>​Testers are welcome. Send your feedback to [[soletan@nihilum.de]].</​note>​ 
- 
-==== System Requirements ==== 
- 
-* PHP 5, required at least due to the use of 
- 
-* strong-type arguments 
- 
-* pass-by-reference arguments **and** functions 
- 
-* PDO for database connectivity\\ **NOTE!** This requirement includes proper drivers for connecting to the database engine of your choice. 
- 
-==== Manual installation (w/o Plugin Manager) ==== 
- 
-Installing this plugin works equivalent to installing most other plugins: Unpack the downloaded archive to your installation of DokuWiki into subfolder lib/​plugins. That's it. 
- 
-==== Current Version ==== 
- 
-Latest available version is 0.4.3, released on December, 18th 2009 CE(S)T. See the [[./​database2:​changelog|version history]] for a list of changes. 
- 
-<note warning>​As the plugin'​s set of features is growing with each release it's getting harder to test every previously available feature. I always try to keep things running and play with new modifications prior to releasing them, but sometimes it doesn'​t prevent inclusion of bugs I didn't encounter myself. Feel free to drop me mail if you think there is a bug introduced with a new release.</​note>​ 
- 
-<note warning>​If you're upgrading from a 0.1.x release to 0.2.x or later you need to adjust setup of all used **remote databases**. See the related section below.</​note>​ 
- 
-<note warning>​If you're upgrading from a version prior to 0.2.2 you need to adjust your static filter definitions as they don't require URL encoding anymore. Simply enclose them by double quotes!</​note>​ 
- 
-===== Discussion ===== 
- 
-There is a [[.:​database2:​talk|separate page]] for discussions... 
- 
-===== How To Use ===== 
- 
-The plugin is a Syntax plugin and thus introducing new markup to be used as often as desired in a wiki page. 
- 
- 
-==== Enable ''​database2''​ ==== 
- 
-On successfully installing plugin or upgrading from versions prior to 0.4.0 you need to enable the plugin on a per-page basis. Change to your wiki's configuration manager and look for the section related to plugin database2. 
- 
-=== Enable on all pages === 
- 
-If your wiki isn't providing any open write access including wiki markup support and/or if you trust all your users you may globally enable database2 on every page using separate option in configuration manager. Doing so any patterns provided for selecting enabled pages are ignored. 
- 
-=== Enable on selected pages === 
- 
-If global option described before is ''​off'',​ you may enter one or more patterns for matching pages' full path selecting those pages being enabled for using database2 tag. Write one pattern per line. Each pattern may be a PCRE pattern wrapped in slashes or a shell pattern with ''​*''​ and ''?''​ as wildcards. 
- 
-<​code>/​^software:​database2:​demo/​i 
- 
-*:​database2</​code>​ 
- 
-==== Syntax ==== 
- 
-The plugin is integrated in a page by using a special tag called ''​database''​ like this: 
- 
-<​code><​database tablename [attribute=value ...]> 
- 
-column definition 
- 
-column definition 
- 
-... 
- 
-</​database></​code>​ 
- 
-The tag is surrounding a set of column definitions. Additionally the tag **must** contain a table'​s name to use in connected database and **may** contain further options. These options are called **table options** furtheron since every column may have individual options as well called **column options** throughout the rest of this documentation. 
- 
-=== Table Options === 
- 
-Table options are provided in opening tag. They consist of a token succeeded by an assignment operator and an assigned value string. The latter might be enclosed in double quotes to contain white spaces used for separating multiple options otherwise. 
- 
-**Versions 0.2.1 and earlier** don't support quoted strings as described before. Thus you can't have white spaces in an assigned value string. 
- 
-A special attribute is the mandatory table name which is accepted without preceding token and assignment operator. It may appear at any position in list of attributes. 
- 
-== Table attributes == 
- 
-^ Token ^ Available Since ^ Description ^ Examples ^ 
- 
-|''​addonclick''​ |0.4.1 |Boolean flag for selecting if using column option ''​onclick''​ is extending or replacing related command in same row. |addonclick=yes | 
- 
-|''​auth''​ | |Name of slot in wiki configuration containing username and password required for connecting to remote database server. Since page source is visible to many users providing username and password there isn't supported for security reasons. See section on setting up remote database below for more information! |auth=mydbsrv1 | 
- 
-|''​basefilter''​ |0.1.8 / 0.3.3 |Describes initially used filter. There is a section below explaining syntax for this attribute'​s value. By providing filter here and keeping filter controls hidden it's possible to list a subset of data in table.\\ Since 0.3.3 string operand values may include markup sequences as described on default values below. |basefilter="​email like %.com%%|%%surname like bo" | 
- 
-|''​database''​ | |Explicitly selects local database file or remote database server to connect to. The value is either a page's ID to select associated database file, absolute pathname of SQLite database file or DSN of a remote database server preceded by an initial ''​@''​. See the [[http://​www.php.net/​manual/​en/​book.pdo.php|PHP manual on PDO]] for more information on DSNs! |database=/​dbfiles/​custom.db | 
- 
-|''​mayadmin''​ |0.3.0 |See section on authorizations below! |mayadmin=!@all | 
- 
-|''​maydelete''​ | |See section on authorizations below! |maydelete=@editor | 
- 
-|''​maydownload''​ | |See section on authorizations below! |maydownload=@user | 
- 
-|''​maydrop''​ | |See section on authorizations below! |maydrop=@admin,​manager | 
- 
-|''​mayedit''​ | |See section on authorizations below! |mayedit=@crm | 
- 
-|''​mayexport''​ |0.1.11 |Authorizes users and/or groups to export records to downloadable file for post-processing and backup. |mayexport=@manager | 
- 
-|''​mayfilter''​ |0.1.8 |See section on authorizations below! Selects whether or not filter controls are available. |mayfilter=@manager | 
- 
-|''​mayinsert''​ | |See section on authorizations below! |mayinsert=@crm,​!newbie | 
- 
-|''​mayinspect''​ | |See section on authorizations below! |mayinspect=userB | 
- 
-|''​mayprint''​ |0.1.12 |Authorizes to get separate print version. |mayprint=@ALL | 
- 
-|''​mayview''​ | |See section on authorizations below! |mayview=@user,​!userA | 
- 
-|''​mayviewlog''​ |0.1.12 |Authorizes to export changelog of table to downloadable CSV file. |mayviewlog=@ALL | 
- 
-|''​rowsperpage''​ |0.2.3 |Provides initial maximum number of records listed on a page (making all further records available by navigation controls). Values less than 10 are ignored. |rowsperpage=35 | 
- 
-|''​simplenav''​ |0.1.20 |Boolean flag disabling support for extended navigation in single-record editor which is enabled by default. |simplenav=yes | 
- 
-|''​sort''​ |0.2.18 / 0.3.3 |Selects initial sorting. The assigned value is either a column'​s name for ascending order or a column'​s name prefixed by exclamation mark for descending order.\\ Since 0.3.3 this might be a comma-separated list of multiple columns to sort by. |sort=!colname | 
- 
-|''​view''​ |0.2.2 |Provides arbitrary SQL statement used to retrieve read-only list of records. Don't miss to include table'​s numeric primary key to enable single-record viewer. **This feature exposes a vulnerability in selected setups and thus requires explicit activation in DokuWiki configuration!** |view="​SELECT id,col1 FROM table" | 
- 
-|''​width''​ | |Explicitly selects width of table used on listing table data. Provided value is either integer selecting pixel width or integer succeeded by percent sign to select percental width. If omitted the table is rendered as wide as required to contain all data. |width=600 or width=70% | 
- 
-|''​wikimarkup''​ |0.1.14 |Boolean flag enabling support for Wiki markup in text values. **Note! This is remarkably slowing down page rendering performance!** |wikimarkup=on | 
- 
-|''​wikistyle''​ |0.2.18 |Requests to use native table styles of DokuWiki. |wikistyle | 
- 
-=== Column Definition === 
- 
-Each column is defined in a separate line between opening and closing tag. A column definition line consists of up to four comma-separated fields. 
- 
-<​code>​column_name [, [ column_type ] [, [ column label ] [, [ column options ] ] ] ]</​code>​ 
- 
-According to the first three fields initial and trailing spaces are ignored unless a field is enclosed in double quotes. Doing so this field may even include literal comma unless excluded by other rules. The fourth field is parsed differently (see below). 
- 
-Empty lines and commenting lines starting with ''#''​ or ''​%%//​%%''​ are ignored. 
- 
-== 1st field: Column Name == 
- 
-The first field is mandatory and contains the column'​s internally used name. It mustn'​t contain anything but letters, digits and underscore as found in ASCII character set. If you include any other character it is replaced by an underscore automatically. On using option ''​aliasing''​ this name becomes the alias of an SQL term to be aliased. 
- 
-== 2nd field: Column Type == 
- 
-The second field selects the type of values stored in column. If this field is omitted or empty, ''​text''​ is selected by default. 
- 
-^ Typename ^ Available Since ^ Alternatively ^ Assigned Type ^ Description ^ Examples ^ 
- 
-|''​acl''​ |0.3.0 | |text |authorization rule set overriding table-related authorizations in context of current record | mayedit=!@all;​maydelete=userA,​@groupB | 
- 
-|''​check''​ | |''​mark'',​ ''​bool'',​ ''​boolean''​ |bool / integer |two-state checkbox | | 
- 
-|''​date''​ | | |date / integer |Date information (without time) - this type is stored in DB either as Unix timestamp (if column option ''​unixts''​ is set) or in format ''​YYYY-MM-DD''​. Dates are rendered according to your wiki's configuration. |2009/02/11 | 
- 
-|''​datetime''​ | | |date / integer |Combination of date and time information - this type is stored in DB either as Unix timestamp (if column option ''​unixts''​ is set) or in format ''​YYYY-MM-DDTHH:​MM:​SS''​. Values are rendered according to your wiki's configuration. |2009/02/11 13:53:00 | 
- 
-|''​decimal''​ | |''​numeric'',​ ''​monetary''​ |decimal |Precise numeric value |3.5 or -23.76 | 
- 
-|''​enum''​ | | |enum |enumeration of strings to select one\\ //The selectables are appended after this type name and separated by slashes or semicolons from each other.// | male / female | 
- 
-|''​file''​ | |''​data'',​ ''​blob'',​ ''​binary''​ |data |Single file, in opposition to ''​image''​ files are made available for download on viewing a record while images are rendered inline | | 
- 
-|''​float''​ | |''​real'',​ ''​double''​ |real |Floating-point numeric value |3.5 or -23.76 | 
- 
-|''​image''​ | | |data |Single image file | | 
- 
-|''​integer''​ | |''​int''​ |integer |Numeric integer values |-5 or 3004 | 
- 
-|''​mail''​ | |''​email''​ |text |e-mail address\\ //Values are stored as string/​text,​ but record editor specially validates input.// |[[soletan@nihilum.de]] | 
- 
-|''​phone''​ | |''​fax''​ |text |a telephone number\\ //Values are stored as string/​text,​ but record editor specially validates input.// |+49 (30) 1234 567-8 | 
- 
-|''​related''​ |0.2.11 | |related |enumeration of records of a custom view\\ Providing custom SELECT statement returning numeric ID and a label for each available selection this is a more dynamic version of ''​enum''​. Additionally a selected record'​s numeric ID is stored instead of a string label enabling table linking using this datatype.\\ //Enclose this column type definition in double quotes to have literal commata and append the desired SELECT statement right after keyword ''​related''​ and some whitespace.//​ | "​related SELECT id,surname FROM people"​ | 
- 
-|''​text''​ **[default]** | |''​name'',​ ''​string'',​ ''​char''​ |text |unparsed raw text, e.g. names or similar\\ //If column options select a maximum length of values less than 255 a single-line text edit field is rendered in editor, otherwise a multi-line textarea is used.// |John Doe is not a name. | 
- 
-|''​time''​ | | |time |Time information (without date) in form HH:MM:SS |23:00 or 12:34:56 | 
- 
-|''​url''​ | |''​link'',​ ''​href''​ |text |absolute URL (or web address) for storing links e.g. to websites or remotely available files\\ //Values are stored as string/​text,​ but record editor specially validates input.// |http://​www.nihilum.de | 
- 
-== 3rd field: Column Label == 
- 
-For rendering list view and a single record'​s detail view each column my have an alternative label used in list's header or as labels to fields in single record editor/​viewer. If omitted the column'​s name as given in first field is used instead. 
- 
-== 4th field: Column Options == 
- 
-Column options are in fourth field of a column'​s definition. 
- 
-This whole field is parsed differently from the preceeding ones. It may consist of an arbitrary number of key-value assignments e.g. as used for attributes in HTML tags. By optionally omitting assignment operator and succeeding value boolean true is assigned by default to keep things compatible with earlier versions. Any information in 4th field may include literal comma without using quoted string. 
- 
-<​code>​name = "​value"</​code>​ 
- 
-Enclosing value in double quotes is optional. Quoted strings support C-like escape sequences such as "​\n"​ for newline and "​\x41"​ for an A majuscule. Using extra whitespace around assignment operator is supported as well, however this might decrease the code's readability. 
- 
-<​note>​**Versions prior to 0.2.0** did not include support for quoted strings as described above. A double quote is taken as literal part of value there. White spaces are **always** used to separate multiple options from each other.</​note>​ 
- 
-Multiple attributes are separated by whitespace from each other. 
- 
-^ Token ^ Available Since ^ Assigned Value'​s Type ^ Description ^ Example ^ 
- 
-|''<​integer>''​ | | - |This is an convenience alias for ''​length=<​integer>''​. |32 | 
- 
-|''​@<​integer>''​ | | - |This is an convenience alias for ''​tabindex=<​integer>''​. |@2 | 
- 
-|''​accept''​ | | PCRE pattern |Selects accepted MIME types on uploading files to a column of type file. |accept=§text/​§i | 
- 
-|''​aliasing''​ |0.2.0 | SQL term |Declares column to be an alias of some arbitrary SQL term supported by connected DB engine. This implicitly marks the column to be read-only equivalently to using ''​readonly''​ (see above). You are advised to use a quoted string for providing aliased SQL term here. **This feature exposes a vulnerability in selected setups and thus requires explicit activation in DokuWiki configuration!** |aliasing="​upper(surname)"​ | 
- 
-|''​alwaysshow''​ |0.2.15 | - |Declares column being always visible in single-record detail view. By default columns with NULL values are omitted automatically. This option requests to render them nevertheless.\\ //Note! This is different from column option ''​visible''​ above!// |alwaysshow | 
- 
-|''​booltype''​ | |''​xmark''​ or ''​yesno''​ or ''​int''​ |Selects method of storing boolean state markers in table and thus applies to columns of type ''​boolean'',​ only. The default is ''​yesno''​ storing either ''​y''​ or ''​n''​ for true/set or false/unset in table, while ''​xmark''​ selects to use ''​x''​ for true and ''​ ''​ (single blank) for false. ''​int''​ works with an integer column and selects to use 1 for true and 0 for false. |booltype=xmark | 
- 
-|''​default''​ |0.2.2 | value |Declares default value of column used on adding new record to table. All but file/image columns are supporting this unless option ''​nodefault''​ is set. See the related section on providing default values below! |default="​some text" | 
- 
-|''​filter''​ |0.1.8 | - |Explicitly selects column to be included in filter'​s column selector. If this attribute isn't used on any column, all visible columns are included.\\ **Note!** The selector includes columns with one of the following assigned types, only: ''​integer'',​ ''​real'',​ ''​decimal'',​ ''​text'',​ ''​date'',​ ''​enum'',​ ''​bool''​. |filter | 
- 
-|''​headerlabel''​ |0.3.5 | string |Selects different label for this column'​s header to be used on rendering table. |headerlabel="​short name" | 
- 
-|''​headerlink''​ |0.4.1 | string |Provides __absolute__ URL or page ID for turning label of column'​s header into clickable link on rendering table. |headerlink="​..:​glossar#​surname"​ | 
- 
-|''​length''​ | | integer |Selects maximum length of a string or file resulting in different SQL code used to create a missing table and on rendering single-record editor. |length=32 | 
- 
-|''​mayedit''​ | | authorization rule |See section on authorizations below! |mayedit=@admin | 
- 
-|''​mayview''​ | | authorization rule |See section on authorizations below! |mayview=@user,​!userA | 
- 
-|''​onclick''​ |0.4.1 / 0.4.2 |''​edit''​ or ''​inspect''​ or page ID/URL |Provides //​case-sensitive//​ name of action to perform in relation to current row on clicking value in column (on rendering table). Depending on table option ''​addonclick''​ this is either extending or replacing related command in row. User's authorizations are obeyed.\\ In releases 0.4.2+ it's supporting page IDs and absolute URLs with current cell value embedded using markup sequence ''​%{value}''​. |onclick=edit | 
- 
-|''​nodefault''​ | | - |Disables default value for this column initially inserted on adding a new record to table. |nodefault | 
- 
-|''​noprint''​ |0.2.17 | - |Declares column being exclusively visible in screen version, only. |noprint | 
- 
-|''​notnull''​ |0.2.9 | - |Declares column not supporting NULL values. This affects writing empty/unset values using 0000-00-00, 0 or empty string instead of NULL. |notnull | 
- 
-|''​primary''​ | | - |Marks current column to be (part of) table'​s primary key index. If there is no column with this option defined a hidden column "​id"​ is preceding your set of column definitions automatically.\\ //**Please note,** that all single-record actions are disabled if you define a multi-column or non-integer primary key index.// |primary | 
- 
-|''​print''​ |0.2.17 | - |Declares column being additionally visible in print version, only. |print | 
- 
- 
- 
-|''​readonly''​ |0.1.20 | - |Marks column to be read-only. This is different from using proper declarations on column-based authorizations in that the column is never written back to database and that even administrators mustn'​t edit the column'​s values. |readonly | 
- 
-|''​required''​ | | - |Marks current column to be required. This implies definition of table in SQL as well as requests editor to demand non-empty value in this column. |required | 
- 
-|''​tabindex''​ | | integer |Explicitly selects position of this column/​field in single-record editor.\\ //**Note:** All columns without tabindex are appended to list of columns with tabindex.// |tabindex=2 | 
- 
-|''​unique''​ | | - |Marks current column to be (part of) a unique index. This token may be suffixed by an integer selecting one of several unique indices. All columns with same token and optional integer are then combined in a single unique index. |unique5 | 
- 
-|''​unixts''​ | | - |Applies to columns of type ''​date''​ or ''​datetime''​ and switches to work with an integer internally representing a date as Unix timestamp.\\ //**Note:** Dates without time have noon (12 a.m.) as hidden time when storing as Unix timestamp.//​ |unixts | 
- 
-|''​visible''​ | | - |Explicitly selects to include this column on rendering list. All columns are visible by default unless you set this option on at least one column so all other columns without this option become invisible by default. |visible | 
- 
-|''​wikimarkup''​ |0.2.18 | - |Individually enables processing of wiki markup in column. This is available in columns of type ''​text'',​ only!\\ **Note! Enabling wiki markup decreases performance on rendering pages.** |wikimarkup | 
- 
-=== Authorizations === 
- 
-This plugin features table-related and column-related authorizations. In 0.3.0 support for row-related authorizations has been added. 
- 
-**Note:** Administrators always gain full access and can't be excluded by an authorization rule. 
- 
-== Table-Related Authorizations == 
- 
-Table-related authorizations are given as attributes in opening tag. See the related section above for basic syntax for doing this. The following table lists all authorizations supported in context of a whole table. 
- 
-^ Authorization ^ Description ^ Authorized if rule is omitted((administrators are always authorized)) ^ 
- 
-|view |See list view of table. |all users | 
- 
-|inspect |See single record in detail view. |all users | 
- 
-|insert |Add new record to table. |none | 
- 
-|edit |Edit existing record in table. |none | 
- 
-|delete |Delete single record from table. |none | 
- 
-|drop |Drop whole table and all contained records. |none | 
- 
-|download |Download files or view attached images. |derived from ''​view''​ | 
- 
-^ **since 0.1.8** ^^^ 
- 
-|filter |See and use filter controls on top of record list. |none | 
- 
-^ **since 0.1.11** ^^^ 
- 
-|export |Export records to CSV file provided for download. **Note! Exporting records includes all defined columns((Columns containing binary data like files and images are basically available for export, but excluded from exporting to CSV!)) and thus supercedes authorizations for viewing columns.** |none | 
- 
-^ **since 0.1.12** ^^^ 
- 
-|print |See separate print version listing all matching records in a vanilla document. |none | 
- 
-|viewlog |Export table'​s changelog to downloadable CSV file. |none | 
- 
-^ **since 0.3.0** ^^^ 
- 
-|admin |See and edit row-related ACL rule sets. |none | 
- 
-== Column-Related Authorizations == 
- 
-Column-related authorizations are given as options in fourth field of a single column definition. See the related section above for basic syntax for doing this. The table below is listing the limited set of authorizations available in context of a single column. 
- 
-^ Authorization ^ Description ^ Authorized if rule is omitted((administrators are always authorized)) ^ 
- 
-|view |View column in any record. |all users | 
- 
-|edit |Edit column in any record. |none | 
- 
-A special case of combining authorizations ''​view''​ and ''​edit''​ is supported as well: If a user or group of users may edit a column, but mustn'​t view it, then it is available for editing on creating a new record, only. 
- 
-In the opposite case a column appears to be read-only. However, according to current implementation this affects the provided single-record editor, only. The column'​s value is stored internally and then written back to database without change nevertheless. This might cause some side-effects such as DB server rejecting to write that column and thus failing to save the whole record. In addition this sort of read-only column is still available for edit to administrators since they are always granted every supported authorization. **As this behaviour might be desired in selected situations it is kept. See the column option ''​readonly''​ to completely suppress writing a column!** 
- 
-== Row-Related Authorizations == 
- 
-In version 0.3.0 support for row-related authorizations has been added by introducing new column type ''​acl''​. A table may include exactly one column of type ''​acl''​. This column is then considered to contain an authorization rule set overriding table-related authorizations in context of an individual record. A limited set of table-related authorizations is available for overriding, only: ''​view'',​ ''​inspect'',​ ''​edit'',​ ''​delete'',​ ''​download''​. See the list of table-related authorizations above for detailed information on each of these authorizations. 
- 
-A rule set is a semicolon-separated list of one or more authorization rules. Every such rule is prefixed by ''​may'',​ the authorization'​s name and an assignment operator. See the next section for information on a single rule's syntax. Empty rules like 
- 
-<​code>​mayedit=</​code>​ 
- 
-are ignored. It is required to provide a non-empty rule to override any table-related authorization. Thus, it's now supported to use rule ''​!@all''​ to revoke authorizations from every non-administrative user granted otherwise, e.g. in context of table or by default. The special rule element ''​@none''​ has been introduced in 0.3.0, too, as a negation-free alias of ''​!@all''​. 
- 
-**Example** 
- 
-Consider table granting ''​view''​ and ''​edit''​ authorization to a user ''​userA''​ while revoking it from user ''​userB''​ and everyone in group ''​groupA''​. Then the following row-related authorization rule set might be used to revoke ''​edit''​ authorization from ''​userA'',​ to grant ''​view''​ authorization to users in ''​groupA''​ and to grant ''​view''​ and ''​edit''​ authorizations to ''​userB''​. 
- 
-<​code>​mayview=userB,​@groupA;​mayedit=!userA,​userB</​code>​ 
- 
-**How to Manage Row-Related Authorizations** 
- 
-Any user may be granted new table-related authorization ''​admin''​ which is required to see and edit columns of type ''​acl''​. Listing this column in table requires explicit request using column option ''​visible''​. 
- 
-== Authorization Rule Syntax == 
- 
-An authorization rule is a comma-separated list of user and group names as used in DokuWiki. In addition you may precede every name by an exclamation mark to negate match. Additionally you may use special name ''​@ALL''​ to select matching all users. By introducing row-related ACL rule sets in 0.3.0 negating the special selector ''​@ALL''​ became required to revoke access on a row from any non-administrative user granted otherwise. 
- 
-Authorization rules are processed left to right. Basically a set custom rule rejects authorization. As soon as a match is granting access processing left rule components is stopped. 
- 
-**Some Rule Examples** 
- 
-<​code>​@editors</​code>​ 
- 
-All users in group ''​editors''​ are authorized. 
- 
-<​code>​@editors,​!userA</​code>​ 
- 
-All users in group ''​editors''​ are authorized. 
- 
-<​code>​!userA,​@editors</​code>​ 
- 
-All users but ''​userA''​ in group ''​editors''​ are authorized. 
- 
-<​code>​@editors,​userB</​code>​ 
- 
-All users in group ''​editors''​ and user ''​userB''​ are authorized. 
- 
-Empty authorization rules are ignored. 
- 
-=== Hard-coded Filter Definitions === 
- 
-Since release 0.1.8 it's possible to have a filter definition hard-coded into your page's source code. This definition is included as attribute to the tag, thus being a "Table Option"​. **Versions up to 0.2.1** do not support spaces in table option values, so the filter definition is considered to be URL encoded. **Versions 0.2.2 and later** support quoted string to contain spaces so URL encoding isn't supported here anymore. 
- 
-A definition consists of one or more components each separated by single (non-URL-encoded!) ampersand or pipe character selecting either intersection or union of components'​ matches.((There is no support for controlling order of filter application,​ e.g. by using parentheses or similar. This order basically depends on used DB backend.)) Each component consists of a column'​s name, an operator'​s name and the value to that operator, e.g. 
- 
-<​code>​surname like %son</​code>​ 
- 
-properly encoded as (**in versions 0.2.1 and earlier**) 
- 
-<​code>​surname+like+%25son</​code>​ 
- 
-matching all records having surnames ending with ''​son''​. 
- 
-Some supported operators don't take arguments and thus you don't need to provide any value after operator. Valid operators are 
- 
-* ''​like''​ (substring matching), 
- 
-* ''​nlike''​ ("not like", matches if ''​like''​ would not match), 
- 
-* ''​eq''​ (equal), 
- 
-* ''​lt''​ (less than), 
- 
-* ''​gt''​ (greater than), 
- 
-* ''​ne''​ (not equal), 
- 
-* ''​le''​ (less or equal) and 
- 
-* ''​ge''​ (greater or equal). 
- 
-Additionally the following operators are supported since 0.3.0 not taking any operand value. They apply to columns of type boolean, only. 
- 
-* ''​isset''​ (matching on set boolean value) 
- 
-* ''​isclear''​ (matching on unset/clear boolean value) 
- 
-On using ''​like''​ or ''​nlike''​ the value may use ''​%''​ as a wildcard matching any sequence of arbitrary characters. If no such ''​%''​ is found in value the latter is automatically adjusted to start and end with that wildcard enabling simple substring searching. 
- 
-Any invalid component definition is ignored. 
- 
-== Markup in Operand Values == 
- 
-Since release 0.3.3 operand values may include markup as described for providing default values below. See that section for more information. 
- 
-=== Default Values === 
- 
-On inserting new records the table definition may define //default values// initially set in single-record editor. Default values are supported per column using column option ''​default''​. It is available on every column but file/image columns. The assigned value is processed differently according to the column'​s type of value. 
- 
-Boolean columns may take default values like ''​true'',​ ''​on'',​ ''​false''​ or ''​off''​ selecting one of the two valid states. Selectors (''​enum''​ and ''​related''​) take one of the available options'​ value. All else columns take default values as strings as provided. 
- 
-== Markup in Default Values == 
- 
-Versions 0.3.2 and above support markup enabling to embed context-dependent data in provided string values. This markup is given as 
- 
-<​code>​%{keyword}</​code>​ 
- 
-with ''​keyword''​ being replaced by one the keywords listed in table below. Every marker is replaced by the related keywords actual value in current context. Replacing a keyword includes the percent sign and the surrounding curly braces. If a marker is using an unknown keyword it is replaced by the empty string. All keywords are case-insensitive. 
- 
-^ Keyword ^ Since Version ^ Description of Related Value ^ 
- 
-|''​date.<​format>''​ | 0.3.3 |current date/time formatted according to provided format string replacing ''<​format>''​ in given keyword; the format string syntax is in accordance to PHP's function ''​date()'',​ e.g. ''​Y-m-d''​ thus resulting in keyword ''​date.Y-m-d''​ | 
- 
-|''​wiki.user''​ | 0.3.2 |login name of currently authenticated wiki user | 
- 
-|''​wiki.groups''​ | 0.3.2 |all groups currently authenticated wiki user is a member of | 
- 
-As an example using an option like 
- 
-<​code>​ ... default="​added by '​%{wiki.user}'"</​code>​ 
- 
-on a column the related field in editor on inserting new record is initialized to 
- 
-<​code>​added by '​testuser'</​code>​ 
- 
-**if currently authenticated user is ''​testuser''​**. 
- 
-=== A full example === 
- 
-<​code><​database addresses width=100% mayinsert=@user>​ 
- 
- 
- 
-# basic data 
- 
-surname, , Surname, 32 visible required @1 
- 
-first_name, , First Name, 32 visible required tabindex=2 
- 
-gender, enum male / female, Gender 
- 
-birthday, date, Birthday, nodefault 
- 
-# contact information 
- 
-address, string, Address, 64 
- 
-phone, phone, Telephone, 32 
- 
-email, email, E-Mail, 128 visible required @3 mayview=soletan 
- 
-website, url, Website 
- 
-# additional/​optional stuff 
- 
-married, bool, Married? 
- 
-children, integer, # Children, required 
- 
-employed, bool, Employed?, booltype=xmark 
- 
-salary, monetary, Salary 
- 
-daytime, time, favourite daytime 
- 
-start, datetime, Start of Subscription,​ nodefault 
- 
-time_100m, decimal, Time for 100m 
- 
-foto, image, Your Photo, visible 
- 
-vcard, file, Your VCard 
- 
-# internal data 
- 
-comment, , Comment 
- 
-validated, bool, Validated?, booltype=int required 
- 
-</​database></​code>​ 
- 
-==== Database Setup ==== 
- 
-=== Local Databases === 
- 
-The plugin is managing local database files in scope of current page using SQLite. You don't need to provide any additional information to access database file related to current page. 
- 
-Of course it is possible to select a different database file using table option ''​database''​ as described in section above. 
- 
-=== Remote Databases === 
- 
-<note warning>​This feature is currently tested to work with MySQL, only, and thus you may encounter malfunctions on trying to use it with other PDO drivers.</​note>​ 
- 
-In addition the plugin features connecting to a remote database server integrating a table hosted in a remote database. 
- 
-<note warning>​Integrating remotely hosted tables may irreversibly drop data there.</​note>​ 
- 
-Accessing a remote database server requires 
- 
-* a DSN selecting database server type, host and name. 
- 
-* a username and a password for authentication 
- 
-The DSN is given in opening tag using attribute ''​database''​. You'll need to precede it with an ''​@''​ to make it distinguishable from a local file's pathname. 
- 
-As a page's source is often visible to every visitor including unauthenticated guests directly providing username and password in same location isn't supported due to security considerations. You need to 
- 
-- provide them in your wiki's site configuration choosing an arbitrary "slot name", e.g. ''​mydbsrv''​. See below for how to achieve this in your version of database2. 
- 
-- provide the selected slot name in attribute ''​auth''​ in opening tag, e.g.:<​code><​database tablename auth=mydbsrv database=@mysql:​host=localhost;​dbname=mydb></​code>​ 
- 
-The selected slot name mustn'​t contain spaces and should consist of ASCII letters and digits, only. 
- 
-== Versions prior 0.2.0 == 
- 
-To provide username and password in a release of database2 prior to version 0.2.0 you need to append some code like this to your ''​conf/​local.protected.php''​((This is used to contain custom configuration data not overwritten by Configuration Settings Tool bundled with DokuWiki. If the file is missing you may create it. Don't miss leading and trailing PHP tags - ''<?​php''​ and ''?>''​)):<​code>​ 
- 
-$conf['​database2'​]['​mydbsrv'​]['​username'​] = '​johndoe';​ 
- 
-$conf['​database2'​]['​mydbsrv'​]['​password'​] = '​foobar';​ 
- 
-</​code>//​(replace "​johndoe"​ by your username and "​foobar"​ by your password)// 
- 
-== Versions 0.2.0 and later == 
- 
-Due to revising integration with DokuWiki'​s Configuration Manager the way of providing username and password has changed. Now there is a multi-line text input as a configuration option. You may enter all slot definitions used on any page of your Wiki there. Here comes the syntax. 
- 
-Each slot definition is given on a separate, single line starting with the slot's name. Using assignment operator the pair of username and password is assigned to the slot compiled into single string using colon as separator. According to the example above the same slot definition looks like this now: 
- 
-<​code>​mydbsrv=johndoe:​foobar</​code>​ 
- 
-Using quotes around the string after assignment operator is optional. Doing so you need to keep in mind support for escaping sequences. The username **mustn'​t** contain any colon, the password **may** contain colons. 
- 
-==== Query options ==== 
- 
-Since 0.4.3 it's possible to select one of the supported actions on a table using related variable in a page query. In addition supporting related query options have been introduced as well. 
- 
-**This feature is somewhat rudimentary and thus provides little convenience,​ only.** You are considered to be familiar with HTTP GET queries. 
- 
-=== Basic Format === 
- 
-Provide as much query elements as desired. As usual an element consists of a name, an assignment operator and a value. Names recognized by database2 start with prefix ''​db2do''​ and end with a suffix ''​[n]''​ with ''​n''​ being replaced by number of ''<​database2>''​ on current page, e.g. ''​[1]''​ for addressing the topmost instance of a ''<​database2>''​ tag. Between prefix and suffix there is a command or option name optionally followed by a row's ID a command is applied on. 
- 
-The whole name does not include any whitespace. They are case-sensitive. 
- 
-Command names start with ''​cmd'',​ related query options start with ''​opt''​. Commands usually take an arbitrary value evaluating to true. 
- 
-=== Select Detail View === 
- 
-<​code>​...?​db2docmdinspect97[2]=y</​code>​ 
- 
-This selects to open detail view (inspector) on record with ID 97 in table integrated using second ''<​database2>''​ tag on page retrieved. 
- 
-<​code>​...?​db2docmdinspect97[2]=y&​db2dooptnoctl[2]=1</​code>​ 
- 
-This additionally requests to hide the controls rendered at bottom of same detail view otherwise. 
- 
-==== Troubleshooting ==== 
- 
-=== Tables have text controls instead of icons === 
- 
-If used set of icons isn't available the button'​s "​alt-text"​ is rendered instead breaking the table layout due to the additional space consumed by those labels. 
- 
-All icons are included with the plugin and thus installed to the subfolder 
- 
-<​code>/​lib/​plugins/​database2/​icons</​code>​ which must be available for HTTP requests. To locate the reason for icons being unavailable you should first try to request one manually, e.g. using a URL similar to this one: 
- 
-<​code>​http://​www.mywikisite.tld/​lib/​plugins/​database2/​icons/​add.gif</​code>​ 
- 
-If you see a **404 (Not found)** you're probably using a path layout different from URL prefixes used. 
- 
-If you see a **403 (Forbidden)** accessing icons in plugin'​s folder is prohibited. As DokuWiki isn't protecting these folders by default there is either an additional .htaccess file in one of /lib, /​lib/​plugins and so on. None of them should contain Deny/Allow directives to protect accessing the folder. 
- 
-Finally, check your site's file permissions. DokuWiki might be using permissions 0660 for files and 0770 for folders. If neither owner nor group of icon files match your web server'​s user or group the latter mustn'​t access icon files for retrieval and thus failing on 403 then. 
- 
-=== database2 tries to create additional tables === 
- 
-database2 is using up to three further tables in a connected database to serve in different situations. These tables are created automatically on demand. If your setup isn't granting permissions to create these tables selected actions on a table might fail. The related error codes will claim lack of permission to perform CREATE statements or to create a table. 
- 
-To circumvent this, it might be required to create these tables manually using database account with elevated permissions. Here come the CREATE statements required to add these hidden tables. 
- 
-This first table is used to manage locks to mutually exclude parallel requests for editing a single record in a table. 
- 
-<​code>​CREATE TABLE __locks ( 
- 
-tablename CHAR(64) NOT NULL, 
- 
-record INTEGER NOT NULL, 
- 
-username CHAR(64) NOT NULL, 
- 
-obtained INTEGER NOT NULL, 
- 
-PRIMARY KEY ( tablename, record ) 
- 
-)</​code>​ 
- 
-The next table is required on adding new records to an existing database. 
- 
-<​code>​CREATE TABLE __keys ( 
- 
-tablename CHAR(64) NOT NULL PRIMARY KEY, 
- 
-recent INTEGER NOT NULL 
- 
-)</​code>​ 
- 
-Finally the third one is used to log changes to records and tables. 
- 
-<​code>​CREATE TABLE __log ( 
- 
-tablename CHAR(64) NOT NULL, 
- 
-rowid INTEGER NULL, 
- 
-action CHAR(8) NOT NULL, 
- 
-username CHAR(64) NOT NULL, 
- 
-ctime INTEGER NOT NULL 
- 
-)</​code>​ 
- 
-=== Duplicate entry error on adding/​copying record === 
- 
-database2 might be used to manage a database already in use, of course. If you intend to enable managing records through database2 you might need to manually fix content of table <​nowiki>​__keys</​nowiki>​ yourself as it is used to select next available values for numeric primary keys on adding/​copying records. 
- 
-An ''​AUTO INCREMENT''​ attribute as supported by MySQL isn't found in every database engine supported by PDO and thus database2 is using a different approach of managing its own pool of automatically incrementing keys. This pool is found in table <​nowiki>​__keys</​nowiki>​ containing records each consisting of a related table'​s name and the most recently applied key used on previously adding/​copying record to that table. 
- 
-On using different methods of selecting next available key you obviously can't manage your database using more than one application at the same time. 
- 
-=== Occurrences of database2 tag are ignored / My tables have gone!! === 
- 
- 
- 
-If you've upgraded from versions prior to 0.4.0 or installing release 0.4.0+ you need to enable support for database2 tags on all or selected pages of your Wiki. This is required to prevent severe vulnerability. 
- 
-Open your site's configuration manager and look for the section related to plugin ''​database2''​. There is one option for enabling database2 on all pages, but you're strongly discouraged from using that unless you're totally sure about what you do. Instead you should use patterns in the second option ''​enablepages''​ to enable database2 on selected pages, only. 
- 
-Be aware of combining enabled suppport for database2 and some plugins like ''​discussion''​ on a single page introduces severe vulnerability to your whole site/​hosting,​ **nevertheless**! Read more on this in sections **Known Vulnerabilities** and **Enabling database2**,​ above!