public static
string
|
#
addBackticks( string $identifier )
Enclose table and column identifiers in backticks to escape them.
Enclose table and column identifiers in backticks to escape them.
This method must only be used on TABLE and COLUMN names. Column values must be escaped
through the use of bound parameters.
Parameters
- $identifier
- Table or column name.
Returns
string
Identifier encapsulated in backticks.
|
public static
boolean
|
#
close( )
Close the connection to the database.
Close the connection to the database.
Returns
boolean
True on success false on failure.
|
public static
boolean
|
#
connect( )
Establish a connection to the database.
Establish a connection to the database.
Connection is deliberately non-persistent (persistence can break things if scripts terminate
unexpectedly).
Returns
boolean
True on success, false on failure.
|
public static
string|boolean
|
#
create( string $db_name )
Create an SQLite database with random prefix and creates a language constant for it.
Create an SQLite database with random prefix and creates a language constant for it.
Database name must be alphanumeric and underscore characters only. The database will
automatically be appended with the suffix .db
Parameters
Returns
string|boolean
Path to database file on success, false on failure.
|
public static
boolean
|
#
createTable( string $table, array $columns, string $primary_key = null )
Create a table in the database.
Create a table in the database.
Table names may only be alphanumeric characters. Column names are also alphanumeric but may
also contain underscores.
Parameters
- $table
- Table name (alphanumeric characters only).
- $columns
- Array of column names (keys) and types (values).
- $primary_key
- Name of field to be used as primary key.
Returns
boolean
True on success, false on failure.
|
public static
boolean
|
#
delete( string $table, integer $id )
Delete single row from table based on its ID.
Delete single row from table based on its ID.
Parameters
- $table
- Name of table.
- $id
- ID of row to be deleted.
Returns
boolean
True on success false on failure.
|
public static
boolean
|
#
deleteAll( string $table, TfishCriteria $criteria )
Delete multiple rows from a table according to criteria.
Delete multiple rows from a table according to criteria.
For safety reasons criteria are required; the function will not unconditionally empty table.
Note that SQLite does not support DELETE with INNER JOIN or table alias. Therefore, you
cannot use tags as a criteria in deleteAll() (they will simply be ignored). It may be
possible to get around this restriction with a loop or subquery.
Parameters
- $table
- Name of table.
- $criteria
- TfishCriteria object used to build conditional database query.
Returns
boolean
True on success, false on failure.
|
public static
string
|
#
escapeIdentifier( string $identifier )
Escape delimiters for identifiers (table and column names).
Escape delimiters for identifiers (table and column names).
SQLite supports three styles of identifier delimitation:
- Standard SQL double quotes: "
- MySQL style grave accents: `
- MS SQL style square brackets: []
Escaping of delimiters where they are used as part of a table or column name is done by
doubling them, eg ` becomes ``. In order to safely escape table and column names ALL
three delimiter types must be escaped.
Tuskfish policy is that table names can only contain alphanumeric characters (and column
names can only contain alphanumeric plus underscore characters) so delimiters should never
get into a query as part of an identifier. But just because we are paranoid they are
escaped here anyway.
Parameters
- $identifier
- Name of table or column.
Returns
string
Escaped table or column name.
|
public static
boolean
|
#
executeTransaction( PDOStatement $statement )
Execute a prepared statement within a transaction.
Execute a prepared statement within a transaction.
The $statement parameter should be a prepared statement obtained via preparedStatement($sql).
Note that statement execution is within a transaction and rollback will occur if it fails.
This method should be used with database write operations (INSERT, UPDATE, DELETE).
Parameters
- $statement
- Prepared statement.
Returns
boolean
True on success, false on failure.
|
public static
boolean
|
#
insert( string $table, array $key_values )
Insert a single row into the database within a transaction.
Insert a single row into the database within a transaction.
Parameters
- $table
- Name of table.
- $key_values
- Column names and values to be inserted.
Returns
boolean
True on success, false on failure.
|
public static
integer|boolean
|
#
lastInsertId( )
Retrieves the ID of the last row inserted into the database.
Retrieves the ID of the last row inserted into the database.
Used primarily to grab the ID of newly created content objects so that their accompanying
taglinks can be correctly associated to them.
Returns
integer|boolean
Row ID on success, false on failure.
|
public static
object
|
#
preparedStatement( string $sql )
Return a PDO statement object.
Return a PDO statement object.
Statement object can be used to bind values or parameters and execute queries, thereby
mitigating direct SQL injection attacks.
Parameters
Returns
object
PDOStatement object on success PDOException object on failure.
|
public static
object
|
#
select( string $table, TfishCriteria $criteria = null, array $columns = null )
Prepare and execute a select query.
Prepare and execute a select query.
Returns a PDO statement object, from which results can be extracted with standard PDO calls.
Parameters
- $table
- Name of table.
- $criteria
- TfishCriteria object used to build conditional database query.
- $columns
- Names of database columns to be selected.
Returns
object
PDOStatement object on success PDOException on failure.
|
public static
integer|object
|
#
selectCount( string $table, TfishCriteria $criteria = null, string $column = '' )
Count the number of rows matching a set of conditions.
Count the number of rows matching a set of conditions.
Parameters
- $table
- Name of table.
- $criteria
- TfishCriteria object used to build conditional database query.
- $column
- Name of column.
Returns
integer|object
Row count on success, PDOException object on failure.
|
public static
object
|
#
selectDistinct( string $table, TfishCriteria $criteria, array $columns )
Select results from the database but remove duplicate rows.
Select results from the database but remove duplicate rows.
Use the $columns array to specify which fields you want to filter the results by.
Parameters
- $table
- Name of table.
- $criteria
- TfishCriteria object used to build conditional database query.
- $columns
- Name of columns to filter results by.
Returns
object
PDOStatement on success, PDOException on failure.
|
public static
boolean
|
#
toggleBoolean( integer $id, string $table, string $column )
Toggle the online status of a column between 0 and 1, use for columns representing booleans.
Toggle the online status of a column between 0 and 1, use for columns representing booleans.
Note that the $id MUST represent a column called ID for whatever table you want to run it on.
Parameters
- $id
- ID of the row to update.
- $table
- Name of table.
- $column
- Name of column to update.
Returns
boolean
True on success, false on failure.
|
public static
boolean
|
#
updateCounter( integer $id, string $table, string $column )
Increment a content object counter field by one.
Increment a content object counter field by one.
Call this method when the full description of an individual content object is viewed, or
when a related media file is downloaded.
Parameters
- $id
- ID of content object.
- $table
- Name of table.
- $column
- Name of column.
Returns
boolean
True on success false on failure.
|
public static
boolean
|
#
update( string $table, integer $id, array $key_values )
Update a single row in the database.
Update a single row in the database.
Parameters
- $table
- Name of table.
- $id
- ID of row to update.
- $key_values
- Array of column names and values to update.
Returns
boolean
True on success, false on failure.
|
public static
|
#
updateAll( string $table, array $key_values, TfishCriteria $criteria = null )
Update multiple rows in a table according to criteria.
Update multiple rows in a table according to criteria.
Note that SQLite does not support INNER JOIN or table aliases in UPDATE; therefore it is
not possible to use tags as a criteria in updateAll() at present. It may be possible to get
around this limitation with a subquery. But given that the use case would be unusual /
marginal it is probably just easier to work around it.
Parameters
- $table
- Name of table.
- $key_values
- Array of column names and values to update.
- $criteria
- TfishCriteria object used to build conditional database query.
|
public static
integer
|
#
setType( mixed $data )
Helper method to set appropriate PDO predefined constants in bindValue() and bindParam().
Helper method to set appropriate PDO predefined constants in bindValue() and bindParam().
Do not use this method for arrays, objects or resources. Note that if you pass in an
unexpected data type (ie. one that clashes with a column type definition) PDO will throw
an error.
Parameters
- $data
- Input data to be type set.
Returns
integer
PDO data type constant.
|
public static
object
|
#
validateCriteriaObject( TfishCriteria $criteria )
Validates the properties of a TfishCriteria object to be used in constructing a query.
Validates the properties of a TfishCriteria object to be used in constructing a query.
Parameters
- $criteria
- TfishCriteria object.
Returns
object
Validated TfishCriteria object.
|
public static
array
|
#
validateColumns( array $columns )
Validate and escape column names to be used in constructing a database query.
Validate and escape column names to be used in constructing a database query.
Parameters
- $columns
- Array of unescaped column names.
Returns
array
Array of valid, escaped column names
|
public static
integer
|
#
validateId( integer $id )
Validates and sanitises an ID to be used in constructing a database query.
Validates and sanitises an ID to be used in constructing a database query.
Parameters
- $id
- Input ID to be tested.
Returns
integer
$id Validated ID.
|
public static
array
|
#
validateKeys( array $key_values )
Validate and escapes keys to be used in constructing a database query.
Validate and escapes keys to be used in constructing a database query.
Keys may only consist of alphanumeric and underscore characters. SQLite identifier delimiters
are escaped.
Parameters
- $key_values
- Array of unescaped keys.
Returns
array
Array of valid and escaped keys.
|
public static
string
|
#
validateTableName( string $table_name )
Validate and escape a table name to be used in constructing a database query.
Validate and escape a table name to be used in constructing a database query.
Parameters
- $table_name
- Table name to be checked.
Returns
string
Valid and escaped table name.
|