Copyright 1998-2000 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 firstname.lastname@example.org)
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
- headers/footers for login, auhorize and connnected phases
- Version # added
- Crypted session #
- Multiple default accesses using session=~/^default/i
- IP check optional
- Script name can be changed
- 'loggout' is shown instead of 'login' once logged
- order_by in the url using %%%%ob=field_name
- _RECORD_COUNTER_ and _KEYWORD_ parsed in the display_line files and returning
the number of search results and the keywords entered.
- Auto Upgrade from 2.x to 2.6 in the wizard
- EzTree ©
- EzTree_fields field added to tables
- *date used as special field for date stamp
- _CONTEXT_ parsed in the headers, footers, display and form files.
- Bug fixed on the order_by routine, on textarea fields generation from the wizard
- special form name is now 'ezform' (ezrdb_from => ezform for relational imports)
- 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.
- Parameters in the command line
for queries with max_record_no
- 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
- 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
- First Shareware release
- Setup Analyzer
- 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
- 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 displaying a form
- Aliases for table path
- Log options at table level
- Search page by page updated
- Search result sorted in alphabetical order
- 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 1: Compatibility
EZDB was tested developed on Unix (Apache / FreeBSD) and Windows (Apache / NT 4.0)
Note 2: Release 2.6
The release 2.6 is a major release: it comes with a brand new feature called EzTree © allowing powerful classifications of the
data connected to EZDB.
With EzTree ©, you can generate your own classification tree structure that will let you classify ANY type of data.
You can find tools that help classifying urls and delivers Yahoo! © like sites. With EzTree, you can classify anything using the
same Tree: you can mix websites urls with company profiles and job offerings for example...
It comes with a static html file generation feature that will mirror the Tree pages into static pages for faster access time.
We hope you will enjoy this major improvement of EZDB.
Here is the manual for installing and using EZDB:
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"
XI Cascading Additions
XII EZ Wizards
Use the Example!:
This package comes with the online example (invoicing system and job posting example).
If you 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 2.6 comes with the following structure:
|- ezdb.cgi 755
|- eztree.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
Make it -rw-rw-rw-
B- system_db_html: this is folder contains 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
Make it rwxrwxrwx.
Important Note 1:
The structure described here is just a suggestion: you can locate the folders
anywhere, except for ezdb_lib.pl, eztree.cgi, ezhtml.pl, ezwizard.pl 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:
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
- 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:
II About global variables:
You can rename ezdb.cgi into any other name by changing this variable and renaming the script.
is the script url.
'.' by default.
$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...
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.
this variable to 1 to enable IP check, 0 otherwise.
When enabled, each time a user sign in, his IP address is registered by ezdb.
Then, for each request of the users, the session number of this user is checked against his
This enforces the security controls for accessing ezdb.
BUT, some users can change their IP for each web request:
1 user having many IP addresses during the same session (AOL users)
AND, some users can come behind firewalls (they work within a same company and share an
Internet connection): 1 IP address for many users. (Note that theses users don't have the same IP
within their LAN so that's no problematic for an intranet application).
should you do?
MOST of the users have a fixed IP during a given session (usually, you connect to your ISP
and it gives you an IP that is fixed as long as you are connected.)
The good answer is: It depends... on what you're doing and where your users are coming from...
This variable enables the setup
report (ezdb.cgi?setup) when set to 1
We suggest that you turn it off once your setup is ok.
This is the path to the system database folder. Default value is ./system_db
The path to the directory where the
EzTree generated file should be located ('./my_html' by default)
This directory should be http readable.
The directory path to the EzTree data file ('./my_db' by default)
The url to the generated EzTree files.
You can use mail notification to warn users when a record is added, modified,
deleted or roll backed.
The email address of the notifications sender
The signature of the mail.
The complete path to the mail subroutine (EZDB was tested with sendmail only).
Under NT, you can use Blat.
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.csv
(end_of_file you notice the newline caracter!)
Important Note: Special fields
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
this field to add time stamps to each record.
They will look like this: Tue Jul 25 21:24:15 2000
2- Define a database with EZDB:
- Launch EZDB (You need to put the script on a server and run it from
- Log in as Administrator (By default login: admin password: admin).
- Connect to tables.
- Add all required information:
- database name: this is the alias followed by -- and then by the entire
path of the flat file (with extension and path):
- Path to the HTML Display File and Path to the HTML Form File
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)
Delimiter: the field separator used in your flat database
Note: | will be translated into 'pipe'
- 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.
- Files' extension:
It is possible to select the
files extension, which allows for example to generate XML files
your flat database
Field used for files' name:
From version 2.5, it is possible to name the output files using the name of one of the field.
Note: The HTML Output directory must be 666 and allow http access for HTMLs.
EzTree Fields: Specify here
which fields will be used in the eztree file.
See discussion in the EzTree Chapter for more information
- Path to the HTML Line Display File
Path of the HTML file used to display results in line mode (see Display Mode).
- Path to the No Match HTML File
Path of the HTML file used when no record match a query.
- 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
- Display Mode: c.f. ch V and VII for detailed information
Full Display: When there is just one match for a search, EZDB
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 ,
Use 'reverse to sort by reverse order: example: order_by = reverse name would sort by
name with a reverse order.
- Password protection field
One of the database field used as a password to protect EACH record of a database.
- Bulk Delete
Set to Yes to enable bulk deletion of records.
- Mandatory Fields: put here the names of the fields that are
for insertion. Syntax is:
- 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 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
<input type="file" name="image1" value="_image1">
Note: The Image Upload directory must be 666 and allow http access for Gifs/Jpegs.
- 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
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
-> Add: If you want to archive every addition to the
-> 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:
This will display the product_header_for_add.html and
product_footer_for_add.html for the add context and any
This will display headers and footers for every context
during the processing phase
Note 1: You can use header and footers for Login, Authorize and Connected:
It will only work if the table name is specified in the url.
In this case, the phase MUST be *:
Note 2: _CONTEXT_, _LOGIN_ and _SESSION_ will respectively be
replaced by the context,
the users login and session number.
- Email notifications:
The mail notifications are a list of values SEPARATED BY CARRIAGE
The syntax of a value is:
If you set condition to *, it will always be triggered as true.
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->email@example.com->Company Name:company_name+Reference No:ref
_GROUP_=manager->firstname.lastname@example.org->Manager Name:name+Problem description:pb_desc
- 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
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:
Your invoice # %_ref_% has been received and
will soon be proceeded.
Thanks for shopping at EZStore.
This produces a very nice result.
3- Grant Group Permissions to access the table, Create Group Accounts:
The logic behind EZDB authentication and permissions management is fairly simple.
i- A user is defined in the
user_data system database: it is basically a combination of
profile name and login/password
ii- A Database is accessible by groups of users.
At least 1 group should be able to access a database: the administrator group admin_group.
If you need a database to be accessible without Authentication, you need to define how the default user group shall access the database.
Groups access for a given database are defined in the table_group system database.
In v 2.6+, any group named "default..." is considered as a default group (it doesn't need user's authentication for access):
it is the multiple default access.
Iii- The fact that a user belong to a certain group when accessing a given database is defined in the user_rights system database.
iv- Note: All of the following is true
A group is made of many users.
A user can belong to many groups.
A group can be granted access to many databases.
A Database can be accessed by many groups.
The combination of (user,group, database) must be unique
Here is how you should define a
group permissions for a given database:
- 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
- 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 context 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
4) modify:name=_LOGIN_;search:paid=yes :
When modifying, only show the record belonging to the user whos logged.
When searching, displays only records for users that have paid
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
- Repeat the following step for the default user group (group name is 'default').
- Repeat the following step for any group you want.
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.
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!
1- A USER CAN HAVE GIVEN RIGHT FOR ONE TABLE AND OTHER RIGHTS FOR
2- IF A USER DOES NOT BELONG TO A GROUP FOR A GIVEN TABLE, HE IS
GRANTED DEFAULT PERMISSIONS FOR 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
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:
Here is an example of simple HTML code you could use to display records:
When you retrieve a record from the database, the program populate dynamically the fields.
This allows powerful connectivity to the database with DHTML, Cascading Style Sheet...
2- An HTML file to display Search Results: The LINE DISPLAY MODE:
This is used to show search results only (retrieve phase).
It is usually name database_line_display.html
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_
Ex: <a href="_DETAIL_">View It</a>
Will be replaced by <a href="ezdb.cgi?product+search+display+90876876">View It</a>
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 method=post action="_DETAIL_">
<input type=submit size=5 value="_PERCENTAGE_">
This would display a button showing the percentage of keyword found
and linking to the Full record.
You can use 2 special variables in your line_display file:
_RECORD_COUNTER_ : this displays the number of matching corresponding to the query
_KEYWORD_ : this displays the
keyword entered by the user.
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:
* 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">
* Insert a textarea form object:
Here is the syntax:
<textarea rows=2 cols=20 name=" _name_of_the_field">_name_of_the_field</textarea>
The textarea field must be named as the database field and the content
of the textarea must be "underscore db filed": for a field called remark:
<textarea rows=2 cols=20 name="remark">_remark</textarea>
* Insert a List form object (pop-down menu):
Here is an example to connect a list to the database field named from:
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>
* 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:
- _CONTEXT_ will be replaced by the context: add, delete, search or modify.
- _LOGIN_ will be replaced by the logged of the user.
_SESSION_ will be replaced by the session number of the user
(useful when having
link to other database with restricted access).
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: Shows the selected record or the blank form for additions.
- Process: Shows the result of processing on selected record.
THE CALL TO EZDB, WITH DEFAULT PERMISSIONS ONLY, IS
What's that for?: Examples
- You want a link to add directly a record to your product catalog?:
- You want a link to the search page for the vendor table?:
- 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">
That will do it...
Note: Make sure the default group is granted correct permission towards the table!
A more powerful 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):
- 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
in the Display 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 clause (ob), direct full display (dfd)
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.
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 fields 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
Examples: If we enter as keywords:
This returns every record with a name field equal to Bill
This return every record with a first_name field beginning
with Mi (Michel, Mickey)
This return every record with a first_name field containing
che (Rachel, Michel)
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.
- It can be case sensitive.
- You can use AND or OR connectors: AND will only return records
matching all the keywords submitted.
- 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 Display Mode:
In the table definition form ("tables" database), you can choose from 3 modes:
-> 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 customized way. Just use the same HTML formatting as for
html_display files (_field_name replaced by the actual value of
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:
<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>
We define the table layout (table html tag) in the first line,
preceding 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
-> Full: this mode shows the search result using the html_display
file defined in the "tables" database.
VIII The Audit function:
Important Note: This function is only available for the admin_group
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
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">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:
CURSORS (v 2.5+):
Cursors allow to display a database record using information coming from different 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:
face="Arial, Helvetica, sans-serif"><b>_title</b>
Start date: _start<br>
<font face="Arial, Helvetica, sans-serif">Posted by:
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),)">
Here the HTML code to be displayed in the cursor header
the HTML code for the cursor body: to display a field from the
the syntax is %field_name%
Here the HTML code to be displayed in the cursor footer
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.
You MUST name your form "ezform"
<form method="post" action="_CONTEXT_" name="ezform">
Here is the syntax: Everything is passed through the name value of a submit button:
value=" ? ">
search_parameters have the following structure:
- The operator can be: = , =~ , =^ , != ,... OR * which means "no
specific operator specified". WE BROWSE ONLY THE FOREIGN FIELDS, NOT ALL FIELDS
OF THE FOREIGN TABLE
- cs case_sensitive can be Yes or No
- dm display_mode is the mode used to display the search result.
- em exact_match can be Yes or No
- ob order_by 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%%op=*+dm=Line+ob=Yes+cs=No+em=No" value=" ? ">
if we use:
<input type=submit name="%%invoice:desc1~product:idid1:name--desc1:price--price1%%op==^,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
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.
XI Cascading Additions:
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
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:
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
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
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).
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.
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
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- Edit a Database:
4- 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 scan 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; +
5- Static Files 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
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.
6- EzTree Update:
This features generates a static HTML
version of EzTree using the EzTree parameters defined as global
variables in the ezdb.cgi script.
The index pages is named eztree.html
7- EZDB Upgrade:
This helps you to upgrade your ezdb configurations from any 2.x version to the current version.
It takes care of the system database files and add all necessary parameters and/or missing system files.
Here is the roadmap to upgrade to
the latest version:
i- Back up your current configuration.
ii- Update the scripts and libraries to their latest versions by posting the files on your server.
iii- Update all system HTML files (posted under ./system_html by default)
iv- Run the EZDB Upgrade from the EZDB Wizard.
v- Run the Setup Analyzer from the Wizard.
vi- Once everything is setup properly, turn the setup analyzer off (see global variables).
8- EZDB Setup Analyzer:
This tool help you analyze all
database connected to EZDB. It also checks
that the files are assigned correct permissions.
We suggest you turn this feature off (v 2.6+) once your setup is correct.
6- EZDB Setup
This report show all user group rights versus all the databases that are
connected to EZDB.
We suggest you turn this feature off (v 2.6+) once your setup is correct by removing the report.cgi file
EzTree is a classification tool to
help you organize the data you manage using EZDB.
It uses a custom built Tree Structure (Like Yahoo! does for example).
1- Define your classification
structure with EZDB:
Log in to ezdb.cgi as admin and connect to the categories database.
Then define all your categories.
We call Master Category a category that doesn't have a parent category, which means it sits at the highest level of the Tree.
We call Child Category a category that has a parent category.
At this point, you can connect to eztree.cgi (connect to http://www.yourserver.com/cgi-bin/eztree.cgi) and see the structure of your EzTree.
2- Assign a Resource to a Category:
Once you built the EzTree structure,
you can assign a resource to 1 or many categories.
Connect to the database you want to classify (as administrator) and select the record to be assigned in MODIFY mode:
You will see a link "Assign to a Category" that will help you to select one or many categories.
(this link is also viewable in ADDITION mode)
This generates a new record in the "eztree" database using the eztree fields you defined in the tables system file for that database (See ch III): we duplicate in the eztree database the name and description of the record selected to accelerate the processing time when using eztree. EzTree also gives the ability to use flex fields, that is additional fields you want to import from your database into EzTree.
3- Browse you EzTree:
and browse by clicking over the
categories name and/or the resources name.
The EzTree uses the tree_display.html file located by default under ./my_html/ to build the EzTree pages:
This file has the following structure:
The portion of code between the
<subcategories> and </subcategories> tags are used as
delimiter for the portion of html code used to show the information
about the current category being viewed and its child categories.
Use %field_name% to have category fields displayed in this section (fields can be here: master, category_name, long_name, description)
The portion of code between <items> and </items> tags is used as delimiter for the html used to show resources assigned to the current category.
Use %field_name% to have eztree fields displayed in this section (fields can be here: key, database, category, name, field1, field2, field3)
Anywhere outside these tags, %field_name% will be replaced by the current category attributes for these fields: master, category_name, long_name, description.
Let's have a look at the default HTML file:
width=400 border="1" CELLPADDING=2 CELLSPACING=0>
<font face="Verdana, Arial, Helvetica, sans-serif" size="3">%long_name%</font></b></a>
<font face="Verdana, Arial, Helvetica, sans-serif" size="3"><b>%category_name%</b></font></a>
<i><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<li><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#7878cc">%database%:
<font face="Verdana, Arial, Helvetica, sans-serif" size=2> <i>%field1%</i></font>
You can generate a static HTML version of EzTree using the EZDB wizard:
Log in as administrator to ezdb and select the EzTree wizard: this will generate
a set of static files using the parameters defined as global variables in the ezdb.cgi script:
(see global variables in ch II)
Here are some basic security rules:
Change the script name
a very popular software.
To prevent users to recognize easily which tools you are working with, you should change the name of the script.
Use IP check if possible
If you have no problem with IP
checking constraints (see previous explanations), you should enforce
using IP checks.
Once your setup is done
Turn the setup feature off (in ezdb.cgi) and remove the report.cgi script: the less information you give out, the best.
Make sure you use at least Perl
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
Before asking for support, please check the following:
- You're at least running Perl 5.004
- 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 or Textpad to check the mode (Unix or DOS) of your file and make conversions.