DBConnectorPlugin

Enables you to use simple methods to store and read data for topics. The Editor is included in the plugin - use it right away

Introduction

This plugin enables you to store and read additional topic data in a variable backend very easy. The setup e.g. with SQLite should be straight forward and easy, if not, leave me a note. In addition, the database is stored on filesystem-level in the working_areas and is backuped by the old great cp / rsync way like the general topic data.

In addition, the Connector watches fields you choose to be updated including references on a topic, when this topic gets renamed in Foswiki. This is excatly the same behavior you get when renaming topics with searching for references, to rename them. This behavior is enabled by default but can be disabled or only enabled for specific webs

This plugin also includes a default user interface to let the user edit such fields you defined in the database in Foswiki with the WYSIWYG. This UI is generalized and can be used with every field and can be modified to fit your special needs easy. This means you dont need to write any code to let users modify a field of a topic, all you need is to add a button this way %EDITFIELDBUTTON{ field="samplefield2" }% .. but just look at the Live Demo

Usage

Getting started

This plugin provides an example on using this plugin with SQLite when you used the optional example installation. There will be a table, named by the Web-Name with

• a primary key "topic_id" which will hold the topic name as identifier
• samplefield1: And integer field
• samplefield2: and text field

You can also look at topic to see, which query was used to create the table and also modify it

Example application

You are alreay using a example application, because anything is included in this plugin. With the build in Field-Editor, you can just use this link to edit one field of the example database, when you have installed it:

MACRO EXAMPLE	MEANING	LIVE DEMO
%EDITFIELDBUTTON{ field="samplefield2" }%	displays the edit button :	%EDITFIELDBUTTON{ field="samplefield2" }%
%DISPLAYDBFIELD{ field="samplefield2" }%	you display the current value	%DISPLAYDBFIELD{ field="samplefield2" }%

The plugin TopicSpecificNavigationPlugin is a good and simple example on how to use DBConnectorPlugin

Customizing Field-Editor User Interface

This is much the same thing else with other templates. The Field Editor template (editdbfield.tmpl) is completely based on the edit.tmpl. The editor respects skins, so if you create a new editdbfield.yourskin.tmpl, you will be able to customize the Editors Look&Feel
. In Addition, you can use editdbfieldYOURTYPE.yourskin.tmpl to define different editing modes depending on a field type, you invoke the editor with.

Edit-Button for Field-Editor

With the macro %EDITFIELDBUTTON% you can let apear a button for editing a field with the Field-Editr

Parameter	Default	Description
web	Current web	Sets the web of the topic, where the topic is located
topic	Current topic	Sets the topic name of which you want to edit the field
field	-	required Name of the field you want to edit
type	""	optional Type of the field. This is used to use different edit templates. Look Customizing Field-Editor User Interface for informations
format	""	a string including %URL% which is used as the output, while %URL% is replaced with the edit-url

buttonName

$field

Sets the name of the button, which will be displayed to the user

Example: %EDITFIELDBUTTON{ field="samplefield2" }% -> %EDITFIELDBUTTON{ field="samplefield2" }%

Display a value in a topic

With the macro %DISPLAYDBFIELD% you can let display the value of a field in a topic

Parameter	Default	Description
web	Current web	Sets the web of the topic, where the topic is located
topic	Current topic	Sets the topic name of which you want to display the field
field	-	required Name of the field you want to display
format	field	here you can use a format string, while %VALUE% get replaced by the field value
raw	0	if you set this to true, the result field will not be rendered, so no TML to html conversion

Example: %DISPLAYDBFIELD{ field="samplefield2" }% -> %DISPLAYDBFIELD{ field="samplefield2" }%

Methods

getValues( $web, $topic, @fields ) -> ( %result )

get values for out of the database

• $web - Web name, required, will be used as table
• $topic Topic name, required, will be used as identifier/key
• $fields - reference on an array of field names, optional. This fields are fetched out of the db
• $checkAccess if this is zero, access is not checked

Return: ( %result ) Result, a hash with each fetched field-name as ke

if you want to fetch fields ('samplefield1','samplefield2') from System you call it :

my %result getValues("System",'WebHome',('samplefield1','samplefield2'));
accessing results this way
print %result->{'bar'};

getValues( $web, $topic, @fields ) -> ( %result )

get values for out of the database

• $web - Web name, required, will be used as table
• $topic Topic name, required, will be used as identifier/key
• $fields - reference on an array of field names, optional. This fields are fetched out of the db
• $checkAccess if this is zero, access is not checked

Return: ( %result ) Result, a hashref with each fetched field-name as ke

if you want to fetch fields ('samplefield1','samplefield2') from System you call it :

my $result getValues("System",'WebHome',('samplefield1','samplefield2'));
accessing results this way
print $result->{'bar'};

deleteEntry( $web, $topic ) -> ( )

deletes an entry out of the database $web, identiefied by $topic

• $web - Web name, required, will be used as table
• $topic Topic name, required, will be used as identifier/key

_createDB( $session ) -> ( )

if you want to create a initial table for a web, where informations can be stored for topics, you got to run this rest handler to initialize/create it. The query defined on topic DBConnectorPluginCreateTableQuery is used as a template for the query.

• %TABLENAME% gets expanded to the corresponding Web, when you create the table;
• %TOPICNAME% gets expanded to the topic. This should be actually always be a existing topic. In normal cases, this value is not needed in the template#
• %DBCONTABLEKEYFIELD% gets expanded to the primary key which is defined in the Configuration-Center $Foswiki::cfg{Plugins}{DBConnectorPlugin}{TableKeyField}

_you can disallow the creatinof table with unchecking $Foswiki::cfg{Plugins}{DBConnectorPlugin}{allowCreatedb} in the Configuration-Center _ you call the rest handler this way, creating a data for the web "TheWeb"

https://foswiki.org/bin/rest/DBConnectorPlugin/createdb?topic=TheWeb.WebHome 

Attention: Only admin user will be allowed to call this rest-handler

Installation instructions

• just use the installer attached to the topic or even easier, use the configure-system/Extension to easy install it trough an user-interface

Configuration

• Use the Configuration Center ( bin/configure ) to configure the driver you want to use, the DSN.

Enable/Disable autoupdating topic links in fields

Decide if you want links to be auto updated, if a topic gets removed and which webs should be searched for such topic-references

Optional: Create table

1. check this topic to see, which query is used to create the table for a web, where information is stored
2. create test table

Create table for:
Query from web
Query to create table located in topic

1. now you can query the database with update and get querys like described above

Note: You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the Plugin on the server where Foswiki is running.

ToDo

• provide easy way to set fields for a topic to the default-values
• auto-creating of tables for all webs and stuff like that

• Set SHORTDESCRIPTION = Enables you to use simple methods to store and read data for topics. The Editor is included in the plugin - use it right away

Plugin Author:	Foswiki:Main.EugenMayer
Copyright:	© 2009, Collaborganize www.collaborganize.com
License:	GPL (GNU General Public License)
Plugin Version:	15 Mar 2009 (V0.9)
Change History:
15 Mar 2009:	fixed a small problem when creating tables
05 Feb 2009:	fixed some "fixed" script-url calls
28 Jan 2009:	Made a workarround for the bug in the debian(etch stable) lib-perl-sqlite package, which yet fails on named binding due this bug
22 Dez 2008:	Fixed editing with the WYSIWYG Editor, content is now saved with TML syntax like topics are
17 Dez 2008:	* Fixed some bugs. Also disabled the locking, because if something is locked, and "i try anyway" the redirect is wrong * generalized createDB to be able to use other topics for creation of tables * added form on the plugin-topic to be easy able to create tables for webs
17 Dez 2008:	* added access checks, so users can`t change data for a topic in the database, if they have CHANGE rights on the topic inf foswiki * Added default Field-Editor User Interface to edit fields of a topic with the WYSIWYG out of the box, respectfully added handler for that. This UI is build to be reused and is customizeable * Added locking/unlockin topics when fields are edited * Added Macro to show Edit-Button and to display Field-Values in topics * createDB is now more secure, only allowed to admins anymore
16 Dez 2008:	extended functionality to update references(links) on topics, when this topcis are renamed. Tested, bugixed.
15 Dez 2008:	initial release
Foswiki Dependency:
CPAN Dependencies:	DBI
Other Dependencies:	your backend, e.g. DBD::SQLite
Perl Version:	5.005
Add-on Home:	http://foswiki.org/Extensions/DBConnectorPlugin
Feedback:	http://foswiki.org/Extensions/DBConnectorPluginDev
Appraisal:	http://foswiki.org/Extensions/DBConnectorPluginAppraisal

Related Topic:

-- Foswiki:Main/EugenMayer - 15 Dez 2008