Make your own free website on Tripod.com

              EZDB 2.62  ©          

By Stephane Barde
webmaster@ezperl.com
September 22 2000
http://www.ezperl.com



@table of content


COPYRIGHT NOTICE:

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 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.63 9/25/00
           - headers/footers for login, auhorize and connnected phases
          

2.62 9/22/00
           - Table_group_form updated within the package
           - Image upload backward compatibility
          

2.61 8/30/00
           - Bulk deletions
           - Reverse sorting
           - Textarea fixed in file generated from wizard
           - Record protection based on one database field
           - No match html page
           - Bulk update fixed and name_file_after scans for spaces

2.6 7/14/00
           - 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)

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 displaying 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 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:

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 EzTree

XIV Security


XV
Support



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
               definitions.
               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
             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.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
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
      - add_external

<<Top


II About global variables:


script_name

You can rename ezdb.cgi into any other name by changing this variable and renaming the script.

script_url

This is the script url.
'.' by default.


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_archiving_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.

enable_ip_check

Set 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
IP address.
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).

So what 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...

setup_on

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.

system_file_path

This is the path to the system database folder. Default value is ./system_db
 
eztree_html_directory

The path to the directory where the EzTree generated file should be located ('./my_html' by default)
This directory should be http readable.

eztree_data_directory

The directory path to the EzTree data file ('./my_db' by default)

eztree_html_url

The url to the generated EzTree files.


Mail variables

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

mail_from
The email address of the notifications sender

mail_signature
The signature of the mail.

mail_routine_path
The complete path to the mail subroutine (EZDB was tested with sendmail only).
Under NT, you can use Blat.

<<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.csv

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

(end_of_file  you notice the newline caracter!)


Important Note: Special fields

*owner, *group_owner

phone.csv
pkey|*date|name|phone|address|email|*owner|*group_owner|department|manager

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


*date

Use 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
  a browser).

- 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):
            ex: phone--/usr/home/database/phone.csv
       
      - 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 file
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 from
  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

     - 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 ,
      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 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 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.
     
           
      - 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, Authorize 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
           authorize--*--./my_html/product_header_for_connect.html--./my_html/product_footer_for_connect.html

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


      - Email notifications:
        The mail notifications are a list of values SEPARATED BY CARRIAGE
        RETURN.
        The syntax of a value is:
           
            ((condition)+)->((email_address),)->((Alias:fied_name)+)
           
            If you set condition to *, it will always 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:

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
        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 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
            2) *owner=_LOGIN_
            3) *group_owner=_GROUP_
      4) modify:name=_LOGIN_;search:paid=yes :
      When modifying, only show the record belonging to the user who’s logged.
      When searching, displays 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 (group name is 'default').

- 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 RIGHTS FOR
ANOTHER TABLE
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

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

<<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 simple 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 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_">
    </form>

   
    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:

  it MUST be <input type=submit value="Submit" (any_javascript 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">


* 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:

          <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:

- _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).


<<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 or the blank form for additions.

- 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 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):

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|field_name)+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 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)

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 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:

  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.

- 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
         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,
         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
         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


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">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 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.

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:

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


search_parameters have the following structure:
op=operator+dm=(Default|Line|Full)+ob=(Yes|No|field_name)+cs=(Yes|No)+em=(Yes|No)"

- 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:id—id1: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
   
<<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:


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- 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; +
  ex: Arizona;California;Nevada
 

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
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.


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 Report:

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

<<Top


XIII EzTree:

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:

Connect to http://www.yourserver.com/cgi-bin/eztree.cgi 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:

<html>
html code...

<subcategories>
html code...
%any_field_name%

</subcategories>

<items>
html code...
%any_field_name%

</items>

html code...
</html>

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:

<table width=400 border="1" CELLPADDING=2 CELLSPACING=0>
<tr><td bgcolor="#cecece">
<a href="http://localhost/cgi-bin/eztree.cgi?category=%master%"><b>
<font face="Verdana, Arial, Helvetica, sans-serif" size="3">%long_name%</font></b></a>
</td></tr>

<tr><td>
<subcategories>
<li>
<a href="_SUBCATEGORIES_">
<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">
%description%</i> _SELECT_
</font>
</subcategories>
</td></tr>
<tr><td>
<items>
<li><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#7878cc">%database%:
<a href="http://localhost/cgi-bin/ezdb.cgi?%database%+search+display+%key%">%name%</a></font></li>
<br>
<font face="Verdana, Arial, Helvetica, sans-serif" size=2> &nbsp; &nbsp; <i>%field1%</i></font>
</items>
</td></tr>
</table>

Note:
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:

$eztree_html_directory='./my_html';
$eztree_data_directory='./my_db';
$eztree_html_url='http://localhost/ezperl/my_html';
(see global variables in ch II)


<<Top


XIV Security:

Here are some basic security rules:

Change the script name

EZDB is 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 access
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 5.004


<<Top



XV 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 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.


Have fun...:-)

<<Top