Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.

Differences Between: [Versions 310 and 401] [Versions 39 and 401] [Versions 401 and 402] [Versions 401 and 403]

This class represent the base generator class where all the needed functions to generate proper SQL are defined. The rest of classes will inherit, by default, the same logic. Functions will be overridden as needed to generate correct SQL.

Copyright: 1999 onwards Martin Dougiamas http://dougiamas.com
License: http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
File Size: 1462 lines (62 kb)
Included or required: 1 time
Referenced: 0 times
Includes or requires: 0 files

Defines 1 class


Class: sql_generator  - X-Ref

Abstract sql generator class, base for all db specific implementations.

__construct($mdb, $temptables = null)   X-Ref
Creates a new sql_generator.

param: moodle_database $mdb The moodle_database object instance.
param: moodle_temptables $temptables The optional moodle_temptables instance, null by default.

dispose()   X-Ref
Releases all resources.


getEndedStatements($input)   X-Ref
Given one string (or one array), ends it with $statement_end .

param: array|string $input SQL statement(s).
return: array|string

table_exists($table)   X-Ref
Given one xmldb_table, checks if it exists in DB (true/false).

param: mixed $table The table to be searched (string name or xmldb_table instance).
return: boolean true/false

getCreateStructureSQL($xmldb_structure)   X-Ref
This function will return the SQL code needed to create db tables and statements.

param: xmldb_structure $xmldb_structure An xmldb_structure instance.
return: array

getTableName(xmldb_table $xmldb_table, $quoted=true)   X-Ref
Given one xmldb_table, this returns it's correct name, depending of all the parameterization.
eg: This appends $prefix to the table name.

param: xmldb_table $xmldb_table The table whose name we want.
param: boolean $quoted To specify if the name must be quoted (if reserved word, only!).
return: string The correct name of the table.

getCreateTableSQL($xmldb_table)   X-Ref
Given one correct xmldb_table, returns the SQL statements
to create it (inside one array).

param: xmldb_table $xmldb_table An xmldb_table instance.
return: array An array of SQL statements, starting with the table creation SQL followed

getCreateIndexSQL($xmldb_table, $xmldb_index)   X-Ref
Given one correct xmldb_index, returns the SQL statements
needed to create it (in array).

param: xmldb_table $xmldb_table The xmldb_table instance to create the index on.
param: xmldb_index $xmldb_index The xmldb_index to create.
return: array An array of SQL statements to create the index.

getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL, $specify_nulls_clause = NULL, $specify_field_name = true)   X-Ref
Given one correct xmldb_field, returns the complete SQL line to create it.

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
param: string $skip_type_clause The type clause on alter columns, NULL by default.
param: string $skip_default_clause The default clause on alter columns, NULL by default.
param: string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
param: string $specify_nulls_clause To force a specific null clause, NULL by default.
param: bool $specify_field_name Flag to specify fieldname in return.
return: string The field generating SQL statement.

getKeySQL($xmldb_table, $xmldb_key)   X-Ref
Given one correct xmldb_key, returns its specs.

param: xmldb_table $xmldb_table The table related to $xmldb_key.
param: xmldb_key $xmldb_key The xmldb_key's specifications requested.
return: string SQL statement about the xmldb_key.

getDefaultValue($xmldb_field)   X-Ref
Give one xmldb_field, returns the correct "default value" for the current configuration

param: xmldb_field $xmldb_field The field.
return: The default value of the field.

getDefaultClause($xmldb_field)   X-Ref
Given one xmldb_field, returns the correct "default clause" for the current configuration.

param: xmldb_field $xmldb_field The xmldb_field.
return: The SQL clause for generating the default value as in $xmldb_field.

getRenameTableSQL($xmldb_table, $newname)   X-Ref
Given one correct xmldb_table and the new name, returns the SQL statements
to rename it (inside one array).

param: xmldb_table $xmldb_table The table to rename.
param: string $newname The new name to rename the table to.
return: array SQL statement(s) to rename the table.

getDropTableSQL($xmldb_table)   X-Ref
Given one correct xmldb_table, returns the SQL statements
to drop it (inside one array). Works also for temporary tables.

param: xmldb_table $xmldb_table The table to drop.
return: array SQL statement(s) for dropping the specified table.

cleanup_after_drop(xmldb_table $table)   X-Ref
Performs any clean up that needs to be done after a table is dropped.

param: xmldb_table $table

getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL)   X-Ref
Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table.

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
param: string $skip_type_clause The type clause on alter columns, NULL by default.
param: string $skip_default_clause The default clause on alter columns, NULL by default.
param: string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
return: array The SQL statement for adding a field to the table.

getDropFieldSQL($xmldb_table, $xmldb_field)   X-Ref
Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table.

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
return: array The SQL statement for dropping a field from the table.

getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL)   X-Ref
Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
param: string $skip_type_clause The type clause on alter columns, NULL by default.
param: string $skip_default_clause The default clause on alter columns, NULL by default.
param: string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
return: string The field altering SQL statement.

getModifyDefaultSQL($xmldb_table, $xmldb_field)   X-Ref
Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table.

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from.
return: array The SQL statement for modifying the default value.

getRenameFieldSQL($xmldb_table, $xmldb_field, $newname)   X-Ref
Given one correct xmldb_field and the new name, returns the SQL statements
to rename it (inside one array).

param: xmldb_table $xmldb_table The table related to $xmldb_field.
param: xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
param: string $newname The new name to rename the field to.
return: array The SQL statements for renaming the field.

getAddKeySQL($xmldb_table, $xmldb_key)   X-Ref
Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table
note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters.

param: xmldb_table $xmldb_table The table related to $xmldb_key.
param: xmldb_key $xmldb_key The xmldb_key to add.
return: array SQL statement to add the xmldb_key.

getDropKeySQL($xmldb_table, $xmldb_key)   X-Ref
Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.

param: xmldb_table $xmldb_table The table related to $xmldb_key.
param: xmldb_key $xmldb_key The xmldb_key to drop.
return: array SQL statement to drop the xmldb_key.

getRenameKeySQL($xmldb_table, $xmldb_key, $newname)   X-Ref
Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table
Experimental! Shouldn't be used at all!

param: xmldb_table $xmldb_table The table related to $xmldb_key.
param: xmldb_key $xmldb_key The xmldb_key to rename.
param: string $newname The xmldb_key's new name.
return: array SQL statement to rename the xmldb_key.

getAddIndexSQL($xmldb_table, $xmldb_index)   X-Ref
Given one xmldb_table and one xmldb_index, return the SQL statements needed to add the index to the table.

param: xmldb_table $xmldb_table The xmldb_table instance to add the index on.
param: xmldb_index $xmldb_index The xmldb_index to add.
return: array An array of SQL statements to add the index.

getDropIndexSQL($xmldb_table, $xmldb_index)   X-Ref
Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.

param: xmldb_table $xmldb_table The xmldb_table instance to drop the index on.
param: xmldb_index $xmldb_index The xmldb_index to drop.
return: array An array of SQL statements to drop the index.

getRenameIndexSQL($xmldb_table, $xmldb_index, $newname)   X-Ref
Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table
Experimental! Shouldn't be used at all!

param: xmldb_table $xmldb_table The xmldb_table instance to rename the index on.
param: xmldb_index $xmldb_index The xmldb_index to rename.
param: string $newname The xmldb_index's new name.
return: array An array of SQL statements to rename the index.

getNameForObject($tablename, $fields, $suffix='')   X-Ref
Given three strings (table name, list of fields (comma separated) and suffix),
create the proper object name quoting it if necessary.

IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED,
NEVER TO GUESS NAMES of EXISTING objects!!!

param: string $tablename The table name.
param: string $fields A list of comma separated fields.
param: string $suffix A suffix for the object name.
return: string Object's name.

getEncQuoted($input)   X-Ref
Given any string (or one array), enclose it by the proper quotes
if it's a reserved word

param: string|array $input String to quote.
return: string|array Quoted string.

getExecuteInsertSQL($statement)   X-Ref
Given one XMLDB Statement, build the needed SQL insert sentences to execute it.

param: string $statement SQL statement.
return: array Array of sentences in the SQL statement.

getConcatSQL($elements)   X-Ref
Given one array of elements, build the proper CONCAT expression, based
in the $concat_character setting. If such setting is empty, then
MySQL's CONCAT function will be used instead.

param: array $elements An array of elements to concatenate.
return: mixed Returns the result of moodle_database::sql_concat() or false.

getSequenceFromDB($xmldb_table)   X-Ref
Returns the name (string) of the sequence used in the table for the autonumeric pk
Only some DB have this implemented.

param: xmldb_table $xmldb_table The xmldb_table instance.
return: bool Returns the sequence from the DB or false.

isNameInUse($object_name, $type, $table_name)   X-Ref
Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).

(MySQL requires the whole xmldb_table object to be specified, so we add it always)

This is invoked from getNameForObject().
Only some DB have this implemented.

param: string $object_name The object's name to check for.
param: string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
param: string $table_name The table's name to check in
return: bool If such name is currently in use (true) or no (false)

getRenameFieldExtraSQL($xmldb_table, $xmldb_field)   X-Ref
Returns the code (array of statements) needed to execute extra statements on field rename.

param: xmldb_table $xmldb_table The xmldb_table object instance.
param: xmldb_field $xmldb_field The xmldb_field object instance.
return: array Array of extra SQL statements to run with a field being renamed.

getCreateSequenceSQL($xmldb_table, $xmldb_field)   X-Ref
Returns the code (array of statements) needed
to create one sequence for the xmldb_table and xmldb_field passed in.

param: xmldb_table $xmldb_table The xmldb_table object instance.
param: xmldb_field $xmldb_field The xmldb_field object instance.
return: array Array of SQL statements to create the sequence.

getRenameTableExtraSQL($xmldb_table, $newname)   X-Ref
Returns the code (array of statements) needed to execute extra statements on table rename.

param: xmldb_table $xmldb_table The xmldb_table object instance.
param: string $newname The new name for the table.
return: array Array of extra SQL statements to rename a table.

getDropTableExtraSQL($xmldb_table)   X-Ref
Returns the code (array of statements) needed to execute extra statements on table drop

param: xmldb_table $xmldb_table The xmldb_table object instance.
return: array Array of extra SQL statements to drop a table.

getReservedWords()   X-Ref
Returns an array of reserved words (lowercase) for this DB
You MUST provide the real list for each DB inside every XMLDB class.

return: array An array of database specific reserved words.

getAllReservedWords()   X-Ref
Returns all reserved words in supported databases.
Reserved words should be lowercase.

return: array ('word'=>array(databases))

addslashes($s)   X-Ref
Adds slashes to string.

param: string $s
return: string The escaped string.

get_nullable_fields_in_index($xmldb_table, $xmldb_index)   X-Ref
Get the fields from an index definition that might be null.

param: xmldb_table $xmldb_table the table
param: xmldb_index $xmldb_index the index
return: array list of fields in the index definition that might be null.