Server Configuration

WebGUI Setup

As of v7.5.11, SQL Form is no longer distributed with WebGUI. It is available via Subversion at https://svn.webgui.org/sqlform/WebGUI/. The websvn interface is here.

Installation

• Export from SVN -- svn export https://svn.webgui.org/sqlform /tmp/sqlform
  
• Copy files to /data directory
  • We recommend setting up a /data/site_lib directory as described in Override Core Modules for the files in WebGUI/lib/WebGUI (e.g., Asset, Help, i18n)
  • The other files in www/extras will need to be placed into the core directories 
• Restart the modperl service (sudo /data/wre/sbin/wreservice.pl --restart modperl)
• Import the editor templates
  • Go to the Asset Manager
  • Navigate to Root > Import
    
  • Select Browse from the Packages box below the assets table
  • Find the docs/SQLForm-templates.wgpkg file
  • Import the file
• Setup SQL (replace WEBGUIDB with the name of your WebGUI database; if you don't know what it is check your conf file in /data/WebGUI/etc). Execute following commands:
  
  • source /data/wre/sbin/setenvironment.sh
  • mysql -u root -p WEBGUIDB < docs/SQLForm-install.sql
    
• Setup an external database link before attempting to use the module (see below)
  

Database Setup

The SQL Form object provides a form and search function to an external database. You must setup an external link and provide privileges to the assigned user. To grant all privileges to a new MySQL table use the following sql (replace DATABASE and USER with your local settings):

  create database DATABASE;
  grant all privileges on DATABASE.* to USER;

Then add a new Database link via the Databases option in the Admin Console.

1. Set the DSN (something like the following for a MySQL database):

  DBI:mysql:DATABASE;host=localhost;port=3306

2. Set the user and password.

3. Edit the Allowed Keywords as needed. You'll at least want to include 'insert' and 'update'. Here's the complete list of options:

select

describe

show

insert

update

alter

create

delete

index

4. Save the database link settings.

Adding a new SQL Form

1. Add the new asset
2. Enter the table name into the Properties tab
3. Check the "Import" checkbox if the table already exists and is being imported (WARNING: This will permanently alter your table schema)
4. Select the appropriate database to use
5. Save your new form
6. If you run into any errors, check the webgui.log file (/data/wre/var/logs).
   

Usage Notes

Customizing Search Results 

You can customize which fields are show in the search results. To do this, first edit the field you want to hide. Then click on the 'Search and Summary tab and set 'Show in search results' to 'No'.

Relationships

• One-to-Many - Use a Checklist/varchar field type to store multi-select values for a one-to-many style relationship (e.g., a restaurant may be located in multiple cities). Use the Form Population tab of the field properties to define the related table and column. When doing SQL queries on this type of field use a LIKE statement and pass in the id of the value from the related table as follows (if using SQL Search asset, set the form:KEY_PARAM placeholder parameter):
  

   merchants.area LIKE  concat('%',?,'%') 
  

• Many-to-Many - You'll need to use a link table to build this solution. We like to name the SQL Form that manages the link table "Assign NAME to NAME".