|
EZDB
2.62 ©
|
@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.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
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).
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
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
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>
<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...:-)