NAME

pqedit.cgi - Web-based database table editor.

SYNOPSIS

'http://www.domain.edu/mod_perl/pqedit.cgi?server=sqlserver &server_type=oracle &database=mydb&table=tablename&username=user&password=pwd &helppage=http://www.domain.edu/helpfile.html &labels=field1name;Field 1 Label,field2name;Field 2 Label &subedit_tables=table1%2Ctable2&button_subedit=1 &web_body_bgcolor=html_bgcolor &web_textarea_columns=number_of_columns &web_textarea_rows=number_of_columns &begin_html_include=/subweb_directory/html_to_include.html &middle_html_include=/subweb_directory/html_to_include.html &end_html_include=/subweb_directory/html_to_include.html &disable_lookup=nonlookup_table&no_schemacache=1 &label_match=label_match_string,label_match_string2,... &auto_field=table,field,replacement;... &replace_field=table,field,replacement;... &pq_fieldname=field_value&pqsearch_fieldname=1 &pk_value=tab_delimited_list_of_primary_key_values &Overview=1 &single_window=ON &nondisplay=table,field;... &readonly=table,field;... &pqdebug=1'

or

All parameters are optional. pqedit.cgi may be run from a shell in offline mode. It will then prompt you for the parameters:

(offline mode: enter name=value pairs on standard input).

ABSTRACT

The pqedit.cgi perl script is a Perl CGI script designed to allow easy editing of any Transact-SQL (Sybase or MS SQL server) or Oracle compliant database table. It is designed to provide a web, CGI form interface for editing any arbitrary database table--sufficient information to provide a reasonable form interface is acquired dynamically from the database server itself.

It runs on a web server as a Perl 5 CGI script that uses the CGI, Apache::Sybase::DBlib, Sybase::DBlib, Win32::ODBC, RDBAL, RDBAL::Schema modules.

INSTALLATION:

To install this package, just change to the directory in which this file is found and type the following:

perl Makefile.PL
make
make test
make install

And then copy pqedit.cgi to a place in your web directory tree that allows CGI scripts to run (if your web server supports mod_perl, a mod_perl location is preferred). pqedit.cgi will be copied to your '/usr/local/bin' directory also.

The mssql_pqweb.sql or sybase_pqweb.sql DDL scripts may be used to create the appropriate tables in the optional pqweb database. See the comments at the top of these files for their usage.

DESCRIPTION

PQ Edit uses the RDBAL::Schema module to query the database for information about the tables and fields available. Based on this information, it tries construct a reasonable interface for editing any table of interest.

PQ Edit does not maintain a connection to the database--it reconnects to the database server on each action. PQ Edit passes the username and password given to it to itself on each action so that it can reconnect to the database server.

PQEdit assumes that there are not any tabs in any of the fields being displayed or edited (it will turn each tab into a space).

PQEdit assumes that the names of the tables' fields do not have 15 underscores in a row as part of the name (Example: 'server.owner.table.my_______________field' is not allowed).

If called with no CGI parameters, it will prompt the user for:

server
database
table
username
password

or some or all of these may be passed as CGI GET or POST parameters.

PQEdit parameters

server

The name of the Transact-SQL database server (Sybase or MS SQL) to connect to.

server_type=oracle

This tells PQ Edit that it is an Oracle database which it is talking to. This parameter is mandatory for the correct working of PQ Edit with an Oracle database but should not be used for all Transact-SQL databases.

database

The name of the database on the database server that contains the table(s) to edit.

table

The name of the database table to start editing.

username

The username to use to login to the database server.

password

The password to use to login to the database server.

helppage

The URL of an HTML page which contain bookmarks which correspond to the names of the fields in the database. If this parameter is given, then PQ Edit will make the labels of the form's fields into clickable links pointing to htmlpage#fieldname. If a fieldname is part of a foreign key tuple, then the bookmark which will be used will consist of the field names seperated by ' and ' (Example: 'field1 and field2').

The helppage value may also be retrieved from the optional pqweb database.

labels

This parameter may contain a list of comma seperated pairs of fields and their overridden labels. The field;label pair is seperated by a semicolon.

Example: labels=field1name;Field 1 Label,field2name;Field 2 Label

subedit_tables

A comma seperated list of tables that should be also made available for editing. (NOTE: the HTML escaped form for comma is %2C. )

button_subedit

When given a true value, 1, uses push buttons instead of radio buttons for selecting (and switching to) another table for editing.

web_body_bgcolor

An HTML background color, BGCOLOR, value for the displayed pages.

web_textarea_columns

The number of text columns to use for larger character fields. Any character field larger than this value uses a text area CGI field instead of a text CGI field for editing. The default is 61.

web_textarea_rows

The number of text rows to use for larger character fields. The text area field will only be as large as is necessary based on the size of the character field--this value limits the maximum number of rows used. The default is 2.

begin_html_include

A file which contains HTML to include near the beginning of the PQ Edit form. The specification of this file should be the absolute path relative to the root of the web hierarchy. Example: '/mydir/helpfile.html'. This file should not contain the <HTML>, <BODY>, </BODY>, or </HTML> tags.

middle_html_include

A file which contains HTML to include near the middle of the PQ Edit form. The specification of this file should be the absolute path relative to the root of the web hierarchy. Example: '/mydir/helpfile.html'. This file should not contain the <HTML>, <BODY>, </BODY>, or </HTML> tags.

end_html_include

A file which contains HTML to include near the end of the PQ Edit form. The specification of this file should be the absolute path relative to the root of the web hierarchy. Example: '/mydir/helpfile.html'. This file should not contain the <HTML>, <BODY>, </BODY>, or </HTML> tags.

disable_lookup

A comma seperated list of tables that should NOT used as Lookup Tables or to provide the values and labels for Pull Down Menus. (NOTE: the HTML escaped form for comma is %2C. )

label_match

A comma seperated list of strings to use to match field names against when lookup for the field to use as a label for lookup fields. The default strings (in the order used for matching is: 'description', 'comment', '_name', 'label', 'desc' (feedback welcomed on this default list).

So if the parent table used to create a lookup pulldown list has a field such as 'mydescription' or 'foo_comment' PQ Edit will use the field 'mydescription'.

replace_field auto_field

A semicolan seperated list of table,field,SQL:replacement or table,field,VALUE:replacement or table,field,replacement.

replace_field always replaces a fields values whereas auto_field only replaces values of fields which are left blank (NULL). replace_field fields are shown as read-only fields on the PQ Edit form.

Specifies values for fields for inserts or updates. The replacement portion is processed to replace variables in the list specified below as well as all variables of type $pq_field. If SQL: is specified, then the replacement is used as a valid command to be executed on the database server, with the resulting single value replacing that field's value for the insert or update operation. If SQL: not is specified, then the replacement replaces that field's value for the insert or update operation.

For example:

pqedit.cgi?replace_field=main_table,first_field,SQL:select '$remote_address'

would execute: "select 'ip_address'" on the database server and put the first value returned into the first_field value for the insert or update statement of table main_table.

pqedit.cgi?replace_field=main_table,first_field,VALUE:$remote_address $remote_user $pq_another_field

would put 'ip_address username another_field' into the first_field value for the insert or update statement of table main_table.

Replacement variables:

$pq_field: $pq_field gives the current value (from the PQ Edit form) of some other table field from the same record.
$referer: The URL of the page the browser was viewing prior to fetching your script. Not available for all browsers.
$remote_address: The dotted IP address of the remote host.
$remote_ident: The identity-checking information from the remote host. Only available if the remote host has the identd daemon turned on.
$remote_host: Either the remote host name or IP address. if the former is unavailable.
$remote_user: The name given by the remote user during password authorization.
$script_name: The script name as a partial URL, for self-refering scripts.
$server_name: The name of the WWW server the script is running under.
$server_admin: The name of the administrator of the WWW server the script is running under.
$server_software: The name and version of the server software.
$virtual_host: When using the virtual host feature of some servers, the name of the virtual host the browser is accessing.
$server_port: The communications port the server is using.
$user_agent: The identity of the remote user's browser software, e.g. "Mozilla/1.1N (Macintosh; I; 68K)"
$user_name: Attempts to obtain the remote user's name, using a variety of environment variables. This only works with older browsers such as Mosaic. Netscape does not reliably report the user name!
$datetime: The current date and time as a standard string.
$unique_id: The Apache web server generated unique ID.

pq_fieldname

Values for the fields are passed to PQ Edit in this format to provide default values for the form's fields. Example, assuming database field: telephone_number is: pq_telephone_number='(314) 555-1111'.

pqsearch_fieldname

Setting this to one as well as setting the corresponding pq_fieldname, will allow setting the search criteria for the record(s) to be displayed. The 'Overview' option may be used with the pq_fieldname and pqsearch_fieldname options.

Overview

Setting this to one (1) will display an Overview ('View Search Set') window with the search parameters set by the pq_fieldname and pqsearch_fieldname parameters.

single_window

Value none or 'ON'. Used to force disabling of multiple window frames.

readonly

A list of fields to display but not allow to be edited.

nondisplay

A list of fields to not display.

no_schemacache

When given a true value, 1, causes PQ Edit to not use the cached copy of the schema and to write out a new schema cache. Note that this applies for the whole PQ Edit session.

pqdebug

When given a true value, 1, causes PQ Edit to run in a debugging mode.

PQ Web Database Parameters

An optional database may be used to store certain parameters used by PQEdit.

The parameters that may be stored in the optional pqweb database are:

helppage

The URL of the helppage to use.

labels

The overridden labels for table fields

web_body_bgcolor

The web pages background color.

pqweb_server

The name of the database server which contains the pqweb database.

pqweb_database

The name of the pqweb database. This defaults to pqweb.

pqweb_username

The username to use when connecting to the pqweb database. This account only needs 'select' permissions.

pqweb_password

The password to use when connecting to the pqweb database.

pqweb_entry

Which pqweb entry to use (the pqweb..pqweb.pq value).

PQ Edit Frames

When PQ Edit is in multiple window frame mode (single_window != 'ON'), the names of the frames which it uses are in the form:

pqedittable for Editing windows
overviewtable for Overview windows

If it is desired, a Frameset HTML window could be constructed prior to invoking PQ Edit which would contain the PQ Edit frames as subframes.

AUTHOR INFORMATION

Brian H. Dunford-Shore brian@ibc.wustl.edu

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Address bug reports and comments to: www@ibc.wustl.edu

CREDITS

Thanks very much to:

David J. States (states@ibc.wustl.edu)

and

Fyodor Krasnov (fyodor@bws.aha.ru)

for suggestions and bug fixes.

BUGS

You really mean 'extra' features ;). None known.

TODO

These are features that would be nice to have and might even happen someday (especially if YOU write it).

More control of the 'Look and Feel' of the HTML page:: (colors, fonts, etc.).
Other types of database servers:: (PostgreSQL, mSQL, mySQL, etc.). (Note: this depends on there being a version of RDBAL and RDBAL::Schema for the database in question).

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)