DBConnectorPlugin < System

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

%TOC%

---++ 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 [[DBConnectorPluginCreateTableQuery][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:%BR%
%BR%
| *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
%BR%. 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.WebHome you call it :
<pre>my %result getValues("System",'WebHome',('samplefield1','samplefield2'));
accessing results this way
print %result->{'bar'};
</pre>


---+++ 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.WebHome you call it :
<pre>my $result getValues("System",'WebHome',('samplefield1','samplefield2'));
accessing results this way
print $result->{'bar'};
</pre>

---+++ 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 Syste.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}
<em>_you can disallow the creatinof table with unchecking $Foswiki::cfg{Plugins}{DBConnectorPlugin}{allowCreatedb} in the Configuration-Center _</em>
you call the rest handler this way, creating a data for the web "TheWeb"
<pre>%SCRIPTURL{rest}%/DBConnectorPlugin/createdb?topic=TheWeb.WebHome </pre>
*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 [[DBConnectorPluginCreateTableQuery][topic]] to see, which query is used to create the table for a web, where information is stored
   1 create test table
   <form action="%SCRIPTURL{rest}%/DBConnectorPlugin/createdb?topic=%WEB%.%TOPIC%"" method="get">
 
| Create table for: | <input type="text" name="targetWeb" value="%WEB%"> | |
| Query from web | <input type="text" name="sourceWeb" value="%WEB%"> | |
| Query to create table located in topic | <input type="text" name="sourceTopic" value="DBConnectorPluginCreateTableQuery"> | <input type="submit" name="Create Table" value="Create Table"> |
</form>

   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: | &copy; 2009, Collaborganize [[www.collaborganize.com]] |
|  License: | GPL ([[http://www.gnu.org/copyleft/gpl.html][GNU General Public License]]) |
|  Plugin Version: | 15 Mar 2009 (V0.9) |
|  Change History: | <!-- versions below in reverse order --> |
|  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%BR% * generalized createDB to be able to use other topics for creation of tables%BR% * 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%BR% * 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%BR% * Added locking/unlockin topics when fields are edited%BR% * Added Macro to show Edit-Button and to display Field-Values in topics%BR% * 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/%TOPIC% |
|  Feedback: | http://foswiki.org/Extensions/%TOPIC%Dev |
|  Appraisal: | http://foswiki.org/Extensions/%TOPIC%Appraisal |
 __Related Topic:__

-- Foswiki:Main/EugenMayer - 15 Dez 2008
Form definition 'PackageForm' not found
PackageForm edit

ExtensionClassification	Data and files, Editing and Editors, Libraries
ExtensionType	PluginPackage
Compatibility
DemoUrl	http://
DevelopedInSVN	Yes
ModificationPolicy	PleaseFeelFreeToModify
This topic: System > DBConnectorPlugin
Topic revision: 15 Dec 2008, EugenMayer (raw view)
Copyright © CC-BY-SA by the contributing authors. All material on this collaboration platform is copyrighted under CC-BY-SA by the contributing authors unless otherwise noted.
Ideas, requests, problems regarding Foswiki? Send feedback