EZDB 2.5 ©

By Stephane Barde
webmaster@ezperl.com
Dec 25 1999
http://www.ezperl.com

@table of content

# COPYRIGHT NOTICE:
#
# Copyright 1999 Stephane Barde. All Rights Reserved.
#
# This program is being distributed as a FREEWARE.
#
# It may be used and modified by anyone, as long as this copyright notice and the header
# above remain intact, but any usage should be registered. (See the
# chapter XIII- for registration information.) By using this
# program you agree to indemnify Stephane Barde from any liability.
#
# Selling the code for this program without prior written consent is
# expressly forbidden. Obtain permission before redistributing this
# program over the Internet or in any other medium. In all cases
# copyright and header must remain intact.
#
# Contributions are welcome and will be used exclusively to enhance
# the script (contact EZ Perl at webmaster@ezperl.com)

# I would off course appreciate to have some feedback about the
# use you are doing with this script.

# It also would be nice to make a link to the EZ Perl site:
# http://www.ezperl.com

# VERSION HISTORY:

# 2.5 12/25/99

# - Cursors (relational display)

# - Direct Full Display parameter usable in the URL

# - _LOGIN_ and _SESSION_ usable when displaying the db form

# - Select form elements parsed only for column name is used in a <select…> tag

# - File generation: named after columns, extension configurable.

# 2.1b 11/07/99
# - Parameters in the command line
# for queries with max_record_no
# 2.1 08/15/99
# - Static HTML output
# - EZ Wizards
# - Order By Statement
# - Image Upload
# - Headers and footers at group level
# - Control over the direct Full display
# - Text Color for Default display
# - Multiple Selections
#
# 2.0 03/20/99
# - Templates for mail notification.
# - Line Mode Display automatically recognize <tr> tags
# for nice table formatting
# - HTML file auto generation
# - Cascading additions (ex: Member Account Generation)
#
# 2.0 PR1 02/18/99
# - Relational flat databases.
# - Display Mode configurable from the search engine
# - Accuracy of the error messages
# - Possibility to create member account
# - Display line mode vs. tables
#
# 1.5 01/01/99
# - First Shareware release
# - Setup Analyzer
# - Javascript added to handle the problem of checkboxes
# - Some bug fixed (Modify vs. Full display mode).
# - Search revised.
# - Email Notification.
# - Viewing restrictions option at group level.
# - GUI enhanced: menu bar to navigate between system db.
# - Ability to go to the login page with table data.
# - Go to connect screen directly after login.
# - Show authorized table at connection.
# - Display mode configurable at group level.
# - Separate folder for system db
# - Feedback utility removed (useless with v1.3)
# - Pop-down menu for connection.
# - Indexed fields
# 1.3 12/10/98
# - Numerous bug fixed
# - Search engine enhanced: line mode + many options
# - Headers and Footers
# - Mandatory fields and Unique Constraint
# - HTML configurable at group level
# - Support default values for html fields
# - HTML fields in read only access when fisplaying a form
# - Aliases for table path
# - Log options at table level
#
# 1.21 11/06/98
# - Search page by page updated
# - Search result sorted in alphabetical order
#
# 1.2 10/28/98
# - sub display_feedback
# - decrease size of error message
# - enhanced search engine: added the following functions:
# =,=~,=^,!=,!~,!^ plus case sensitivity
# - Auditing function implemented
# - Exit after error message
#
# 1.1 10/01/98 New User Interface + numerous bug fixed
#
# 1.0 09/11/98 FIRST RELEASE



Note: Compatibility
EZDB was tested developed on Unix (Apache / FreeBSD) and Windows (Apache / NT 4.0)



Here is the manual for installing and using EZDB v 2.1:

CONTENT:

...I EZDB files and folders

..II About global variables

.III How to Create a Database and Grant permissions

..IV Create User Account and Group Assignment

...V About HTML's

..VI How to access DIRECTLY a table with default right

.VII The search engine

VIII The Audit function

..IX Cross Table References: How to implement "Relations"

...X Indexes

..XI Cascading Additions

.XII EZ Wizards

XIII Support




Use the Example!:
-----------------

This package comes with the online example (invoicing system and job posting example).
Ifyou have any problem of configuration with your own
database, have a look at it!...

You have to configure the job_home.html page to make it point on your own URLs.

------------*--------------
------------------------------------------------------------------------------
SETUP TAKES 5 MINUTES: Set the file rights properly (chapter 1)
Go to http://www.yourserver.com/cgi-bin/.../ezdb.cgi

CHECK YOUR SETUP: http://www.yourserver.com/cgi-bin/.../ezdb.cgi?setup
------------------------------------------------------------------------------
------------*--------------



I EZDB files and folders:
-------------------------

EZDB v2.1 comes with the following structure:


./
|- ezdb.cgi 755
|
|- ezdb_lib.pl 755
|
|- ezhtml.pl 755
|
|- ezwizard.pl 755
|
|- report.cgi 755
|
|- /system_db/ 777
| |- user_session 666
| |- user_data 666
| |- user_rights 666
| |- tables 666
| |- table_group 666
| |- index 666
| |- db_cookie 666
| |- add_external 666
|
|- /system_db_html/ 755
| |- user_data_form.html 755
| |- ... 755
|
|- /my_db/ 777
| |- invoice 666
| |- product 666
| |- ... 666
|
|- /my_html/ 755
| |- invoice_form.html 755
| |- ... 755
|
|- /log_files/ 777
| |- invoice.log 666
|
|- /mail_templates/ 777
| |- invoice_add.txt 755
|





- The main program is ezdb.cgi: make it -rwxr-xr-x so that you can execute it.

- The package comes with 5 subfolders:

A- system_db: this is the folder containing the system database:
Make it rwxrwxrwx

-> user_session is a system file containing all the active
session. Make it -rw-rw-rw-

-> user_data is a system file containing all the user name,
login and password. Make it -rw-rw-rw-

-> user_rights is a system file containing all the user
permissions. Make it -rw-rw-rw-

-> tables is a system file containing all the flat file
database informations. Make it -rw-rw-rw-

-> table_group is a system file containing all the user group
information relative to each database. Make it -rw-rw-rw-

-> index is a system file containing the indexes for user
defined tables (see chapter IX for detail).
Make it -rw-rw-rw-

-> db_cookie is a system file containing the cookies left by users
navigating from one table to another.
Make it -rw-rw-rw-

-> add_external is a system file containing the cascading additions
definitions.
Make it -rw-rw-rw-

B- system_db_html: this is folder containg all system html files.
Make it rwxr-xr-x

C- my_db is a directory where you can put your database.
Make it rwxrwxrwx (by default, it contains the example db product and invoice)

D- my_html is a directory where you can put your html's.
Make it rwxr-xr-x
Note: these html's will NEVER be accessed directly but will be
scanned by the script. Therefore, it's not a problem to
have html's under your cgi-bin directory.

E- log_files is a directory containing the log files.
Make it rwxrwxrwx.
(Make also the example log file product.log and invoice.log rw-rw-rw-.)

F- mail_templates is a directory containing the templates for mail
notifications.
Make it rwxrwxrwx.


Important Note 1:
-----------------
The structure described here is just a suggestion: you can locate the folders
anywhere, except for ezdb_lib.cgi and the log_files folder which must be under
the root of ezdb.cgi.

Default setup: to get a quick overview of EZDB, keep the structure indicated
here (the package is setup to work with it)

Important Note 2:
------------------
From version 1.3, you can use aliases for database:
For example, if you define the alias phone for the database
/usr/home/database/phone.csv, you just have to use the alias phone to connect
to it.

In consequence:
- You can name your database with extensions: my_db.csv, my_db.asc.
- You can put your database ANYWHERE you want.


Important Note 3: Default Setup
-----------------
- By default, the Administrator login is admin and the password is admin:
You can (and should) change it...
The admin_group is granted full power.
With this login you can connect to the following databases:
- tables
- table_group
- user_data
- user_rights
- index

<<Top

II About global variables:
--------------------------


session_max_duration:
---------------------
$session_max_duration is a variable defining the maximum duration of a session
in seconds. If you log in and want to access the database $session_max_duration
seconds after you signed in, you will have to log in again...

$session_max_duration=86400; # This value clean the session file about every day...

log_max_duration:
---------------------

To prevent the log files to grow too much, log_archiving_duration
define the number of seconds for which a record remains in the log files.
In other words, this defines the range of the backup in seconds:
ex: $log_archiving_duration=86400*7; this would keep a track of change made
during the last 7 days.


system_file_path:
-------------------
This is the path to the system database folder. Default value is ./system_db


Mail variables:
----------------
You can use mail notification to warn users when a record is added, modified,
deleted or roll backed.

$mail_from="stephane\@ezperl.com";
The email of the notifications sender

$mail_signature="The Webmaster";
The signature of the mail.

$mail_routine_path="/usr/sbin/sendmail";
The complete path to the mail subroutine (EZDB was tested with sendmail only).

<<Top

III How to Create a Database and Grant permissions:
---------------------------------------------------


1- Create a new database:
-------------------------

Pretty easy: create a new text file, first line of which is:

pkey|field1|field2|... RETURN (-> new line)

- You can replace the pipe | by anything you want...
- The first field must be pkey (Primary Key)
- YOU MUST PUT A CARRIAGE RETURN AT THE END OF THE FIRST LINE

Make this file -rw-rw-rw- and place it under the folder ./my_db
(that's not a 'must', cf I).
Save it in UNIX MODE (to prevent from having junk ^M appearing after
transfer from DOS to UNIX).

Ex: Here is the database text file I call phone

pkey|name|phone|address|email|*owner|*group_owner|department|manager

(end_of_file you notice the newline caracter!)


Important Note: Specific fields *owner and *group_owner
-------------------------------------------------------

You can use to specific fields to assign record ownership when ADDING
a new entry:

- The field *owner. This field will be automatically be replaced by the
login of the user creating the record.

- The field *group_owner. This field will be automatically be replaced
by the group of the user creating the record.

- You can call these field for read-only access with:
_owner and _group_owner



2- Define Table versus EZDB:
----------------------------

- Launch EZDB (You need to put the script on a server and run it from
a browser).

- Log in as Administrator (By default login: admin password: admin).

- Connect to tables.

- Add all required information:

- table name: this is the alias followed by -- and then by the entire
path of the flat file (with extension and path):
ex: phone--/usr/home/database/phone.csv

- HTML's location: this is the path to the HTML files for display
(full and line mode, cf VII) and input (You can put them anywhere
but specify the path correctly... cf IV for more info)

- Path and URL of the HTML Output directory:
From v 2.1, you can associate 1 static HTML file per record: specify here
the server path to the directory storing these html files, and the URL to access
these files through the web.

From version 2.5, it is possible to name the ouutput files using the name of one of the field.

It is also possible to select the files’ extension, which allows for example to generate XML files from

your flat database…

Note: The HTML Output directory must be 666 and allow http access for HTMLs.

- Field displayed and Color used: This is for Default Display Mode Only.
When you are searching for a record, EZDB first display every record
matching the search. It shows then (in order to let you choose the
one you want to see) the field listed here and use the colors as
cell background and text color.

Field Syntax: Alias1:field_name1--Alias2:field_name2--...

this form field is a list of aliases and table fields
separated by --

Color Syntax: background1/text1--background2/text2--...

If You do not define any text color, the default color will be used.

Ex: Fields displayed for result: User Name:name--Phone No:phone
Colors used: ff5533/ffffff--cecece

Tips: If the alias is the same as the filed_name, you can just
specify the alias (it's not case sensitive):
Fields displayed for result: Name--Phone No:phone


- Delimiter: put here the delimiter used to separate fields in your
database.
Note: | will be translated into: pipe (that's no big deal...)

- Mandatory Fields: put here the names of the fields that are required
for insertion. Syntax is:
field1--field2--field3...

- Unique Constraint: you can asset uniqueness constraint to fields or
group of fields. Let's say you have a phone database: you want the
phone numbers to be unique (a phone number appears 1 time only in
the database) and you also want the couple of identifier
(first_name, last_name) to be unique: Here is the syntax:

Unique Constraint: phone--first_name+last_name

More generally: field1+field2+field3+...--field11+field12+...--...

- Image Upload:
-------------
EZDB 2.1 allow automatic image upload. When you want to store images in your
database, define the Target Directory where the images will be stored (this is the server path
to this directory), the Target URL which is the URL used to access this directory through
the web, and the maximum image size you authorize for image upload.

To use Image Upload, you must use this form definition in your HTML form template:

<form method="post" action="_CONTEXT_" ENCTYPE="multipart/form-data">

You must name the image fields image1, image2,... and use this field in your
form:

<input type="file" name="image1" value="_image1">


Note: The Image Upload directory must be 666 and allow http access for Gifs/Jpegs.

- Display Mode: c.f. ch V and VII for detailed information
-------------

- Direct Full Display: When there is just one match for a search, EZDB can
directly format the result (display phase) or present it with the regular
search result interface (retrieve phase).

- Order By: You can specify which fields will be used for sorting the results
in alphabetical order. Just enter a list of database field separated by --
or a comma ,

- AUDIT Options:
--------------
You can keep track of every modifications made to the database.
ex: A certain user delete a record by mistake.
As administrator, you can backup this record and rollback
the deletion.
THIS FUNCTION IS ONLY ACCESSIBLE BY ADMINISTRATOR.

- If you want to track the changes made set check the
enable log box.
- We have then 4 options to define which actions we want to
trace:
-> Add: If you want to archive every addition to the
database.
-> Delete: If you want to archive every deletion made
in the database.
-> Rollback: If you want to archive every rollback made
to the database.
-> Modify: If you want to archive every modification made
to the database.

- Headers and Footers:
--------------------
You can use you own headers and footers to customize the look of
EZDB. The headers and footers are defined for each (context,phase)
pair. The syntax is:

context--phase--header_path--footer_path

ex: add--*--./my_html/product_header_for_add.html--./my_html/product_footer_for_add.html
This will display the product_header_for_add.html and
product_footer_for_add.html for the add context and any
phase.

*--process--./my_html/product_footer_for_process.html--./my_html/product_footer_for_process.html
This will display headers and footers for every context
during the processing phase

Note 1: You can use header and footers for Login and Connected:
It will only work if the table name is specified in the url.
In this case, the phase MUST be *:

login--*--./my_html/product_header_for_login.html--./my_html/product_footer_for_login.html
connected--*--./my_html/product_header_for_connect.html--./my_html/product_footer_for_connect.html

Note 2: _LOGIN_ and _SESSION_ will respectively be replaced by the user’s login and session number.


- Email notifications:
--------------------
The mail notifications are a list of values SEPARATE BY CARRIAGE
RETURN.
The syntax of a value is:

((condition)+)->((email_address),)->((Alias:fied_name)+)

If you set condition to *, it always will be triggered as true.

((field_name=string)+)->((email_address),)->((Alias:fied_name)+)
OR ((field_name!=string)+)->((email_address),)->((Alias:fied_name)+)

In the first block (the condition), the string can be:
_LOGIN_ this will evaluate the name of the user login
_GROUP_ this will evaluate the name of the user group

Ex: company_name=ABCD+dpt=Accounting->jdooe@here.com->Company Name:company_name+Reference No:ref
_GROUP_=manager->webmaster@dob.com->Manager Name:name+Problem description:pb_desc
_LOGIN_!=default->me@here.net->Date:date+Email address:email
*->me@here.net->Date:date+Email address:email


MAIL TEMPLATES:
---------------

You can also use mail templates.
Just write a regular mail text and save it under mail_templates.

In order to send this text as mail notification, you just have to use this
syntax:
((condition)+)->((email_address),)->(Template:filename)


Ex: *->me@here.net->(Template:add_invoice.txt)
Note that the file MUST be under mail_templates

To make it fancier, you can use special caracters in your template so that
it will be parsed and populated with database fields:

The syntax is %_field_name_%

Ex: In our invoice_add.txt template, we use:

Dear %_contact_%,

Your invoice # %_ref_% has been received and
will soon be proceeded.

Thanks for shopping at EZStore.

The Manager.

This produces a very nice result.

- Submit


3- Grant Group Permissions to access the table, Create Group Accounts:
----------------------------------------------------------------------

- Connect to table_group

- You first need to grant Administrator Permissions:

- Go to the Add screen, enter the table name and the group that should
be connected to this table (here: admin).

- Check the function this group is allowed to perform FOR THIS TABLE.

- Check the menus this group should see (the bar at the top of the
screen).

- If necessary, define the HTML files to use to access the database from
this group. By default, the files specified in the "tables" database are used

- If necessary, define the HTML headers and footers files used to access the
database from this group. By default, the files specified in the "tables"
database are used. The syntax is similar to the one used for the table definition.

This implies that for a given group, headers and footers can change depending on

the contaxt and phase.

- If necessary, define the display mode for this group (see chapter VII).

- If necessary, define Access Restrictions:

The syntax for access restriction has been changed in version 2.5 and upper!

The syntax is (v2.5+):

((context:(field_name=string)+);) where string can also be _LOGIN_ or _GROUP_
(see previous chapter for details)

Ex: 1) manager_group=_GROUP_+company=ABCD
2) *owner=_LOGIN_
3) *group_owner=_GROUP_

4) modify:name=_LOGIN_;search:paid=yes (e.g., when modifying, only show the record

belonging to the user who’s logged. When searching, display only records for users that have paid)

Important Note:
---------------
The combination of ownership fields (*owner and *group_owner)
and of _LOGIN_ and _GROUP_ parameters is very powerful.
This allows to restrict access to self created records, or to
group owned records.

- If necessary, define how single search results should be returned to be used for this group (direct full display).

- Repeat the following step for the default user group.

- Repeat the following step for any group you want.

<<Top

IV Create User Account and Group Assignment:
--------------------------------------------


Once you defined several user group for each table and gave them permissions,
you have to create user accounts and to assign each user to one group.

Follow these 2 steps:

1- Create a User Account:
-------------------------

That's the role of the user_data table.
This table contain every user account: name, login, password.

- Connect to user_data:

- Fill up the form: name, login and password.

- Submit


2- Assign a User to a Group:
----------------------------

You remember that you create group permissions for each table!
That's why you have to assign each user to one group...

- Connect to user_rights

- Put the login of the user, the table name, and the group this
user belong to FOR THIS TABLE!


Important Note:
---------------
### 1- A USER CAN HAVE GIVEN RIGHT FOR ONE TABLE AND OTHER RIGHT FOR
ANOTHER TABLE
### 2- IF A USER DOES NOT BELONG TO A GROUP FOR A GIVEN TABLE, HE IS
GRANTED DEFAULT PERMISSIONS TOWARDS THIS TABLE.


------------------ Read this Example Carefully ------------------------------

Ex: Let's say you have two tables:
- customer_table (3 groups: default, admin, representative)
- vendor_table (3 groups: default, admin, representative)

and a new user: John Doe.

1- You create an account for John Doe: you connect to user_data and add him.

2- If you want John to be able to use the vendor table with representative
right, you need to add the following to user_right table:
login: jdoe table: vendor group: representative

3- With this configuration John can access the vendor table with
representative right and the customer table with default rights...

4- You can grant him Administrator permission towards the customer
table: connect to user_rights and enter John's login, the table name
(customer_table) and enter admin as group.
That's it... He's now Administrator of the customer table.

5- If you want anything to happen, you have to define the right of each
group (including default group) versus each table:
> Connect to table_group
> Give the proper right to the default group versus customer_table
Define also the HTML to use to access the database from this group
(by default, the HTML's defined in the "tables" database )
> Repeat the operation for each group versus customer_table
> Repeat the operation for vendor_table

------------------------------------------------------------------------------

<<Top

V About HTML's:
---------------


Now you configured the rights, you need to create the HTML files allowing
you to "talk" to the database.

You need two HTML files: one to display records (read access), one to
enter records (write access).

You can use the HTML Autogenerate Function describe in chapter XII or
write manually each of these files.

1- An HTML file to display records:
-----------------------------------

Just create a usual HTML file. The EZDB program will read this HTML code
and replace every _fieldname with the value of the field:


ex: The phone Database is defined as following:

pkey|first_name|last_name|phone_no


Here is an example of HTML code you could use to display records:

<html><body>
<table>
<tr><td>First Name</td><td>_first_name</td></tr>
<tr><td>Last Name</td><td>_last_name</td></tr>
<tr><td>Phone No</td><td>_phone_no</td></tr>
</table>
</body></html>


When you retrieve a record from the database, the program populate dynamicaly the fields.


This allows powerful connectivity to the database with DHTML, Cascading Style Sheet...


Important Note:
---------------
If you use the "Full" display mode, the search engine will use show the entire
record when retrieving. Therefore, you might want to have a link to show a single
record.
You can use the _DETAIL_ special word as href for a link to the display phase:

Ex: <a href="_DETAIL_">View It</a>



2- An HTML file to display records:
-----------------------------------

This is used to show search results only (retrieve phase).
It really works like the html_display file (ch VII 1-) with two difference:
You can have a link on each "short display" to the Full Display:

The href you have to use is _DETAIL_

If you want to show the percentage of keyword found, put _PERCENTAGE_
in your HTML code and it will be replaced by the script by the proper value

Ex: <form name="form$i" method=post action="_DETAIL_">
<input type=submit size=5 value="_PERCENTAGE_">
</form>

This would display a button showing the percentage of keyword found
and linking to the Full record.


3- An HTML file to input/modify records:
----------------------------------------

Here is the most sensitive part:

This HTML file is used to input data into the database when adding records
as well as retrieve and re-input when modifying (read-write access...).

Here is How it works: Be careful, it's CASE SENSITIVE.

* Define the form ACTION: it MUST be action="_CONTEXT_"

Here is an example of the syntax:

<form name="myform" method=post action="_CONTEXT_">


* Define the form SUBMIT button:

it MUST be <input type=submit value="Submit" (any_javascipt or whatever...)>


* Insert a text form object:

To connect a text input field to any field of the database, name the
form field "underscore field name":_name_of_the_dbfield
You can also use default values:

<INPUT type=text name="field_name" value="(Default Value) _field_name">

Note: If you use a default value, the space before _field_name is MANDATORY

Example: To connect to the first_name field of our phone database:

<INPUT type=TEXT name="first_name" value="Your Name Here _first_name">

Important Note: From version 1.3:
You can use certain fields in read only_access (only when modifying)
with the following syntax:

--> <INPUT type=text name="field_name" value="(Default Value) !_field_name">

This will behave normaly during adding but will show the field in read-only
mode when modifying



* Insert a textarea form object:

Here is the syntax: we are here connecting to the database field named remark:

<textarea rows=2 cols=20 name="remark">_remark</textarea>

The textarea field must be named as the database field and the content
of the textarea must be "underscore db filed":

_name_of_the_dbfield.


* Insert a List form object (pop-down menu):

Here is an example to connect a list to the database field named from:

<select name="from">
<option>_L_France</option>
<option>_L_USA</option>
<option>_L_UK</option>
<option>_L_Mars</option>
<option>_L_BZH</option>
<option>_L_Who cares?</option>
</select>

The select field must be named as the database field, and every option must
be preceded by _L_

To use multiple selection, you must precede the field name by _M_

<select name="_M_color" size="3" multiple>
<option selected>_L_red
<option>_L_blue
<option>_L_rose
<option>_L_green
</select>


* Insert Radio buttons:

Here is the syntax to connect to the database field named own_a_site:

<input type="radio" name="own_a_site" value="_R_Yes">Yes
<input type="radio" name="own_a_site" value="_R_No">No

The select field must be named as the database field, and the value must
be preceded by _R_


* Insert a Checkbox:

Here is the syntax to insert a Checkbox connected to the field named check_it:

<input type="checkbox" name="check_it" value="_C_So Do I">text beside the checkbox


The checkbox must be named as the database field, and the value must
be preceded by _C_


Note 1: Be careful with the case of the field names, it does matter!...

Note 2: You can use in your HTML form file these variables:

- _LOGIN_ will be replaced by the logged of the user.

- _SESSION_ will be replaced by the session number of the user (usefull when having

link to other database with restricted access).

<<Top

VI How to access DIRECTLY a table with default right:
-----------------------------------------------------


If you want lo lead users directly to specific EZDB page,
here is the way to do it:

They are 4 Context: add, search, delete and modify
And 4 Phases: entry, display, retrieve, process

Which gives us a nice 4*4 matrix:

Entry Retrieve Display Process
----------------------------------------------
Add | X X
Search | X X (X)
Modify | X X X X
Delete | X X X X


- Entry: Displays the search engine screen

- Retrieve: Lists all records matching the search (If you activate the page
by page search result, it will show the records directly -> no
display phase...)
- Display: Shows the selected record.

- Process: Shows the result of processing on selected record.


----------------------------------------------------------
THE CALL TO EZDB, WITH DEFAULT PERMISSIONS ONLY, IS

ezdb.cgi?table_name+context+phase&default
----------------------------------------------------------


What's that for?: Examples
-----------------

- You want a link to add directly a record to your product catalog?:

http://your_server/.../ezdb.cgi?product+add+retrieve&default

- You want a link to the search page for the vendor table?:

http://your_server/.../ezdb.cgi?vendor+search+entry&default

- A small search engine to search your rolodex table?:

<form method=post action="http://your_server/.../ezdb.cgi?product+search+retrieve&default">
Type any word:<input type=text name=keyword>
<input type=hidden name=case_sensitive value="No">
<input type=hidden name=max_record_no value="8">
<input type=hidden name=exact_match value="No">
<input type=hidden name=order_by_date value="No">
</form>

That will do it...


Note: Make sure the default group is granted correct permission towards the table!!

A more powerfull way to do a direct search using the URL:

You can use the URL to activate a search with a single href (no form submission):

http://your_server/.../ezdb.cgi?table_name+context+phase&_SESSION_&page_number-record_per_page&keywords%%%%dm=(Default|Line|Full)+cs=(Yes|No)+em=(Yes|No)+ob=(Yes|No)+dfd=(Yes|No)

Some explanations:

- The first part of the URL following the ? should now be familiar to you.

- The _SESSION_ part can be blank (if the database access is granted to the default user). If you put _SESSION_

in the HTML form for example, it would be replaced by the session number, which allows to link to

databases with restricted access.

- Define then the page to be displayed and the number of record per page

- The last part following %%%% is used to define the search parameters: display mode (dm),

case sensitivity (cs), booleans (em: Yes for AND, No for OR), order by date (ob), direct full display (dfd)

Example:

This URL will retrieve all job offering having ‘dba’ in the position field. It will show the second page of results

using 10 result per page. The Default display mode will be used, it is not a case sensitive search, boolean is AND,

results are not ordered by date and direct full display is not used.

http://your_server/.../ezdb.cgi?job+search+retrieve+&&2-10&position=~dba%%%%dm=Default+cs=No+em=Yes+ob=No+dfd=No

<<Top

VII The search engine:
----------------------

- You can list all the records: type in * as keyword

- You can use apostrophes: 'John Doe is a cool guy' match records
containing this entire sentence only.

- You can make a search on certain fieds only:
Here is the correct syntax:
field_name operator keyword
The operator is:
= equal for strings
=^ begin with for strings
=~ contains for strings
!= not equal for strings
!^ do not begin with for strings
!~ do not contain for strings
== equal for numbers
<> not equal for numbers
<,>,<=,>= for numbers

ex:
If we enter as keywords:
name=Bill
This returns every record with a name field equal to Bill

first_name=^Mi
This return every record with a first_name field beginning
with Mi (Michel, Mickey)

first_name=~che
This return every record with a first_name field containing
che (Rachel, Michel)

salary>1500
This returns every record with a salary greater than 1500 (not mine)


You can also make a mix of all of them:

first_name=^Bi salary>1000 california

This would return every record for which the first_name field
begins with Bi, the salary is greater than 1000, and that contains
"somewhere" (it's not field-dependant) the word california.


Options:
--------

- It can be case sensitive.

- The Match All Only options shows record matching every keywords only
If you enter: first_name=John 'Vice President' and check the Match All
Only box, it will only show records for VP named John.

- Order by date of insertion: By default, the search result is sorted
alphabetically. If you check this box, the result will be sorted by the
date of insertion (more recent record are shown first).

- Max number of record retrieved: this limits the number of record retrieved
by the search engine. The default value * retrieves everything.



The Search Diplay Mode:
-----------------------

In the table definition form ("tables" database), you can choose from 3 mode:

-> Default: this uses the field_displayed_for_result input to show
some elements of each record in basic format.

-> Line: this uses the html_line_display field to show search result
in a customised way. Just use the same HTML formatting as for
html_display files (_field_name replaced by the actual value of
the field).

Note: In the 2.0 version, you can use a display the search results
inside one customized table with the Line display mode. Let's have
a look at the product database example:

file: ./my_html/product_line_display.html

--<center><table border=0>
<tr bgcolor="#a0b8c8"><td width=400>
<font face="Arial"><b>_name</b> <i>Price: $ _price</i></td>
<td align=right> <a href="_DETAIL_"><i>view</i></a></font></td>
</tr>

We define the table layout (table html tag) in the first line,
preceeding by -- (double dash).
Then, we put the regular HTML formatting each line of the table.
What it does: EZDB© is scanning the file for the -- and if it finds it,
it understands that each result will be formatted as a single line of a
global table.


-> Full: this mode shows the search result using the html_display
file defined in the "tables" database.

<<Top

VIII The Audit function:
------------------------

--------------------------------------------------------------------
Important Note: This function is only available for the admin_group
--------------------------------------------------------------------

From the version 1.2, you can keep a track of every change made.
The goal is here to be able to rollback to an older version
of a record after a bad modification or even after deletion.

Log as administrator and click the audit link:
You can use the search engine to view change made on an
existing record, or retrieve the deleted record.
In both cases, you end up on a screen listing the changes
made on every record:
- You can view an old record
- You can make a rollback, meaning you reaffect an older version
of the record to the database.


Note: The $log_archiving_duration defines the range of time for
which you can view the changes. If you set it for example
to 86400*7 (7 days), you will see the changes for the past
week only.

<<Top

IX Cross Table References: How to implement "Relations":
--------------------------------------------------------


1- In the HTML display file:
----------------------------

We display a record and one of the element of this record refers to an element
of another database (a foreign field in a foreign table): from the "product_name"
field of the invoice table, we want to have an hot link to the "name" field
(this is the foreign field) of the product catalog database (this is the foreign table).

Here is the syntax:

<a href="%%field_name~foreign_table:foreign_field">Hot Link to the foreign table</a>

This function is currently working with anchor only. It scans the value of href and
replace it with the proper value.

Ex: With the display of an invoice records, we link from the product name to
field "name" in the product database:

<a href="%%desc4~product:name">

CURSORS (v 2.5+):

-----------------

Cursors allow to display a database record using information coming from

Different many databases.

For example, we have a job posting database and a company database. When

we show a job posting, we also want to display some information about the company

offering the position.

So here we go:

<p><font face="Arial, Helvetica, sans-serif"><b>_title</b> <br>

<i>_description</i> <br>

<br>

Start date: _start<br>

Location: _location</font><br>

</p>

<cursor value="*owner=login:./my_db/company.txt>pkey,name,description">

<cursor_header>

<font face="Arial, Helvetica, sans-serif">Posted by:

</cursor_header>

<a href="ezdb.cgi?./my_db/company.txt+search+display+_%pkey%">

<b>_%name%</b></a><br> <i>_%description%</i>

<cursor_footer>

</font>

</cursor_footer>

</cursor>

…

We retrieve the name and the description to show one the job offering and we take the primary key

(pkey) in the company database to have a direct link to the company database.

Here is the syntax: we retrieve information from a foreign database called db

<cursor value="(field operator db_field)+:db>((fields),)">

<cursor_header>

Here the HTML code to be displayed in the cursor header

</cursor_header>

Here the HTML code for the cursor body: to display a field from the foreign database,

The syntax is %field_name%

<cursor_footer>

Here the HTML code to be displayed in the cursor footer

</cursor_footer>

</cursor>

If no data is found in the foreign database, the cursor code is not displayed.

2- In the HTML form file:
-------------------------

We display the form for addition or modification of a record into a database. One of the
fields refers to another field (the foreign field) in another table (the foreign table).
We want to be able to browse this foreign table to retrieve some elements and insert them
in our primary table.

Here is the syntax: Everything is passed through the name value of a submit button:

<input type=submit
name="%%table_name:field_name~foreign_table:(foreign_field:foreign_field)+ %%search_parameters"
value=" ? ">

search_parameters have the following structure:
(search_type,case_sensitive,max_record_no,exact_match,order_by_date)

- search_type can be a search operator: = , =~ , =^ , != ,... OR * which means "no
specific operator specified". WE BROWSE ONLY THE FOREIGN FIELDS, NOT ALL FIELDS
OF THE FOREIGN TABLE
- case_sensitive van be Yes or No
- max_record_no is a number or * to list all records
- exact_match can be Yes or No
- order_by_date can be Yes or No



Note: This function is currently implemented with submit buttons only


Ex: We link from the description field desc1 of the invoice form to the
product catalog, and we want to retrieve the product id (id1 field
in the invoice db which refers to the id field in the product db),
the product name (desc1 field in the invoice db which refers to the
name field in the product db), and the price (price1 field
in the invoice db which refers to the price field in the product db).
We don't specify any operator.

<input type=submit name="%%invoice:desc1~product:id--id1:name--desc1:price--price1%%*,No,*,No,No,No" value=" ? ">

if we use:

<input type=submit name="%%invoice:desc1~product:id--id1:name--desc1:price--price1%%=^,Yes,5,No,No,No" value=" ? ">

We will retrieve records of the product catalog the begins like the
value of the desc1 field in our invoice form

<<Top

X Indexes:
----------

Index is a system database containing the value of user defined indexes.

You can define a field of a specified table to be an index, which means that
each time you add a record to this table, this field will take the value of
the index + 1. It works like a counter!
You just have to specify the table and field_name and assign a starting value
to the index.

<<Top

XI Cascading Additions:
-----------------------

With the version 2.0, you have the possibility to make additions to several
databases while connected to a database and performing a regular addition.

For Example, you a member database containing all kind of user information.
You let them choose a login and a password in order to let them access another
database (let's say your downloads database).

In other words, you want the user_data and user_rights system databases to be updated
based on the information submitted for the member database. That's why you will use
"Cascading Additions".

You have to connect to add_external and define your database, the foreign database where
external additions will be performed and the fields that should be populated.
In order to define the field listing, you must use one field definition per line.

The structure for field definition is:

field->field_in_the_foreign_table

OR (Any Value)->field_in_the_foreign_table

Let's follow our member account example:
In order to add a member, we want user_data and user_rights to be updated
with external additions:

Table Name: member
Foreign Table Name: user_data
Fields:
member_login->login
member_password->password
(Member Account)->name

This will automatically add to the user_data table the value of the member_login field
(in user_data), as value of the login field, the value of the member_password field
to the the password field, and will put 'Member Account' in the name field of user_data.

We use then a similar external addition to user_rights:

Table Name: member
Foreign Table Name: user_rights
Fields:
member_login->login
(members_groups)->group
(downloads_db)->table

You guessed it: we authorized the members to accessed our download database with the rights
defined for the members_group (group rights defined in table_group).


Important Note:
---------------
The unique constraint defined in the tables system database works also for external additions.
A failure due to a unique constraint violated will cause the system to fail. Therefore, if
you want to perform multiple external additions and if one of them fails because
of unique constraint violation, all following external additions won't be executed.

For example: if you try to execute 3 external additions and if the second one fails,
the third one won't execute. As a consequence, in that case, only the first external addition
will have been performed.

<<Top

XII EZ Wizards:
---------------

1- EZ Wizard:

This wizards guide you through the steps of resetting a new database instance.
Define first the database name, the separator and the fields.
Then, you can define default HTML pages to work with your database. These
html files will help you getting started with EZDB Concepts. See ch. 3- for
more details.

2- Build a New Database:

Use this feature to edit a database. Enter the name of your database file.
You will be proposed to update the first line of the database (definition
line) or to update all the records as well.
If the database name you entered is not recognized, you can create a new
database even though we encourage you to use the EZ Wizard for that matter.

3- HTML File Autogeneration:

EZDB can generate the HTML files described in chapter V automatically.

Note: This can only be done once you defined your database.

When you click the HTML link in the menu bar, you are first asked to
give the path to your database and the separator.

EZDB will scann the table and prompt you to define each form elements for
each database field.

The syntax for the parameters is:

- Text/Hidden Fields:You can enter a string taken as default value .

- Textarea Fields: rows;cols;default_value
ex: 5;40;default value for textarea

- Checkbox/Radio Box Fields: label:value:"checked"; +
ex: I Agree:agree:checked;I disagree:disagree

- Select Fields: value; +
ex: Arizona;California;Nevada


4- Bulk Update:

When you have a static HTML Output defined for a database and modify the template
for displaying record, you can modify all static html files at once by using this
link.

With the version 2.5, you can name the generated file after one of the field in your database: for example, if you have a database storing products information, you might want to name your files product_name.html (e.g. hard_drive_23.html). If the field you selected for naming files contains spaces, the will be replaced by underscores '-'.

With v 2.5 also, you can select the file extension of your choice. This allow you for example to generate XML files.

5- EZDB Setup Analyzer:

This tool help you analyze all database connected to EZDB. It also checks
that the files are assigned correct permissions.

6- EZDB Report:

This report show all user group rights versus all the databases that are
connected to EZDB.

<<Top

XIII Support:
------------

_______________________________________________________
-------------------------------------------------------
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF
ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT
SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR
ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE
OR OTHER DEALINGS IN THE SOFTWARE.
_______________________________________________________
-------------------------------------------------------


If you face any problem or would like to make suggestions, feel free to
contact support@ezperl.com.

Before asking for support, please check the following:

- You're running Perl 5.0003+
- The perl executable is located @: /usr/local/bin/perl
(change the first line of code accordingly for ezdb.cgi and report.cgi)
- The rights are set up properly.
- If you can check it, verify there are no weird characters such as ^M.
They appear when you ftp a DOS file to a Unix box. I recommend
UltraEdit to check the mode (Unix or DOS) of your file and make conversions.


Have fun...:-)

<<Top