SqlUtil::AbstractTable Class Reference

the base abstract class for the table implementation More...

#include <AbstractTable.qc.dox.h>

Inheritance diagram for SqlUtil::AbstractTable:

Public Member Methods
AbstractCheckConstraint	addCheckConstraint (string cname, string src, *hash< auto > opt, *reference< string > sql)
	adds a check constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
AbstractColumn	addColumn (string cname, hash< auto > opt, bool nullable=True, *reference lsql)
	adds a column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
	addCustomCopOperator (string name, hash< auto > operator)
	register custom user column operator for this table object More...
AbstractForeignConstraint	addForeignConstraint (string cname, softlist cols, string table, *softlist tcols, *hash< auto > opt, *reference< string > sql)
	adds a foreign constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
AbstractIndex	addIndex (string iname, bool unique, softlist cols, *hash< auto > opt, *reference< string > sql)
	adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
AbstractPrimaryKey	addPrimaryKey (string pkname, softlist cols, *hash< auto > opt, *reference< string > sql)
	adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
AbstractTrigger	addTrigger (string tname, string src, *hash< auto > opt, *reference lsql)
	adds a trigger to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
AbstractUniqueConstraint	addUniqueConstraint (string cname, softlist cols, *hash< auto > opt, *reference< string > sql)
	adds a unique constraint to the table; if the table is known to be in the database already, then the constraint is added to the database also immediately; otherwise it is only added internally and can be created when create() is called for example More...
	beginTransaction ()
	begins a transaction on the underlying Qore::SQL::AbstractDatasource
bool	bindEmptyStringsAsNull ()
	returns True if the DB treats empty strings as NULL, False if not; by default this method returns False More...
	cache (*hash< auto > opts)
	reads in all attributes of the table from the database More...
bool	checkExistence ()
	returns True if the table exists in the database, False if not More...
	clear ()
	purges the current table definition More...
	commit ()
	commits the current transaction on the underlying Qore::SQL::AbstractDatasource
	copy (AbstractTable old)
	copies the object
	create (*hash< auto > opt)
	creates the table with all associated properties (indexes, constraints, etc) without any transaction management More...
	createCommit (*hash< auto > opt)
	creates the table in the database; releases the transaction lock after creating the table More...
deprecated	createNoCommit (*hash< auto > opt)
	A legacy wrapper for create()
int	del ()
	SqlUtil::AbstractTable::del() variant
int	del (hash< auto > cond)
	SqlUtil::AbstractTable::del() variant
int	del (hash< auto > cond, hash< auto > opt)
	SqlUtil::AbstractTable::del() variant
int	del (hash< auto > cond, reference< string > sql)
	SqlUtil::AbstractTable::del() variant
int	del (hash< auto > cond, reference< string > sql, hash< auto > opt)
	deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method More...
int	delCommit ()
	SqlUtil::AbstractTable::delCommit() variant
int	delCommit (hash< auto > cond)
	SqlUtil::AbstractTable::delCommit() variant
int	delCommit (hash< auto > cond, hash< auto > opt)
	SqlUtil::AbstractTable::delCommit() variant
int	delCommit (hash< auto > cond, reference< string > sql)
	SqlUtil::AbstractTable::delCommit() variant
int	delCommit (hash< auto > cond, reference< string > sql, hash< auto > opt)
	deletes rows in the table matching the condition and returns the count of rows deleted; the transaction is committed if successful, if an error occurs then it is rolled back More...
deprecated int	delNoCommit (*hash< auto > cond, *reference< string > sql)
	A legacy SqlUtil::AbstractTable::del() wrapper.
hash< SqlResultInfo >	delWithInfo (hash< auto > cond, *hash< auto > opt)
	deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method More...
Columns	describe ()
	returns an object of class Columns describing the table More...
	drop (*hash< auto > opt)
	drops the table from the database without any transaction management More...
AbstractColumn	dropColumn (string cname, *reference lsql)
	drops a column from the table More...
	dropCommit (*hash< auto > opt)
	drops the table from the database; releases the transaction lock after dropping the table More...
AbstractConstraint	dropConstraint (string cname, *reference< string > sql)
	drops a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally More...
AbstractForeignConstraint	dropForeignConstraint (string cname, *reference< string > sql)
	drops a foreign constraint from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally More...
AbstractIndex	dropIndex (string iname, *reference< string > sql)
	drops the given index from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally More...
deprecated	dropNoCommit (*hash< auto > opt)
	A legacy wrapper for drop()
AbstractPrimaryKey	dropPrimaryKey (*reference lsql)
	drops the primary key from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally More...
AbstractTrigger	dropTrigger (string tname, *reference< string > sql)
	drops the given trigger from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally More...
bool	empty ()
	returns True if the table has no definitions, False if not More...
bool	emptyData ()
	returns True if the table has no data rows, False if not More...
*hash	find (auto id)
	finds a row in the table with the given primary key value; if no row matches the primary key value passed then NOTHING is returned More...
*hash< auto >	find (hash< auto > row)
	finds a row in the table with the given primary key value given as a hash; if no row matches the primary key value passed then NOTHING is returned More...
*list	find (list< auto > ids)
	finds rows in the table with the given primary key values; if no row matches any primary key value passed then NOTHING is returned More...
*list< auto >	findAll (*hash< auto > cond)
	finds all rows in the table with the given column values; a list of hashes is returned representing the rows returned More...
*hash< auto >	findSingle (*hash< auto > cond)
	finds a single row in the table that match the row condition passed; multiple rows may match, but only one row will be returned from the database; if no row matches the condition hash passed then NOTHING is returned More...
*AbstractUniqueConstraint	findUniqueConstraint (string name)
	returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key) More...
string	getAddCheckConstraintSql (string cname, string src, *hash copt, *hash< auto > opt)
	returns an SQL string that can be used to add a check constraint to the table More...
list< auto >	getAddColumnSql (string cname, hash< auto > copt, bool nullable=True, *hash< auto > opt)
	returns a list of SQL strings that can be use to add a column to the table More...
string	getAddForeignConstraintSql (string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash< auto > opt)
	returns an SQL string that can be used to add a foreign constraint to the table More...
string	getAddIndexSql (string iname, bool unique, softlist cols, *hash< auto > ixopt, *hash< auto > opt)
	returns an SQL string that can be used to add an index to the table More...
string	getAddPrimaryKeySql (string pkname, softlist cols, *hash pkopt, *hash< auto > opt)
	returns the SQL that can be used to add a primary key to the table More...
list< auto >	getAddTriggerSql (string tname, string src, *hash topt, *hash< auto > opt)
	returns a list of SQL strings that can be used to add a trigger to the table More...
string	getAddUniqueConstraintSql (string cname, softlist cols, *hash ukopt, *hash< auto > opt)
	returns an SQL string that can be used to add a unique constraint to the table More...
list< auto >	getAlignSql (AbstractTable t, *hash< auto > opt)
	returns a list of SQL strings required to align the table to the table given as an argument More...
string	getAlignSqlString (AbstractTable t, *hash< auto > opt)
	accepts an AbstractTable argument and returns an SQL string that could be executed to align the structure and configuration of the current table with that of the argument; if the tables are identical then an empty string is returned More...
string	getBaseType ()
	returns the base type of the underlying object (normally "table", some DB-specific implementations may support others like "view")
code	getBulkUpsertClosure (hash< auto > example_row, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	returns a closure that can be executed given a hash argument representing either a single row or a set of rows (where each key value is a list of column values) that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned does not check the input hash for validity More...
AbstractDataField	getColumnDataField (AbstractColumn column, *hash< SqlUtilDataTypeOptionInfo > options, *string append_desc)
	returns a field object for the given column More...
AbstractDataField	getColumnDataField (string column_name, *hash< auto > options, *string append_desc)
	returns a field object for the given column More...
AbstractDataProviderType	getColumnDataType (string column_name, *hash< SqlUtilDataTypeOptionInfo > options)
	returns the data type for the given column More...
string	getColumnSqlName (string col)
	returns the column name for use in SQL strings; subclasses can return a special string in case the column name is a reserved word
list< auto >	getColumnSqlNames (softlist cols)
	returns a list of column names for use in SQL strings; subclasses can process the argument list in case a column name is a reserved word
Constraints	getConstraints ()
	returns a Constraints object describing the non-foreign constraints on the table
*list< auto >	getCreateConstraintsSql (*hash< auto > opt, bool cache=True)
	returns a list of SQL strings that could be used to create non-foreign constraints on the table or NOTHING if there are no non-foreign constraints on the table More...
*list< auto >	getCreateForeignConstraintsSql (*hash< auto > opt, bool cache=True)
	returns a list of SQL strings that could be used to create foreign constraints on the table or NOTHING if there are no foreign constraints on the table More...
*list< auto >	getCreateIndexesSql (*hash< auto > opt, bool cache=True)
	returns a list of SQL strings that could be used to create indexes on the table or NOTHING if there are no indexes on the table More...
*list< auto >	getCreateMiscSql (*hash< auto > opt, bool cache=True)
	returns a list of SQL strings that could be used to create other table attributes (such as comments, if supported and present) or NOTHING if there are none More...
*string	getCreatePrimaryKeySql (*hash< auto > opt, bool cache=True)
	returns an SQL string that could be used to create the primary key on the table More...
list< auto >	getCreateSql (*hash< auto > opt)
	returns a list of SQL strings that could be used to create the table and all known properties of the table More...
string	getCreateSqlString (*hash< auto > opt)
	returns an SQL string that could be used to create the table and all known properties of the table More...
string	getCreateTableSql (*hash< auto > opt)
	returns an SQL string that could be used to create the basic table structure without indexes and constraints More...
*list< auto >	getCreateTriggersSql (*hash< auto > opt, bool cache=True)
	returns a list of SQL strings that could be used to create triggers on the table or NOTHING if there are no triggers on the table More...
AbstractDataProviderType	getDbType (AbstractColumn column, *hash< SqlUtilDataTypeOptionInfo > options)
	returns the DB type for the given column type More...
AbstractDataProviderType	getDbType (string native_type, *string qore_type, bool nullable, int max_size=-1, *hash< SqlUtilDataTypeOptionInfo > options)
	returns the DB type for the given column type More...
string	getDesc ()
	returns a descriptive string of the datasource (without the password) and the table name (with a possible qualifier for schema, etc) More...
hash< auto >	getDescriptionHash ()
	Returns a description hash of the table. More...
list< auto >	getDropAllConstraintsAndIndexesOnColumnSql (string cname, *hash< auto > opt)
	gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned More...
list< auto >	getDropColumnSql (string cname, *hash< auto > opt)
	returns the SQL that can be used to drop a column from the table More...
*string	getDropConstraintIfExistsSql (string cname, *hash< auto > opt, *reference< AbstractConstraint > cref)
	gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOTHING; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint More...
string	getDropConstraintSql (string cname, *hash< auto > opt)
	gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint More...
string	getDropIndexSql (string iname, *hash< auto > opt)
	gets the SQL that can be used to drop an index from the table More...
list< auto >	getDropPrimaryKeySql (*hash< auto > opt)
	gets a list of SQL strings that can be used to drop the primary key from the table More...
softlist< auto >	getDropSql (*hash< auto > opt)
	returns the sql required to drop the table; reimplement in subclasses if necessary More...
list< auto >	getDropTriggerSql (string tname, *hash< auto > opt)
	returns SQL that can be used to drop the given trigger from the table More...
hash< auto >	getExpressionMap ()
	Returns the expression map for this database server. More...
ForeignConstraints	getForeignConstraints (*hash< auto > opt)
	returns a ForeignConstraints object describing the foreign constraints that the table has on other tables
Indexes	getIndexes ()
	returns an object of class Indexes describing the indexes on the table More...
list< auto >	getModifyColumnSql (string cname, hash< auto > copt, bool nullable=True, *hash< auto > opt)
	gets a list of SQL strings that can be used to modify an existing column in the table More...
string	getName ()
	returns the name of the table
AbstractDataProviderType	getNumericType (string type_name, bool nullable, *hash< auto > options)
	returns the type for number / numeric columns for the database so that data conversions can be handled properly More...
AbstractPrimaryKey	getPrimaryKey ()
	returns an object of class AbstractPrimaryKey describing the primary key of the table More...
*hash< string, AbstractDataField >	getRecordType ()
	returns a record description for the table More...
string	getRenameColumnSql (string old_name, string new_name, *hash< auto > opt)
	gets an SQL string that can be used to rename an existing column in the table More...
string	getRenameSql (string new_name, *hash< auto > opt)
	returns an SQL string that could be used to rename the table in the database More...
Qore::SQL::SQLStatement	getRowIterator (*hash< auto > sh, *hash< auto > opt)
	returns an SQLStatement object that will iterate the results of a select statement matching the arguments More...
Qore::SQL::SQLStatement	getRowIterator (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns an SQLStatement object that will iterate the results of a select statement matching the arguments More...
Qore::SQL::SQLStatement	getRowIteratorNoExec (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns an SQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared More...
AbstractSavepointHelper	getSavepointHelper (*string savepoint)
	get DB-specific savepoint helper More...
string	getSelectSql (*hash< auto > sh, *reference< list< auto > > args)
	returns the SQL string to be executed corresponding to the argument hash with an output parameter for the select bind arguments More...
string	getSqlFromList (list< auto > l)
	returns an SQL string corresponding to the list of commands in the argument More...
string	getSqlName ()
	returns the name of the table to be used in SQL (with a possible qualifier for schema, etc)
string	getSqlValue (auto v)
	returns a string for use in SQL queries representing the DB-specific value of the argument More...
Qore::SQL::AbstractSQLStatement	getStatement (*hash< auto > sh, *hash< auto > opt)
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments More...
Qore::SQL::AbstractSQLStatement	getStatement (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments More...
Qore::SQL::AbstractSQLStatement	getStatementNoExec (*hash< auto > sh, *hash< auto > opt)
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared More...
Qore::SQL::AbstractSQLStatement	getStatementNoExec (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared More...
hash< SqlResultInfo >	getStatementNoExecWithInfo (*hash< auto > select_hash, *hash< auto > opt)
	returns a result hash including an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared More...
hash< SqlResultInfo >	getStatementWithInfo (*hash< auto > select_hash, *hash< auto > opt)
	returns a result hash including an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments More...
AbstractTable	getSubtableFromString (string table, *hash< auto > opt)
	Returns the given table from the argument, using any "tablecode" option if present.
Triggers	getTriggers ()
	returns an object of class Triggers describing the triggers on the table More...
string	getTruncateSql (*hash< auto > opt)
	gets the SQL that can be used to truncate the table More...
Qore::AbstractIterator	getUniqueConstraintIterator ()
	returns an iterator for all unique constraints on the table (including the primary key if any) More...
hash< SqlCommandInfo >	getUpdateSql (hash< auto > set, *hash< auto > cond)
	Returns the SQL for the given update parameters. More...
code	getUpsertClosure (hash< auto > row, int upsert_strategy=UpsertAuto, *hash< auto > opt)
	returns a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned does not check the input hash for validity More...
code	getUpsertClosureWithValidation (hash< auto > example_row, int upsert_strategy=UpsertAuto, *hash< auto > opt)
	returns a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned checks the input hash for validity More...
hash< auto >	getWhereOperatorMap ()
	returns the "where" operator map for this object More...
abstract bool	hasArrayBind ()
	returns True if the underlying DB driver supports bulk DML operations
bool	hasReturning ()
	returns True if the current database driver supports the "returning" clause in insert statements, False if not More...
bool	inDb ()
	returns True if the table has been read from or created in the database, False if not More...
*hash< auto >	insert (hash< auto > row)
	inserts a row into the table without any transaction management; a transaction will be in progress after this method is successfully executed More...
*hash< auto >	insert (hash< auto > row, hash< auto > opt)
	SqlUtil::AbstractTable::insert() variant More...
*hash< auto >	insert (hash< auto > row, reference< string > sql)
	SqlUtil::AbstractTable::insert() variant More...
*hash< auto >	insert (hash< auto > row, reference< string > sql, hash< auto > opt)
	SqlUtil::AbstractTable::insert() variant More...
*hash< auto >	insertCommit (hash< auto > row)
	inserts a row into the table; the transaction is committed if successful, if an error occurs, it is rolled back More...
*hash< auto >	insertCommit (hash< auto > row, hash< auto > opt)
	SqlUtil::AbstractTable::insertCommit() variant More...
*hash< auto >	insertCommit (hash< auto > row, reference< string > sql)
	SqlUtil::AbstractTable::insertCommit() variant More...
*hash< auto >	insertCommit (hash< auto > row, reference< string > sql, hash< auto > opt)
	SqlUtil::AbstractTable::insertCommit() variant More...
int	insertFromIterator (Qore::AbstractIterator i, *hash< auto > opt)
	this method inserts data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; no transaction management is performed with this method More...
int	insertFromIteratorCommit (Qore::AbstractIterator i, *hash< auto > opt)
	this method inserts data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; the transaction is committed if successful, if an error occurs then it is rolled back More...
deprecated int	insertFromIteratorNoCommit (Qore::AbstractIterator i, *hash< auto > opt)
	A legacy SqlUtil::AbstractTable::insertFromIterator() wrapper.
int	insertFromSelect (list cols, AbstractTable source)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelect (list cols, AbstractTable source, hash< auto > sh)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelect (list cols, AbstractTable source, hash< auto > sh, hash< auto > opt)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelect (list cols, AbstractTable source, hash< auto > sh, reference< string > sql)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelect (list cols, AbstractTable source, hash< auto > sh, reference< string > sql, hash< auto > opt)
	inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); a transaction will be in progress after this method is successfully executed More...
int	insertFromSelectCommit (list cols, AbstractTable source)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelectCommit (list cols, AbstractTable source, hash< auto > sh)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelectCommit (list cols, AbstractTable source, hash< auto > sh, hash< auto > opt)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelectCommit (list cols, AbstractTable source, hash< auto > sh, reference< string > sql)
	SqlUtil::AbstractTable::insertFromSelectCommit() variant
int	insertFromSelectCommit (list cols, AbstractTable source, hash< auto > sh, reference< string > sql, hash< auto > opt)
	inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); the transaction is committed if successful, if an error occurs, it is rolled back More...
deprecated int	insertFromSelectNoCommit (list cols, AbstractTable source, *hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	A legacy SqlUtil::AbstractTable::insertFromSelect() wrapper.
hash< SqlResultInfo >	insertFromSelectWithInfo (list< auto > cols, AbstractTable source, hash< auto > select_hash, *hash< auto > opt)
	inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table) More...
deprecated *hash< auto >	insertNoCommit (hash< auto > row, *reference< string > sql, *hash< auto > opt)
	A legacy wrapper for SqlUtil::AbstractTable::insert()
deprecated *hash< auto >	insertNoCommit (hash< auto > row, hash< auto > opt)
	A legacy wrapper for SqlUtil::AbstractTable::insert()
hash< SqlResultInfo >	insertWithInfo (hash< auto > row, *hash< auto > opt)
	Inserts a row and returns the result and also the SQL used. More...
bool	isDuplicateRowError (hash< ExceptionInfo > ex)
	Returns True if the exception was raised because of a duplicate row / key error. More...
AbstractColumn	modifyColumn (string cname, hash< auto > opt, bool nullable=True, *reference lsql)
	modifies an existing column in the table; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally and the new column definition will be created when create() is called for example More...
AbstractForeignConstraint	removeForeignConstraint (string cname)
	removes the named foreign constraint from the table; no SQL is executed in any case, only the named foreign constraint is removed from the table definition More...
	rename (string new_name, *reference< string > sql, *Tables table_cache)
	renames the table More...
AbstractColumn	renameColumn (string old_name, string new_name, reference< string > sql)
	renames an existing column; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally More...
AbstractConstraint	renameConstraint (string old_name, string new_name, reference lsql)
	renames an existing constraint; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally More...
AbstractIndex	renameIndex (string old_name, string new_name, reference< string > sql)
	renames an existing index; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally More...
	rollback ()
	rolls back the current transaction on the underlying Qore::SQL::AbstractDatasource
softint	rowCount ()
	returns the number of rows in the table More...
*hash< auto >	select (*hash< auto > sh, *hash< auto > opt)
	returns a hash of lists representing the columns and rows in the table that match the argument hash More...
*hash< auto >	select (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns a hash of lists representing the columns and rows in the table that match the argument hash More...
*hash< auto >	selectRow (*hash< auto > sh, *hash< auto > opt)
	returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised More...
*hash< auto >	selectRow (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns a hash representing the row in the table that matches the argument hash More...
*list< auto >	selectRows (*hash< auto > sh, *hash< auto > opt)
	returns a list of hashes representing the rows in the table that match the argument hash More...
*list< auto >	selectRows (*hash< auto > sh, *reference< string > sql, *hash< auto > opt)
	returns a list of hashes representing the rows in the table that match the argument hash More...
hash< SqlResultInfo >	selectRowsWithInfo (*hash< auto > select_hash, *hash< auto > opt)
	returns a hash with a result key assigned to a list of hashes representing the rows in the table that match the argument hash More...
hash< SqlResultInfo >	selectRowWithInfo (*hash< auto > select_hash, *hash< auto > opt)
	returns a hash with a result representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised More...
hash< SqlResultInfo >	selectWithInfo (*hash< auto > select_hash, *hash< auto > opt)
	returns a hash with a result key assigned to a hash of lists representing the columns and rows in the table that match the argument hash More...
	setDatasource (AbstractDatasource nds)
	changes the datasource for the table; if the inDb flag is True, then it is set to False by calling this method More...
	setupTable (hash< auto > desc, *hash< auto > opt)
	creates the object from a table description hash More...
	truncate ()
	truncates all the table data without any transaction management More...
	truncateCommit ()
	truncates all the table data; releases the transaction lock after executing More...
deprecated	truncateNoCommit ()
	A legacy warpper for truncate()
auto	tryExec (string sql)
	executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost More...
auto	tryExecArgs (string sql, *softlist< auto > args)
	executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost More...
auto	tryExecRaw (string sql)
	executes some SQL so that if an error occurs the current transaction state is not lost More...
int	update (hash< auto > set)
	A SqlUtil::AbstractTable::update() variant.
int	update (hash< auto > set, hash< auto > cond)
	A SqlUtil::AbstractTable::update() variant.
int	update (hash< auto > set, hash< auto > cond, hash< auto > opt)
	A SqlUtil::AbstractTable::update() variant.
int	update (hash< auto > set, hash< auto > cond, reference< string > sql)
	A SqlUtil::AbstractTable::update() variant.
int	update (hash< auto > set, hash< auto > cond, reference< string > sql, hash< auto > opt)
	updates rows in the table matching an optional condition and returns the count of rows updated; no transaction management is performed with this method More...
int	updateCommit (hash< auto > set)
	A SqlUtil::AbstractTable::updateCommit() variant.
int	updateCommit (hash< auto > set, hash< auto > cond)
	A SqlUtil::AbstractTable::updateCommit() variant.
int	updateCommit (hash< auto > set, hash< auto > cond, hash< auto > opt)
	A SqlUtil::AbstractTable::updateCommit() variant.
int	updateCommit (hash< auto > set, hash< auto > cond, reference< string > sql)
	A SqlUtil::AbstractTable::updateCommit() variant.
int	updateCommit (hash< auto > set, hash< auto > cond, reference< string > sql, hash< auto > opt)
	updates rows in the table matching an optional condition and returns the count of rows updated; the transaction is committed if successful, if an error occurs then it is rolled back More...
deprecated int	updateNoCommit (hash< auto > set, *hash< auto > cond, *hash< auto > opt)
	A legacy SqlUtil::AbstractTable::update() wrapper.
deprecated int	updateNoCommit (hash< auto > set, *hash< auto > cond, *reference< string > sql)
	A legacy SqlUtil::AbstractTable::update() wrapper.
hash< SqlResultInfo >	updateWithInfo (hash< auto > set, hash< auto > cond, *hash< auto > opt)
	updates rows in the table matching an optional condition and returns an info hash with the count of rows updated; no transaction management is performed with this method More...
int	upsert (hash< auto > row, int upsert_strategy=UpsertAuto, *hash< auto > opt)
	update or insert the data in the table according to the hash argument; the table must have a unique key to do this More...
int	upsertCommit (hash< auto > row, int upsert_strategy=UpsertAuto, *hash< auto > opt)
	update or insert the data in the table according to the hash argument; the table must have a unique key to do this; the transaction is committed if successful, if an error occurs then it is rolled back More...
*hash< string, int >	upsertFromIterator (Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	this method upserts or merges data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; no transaction management is performed with this method More...
*hash< string, int >	upsertFromIteratorCommit (Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	this method upserts or merges data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; the transaction is committed if successful, if an error occurs then it is rolled back More...
deprecated *hash< string, int >	upsertFromIteratorNoCommit (Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	A legacy SqlUtik::AbstractTable::upsertFromIterator() wrapper.
*hash< string, int >	upsertFromSelect (AbstractTable t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method More...
deprecated *hash< string, int >	upsertFromSelect (Table t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	SqlUtil::AbstractTable::upsertFromSelect() variant
*hash< string, int >	upsertFromSelectCommit (AbstractTable t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	Upserts or merges data from the given foreign table and select option hash into the current table. More...
*hash< string, int >	upsertFromSelectCommit (Table t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	SqlUtil::AbstractTable::upsertFromSelectCommit() variant
deprecated *hash< string, int >	upsertFromSelectNoCommit (AbstractTable t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	A legacy SqlUtil::AbstractTable::upsertFromSelect() wrapper.
deprecated *hash< string, int >	upsertFromSelectNoCommit (Table t, *hash< auto > sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt)
	A legacy SqlUtil::AbstractTable::upsertFromSelect() wrapper.
deprecated int	upsertNoCommit (hash< auto > row, int upsert_strategy=UpsertAuto)
	A legacy SqlUtil::AbstractTable::upsert() wrapper.
Public Member Methods inherited from SqlUtil::AbstractSqlUtilBase
Qore::SQL::AbstractDatasource	getDatasource ()
	gets the underlying AbstractDatasource
string	getDatasourceDesc ()
	returns a descriptive string for the datasource
string	getDriverName ()
	returns the database driver name

Public Attributes
const	AdditionalColumnDescOptions
	additional column description keys valid when describing columns in a table description hash More...
const	AlignTableOptions
	table alignment options More...
const	CacheOptions
	default cache options
const	ColumnDescOptions
	Column description options. More...
const	ColumnOptions = {}
	Column options; this is currently empty and can be extended in database-specific modules.
const	ConstraintOptions = IndexOptions
	default constraint options More...
const	ForeignConstraintOptions
	default foreign constraint options More...
const	IndexOptions
	default index options More...
const	InsertFromIteratorOptions
	default insert option keys More...
const	InsertOptions
	generic SQL insert options More...
const	SelectOptions
	default possible select options; can be extended by driver-specific modules More...
const	SqlDataCallbackOptions
	generic SQL data operation callbacks More...
const	TableCreationOptions
	table creation options More...
const	TableDescriptionHashOptions
	Table description options. More...
const	TableOmissionOptions
	alignment omission options
const	TableOptions
	table options More...
const	TriggerOptions = AbstractDatabase::CreationOptions
	default trigger options More...
const	UpsertAuto = 4
	Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst. More...
const	UpsertInsertFirst = 1
const	UpsertInsertOnly = 5
	Upsert option: insert if the row does not exist, otherwise ignore. More...
const	UpsertOptions
	default upsert option keys More...
const	UpsertResultDescriptionMap
	hash mapping upsert descriptions to codes More...
const	UpsertResultLetterMap
	maps upsert result codes to single letter symbols
const	UpsertResultMap
	hash mapping upsert results to a description More...
const	UpsertSelectFirst = 3
	Upsert option: select first, if the row is unchanged, do nothing, if it doesn't exist, insert, otherwise update. More...
const	UpsertStrategyDescriptionMap
	hash mapping upsert strategy descriptions to upsert strategy codes More...
const	UpsertStrategyMap
	hash mapping upsert strategy codes to a text description More...
const	UpsertUpdateFirst = 2
	Upsert option: update first, if the update fails, then insert. More...
const	UpsertUpdateOnly = 6
	Upsert option: update if the row exists, otherwise ignore. More...
const	UR_Deleted = 5
	row was deleted (only possible with batch upsert methods such as AbstractTable::upsertFromIterator() where upsert option delete_others is True)
const	UR_Inserted = 1
const	UR_Unchanged = 4
	row was unchanged (only possible with UpsertSelectFirst, UpsertInsertOnly, and UpsertUpdateOnly)
const	UR_Updated = 3
	row was updated because it was different (only possible with UpsertSelectFirst)
const	UR_Verified = 2
	row was updated unconditionally (not returned with UpsertSelectFirst)

Private Member Methods
bool	asteriskRequiresPrefix ()
	returns True if the database requires a wildcard "*" to be prefixed with the table name when it appears with other column arguments in a select statement
abstract bool	checkExistenceImpl ()
	returns True if the table exists in the DB, False if not More...
	clearImpl ()
	clears any driver-specific table information
abstract bool	constraintsLinkedToIndexesImpl ()
	returns True if the database links constraints to indexes (ie dropping the constraint drops the index, etc)
	constructor (AbstractDatasource nds, string nname, *hash nopts)
	creates the object; private constructor More...
abstract	copyImpl (AbstractTable old)
	db-specific copy actions
abstract	doSelectLimitOnlyUnlockedImpl (reference< hash< QueryInfo > > info, reference< string > sql)
	processes a string for use in SQL select statements when there is a "limit" argument, but no "orderby" or "offset" arguments
abstract	doSelectOrderByWithOffsetSqlUnlockedImpl (reference< hash< QueryInfo > > info, reference< string > sql, list< auto > coll)
	processes a string for use in SQL select statements when there is an "order by" and "offset" argument
hash< auto >	getAlignTableOptions ()
	returns the align table options for this driver More...
hash< string, Columns >	getAllUpsertColumns (*hash< auto > row)
	returns a hash with a single value\ More...
hash< auto >	getCacheOptions ()
	returns the cache options for this driver More...
hash< auto >	getColumnDescOptions ()
	returns the column description options for this driver More...
hash< auto >	getColumnOperatorMap ()
	returns the column operator map for this object More...
*hash< auto >	getColumnOperatorMapImpl ()
	Reimplement in subclasses to provide driver specific column operators.
hash< auto >	getColumnOptions ()
	returns the column options for this driver More...
hash< auto >	getConstraintOptions ()
	returns the constraint options for this driver More...
hash< auto >	getForeignConstraintOptions ()
	return the foreign constraint options for this driver More...
hash< auto >	getIndexOptions ()
	returns the index options for this driver More...
hash< auto >	getInsertFromIteratorOptions ()
	returns the insert from iterator options for this driver More...
hash< auto >	getInsertOperatorMap ()
	returns the insert operator map for this object More...
hash< auto >	getInsertOptions ()
	returns the insert options for this driver More...
abstract AbstractDataProviderType	getNumericTypeImpl (string type_name, bool nullable, *hash< auto > options)
	returns the type for number / numeric columns for the database so that data conversions can be handled properly More...
*hash< auto >	getPseudoColumnHash ()
	returns a hash of valid pseudocolumns More...
abstract hash< auto >	getQoreTypeMapImpl ()
	returns the qore type -> column type map
hash< auto >	getRawUpdateOperatorMap ()
	returns the raw (default) update operator map for this object More...
abstract AbstractSavepointHelper	getSavepointHelperImpl (*string savepoint)
	get DB-specific savepoint helper More...
hash< auto >	getSelectOptions ()
	returns the select options for this driver More...
hash< auto >	getSqlDataCallbackOptions ()
	returns the sql data operation callback options for this driver More...
abstract *string	getSqlValueImpl (auto v)
	returns a string for use in SQL queries representing the DB-specific value of the argument; returns NOTHING if the type cannot be converted to an SQL string
hash< auto >	getTableColumnDescOptions ()
	returns the table column description options for this driver More...
hash< auto >	getTableCreationOptions ()
	returns the table creation options for this driver More...
hash< auto >	getTableDescriptionHashOptions ()
	returns the table description hash<auto> options for this driver More...
hash< auto >	getTableOptions ()
	returns the table options for this driver More...
hash< auto >	getTriggerOptions ()
	returns the trigger options for this driver More...
abstract hash< auto >	getTypeMapImpl ()
	returns the type name -> type description hash
hash< auto >	getUpdateOperatorMap ()
	returns the update operator map for this object More...
hash< auto >	getUpsertOptions ()
	returns the upsert options for this driver More...
bool	hasReturningImpl ()
	returns True if the current database driver supports the "returning" clause in insert statements, False if not
abstract bool	isDuplicateRowErrorImpl (hash< ExceptionInfo > ex)
	Returns True if the exception was raised because of a duplicate row / key error. More...
abstract bool	supportsTablespacesImpl ()
	returns True if the database support tablespaces
auto	tryExecArgsImpl (string sql, *softlist< auto > args)
	tries to execute a command so that if an error occurs the current transaction status is not lost
auto	tryExecRawImpl (string sql)
	tries to execute a command so that if an error occurs the current transaction status is not lost
abstract bool	tryInsertImpl (string sql, hash< auto > row)
	tries to insert a row, if there is a duplicate key, then it returns False, if successful, returns True
abstract bool	uniqueIndexCreatesConstraintImpl ()
	returns True if the database automatically creates a unique constraint when a unique index is created (ex: mysql)
	validateColumnOptions (string cname, reference< hash > opt, bool nullable)
	validates column options
Private Member Methods inherited from SqlUtil::AbstractSqlUtilBase
	constructor (AbstractDatasource nds, *hash nopts)
	creates the object; private constructor More...
transient Mutex	l ()
	mutex for atomic actions
	validateHashKeysForWhitespaces (auto node)
	Check input node for all hash keys - if it contains a key with whitespace in the beginning or at the end -> error.

Private Attributes
Columns	columns
	column description object
Constraints	constraints
	constraint descriptions
ForeignConstraints	foreignConstraints
	foreign constraints description
bool	inDb = False
	in database
Indexes	indexes
	index descriptions
bool	manual = False
	manual edits
string	name
	the table's name
bool	native_case = False
	native case option
AbstractPrimaryKey	primaryKey
	primary key description
Triggers	triggers
	trigger descriptions
Private Attributes inherited from SqlUtil::AbstractSqlUtilBase
transient AbstractDatasource	ds
	the connection to the database server
string	dsdesc
	datasource description
*hash< auto >	opts
	option hash

Detailed Description

the base abstract class for the table implementation

Use SqlUtil::Table if you need to construct table object directly

Member Function Documentation

addCheckConstraint()

AbstractCheckConstraint SqlUtil::AbstractTable::addCheckConstraint	(	string	cname,
		string	src,
		*hash< auto >	opt,
		*reference< string >	sql
	)

adds a check constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractCheckConstraint cc = table.addCheckConstraint("check_mytable_id", "id > 10", NOTHING, \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new constraint
src	the source of the constraint clause
opt	a hash of options for the new constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractCheckConstraint object corresponding to the check constraint created

Exceptions

CHECK-CONSTRAINT-ERROR

the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addColumn()

AbstractColumn SqlUtil::AbstractTable::addColumn	(	string	cname,
		hash< auto >	opt,
		bool	nullable = True,
		*reference	lsql
	)

adds a column to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

table.addColumn("name", ("qore_type": Type::String, "size": 50), False);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
opt	a hash<auto> describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native functions or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if True then the column can hold NULL values; note that primary key columns cannot be nullable
lsql	an optional reference to a list of strings to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Exceptions

COLUMN-ERROR

no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

make sure and add a default_value value when adding a column with a "not null" constraint with existing data

See also

inDb() for a method that tells if the table is already in the database or not

addCustomCopOperator()

SqlUtil::AbstractTable::addCustomCopOperator	(	string	name,
		hash< auto >	operator
	)

register custom user column operator for this table object

This method allows to register custom operators for select statements.

Parameters

name	a string with operator name. It has to be unique in the driver
operator	an operator has as expected by SqlUtil::make_cop() function

Exceptions

CUSTOM-OPERATOR-ERROR

in case when user tries to register already existing operator with name or if the operator hash does not contain "code" key/value.

# t is a AbstractTable/Table object
# custom operator hash
hash<auto> to_char = (
"code" : string sub(string arg1, auto arg) {
                return sprintf("to_char(%s, '%s')", arg1, arg);
            },
);
# operator registration
t.addCustomCopOperator("to_char", to_char);
# example usage
hash<auto> sh = (
"columns" : cop_as(SqlUtil::make_cop("to_char", "d", "yyyymmddhh24miss"), "string_fmt_date"),
"limit" : 1,
);
# output
string sql;
on_exit printf("SQL> %s\n", sql);
*list<auto> res = t.selectRows(sh, \sql);
# result (from Oracle)
# list: (1 element)
#  [0]=hash: (3 members)
#    string_fmt_date : "20171020071854"
# SQL> select to_char(d, 'yyyymmddhh24miss') as string_fmt_date from test_schema.t

addForeignConstraint()

AbstractForeignConstraint SqlUtil::AbstractTable::addForeignConstraint	(	string	cname,
		softlist	cols,
		string	table,
		*softlist	tcols,
		*hash< auto >	opt,
		*reference< string >	sql
	)

adds a foreign constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractForeignConstraint fc = table.addForeignConstraint("fk_mytable_other_table", ("name", "version"), "other_table", NOTHING, \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets
tcols	a single column name or a list of columns in the foreign table or NOTHING meaning that the column names are the same as in the local table; if column names are given the same number of columns must be given in the local and foreign tables
opt	a hash of options for the new foreign constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ForeignConstraintOptions
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractForeignConstraint object corresponding to the foreign constraint created

Exceptions

FOREIGN-CONSTRAINT-ERROR

the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addIndex()

AbstractIndex SqlUtil::AbstractTable::addIndex	(	string	iname,
		bool	unique,
		softlist	cols,
		*hash< auto >	opt,
		*reference< string >	sql
	)

adds an index to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractIndex ix = table.addIndex("uk_mytable_name", True, "name", ("index_tablespace": "index1"), \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index
opt	a hash of options for the new index; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::IndexOptions
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractIndex object corresponding to the index created

Exceptions

INDEX-ERROR

the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addPrimaryKey()

AbstractPrimaryKey SqlUtil::AbstractTable::addPrimaryKey	(	string	pkname,
		softlist	cols,
		*hash< auto >	opt,
		*reference< string >	sql
	)

adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractPrimaryKey pk = table.addPrimaryKey("pk_mytable", "id", ("index_tablespace": "index1"), \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
cols	a single column name or a list of columns that make up the primary key
opt	a hash of options for the new primary key; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractPrimaryKey object corresponding to the primary key created

Exceptions

PRIMARY-KEY-ERROR

the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

addTrigger()

AbstractTrigger SqlUtil::AbstractTable::addTrigger	(	string	tname,
		string	src,
		*hash< auto >	opt,
		*reference	lsql
	)

adds a trigger to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractTrigger trig = table.addTrigger("trig_mytable", "", NOTHING, \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the new trigger
src	the source of the trigger
opt	a hash of options for the new trigger; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::TriggerOptions
lsql	an optional reference to a list of strings to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractTrigger object corresponding to the trigger created

Exceptions

TRIGGER-ERROR

the table already has a trigger with the given name or invalid options were passed

See also

inDb() for a method that tells if the table is already in the database or not

addUniqueConstraint()

AbstractUniqueConstraint SqlUtil::AbstractTable::addUniqueConstraint	(	string	cname,
		softlist	cols,
		*hash< auto >	opt,
		*reference< string >	sql
	)

adds a unique constraint to the table; if the table is known to be in the database already, then the constraint is added to the database also immediately; otherwise it is only added internally and can be created when create() is called for example

Example:

string sql;
AbstractUniqueConstraint c = table.addUniqueConstraint("uk_mytable", "name", ("index_tablespace": "index1"), \sql);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint
opt	a hash of options for the new unique constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

an AbstractUniqueConstraint object corresponding to the unique constraint created

Exceptions

UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed
OPTION-ERROR	invalid or unsupported option passed

See also

inDb() for a method that tells if the table is already in the database or not

bindEmptyStringsAsNull()

bool SqlUtil::AbstractTable::bindEmptyStringsAsNull

(

)

returns True if the DB treats empty strings as NULL, False if not; by default this method returns False

Since

SqlUtil 1.3

cache()

SqlUtil::AbstractTable::cache

(

*hash< auto >

opts

)

reads in all attributes of the table from the database

Example:

table.cache();

Parameters

opts	cache options; see SqlUtil::AbstractTable::CacheOptions for common options; each driver can support additional driver-specific options

checkExistence()

bool SqlUtil::AbstractTable::checkExistence

(

)

returns True if the table exists in the database, False if not

this method will actively check the database if the table is defined there in case no configuration from the database has already been cached for the object

See also

AbstractTable::inDb()

checkExistenceImpl()

abstract bool SqlUtil::AbstractTable::checkExistenceImpl ( )

privatepure virtual

returns True if the table exists in the DB, False if not

this method will only be called if inDb is False and should set inDb = True if it returns True

clear()

SqlUtil::AbstractTable::clear

(

)

purges the current table definition

Example:

table.clear();

constructor()

SqlUtil::AbstractTable::constructor ( AbstractDatasource  nds, string  nname, *hash  nopts  )

private

creates the object; private constructor

Parameters

nds	the AbstractDatasource for the connection to the database
nname	the name of the table
nopts	an optional hash of options for the table creation string; see SqlUtil::AbstractTable::TableOptions for common options; each driver can support additional driver-specific options

Exceptions

OPTION-ERROR

invalid or unsupported option passed

create()

SqlUtil::AbstractTable::create

(

*hash< auto >

opt

)

creates the table with all associated properties (indexes, constraints, etc) without any transaction management

Example:

table.create();

Parameters

opt	a hash of options for the SQL creation strings

Note

Transaction management is normally not performed when creating tables, however this method uses the Qore::SQL::AbstractDatasource::exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

Exceptions

CREATE-TABLE-ERROR

table has already been read from or created in the database

createCommit()

SqlUtil::AbstractTable::createCommit

(

*hash< auto >

opt

)

creates the table in the database; releases the transaction lock after creating the table

Example:

table.createCommit();

Parameters

opt	a hash of options for the SQL creation strings

Note

The transaction is committed if successful or rolled back if an exception occurs; use create() to execute without any transaction management

del()

int SqlUtil::AbstractTable::del	(	hash< auto >	cond,
		reference< string >	sql,
		hash< auto >	opt
	)

deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method

Example:

int dcnt = table.del(("name": name));

Parameters

cond	a hash of conditions for the where clause; see Where Clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows deleted

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

See also

delCommit(*hash)

delCommit()

int SqlUtil::AbstractTable::delCommit	(	hash< auto >	cond,
		reference< string >	sql,
		hash< auto >	opt
	)

deletes rows in the table matching the condition and returns the count of rows deleted; the transaction is committed if successful, if an error occurs then it is rolled back

Example:

int dcnt = table.delCommit(("name": name));

Parameters

cond	a hash of conditions for the where clause; see Where Clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows deleted

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

See also

delete(*hash)

delWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::delWithInfo	(	hash< auto >	cond,
		*hash< auto >	opt
	)

deletes rows in the table matching the condition and returns the count of rows deleted; no transaction management is performed with this method

Example:

hash<SqlResultInfo> info = table.delWithInfo({"name": name});

Parameters

cond	a hash of conditions for the where clause; see Where Clauses for more information
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash where the result key is the count of rows deleted

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

Since

SqlUtil 1.7

describe()

Columns SqlUtil::AbstractTable::describe

(

)

returns an object of class Columns describing the table

Example:

Columns cols = table.describe();

Returns

an object of class Columns describing the table

drop()

SqlUtil::AbstractTable::drop

(

*hash< auto >

opt

)

drops the table from the database without any transaction management

Example:

table.drop();

Parameters

opt	optional callback options; see AbstractDatabase::CallbackOptions for more info

Exceptions

OPTION-ERROR

invalid or unknown callback option

Note

Transaction management is normally not performed when dropping tables, however this method uses the Qore::SQL::AbstractDatasource::exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

dropColumn()

AbstractColumn SqlUtil::AbstractTable::dropColumn	(	string	cname,
		*reference	lsql
	)

drops a column from the table

Example:

table.dropColumn("notes_2");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column to drop
lsql	an optional reference to a list of strings to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the AbstractColumn object representing the column dropped from the table

Exceptions

COLUMN-ERROR

the named column is not present in the table

Note

this method retrieves the table definition from the database if none has already been defined

See also

inDb() for a method that tells if the table is already in the database or not

dropCommit()

SqlUtil::AbstractTable::dropCommit

(

*hash< auto >

opt

)

drops the table from the database; releases the transaction lock after dropping the table

Example:

table.dropCommit();

Parameters

opt	optional callback options; see AbstractDatabase::CallbackOptions for more info

Exceptions

OPTION-ERROR

invalid or unknown callback option

Note

The transaction is committed if successful or rolled back if an exception occurs; use drop() to execute without any transaction management

dropConstraint()

AbstractConstraint SqlUtil::AbstractTable::dropConstraint	(	string	cname,
		*reference< string >	sql
	)

drops a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally

Example:

table.dropConstraint("uk_mytable_name");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the constraint to drop
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the constraint dropped from the table

Exceptions

CONSTRAINT-ERROR

the given constraint does not exist in the table

Note

this method retrieves current constraint definitions from the database if not already loaded or defined

See also

inDb() for a method that tells if the table is already in the database or not

dropForeignConstraint()

AbstractForeignConstraint SqlUtil::AbstractTable::dropForeignConstraint	(	string	cname,
		*reference< string >	sql
	)

drops a foreign constraint from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally

Example:

table.dropForeignConstraint("uk_mytable_name");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the foreign constraint to drop
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the foreign constraint dropped from the table

Exceptions

FOREIGN-CONSTRAINT-ERROR

the given constraint does not exist

Note

this method retrieves all current foreign constraint definitions from the database if none have already been defined

See also

inDb() for a method that tells if the table is already in the database or not

dropIndex()

AbstractIndex SqlUtil::AbstractTable::dropIndex	(	string	iname,
		*reference< string >	sql
	)

drops the given index from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally

Example:

table.dropIndex("uk_mytable_name");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

iname	the name of the index to drop
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the index dropped from the table

Exceptions

INDEX-ERROR

the given index does not exist

Note

this method retrieves all current index definitions from the database if none have already been defined

See also

inDb() for a method that tells if the table is already in the database or not

dropPrimaryKey()

AbstractPrimaryKey SqlUtil::AbstractTable::dropPrimaryKey

(

*reference

lsql

)

drops the primary key from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally

Example:

table.dropPrimaryKey();

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

lsql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the primary key dropped from the table

Exceptions

PRIMARY-KEY-ERROR

no primary key exists

Note

• this method retrieves the primary key definition from the database if none has already been defined
• if there are known foreign contraints on the primary key, SQL for dropping those constraints is also included

See also

inDb() for a method that tells if the table is already in the database or not

dropTrigger()

AbstractTrigger SqlUtil::AbstractTable::dropTrigger	(	string	tname,
		*reference< string >	sql
	)

drops the given trigger from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally

Example:

table.dropTrigger("trig_mytable");

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the trigger to drop
sql	an optional reference to a string to retrieve the SQL used to modify the database (only executed if the table is already in the database)

Returns

the trigger dropped from the table

Exceptions

TRIGGER-ERROR

the given trigger does not exist

Note

this method retrieves all current trigger definitions from the database if none have already been defined

See also

inDb() for a method that tells if the table is already in the database or not

empty()

bool SqlUtil::AbstractTable::empty

(

)

returns True if the table has no definitions, False if not

Example:

bool b = table.empty();

Returns

True if the table has no definitions, False if not

See also

emptyData()

emptyData()

bool SqlUtil::AbstractTable::emptyData

(

)

returns True if the table has no data rows, False if not

Example:

bool b = table.emptyData();

Returns

True if the table has no data rows, False if not

See also

• empty()
• rowCount()

find() [1/3]

*hash SqlUtil::AbstractTable::find

(

auto

id

)

finds a row in the table with the given primary key value; if no row matches the primary key value passed then NOTHING is returned

Example:

*hash<auto> row = table.find(id);

Exceptions

PRIMARY-KEY-ERROR

the table has no primary key or the primary key has more than one column

find() [2/3]

*hash< auto > SqlUtil::AbstractTable::find

(

hash< auto >

row

)

finds a row in the table with the given primary key value given as a hash; if no row matches the primary key value passed then NOTHING is returned

Example:

*hash<auto> row = table.find(("account_type": type, "name": name));

Parameters

row	a hash giving the primary key value to find; other columns may also appear in the hash, however at least all columns of the primary key must be present

Returns

a hash of the row value matching the primary key value passed or NOTHING if no row matches the primary key value passed

Exceptions

PRIMARY-KEY-ERROR

the table has no primary key or the the hash passed does not contain all columns of the primary key

Note

a table with a primary key with a single column can also be used with this method; just pass a hash with one key

find() [3/3]

*list SqlUtil::AbstractTable::find

(

list< auto >

ids

)

finds rows in the table with the given primary key values; if no row matches any primary key value passed then NOTHING is returned

Example:

*list rows = table.find(list);

Parameters

ids	the list of primary key IDs to find; if the list is empty then NOTHING is returned

Returns

a list of hashes of rows matching the primary key IDs passed or NOTHING if no row matches any primary key value passed

Exceptions

PRIMARY-KEY-ERROR

the table has no primary key or the primary key has more than one column

findAll()

*list< auto > SqlUtil::AbstractTable::findAll

(

*hash< auto >

cond

)

finds all rows in the table with the given column values; a list of hashes is returned representing the rows returned

Example:

*list<auto> rows = table.findAll(h);

Parameters

cond	a hash giving the column values to find; see Where Clauses for the format of this argument

Returns

a list of hashes is returned representing the rows returned

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

Note

this is equivalent to calling selectRows() with where = cond

findSingle()

*hash< auto > SqlUtil::AbstractTable::findSingle

(

*hash< auto >

cond

)

finds a single row in the table that match the row condition passed; multiple rows may match, but only one row will be returned from the database; if no row matches the condition hash passed then NOTHING is returned

Example:

*hash<auto> row = table.findSingle(h);

Parameters

cond	a hash giving the column values to find; see Where Clauses for the format of this argument

Returns

a hash representing a single row in the table with the given column values; multiple rows may match, but only one row will be returned from the database; if no row matches the condition hash passed then NOTHING is returned

Exceptions

WHERE-ERROR

unknown operator or invalid arguments given in the cond hash for the where clause

Note

this is equivalent to calling selectRow() with where = cond and limit = 1

findUniqueConstraint()

*AbstractUniqueConstraint SqlUtil::AbstractTable::findUniqueConstraint

(

string

name

)

returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key)

Example:

my *AbstractUniqueConstraint uk = table.findUniqueConstraint("uk_table2");

Parameters

name	the name of the unique constraint to find

Returns

the given AbstractUniqueConstraint object if defined for the table (also includes the primary key)

getAddCheckConstraintSql()

string SqlUtil::AbstractTable::getAddCheckConstraintSql	(	string	cname,
		string	src,
		*hash	copt,
		*hash< auto >	opt
	)

returns an SQL string that can be used to add a check constraint to the table

Example:

string sql = table.getAddCheckConstraintSql("check_mytable_id", "id > 10");
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the new constraint
src	the source of the constraint clause
copt	a hash of options for the new constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that can be used to add a check constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CHECK-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAddColumnSql()

list< auto > SqlUtil::AbstractTable::getAddColumnSql	(	string	cname,
		hash< auto >	copt,
		bool	nullable = True,
		*hash< auto >	opt
	)

returns a list of SQL strings that can be use to add a column to the table

Example:

list l = table.getAddColumnSql("name", ("qore_type": Type::String, "size": 50), False);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
copt	a hash<auto> describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native functions or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if True then the column can hold NULL values; note that primary key columns cannot be nullable
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be use to add a column to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column already exists, invalid column data

Note

• make sure and add a default_value value when adding a column with a "not null" constraint with existing data
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getAddForeignConstraintSql()

string SqlUtil::AbstractTable::getAddForeignConstraintSql	(	string	cname,
		softlist	cols,
		string	table,
		*softlist	tcols,
		*hash	fkopt,
		*hash< auto >	opt
	)

returns an SQL string that can be used to add a foreign constraint to the table

Example:

string sql = table.getAddForeignConstraintSql("fk_mytable_other_table", ("name", "version"), "other_table");
printf("%s;\n", sql);

Parameters

cname	the name of the new foreign constraint
cols	a single column name or a list of columns in the local table that make up the foreign constraint
table	the name of the other table that the constraint targets
tcols	a single column name or a list of columns in the foreign table or NOTHING meaning that the column names are the same as in the local table; if column names are given the same number of columns must be given in the local and foreign tables
fkopt	a hash of options for the new foreign constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ForeignConstraintOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that can be used to add a foreign constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
FOREIGN-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAddIndexSql()

string SqlUtil::AbstractTable::getAddIndexSql	(	string	iname,
		bool	unique,
		softlist	cols,
		*hash< auto >	ixopt,
		*hash< auto >	opt
	)

returns an SQL string that can be used to add an index to the table

Example:

string sql = table.getAddIndexSql("uk_mytable_name", True, "name", ("index_tablespace": "index1"));
printf("%s;\n", sql);

Parameters

iname	the name of the new index
unique	a flag to tell if the new index should be unique or not
cols	a single column name or a list of columns that make up the index
ixopt	a hash of options for the new index; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::IndexOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that can be used to add an index to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the table already has an index with the given name or invalid columns or options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAddPrimaryKeySql()

string SqlUtil::AbstractTable::getAddPrimaryKeySql	(	string	pkname,
		softlist	cols,
		*hash	pkopt,
		*hash< auto >	opt
	)

returns the SQL that can be used to add a primary key to the table

Example:

string sql = table.getAddPrimaryKeySql("pk_mytable", "id", ("index_tablespace": "index1"), opt);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

pkname	the name of the new primary key constraint
cols	a single column name or a list of columns that make up the primary key
pkopt	a hash of options for the new primary key; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to add a primary key to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table already has a primary key or invalid columns or options passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAddTriggerSql()

list< auto > SqlUtil::AbstractTable::getAddTriggerSql	(	string	tname,
		string	src,
		*hash	topt,
		*hash< auto >	opt
	)

returns a list of SQL strings that can be used to add a trigger to the table

Example:

string sql = table.getAddTriggerSql("trig_mytable", trigger_src);
printf("%s;\n", sql);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

tname	the name of the new trigger
src	the source of the trigger
topt	a hash of options for the new trigger; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::TriggerOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to add a trigger to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the table already has a trigger with the given name or invalid options were passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAddUniqueConstraintSql()

string SqlUtil::AbstractTable::getAddUniqueConstraintSql	(	string	cname,
		softlist	cols,
		*hash	ukopt,
		*hash< auto >	opt
	)

returns an SQL string that can be used to add a unique constraint to the table

Example:

string sql = table.getAddUniqueConstraintSql("uk_mytable", "name", ("index_tablespace": "index1"));
printf("%s;\n", sql);

Parameters

cname	the name of the new unique constraint
cols	a single column name or a list of columns that make up the unique constraint
ukopt	a hash of options for the new unique constraint; each driver may implement its own options; for common options, see SqlUtil::AbstractTable::ConstraintOptions
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that can be used to add a unique constraint to the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
UNIQUE-CONSTRAINT-ERROR	the table already has a constraint with the given name or invalid columns passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAlignSql()

list< auto > SqlUtil::AbstractTable::getAlignSql	(	AbstractTable	t,
		*hash< auto >	opt
	)

returns a list of SQL strings required to align the table to the table given as an argument

if the tables are identical then an empty list is returned

Example:

list<auto> l = table.getAlignSql(table2);

Parameters

t	the template table that the current table will be compared to
opt	a hash of options for the SQL creation string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings required to align the structure and configuration of the current table with that of the AbstractTable argument; if the tables are identical then an empty list is returned

Exceptions

ALIGN-TABLE-ERROR	the argument must be of the same class as the current object; template table has no columns
OPTION-ERROR	invalid or unsupported option passed

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getAlignSqlString()

string SqlUtil::AbstractTable::getAlignSqlString	(	AbstractTable	t,
		*hash< auto >	opt
	)

accepts an AbstractTable argument and returns an SQL string that could be executed to align the structure and configuration of the current table with that of the argument; if the tables are identical then an empty string is returned

Example:

string sql = table.getAlignSqlString(table2);

Parameters

t	the template table that the current table will be compared to
opt	a hash of options for the SQL creation string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that could be executed to align the structure and configuration of the current table with that of the argument; if the tables are identical then an empty string is returned

Exceptions

ALIGN-TABLE-ERROR	the argument must be of the same class as the current object; template table has no columns
OPTION-ERROR	invalid or unsupported option passed

getAlignTableOptions()

hash< auto > SqlUtil::AbstractTable::getAlignTableOptions ( )

private

returns the align table options for this driver

override in subclasses to return driver-specific options

getAllUpsertColumns()

hash< string, Columns > SqlUtil::AbstractTable::getAllUpsertColumns ( *hash< auto >  row )

private

returns a hash with a single value\

Parameters

row	if given, a row to match a unique constraint or unique index

Returns

a hash where the key is a description of the column set returned in the value;

• if row is given, then this method returns a column set matching row
• if row is not given, then this method return the first unique constraint or unique index

Exceptions

UPSERT-ERROR

either no unique constraint or index exists or none matching row exist

getBulkUpsertClosure()

code SqlUtil::AbstractTable::getBulkUpsertClosure	(	hash< auto >	example_row,
		int	upsert_strategy = AbstractTable::UpsertAuto,
		*hash< auto >	opt
	)

returns a closure that can be executed given a hash argument representing either a single row or a set of rows (where each key value is a list of column values) that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned does not check the input hash for validity

Example:

Datasource ds("pgsql:user/pass@db%localhost");
Table table(ds, "table_name");
code upsert = table.getBulkUpsertClosure(row, AbstractTable::UpsertSelectFirst);
on_success ds.commit();
on_error ds.rollback();
upsert(row_hash);

Parameters

example_row	a hash representing an example row to insert or update; every hash passed to the upsert closure returned must have the same keys in the same order
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

a closure that can be executed given a hash argument representing either a single row or a set of rows (where each key value is a list of column values) that will be updated or inserted in the database with the given upsert strategy; the closure returned does not check the input hash for validity; the closure has the following signature:

int sub upsert(hash<auto> row) {} 

The return value of the closure is always SqlUtil::AbstractTable::UR_Verified; see Upsert Result Codes for more information

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

the row values passed to the closure for upserting are not checked if they match the example row passed to the getUpsertClosure() method; passing non-conforming data will cause errors; see SqlUtil::AbstractTable::getUpsertClosureWithValidation() for a similar method that returns a validating closure; the closure returned by this method is faster than the one returned by SqlUtil::AbstractTable::getUpsertClosure() since there is no validation

See also

• upsert()
• upsertCommit()
• getUpsertClosure()
• getUpsertClosureWithValidation()

getCacheOptions()

hash< auto > SqlUtil::AbstractTable::getCacheOptions ( )

private

returns the cache options for this driver

override in subclasses to return driver-specific options

getColumnDataField() [1/2]

AbstractDataField SqlUtil::AbstractTable::getColumnDataField	(	AbstractColumn	column,
		*hash< SqlUtilDataTypeOptionInfo >	options,
		*string	append_desc
	)

returns a field object for the given column

Parameters

column_name	the column
type	options
append_desc	an optional string to append to the field's description

Returns

a field object for the given column

Since

SqlUtil 1.6

getColumnDataField() [2/2]

AbstractDataField SqlUtil::AbstractTable::getColumnDataField	(	string	column_name,
		*hash< auto >	options,
		*string	append_desc
	)

returns a field object for the given column

Parameters

column_name	the column name
append_desc	an optional string to append to the field's description

Returns

a field object for the given column

Exceptions

COLUMN-ERROR

unknown column

Since

SqlUtil 1.6

getColumnDataType()

AbstractDataProviderType SqlUtil::AbstractTable::getColumnDataType	(	string	column_name,
		*hash< SqlUtilDataTypeOptionInfo >	options
	)

returns the data type for the given column

Parameters

column_name

the column name

Returns

the data type for the given column

Exceptions

COLUMN-ERROR

unknown column

Since

SqlUtil 1.6

getColumnDescOptions()

hash< auto > SqlUtil::AbstractTable::getColumnDescOptions ( )

private

returns the column description options for this driver

override in subclasses to return driver-specific options

getColumnOperatorMap()

hash< auto > SqlUtil::AbstractTable::getColumnOperatorMap ( )

private

returns the column operator map for this object

subclasses should to implement getColumnOperatorMapImpl() to return driver-specific options

getColumnOptions()

hash< auto > SqlUtil::AbstractTable::getColumnOptions ( )

private

returns the column options for this driver

override in subclasses to return driver-specific options

getConstraintOptions()

hash< auto > SqlUtil::AbstractTable::getConstraintOptions ( )

private

returns the constraint options for this driver

override in subclasses to return driver-specific options

getCreateConstraintsSql()

*list< auto > SqlUtil::AbstractTable::getCreateConstraintsSql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns a list of SQL strings that could be used to create non-foreign constraints on the table or NOTHING if there are no non-foreign constraints on the table

Example:

*list l = table.getCreateConstraintsSql();

Parameters

opt	a hash of options for the non-foreign constraint creation string; see SqlUtil::AbstractTable::ConstraintOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

a list of SQL strings that could be used to create non-foreign constraints on the table or NOTHING if there are no non-foreign constraints on the table

See also

getCreateForeignConstraintsSql()

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreateForeignConstraintsSql()

*list< auto > SqlUtil::AbstractTable::getCreateForeignConstraintsSql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns a list of SQL strings that could be used to create foreign constraints on the table or NOTHING if there are no foreign constraints on the table

Example:

*list l = table.getCreateForeignConstraintsSql();

Parameters

opt	a hash of options for the foreign constraint creation string; see SqlUtil::AbstractTable::ForeignConstraintOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

a list of SQL strings that could be used to create foreign constraints on the table or NOTHING if there are no foreign constraints on the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreateIndexesSql()

*list< auto > SqlUtil::AbstractTable::getCreateIndexesSql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns a list of SQL strings that could be used to create indexes on the table or NOTHING if there are no indexes on the table

Example:

*list l = table.getCreateIndexesSql();

Parameters

opt	a hash of options for the index creation string; see SqlUtil::AbstractTable::IndexOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

a list of SQL strings that could be used to create indexes on the table or NOTHING if there are no indexes on the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreateMiscSql()

*list< auto > SqlUtil::AbstractTable::getCreateMiscSql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns a list of SQL strings that could be used to create other table attributes (such as comments, if supported and present) or NOTHING if there are none

Example:

*list l = table.getCreateMiscSql();

Parameters

opt	a hash of options for the SQL creation string; see TableCreationOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

a list of SQL strings that could be used to create other table attributes (such as comments, if supported and present) or NOTHING if there are none

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreatePrimaryKeySql()

*string SqlUtil::AbstractTable::getCreatePrimaryKeySql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns an SQL string that could be used to create the primary key on the table

Example:

*string sql = table.getCreatePrimaryKeySql();

Parameters

opt	a hash of options for the primary key creation string; see SqlUtil::AbstractTable::ConstraintOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

an SQL string that could be used to create the primary key on the table or NOTHING if there is no primary key on the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreateSql()

list< auto > SqlUtil::AbstractTable::getCreateSql

(

*hash< auto >

opt

)

returns a list of SQL strings that could be used to create the table and all known properties of the table

Example:

map printf("$1;\n", $1), table.getCreateSql();

Parameters

opt	a hash of options for the table, index, and constraint creation strings; see TableCreationOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that could be used to create the table and all known properties of the table

getCreateSqlString()

string SqlUtil::AbstractTable::getCreateSqlString

(

*hash< auto >

opt

)

returns an SQL string that could be used to create the table and all known properties of the table

Example:

printf("%s\n", table.getCreateSql());

Parameters

opt	a hash of options for the SQL creation string

Returns

an SQL string that could be used to create the table and all known properties of the table

getCreateTableSql()

string SqlUtil::AbstractTable::getCreateTableSql

(

*hash< auto >

opt

)

returns an SQL string that could be used to create the basic table structure without indexes and constraints

Example:

string sql = table.getCreateTableSql();

Parameters

opt	a hash of options for the table creation string; see TableCreationOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that could be used to create the basic table structure without indexes and constraints

Exceptions

OPTION-ERROR

invalid or unsupported option passed

getCreateTriggersSql()

*list< auto > SqlUtil::AbstractTable::getCreateTriggersSql	(	*hash< auto >	opt,
		bool	cache = True
	)

returns a list of SQL strings that could be used to create triggers on the table or NOTHING if there are no triggers on the table

Example:

*list l = table.getCreateTriggersSql();

Parameters

opt	a hash of options for the trigger creation strings; see TableCreationOptions for common options; each driver can support additional driver-specific options
cache	read in data from the database for uncached properties of the table

Returns

a list of SQL strings that could be used to create triggers on the table or NOTHING if there are no triggers on the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

See also

getCreateTriggersSql()

getDbType() [1/2]

AbstractDataProviderType SqlUtil::AbstractTable::getDbType	(	AbstractColumn	column,
		*hash< SqlUtilDataTypeOptionInfo >	options
	)

returns the DB type for the given column type

Since

SqlUtil 1.6

getDbType() [2/2]

AbstractDataProviderType SqlUtil::AbstractTable::getDbType	(	string	native_type,
		*string	qore_type,
		bool	nullable,
		int	max_size = -1,
		*hash< SqlUtilDataTypeOptionInfo >	options
	)

returns the DB type for the given column type

Since

SqlUtil 1.6

getDesc()

string SqlUtil::AbstractTable::getDesc

(

)

returns a descriptive string of the datasource (without the password) and the table name (with a possible qualifier for schema, etc)

Used in exception descriptions

Since

SqlUtil 1.3

getDescriptionHash()

hash< auto > SqlUtil::AbstractTable::getDescriptionHash

(

)

Returns a description hash of the table.

Returns

a description hash of the table

currently this method does not return information for all supported elements; it returns only:

• columns
• primary_key
• indexes
• foreign_constraints

Since

SqlUtil 1.7.5

getDropAllConstraintsAndIndexesOnColumnSql()

list< auto > SqlUtil::AbstractTable::getDropAllConstraintsAndIndexesOnColumnSql	(	string	cname,
		*hash< auto >	opt
	)

gets a list of SQL strings to drop all constraints and indexes with the given column name; if the column does not exist then an empty list is returned

Example:

list l = table.getDropAllConstraintsAndIndexesOnColumnSql("status");

Parameters

cname	the name of the column
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropColumnSql()

list< auto > SqlUtil::AbstractTable::getDropColumnSql	(	string	cname,
		*hash< auto >	opt
	)

returns the SQL that can be used to drop a column from the table

Example:

string sql = table.getDropColumnSql("notes_2");

Parameters

cname	the name of the column to drop
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a column from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	the named column is not present in the table

Note

• this method retrieves the column definitions from the database if none have already been defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintIfExistsSql()

*string SqlUtil::AbstractTable::getDropConstraintIfExistsSql	(	string	cname,
		*hash< auto >	opt,
		*reference< AbstractConstraint >	cref
	)

gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOTHING; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

*string sql = table.getDropConstraintIfExistsSql("uk_mytable_name");

Parameters

cname	the name of the constraint to drop
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options
cref	an optional reference to an AbstractConstraint object that will return the constraint dropped

Returns

the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOTHING; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropConstraintSql()

string SqlUtil::AbstractTable::getDropConstraintSql	(	string	cname,
		*hash< auto >	opt
	)

gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Example:

string sql = table.getDropConstraintSql("uk_mytable_name");

Parameters

cname	the name of the constraint to drop
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop a constraint from the table; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint

Exceptions

OPTION-ERROR	invalid or unsupported option passed
CONSTRAINT-ERROR	the given constraint does not exist in the table

Note

• this method retrieves current constraint definitions from the database if not already loaded or defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropIndexSql()

string SqlUtil::AbstractTable::getDropIndexSql	(	string	iname,
		*hash< auto >	opt
	)

gets the SQL that can be used to drop an index from the table

Example:

string sql = table.getDropIndexSql("uk_mytable_name");

Parameters

iname	the name of the index to drop
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop an index from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
INDEX-ERROR	the given index does not exist in the table

Note

• this method retrieves current index definitions from the database if not already loaded or defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropPrimaryKeySql()

list< auto > SqlUtil::AbstractTable::getDropPrimaryKeySql

(

*hash< auto >

opt

)

gets a list of SQL strings that can be used to drop the primary key from the table

Example:

list l = table.getDropPrimaryKeySql();

Parameters

opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to drop the primary key from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
PRIMARY-KEY-ERROR	the table has no primary key

Note

• this method retrieves current primary key definition from the database if not already loaded or defined
• if there are known foreign contraints on the primary key, SQL for dropping those constraints is also returned
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getDropSql()

softlist< auto > SqlUtil::AbstractTable::getDropSql

(

*hash< auto >

opt

)

returns the sql required to drop the table; reimplement in subclasses if necessary

Example:

list l = table.getDropSql();

Parameters

opt	optional callback options; see AbstractDatabase::CallbackOptions for more info

Returns

a list of strings that can be used to drop the table and any other objects assocatied with the table (for example: PostgreSQL table trigger function(s))

Exceptions

OPTION-ERROR

invalid or unknown callback option

getDropTriggerSql()

list< auto > SqlUtil::AbstractTable::getDropTriggerSql	(	string	tname,
		*hash< auto >	opt
	)

returns SQL that can be used to drop the given trigger from the table

Example:

string sql = table.getDropTriggerSql("trig_mytable");

Parameters

tname	the name of the trigger to drop
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to drop the given trigger from the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
TRIGGER-ERROR	the given trigger does not exist

Note

• this method retrieves all current trigger definitions from the database if none have already been defined
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getExpressionMap()

hash< auto > SqlUtil::AbstractTable::getExpressionMap

(

)

Returns the expression map for this database server.

override in subclasses to return driver-specific options

Since

SqlUtil 1.8

getForeignConstraintOptions()

hash< auto > SqlUtil::AbstractTable::getForeignConstraintOptions ( )

private

return the foreign constraint options for this driver

override in subclasses to return driver-specific options

getIndexes()

Indexes SqlUtil::AbstractTable::getIndexes

(

)

returns an object of class Indexes describing the indexes on the table

If there are no indexes on the table then the object returned will be empty (see SqlUtil::Indexes::empty())

Example:

Indexes ix = table.getIndexes();

Returns

an object of class Indexes describing the indexes on the table

getIndexOptions()

hash< auto > SqlUtil::AbstractTable::getIndexOptions ( )

private

returns the index options for this driver

override in subclasses to return driver-specific options

getInsertFromIteratorOptions()

hash< auto > SqlUtil::AbstractTable::getInsertFromIteratorOptions ( )

private

returns the insert from iterator options for this driver

override in subclasses to return driver-specific options

getInsertOperatorMap()

hash< auto > SqlUtil::AbstractTable::getInsertOperatorMap ( )

private

returns the insert operator map for this object

override in subclasses to return driver-specific options

getInsertOptions()

hash< auto > SqlUtil::AbstractTable::getInsertOptions ( )

private

returns the insert options for this driver

override in subclasses to return driver-specific options

getModifyColumnSql()

list< auto > SqlUtil::AbstractTable::getModifyColumnSql	(	string	cname,
		hash< auto >	copt,
		bool	nullable = True,
		*hash< auto >	opt
	)

gets a list of SQL strings that can be used to modify an existing column in the table

Example:

list l = table.getModifyColumnSql("name", ("qore_type": Type::String, "size": 240), False);

Parameters

cname	the name of the column
copt	a hash<auto> describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native functions or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
nullable	if True then the column can hold NULL values; note that primary key columns cannot be nullable
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

a list of SQL strings that can be used to modify an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	no native_type or qore_type keys in column option hash, column does not exist, invalid column data

Note

• make sure and add a default_value value when modifying a column to have a "not null" constraint with existing data
• if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

See also

inDb() for a method that tells if the table is already in the database or not

getNumericType()

AbstractDataProviderType SqlUtil::AbstractTable::getNumericType	(	string	type_name,
		bool	nullable,
		*hash< auto >	options
	)

returns the type for number / numeric columns for the database so that data conversions can be handled properly

Since

SqlUtil 1.6

getNumericTypeImpl()

abstract AbstractDataProviderType SqlUtil::AbstractTable::getNumericTypeImpl ( string  type_name, bool  nullable, *hash< auto >  options  )

privatepure virtual

returns the type for number / numeric columns for the database so that data conversions can be handled properly

Since

SqlUtil 1.6

getPrimaryKey()

AbstractPrimaryKey SqlUtil::AbstractTable::getPrimaryKey

(

)

returns an object of class AbstractPrimaryKey describing the primary key of the table

If there is no primary key then the object returned will be empty (see SqlUtil::AbstractPrimaryKey::empty())

Example:

Returns

an object of class AbstractPrimaryKey describing the primary key of the table

getPseudoColumnHash()

*hash< auto > SqlUtil::AbstractTable::getPseudoColumnHash ( )

private

returns a hash of valid pseudocolumns

override in subclasses to return driver-specific pseudocolumns; by default this method returns NOTHING

getRawUpdateOperatorMap()

hash< auto > SqlUtil::AbstractTable::getRawUpdateOperatorMap ( )

private

returns the raw (default) update operator map for this object

override in subclasses to return driver-specific options

getRecordType()

*hash< string, AbstractDataField > SqlUtil::AbstractTable::getRecordType

(

)

returns a record description for the table

Returns

the record type for the table

Since

SqlUtil 1.6

getRenameColumnSql()

string SqlUtil::AbstractTable::getRenameColumnSql	(	string	old_name,
		string	new_name,
		*hash< auto >	opt
	)

gets an SQL string that can be used to rename an existing column in the table

Example:

string sql = table.getRenameColumnSql("name", "family_name");

Parameters

old_name	the current name of the column
new_name	the new name of the column
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that can be used to rename an existing column in the table

Exceptions

OPTION-ERROR	invalid or unsupported option passed
COLUMN-ERROR	if the old column does not exist in the table or the new column already does

See also

getModifyColumnSql() for a method that allows the column definition to be updated

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getRenameSql()

string SqlUtil::AbstractTable::getRenameSql	(	string	new_name,
		*hash< auto >	opt
	)

returns an SQL string that could be used to rename the table in the database

Example:

string sql = table.getRenameSql("new_name");

Parameters

new_name	the new name for the table
opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

an SQL string that could be used to rename the table in the database

Exceptions

OPTION-ERROR

invalid or unsupported option passed

Note

If the sql_callback_executed option key is True, this method also renames the object internally (see SQL CallBack Executed Flag for more information), additionally if the "db_table_cache" option key is assigned to a Tables argument, then the table cache is also updated with the name change in this case.

See also

• rename()
• inDb() for a method that tells if the table is already in the database or not

getRowIterator() [1/2]

Qore::SQL::SQLStatement SqlUtil::AbstractTable::getRowIterator	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns an SQLStatement object that will iterate the results of a select statement matching the arguments

Example:

SQLStatement i = table.getRowIterator();

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an SQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the SQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

Deprecated:

for getStatement(); if the underlying database connection object returns an AbstractSQLStatement instead of an SQLStatement, then an exception will be raised; use getStatement() instead

getRowIterator() [2/2]

Qore::SQL::SQLStatement SqlUtil::AbstractTable::getRowIterator	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns an SQLStatement object that will iterate the results of a select statement matching the arguments

Example:

SQLStatement i = table.getRowIterator(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an SQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the SQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

Deprecated:

for getStatement(); if the underlying database connection object returns an AbstractSQLStatement instead of an SQLStatement, then an exception will be raised; use getStatement() instead

getRowIteratorNoExec()

Qore::SQL::SQLStatement SqlUtil::AbstractTable::getRowIteratorNoExec	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns an SQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared

Example:

SQLStatement i = table.getRowIteratorNoExec(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an SQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• no transaction thread resources / locks are acquired with this method as the SQL statement is not executed

See also

getRowIterator()

Deprecated:

for getStatementNoExec(); if the underlying database connection object returns an AbstractSQLStatement instead of an SQLStatement, then an exception will be raised; use getStatementNoExec() instead

getSavepointHelper()

AbstractSavepointHelper SqlUtil::AbstractTable::getSavepointHelper

(

*string

savepoint

)

get DB-specific savepoint helper

Parameters

savepoint

the savepoint string, if not given a unique savepoint string will be generated

Since

SqlUtil 1.6

getSavepointHelperImpl()

abstract AbstractSavepointHelper SqlUtil::AbstractTable::getSavepointHelperImpl ( *string  savepoint )

privatepure virtual

get DB-specific savepoint helper

Parameters

savepoint

the savepoint string, if not given a unique savepoint string will be generated

Since

SqlUtil 1.6

getSelectOptions()

hash< auto > SqlUtil::AbstractTable::getSelectOptions ( )

private

returns the select options for this driver

override in subclasses to return driver-specific options

getSelectSql()

string SqlUtil::AbstractTable::getSelectSql	(	*hash< auto >	sh,
		*reference< list< auto > >	args
	)

returns the SQL string to be executed corresponding to the argument hash with an output parameter for the select bind arguments

Example:

list<auto> args;
string sql = table.getSelectSql(sh, \args);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
args	an optional reference to a list for any bind arguments

Returns

the SQL that can be used to execute a select on the database corresponding to the arguments; the select bind arguments are returned in an output parameter

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• the select string is specific to the arguments passed (for example NULL arguments will have clauses like 'is null')
• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically

getSqlDataCallbackOptions()

hash< auto > SqlUtil::AbstractTable::getSqlDataCallbackOptions ( )

private

returns the sql data operation callback options for this driver

override in subclasses to return driver-specific options

getSqlFromList()

string SqlUtil::AbstractTable::getSqlFromList

(

list< auto >

l

)

returns an SQL string corresponding to the list of commands in the argument

Example:

string sql = t.getSqlFromList(list);

Parameters

l	a list of SQL commands

Returns

an SQL string corresponding to the list of commands in the argument

getSqlValue()

string SqlUtil::AbstractTable::getSqlValue

(

auto

v

)

returns a string for use in SQL queries representing the DB-specific value of the argument

Example:

string str = table.getSqlValue(date);

Parameters

v	the value to convert to an SQL value

Returns

a string representing the value usable in SQL strings for the given database driver

Exceptions

VALUE-ERROR

the given value cannot be converted to an SQL string (missing rules for value)

getStatement() [1/2]

Qore::SQL::AbstractSQLStatement SqlUtil::AbstractTable::getStatement	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Example:

AbstractSQLStatement i = table.getStatement();

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExec()

Since

SqlUtil 1.5

getStatement() [2/2]

Qore::SQL::AbstractSQLStatement SqlUtil::AbstractTable::getStatement	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Example:

AbstractSQLStatement i = table.getStatement(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExec()

Since

SqlUtil 1.5

getStatementNoExec() [1/2]

Qore::SQL::AbstractSQLStatement SqlUtil::AbstractTable::getStatementNoExec	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared

Example:

AbstractSQLStatement i = table.getStatement();

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• no transaction thread resources / locks are acquired with this method as the SQL statement is not executed

See also

getStatement()

Since

SqlUtil 1.5

getStatementNoExec() [2/2]

Qore::SQL::AbstractSQLStatement SqlUtil::AbstractTable::getStatementNoExec	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared

Example:

AbstractSQLStatement i = table.getStatementNoExec(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• no transaction thread resources / locks are acquired with this method as the SQL statement is not executed

See also

getStatement()

Since

SqlUtil 1.5

getStatementNoExecWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::getStatementNoExecWithInfo	(	*hash< auto >	select_hash,
		*hash< auto >	opt
	)

returns a result hash including an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments without executing the statement itself; the statement is only prepared

Example:

hash<SqlResultInfo> info = table.getStatementNoExecWithInfo(sh);

Parameters

select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the result info hash; the result key will hold an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• no transaction thread resources / locks are acquired with this method as the SQL statement is not executed

See also

getStatementNoExecWithInfo()

Since

SqlUtil 1.7

getStatementWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::getStatementWithInfo	(	*hash< auto >	select_hash,
		*hash< auto >	opt
	)

returns a result hash including an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Example:

hash<SqlResultInfo> info = table.getStatementWithInfo(sh);

Parameters

select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the result info hash; the result key will hold an AbstractSQLStatement object that will iterate the results of a select statement matching the arguments

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• the AbstractSQLStatement object created by a successful call to this method acquires a thread resource for the underlying AbstractDatasource object that must be released by calling commit() or rollback(), even if the statement does not acquire any database locks

See also

getStatementNoExecWithInfo()

Since

SqlUtil 1.7

getTableColumnDescOptions()

hash< auto > SqlUtil::AbstractTable::getTableColumnDescOptions ( )

private

returns the table column description options for this driver

override in subclasses to return driver-specific options

getTableCreationOptions()

hash< auto > SqlUtil::AbstractTable::getTableCreationOptions ( )

private

returns the table creation options for this driver

override in subclasses to return driver-specific options

getTableDescriptionHashOptions()

hash< auto > SqlUtil::AbstractTable::getTableDescriptionHashOptions ( )

private

returns the table description hash<auto> options for this driver

override in subclasses to return driver-specific options

getTableOptions()

hash< auto > SqlUtil::AbstractTable::getTableOptions ( )

private

returns the table options for this driver

override in subclasses to return driver-specific options

getTriggerOptions()

hash< auto > SqlUtil::AbstractTable::getTriggerOptions ( )

private

returns the trigger options for this driver

override in subclasses to return driver-specific options

getTriggers()

Triggers SqlUtil::AbstractTable::getTriggers

(

)

returns an object of class Triggers describing the triggers on the table

If there are no triggers on the table then the object returned will be empty (see SqlUtil::Triggers::empty())

Example:

Triggers trig = table.getTriggers();

Returns

an object of class Triggers describing the triggers on the table

getTruncateSql()

string SqlUtil::AbstractTable::getTruncateSql

(

*hash< auto >

opt

)

gets the SQL that can be used to truncate the table

Example:

string sql = table.getTruncateSql();

Parameters

opt	a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options

Returns

the SQL that can be used to truncate the table

Exceptions

OPTION-ERROR

invalid or unsupported option passed

See also

inDb() for a method that tells if the table is already in the database or not

Note

if the sql_callback_executed option is True in opt, then the changes are also effected in the current object, if not, then they are not (see SQL CallBack Executed Flag for more information)

getUniqueConstraintIterator()

Qore::AbstractIterator SqlUtil::AbstractTable::getUniqueConstraintIterator

(

)

returns an iterator for all unique constraints on the table (including the primary key if any)

Example:

map printf("unique constraint %s\n", $1.getName()), table.getUniqueConstraintIterator();

Returns

an iterator for all unique constraints on the table (including the primary key if any)

getUpdateOperatorMap()

hash< auto > SqlUtil::AbstractTable::getUpdateOperatorMap ( )

private

returns the update operator map for this object

override in subclasses to return driver-specific options

getUpdateSql()

hash< SqlCommandInfo > SqlUtil::AbstractTable::getUpdateSql	(	hash< auto >	set,
		*hash< auto >	cond
	)

Returns the SQL for the given update parameters.

Parameters

set	the column names and values to set in the update
cond	the update condition

Returns

a hash with the following keys:

• sql: the SQL for the given update parameters
• args: the arguments for the SQL statement

Exceptions

UPDATE-ERROR	set hash is empty; invalid operators or update expressions
COLUMN-ERROR	invalid column reference

Since

SqlUtil 1.6

getUpsertClosure()

code SqlUtil::AbstractTable::getUpsertClosure	(	hash< auto >	row,
		int	upsert_strategy = UpsertAuto,
		*hash< auto >	opt
	)

returns a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned does not check the input hash for validity

Example:

Datasource ds("pgsql:user/pass@db%localhost");
Table table(ds, "table_name");
code upsert = table.getUpsertClosure(row, AbstractTable::UpsertSelectFirst);
on_success ds.commit();
on_error ds.rollback();
map upsert($1), row_list;

Parameters

row	a hash representing an example row to insert or update; every row passed to the upsert closure returned must have the same keys in the same order
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the closure returned does not check the input hash for validity; the closure has the following signature:

int sub upsert(hash<auto> row) {} 

The return value of the closure is an integer code giving the result of the update; see Upsert Result Codes for more information

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

the row values passed to the closure for upserting are not checked if they match the example row passed to the getUpsertClosure() method; passing non-conforming data will cause errors; see SqlUtil::AbstractTable::getUpsertClosureWithValidation() for a similar method that returns a validating closure; the closure returned by this method is faster than the one returned by SqlUtil::AbstractTable::getUpsertClosure() since there is no validation

getUpsertClosureWithValidation()

code SqlUtil::AbstractTable::getUpsertClosureWithValidation	(	hash< auto >	example_row,
		int	upsert_strategy = UpsertAuto,
		*hash< auto >	opt
	)

returns a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the table must have a unique key to do this; the closure returned checks the input hash for validity

Example:

Datasource ds("pgsql:user/pass@db%localhost");
Table table(ds, "table_name");
code upsert = table.getUpsertClosureWithValidation(row, AbstractTable::UpsertSelectFirst);
on_success ds.commit();
on_error ds.rollback();
map upsert($1), row_list;

Parameters

example_row	a hash representing an example row to insert or update; every row passed to the upsert closure returned must have the same keys in the same order or the closure returned will throw an UPSERT-ERROR exception
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

a closure that can be executed given a hash argument representing a single row that will be updated or inserted in the database with the given upsert strategy; the closure has the following signature:

int sub upsert(hash<auto> row) {} 

The return value of the closure is an integer code giving the result of the update; see Upsert Result Codes for more information

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

the row values passed to the closure for upserting are checked if they match the example row passed to the getUpsertClosure() method; passing non-conforming data to the closure will cause the closure to throw an UPSERT-ERROR exception; see SqlUtil::AbstractTable::getUpsertClosure() for a similar method that returns a non-validating closure; the closure returned by this method is a little slower than the one returned by SqlUtil::AbstractTable::getUpsertClosure() since each row is validated

getUpsertOptions()

hash< auto > SqlUtil::AbstractTable::getUpsertOptions ( )

private

returns the upsert options for this driver

override in subclasses to return driver-specific options

getWhereOperatorMap()

hash< auto > SqlUtil::AbstractTable::getWhereOperatorMap

(

)

returns the "where" operator map for this object

override in subclasses to return driver-specific options

hasReturning()

bool SqlUtil::AbstractTable::hasReturning

(

)

returns True if the current database driver supports the "returning" clause in insert statements, False if not

Returns

True if the current database driver supports the "returning" clause in insert statements, False if not

Since

SqlUtil 1.3

inDb()

bool SqlUtil::AbstractTable::inDb

(

)

returns True if the table has been read from or created in the database, False if not

Example:

bool b = table.inDb();

Returns

True if the table has been read from or created in the database, False if not

Note

this method only returns a flag if the object contains configuration already retrieved from the database,

See also

AbstractTable::checkExistence() for a method that will check the database if the table exists in case the table is not already known to exist in the database

insert() [1/4]

*hash< auto > SqlUtil::AbstractTable::insert

(

hash< auto >

row

)

inserts a row into the table without any transaction management; a transaction will be in progress after this method is successfully executed

Example:

table.insert(row);

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement

Returns

in case the "returning" insert option is used, a hash of return values is returned, otherwise NOTHING is returned

Exceptions

COLUMN-ERROR

an unknown column was referenced in the hash to be inserted

insert() [2/4]

*hash< auto > SqlUtil::AbstractTable::insert	(	hash< auto >	row,
		hash< auto >	opt
	)

SqlUtil::AbstractTable::insert() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
opt	optional insert options; see AbstractTable::InsertOptions for more info

insert() [3/4]

*hash< auto > SqlUtil::AbstractTable::insert	(	hash< auto >	row,
		reference< string >	sql
	)

SqlUtil::AbstractTable::insert() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
sql	an optional reference to a string to return the SQL generated for the insert statement

insert() [4/4]

*hash< auto > SqlUtil::AbstractTable::insert	(	hash< auto >	row,
		reference< string >	sql,
		hash< auto >	opt
	)

SqlUtil::AbstractTable::insert() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
sql	an optional reference to a string to return the SQL generated for the insert statement
opt	optional insert options; see AbstractTable::InsertOptions for more info

insertCommit() [1/4]

*hash< auto > SqlUtil::AbstractTable::insertCommit

(

hash< auto >

row

)

inserts a row into the table; the transaction is committed if successful, if an error occurs, it is rolled back

Example:

table.insertCommit(row);

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement

Returns

in case the "returning" insert option is used, a hash of return values is returned, otherwise NOTHING is returned

Exceptions

COLUMN-ERROR

an unknown column was referenced in the hash to be inserted

insertCommit() [2/4]

*hash< auto > SqlUtil::AbstractTable::insertCommit	(	hash< auto >	row,
		hash< auto >	opt
	)

SqlUtil::AbstractTable::insertCommit() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
opt	optional insert options; see AbstractTable::InsertOptions for more info

insertCommit() [3/4]

*hash< auto > SqlUtil::AbstractTable::insertCommit	(	hash< auto >	row,
		reference< string >	sql
	)

SqlUtil::AbstractTable::insertCommit() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
sql	an optional reference to a string to return the SQL generated for the insert statement

insertCommit() [4/4]

*hash< auto > SqlUtil::AbstractTable::insertCommit	(	hash< auto >	row,
		reference< string >	sql,
		hash< auto >	opt
	)

SqlUtil::AbstractTable::insertCommit() variant

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
sql	an optional reference to a string to return the SQL generated for the insert statement
opt	optional insert options; see AbstractTable::InsertOptions for more info

insertFromIterator()

int SqlUtil::AbstractTable::insertFromIterator	(	Qore::AbstractIterator	i,
		*hash< auto >	opt
	)

this method inserts data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; no transaction management is performed with this method

Example:

int rows = table.insertFromIterator(i);

Parameters

i	the Qore::AbstractIterator argument that will provide the data to insert into the current table; the getValue() method of this object must return a hash giving row values (for example, the Qore::SQL::AbstractSQLStatement class is well-suited to be used here); note that the iterator is assumed to return the same columns in the same order in every element
opt	a hash of options for the insert operation; see SqlUtil::AbstractTable::InsertFromIteratorOptions for common options; each driver can support additional driver-specific options; note that this method ignores any "commit_block" option

Returns

the number of rows inserted

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted

See also

• insertFromIterator()
• upsertFromIterator()
• upsertFromIteratorCommit()

insertFromIteratorCommit()

int SqlUtil::AbstractTable::insertFromIteratorCommit	(	Qore::AbstractIterator	i,
		*hash< auto >	opt
	)

this method inserts data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; the transaction is committed if successful, if an error occurs then it is rolled back

Example:

int rows = table.insertFromIteratorCommit(i);

Parameters

i	the Qore::AbstractIterator argument that will provide the data to insert into the current table; the getValue() method of this object must return a hash giving row values (for example, the Qore::SQL::AbstractSQLStatement class is well-suited to be used here); note that the iterator is assumed to return the same columns in every element
opt	a hash of options for the insert operation; see SqlUtil::AbstractTable::InsertFromIteratorOptions for common options; each driver can support additional driver-specific options

Returns

the number of rows inserted

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted

See also

• insertFromIterator()
• upsertFromIterator()
• upsertFromIteratorCommit()

insertFromSelect()

int SqlUtil::AbstractTable::insertFromSelect	(	list	cols,
		AbstractTable	source,
		hash< auto >	sh,
		reference< string >	sql,
		hash< auto >	opt
	)

inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); a transaction will be in progress after this method is successfully executed

Example:

int rows = table.insertFromSelect(("id", "name", "created"), source_table, (("columns": ("id", "name", "created"), "where": ("type": "CUSTOMER"))));

Parameters

cols	the list of column names to use to insert in the current table
source	the source table for the select statement
sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more inf

Returns

the number of rows inserted

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	unknown or invalid column in insert list
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

this method does not take insert options because it is executed entirely in the database server; use insertFromIterator() or insertFromIteratorCommit() to insert arbitrary data with insert options

insertFromSelectCommit()

int SqlUtil::AbstractTable::insertFromSelectCommit	(	list	cols,
		AbstractTable	source,
		hash< auto >	sh,
		reference< string >	sql,
		hash< auto >	opt
	)

inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table); the transaction is committed if successful, if an error occurs, it is rolled back

Example:

int rows = table.insertFromSelectCommit(("id", "name", "created"), source_table, (("columns": ("id", "name", "created"), "where": ("type": "CUSTOMER"))));

Parameters

cols	the list of column names to use to insert in the current table
source	the source table for the select statement
sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more inf

Returns

the number of rows inserted

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	unknown or invalid column in insert list
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

this method does not take insert options because it is executed entirely in the database server; use insertFromIterator() or insertFromIteratorCommit() to insert arbitrary data with insert options

insertFromSelectWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::insertFromSelectWithInfo	(	list< auto >	cols,
		AbstractTable	source,
		hash< auto >	select_hash,
		*hash< auto >	opt
	)

inserts rows into a table based on a select statement from another table (which must be using the same datasource as the current table)

Parameters

cols	the list of column names to use to insert in the current table
source	the source table for the select statement
select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash giving the result of the operation and the SQL used for it; the result key will hold the integer number of rows inserted

Since

SqlUtil 1.7

insertWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::insertWithInfo	(	hash< auto >	row,
		*hash< auto >	opt
	)

Inserts a row and returns the result and also the SQL used.

Parameters

row	a hash representing the row to insert; hash values can also be set with SQL Insert Operator Functions to insert values based on SQL operations to be used directly in the insert statement
opt	optional insert options; see AbstractTable::InsertOptions for more info

Returns

a hash giving the result of the operation and the SQL used for it; the result key is assigned to the integer number of rows inserted

Since

SqlUtil 1.7

isDuplicateRowError()

bool SqlUtil::AbstractTable::isDuplicateRowError

(

hash< ExceptionInfo >

ex

)

Returns True if the exception was raised because of a duplicate row / key error.

Since

SqlUtil 1.7.3

isDuplicateRowErrorImpl()

abstract bool SqlUtil::AbstractTable::isDuplicateRowErrorImpl ( hash< ExceptionInfo >  ex )

privatepure virtual

Returns True if the exception was raised because of a duplicate row / key error.

Since

SqlUtil 1.7.3

modifyColumn()

AbstractColumn SqlUtil::AbstractTable::modifyColumn	(	string	cname,
		hash< auto >	opt,
		bool	nullable = True,
		*reference	lsql
	)

modifies an existing column in the table; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally and the new column definition will be created when create() is called for example

Example:

table.modifyColumn("name", ("qore_type": Type::String, "size": 240), False);

In case the table is already in the database, this method commits the transaction on success and rolls back the transaction if there's an error.

Parameters

cname	the name of the column
opt	a hash<auto> describing the column; the following keys are permitted (other column options may be supported depending on the underlying AbstractTable implementation): qore_type: a qore type string that will be converted to a native DB type with some default conversion; native_type: the native database column type; if both native_type and qore_type are given then native_type is used size: for data types requiring a size component, the size; for numeric columns this represents the precision for example scale: for numeric data types, this value gives the scale default_value: the default value for the column default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native functions or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
lsql	an optional reference to a list of strings to retrieve the SQL used to modify the table (only executed if the table is already in the database)
nullable	if True then the column can hold NULL values; note that primary key columns cannot be nullable

Exceptions

COLUMN-ERROR

no native_type or qore_type keys in column option hash, column does not exist, invalid column data

Note

make sure and add a default_value value when modifying a column to have a "not null" constraint with existing data

See also

inDb() for a method that tells if the table is already in the database or not

removeForeignConstraint()

AbstractForeignConstraint SqlUtil::AbstractTable::removeForeignConstraint

(

string

cname

)

removes the named foreign constraint from the table; no SQL is executed in any case, only the named foreign constraint is removed from the table definition

Example:

table.removeForeignConstraint("uk_mytable_name");

Parameters

cname

the name of the foreign constraint to remove

Returns

the foreign constraint removed from the table

Exceptions

FOREIGN-CONSTRAINT-ERROR

the given constraint does not exist

Note

this method retrieves all current foreign constraint definitions from the database if none have already been defined

See also

inDb() for a method that tells if the table is already in the database or not

rename()

SqlUtil::AbstractTable::rename	(	string	new_name,
		*reference< string >	sql,
		*Tables	table_cache
	)

renames the table

if the table is already known to be in the database in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally

Example:

table.rename("new_name");

Parameters

new_name	the new name for the table
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)
table_cache	an optional Tables argument to update the table name in any table cache

See also

getRenameSql()

renameColumn()

AbstractColumn SqlUtil::AbstractTable::renameColumn	(	string	old_name,
		string	new_name,
		reference< string >	sql
	)

renames an existing column; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally

Example:

AbstractColumn col = table.renameColumn("name", "family_name");

Parameters

old_name	the current name of the column
new_name	the new name of the column
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the column object that was updated

Exceptions

COLUMN-ERROR

if the given column does not exist in the table

See also

modifyColumn() for a method that allows the column definition to be updated

Note

if any primary key or foreign table constraints refer to the column, the internal representations are also renamed, however check constraints are not updated and will be invalid if they refer to the renamed column

renameConstraint()

AbstractConstraint SqlUtil::AbstractTable::renameConstraint	(	string	old_name,
		string	new_name,
		reference	lsql
	)

renames an existing constraint; this can be any constraint on the table, a primary key, a foreign key constraint, or a generic constraint; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally

Example:

AbstractConstraint con = table.renameConstraint("pk_table_name", "pkey_table_name");

Parameters

old_name	the current name of the constraint
new_name	the new name for the constraint
lsql	an optional reference to a list of strings to retrieve the SQL used to modify the constraint (only executed if the table is already in the database)

Returns

the constraint object that was updated

Exceptions

CONSTRAINT-ERROR

if the old constraint does not exist in the table or if the new constraint name already exists

renameIndex()

AbstractIndex SqlUtil::AbstractTable::renameIndex	(	string	old_name,
		string	new_name,
		reference< string >	sql
	)

renames an existing index; if the table is already known to be in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally

Example:

AbstractIndex ix = table.renameIndex("uk_table_name", "unique_table_name");

Parameters

old_name	the current name of the index
new_name	the new name for the index
sql	an optional reference to a string to retrieve the SQL used to modify the table (only executed if the table is already in the database)

Returns

the index object that was updated

Exceptions

INDEX-ERROR

if the old index does not exist in the table or if the new index name already exists

rowCount()

softint SqlUtil::AbstractTable::rowCount

(

)

returns the number of rows in the table

Example:

int cnt = table.rowCount();

Returns

the number of rows in the table

Note

to see if the table is empty or not, use emptyData() as this is much faster than rowCount()

See also

emptyData()

select() [1/2]

*hash< auto > SqlUtil::AbstractTable::select	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns a hash of lists representing the columns and rows in the table that match the argument hash

Example:

*hash h = table.select(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

select() [2/2]

*hash< auto > SqlUtil::AbstractTable::select	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns a hash of lists representing the columns and rows in the table that match the argument hash

Example:

*hash h = table.select(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRow() [1/2]

*hash< auto > SqlUtil::AbstractTable::selectRow	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Example:

*hash h = table.selectRow(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint
DBI-SELECT-ROW-ERROR	more than 1 row retrieved from the server

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRow() [2/2]

*hash< auto > SqlUtil::AbstractTable::selectRow	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns a hash representing the row in the table that matches the argument hash

Example:

*hash<auto> h = table.selectRow(sh);

If more than one row would be returned an exception is raised

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint
DBI-SELECT-ROW-ERROR	more than 1 row retrieved from the server

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRows() [1/2]

*list< auto > SqlUtil::AbstractTable::selectRows	(	*hash< auto >	sh,
		*hash< auto >	opt
	)

returns a list of hashes representing the rows in the table that match the argument hash

Example:

*list l = table.selectRows(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRows() [2/2]

*list< auto > SqlUtil::AbstractTable::selectRows	(	*hash< auto >	sh,
		*reference< string >	sql,
		*hash< auto >	opt
	)

returns a list of hashes representing the rows in the table that match the argument hash

Example:

*list<auto> l = table.selectRows(sh);

Parameters

sh	a hash of conditions for the select statement; see select option hash for information about this argument
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

selectRowsWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::selectRowsWithInfo	(	*hash< auto >	select_hash,
		*hash< auto >	opt
	)

returns a hash with a result key assigned to a list of hashes representing the rows in the table that match the argument hash

Example:

hash<SqlResultInfo> info = table.selectRowsWithInfo(sh);

Parameters

select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an info hash; the result key will contain a list of hashes representing the rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

Since

Qorus 1.7

selectRowWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::selectRowWithInfo	(	*hash< auto >	select_hash,
		*hash< auto >	opt
	)

returns a hash with a result representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Example:

hash<SqlResultInfo> info = table.selectRowWithInfo(sh);

Parameters

select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an info hash; the result key will contain a hash representing the row in the table that matches the argument hash; if more than one row would be returned an exception is raised

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint
DBI-SELECT-ROW-ERROR	more than 1 row retrieved from the server

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

Since

SqlUTil 1.7

selectWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::selectWithInfo	(	*hash< auto >	select_hash,
		*hash< auto >	opt
	)

returns a hash with a result key assigned to a hash of lists representing the columns and rows in the table that match the argument hash

Example:

hash<SqlResultInfo> info = table.selectWithInfo(sh);

Parameters

select_hash	a hash of conditions for the select statement; see select option hash for information about this argument
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

a hash with the result key assigned to a hash of lists representing the columns and rows in the table that match the argument hash

Exceptions

OPTION-ERROR	invalid or unsupported select option
SELECT-ERROR	'offset' supplied without 'orderby' or 'limit', 'orderby' with 'limit' and 'offset' does not match any unique constraint

Note

• if "offset" is supplied and no "orderby" is supplied, then if any primary key exists, the primary key columns will be used for the "orderby" option automatically
• if the forupdate select option is used, then after a successful select operation, the calling thread will own the thread transaction lock

Since

Qorus 1.7

setDatasource()

SqlUtil::AbstractTable::setDatasource

(

AbstractDatasource

nds

)

changes the datasource for the table; if the inDb flag is True, then it is set to False by calling this method

Example:

Table table(ds1, "table_name");
table.setDatasource(ds2);
table.create();

Parameters

nds	the new datasource for the table

Note

it's generally not safe to call this method while SQL operations are in progress in other threads on the same table

setupTable()

SqlUtil::AbstractTable::setupTable	(	hash< auto >	desc,
		*hash< auto >	opt
	)

creates the object from a table description hash

Parameters

desc	a table description hash describing the table
opt	an optional hash of options for the table creation string; see SqlUtil::AbstractTable::TableOptions for common options; each driver can support additional driver-specific options

Exceptions

OPTION-ERROR	invalid or unsupported option passed
DESCRIPTION-ERROR	invalid or unsupported description hash value passed

truncate()

SqlUtil::AbstractTable::truncate

(

)

truncates all the table data without any transaction management

Example:

table.truncate();

Note

Transaction management may not be applied when truncating tables depending on the database driver (for example truncating tables in Oracle does not participate in transaction management), however this method uses the Qore::SQL::AbstractDatasource::exec() method, which normally participates in acquiring a transaction lock for the underlying datasource object; therefore after this method executes normally the transaction lock will be dedicated to the calling thread.

truncateCommit()

SqlUtil::AbstractTable::truncateCommit

(

)

truncates all the table data; releases the transaction lock after executing

Example:

table.truncateCommit();

Note

The transaction is committed if successful or rolled back if an exception occurs; use truncate() to execute without any transaction management

tryExec()

auto SqlUtil::AbstractTable::tryExec

(

string

sql

)

executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost

Example:

t.tryExec("drop table tmp_table");

Include any arguments in the parameter list after the sql argument

Parameters

sql	the SQL to execute

Returns

any return value from the SQL command executed

tryExecArgs()

auto SqlUtil::AbstractTable::tryExecArgs	(	string	sql,
		*softlist< auto >	args
	)

executes some SQL with optional arguments so that if an error occurs the current transaction state is not lost

Example:

t.tryExec("delete from tmp_table where id = %v and name = %v", arglist);

Parameters

sql	the SQL to execute
args	the bind / placeholder or other arguments corresponding to the SQL string

Returns

any return value from the SQL command executed

tryExecRaw()

auto SqlUtil::AbstractTable::tryExecRaw

(

string

sql

)

executes some SQL so that if an error occurs the current transaction state is not lost

Example:

t.tryExecRaw("drop table tmp_table");

Include any arguments in the parameter list after the sql argument

Parameters

sql	the SQL to execute

Returns

any return value from the SQL command executed

update()

int SqlUtil::AbstractTable::update	(	hash< auto >	set,
		hash< auto >	cond,
		reference< string >	sql,
		hash< auto >	opt
	)

updates rows in the table matching an optional condition and returns the count of rows updated; no transaction management is performed with this method

Example:

int ucnt = table.update(("id": id), ("name": name));

Parameters

set	the hash of values to set, key values are column names, hash values are the values to assign to those columns or update operators (see SQL Update Operator Functions)
cond	a hash of conditions for the where clause; see Where Clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows updated

Exceptions

UPDATE-ERROR	the set hash is empty
WHERE-ERROR	unknown operator or invalid arguments given in the cond hash for the where clause

See also

updateCommit()

updateCommit()

int SqlUtil::AbstractTable::updateCommit	(	hash< auto >	set,
		hash< auto >	cond,
		reference< string >	sql,
		hash< auto >	opt
	)

updates rows in the table matching an optional condition and returns the count of rows updated; the transaction is committed if successful, if an error occurs then it is rolled back

Example:

int ucnt = table.updateCommit(("id": id), ("name": name));

Parameters

set	the hash of values to set, key values are column names, hash values are the values to assign to those columns or update operators (see SQL Update Operator Functions)
cond	a hash of conditions for the where clause; see Where Clauses for more information
sql	an optional reference to a string to return the SQL generated for the select statement
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

the count of rows updated

Exceptions

UPDATE-ERROR	the set hash is empty
WHERE-ERROR	unknown operator or invalid arguments given in the cond hash for the where clause

See also

update()

updateWithInfo()

hash< SqlResultInfo > SqlUtil::AbstractTable::updateWithInfo	(	hash< auto >	set,
		hash< auto >	cond,
		*hash< auto >	opt
	)

updates rows in the table matching an optional condition and returns an info hash with the count of rows updated; no transaction management is performed with this method

Example:

hash<SqlResultInfo> info = table.updateWithInfo({"id": id}, {"name": name});

Parameters

set	the hash of values to set, key values are column names, hash values are the values to assign to those columns or update operators (see SQL Update Operator Functions)
cond	a hash of conditions for the where clause; see Where Clauses for more information
opt	optional SQL data operation callback options; see AbstractTable::SqlDataCallbackOptions for more info

Returns

an info hash where the result key is assigned to the count of rows updated

Exceptions

UPDATE-ERROR	the set hash is empty
WHERE-ERROR	unknown operator or invalid arguments given in the cond hash for the where clause

Since

SqlUtil 1.7

upsert()

int SqlUtil::AbstractTable::upsert	(	hash< auto >	row,
		int	upsert_strategy = UpsertAuto,
		*hash< auto >	opt
	)

update or insert the data in the table according to the hash argument; the table must have a unique key to do this

Example:

table.upsert(row);

Parameters

row	a hash representing the row to insert or update
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

an integer code giving the result of the update; see Upsert Result Codes for more information

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upserting multiple rows; it's better to use getBulkUpsertClosure(), getUpsertClosure(), or getUpsertClosureWithValidation() and execute the closure on each row; when using this method, the overhead for setting up the upsert is made for each row which is very inefficient

upsertCommit()

int SqlUtil::AbstractTable::upsertCommit	(	hash< auto >	row,
		int	upsert_strategy = UpsertAuto,
		*hash< auto >	opt
	)

update or insert the data in the table according to the hash argument; the table must have a unique key to do this; the transaction is committed if successful, if an error occurs then it is rolled back

Example:

table.upsertCommit(row);

Parameters

row	a hash representing the row to insert or update
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

an integer code giving the result of the update; see Upsert Result Codes for more information

Exceptions

COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upserting multiple rows; it's better to use getBulkUpsertClosure(), getUpsertClosure(), or getUpsertClosureWithValidation() and execute the closure on each row; when using this method, the overhead for setting up the upsert is made for each row which is very inefficient

upsertFromIterator()

*hash< string, int > SqlUtil::AbstractTable::upsertFromIterator	(	Qore::AbstractIterator	i,
		int	upsert_strategy = AbstractTable::UpsertAuto,
		*hash< auto >	opt
	)

this method upserts or merges data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; no transaction management is performed with this method

Example:

hash h = table.upsertFromIterator(i, AbstractTable::UpsertUpdateFirst);

Parameters

i	the Qore::AbstractIterator argument that will provide the data to upsert or merge into the current table; the getValue() method of this object must return a hash giving row values (for example, the Qore::SQL::SQLStatement class is well-suited to be used here)
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options; note that this method ignores any "commit_block" option

Returns

NOTHING if no actions were taken or a hash with the following keys assigned to numeric values indicating the number of rows processed (keys correspond to SqlUtil::AbstractTable::UpsertResultDescriptionMap keys):

• "inserted": the number of rows inserted
• "verified": the number of rows updated unconditionally; note that this key is returned with all upsert strategy codes other than SqlUtil::AbstractTable::UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, otherwise updated rows are reported as "verified" since rows are updated unconditionally with other the upsert strategy codes
• "unchanged": the number of rows unchanged; this key can only be returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, SqlUtil::AbstractTable::UpsertInsertOnly, or SqlUtil::AbstractTable::UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is True

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upsert option delete_others is True, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is True and is expensive for large data sets.

See also

• upsertFromIteratorCommit()
• upsertFromSelect()
• upsertFromSelectCommit()
• upsert()
• upsertCommit()
• getUpsertClosure()
• insertFromIteratorCommit()
• insertFromIterator()

upsertFromIteratorCommit()

*hash< string, int > SqlUtil::AbstractTable::upsertFromIteratorCommit	(	Qore::AbstractIterator	i,
		int	upsert_strategy = AbstractTable::UpsertAuto,
		*hash< auto >	opt
	)

this method upserts or merges data from the given iterator argument (whose getValue() method must return a hash giving row values) into the current table; the transaction is committed if successful, if an error occurs then it is rolled back

Example:

hash h = table.upsertFromIterator(i, AbstractTable::UpsertUpdateFirst);

Parameters

i	the Qore::AbstractIterator argument that will provide the data to upsert or merge into the current table; the getValue() method of this object must return a hash giving row values (for example, the Qore::SQL::SQLStatement class is well-suited to be used here)
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

NOTHING if no actions were taken or a hash with the following keys assigned to numeric values indicating the number of rows processed (keys correspond to SqlUtil::AbstractTable::UpsertResultDescriptionMap keys):

• "inserted": the number of rows inserted
• "verified": the number of rows updated unconditionally; note that this key is returned with all upsert strategy codes other than SqlUtil::AbstractTable::UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, otherwise updated rows are reported as "verified" since rows are updated unconditionally with other the upsert strategy codes
• "unchanged": the number of rows unchanged; this key can only be returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, SqlUtil::AbstractTable::UpsertInsertOnly, or SqlUtil::AbstractTable::UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is True

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

if upsert option delete_others is True, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is True and is expensive for large data sets.

See also

• upsertFromIteratorCommit()
• upsertFromSelect()
• upsertFromSelectCommit()
• upsert()
• upsertCommit()
• getUpsertClosure()
• insertFromIteratorCommit()
• insertFromIterator()

upsertFromSelect()

*hash< string, int > SqlUtil::AbstractTable::upsertFromSelect	(	AbstractTable	t,
		*hash< auto >	sh,
		int	upsert_strategy = AbstractTable::UpsertAuto,
		*hash< auto >	opt
	)

this method upserts or merges data from the given foreign table and select option hash into the current table; no transaction management is performed with this method

Example:

on_success { table.commit(); table2.commit(); }
on_error { table.rollback(); table2.rollback(); }
hash h = table.upsertFromSelect(table2, ("where": ("account_type": "CUSTOMER")), AbstractTable::UpsertUpdateFirst);

The table argument does not need to be in the same database as the current table; it can also be in a different database server or a database server of a different type (you can use this method to upsert or merge data to or from any database supported by SqlUtil).

Parameters

t	the table for the source data; this does not need to be in the same database as the target (the current table), nor does it need to be the same database type
sh	a hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options; note that this method ignores any "commit_block" option

Returns

NOTHING if no actions were taken or a hash with the following keys assigned to numeric values indicating the number of rows processed (keys correspond to SqlUtil::AbstractTable::UpsertResultDescriptionMap keys):

• "inserted": the number of rows inserted
• "verified": the number of rows updated unconditionally; note that this key is returned with all upsert strategy codes other than SqlUtil::AbstractTable::UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, otherwise updated rows are reported as "verified" since rows are updated unconditionally with other the upsert strategy codes
• "unchanged": the number of rows unchanged; this key can only be returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, SqlUtil::AbstractTable::UpsertInsertOnly, or SqlUtil::AbstractTable::UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is True

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

• if upsert option delete_others is True, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is True and is expensive for large data sets.
• this method uses an AbstractSQLStatement object to pipeline the select data to the upsert code; to release the transaction lock acquired by the AbstractSQLStatement object, a commit() or rollback() action must be executed on the underlying datasource object as in the example above
• unlike insertFromSelect() and insertFromSelectCommit(), this method processes arbitrary input data and accepts upsert options

See also

• upsertFromSelect()
• upsertFromIterator()
• upsertFromIteratorCommit()
• upsert()
• upsertCommit()
• getUpsertClosure()

upsertFromSelectCommit()

*hash< string, int > SqlUtil::AbstractTable::upsertFromSelectCommit	(	AbstractTable	t,
		*hash< auto >	sh,
		int	upsert_strategy = AbstractTable::UpsertAuto,
		*hash< auto >	opt
	)

Upserts or merges data from the given foreign table and select option hash into the current table.

Example:

hash<auto> h = table.upsertFromSelectCommit(table2, ("where": ("account_type": "CUSTOMER")),
    AbstractTable::UpsertUpdateFirst);

The transaction is committed if successful, if an error occurs then it is rolled back.

The table argument does not need to be in the same database as the current table; it can also be in a different database server or a database server of a different type (you can use this method to upsert or merge data to or from any database supported by SqlUtil).

Parameters

t	the table for the source data; this does not need to be in the same database as the target (the current table), nor does it need to be the same database type
sh	a hash of conditions for the select statement; see select option hash for information about this argument
upsert_strategy	see Upsert Strategy Codes for possible values for the upsert strategy
opt	a hash of options for the upsert operation; see SqlUtil::AbstractTable::UpsertOptions for common options; each driver can support additional driver-specific options

Returns

NOTHING if no actions were taken or a hash with the following keys assigned to numeric values indicating the number of rows processed (keys correspond to SqlUtil::AbstractTable::UpsertResultDescriptionMap keys):

• "inserted": the number of rows inserted
• "verified": the number of rows updated unconditionally; note that this key is returned with all upsert strategy codes other than SqlUtil::AbstractTable::UpsertSelectFirst instead of "updated"
• "updated": the number of rows updated; note that this key is only returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, otherwise updated rows are reported as "verified" since rows are updated unconditionally with other the upsert strategy codes
• "unchanged": the number of rows unchanged; this key can only be returned if upsert_strategy is SqlUtil::AbstractTable::UpsertSelectFirst, SqlUtil::AbstractTable::UpsertInsertOnly, or SqlUtil::AbstractTable::UpsertUpdateOnly
• "deleted": the number of rows deleted; this can only be returned if upsert option delete_others is True

Exceptions

OPTION-ERROR	invalid or unsupported option
COLUMN-ERROR	an unknown column was referenced in the hash to be inserted
UPSERT-ERROR	no primary key, unique constraint, or unique index for upsert; not all columns of the unique constraint/index are used in the upsert statement

Note

• if upsert option delete_others is True, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation is only executed if delete_others is True and is expensive for large data sets.
• this method also calls commit() or rollback() on the source table to release the transaction lock on the underlying source table datasource as an AbstractSQLStatement object is used to pipeline the data to the upsert code; use upsertFromSelect() to do manual transaction management instead
• unlike insertFromSelect() and insertFromSelectCommit(), this method processes arbitrary input data and accepts upsert options

See also

• upsertFromSelect()
• upsertFromIterator()
• upsertFromIteratorCommit()
• upsert()
• upsertCommit()
• getUpsertClosure()

Member Data Documentation

AdditionalColumnDescOptions

const SqlUtil::AbstractTable::AdditionalColumnDescOptions

Initial value:

= {
            "notnull": Type::Boolean,
        }

additional column description keys valid when describing columns in a table description hash

the following keys are valid in a table description hash in addition to any AbstractTable::ColumnDescOptions:

• notnull: (bool) if True then the column will have a not null constraint

AlignTableOptions

const SqlUtil::AbstractTable::AlignTableOptions

Initial value:

= TableCreationOptions + {
            "column_map": Type::Hash,
            "index_map": Type::Hash,
            "constraint_map": Type::Hash,
            "trigger_map": Type::Hash,
            "db_table_cache": "Tables",
            "force": Type::Boolean,
        }

table alignment options

currently this option is a combination of SqlUtil::AbstractTable::TableCreationOptions and the following options:

• column_map: (hash) a hash for automatically renaming columns; if the source name (key) exists and the target name (value) does not exist, then the source column is automatically renamed
• index_map: (hash) a hash for automatically renaming indexes; if the source name (key) exists and the target name (value) does not exist, then the source index is automatically renamed
• constraint_map: (hash) a hash for automatically renaming constraints; if the source name (key) exists and the target name (value) does not exist, then the source constraint is automatically renamed
• trigger_map: (hash) a hash for automatically renaming triggers; if the source name (key) exists and the target name (value) does not exist, then the source trigger is automatically renamed
• db_table_cache: (Tables) an optional table cache for maintaining tables in the database and foreign key relationships between tables
• force: (bool) if True and supported by the driver and object, any objects dropped will be dropped with FORCE or CASCADE options

ColumnDescOptions

const SqlUtil::AbstractTable::ColumnDescOptions

Initial value:

= {
            "qore_type": Type::String,
            "native_type": Type::String,
            "size": Type::Int,
            "scale": Type::Int,
            "default_value": Type::NothingType,
            "default_value_native": Type::Boolean,
            "comment": Type::String,
            "notnull": Type::Boolean,
            "driver": Type::Hash,
        }

Column description options.

this option is made up of the following keys:

• qore_type: (string) a qore type string that will be converted to a native DB type with some default conversion
• native_type: (string) the native database column type; if both native_type and qore_type are given then native_type is used
• size: (int) for data types requiring a size component, the size; for numeric columns this represents the precision for example
• scale: (int) for numeric data types, this value gives the scale
• default_value: the default value for the column
• default_value_native: a boolean flag to say if a default_value should be validated against table column type (False) or used as it is (True) to allow to use DBMS native functions or features. Defaults to False. It is strongly recommended to use default_value_native for default_value in driver specific sub-hash to avoid non-portable schema hashes
• comment: (string) an optional comment for the column
• notnull: if the column should have a "not null" constraint on it; if missing the default value is False
• driver: this key can optionally contain a hash keyed by driver name which contains a hash of values that will be added to the column description hash before processing; this way a column description hash can contain all the information required for the column including driver-specific options; any driver-specific options will overwrite values in the top level of the hash if there are duplicate hash keys, see below for an example

  See also

  Column Description Hash

ConstraintOptions

const SqlUtil::AbstractTable::ConstraintOptions = IndexOptions

default constraint options

currently this option is identical to SqlUtil::AbstractTable::IndexOptions

ForeignConstraintOptions

const SqlUtil::AbstractTable::ForeignConstraintOptions

Initial value:

= ConstraintOptions + {
            "table_cache": "Tables",
        }

default foreign constraint options

The following keys can be set for this option:

• table_cache: (Tables) an optional table cache for maintaining cached tables and foreign key relationships between tables

IndexOptions

const SqlUtil::AbstractTable::IndexOptions

Initial value:

= {
            "index_tablespace": Type::String,
            "replace": Type::Boolean,
        }

default index options

• index_tablespace: (string) a string giving the index tablespace to use for indexes
• replace: (bool) if True and supported by the underlying db driver "create or replace" text is used when creating objects

InsertFromIteratorOptions

const SqlUtil::AbstractTable::InsertFromIteratorOptions

Initial value:

= SqlDataCallbackOptions + {
            "info_callback": "code",
            "commit_block": Type::Int,
        }

default insert option keys

In addition to any SqlDataCallbackOptions, the following keys can be set for this option:

• info_callback: see Insert Info Callback
• commit_block: the number of changes made before an automatic commit is made for insert methods that perform commits

InsertOptions

const SqlUtil::AbstractTable::InsertOptions

Initial value:

= SqlDataCallbackOptions + {
            "returning": "stringhashlist",
        }

generic SQL insert options

In addition to any SqlDataCallbackOptions, the following keys can be set for this option:

• returning: a list having elements of one of the two following types:
  • string: column names to return the value inserted
  • hash: a hash having the following keys:
    • "key": (required) the column name to return
    • "type": (optional) the data type for the output placeholder buffer (ex: Type::Number)

Note

using "returning" with a database that does not support this clause will cause an exception to be thrown; see SqlUtil::AbstractTable::hasReturning()

SelectOptions

const SqlUtil::AbstractTable::SelectOptions

Initial value:

= {
            "alias": Type::String,
            "comment": Type::String,
            "hint": Type::String,
            "columns": Type::NothingType,
            "where": "hash/list",
            "orderby": "softstringinthashlist",
            "desc": Type::Boolean,
            "limit": Type::Int,
            "offset": Type::Int,
            "join": Type::Hash,
            "groupby": "softstringinthashlist",
            "having": Type::Hash,
            "superquery": Type::Hash,
            "forupdate": Type::Boolean,
        }

default possible select options; can be extended by driver-specific modules

See also

Complex Select Criteria for more information on the default possible select options; see driver-specific information for any additional select options supported by the driver-specific module

SqlDataCallbackOptions

const SqlUtil::AbstractTable::SqlDataCallbackOptions

Initial value:

= {
            "sqlarg_callback": "code",
            "tablecode": "code",
        }

generic SQL data operation callbacks

The following keys can be set for this option:

• sqlarg_callback: see SQL Operation Callback Closure or Call Reference
• tablecode: a closure taking a string table name and returning an AbstractTable object for late resolution of table names in joins

TableCreationOptions

const SqlUtil::AbstractTable::TableCreationOptions

Initial value:

= IndexOptions + AbstractDatabase::CreationOptions + {
            "omit": "softstringlist",
        }

table creation options

currently this option is a combination of SqlUtil::AbstractTable::IndexOptions and AbstractDatabase::CreationOptions plus the following:

• omit: a list pf attributes to omit; possible values are: indexes, foreign_constraints, triggers (see TableOmissionOptions)

TableDescriptionHashOptions

const SqlUtil::AbstractTable::TableDescriptionHashOptions

Initial value:

= {
            "columns": Type::Hash,
            "primary_key": Type::Hash,
            "indexes": Type::Hash,
            "triggers": Type::Hash,
            "foreign_constraints": Type::Hash,
            "unique_constraints": Type::Hash,
            
            "table_cache": "Tables",
        }

Table description options.

this option is made up of the following keys:

• columns: (column description hash) a hash<auto> describing the column
• primary_key : (primary key description hash) a hash<auto> describing the primary key for the table
• indexes: (index description hashes) a hash<auto> describing the indexes on the table
• triggers: a hash of trigger information keyed by trigger name; the values are the trigger source code; since triggers are driver-dependent, a driver-independent table description would include trigger hashes under the drivers key and the driver key name under that
• foreign_constraints: (foreign constraint hashes) a hash<auto> describing the foreign constraints on the table
• unique_constraints: (unique constraint hashes) a hash<auto> describing the unique constraints on the table
• table_cache: (Tables) an optional table cache for maintaining cached tables and foreign key relationships between tables

See also

Table Description Hash

TableOptions

const SqlUtil::AbstractTable::TableOptions

Initial value:

= {
            "native_case": Type::Boolean,
            "table_cache": "Tables",
        }

table options

The following keys can be set for this option:

• native_case: (bool) if True then objects will be returned in the case the database server returns them in even if the database server uses case-insensitive names; normally in this case SqlUtil will convert the names to lower-case
• table_cache: (Tables) an optional table cache for maintaining cached tables and foreign key relationships between tables

TriggerOptions

const SqlUtil::AbstractTable::TriggerOptions = AbstractDatabase::CreationOptions

default trigger options

currently this option is identical to AbstractDatabase::CreationOptions

UpsertOptions

const SqlUtil::AbstractTable::UpsertOptions

Initial value:

= {
            "info_callback": "code",
            "commit_block": Type::Int,
            "delete_others": Type::Boolean,
            "omit_update": "softstringlist",
        }

default upsert option keys

The following keys can be set for this option:

• commit_block: the number of changes made before an automatic commit is made for upsert methods that perform commits
• delete_others: if this option is True, then a hash of primary key values in the input data is built as the input data is iterated. After iterating, if the row count of the table and the input data matches, then nothing more is done, otherwise, every row of the table is iterated and compared to the primary key hash; if a row does not match a primary key value, then it is deleted. This operation allows tables to be completely synchronized by removing rows in the target table not present in the source table. This operation is expensive for large data sets.
• info_callback: see Upsert Info Callback
• omit_update: allows for an asymmetrical upsert where a set of column values is inserted, but a smaller set is updated in case the unique key values are present in the target table; the value of this key should be set to the columns to omit in the update clause

UpsertResultDescriptionMap

const SqlUtil::AbstractTable::UpsertResultDescriptionMap

Initial value:

= {
            "inserted": UR_Inserted,
            "verified": UR_Verified,
            "updated": UR_Updated,
            "unchanged": UR_Unchanged,
            "deleted": UR_Deleted,
        }

hash mapping upsert descriptions to codes

See also

UpsertResultMap for a reverse mapping

UpsertResultMap

const SqlUtil::AbstractTable::UpsertResultMap

Initial value:

= {
            UR_Inserted: "inserted",
            UR_Verified: "verified",
            UR_Updated: "updated",
            UR_Unchanged: "unchanged",
            UR_Deleted: "deleted",
        }

hash mapping upsert results to a description

See also

UpsertResultDescriptionMap for a reverse mapping