NAME

sqlay_php - small database oriented framework for use with PHP.

SYNOPSIS

sqlay_php is a small (currently not allowing to build complex forms or reports) PHP framework allowing to easily build database oriented pages (data sheets and data forms), simply by setting some variables in a PHP page and calling a function.

Besides from this it may also be used to build a (very) basic portal regardless of the use of any database.

FEATURES

The web pages generated by sqlay_php functions use minimalistic HTML features which should make them viewable by any browser compliant with HTML 3.2. In rare cases, HTML 4.0 features (such as span) may be used with only cosmetic consequences if viewed by an older browser.

Nevertheless, frames and framesets (but no pop-ups) are used with the consequence that ultra-light browsers such as those found in mobile phones are currently not in state to view those pages, or at least a full site (forms and sheets themselves may be viewed, but probably not menus).

Client side scripting language (such as Javascript) is currently not used, but may be in the future in a redundant fashion in order to speed up some operations. This means that the various functionalities (such as enforcement of checks on form completion) would be implemented both client- AND server-side and that the site should stay fully functional if scripting is disabled in the browser.

Cookies and sessions are currently not used either, maybe in the future but then, only as option that must be explicitly enabled in config. Again, this slows down operations and thus requires a powerfull server but ensures that ***any*** browser has access to the pages, without any need for cryptic URL's.

The internal structure is designed to be modulable but is not written in object oriented PHP. Maybe one day, either it will go back (previous attempts had been made in the past in this direction) to object oriented either internally with procedural bindings or the opposite way, but there is no immediate plan to do so.

INVOCATION

Before using any sqlay_php function in your PHP pages, you should first make sure that the directory above (NOT the directory itself) the SqLay library files is in the PHP include path, then you could make sure that a file called sqlay_config.php is found by your page.

Those two operations can be done in various ways, either in your page by hardcoding the sqlay_config.php path in the require directive in your page AND using an ini_set or ini_alter directive with the include_path parameter for the directory above SqLay libraries, or by putting both paths in the host's php.ini (generally requires root privilege), or by setting a php_value of type include_path with both paths either in an .htaccess file or in a vhost or even the main httpd.conf file, or any combination of those methods.

Then, you should fill some site wide global variables in the sqlay_config file. See sqlay_config (5) for details on those variables.

Then, you may begin writing pages using sqlay_php functions. In order to do that, you need to include the sqlay library page, by using a require directive and referencing it by prepending it's name by the 'sqlay/' prefix, then you should set some global variables inside the page (or in another file included from the page, as you like) and finally, call a function.

Most sqlay_php objects use GET and / or POST parameters to define their state following the way they are called. If you wish to use those objects in your pages, you should understand that some GET / POST parameter names have special meaning for them. Those are described in the next section.

OPTIONS

Here are the GET / POST parameters (similar to command line options) that are used internally by sqlay_php objects.

• action GET and / or POST string, setting the mode of a data object.
  • search Switch to search form (GET).
  • search Send data as search criterias (POST).
  • list Switch to data sheet mode (GET).
  • prepare Switch to new record wizard mode (GET+POST).
  • add Switch to new record form mode (GET).
  • insert Send new data to be saved (POST).
  • view Switch to record visualization form mode (GET), requires id parameter.
  • edit Switch to modify record form mode (GET), requires id parameter.
  • update Send data to be updated (POST), normally based on previous values.
  • delete Switch to delete record confirmation page mode (GET), requires id parameter.
  • delete Send data to be deleted (POST), normally based on previous values.
  • help Switch to help page (GET), accepts context as other option.
• context GET string, same values as action. Only effective with action=help.
• goto GET page name without .php extension, allows page referencing from anywhere for display inside it's directory frameset. Only effective on index.php pages.
• sort GET string, SQL order by expression. Only effective with action=list.
• filter GET string, SQL where clause. Only effective with action=list.
• id, id1, id2, ... GET any, value(s) of (compound) primary key field(s) used to to select a record for view, update or delete.
• field GET integer, field number for which, in view action and raw format, to output a single value. Must be used in combination with id GET parameter.
• format GET string, output format, to be chosen in html, ooo and raw, optional, default html. Note that the most advanced is html. ooo currently supports only datasheets exports and raw supports only single values (i.e. images like png. Must then be used with field GET parameter).
• view GET string, in menu or home. Only used in directory indexes for frameset generation.
• tab (WIP) GET integer, tab number to be displayed in sqlay_dp style pages. Currently only usable with action=view.

FUNCTIONS

Here are the functions which may be called inside pages. Mention may also be done of other functions which should theoretically NOT be used as they are very likely to change in future releases without notice.

Database functions:

• sqlay_sql_connect ( $string ) Connects to a database using string as connection parameters and returns a connection number.
• sqlay_sql_pconnect ( $string ) Same as above.
• sqlay_sql_select ( $handle, $string ) Runs string as select SQL statement on the connection number given as handle and returns a resultset number.
• sqlay_sql_exec ( $handle, $string ) Runs string as SQL statement not returning any resultset on the connection number given as handle and returns an amount of affected rows or an sql error number if negative.
• sqlay_sql_numrows ( $result ) Returns the number of rows for the resultset whose number is given as result.
• sqlay_sql_numfields ( $result ) Returns the number of fields for the resultset whose number is given as result.
• sqlay_sql_fetchrow ( $result, $row ) Returns an array of string values from the resultset whose number is given as result, eventually for the row given as parameter.
• sqlay_sql_fetchvalue ( $result, $row, $field ) Returns a string value from the resultset whose number is given as result, eventually for the row given as parameter, and for the field given as parameter.
• sqlay_sql_fieldname ( $result, $field ) Returns as string the name of the database field whose number is given as field in the given resultset.
• sqlay_sql_resultstatus ( $result ) Returns TRUE if the given resultset is worth while further trying to fetch rows, FALSE otherwise. This function must be used with some backends but may be omitted for most if portability is not an issue.
• sqlay_sql_freeresult ( $result ) Frees the resultset whose number is given as parameter.
• sqlay_sql_disconnect ( $handle ) Releases the connection whost number is given as parameter.

Authentication Functions:

• bool sqlay_auth_group_user ( $user = $userId, $group = $defaultGroup ) Returns TRUE if user belongs to group, FALSE otherwise.

Application Functions:

• sqlay_app ( $src ) Loads, source and run a php file. This function is a convenience function. It is not mandatory to use it.

Output Functions:

• sqlay_out_info ( ) Display environnement informations over SqLay variables and versions, RDBMS, httpd, PHP versions, ....

Page functions:

• sqlay_out_page_name ( ) Returns standardized page name including site and directory short names.
• sqlay_out_page_caption ( ) Returns standardized page caption including site and directory long names.
• sqlay_out_page_header ( ) Outputs standardized head and header for the page.
• sqlay_out_page_footer ( ) Outputs standardized footer and foot for the page.
• sqlay_out_page_body ( ) Outputs pending dataobject contents.
• sqlay_out_page ( ) Outputs full standard page including head, header, pending dataobject contents, footer and foot.

Dataobjects functions:

• sqlay_df ( $action = 'view' ) Run a data form. There is no interest to call this function directly unless for read-only forms. Preferably call sqlay_do instead.
• sqlay_ds ( ) Run a datasheet. May be called directly on read-only datasheets in order to avoid loading all the logic of sqlay_do.
• sqlay_do ( ) Run a data object, which may be in turn a form or a sheet.
• sqlay_dp ( $style ) Run a data page, which is supposed to contain at least 2 data objects, or the same in at least 2 different states. Style is either 'googlish' or 'master/detail'. Default depends on context. This function is still in alpha stage.

VARIABLES

SqLay behaviour is customized by the use of global arrays. As seen above, functions don't accept any (or almost) parameters, eventhough this may change in future releases (probably in a fashion similar to ncurses library, offering fully qualified functions but also shortcut functions using current objects defined in global variables).

The general behaviour is defined in a global array called $sqlay, documented in sqlay_config (5).

The menu system also uses a global array called $sqlay_menu which contains informations on the global (site wide) menu. This is also documented in sqlay_config (5) as all menu frames using it also need to know about the global menu.

All data object functions also use a global array called $table which contains a description of the database table or view around which the object operates. It is very likely that this would be changed into sqlay_table or something similar in future releases (in which case backward compatibility with existing code would be ensured as much as possible).

The main sub-array of the table array is called $table['fields'] which contains the description of the fields of the table or view.

Filling those arrays is considered to be mandatory before calling any database function.

Some variables may accept a list which is a coma separated string, i.e. action list may be 'add,list'.

$table[ ] array indices:

• name String, used to build 'select *' queries if select not given, no default, mandatory. Inserts, updates and deletes will be executed on this table.
• select String, SQL query used to fetch the data, no default, optional.
• filter String, SQL where clause unconditionnally applide to the table in all situations, no default, optional.
• sort String, field name(s list) optionally including DESC SQL keyword(s) used to build order by clause for data sheet, no default, optional.
• nbflds Integer, amount of fields to process in sheets before giving up even if underlying query returns more fields. Optional, dynamically set in retrieve if empty.
• listable Mixed (boolean or URL), whether or where users should be able to display the list, default TRUE.
• sortable Mixed (boolean or URL), whether or where users should be able to sort the list, default FALSE.
• searchable Mixed (boolean or URL), whether or where users should be able to find a search form, default FALSE.
• insertable Mixed (integer or URL), > 0 if users should be able to add a new record, default 0. If 2, go back to add form after insertion.
• editable Mixed (boolean or URL), whether or where users should be able to edit existing records, default FALSE.
• viewable Mixed (boolean or URL), whether or where users should be able to view a record card, default FALSE.
• deletable Mixed (boolean or URL), whether or where users should be able to delete existing records, default FALSE.
• statable Integer, > 0 if datasheet should display a statistics / totals additional row, > 1 if legend for the aggregate function should be displayed as well, default 0.
• uploadable Mixed (boolean or path), whether or where users may upload files, default FALSE.
• exportable Boolean, whether users should be able to export table data into OpenOffice.org format, default FALSE.
• properties String, function name which, if callable, will be executed without args after data loading and before database operations, no default, optional. May be replaced by real events in the future.
• sametable Boolean, only used in html ds, wheter to avoid writing table opening and closing tags, optional, default FALSE, only usable in readonly sheets (otherwise generates inconsistent html).
• center Boolean, whether to center the data objet in it's page, default FALSE.
• border Integer, width of HTML table border, default 1.
• width Integer or percent, width of the HTML table, no default, using browser default.
• attrs String, backend dependant attributes string for the table, only used in html ds, optional, default class="sqlds_row_even" and class="datasheet" border="$table[border]" width="$table[width]".
• samerow Boolean, only used in html ds, wheter to avoid writing row opening and closing tags, optional, default FALSE.
• rowattrs String, backend dependant attributes string for the row, only used in html ds, optional, default alternatively class="sqlds_row_even" and class="sqlds_row_odd".
• legend Boolean, whether to display a legend ('(*) Mandatory fields.') under the table, default TRUE.
• grouped Boolean, whether to perform a group break on this field in report stylish sheets, default FALSE.
• title Mixed (string or array), string, or if array string of locale subindex, used as legend displayed in bold above detail table in master/detail pages, no default. See below.
• header Array describing the sheet header row, see below.
• margin Array describing the sheet margin column, see below.
• help Array containing contextual help info, see below, no default, optional.
• events Array of events, see below, no default, optional.
• key Numbered array containing information on the table primary key, no default, mandatory for updates or deletes.
• fkey Numbered array containing information on the detail table foreign key, no default, mandatory for linking master/detail forms.
• groups Numbered array describing groups in report stylish sheets.
• fields Array of fields, see below, defaults to fields list returned by the query.
• row Numbered array of strings storing values of the current row, available read-write for triggers (see events below). Dynamically filled in retrieves.
• tab (WIP) Integer, number of the tab on which table will be displayed.
• ... T.B.W.

$table[$action]['buttons'][ n ][ ] numbered sub-array indices for custom buttons:

• img String, image file name.
• text String, button text.
• title String, button title.
• ref String, button reference (URL).
• parm String, button parameters (URL parameters).
• target String, button target frame.

$action refers to values of the GET parameter action. Meaninfull values are view, list, add, edit and search. Others are ignored.

$table['title'][ ] sub-array indices:

• autohide Boolean (1 or 0), whetre to hide the title if the datasheet is empty.
• text Mixed (string or array), string, or if array string of locale subindex, used as legend displayed in bold above detail table in master/detail pages, no default.

$table['header'][ ] sub-array indices:

• hidden Boolean, whether to show column headers in sheet, default FALSE.
• autohide Boolean, whether to hide column headers in sheet if no rows found, default FALSE.

$table['margin'][ ] sub-array indices:

• width Integer, width of buttons margin for sheet.

$table['help'][ ] sub-array indices:

• text String, help text shown to users having clicked the question mark button.

$table['events'][ ] sub-array indices:

• execbefore String, function name which, if callable, will be executed without arg before execution of an insert, update or delete operation done from a sqlay_do type form, and before operation specific event (insertafter, updateafter or deleteafter).
• execafter String, function name which, if callable, will be executed without arg after successfull execution of an insert, update or delete operation done from a sqlay_do type form, and after operation specific event (insertafter, updateafter or deleteafter).
• insertbefore String, function name which, if callable, will be executed without arg before execution of an insert operation done from a sqlay_do type form.
• insertafter String, function name which, if callable, will be executed without arg after successfull execution of an insert operation done from a sqlay_do type form.
• updatebefore String, function name which, if callable, will be executed without arg before execution of an update operation done from a sqlay_do type form.
• updateafter String, function name which, if callable, will be executed without arg after successfull execution of an update operation done from a sqlay_do type form.
• deletebefore String, function name which, if callable, will be executed without arg before execution of a delete operation done from a sqlay_do type form.
• deleteafter String, function name which, if callable, will be executed without arg after successfull execution of a delete operation done from a sqlay_do type form.
• rowretrieved String, function name which, if callable, will be executed without arg after retrieval of a row in a datasheet.
• listbefore String, function name which, if callable, will be executed without arg in the start of an sqlay_ds call.
• listafter String, function name which, if callable, will be executed without arg at the end of an sqlay_ds call.

$table['key'][ n ][ ] sub-array(s) indices:

• name String, database field name, no default.
• order String, database field number, preferably used over name for updates, no default.

n, starting at 0 should be given for each field of a compound primary key.

$table['fkey'][ n ][ ]. This array is used in master/detail pages by the detail table. Numbered sub-array(s) indices:

• name String, database field name, no default.
• order String, database field number, preferably used over name for updates, no default.
• operator String, operator used to build the join in master / detail pages, default =.

n, starting at 0 should be given for each field of a compound foreign key.

$table['groups'][ ]. This array is used in sheets to skip display of certain fields having same value among rows. Those are alpha level variables which may change in future releases. Sub-array(s) indices:

• number Integer, number of groups (may be deprecated). Optional, no default.
• function String, custom function name which, if callable would be called on each row with, as paramater, an array containing the values of the current row, optional, no default.
• [ n ]header Array allowing to perform operations on group header.
• [ n ]header[ text ] String, text to be displayed above group header.
• [ n ]footer Array allowing to perform operations on group footer.
• [ n ]footer[ text ] String, text to be displayed under group footer.
• [ n ]footer[ function ] String, custom function name which, if callable, would be called after group footer.

n, starting at 0 should be given for each group (whose amount is computed from the number of fields having the grouped flag set).

$table['fields'][ n ][ ] numbered sub-array indices:

• name String, field name, default to database field name returned by the query.
• code String, database field name, preferably used over name for updates, no default.
• label Array, with two possible key: hidden. If set to true, label won't be shown in forms, default false.
• ufn Mixed, array or string, user friendly field name used as label, default name. If array, last index should be a language code which will be replaced at runtime by the value of $sqlay['out']['locale']. To be deprecated, at least in forms, in favor of [label][text]
• description String, description used as title HTML tag on label and rendered as pop-up by most browsers, no default.
• parameter Boolean, whether field should be filled in a first form before showing the final new record form, default FALSE.
• hidden Mixed (boolean or actions list), whether field is visible inside form or list, default FALSE.
• sortable Boolean (integer), whether field is sortable in datasheets, default TRUE.
• height Integer, height of form input, default 1.
• width Integer, width of form input, no default.
• adjacent Boolean, whether field should be packed on the right side of previous field in data forms, default FALSE.
• prefix String, to be displayed before field in sheets, no default.
• suffix String, to be displayed after field in sheets, no default.
• samecell Boolean, whether field should be glued on the right side of previous field in data sheets, typically used in report stylish sheets, default FALSE.
• rowspan Integer, only used by html ds, amount of rows to span the cell, no default.
• cellattrs String, backend dependant cell attributes, only used by html ds, default valign="top" align="datatype dependant]".
• link String, to be displayed on the field in sheets as hyperlink, no default. Admits substitutions, a.o. '#--#' for the field value. Behaves smartly and prepend 'a href=http://' or 'mailto' if not full anchor given.
• linkif String, same as above but only applied if value has any length.
• datatype String, datatype of field, no default. See sqlsc (1) for list of datatypes.
• mimetype String, mime type of data for blob field, allowing to perform check on data entry, optional, no default. Only first half (before '/', i.e. 'image') of mime type may be given as well in order to allow lighter checks.
• length Integer, maximum length of entry, no default.
• notnull Boolean, whether field completion is mandatory, default FALSE.
• null Boolean, whether emptying the field on update should write NULL in the database instead of '', default FALSE.
• default Any, default value to use for inserts, no default, optional.
• defaultenforce Mixed (boolean or actions list), whether default value should be enforced on inserts or updates even if the field is flagged as readonly, default FALSE.
• readonly Mixed (boolean or actions list), whether field may be updated or inserted, default FALSE.
• counter Boolean, whether field autoincrements, default FALSE.
• computed Boolean, whether field is a computed field, default FALSE.
• grouped Integer, group number on which this field is grouped, no default.
• icase Boolean, whether searches on this fields sould be case insensitive, default TRUE.
• dcase Boolean or list of 'insert' and 'update' actions and / or 'warn' flag, whether value should be lower cased on the fly on database operation with or without warning (default without if set). Optional, default FALSE.
• ucase Boolean or list of 'insert' and 'update' actions and / or 'warn' flag, whether value should be upper cased on the fly on database operation with or without warning (default without if set). Optional, default FALSE.
• unspc Boolean or list of 'insert' and 'update' actions and / or 'warn' flag, whether spaces should be stripped from value on the fly on database operation with or without warning (default without if set). Optional, default FALSE.
• strip String, which should be silently stripped from value on the fly on database operation. Optional, no default.
• upper Boolean, whether value should be upper case. Optional, default FALSE.
• lower Boolean, whether value should be lower case. Optional, default FALSE.
• nospc Boolean, whether value shhould be free of spaces. Optional, default FALSE.
• search String, search options list, understanding 'exact' meaning that no '%' would automatically be added to the end of the value, 'strict', meaning that '=' operator should be used instead of 'like', and 'interval' meaning that the field will be duplicated in the search form and that a search will be performed between both values. Optional, no default.
• select String, either a select query to run against current connection to retrieve 1 or 2 values (data value and display value), or a list of value( pair)s beginning with keyword list, whose value( pair)s are separated by '|' and whose data values may be separated from display values by ':', in order to fill a drop down listbox, no default.
• file Boolean, whether field is an upload field (requires $table['uploadable'] to be set), default FALSE. Note that no action is associated with the uploaded file which will be uploaded as file into $sqlay[app][tmp], not as blob field into the database (hint: think about filesystem permissions and UID of httpd process in case of failure to use it).
• check String, user callable function name accepting a single string argument for the value and returning an empty string on no error, or a non-empty string as message on error. Optional, no default.

n, starting at 0 should be given for each field of the $table['select'] query if any, or for each field of the underlying database table, unless $table['nbflds'] is set in which case nor processing nor display display will be done at least in sheets on fields numbered higher than this value.

$table['fields'][ n ]['label'][ ] sub-array indices:

• text Mixed, array or string, user friendly field name used as label in forms, default [field][name]. If array, last index should be a language code which will be replaced at runtime by the value of $sqlay['out']['locale'].
• hidden Boolean, if true, label won't be shown in forms, default false.
• class String, HTML class to be used for the label in forms. Optional, default sqldf_fieldname.
• prefix String, to be displayed before label text in forms. Optional, no default.
• suffix String, to be displayed after label text in forms. Optional, no default.

NOTES

This page is currently not extremely accurate, a.o. some default values may be wrong (i.e. saying FALSE when it is TRUE) and should be checked.

Pls understand that this is an alpha release and there may be unexpected results in untested use of combination of variables. Also the API is not considered to be fixed, meaning that variables, functions names or parameters lists may change, eventhough in such cases, backward compatibility files would (hopefully :) be offered for at least one major version.

Risks of damaging your database by breaking the integrity of your data or allowing unauthorized access (SQL injection or Cross-Site Scripting) are present eventhough I do my best to minimize them. Please be aware of this and avoid too large exposure (currently no suited for Internet use, unless with most features disabled).

BUGS

See the NOTE above for important security warnings.

AUTHOR

E. Lurquin <sqlay@hitud.net>

SEE ALSO

sqlay(1), sqlayrc(5), the source code.