With table options you can

• Connect to a remote database (server, authentication)
• Control “look” of resulting table (width, colors, …)
• Add functions like pager, filter, sorting, …
• Authorize users to edit, delete, insert, view, export, filter records of table
• Authorize users to create, delete, backup, restore tables in database

<database2 OPTION>

Table options are provided in opening tag. Please respect the following rules:

1. Options are delimited by whitespace
2. Successive whitespace characters (space, tab, newline) are treaded as one
3. Options can have values, which are assigned by equal-char (=). There may be whitespace around the equal-sign. The value starts with the first non-whitespace char and ends with a whitespace
4. To have whitespace or other chars in values, enclose them in single/double-quotes. Quotes inside a quoted value had to be escaped by a preceeding \. To use escape-char itself, double-escape it \\.
5. Each option has an internal default-value (see table below)
6. Options are not case-sensitive
7. Comments are allowed after # or // anywhere between options. All chars up to the next newline are treated as comment.

<database2 OPTION1 OPTION2=VALUE OPTION3 = "VALUE" OPTION4="">

can be written also as: <code>
<database2
  OPTION1
  OPTION2=VALUE # comments are not part of value
  OPTION3 = "VALUE"
  OPTION4="" // an empty value
>

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.

column_name [, [ column_type ] [, [ column label ] [, [ column options ] ] ] ]

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.

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.

The second field selects the type of values stored in column. If this field is omitted or empty, text is selected by default.

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.

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.

name = "value"

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.

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

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.

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

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

mayedit=

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.

mayview=userB,@groupA;mayedit=!userA,userB

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.

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

@editors

All users in group editors are authorized.

@editors,!userA

All users in group editors are authorized.

!userA,@editors

All users but userA in group editors are authorized.

@editors,userB

All users in group editors and user userB are authorized. Empty authorization rules are ignored.

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.⁴⁾ Each component consists of a column's name, an operator's name and the value to that operator, e.g.

surname like %son

properly encoded as (in versions 0.2.1 and earlier)

surname+like+%25son

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.

Since release 0.3.3 operand values may include markup as described for providing default values below. See that section for more information.

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.

Versions 0.3.2 and above support markup enabling to embed context-dependent data in provided string values. This markup is given as

%{keyword}

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.

As an example using an option like

 ... default="added by '%{wiki.user}'"

on a column the related field in editor on inserting new record is initialized to

added by 'testuser'

if currently authenticated user is testuser.

<database2 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
</database2>

The plugin is managing local database2 files in scope of current page using SQLite. You don't need to provide any additional information to access database2 file related to current page. Of course it is possible to select a different database2 file using table option database as described in section above.

<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 database2 server integrating a table hosted in a remote database. <note warning>Integrating remotely hosted tables may irreversibly drop data there.</note> Accessing a remote database2 server requires * a DSN selecting database2 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.:

<database2 tablename auth=mydbsrv database=@mysql:host=localhost;dbname=mydb>

The selected slot name mustn't contain spaces and should consist of ASCII letters and digits, only.

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⁵⁾:

$conf['database2']['mydbsrv']['username'] = 'johndoe';
$conf['database2']['mydbsrv']['password'] = 'foobar';

(replace “johndoe” by your username and “foobar” by your password)

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:

mydbsrv=johndoe:foobar

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.

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.

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.

...?db2docmdinspect97[2]=y

This selects to open detail view (inspector) on record with ID 97 in table integrated using second <database2> tag on page retrieved.

...?db2docmdinspect97[2]=y&db2dooptnoctl[2]=1

This additionally requests to hide the controls rendered at bottom of same detail view otherwise.

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

/lib/plugins/database2/icons

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:

http://www.mywikisite.tld/lib/plugins/database2/icons/add.gif

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 is using up to three further tables in a connected database2 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 database2 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.

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

The next table is required on adding new records to an existing database.

CREATE TABLE __keys (
tablename CHAR(64) NOT NULL PRIMARY KEY,
recent INTEGER NOT NULL
)

Finally the third one is used to log changes to records and tables.

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
)

database2 might be used to manage a database2 already in use, of course. If you intend to enable managing records through database2 you might need to manually fix content of table __keys 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 database2 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 __keys 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 database2 using more than one application at the same time.

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!