Query Methods and Subroutines

There's a general convention to use the PRE, POST and POST_NO methods for handling fields in the tables and INSERT, UPDATE and DELETE methods for defining complete queries.

SELECT

Method Name	Arguments	Description	Default Contents	Example Usage
SELECT_FIELDS		A list of fields to be inserted into table's select query.	<%method SELECT_FIELDS> <&SELF:FIELDS&> </%method> By default identical to the FIELDS method.	<%method SELECT_FIELDS> nome, cognome, codice_fiscale, descrizione, indirizzo, provincia, citta, tel1, tel2, cell_sms, email, login, '' as password, no_psw_expiration </%method>
JOIN_TABLES		Is used for defining additional join clauses.	<%method JOIN_TABLES> <&SELF:SCHEMA_FROM&> </%method>	See data/public/messages_users_detail.mql and data/public/user_messages.mql
WHERE		A contents of WHERE statement.	Empty.	<%method WHERE> zone.id_coordinatore = soci.id </%method>
EVAL_WHERE		Evaluates the query provided by the browser. It is called from SelectWhere() to check the SQL query for unauthorized access (sql injection).	Method does not exist by default.
	$where	The parameter contains the query from the browser.
SELECT_WHERE		Is passed to the EvalSqlWhere().	<%method SELECT_WHERE> <&SELF:WHERE&> </%method>
PRE_SELECT		The method is called just before SELECT query.	Method does not exist by default.	See data/public/logs_report.mql
	$PARAMS	The argument contains optional parameters for the prepared query.
	$ID		Parameter undefined by default. When defined it is compared to <&SELF:KEY&> = <% encodeSql($ID)
	$WHERE	WHERE statement.
DIV_SELECT

INSERT, UPDATE, DELETE

Method Name	Arguments	Description	Default Contents
UPDATE		Update an existing record. The method is called from Array2UpdateSql() function where it is handled similarly to the INSERT method. Prior to the insertion it is checked whether the DELETE_IF_FIELD_EMPTY method must be handled.	update <&SELF:SCHEMA_FROM&> set <%$UPDATES%> where <&SELF:KEY&> = <% encodeSql($KEY) %>;
INSERT		Insert a new record. The metod is called from Array2UpdateSql() function which uses it with the Perl DBI. The Array2UpdateSql() first makes a prepared query then it executes it with the @{$LIST_VALUES} parameters.	insert into <&SELF:SCHEMA_FROM&> (<%$FIELDS%>) values (<%$VALUES%>); If the key is not provided, it is retrieved from the sequence: select currval('<&SELF:SERIAL_SEQ&>');
DELETE		Delete a record. The method is called from Array2DeleteSql() function, from the delete method and also during handling of the UPDATE query. Examples of the overloaded DELETE method can be found in: /opt/masonsql/htdocs/data/public/anagrafiche.mql , /opt/masonsql/htdocs/data/public/funzioni.mql files.	delete from <&SELF:SCHEMA_FROM&> where <&SELF:KEY&> = <% encodeSql($KEY) %>;
	$NEW_KEY	A new primary key - if provided.
	$FIELDS	List of field names: field1, field2, ...
	$FIELDS_INSERT	A hash of the field names with their values. The $FIELDS, $VALUES and $LIST_VALUES are extracted from the $FIELDS_INSERT.
	$VALUES	A list of question marks: ?,?,...
	$LIST_VALUES	An array of values $LIST_VALUES->[n] used during $sth->execute(); The list can be modified.
	$UPDATES	A list of fields to be updated: field1 = ?, field2 = ?, ...
	$FIELDS_UPDATE	A hash of the field names with their values.
	$LIST_VALUES	An array of values $LIST_VALUES->[n] used during $sth->execute(); It can be modified.
	$KEY	A primary key.
	$KEY	A primary key.

PRE, POST, POST_NO

The hooks for these methods are available to be executed prior or after the INSERT, UPDATE or DELETE queries. The methods can generate a SQL code depending on which user is connected. The methods are used often in the .mql files, many examples of their use can be found in the /opt/masonsql/htdocs/ directory tree.

Group Name	Method Name	Arguments	Description
PRE_			The PRE methods are called before the main query is executed.
	PRE_INSERT
		$FIELDS_INSERT	Is a hash pointer. The $FIELDS, $VALUES and $LIST_VALUES are extracted from the $FIELDS_INSERT.
		$DUP_KEY	$Params->{'dup_key'}
		$PARAMS	A parameters for the prepared query must be added to the \%params
	PRE_UPDATE		The PRE_DELETE, DELETE are also called just before PRE_UPDATE if any of the fields from DELETE_IF_FIELD_EMPTY is to be updated with an empty value.
		$KEY	A primary key value.
		$FIELDS_UPDATE	Is a hash pointer. The $UPDATES and $LIST_VALUES are extracted from the $FIELDS_UPDATE.
		$PARAMS	A parameters for the prepared query must be added to the \%params
	PRE_DELETE
		$KEY	A primary key value.
		$PARAMS	A parameters for the prepared query must be added to the \%params
POST_			The POST methods are called after the main query was executed.
	POST_INSERT
		$KEY	A primary key value.
		$DUP_KEY	$Params->{'dup_key'}
		$ROW	A contents of a record.
		$FIELDS_INSERT	Is a hash pointer.
		$PARAMS	A parameters for the prepared query must be added to the \%params
	POST_UPDATE
		$KEY	A primary key value.
		$ROW	A contents of a record.
		$FIELDS_UPDATE	Is a hash pointer.
		$PARAMS	A parameters for the prepared query must be added to the \%params
	POST_DELETE
		$KEY	A primary key value.
		$PARAMS	A parameters for the prepared query must be added to the \%params
POST_NO_			The POST_NO methods are called when the main query was not executed.
	POST_NO_INSERT
		$KEY	A primary key value.
		$DUP_KEY	$Params->{'dup_key'}
		$ROW	A contents of a record.
		$PARAMS	A parameters for the prepared query must be added to the \%params
	POST_NO_UPDATE
		$KEY	A primary key value.
		$ROW	A contents of a record.
		$PARAMS	A parameters for the prepared query must be added to the \%params
	POST_NO_DELETE
		$KEY	A primary key value.
		$PARAMS	A parameters for the prepared query must be added to the \%params

Compared to the use of editable views it is easier to manage code, inside the MasonSQL .mql files.
The sql rules that must be used with the editable views are complicated, hard to understand even by DBAs and consequently not practical.

Auxiliary Methods

Method Name	Arguments	Description	Default Contents
DELETE_IF_FIELD_EMPTY		Contains a list of field names. Defines which field causes execution of PRE_DELETE and DELETE on the specified row prior to UPDATE, when the field is empty.	By default the method does not exist.
ENTITY_CHILDREN
ENTITY_FIELDS
ENTITY_RELATIONS
DB_LOG		Add variations to logs table
	$type	update type (insert, update, delete)
	$id	record id (PK)
	$fields	hash of modified fields
	$old_fields	hash of original fields	undef
	$table_name	table name (with schema)	undef

Query Subroutines

Name	Arguments	Description
Array2UpdateSql()		The subroutine is used for updating or inserting records sent by GUI in an array format. The array is passed from gotoRecords method from the lib/databinding.js in the update parameter and it contains only record-sets of changed or inserted fields. The record-set exchanged between a server and a browser are in a array format with the Primary Key in the first column and fields in the same positions as defined with the FIELDS method from the .mql file.
Call_OutFieldFilter()		See the _OUT_FILTER method.
Call_InFieldFilter		If the _IN_FILTER method is defined, applies the filter to the specified field.
EvalSqlWhere()
ExecQuery()
PrepareQuery()
SelectWhere()
SqlSelect()
SqlWhere()
str2sql_js_delimited()		It returns a string adapted to be inserted into a SQL query, or to be inserted in a JavaScript code.