Qore SqlUtil Module Reference 1.9.2
|
the base abstract class for the table implementation More...
#include <AbstractTable.qc.dox.h>
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 | |
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 | |
addCustomCopOperator (string name, hash< auto > operator) | |
register custom user column operator for this table object | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
cache (*hash< auto > opts) | |
reads in all attributes of the table from the database | |
bool | checkExistence () |
returns True if the table exists in the database, False if not | |
clear () | |
purges the current table definition | |
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 | |
createCommit (*hash< auto > opt) | |
creates the table in the database; releases the transaction lock after creating the table | |
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 | |
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 | |
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 | |
Columns | describe () |
returns an object of class Columns describing the table | |
drop (*hash< auto > opt) | |
drops the table from the database without any transaction management | |
AbstractColumn | dropColumn (string cname, *reference lsql) |
drops a column from the table | |
dropCommit (*hash< auto > opt) | |
drops the table from the database; releases the transaction lock after dropping the table | |
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 | |
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 | |
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 | |
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 | |
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 | |
bool | empty () |
returns True if the table has no definitions, False if not | |
bool | emptyData () |
returns True if the table has no data rows, False if not | |
*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 | |
*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 | |
*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 | |
*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 | |
*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 | |
*AbstractUniqueConstraint | findUniqueConstraint (string name) |
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key) | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
AbstractDataField | getColumnDataField (AbstractColumn column, *hash< SqlUtilDataTypeOptionInfo > options, *string append_desc) |
returns a field object for the given column | |
AbstractDataField | getColumnDataField (string column_name, *hash< auto > options, *string append_desc) |
returns a field object for the given column | |
AbstractDataProviderType | getColumnDataType (string column_name, *hash< SqlUtilDataTypeOptionInfo > options) |
returns the data type for the given column | |
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 | |
*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 | |
*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 | |
*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 | |
*string | getCreatePrimaryKeySql (*hash< auto > opt, bool cache=True) |
returns an SQL string that could be used to create the primary key on the table | |
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 | |
string | getCreateSqlString (*hash< auto > opt) |
returns an SQL string that could be used to create the table and all known properties of the table | |
string | getCreateTableSql (*hash< auto > opt) |
returns an SQL string that could be used to create the basic table structure without indexes and constraints | |
*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 | |
AbstractDataProviderType | getDbType (AbstractColumn column, *hash< SqlUtilDataTypeOptionInfo > options) |
returns the DB type for the given column type | |
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 | |
string | getDesc () |
returns a descriptive string of the datasource (without the password) and the table name (with a possible qualifier for schema, etc) | |
hash< auto > | getDescriptionHash () |
Returns a description hash of the table. | |
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 | |
list< auto > | getDropColumnSql (string cname, *hash< auto > opt) |
returns the SQL that can be used to drop a column from the table | |
*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 | |
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 | |
string | getDropIndexSql (string iname, *hash< auto > opt) |
gets the SQL that can be used to drop an index from the table | |
list< auto > | getDropPrimaryKeySql (*hash< auto > opt) |
gets a list of SQL strings that can be used to drop the primary key from the table | |
softlist< auto > | getDropSql (*hash< auto > opt) |
returns the sql required to drop the table; reimplement in subclasses if necessary | |
list< auto > | getDropTriggerSql (string tname, *hash< auto > opt) |
returns SQL that can be used to drop the given trigger from the table | |
hash< auto > | getExpressionMap () |
Returns the expression map for this database server. | |
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 | |
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 | |
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 | |
AbstractPrimaryKey | getPrimaryKey () |
returns an object of class AbstractPrimaryKey describing the primary key of the table | |
*hash< string, AbstractDataField > | getRecordType () |
returns a record description for the table | |
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 | |
string | getRenameSql (string new_name, *hash< auto > opt) |
returns an SQL string that could be used to rename the table in the database | |
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 | |
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 | |
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 | |
AbstractSavepointHelper | getSavepointHelper (*string savepoint) |
get DB-specific savepoint helper | |
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 | |
string | getSqlFromList (list< auto > l) |
returns an SQL string corresponding to the list of commands in the argument | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
string | getTruncateSql (*hash< auto > opt) |
gets the SQL that can be used to truncate the table | |
Qore::AbstractIterator | getUniqueConstraintIterator () |
returns an iterator for all unique constraints on the table (including the primary key if any) | |
hash< SqlCommandInfo > | getUpdateSql (hash< auto > set, *hash< auto > cond) |
Returns the SQL for the given update parameters. | |
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 | |
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 | |
hash< auto > | getWhereOperatorMap () |
returns the "where" operator map for this object | |
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 | |
bool | inDb () |
returns True if the table has been read from or created in the database, False if not | |
*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 | |
*hash< auto > | insert (hash< auto > row, hash< auto > opt) |
SqlUtil::AbstractTable::insert() variant | |
*hash< auto > | insert (hash< auto > row, reference< string > sql) |
SqlUtil::AbstractTable::insert() variant | |
*hash< auto > | insert (hash< auto > row, reference< string > sql, hash< auto > opt) |
SqlUtil::AbstractTable::insert() variant | |
*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 | |
*hash< auto > | insertCommit (hash< auto > row, hash< auto > opt) |
SqlUtil::AbstractTable::insertCommit() variant | |
*hash< auto > | insertCommit (hash< auto > row, reference< string > sql) |
SqlUtil::AbstractTable::insertCommit() variant | |
*hash< auto > | insertCommit (hash< auto > row, reference< string > sql, hash< auto > opt) |
SqlUtil::AbstractTable::insertCommit() variant | |
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 | |
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 | |
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 | |
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 | |
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) | |
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. | |
bool | isDuplicateRowError (hash< ExceptionInfo > ex) |
Returns True if the exception was raised because of a duplicate row / key error. | |
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 | |
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 | |
rename (string new_name, *reference< string > sql, *Tables table_cache) | |
renames the table | |
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 | |
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 | |
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 | |
rollback () | |
rolls back the current transaction on the underlying Qore::SQL::AbstractDatasource | |
softint | rowCount () |
returns the number of rows in the table | |
*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 | |
*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 | |
*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 | |
*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 | |
*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 | |
*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 | |
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 | |
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 | |
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 | |
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 | |
setupTable (hash< auto > desc, *hash< auto > opt) | |
creates the object from a table description hash | |
truncate () | |
truncates all the table data without any transaction management | |
truncateCommit () | |
truncates all the table data; releases the transaction lock after executing | |
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 | |
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 | |
auto | tryExecRaw (string sql) |
executes some SQL so that if an error occurs the current transaction state is not lost | |
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 | |
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 | |
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 | |
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 | |
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 | |
*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 | |
*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 | |
deprecated *hash< string, int > | upsertFromIteratorNoCommit (Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash< auto > opt) |
A legacy SqlUtil::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 | |
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. | |
*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 | |
const | AlignTableOptions = ... |
table alignment options | |
const | CacheOptions = ... |
default cache options | |
const | ColumnDescOptions = ... |
Column description options. | |
const | ColumnOptions = {} |
Column options; this is currently empty and can be extended in database-specific modules. | |
const | ConstraintOptions = IndexOptions |
default constraint options | |
const | ForeignConstraintOptions = ... |
default foreign constraint options | |
const | IndexOptions = ... |
default index options | |
const | InsertFromIteratorOptions = ... |
default insert option keys | |
const | InsertOptions = ... |
generic SQL insert options | |
const | SelectOptions = ... |
default possible select options; can be extended by driver-specific modules | |
const | SqlDataCallbackOptions = ... |
generic SQL data operation callbacks | |
const | TableCreationOptions = ... |
table creation options | |
const | TableDescriptionHashOptions = ... |
Table description options. | |
const | TableOmissionOptions = ... |
alignment omission options | |
const | TableOptions = ... |
table options | |
const | TriggerOptions = AbstractDatabase::CreationOptions |
default trigger options | |
const | UpsertAuto = 4 |
Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst. | |
const | UpsertInsertFirst = 1 |
const | UpsertInsertOnly = 5 |
Upsert option: insert if the row does not exist, otherwise ignore. | |
const | UpsertOptions = ... |
default upsert option keys | |
const | UpsertResultDescriptionMap = ... |
hash mapping upsert descriptions to codes | |
const | UpsertResultLetterMap = ... |
maps upsert result codes to single letter symbols | |
const | UpsertResultMap = ... |
hash mapping upsert results to a description | |
const | UpsertSelectFirst = 3 |
Upsert option: select first, if the row is unchanged, do nothing, if it doesn't exist, insert, otherwise update. | |
const | UpsertStrategyDescriptionMap = ... |
hash mapping upsert strategy descriptions to upsert strategy codes | |
const | UpsertStrategyMap = ... |
hash mapping upsert strategy codes to a text description | |
const | UpsertUpdateFirst = 2 |
Upsert option: update first, if the update fails, then insert. | |
const | UpsertUpdateOnly = 6 |
Upsert option: update if the row exists, otherwise ignore. | |
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 | |
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 | |
abstract | copyImpl (AbstractTable old) |
db-specific copy actions | |
abstract | doSelectLimitOnlyInternImpl (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 | doSelectOrderByWithOffsetSqlInternImpl (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 | |
*AbstractUniqueConstraint | findUniqueConstraintUnlocked (string name) |
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key) | |
hash< auto > | getAlignTableOptions () |
returns the align table options for this driver | |
hash< string, Columns > | getAllUpsertColumns (*hash< auto > row) |
returns a hash with a single value\ | |
hash< auto > | getCacheOptions () |
returns the cache options for this driver | |
hash< auto > | getColumnDescOptions () |
returns the column description options for this driver | |
hash< auto > | getColumnOperatorMap () |
returns the column operator map for this object | |
*hash< auto > | getColumnOperatorMapImpl () |
Reimplement in subclasses to provide driver specific column operators. | |
hash< auto > | getColumnOptions () |
returns the column options for this driver | |
hash< auto > | getConstraintOptions () |
returns the constraint options for this driver | |
hash< auto > | getForeignConstraintOptions () |
return the foreign constraint options for this driver | |
abstract Indexes | getIndexesImpl () |
Always called with the lock held. | |
hash< auto > | getIndexOptions () |
returns the index options for this driver | |
hash< auto > | getInsertFromIteratorOptions () |
returns the insert from iterator options for this driver | |
hash< auto > | getInsertOperatorMap () |
returns the insert operator map for this object | |
hash< auto > | getInsertOptions () |
returns the insert options for this driver | |
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 | |
*hash< auto > | getPseudoColumnHash () |
returns a hash of valid pseudocolumns | |
abstract hash< auto > | getQoreTypeMapImpl () |
returns the qore type -> column type map | |
hash< auto > | getRawUpdateOperatorMap () |
returns the raw (default) update operator map for this object | |
abstract AbstractSavepointHelper | getSavepointHelperImpl (*string savepoint) |
get DB-specific savepoint helper | |
hash< auto > | getSelectOptions () |
returns the select options for this driver | |
hash< auto > | getSqlDataCallbackOptions () |
returns the sql data operation callback options for this driver | |
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 | |
hash< auto > | getTableCreationOptions () |
returns the table creation options for this driver | |
hash< auto > | getTableDescriptionHashOptions () |
returns the table description hash<auto> options for this driver | |
hash< auto > | getTableOptions () |
returns the table options for this driver | |
hash< auto > | getTriggerOptions () |
returns the trigger options for this driver | |
abstract hash< auto > | getTypeMapImpl () |
returns the type name -> type description hash | |
hash< auto > | getUpdateOperatorMap () |
returns the update operator map for this object | |
hash< auto > | getUpsertOptions () |
returns the upsert options for this driver | |
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. | |
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 | |
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 | |
the base abstract class for the table implementation
Use SqlUtil::Table if you need to construct table object directly
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
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.
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) |
CHECK-CONSTRAINT-ERROR | the table already has a constraint with the given name or invalid columns or options were passed |
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
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.
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):
|
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) |
COLUMN-ERROR | no native_type or qore_type keys in column option hash, column already exists, invalid column data |
default_value
value when adding a column with a "not null"
constraint with existing dataSqlUtil::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.
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 |
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. |
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
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.
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) |
FOREIGN-CONSTRAINT-ERROR | the table already has a constraint with the given name or invalid columns or options were passed |
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
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.
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) |
INDEX-ERROR | the table already has an index with the given name or invalid columns or options were passed |
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
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.
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) |
PRIMARY-KEY-ERROR | the table already has a primary key or invalid columns or options passed |
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
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.
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) |
TRIGGER-ERROR | the table already has a trigger with the given name or invalid options were passed |
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
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.
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) |
UNIQUE-CONSTRAINT-ERROR | the table already has a constraint with the given name or invalid columns passed |
OPTION-ERROR | invalid or unsupported option passed |
bool SqlUtil::AbstractTable::bindEmptyStringsAsNull | ( | ) |
SqlUtil::AbstractTable::cache | ( | *hash< auto > | opts | ) |
reads in all attributes of the table from the database
opts | cache options; see SqlUtil::AbstractTable::CacheOptions for common options; each driver can support additional driver-specific options |
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
|
private |
SqlUtil::AbstractTable::clear | ( | ) |
purges the current table definition
|
private |
creates the object; private constructor
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 |
OPTION-ERROR | invalid or unsupported option passed |
SqlUtil::AbstractTable::create | ( | *hash< auto > | opt | ) |
creates the table with all associated properties (indexes, constraints, etc) without any transaction management
opt | a hash of options for the SQL creation strings |
CREATE-TABLE-ERROR | table has already been read from or created in the database |
SqlUtil::AbstractTable::createCommit | ( | *hash< auto > | opt | ) |
creates the table in the database; releases the transaction lock after creating the table
opt | a hash of options for the SQL creation strings |
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
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 |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
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
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 |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
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
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 |
result
key is the count of rows deletedWHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
Columns SqlUtil::AbstractTable::describe | ( | ) |
SqlUtil::AbstractTable::drop | ( | *hash< auto > | opt | ) |
drops the table from the database without any transaction management
opt | optional callback options; see AbstractDatabase::CallbackOptions for more info |
OPTION-ERROR | invalid or unknown callback option |
AbstractColumn SqlUtil::AbstractTable::dropColumn | ( | string | cname, |
*reference | lsql | ||
) |
drops a column from the table
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.
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) |
COLUMN-ERROR | the named column is not present in the table |
SqlUtil::AbstractTable::dropCommit | ( | *hash< auto > | opt | ) |
drops the table from the database; releases the transaction lock after dropping the table
opt | optional callback options; see AbstractDatabase::CallbackOptions for more info |
OPTION-ERROR | invalid or unknown callback option |
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
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.
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) |
CONSTRAINT-ERROR | the given constraint does not exist in the table |
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
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.
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) |
FOREIGN-CONSTRAINT-ERROR | the given constraint does not exist |
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
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.
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) |
INDEX-ERROR | the given index does not exist |
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
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.
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) |
PRIMARY-KEY-ERROR | no primary key exists |
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
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.
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) |
TRIGGER-ERROR | the given trigger does not exist |
bool SqlUtil::AbstractTable::empty | ( | ) |
returns True if the table has no definitions, False if not
bool SqlUtil::AbstractTable::emptyData | ( | ) |
*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
PRIMARY-KEY-ERROR | the table has no primary key or the primary key has more than one column |
*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
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 |
PRIMARY-KEY-ERROR | the table has no primary key or the the hash passed does not contain all columns of the primary key |
*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
ids | the list of primary key IDs to find; if the list is empty then NOTHING is returned |
PRIMARY-KEY-ERROR | the table has no primary key or the primary key has more than one column |
*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
cond | a hash giving the column values to find; see Where Clauses for the format of this argument |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
where
= cond
*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
cond | a hash giving the column values to find; see Where Clauses for the format of this argument |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
where
= cond
and limit
= 1 *AbstractUniqueConstraint SqlUtil::AbstractTable::findUniqueConstraint | ( | string | name | ) |
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key)
name | the name of the unique constraint to find |
|
private |
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary key)
name | the name of the unique constraint to find |
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
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.
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 |
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 |
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
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.
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):
|
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 |
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 |
default_value
value when adding a column with a "not null"
constraint with existing datastring 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
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 |
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 |
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
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 |
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 |
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
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.
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 |
OPTION-ERROR | invalid or unsupported option passed |
PRIMARY-KEY-ERROR | the table already has a primary key or invalid columns or options passed |
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
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.
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 |
OPTION-ERROR | invalid or unsupported option passed |
TRIGGER-ERROR | the table already has a trigger with the given name or invalid options were passed |
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
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 |
OPTION-ERROR | invalid or unsupported option passed |
UNIQUE-CONSTRAINT-ERROR | the table already has a constraint with the given name or invalid columns passed |
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
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 |
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 |
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
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 |
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 |
|
private |
returns the align table options for this driver
override in subclasses to return driver-specific options
|
private |
returns a hash with a single value\
row | if given, a row to match a unique constraint or unique index |
UPSERT-ERROR | either no unique constraint or index exists or none matching row exist |
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_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 |
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 |
|
private |
returns the cache options for this driver
override in subclasses to return driver-specific options
AbstractDataField SqlUtil::AbstractTable::getColumnDataField | ( | AbstractColumn | column, |
*hash< SqlUtilDataTypeOptionInfo > | options, | ||
*string | append_desc | ||
) |
returns a field object for the given column
column_name | the column |
type | options |
append_desc | an optional string to append to the field's description |
AbstractDataField SqlUtil::AbstractTable::getColumnDataField | ( | string | column_name, |
*hash< auto > | options, | ||
*string | append_desc | ||
) |
returns a field object for the given column
column_name | the column name |
append_desc | an optional string to append to the field's description |
COLUMN-ERROR | unknown column |
AbstractDataProviderType SqlUtil::AbstractTable::getColumnDataType | ( | string | column_name, |
*hash< SqlUtilDataTypeOptionInfo > | options | ||
) |
returns the data type for the given column
column_name | the column name |
COLUMN-ERROR | unknown column |
|
private |
returns the column description options for this driver
override in subclasses to return driver-specific options
|
private |
returns the column operator map for this object
subclasses should to implement getColumnOperatorMapImpl() to return driver-specific options
|
private |
returns the column options for this driver
override in subclasses to return driver-specific options
|
private |
returns the constraint options for this driver
override in subclasses to return driver-specific options
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
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
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 |
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
opt | a hash of options for the SQL creation string |
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
opt | a hash of options for the table creation string; see TableCreationOptions for common options; each driver can support additional driver-specific options |
OPTION-ERROR | invalid or unsupported option passed |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
AbstractDataProviderType SqlUtil::AbstractTable::getDbType | ( | AbstractColumn | column, |
*hash< SqlUtilDataTypeOptionInfo > | options | ||
) |
returns the DB type for the given column type
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
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
hash< auto > SqlUtil::AbstractTable::getDescriptionHash | ( | ) |
Returns a description hash of the table.
columns
primary_key
indexes
foreign_constraints
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
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 |
OPTION-ERROR | invalid or unsupported option passed |
list< auto > SqlUtil::AbstractTable::getDropColumnSql | ( | string | cname, |
*hash< auto > | opt | ||
) |
returns the SQL that can be used to drop a column from the table
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 |
OPTION-ERROR | invalid or unsupported option passed |
COLUMN-ERROR | the named column is not present in the table |
*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
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 |
OPTION-ERROR | invalid or unsupported option passed |
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
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 |
OPTION-ERROR | invalid or unsupported option passed |
CONSTRAINT-ERROR | the given constraint does not exist in the table |
string SqlUtil::AbstractTable::getDropIndexSql | ( | string | iname, |
*hash< auto > | opt | ||
) |
gets the SQL that can be used to drop an index from the table
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 |
OPTION-ERROR | invalid or unsupported option passed |
INDEX-ERROR | the given index does not exist in the table |
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
opt | a hash of options for the SQL string; see SqlUtil::AbstractTable::AlignTableOptions for common options; each driver can support additional driver-specific options |
OPTION-ERROR | invalid or unsupported option passed |
PRIMARY-KEY-ERROR | the table has no primary key |
softlist< auto > SqlUtil::AbstractTable::getDropSql | ( | *hash< auto > | opt | ) |
returns the sql required to drop the table; reimplement in subclasses if necessary
opt | optional callback options; see AbstractDatabase::CallbackOptions for more info |
OPTION-ERROR | invalid or unknown callback option |
list< auto > SqlUtil::AbstractTable::getDropTriggerSql | ( | string | tname, |
*hash< auto > | opt | ||
) |
returns SQL that can be used to drop the given trigger from the table
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 |
OPTION-ERROR | invalid or unsupported option passed |
TRIGGER-ERROR | the given trigger does not exist |
hash< auto > SqlUtil::AbstractTable::getExpressionMap | ( | ) |
Returns the expression map for this database server.
override in subclasses to return driver-specific options
|
private |
return the foreign constraint options for this driver
override in subclasses to return driver-specific options
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())
|
private |
returns the index options for this driver
override in subclasses to return driver-specific options
|
private |
returns the insert from iterator options for this driver
override in subclasses to return driver-specific options
|
private |
returns the insert operator map for this object
override in subclasses to return driver-specific options
|
private |
returns the insert options for this driver
override in subclasses to return driver-specific options
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
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):
|
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 |
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 |
default_value
value when modifying a column to have a "not null"
constraint with existing dataAbstractDataProviderType 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
|
private |
returns the type for number / numeric columns for the database so that data conversions can be handled properly
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())
|
private |
returns a hash of valid pseudocolumns
override in subclasses to return driver-specific pseudocolumns; by default this method returns NOTHING
|
private |
returns the raw (default) update operator map for this object
override in subclasses to return driver-specific options
*hash< string, AbstractDataField > SqlUtil::AbstractTable::getRecordType | ( | ) |
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
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 |
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 |
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
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 |
OPTION-ERROR | invalid or unsupported option passed |
"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.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
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 |
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 |
"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 automaticallyQore::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
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 |
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 |
"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 automaticallyQore::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
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 |
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 |
"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 automaticallyAbstractSavepointHelper SqlUtil::AbstractTable::getSavepointHelper | ( | *string | savepoint | ) |
get DB-specific savepoint helper
savepoint | the savepoint string, if not given a unique savepoint string will be generated |
|
private |
get DB-specific savepoint helper
savepoint | the savepoint string, if not given a unique savepoint string will be generated |
|
private |
returns the select options for this driver
override in subclasses to return driver-specific options
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
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 |
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 |
"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
|
private |
returns the sql data operation callback options for this driver
override in subclasses to return driver-specific options
string SqlUtil::AbstractTable::getSqlFromList | ( | list< auto > | l | ) |
returns an SQL string corresponding to the list of commands in the argument
l | a list of SQL commands |
string SqlUtil::AbstractTable::getSqlValue | ( | auto | v | ) |
returns a string for use in SQL queries representing the DB-specific value of the argument
v | the value to convert to an SQL value |
VALUE-ERROR | the given value cannot be converted to an SQL string (missing rules for value) |
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
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 |
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 |
"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 automaticallyQore::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
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 |
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 |
"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 automaticallyQore::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
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 |
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 |
"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 automaticallyQore::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
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 |
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 |
"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 automaticallyhash< 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
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 |
result
key will hold an AbstractSQLStatement object that will iterate the results of a select statement matching the argumentsOPTION-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 |
"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 automaticallyhash< 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
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 |
result
key will hold an AbstractSQLStatement object that will iterate the results of a select statement matching the argumentsOPTION-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 |
"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
|
private |
returns the table column description options for this driver
override in subclasses to return driver-specific options
|
private |
returns the table creation options for this driver
override in subclasses to return driver-specific options
|
private |
returns the table description hash<auto> options for this driver
override in subclasses to return driver-specific options
|
private |
returns the table options for this driver
override in subclasses to return driver-specific options
|
private |
returns the trigger options for this driver
override in subclasses to return driver-specific options
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())
string SqlUtil::AbstractTable::getTruncateSql | ( | *hash< auto > | opt | ) |
gets the SQL that can be used to truncate 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 |
OPTION-ERROR | invalid or unsupported option passed |
Qore::AbstractIterator SqlUtil::AbstractTable::getUniqueConstraintIterator | ( | ) |
returns an iterator for all unique constraints on the table (including the primary key if any)
|
private |
returns the update operator map for this object
override in subclasses to return driver-specific options
hash< SqlCommandInfo > SqlUtil::AbstractTable::getUpdateSql | ( | hash< auto > | set, |
*hash< auto > | cond | ||
) |
Returns the SQL for the given update parameters.
set | the column names and values to set in the update |
cond | the update condition |
sql:
the SQL for the given update parametersargs:
the arguments for the SQL statementUPDATE-ERROR | set hash is empty; invalid operators or update expressions |
COLUMN-ERROR | invalid column reference |
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
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 |
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 |
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_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 |
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 |
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
|
private |
returns the upsert options for this driver
override in subclasses to return driver-specific options
hash< auto > SqlUtil::AbstractTable::getWhereOperatorMap | ( | ) |
returns the "where" operator map for this object
override in subclasses to return driver-specific options
bool SqlUtil::AbstractTable::hasReturning | ( | ) |
bool SqlUtil::AbstractTable::inDb | ( | ) |
returns True if the table has been read from or created in the database, False if not
*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
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 |
"returning"
insert option is used, a hash of return values is returned, otherwise NOTHING is returnedCOLUMN-ERROR | an unknown column was referenced in the hash to be inserted |
*hash< auto > SqlUtil::AbstractTable::insert | ( | hash< auto > | row, |
hash< auto > | opt | ||
) |
SqlUtil::AbstractTable::insert() variant
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 |
*hash< auto > SqlUtil::AbstractTable::insert | ( | hash< auto > | row, |
reference< string > | sql | ||
) |
SqlUtil::AbstractTable::insert() variant
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 |
*hash< auto > SqlUtil::AbstractTable::insert | ( | hash< auto > | row, |
reference< string > | sql, | ||
hash< auto > | opt | ||
) |
SqlUtil::AbstractTable::insert() variant
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 |
*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
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 |
"returning"
insert option is used, a hash of return values is returned, otherwise NOTHING is returnedCOLUMN-ERROR | an unknown column was referenced in the hash to be inserted |
*hash< auto > SqlUtil::AbstractTable::insertCommit | ( | hash< auto > | row, |
hash< auto > | opt | ||
) |
SqlUtil::AbstractTable::insertCommit() variant
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 |
*hash< auto > SqlUtil::AbstractTable::insertCommit | ( | hash< auto > | row, |
reference< string > | sql | ||
) |
SqlUtil::AbstractTable::insertCommit() variant
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 |
*hash< auto > SqlUtil::AbstractTable::insertCommit | ( | hash< auto > | row, |
reference< string > | sql, | ||
hash< auto > | opt | ||
) |
SqlUtil::AbstractTable::insertCommit() variant
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 |
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
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 |
OPTION-ERROR | invalid or unsupported option |
COLUMN-ERROR | an unknown column was referenced in the hash to be inserted |
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
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 |
OPTION-ERROR | invalid or unsupported option |
COLUMN-ERROR | an unknown column was referenced in the hash to be inserted |
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
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 |
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 |
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
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 |
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 |
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)
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 |
result
key will hold the integer number of rows insertedhash< SqlResultInfo > SqlUtil::AbstractTable::insertWithInfo | ( | hash< auto > | row, |
*hash< auto > | opt | ||
) |
Inserts a row and returns the result and also the SQL used.
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 |
result
key is assigned to the integer number of rows insertedbool SqlUtil::AbstractTable::isDuplicateRowError | ( | hash< ExceptionInfo > | ex | ) |
Returns True if the exception was raised because of a duplicate row / key error.
|
private |
Returns True if the exception was raised because of a duplicate row / key error.
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
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.
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):
|
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 |
COLUMN-ERROR | no native_type or qore_type keys in column option hash, column does not exist, invalid column data |
default_value
value when modifying a column to have a "not null"
constraint with existing dataAbstractForeignConstraint 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
cname | the name of the foreign constraint to remove |
FOREIGN-CONSTRAINT-ERROR | the given constraint does not exist |
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
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 |
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
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) |
COLUMN-ERROR | if the given column does not exist in the table |
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
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) |
CONSTRAINT-ERROR | if the old constraint does not exist in the table or if the new constraint name already exists |
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
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) |
INDEX-ERROR | if the old index does not exist in the table or if the new index name already exists |
softint SqlUtil::AbstractTable::rowCount | ( | ) |
returns the number of rows in the table
*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
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock *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
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock *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
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock *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
If more than one row would be returned an exception is raised
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock *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
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock *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
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 |
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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lock 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
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 |
result
key will contain a list of hashes representing the rows in the table that match the argument hashOPTION-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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lockhash< 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
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 |
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 raisedOPTION-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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lockhash< 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
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 |
result
key assigned to a hash of lists representing the columns and rows in the table that match the argument hashOPTION-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 |
"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 automaticallyforupdate
select option is used, then after a successful select operation, the calling thread will own the thread transaction lockSqlUtil::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
nds | the new datasource for the table |
SqlUtil::AbstractTable::setupTable | ( | hash< auto > | desc, |
*hash< auto > | opt | ||
) |
creates the object from a table description hash
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 |
OPTION-ERROR | invalid or unsupported option passed |
DESCRIPTION-ERROR | invalid or unsupported description hash value passed |
SqlUtil::AbstractTable::truncate | ( | ) |
truncates all the table data without any transaction management
SqlUtil::AbstractTable::truncateCommit | ( | ) |
truncates all the table data; releases the transaction lock after executing
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
Include any arguments in the parameter list after the sql argument
sql | the SQL to execute |
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
sql | the SQL to execute |
args | the bind / placeholder or other arguments corresponding to the SQL string |
auto SqlUtil::AbstractTable::tryExecRaw | ( | string | sql | ) |
executes some SQL so that if an error occurs the current transaction state is not lost
Include any arguments in the parameter list after the sql argument
sql | the SQL to execute |
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
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 |
UPDATE-ERROR | the set hash is empty |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
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
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 |
UPDATE-ERROR | the set hash is empty |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
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
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 |
result
key is assigned to the count of rows updatedUPDATE-ERROR | the set hash is empty |
WHERE-ERROR | unknown operator or invalid arguments given in the cond hash for the where clause |
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
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 |
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 |
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
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 |
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 |
*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
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 |
"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 TrueOPTION-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 |
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.*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
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 |
"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 TrueOPTION-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 |
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.*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
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).
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 |
"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 TrueOPTION-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 |
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.*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.
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).
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 |
"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 TrueOPTION-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 |
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.const SqlUtil::AbstractTable::AdditionalColumnDescOptions = ... |
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:
const SqlUtil::AbstractTable::AlignTableOptions = ... |
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 renamedindex_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 renamedconstraint_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 renamedtrigger_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 renameddb_table_cache:
(Tables) an optional table cache for maintaining tables in the database and foreign key relationships between tablesforce:
(bool) if True and supported by the driver and object, any objects dropped will be dropped with FORCE
or CASCADE
options const SqlUtil::AbstractTable::ColumnDescOptions = ... |
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 conversionnative_type:
(string) the native database column type; if both native_type
and qore_type
are given then native_type
is usedsize:
(int) for data types requiring a size component, the size; for numeric columns this represents the precision for examplescale:
(int) for numeric data types, this value gives the scaledefault_value:
the default value for the columndefault_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 hashescomment:
(string) an optional comment for the columnnotnull:
if the column should have a "not null" constraint on it; if missing the default value is Falsedriver:
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 const SqlUtil::AbstractTable::ConstraintOptions = IndexOptions |
default constraint options
currently this option is identical to SqlUtil::AbstractTable::IndexOptions
const SqlUtil::AbstractTable::ForeignConstraintOptions = ... |
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 const SqlUtil::AbstractTable::IndexOptions = ... |
const SqlUtil::AbstractTable::InsertFromIteratorOptions = ... |
default insert option keys
In addition to any SqlDataCallbackOptions, the following keys can be set for this option:
info_callback:
see Insert Info Callbackcommit_block:
the number of changes made before an automatic commit is made for insert methods that perform commits const SqlUtil::AbstractTable::InsertOptions = ... |
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:"key"
: (required) the column name to return"type"
: (optional) the data type for the output placeholder buffer (ex: Type::Number)"returning"
with a database that does not support this clause will cause an exception to be thrown; see SqlUtil::AbstractTable::hasReturning() const SqlUtil::AbstractTable::SelectOptions = ... |
default possible select options; can be extended by driver-specific modules
const SqlUtil::AbstractTable::SqlDataCallbackOptions = ... |
generic SQL data operation callbacks
The following keys can be set for this option:
sqlarg_callback:
see SQL Operation Callback Closure or Call Referencetablecode:
a closure taking a string table name and returning an AbstractTable object for late resolution of table names in joins const SqlUtil::AbstractTable::TableCreationOptions = ... |
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) const SqlUtil::AbstractTable::TableDescriptionHashOptions = ... |
Table description options.
this option is made up of the following keys:
columns:
(column description hash) a hash<auto> describing the columnprimary_key
: (primary key description hash) a hash<auto> describing the primary key for the tableindexes:
(index description hashes) a hash<auto> describing the indexes on the tabletriggers:
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 thatforeign_constraints:
(foreign constraint hashes) a hash<auto> describing the foreign constraints on the tableunique_constraints:
(unique constraint hashes) a hash<auto> describing the unique constraints on the tabletable_cache:
(Tables) an optional table cache for maintaining cached tables and foreign key relationships between tablesconst SqlUtil::AbstractTable::TableOptions = ... |
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-casetable_cache:
(Tables) an optional table cache for maintaining cached tables and foreign key relationships between tables const SqlUtil::AbstractTable::TriggerOptions = AbstractDatabase::CreationOptions |
default trigger options
currently this option is identical to AbstractDatabase::CreationOptions
const SqlUtil::AbstractTable::UpsertOptions = ... |
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 commitsdelete_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 Callbackomit_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 const SqlUtil::AbstractTable::UpsertResultDescriptionMap = ... |
hash mapping upsert descriptions to codes
const SqlUtil::AbstractTable::UpsertResultMap = ... |
hash mapping upsert results to a description