[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15. SQL manipulation CGI

This chapter lists all the invocations of the sqledit package and is a reference for the available parameters, the template used and the functionality provided.

images/fctsqledit

15.1 Record edit form (edit)  
15.2 Record update (update)  
15.3 Search form (search_form)  
15.4 Record insert form (sinsert_form)  
15.5 Record insert (sinsert)  
15.6 Record remove (remove)  
15.7 Remove confirm (remove_confirm)  
15.8 Search table (search)  
15.9 Hook search (hook_search)  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.1 Record edit form (edit)

images/sqleditedit

`Description'
Generate an HTML form that displays the content of the record from table, pointed by primary, with suitable input boxes for modification of the values contained in each field.

`Template file'
See section sqledit_edit.html.

`Parameters'

`context (mandatory, edit)'
`table (mandatory)'
`primary (mandatory)'
`comment (optional)'
The free text that will be instantiated as the _EDITCOMMENT_ tag.
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.2 Record update (update)

images/sqleditupdate

`Description'
Change the values of the fields of the record from table, pointed by primary, with the values from the parameters. The HTML page displayed as a response it the edit form for the modified record, See section Record edit form (edit).

The _EDITCOMMENT_ tag of the result page will be replaced with a list of the modified fields.

To set the value of a field to the empty string, one should fill the input box with a white space. An empty input box means that no modification is required for this field. This way of reseting the content of a field may seem a bit strange but it is necessary because there is no way in HTML to distinguish a parameter that was set to the empty string by the user from a parameter that was originally the empty string.

`Template file'
See section sqledit_edit.html.

`Parameters'

`context (mandatory, update)'
`table (mandatory)'
`primary (mandatory)'
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.3 Search form (search_form)

images/sqleditsearchform

`Description'
The HTML form to search in a table may either be generated by a cgi-bin or be a static HTML page. The advantage of generating the search form is that you don't have to explicitly list all the search criterion, they can be generated automatically by the cgi-bin.

The search form works as a basic query by example. Each parameter may contain a value. The search process will retrieve all the records of the table that match all the provided values.

For instance, if a table has the url field and that the parameter url is provided with the http://www.ecila.fr/ value, only the records whose field url contains http://www.ecila.fr/ will be retrieved.

The values from the parameters are translated to SQL expressions according to the data type of the corresponding field. All the expressions generated are joined with the and keyword. The resulting expression is used as a where clause to retrieve records.

Here is a table that shows which where expression is generated depending on the data type of the field and the value entered by the user.

`integer or time'
`value'
field = value
`< value'
field < value
`> value'
field > value

`char'
like 'value'

`set or enum'
like '%value%'

It is not possible to specify a negation, search for null fields or to change the and keyword to or.

Note that the default values for fields are not shown on the search form.

`Template file'
See section sqledit_search_form.html.

`Parameters'

`context (mandatory, search_form)'
`table (mandatory)'
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.4 Record insert form (sinsert_form)

images/sqleditinsert

`Description'

The HTML form to insert a new record into the designated table may either be generated by a cgi-bin or be a static HTML page. The advantage of generating the insert form is that you don't have to explicitly list all the fields, they can be generated automatically by the cgi-bin.

The table field names parameters of the cgi-bin may carry values that will be used as default values of the generated HTML form. If no value is provided in the parameter list, the default value, as defined by the database description will be used instead. If there is no default value in the database description for a given field, the value is set to the empty string.

`Template file'
See section sqledit_sinsert_form.html.

`Constraints'

See section SQL editor configuration file.

`Parameters'

`context (mandatory, sinsert_form)'
`table (mandatory)'
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.5 Record insert (sinsert)

`Description'

Request to insert a record into the table designated by the table parameter. The record is initialized with the values specified by the table parameters, See section Database table parameters. When the insertion is complete, the record editing form is called with the comment parameter set to Record inserted, thus allowing to check that the inserted record is indeed what was expected.

`Template file'
See section sqledit_edit.html.

`Parameters'

`context (mandatory, sinsert)'
`table (mandatory)'
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.6 Record remove (remove)

`Description'

Request to remove a record as designated by primary from table. The record will not be removed immediately. Instead a form is generated to ask for confirmation, See section Remove confirm (remove_confirm).

`Template file'
See section sqledit_remove.html.

`Parameters'

`context (mandatory, remove)'
`table (mandatory)'
`primary (mandatory)'


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.7 Remove confirm (remove_confirm)

`Description'

This action should only be called after a first request from the user to remove a record, See section Record remove (remove). The record whose primary key matches primary is deleted from the table table.

`Template file'
See section sqledit_remove_confirm.html.

`Parameters'

`context (mandatory, remove_confirm)'
`table (mandatory)'
`primary (mandatory)'


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.8 Search table (search)

images/sqleditsearch

`Description'

This action is run after the search form was filled. A where clause is built on the basis of the query by example defined by the user, See section Search form (search_form).

The records found are ordered using the order parameter.

In addition to the query by example defined by the user, the limit expression limit the context of the search. It must be a valid where expression.

The records found are displayed paginated, See section Multipage results template.

The links_set parameter, if defined, is used to display records linked to each record found according to the relational specifications described in the relations.spec configuration file, See section Relational constraints. Please note that links_set may only refer to tables described in the relations.spec file. Each record found is displayed below the current record with a margin, as shown on the screen shot above.

The generic syntax of links_set is a tree description using nested parentheses.

 
links_set: table or
           tree_spec

tree_spec: table or
           tree_spec,tree_spec or
           table(tree_spec)

table: name of a table

For instance let's pretend that you have a database with the following relational structure, properly described in the relations.spec file.

 
table1 ----------> table2 ----------> table4
       |
       |---------> table3

You would like to display the records from table2 for each record found in table1. The value of links_set you should use for this purpose will be:

 
links_set=table1(table2)

record1_table1
  record3_table2
  record10_table2
record2_table1
  record7_table2
record3_table1
  record5_table2
record4_table1
...

If you would like to display the records from table4 linked indirectly to table1 thru table2, the value of links_set you should use for this purpose will be:

 
links_set=table1(table2(table4))

record1_table1
  record3_table2
    record100_table4
  record10_table2
    record10_table4
    record7_table4
    record5_table4
record2_table1
  record7_table2
record3_table1
  record5_table2
    record23_table4
    record17_table4
record4_table1
...

If you would like to display the records from table2 and table3 both linked to table1, the value of links_set you should use for this purpose will be:

 
links_set=table1(table2,table3)

record1_table1
  record3_table2
  record10_table2
  record1_table3
record2_table1
  record7_table2
  record10_table3
  record27_table3
record3_table1
  record5_table2
record4_table1
...

When a table is linked to another in the relational specification, it is always possible to specify links in both ways. To continue the example above, all the following case would be legal, with different results, of course.

 
links_set=table4(table2(table1(table3)))
links_set=table2(table1,table4)

Whenever the exploration of the records dive deeper in the relational specification described in links_set, the _MARGIN_ tag becomes wider. Let's continue with the example above and a links_set set to table1(table2(table4)). When displaying the records from table1 the _MARGIN_ and _MARGINTABLE_ tags are the empty string. When displaying the records from table2 the _MARGIN_ tag is one (empty) cell table wide and _MARGINTABLE_ tag is one cell table wide, filled with the string table2. When displaying the records from table4 the _MARGIN_ tag is two (empty) cells table wide and _MARGINTABLE_ tag is two cells table wide, the last one filled with the string table4.

The only, very important constraint, is that the first table mentioned in the links_set parameter must be the same as the value of the table parameter.

`Template file'
See section sqledit_search.html.

`Constraints'

See section SQL editor configuration file.

`Parameters'

`context (mandatory, search)'
`table (mandatory)'
`page (optional, default 1)'
`page_length (optional, default 10)'
`order (optional)'
The order clause of the generated SQL order.
`links_set (optional)'
Display tree specification of the form table1(table2,table3(table4),table5). The table must have the value table1.
`limit (optional)'
A valid where expression that will be added to the generated where expression using the and keyword.
`fct_name'
If set to select, the select link is added to the _LINKS_ tag. The associated action is to return to the calling cgi-bin with the primary key of the corresponding record.
`table parameters'
See section Database table parameters.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

15.9 Hook search (hook_search)

`Description'
Search the full text database coupled with the SQL database and display the records found. The query is built following the instructions found in the configuration file, See section Hook Fulcrum configuration file.

`Template file'
See section hook_search.html.

`Parameters'

`context (mandatory, hook_search)'
`query_text (optional)'
The query typed by the user.
`query_flexion (optional)'
The possible values are:
  • French
  • none
If the value of this parameter is null or set to none, the words from the user question are used verbatim.

If the value of this parameter is set to French, the words from the question are transformed according to the rules of the French language for plural and conjugation. The resulting list of words is searched instead of the original word.

`query_expand (optional)'
The possible values are:
  • or
  • and
  • phrase

If the value of this parameter is null, the question is rephrased to express that we are much interested to find the records that match the question exactly; still quite interested to find the records that match all the words of the question, even if they are scattered over the text; less interested by the documents that contain at least one word from the question but not all of them.

If the value of this parameter is or, we are equally interested by all the records that contain at least one word from the question.

If the value of this parameter is and, we are only interested by the records that contain all the words from the question, even if they are scattered over the text.

If the value of this parameter is phrase, we are only interested by the records that contain the exact phrase that we wrote as a question, each word in order.

`query_<fieldname> (optional)'

Each of these parameters must be built with a valid full text database fieldname as defined in the configuration file for instance, See section Hook Fulcrum configuration file.


[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

This document was generated by root on November, 18 2003 using texi2html