SQL Yoga API

• dbconn_beginTransaction
• dbconn_closeCursor
• dbconn_commitTransaction
• dbconn_connect
• dbconn_createObject

• dbconn_deleteObject
• dbconn_disconnect
• dbconn_escapeString
• dbconn_executeSQL
• dbconn_executeWBindings

• dbconn_exists
• dbconn_get
• dbconn_getArrays
• dbconn_getBooleanValue
• dbconn_getVendor

• dbconn_lastInsertedID
• dbconn_openCursor
• dbconn_retrieveQueryAsArray
• dbconn_retrieveQueryAsData
• dbconn_rollbackTransaction

• dbconn_set
• dbconn_setVendor
• dbobject_createFromArray
• dbobject_createFromFile
• dbobject_createFromObject

• dbobject_createObject
• dbobject_deleteConnection
• dbobject_deleteObject
• dbobject_deleteQueryTemplate
• dbobject_exists

• dbobject_get
• dbobject_getArray
• dbobject_getArrays
• dbobject_getConnection
• dbobject_getQueryTemplate

• dbobject_initialize
• dbobject_reloadSchema
• dbobject_save
• dbobject_set
• dbschema_get

• dbschema_migrateToVersion
• dbschema_processMigrationArray
• dbschema_processMigrationFile
• dbschema_reset
• dbschema_tableExists

• dbschema_tableGet
• dbsynch_databaseWithSchema
• sqlquery_addCondition
• sqlquery_addScope
• sqlquery_close

• sqlquery_convertToArray
• sqlquery_convertToRecords
• sqlquery_create
• sqlquery_createObject
• sqlquery_currentRowToArray

• sqlquery_delete
• sqlquery_get
• sqlquery_moveToFirstRecord
• sqlquery_moveToLastRecord
• sqlquery_moveToNextRecord

• sqlquery_moveToPreviousRecord
• sqlquery_moveToRecord
• sqlquery_retrieve
• sqlquery_retrieveAsArray
• sqlquery_retrieveAsData

• sqlquery_retrieveAsRecords
• sqlquery_set
• sqlquery_setConditionsFromUserSearchString
• sqlquery_update
• sqlquerytmpl_createObject

• sqlquerytmpl_deleteObject
• sqlquerytmpl_get
• sqlquerytmpl_set
• sqlrecord_columnPassesThru
• sqlrecord_create

• sqlrecord_createObject
• sqlrecord_delete
• sqlrecord_find
• sqlrecord_get
• sqlrecord_getRelated

• sqlrecord_link
• sqlrecord_set
• sqlrecord_unlink
• sqlrecord_update
• sqlyoga__errorTypeFromError

• sqlyoga__throwError
• sqlyoga_addToArray
• sqlyoga_array
• sqlyoga_arrayPush
• sqlyoga_escapeString

• sqlyoga_getDatabases
• sqlyoga_getDefaultDatabase
• sqlyoga_lastInsertIDQuery
• sqlyoga_setDefaultDatabase
• sqlyoga_splitUserSearchString

• sqlyoga_version
• tableobj_createObject
• tableobj_deleteObject
• tableobj_get
• tableobj_getScope

• tableobj_set
• tableobjects_get
• tableobjects_getRelationship
• tableobjects_getTable
• tableobjects_tableExists

• tblrelation_createObject
• tblrelation_deleteObject
• tblrelation_get
• tblrelation_set
• tblscope_createObject

• tblscope_deleteObject
• tblscope_get
• tblscope_set

dbconn_beginTransaction

Type: command

Syntax: dbconn_beginTransaction <pParamsA>,<pConnName>,<pDBKey>

Summary: Begins a transaction in the database connection.

Returns: empty

Parameters:

Name	Description
pParamsA	Not implemented. Included for possible future use.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

After calling this command no changes you make to the database will be saved until you call dbconn_commitTransaction. You can cancel any changes you made to the database by calling dbconn_rollbackTransaction rather than dbconn_commitTransaction.

Since SQL Yoga will throw errors that occur you need to take that into consideration when implementing error handling while a transaction is open.

Examples:

## Start a transaction in the database.
dbconn_beginTransaction

# Perform multiple calls that modify the database.
...

## If nothing went wrong then commit the changes you made.
if tError is empty then
  dbconn_commitTransaction
else
  ## Something went wrong, rollback to state database was in before calling dbconn_beginTransaction.
  dbconn_rollbackTransaction
end if

dbconn_closeCursor

Type: command

Syntax: dbconn_closeCursor <pCursor>,<pConnName>,<pDBKey>

Summary: Closes a cursor associated with a connection.

Returns: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR.

Parameters:

Name	Description
pCursor	Cursor id.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_commitTransaction

Type: command

Syntax: dbconn_commitTransaction <pParamsA>,<pConnName>,<pDBKey>

Summary: Commits a transaction in the database connection.

Returns: Empty

Parameters:

Name	Description
pParamsA	Not implemented. Included for possible future use.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_connect

Type: command

Syntax: dbconn_connect <pConnName>,<pDBKey>

Summary: Connects to a database using the Connection Objects connection settings.

Returns:
it: Connection id
the result: Empty. Any errors will be thrown: sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

SQL Yoga will connect to a database if necessary whenever it tries to execute a query. This handler can be used if you need to connect before using a SQL Yoga API call that automatically connects.

dbconn_createObject

Type: command

Syntax: dbconn_createObject <pName>,<pAdaptor>,<pType>,<pDBKey>

Summary: Creates a Database Connection for a Database.

Returns: Empty

Parameters:

Name	Description
pName	The Database Connection name. You will use this name whenever you refer to the connection in the API.
pAdaptor	The adaptor to use to connect to the database that the connection is associated with: mysql, odbc, postgresql, sqlite, valentina.
pType	The type of database you are connecting to. Only required for ODBC.
pDBKey	Database Object to associate connection with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

SQL Yoga allows you to have multiple database connections. You can define one database connection for your development environment and anotherf for distribution. Changing which connection SQL Yoga uses for a Database Object is as simple as:

dbobject_setprop "default connection", "development"

dbconn_deleteObject

Type: command

Syntax: dbconn_deleteObject <pConnName>,<pDBKey>

Summary: Deletes a Database Connection from a Database.

Returns: empty

Parameters:

Name	Description
pConnName	The name of a Connection.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

If the connection you are deleting is the default connection then SQL Yoga will assign one of your other connections as the default connection if one exists.

dbconn_disconnect

Type: command

Syntax: dbconn_disconnect <pConnName>,<pDBKey>

Summary: Disconnects from a database connection.

Returns: Empty. Any errors will be thrown: sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_escapeString

Type: function

Syntax: dbconn_escapeString(<pString>,<pConnName>,<pDBKey>)

Summary: Escapses a string using the appropriate characters for the type of the database connection.

Returns: Escaped string

Parameters:

Name	Description
pString	The string to escape.

dbconn_executeSQL

Type: command

Syntax: dbconn_executeSQL <pSQL>,<pConnName>,<pDBKey>

Summary: Executes a SQL query against a database connection. Only execute queries that do not return record sets.

Returns:
it: The affected rows
the result: empty

Parameters:

Name	Description
pSQL	SQL query to execute.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_executeWBindings

Type: command

Syntax: dbconn_executeWBindings <pSQL>,<pBindingsA>,<pConnName>,<pDBKey>

Summary: Executes a SQL query using pValuesA as the binding array.

Returns:
it: Number of affected rows.
the result: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pSQL	SQL to execute. Use placeholders (:1, :2) for binding.
pBindingsA	Array of values to bind. Keyed using integers.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_exists

Type: function

Syntax: dbconn_exists(<pConnName>,<pDBKey>)

Summary: Checks as to whether or not a Connection Object exists.

Returns: empty

Parameters:

Name	Description
pConnName	The name of a Connection.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_get

Type: function

Syntax: dbconn_get(<pProp>,<pConnName>,<pDBKey>)

Summary: Retrieves properties of a Database Connection Object.

Returns: value

Parameters:

Name	Description
pProp	The property to get.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
adaptor	The adaptor to use to connect to the database: mysql, odbc, postgresql, sqlite, valentina.
connection id	The current RevDB connection id (or V4REV database reference if using Valentina). If you need to call any RevDB or native Valentina calls directly then you will need this value.
database	The name of the Database Object that the connection is associated with.
database name	The name of the database on the server. For sqlite this and the 'file' property are synonymous.
dsn	The DSN you would like to connect to. Used with ODBC and maps to the "host" property.
file	For SQLite databases this is the path to the database file on disk. Synonymous with 'database name'.
host	The host for your database.
last error	The last error reported for this database connection. An error can occur when connection to, disconnecting from or querying the database.
last executed sql	The last SQL statement that was executed using this database connection.
last insert[ed] id	When working with database tables that have auto incrementing primary key fields this will return the value used when creating the last record.
log field	The field where all SQL statements that are executed are logged.
log file	The file where all SQL statements that are executed are logged.
open transactions	The number of transactions that have been opened using dbconn_beginTransaction but not yet closed using dbconn_commitTransaction or dbconn_rollbackTransaction.
port	Port to connect to the database over. Default values for each platform are implied.
quote identifiers	If true then SQL Yoga adds quotes around every table/field name in queries. Default is false.
quotes	The left and right strings used to quote table and field names for the database associated with a connection. An array is returned with two keys - left and right.
type	The type of database that this connection is associated with. For most database types this is the same as the adaptor property. For ODBC it is the type of database that is being connected to through ODBC.
use ssl	Whether or not the connection uses SSL to connect to the database.
valentina client connection id	The id representing the connection with the server when running in client/server mode.
version	The version of the database engine connected to.

dbconn_getArrays

Type: function

Syntax: dbconn_getArrays()

Summary: Returns an array of all currently configured connections.

Returns: Array

dbconn_getBooleanValue

Type: function

Syntax: dbconn_getBooleanValue(<pBoolean>,<pConnName>,<pDBKey>)

Summary: Returns the representation of true/false for the database associated with the database connection.

Returns: string

Parameters:

Name	Description
pBoolean	true or false
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_getVendor

Type: function

Syntax: dbconn_getVendor(<pVendor>,<pProp>,<pConnName>,<pDBKey>)

Summary: Gets vendor specific connection settings.

Returns: Mixed

Parameters:

Name	Description
pVendor	Name of vendor to get property for: mysql, odbc, postgresql, sqlite, valentina.
pProp	Property to get.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

See dbconn_setVendor for list of properties.

dbconn_lastInsertedID

Type: command

Syntax: dbconn_lastInsertedID <pConnName>,<pDBKey>

Summary: Returns the last inserted record id for a database.

Returns:
it: Last inserted id
the result: Empty

Parameters:

Name	Description
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_openCursor

Type: command

Syntax: dbconn_openCursor <pSQL>,<pBindingsA>,<pVendorParamsA>,<pConnName>,<pDBKey>

Summary: Executes a query and opens a database cursor that you can use to interact with the database.

Returns:
it: Cursor id
the result: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pSQL	SQL to execute.
pBindingsA	Array of bindings to use with sql.
pVendorParamsA	Array of parameters to use for executing the query. Valentina: location, lock type, direction.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

This command will try to reconnect to the database 1 time when certain error messages are returned from the RevDB driver. For MySQL this occurs when the error is 'MySQL server has gone away'. For SQLite this occurs when the driver returns an error with 'the database file is locked' or 'some kind of disk i/o error occurred'.

dbconn_retrieveQueryAsArray

Type: command

Syntax: dbconn_retrieveQueryAsArray <pSQL>,<pVendorParamsA>,<pBindingsA>,<xOutArray>,<pConnName>,<pDBKey>

Summary: Executes a SQL query against a database connection and returns the results in a numerically indexed array.

Returns: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pSQL	SQL query to execute.
pVendorParamsA	Array of parameters to use for executing the query. All: report null. Valentina: location, lock type, direction.
pBindingsA	Array of values to bind to pSQL. Key 1 will replace :1 in the query, etc. You can pass empty.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

This handler will query a database and convert any returned records into a multi-dimensional array. Each row in the database cursor will be represented by a number in the first dimension. Each column in each row will be contained in the second dimension for that number.

Examples:

# A cursor with 2 columns (name and email) and 2 rows:

theDataA[1]["name"]
theDataA[1]["email"]
theDataA[2]["name"]
theDataA[2]["email"]

dbconn_retrieveQueryAsData

Type: function

Syntax: dbconn_retrieveQueryAsData(<pSQL>,<pBindingsA>,<pColDel>,<pRowDel>,<pConnName>,<pDBKey>)

Summary: Executes a SQL query against a database connection and returns the result as data from the selected columns (no cursor).

Returns: Text data. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pSQL	SQL query to execute.
pBindingsA	Array of values to bind to pSQL. Key 1 will replace :1 in the query, etc. You can pass empty.
pColDel	Character to delimit columns in the returned text. Default is the default value for revDataFromQuery (tab character).
pRowDel	Character to delimit rows in the returned text. Default is the default value for revDataFromQuery (return character).
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_rollbackTransaction

Type: command

Syntax: dbconn_rollbackTransaction <pParamsA>,<pConnName>,<pDBKey>

Summary: Rolls back a transaction in the database connection.

Returns: Empty

Parameters:

Name	Description
pParamsA	Not implemented. Included for possible future use.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbconn_set

Type: command

Syntax: dbconn_set <pProp>,<pValue>,<pConnName>,<pDBKey>

Summary: Sets properties for a Database Connection Object.

Returns: empty

Parameters:

Name	Description
pProp	The property of the Connection to set.
pValue	The value to set the property to.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Different vendor types have different options.

Examples:

## SQLite Example
dbconn_set "file", thePathToDatabaseFile
dbconn_setVendor "sqlite", "options", "binary,extensions"

## MySQL Example
dbconn_set "host", "localhost"
dbconn_set "username", "root"
dbconn_set "password", empty
dbconn_set "database name", "sql_yoga_test"

## ODBC Example
-- Note: When calling dbconn_createObject you pass in 3 parameters
--       The 2nd is "odbc" and the 3rd is the type of database you are
--       connecting to.
-- dbconn_createObject "development", "odbc", "sql server"
dbconn_set "dsn", "sqlserver_odbc"
dbconn_set "username", "odbc_user"
dbconn_set "password", empty

## Local Valentina Example
dbconn_set "file", theDatabase
dbconn_setVendor "valentina", "mac serial number", MyValMacSerialNumber
dbconn_setVendor "valentina", "win serial number", MyValWinSerialNumber
dbconn_setVendor "valentina", "unix serial number", MyValUnixSerialNumber
dbconn_setVendor "valentina", "encryption key", MyEncryptionKey

## Valentina Client Example
dbconn_set "host", "localhost"
dbconn_set "username", "sa"
dbconn_set "password", "sa"
dbconn_set "use ssl", true
dbconn_set "database name", "my database.vdb"

dbconn_setVendor

Type: command

Syntax: dbconn_setVendor <pVendor>,<pProp>,<pValue>,<pConnName>,<pDBKey>

Summary: Sets vendor specific connection settings.

Returns: empty

Parameters:

Name	Description
pVendor	Name of vendor to set property for: mysql, odbc, postgresql, sqlite, valentina.
pProp	Property to set.
pValue	Value to set property to.
pConnName	The name of a Database Connection. The default is the default connection for the Database Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Vendor	Property	Description
SQLite		See revOpenDatabase documentation for a description of each of these properties.
	options
Valentina		See Valentina documentation for a description of each of these properties. Appropriate defaults are used.
	cache size
	debug level
	encryption key
	encryption type
	flush each log
	mac serial number
	mode
	os encoding
	port
	timeout
	segment size
	storage type
	unix serial number
	win serial number

dbobject_createFromArray

Type: command

Syntax: dbobject_createFromArray <pArray>,<pDBKey>

Summary: Creates a Database Object from database object array that you retrieved using dbobject_getArray().

Returns: Empty

Parameters:

Name	Description
pArray	The array containing the Database Object.
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

If you are using SQL Yoga with Levure you will not use this command. Levure uses the YAML configuration files.

dbobject_createFromFile

Type: command

Syntax: dbobject_createFromFile <pFile>,<pDBKey>

Summary: Creates a Database Object from a file where you have previously stored a Database Object using dbobject_save.

Returns: Error message

Parameters:

Name	Description
pFile	The path to the file the Database Object is stored in.
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

If you are using SQL Yoga with Levure you will not use this command. Levure uses the YAML configuration files.

dbobject_createFromObject

Type: command

Syntax: dbobject_createFromObject <pObjectID>,<pDBKey>

Summary: Creates a Database Object from an object that you have previously stored a Database Object in using dbobject_save.

Returns: Empty

Parameters:

Name	Description
pObjectID	The id of the LiveCode object that the Database Object was stored in.
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

If you are using SQL Yoga with Levure you will not use this command. Levure uses the YAML configuration files.

dbobject_createObject

Type: command

Syntax: dbobject_createObject <pDBKey>

Summary: Creates a new Database Object.

Returns: Empty

Parameters:

Name	Description
pDBKey	Database Object to create. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Calling this command will create a new Database Object.

dbobject_deleteConnection

Type: command

Syntax: dbobject_deleteConnection <pConnectionA>

Summary: Deletes a Connection Object from a Database Object.

Returns: empty

Parameters:

Name	Description
pConnectionA	The name of the Connection Object or a Connection Object reference array.

dbobject_deleteObject

Type: command

Syntax: dbobject_deleteObject <pDBKey>

Summary: Deletes a Database Object.

Returns: empty

Parameters:

Name	Description
pDBKey	Database Object to delete. Default is name returned by sqlyoga_getDefaultDatabase().

dbobject_deleteQueryTemplate

Type: command

Syntax: dbobject_deleteQueryTemplate <pTemplateA>,<pDBKey>

Summary: Deletes a SQL Query Template Object from a Database Object.

Returns: empty

Parameters:

Name	Description
pTemplateA	The name of the SQL Query Template Object or a SQL Query Template Object reference array.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase(). Only applies if pTemplateA is a name and not a reference array.

dbobject_exists

Type: function

Syntax: dbobject_exists(<pDBKey>)

Summary: Checks as to whether or not a Database Object exists.

Returns: empty

Parameters:

Name	Description
pDBKey	Database Object to create. Default is name returned by sqlyoga_getDefaultDatabase().

dbobject_get

Type: function

Syntax: dbobject_get(<pProperty>,<pDBKey>)

Summary: Retrieves a Database Object property.

Returns: Value

Parameters:

Name	Description
pProperty	Property to get.
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
connections	A return delimited list of connections in the Database object.
default connection	The name of the Connection object that is used by default in all SQL Yoga API calls using the Database object.
storage file	The path to the file where dbobject_save will store the Database object. (Not used in conjuction with Levure.)
storage object	A reference to the LiveCode control where dbobject_save will store the Database object. (Not used in conjuction with Levure.)
storage type	object or file depending on whether the Database object will be stored in a LiveCode object or a file. (Not used in conjuction with Levure.)

dbobject_getArray

Type: function

Syntax: dbobject_getArray(<pDBKey>)

Summary: Returns the internal array used to represent a Database Object.

Returns: Array

Parameters:

Name	Description
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

dbobject_getArrays

Type: function

Syntax: dbobject_getArrays()

Summary: Returns the internal array that contains all registered database objects.

Returns: Array

dbobject_getConnection

Type: function

Syntax: dbobject_getConnection(<pConnectionName>,<pDBKey>)

Summary: Returns a Connection Object reference array for a given connection name.

Returns: Connection Object reference array or empty if one doesn't exist.

Parameters:

Name	Description
pConnectionName	The name of the Connection Object.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbobject_getQueryTemplate

Type: function

Syntax: dbobject_getQueryTemplate(<pTemplateName>,<pDBKey>)

Summary: Returns a SQL Query Template Object reference array for a given template name.

Returns: SQL Query Template Object refernece array or empty if one doesn't exist.

Parameters:

Name	Description
pTemplateName	The name of the SQL Query Template Object.

dbobject_initialize

Type: command

Syntax: dbobject_initialize <pDbA>,<pRootFolder>,<pDBKey>

Summary: Initializes a database object using a configuration array.

Returns: empty

Parameters:

Name	Description
pDbA	The configuration array.
pRootFolder	Folder used to resolve relative filenames in pDbA.
pDBKey	Key to assign to the new database object.

Description:

dbobject_initialize allows a developer to create a new database object with schema, connections, migrations, query templates, table objects, scopes, and relationships defined in an array format. This handler will parse that format and make the necessary API calls to create objects. This array format allows a database object to be defined in a markup such as YAML.

pDBA array keys:

conections:
  default connection:
  connections:
    CONNECTION_NAME:
      adaptor:
      type:
      file:
      quote identifiers:
relationships:
  - name:
    type:
    order by:
    left table:
    left table key:
    cross-reference table:
    cross-reference table key for left table:
    cross-reference table key for right table:
    right table:
    right table key:
scopes:
  TABLE_NAME:
    - name:
      related table joins:
      conditions:
schema:
  tables:
    TABLE_NAME:
      primary key:
      field order:
      fields:
        FIELD_NAME:
          accepts null:
          default value:
            primary key:
            limit:
            unique:
            signed:
            meta type:
            type:

dbobject_reloadSchema

Type: command

Syntax: dbobject_reloadSchema <pDBKey>

Summary: Imports the database schema into the Database object using the default connection for the Database object.

Returns: Empty. Any errors will be thrown: sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pDBKey	The Database object to target. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Call this handler when you change your database schema and need to tell SQL Yoga about the change. You should call dbobject_save after calling this handler so that the updated schema is saved.

dbobject_save

Type: command

Syntax: dbobject_save <pDBKey>

Summary: Saves a Database Object to a LiveCode object or a file depending on what you have configured.

Returns: Error message

Parameters:

Name	Description
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

If you have set the "storage object" property of a Database Object it will be saved to the LiveCode object and the stack the object is a part of will be saved. If you have set the "storage file" property then it will be saved to the file.

If you are using SQL Yoga with Levure you will not use this command. Levure uses the YAML configuration files.

dbobject_set

Type: command

Syntax: dbobject_set <pProperty>,<pValue>,<pDBKey>

Summary: Sets a Database Object property.

Returns: Empty

Parameters:

Name	Description
pProperty	Property to get.
pValue	Value to set property to.
pDBKey	Database Object to store created Database Object in. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
default connection	The name of the connection that the database object should use for querying the database.
storage file	The path to the file to store your database object in. This allows the object to persist across sessions by using dbobject_createFromFile. (Not used in conjuction with Levure.)
storage object	The long id of the LiveCode control that you would like to save your database object in (as a custom property). This allows the object to persist across sessions by using dbobject_createFromObject. (Not used in conjuction with Levure.)

dbschema_get

Type: function

Syntax: dbschema_get(<pProp>,<pDBKey>)

Summary: Returns info on a Database Objects internal database schema represenation.

Returns: array

Parameters:

Name	Description
pProp	Property to retrieve.
pDBKey	Database Object schema is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
tables	Returns a return delimited list of tables in SQL Yoga's internal representation of the database schema.
tables array	Returns the array used to SQL Yoga's internal representation of the database schema.

dbschema_migrateToVersion

Type: command

Syntax: dbschema_migrateToVersion <pVersion>,<pConnectionA>,<pDBKey>

Summary: Updates the physical database schema to the schema version specified.

Returns: Empty

Parameters:

Name	Description
pVersion	The version to upgrade the database schema to.

Description:

DEPRECATED

dbschema_processMigrationArray

Type: command

Syntax: dbschema_processMigrationArray <pMigrationVersion>,<pMigrationA>,<pConnectionA>,<pDBKey>

Summary:

dbschema_processMigrationFile

Type: command

Syntax: dbschema_processMigrationFile <pMigrationVersion>,<pStackFilename>,<pConnectionA>,<pDBKey>

Summary: Dispatches RunMigration to a stack file.

Parameters:

Name	Description
pMigrationVersion
pStackFilename
pConnectionA
pDBKey

Description:

dbschema_reset

Type: command

Syntax: dbschema_reset <pDBKey>

Summary: Clears the internal database schema representation for a Database Object.

Returns: empty

Parameters:

Name	Description
pDBKey	Database Object schema is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

When SQL Yoga connects to a database it checks to see if the internal database schema represenation has been created. If it hasn't then it is. You can reset the schema in order to have SQL Yoga regenerate it the next time a connection is made.

dbschema_tableExists

Type: function

Syntax: dbschema_tableExists(<pTable>,<pDBKey>)

Summary: Returns true if the table exists in the internal database schema.

Returns: List (CR delimited)

Parameters:

Name	Description
pTable	The name of the table
pDBKey	Database Object schema is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbschema_tableGet

Type: function

Syntax: dbschema_tableGet(<pTable>,<pProperty>,<pDBKey>)

Summary: Retreives the array that represents a table in the internal database schema .

Returns: Array

Parameters:

Name	Description
pTable	The name of the table
pDBKey	Database Object schema is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

dbsynch_databaseWithSchema

Type: command

Syntax: dbsynch_databaseWithSchema <pConnectionA>,<pDBKey>

Summary: Makes any changes necessary to database to put it in synch with the current schema.

Returns: empty

Parameters:

Name	Description
pConnectionA
pDBKey

Description:

sqlquery_addCondition

Type: command

Syntax: sqlquery_addCondition <pQueryA>,<pCondition>

Summary: Adds a condition to a query by creating a scope and assigning the condition to it.

Returns: empty

Parameters:

Name	Description
pQueryA	A SQL Query Object array.
pCondition	The search condition. No bindings are allowed.

Description:

This handler can be useful if you are dynamically building search conditions and need to add a hard coded search condition.

sqlquery_addScope

Type: command

Syntax: sqlquery_addScope <xQueryA>,<pScope>,<pParamsDotDotDot>

Summary: Adds an existing Scope object to a SQL Query Object.

Returns: empty

Parameters:

Name	Description
pScope	The name of the Scope object to add to the query.
pParamsDotDotDot	Pass in additional parameters for use with any binding entries in the scopes conditions. You can pass in strings or a numerically indexed array.

Description:

This handler enables you to dynamically build search criteria for a SQL Query Object. This makes generating queries from complex search UIs much easier.

The array for parameter 3 is numerically indexed and each numeric key has a string and operater key.

If you are passing in multiple arrays for use in replacing the bindings used in the scope condition then each array MUST have the same number of numeric entries. The string key will only be used from the first array passed in however.

Examples:

put sqlquery_createObject("lessons") into theQueryA
sqlquery_addScope theQueryA, "lessons linked to manuals"
sqlquery_retrieveAsArray theQueryA, theDataA

sqlquery_close

Type: command

Syntax: sqlquery_close <xQueryA>

Summary: Closes an open database cursor associated with a SQL Query Object.

Returns: Empty

sqlquery_convertToArray

Type: command

Syntax: sqlquery_convertToArray <pQueryA>,<xOutArray>

Summary: Converts a SQL Object's open database cursor into an array.

Returns: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pQueryA	A SQL Query Object array.

sqlquery_convertToRecords

Type: command

Syntax: sqlquery_convertToRecords <xQueryA>,<xOutRecordsA>

Summary: Converts a SQL Object's open database cursor into records.

Returns: Empty.

Description:

When converting a cursor to records any relationships that have been defined will be used to transform the flat SQL results into a hierarchy representing defined relationships. Each record will have a @table key in the array that contains the name of the table the record represents.

Examples:

# A Records array that has a lesson table with step table children:

theRecordsA[1]["@table"] = "lesson"
theRecordsA[1]["id"] = "12"
theRecordsA[1]["title"] = "my lesson title"
theRecordsA[1]["steps"][1]["@table"] = "steps"
theRecordsA[1]["steps"][1]["id"] = "36"
theRecordsA[1]["steps"][1]["title"] = "My step title"
theRecordsA[1]["steps"][2]["@table"] = "steps"
theRecordsA[1]["steps"][2]["id"] = "58"
theRecordsA[1]["steps"][2]["title"] = "Another step title"

sqlquery_create

Type: command

Syntax: sqlquery_create <pTable>,<pParamsA>,<pConnName>,<pDBKey>

Summary: Creates a record in a table.

Returns:
it: Number of affected rows
the result: empty

Parameters:

Name	Description
pTable	The name of the table to create the record. If you need to pass in a Database or Connection object that isn't the default then pass in SQL Query object.
pParamsA	An array of key value pairs used to create the record.

Description:

This command will perform a CREATE based on the parameters you pass in.

Examples:

## Create lesson
put 0 into theValuesA["draft"]
sqlquery_create "lessons", theValuesA

sqlquery_createObject

Type: function

Syntax: sqlquery_createObject(<pTemplateOrTableName>,<pConnName>,<pDBKey>)

Summary: Creates a SQL Query Object from a SQL Query Template or a table.

Returns: SQL Query Object array

Parameters:

Name	Description
pTemplateOrTableName	The name of a SQL Query Template or table in your database.
pConnName	Name of the connection to attach the SQL Record to. Default is the default connection for the database object.
pDBKey	The name of the database object to attach the SQL Record object to. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

The returned array represents the SQL query. You should not modify this array yourself but rather use the SQL Query Object API to get and set properties.

sqlquery_currentRowToArray

Type: function

Syntax: sqlquery_currentRowToArray(<pQueryA>,<pFields>)

Summary: Converts the current row of the SQL Query Object cursor to an array.

Returns: Array of the row's column values

Parameters:

Name	Description
pQueryA	A SQL Query Object array.
pFields	By default all fields in the row will be returned. Pass in a comma-delimited list of field names to restrict the fields that are returned.

sqlquery_delete

Type: command

Syntax: sqlquery_delete <pQueryA>

Summary: Executes a DELETE query using the where clause of the SQL Query Object.

Returns:
it: Number of affected rows
the result: Empty

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

sqlquery_get

Type: function

Syntax: sqlquery_get(<xQueryA>,<pProp>)

Summary: Retrieves properties of a SQL Query Object array.

Returns: value

Parameters:

Name	Description
pProp	The property to get.

Description:

Property	Description
column delimiter	When retrieving a query as data or converting a query cursor to data the column delimiter is used. Default is TAB.
column names length	Allows you to explicitly select just column name (short), the table and column name (full) or the default behavior.
conditions	A string representing the search conditions for the SQL query. You can use english or SQL operators. You can also use binding. A condition using english operators would look something like this: lesson.name contains :1 AND lesson.description contains :2 If you are using binding then pass in the binding values as parameters 4 - n, where n = the number of binding variables you use in your conditions string. The following english operators are supported: IS, IS NOT, IS GREATER THAN, IS GREATER THAN OR EQUAL TO, IS LESS THAN, IS LESS THAN OR EQUAL TO, IS IN, IS NOT IN, CONTAINS, DOES NOT CONTAIN, BEGINS WITH, ENDS WITH, DOES NOT BEGIN WITH, DOES NOT END WITH
condition bindings	A numerically indexed array. The value of [1] will be used to replace :1 in the conditions, [2] for :2, and so on.
connection	Name of connection to use when querying database.
cursor id	The id of the RevDB cursor associated with the SQL Query object. This only returns a positive value if you use sqlquery_retrieve.
database type	The type of database that the object's connection points to.
distinct	Returns true/false. True means SQL query returns DISTINCT results.
find	Can be set to first to only return the first record found.
from clause	The FROM clause of the query.
group by	Determines how returned rows are grouped.
having	Used to filter the rows returned using group by.
join clause	The JOIN clause of the query.
number of records	The number of records in the object's cursor. A cursor is opened using sqlquery_retrieve.
limit	The number of rows to limit the result to. Useful for large record sets of which you only want a certain number. Default is empty.
offset	The number of rows to skip when returning the results. Setting this to 21 would skip records 1 to 20. Default is empty.
order by	Fields to sort records by.
query	The full SELECT query for the object. This query is used when opening a cursor or retrieving data.
query is at end	Returns true if the object's cursor is at the end of available records. A cursor is opened using sqlquery_retrieve.
query is at start	Returns true if the object's cursor is at the beginning of available records. A cursor is opened using sqlquery_retrieve.
[related table] joins	The related tables to include in the results. If you have defined Table Objects and Relationship Objects then you can simply pass in a string like LEFT JOIN related_table_name. The string will be parsed and the ON portion of the JOIN will be included. If you have not defined Relationship Objects for your Table Objects then you must pass in the full join string, i.e. JOIN related_table_name ON field = field.
row delimiter	When retrieving a query as data or converting a query cursor to data the row delimiter is used. Default is CR.
select clause	Returns the fields to retrieve from the database.
sort by	alias for order by.
table	The name of the primary table for the SQL Query Object.
table as	The alias to use for the table in the query.
tables	A line delimited list of all tables in the query.
where clause	The WHERE clause of the query.

sqlquery_moveToFirstRecord

Type: command

Syntax: sqlquery_moveToFirstRecord <pQueryA>

Summary: Navigates to the first record in a SQL Query Object's database cursor.

Returns: Boolean

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

sqlquery_moveToLastRecord

Type: command

Syntax: sqlquery_moveToLastRecord <pQueryA>

Summary: Navigates to the last record in a SQL Query Object's database cursor.

Returns: Boolean

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

sqlquery_moveToNextRecord

Type: command

Syntax: sqlquery_moveToNextRecord <pQueryA>

Summary: Navigates to the next record in a SQL Query Object's database cursor.

Returns: Boolean

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

sqlquery_moveToPreviousRecord

Type: command

Syntax: sqlquery_moveToPreviousRecord <pQueryA>

Summary: Navigates to the previous record in a SQL Query Object's database cursor.

Returns: Boolean

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

sqlquery_moveToRecord

Type: command

Syntax: sqlquery_moveToRecord <pQueryA>,<pRecNum>

Summary: Navigates to the specified record in a SQL Query Object's database cursor.

Returns: Boolean

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().
pRecNum	The number of the record to navigate to, starting from 1.

sqlquery_retrieve

Type: command

Syntax: sqlquery_retrieve <xQueryA>

Summary: Opens a database cursor for a SQL Query object.

Returns:
it: Cursor id
the result: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Description:

Use this command to open a database cursor after setting the appropriate properties of a SQL Query Object. After opening a cursor you can interact with the cursor using the sqlquery handlers listed below.

sqlquery_retrieveAsArray

Type: command

Syntax: sqlquery_retrieveAsArray <pQueryA>,<xOutArray>

Summary: Executes a SQL Query Object query and returns the result as an array.

Returns: Empty. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

Description:

This handler will execute the query property of a SQL Query OBject and convert any returned records into a multi-dimensional array. Each row in the database cursor will be represented by a number in the first dimension. Each column in each row will be contained in the second dimension for that number.

Examples:

# A cursor with 2 columns (name and email) and 2 rows:

theDataA[1]["name"]
theDataA[1]["email"]
theDataA[2]["name"]
theDataA[2]["email"]

sqlquery_retrieveAsData

Type: function

Syntax: sqlquery_retrieveAsData(<pQueryA>)

Summary: Executes a SQL Query Object query and returns the result as text data from the selected columns (no cursor).

Returns: Text data. Any errors will be thrown: sqlyoga_executesql_err,ERROR or sqlyoga_connection_err,ERROR.

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

Description:

The 'column delimiter' and 'row delimiter' properties of the SQL Query Object will be used to delimit columns and rows.

sqlquery_retrieveAsRecords

Type: command

Syntax: sqlquery_retrieveAsRecords <pQueryA>,<xOutRecordsA>

Summary: Executes a SQL Query Object query and returns the result as a Records array.

Returns: Empty

Parameters:

Name	Description
pQueryA	A SQL Query Object array as created using sqlquery_createObject().

Description:

When retreiving query results as records any relationships that have been defined using Table and Relationship objects will be used to transform the flat SQL results into a hierarchy representing defined relationships. Each record will have a @table key in the array that contains the name of the table the record represents.

Examples:

# A Records array that has a lesson table with step table children:

theRecordsA[1]["@table"] = "lesson"
theRecordsA[1]["id"] = "12"
theRecordsA[1]["title"] = "my lesson title"
theRecordsA[1]["steps"][1]["@table"] = "steps"
theRecordsA[1]["steps"][1]["id"] = "36"
theRecordsA[1]["steps"][1]["title"] = "My step title"
theRecordsA[1]["steps"][2]["@table"] = "steps"
theRecordsA[1]["steps"][2]["id"] = "58"
theRecordsA[1]["steps"][2]["title"] = "Another step title"

sqlquery_set

Type: command

Syntax: sqlquery_set <xQueryA>,<pProp>,<pValue>

Summary: Sets properties on a SQL Query Object array.

Returns: empty

Parameters:

Name	Description
pProp	The property to set.
pValue	The value to set the property to.

Description:

Property	Description
column delimiter	When retrieving a query as data or converting a query cursor to data the column delimiter is used. Default is TAB.
column names length	Allows you to explicitly select just column name (short), the table and column name (long) or the default behavior. The setting only applies to columns that SQL Yoga generates the SQL for. If you manually set the "select clause" property of the query object this setting will only affect any entries entered as TABLE_NAME.*.
conditions	A string representing the search conditions for the SQL query. You can use english or SQL operators. You can also use binding variables with the one caveat being that you need to put single quotes around the binding variables if you are using a string. This is necessary as some conditions in SQL WHERE statements can break if wrapped in single quotes. A condition using english operators would look something like this: lesson.name contains ':1' AND lesson.description contains ':2' AND lesson.id is in :3 If you are using binding then pass in the binding values as parameters 4 - n, where n = the number of binding variables you use in your conditions string. Support english operators are: IS, IS NOT, IS GREATER THAN, IS GREATER THAN OR EQUAL TO, IS LESS THAN, IS LESS THAN OR EQUAL TO, IS IN, IS NOT IN, IS BETWEEN, CONTAINS, DOES NOT CONTAIN, BEGINS WITH, ENDS WITH, DOES NOT BEGIN WITH, DOES NOT END WITH
condition bindings	A numerically indexed array. The value of [1] will be used to replace :1 in the conditions, [2] for :2, and so on.
connection	Name of connection to use when querying database. Default is the default connection for the Database Object.
distinct	Set to true/false to affect whether a SQL query returns DISTINCT results.
find	Set to first to only return the first record in the result.
group by	Determines how returned rows are grouped.
having	Used to filter the rows returned using group by.
limit	The number of rows to limit the result to. Useful for large record sets of which you only want a certain number. Default is empty.
offset	The number of rows to skip when returning the results. Setting this to 21 would skip records 1 to 20. Default is empty.
order by	Fields to sort records by.
[related table] joins	The related tables to include in the results. If you have defined Table Objects and Relationship Objects then you can simply pass in a string like LEFT JOIN related_table_name. The string will be parsed and the ON portion of the JOIN will be included. If you have not defined Relationship Objects for your Table Objects then you must pass in the full join string, i.e. JOIN related_table_name ON field = field.
report null	Set to true to return "NULL" as the column value if a column is NULL. Default is 'false' in which case empty is returned for the column value.
row delimiter	When retrieving a query as data or converting a query cursor to data the row delimiter is used. Default is CR.
select clause	Fields to retrieve from the database. The default is empty which means all fields for all tables in the query. Use the syntax TABLE.* to include all fields for a table.
sort by	alias for order by.
table	The name of the primary table for the SQL Query Object. You can pass in TABLE as ALIAS_NAME to specify an alias for the table in the query. When setting the table property the select clause, related table joins, group by, having and conditions properties are reset.
table as	The alias to use for the table in the query.

sqlquery_setConditionsFromUserSearchString

Type: command

Syntax: sqlquery_setConditionsFromUserSearchString <xQueryA>,<pString>,<pConditions>,<pDefaultOperator>

Summary: Converts a search string into the condition property of a SQL Query Object.

Returns: Empty.

Parameters:

Name	Description
pString	A string representing the terms a user wants to search for.
pConditions	A string representing the SQL WHERE clause. :1 appears where you want to insert search terms. You do no need to wrap :1 in single quotes as this function always assumes you are searching for a string and wraps the strings in single quotes for you.
pDefaultOperator	Default boolean operator to use between words that do not explicity state one. Options are AND (default) or OR.

Description:

Search interfaces often allow a user to enter a search string for querying the database. A string entered by the user as

tacos pizza

might look like this in a WHERE clause:

field = 'tacos' AND field='pizza'

or

field='tacos' OR field='pizza'

A search string such as

"cheese pizza" tacos

might look like this in a WHERE clause:

field = 'cheese pizza' OR field='tacos'

This command takes a search string and combines it with the conditions of a WHERE clause and applies them to a SQL Query Object.

For example, if you passed in the string

"cheese pizza" taco

with a default operator of "OR" and the conditions were "food.name contains :1" then the following WHERE clause would be generated for the query:

WHERE (food.name LIKE  '%cheese pizza%' OR food.name LIKE '%tacos%')

You can see that the WHERE clause searches the name field of the food table for both terms.

sqlquery_update

Type: command

Syntax: sqlquery_update <pTable>,<pConditions>,<pParamsA>,<pConnName>,<pDBKey>

Summary: Updates a record in a table.

Returns:
it: Number of affected rows
the result: enpty.

Parameters:

Name	Description
pTable	The name of the table to update. If you need to pass in a Database or Connection object that isn't the default then pass in SQL Query object.
pConditions	The search conditions. This can be an array of search parameters, a primary key value or an exact string to assign to the WHERE clause. You can pass in an empty value for pConditions if there is no primary key for the table.
pParamsA	An array of key value pairs used to perform the update.

Description:

This command will perform an UPDATE based on the parameters you pass in.

Examples:

## Update lesson with id of 2
put 0 into theValuesA["draft"]
sqlquery_update "lessons", 2, theValuesA

## Update lesson with id of 2
sqlquery_update "lessons", "id = 2", theValuesA

## Update lessons with an id of 12 and 15
put "id is in :1" into theConditionsA["conditions"]
put "12,15" into theConditionsA["condition bindings"][1]
sqlquery_update "lessons", theConditionsA, theValuesA

sqlquerytmpl_createObject

Type: command

Syntax: sqlquerytmpl_createObject <pName>,<pDBKey>

Summary: Creates a SQL Query Template.

Returns:
it: SQL Query Template reference array
the result: empty

Parameters:

Name	Description
pName	The name of the SQL Query Template.
pDBKey	Database Object SQL Query Template is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

A SQL Query Template allows you to preconfigure settings that can be used to create new SQL Query Objects.

sqlquerytmpl_deleteObject

Type: command

Syntax: sqlquerytmpl_deleteObject <pTemplateA>,<pDBKey>

Summary: Deletes a SQL Query Template.

Returns: empty

Parameters:

Name	Description
pDBKey	Database Object SQL Query Template is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

sqlquerytmpl_get

Type: function

Syntax: sqlquerytmpl_get(<pTemplateA>,<pProp>,<pDBKey>)

Summary: Retrieves a property for a SQL Query Template.

Returns: value

Parameters:

Name	Description
pProp	Property to get value for.
pDBKey

Description:

Property	Description
column delimiter
conditions
database
distinct
from clause
full column names
group by
join clause
order by
query
[related table] joins
report null
row delimiter
select clause
table
table as
where clause

Note: You must create a Connection in your Database object before calling this function.

sqlquerytmpl_set

Type: command

Syntax: sqlquerytmpl_set <pTemplateA>,<pProp>,<pValue>

Summary: Sets a SQL Query Template property.

Returns: value

Parameters:

Name	Description
pProp	Property to set value for.

Description:

Property	Description
column delimiter
conditions
distinct
full column names
group by
order by
[related table] joins
report null
row delimiter
select clause
table
table as

sqlrecord_columnPassesThru

Type: function

Syntax: sqlrecord_columnPassesThru(<pRecordA>,<pColumn>)

Summary: Returns a boolean value specifying whether or not a column value passes through to the db when being inserted.

Returns: Boolean

Parameters:

Name	Description
pRecordA	A SQL Record object.
pColumn	The name of the column to check.

Description:

When setting the value of a SQL Record column you can specify that the value should be passed through to the database without quoting or escaping the value. This returns the setting for a particular column.

sqlrecord_create

Type: command

Syntax: sqlrecord_create <xRecordsA>,<pRefreshRecords>

Summary: Creates records in a database using a SQL Record object.

Returns:
it: Number of affected rows
the result: Empty

Parameters:

Name	Description
pRefreshRecords	Pass in false if you do not want to refresh the records with data from the database after you create them.

Description:

This command creates records in the database based on the SQL Record object you pass in.

If you pass in empty values for any of the fields in the primary key for the table then they
will not be included in the CREATE statement.

Examples:

put sqlrecord_createObject("lessons") into theRecordA
sqlrecord_set theRecordA, "title", "A new title"
sqlrecord_set theRecordA, "description", "It's a description."
sqlrecord_create theRecordA

## theRecordA["id"] is now filled in with new id in database for this record

sqlrecord_createObject

Type: function

Syntax: sqlrecord_createObject(<pTable>,<pConnName>,<pDBKey>)

Summary: Creates a SQL Record object (represented as an array) for a table with all columns initialized to their default values.

Returns: Record array

Parameters:

Name	Description
pTable	Name of the table to create the Record array for.
pConnName	Name of the connection to attach the SQL Record to. Default is the default connection for the database object.
pDBKey	The name of the database object to attach the SQL Record object to. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

A SQL Record object represents a table record. An array is used to hold the object values. You should always use sqlrecord_set to update values in this array as there are special keys in the array that get updated when setting values. In addition, sqlrecord_get/sqlrecord_set will throw errors if you try to set the value of a column not in the table which can help catch bugs early on.

sqlrecord_delete

Type: command

Syntax: sqlrecord_delete <xRecordsA>

Summary: Deletes records in a database using the Record array(s) passed in.

Returns:
it: Number of affected rows
the result: empty

Description:

This command deletes records from the database based on the Record array(s) passed in.

Delete callbacks in your Table Object definitions will be called and broadcasts will be sent to callbacks during this operation.

After this handler completes xRecordsA will only contain SQL Record(s) with the"@table key. Other keys will have been removed since the data no longer exists in the database.

Examples:

## Find lesson record in db with id of 12 and store in theRecordA
sqlrecord_find "lessons", 12, theRecordA

sqlrecord_delete theRecordA

sqlrecord_find

Type: command

Syntax: sqlrecord_find <pRecordA>,<pParamsA>,<xRecordsA>

Summary: Finds records in the database and returns as SQL Record objects.

Returns: Empty

Parameters:

Name	Description
pRecordA	Can be the name of a table or a SQL Record object. Pass in an object if you want to specify the connection and/or database to use. Otherwise default database and default database connection are assumed.
pParamsA	Pass a string to search on the primary key field for the table.

Description:

pParamsA has a number of options.

• Pass in the primary key value.
• If the primary key for the table is composed of more than one field then pass in name:value,name:value pairs.
• Pass in an array whose keys are properties of a SQL Query object for more control over results. In this case an array of SQL Record objects is returned.

Examples:

## Find lesson with id of 2 and store it in theRecordA
sqlrecord_find "lessons", 2, theRecordA
-- theRecordA contains a single SQL Record object

## Find record using primary key composed of two fields (id_1 and id_2)
sqlrecord_find "object", "id_1:53,id_2:25", theRecordA
-- theRecordA contains a single SQL Record object

## Find lessons with ids of 12 or 15 and order by id
## Store result in theRecordsA
put "id is in :1" into theParamsA["conditions"]
put "12,15" into theParamsA["condition bindings"][1]
put "id ASC" into theParamsA["order by"]
sqlrecord_find "lessons", theParamsA, theRecordsA
-- theRecordsA contains an array of SQL Records objects (numerically indexed)

sqlrecord_get

Type: function

Syntax: sqlrecord_get(<pRecordA>,<pProp>)

Summary: Returns a property of a SQL Record object.

Returns: value

Parameters:

Name	Description
pRecordA	SQL Record object that you want to get the property for.
pProp	The property to get.

Description:

pProp can be a built-in property, a column in the SQL Record's table or a custom property. The built-in properties are:

Property	Description
@table	The name of the table to associate the SQL Record with.
@database	The name of the database object the SQL record is associated with.
@connection	The connection object the SQL Record should use.

If pProp is not a built-in property or a column then it is assumed that it is a custom property that you have defined in the table behavior stack script (TABLE_NAME table behavior) for the SQL Record's table. The function that will be called will be TABLE_NAME_getPROP. For example, a custom property named short description for the lessons table would be defined as follows:

function lessons_getShortDescription pRecordA
  return char 1 to 35 of pRecordA["description"]
end lessons_getShortDescription

sqlrecord_getRelated

Type: command

Syntax: sqlrecord_getRelated <xRecordsA>,<pTable>

Summary: Retrieves records from a table related to the Record object's table.

Returns: Empty

Parameters:

Name	Description
pTable	The table whose related records should be retrieved.

Description:

All records in pTable related to the record stored in xRecordsA will be retrieved from the database and stored in xRecordsA[TABLE_NAME]. Note that xRecordsA can be a numerically indexed array of Record arrays if you want to retrieve related records for many records at once.

sqlrecord_link

Type: command

Syntax: sqlrecord_link <xRecord1A>,<xRecord2A>

Summary: Links two records represented by xRecord1A and xRecord2A together in the database.

Returns: Empty

Description:

This handler will link two records together if you have defined a relationship between the two tables. You just need to fill in the column keys for each record that are needed to uniquely identify them.

Examples:

put sqlrecord_createObject("lessons") into theLessonA
sqlrecord_set theLessonA, "id", 2

put sqlrecord_createObject("steps") into theStepA
sqlrecord_set theStepA, "id", 15

sqlrecord_link theLessonA, theStepA

sqlrecord_set

Type: command

Syntax: sqlrecord_set <xRecordA>,<pProp>,<pValue>,<pPassThru>

Summary: Sets a property of a SQL Record object.

Returns: Empty

Parameters:

Name	Description
pProp	The property to set.
pValue	The value to set the property to.
pPassThru	Set to true if SQL Yoga should not quote and escape the value you set. This is useful when setting the value of an expression or a value like CURRENT_TIMESTAMP. Default is false.

Description:

pProp can be either a built-in property, a column in the SQL Record's table or a custom property that you have defined in the Table Object for the SQL Record's table.

The built-in properties are:

Property	Description
@table	The name of the table to associate the SQL Record with.
@database	The name of the database object the SQL record is associated with.
@connection	The name of the database connection the SQL Record should use.
@passthru	A comma delimited list of fields that will not be escaped or quoted, but rather passed directly to the database as is.

If pProp is not a built-in property or a column then it is assumed that it is a custom property that you have defined in the table behavior stack script (TABLE_NAME table behavior) for the SQL Record's table. The function that will be called will be TABLE_NAME_setPROP. For example, a custom property named search terms for the lessons table would be defined as follows:

function lessons_setSearchTerms pRecordA, pValue
  ...
end lessons_setSearchTerms

sqlrecord_unlink

Type: command

Syntax: sqlrecord_unlink <xRecord1A>,<xRecord2A>

Summary: Unlinks the two records represented by xRecord1A and xRecord2A in the database.

Returns: Empty

Description:

This handler will unlink two records from each other if you have defined a relationship between the two tables. You just need to fill in the column keys for each record that are needed to uniquely identify them.

Examples:

put sqlrecord_createObject("lessons") into theLessonA
sqlrecord_set theLessonA, "id", 2

put sqlrecord_createObject("steps") into theStepA
sqlrecord_set theStepA, "id", 15

sqlrecord_unlink theLessonA, theStepA

sqlrecord_update

Type: command

Syntax: sqlrecord_update <xRecordsA>,<pRefreshRecords>

Summary: Updates records in a database using a SQL Record object.

Returns:
it: Number of affected rows
the result: Empty

Parameters:

Name	Description
pRefreshRecords	Pass in true to refresh the record objects with new data.

Description:

This command updates records in the database based on the SQL Record objects you pass in. Note that the primary key column will NOT be updated.

Update callbacks in your Table Object definitions will be called and broadcasts will be sent to callbacks during this operation.

Examples:

sqlrecord_find "lessons", theID, theRecordA
sqlrecord_set theRecordA, "title", "A new title"
sqlrecord_update theRecordA

sqlyoga__errorTypeFromError

Type: function

Syntax: sqlyoga__errorTypeFromError(<pConnectionA>,<pError>)

Summary: Determines whether an or is a connection or SQL related error and returns the appropriate error type.

Returns: kErrorSQLConnection or kErrorSQLExecution

Description:

When executing a query the error message may be related to a connection error rather than a query error. We want to throw the proper error type.

sqlyoga__throwError

Type: command

Syntax: sqlyoga__throwError <pErrNum>,<pMsg>

Summary: Converts a db cursor to a hierarchal array of Record objects based on relationships between tables.

Returns: Empty

sqlyoga_addToArray

Type: command

Syntax: sqlyoga_addToArray <xArrayA>,<pKey>,<pKeyValue>

Summary: Converts any number of key:value pairs into an array that is added to as a new element to another array.

Returns: empty

Parameters:

Name	Description
pKey	Key of xArrayA to store key/values in.
pKeyValue	Key/Value pairs in the format key:value. Each key value passed in will be added to the new key of pArray.

Description:

Helper command for populating arrays.

Examples:

put empty into tFieldsA
sqlyoga_addToArray tFieldsA, "name:id", "type:sequence"

sqlyoga_array

Type: function

Syntax: sqlyoga_array()

Summary: Converts the parameters to an array.

Returns: Array

Description:

Each parameter is a key:value pair.

Examples:

put sqlyoga_array("id:1", "label:My Record") into tDataA

sqlyoga_arrayPush

Type: command

Syntax: sqlyoga_arrayPush <xArrayA>,<pValue>

Summary: Adds a new element onto the end of an array.

Returns: empty

Parameters:

Name	Description
pValue	Variable to add to array.

Description:

Helper command for populating arrays.

Examples:

put "a value" into theFieldsA[1]
sqlyoga_arrayPush theFieldsA, "another value" -- added to theFieldsA[2]

sqlyoga_escapeString

Type: function

Syntax: sqlyoga_escapeString(<pDbType>,<pString>)

Summary: Escapses a string using the appropriate characters for the specified database type.

Returns: Escape string

Parameters:

Name	Description
pDbType	The type of database to escape for (mysql, postgresql, sqlite, valentina, sql server)
pString	The string to escape.

sqlyoga_getDatabases

Type: function

Syntax: sqlyoga_getDatabases()

Summary: Returns a return delimited list of all Database object names.

Returns: Line delimited list of database keys

sqlyoga_getDefaultDatabase

Type: function

Syntax: sqlyoga_getDefaultDatabase()

Summary: Returns the name of the Database object used by default in SQL Yoga API calls.

Returns: Name of default database key.

sqlyoga_lastInsertIDQuery

Type: function

Syntax: sqlyoga_lastInsertIDQuery(<pDbType>,<pConnectionA>)

Summary: Returns the query used to retrieve the id of the last record that was inserted into the database.

Returns: SQL query

sqlyoga_setDefaultDatabase

Type: command

Syntax: sqlyoga_setDefaultDatabase <pDBKey>

Summary: Sets the default Database object to use with the SQL Yoga API calls.

Returns: empty

Parameters:

Name	Description
pDBKey	The name of the Database object to set as the default.

Description:

By default the SQL Yoga API calls assume you are acting on the Database object named "default". You can use this command to specify a different Database object name to use as the default.

sqlyoga_splitUserSearchString

Type: function

Syntax: sqlyoga_splitUserSearchString(<pString>,<pDefaultOperator>)

Summary: Parses a search string and converts it into an array of it's component parts.

Returns: Numerically indexed array. Each index has two keys: operator and string.

Parameters:

Name	Description
pString	A string representing the terms a user wants to search for.
pDefaultOperator	Default boolean operator to use between words that do not explicity state one. Valid values are AND or OR. Default value is AND.

Description:

Search interfaces often allow a user to enter a search string for querying the database. A string entered by the user astacos pizza might look like this in a WHERE clause:

field = 'tacos' AND field='pizza'

or

field='tacos' OR field='pizza'

A search string such as "cheese pizza" tacos might look like this in a WHERE clause:

field = 'cheese pizza' OR field='tacos'

This function splits up the string into it's component parts and stores them in a numerically indexed array. This array can be used to generate conditions for a SQL Query object. For example, it can be passed as a parameter to sqlquery_addScope.

The string "cheese pizza" tacos would be parsed and converted to an array that looks like this (assuming OR was the default operator):

[1]["string"] = cheese pizza
[1]["operator"] = OR
[2]["string"] = tacos
[2]["operator"] = OR

sqlyoga_version

Type: function

Syntax: sqlyoga_version()

Summary: Returns the version of the SQLite library.

Returns: String

Description:

Returns a string in a format similar to "1.0.0 build 1".

tableobj_createObject

Type: command

Syntax: tableobj_createObject <pName>,<pType>,<pDBKey>

Summary: Creates a Table Object.

Returns:
it: Table Object reference array
the result: Empty

Parameters:

Name	Description
pName	The name of the Table Object to create.
pType	"table" or "alias". Default is "table".
pDBKey	Database Object table is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Creating a Table Object allows you to take advantage of a number of SQL Yoga features. SQL Yoga already knows about the tables and fields in your database and can generate queries for those tables. But when you define Table Objects you can take advantage of:

• Defining Relationships between tables, allowing SQL Yoga to generate hierachel query results.
• Defining Scopes, or predefined searches, which make dynamic searches a snap.
• Defining additional table properties i.e. a property that returns a condensed version of a field with lots of text.
• Defining callbacks when records are created, deleted, retrieved or updated.

In addition, a Table Object can be an "alias" to another table. An alias is a table object that points to a real table but has certain conditions that filter the content returned. For example, you may have an attributes table for foods. While attributes is a single table you may have a field called type that categorizes the various attributes. If you wanted to treat different types of attributes as their own tables you could creat ean alias to the attributes table. For example, you could create a an alias called region. The table would be the attributes table but the conditions would be:

type = "region"

Whenever you referenced the region table in a SQL Query Object or join SQL Yoga would know to target the attributes table and to add type="region" to the WHERE condition.

tableobj_deleteObject

Type: command

Syntax: tableobj_deleteObject <pTableA>,<pDBKey>

Summary: Deletes a Table Object.

Returns: Empty.

Parameters:

Name	Description
pDBKey	Database Object table is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

tableobj_get

Type: function

Syntax: tableobj_get(<pTableA>,<pProp>,<pDBKey>)

Summary: Retrieves properties of a Table Object.

Returns: value

Parameters:

Name	Description
pTableA	Name of the table to get a property for.
pProp	The property to get.
pDBKey	Database Object table is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
conditions	If a Table object is an alias to another table then this property returns any conditions that have been set. The conditions are used to filter the table content that the alias represents. The format is FIELD=VALUE,FIELD2=VALUE2,...
database	The name of the Database object the Table object is associated with.
fields	A return delimited list of the fields in the table.
fields array	The array containing all field information for the table. The keys are the names of the fields. Each field key has properties such as name, type, meta type, etc.
name	The name of the Table object.
primary key	A comma delimited list of field names that make up the primary key for the table.
relationships	A return delimited list of the names of relationships associated with the table.
scopes	A return delimited list of the names of the Table object's Scope objects.
table aliases	A return delimited list of names of Table objects that are aliases to the Table object.
type	The type of the Table object - table or alias.

tableobj_getScope

Type: function

Syntax: tableobj_getScope(<pTableA>,<pScopeName>)

Summary: Returns a Scope reference array.

Returns: Scope Reference array

Parameters:

Name	Description
pTableA	Table name or Table Reference array.
pScopeName	Name of scope.

tableobj_set

Type: command

Syntax: tableobj_set <pTableA>,<pProp>,<pValue>,<pDBKey>

Summary: Retrieves properties of a Table Object.

Returns: value

Parameters:

Name	Description
pTableA	Name of the table to get a property for.
pProp	The property to get.
pValue	The value.
pDBKey	Database Object table is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Property	Description
conditions
primary key
type

tableobjects_get

Type: function

Syntax: tableobjects_get(<pProp>)

Summary: Retrieves properties of the Table Objects in a Database Object

Returns: value

Parameters:

Name	Description
pProp	The property to get.

Description:

Property	Description
tables	An array of Table Object reference arrays.
relationships	An array of Relationship Object reference arrays.

tableobjects_getRelationship

Type: function

Syntax: tableobjects_getRelationship(<pIndexOrName>,<pDBKey>)

Summary: Returns a Relationship object reference array.

Returns: Relationship reference array.

Parameters:

Name	Description
pIndexOrName	The index of the relationship to retrieve. Alternatively you can pass in a name if you specified a name when creating the relationship.
pDBKey	Database Object to target. Default is name returned by sqlyoga_getDefaultDatabase().

tableobjects_getTable

Type: function

Syntax: tableobjects_getTable(<pTableName>,<pDBKey>)

Summary: Returns a Table Object reference array.

Returns: Table reference array. Empty if no Table Object exists.

Parameters:

Name	Description
pTableName	Name of the table to get the reference array for.
pDBKey	Database Object to target. Default is name returned by sqlyoga_getDefaultDatabase().

tableobjects_tableExists

Type: function

Syntax: tableobjects_tableExists(<pTableName>,<pDBKey>)

Summary: Determines whether a table object exists.

Returns: Boolean

Parameters:

Name	Description
pTableName	Name of the table to get the reference array for.
pDBKey	Database Object to target. Default is name returned by sqlyoga_getDefaultDatabase().

tblrelation_createObject

Type: command

Syntax: tblrelation_createObject <pName>,<pDBKey>

Summary: Creates a Relationship Object.

Returns:
it: Relationship Reference array
the result: Error message

Parameters:

Name	Description
pName	A name for the relationship.
pDBKey	Database Object to associate relationship with.

Description:

Internally relationships are stored used numerical indexes but if you would like to provide a name for lookup purposes you may. If you specify a name it is up to you to ensure that names are unique.

tblrelation_deleteObject

Type: command

Syntax: tblrelation_deleteObject <pRelationA>

Summary: Deletes a relationship object.

Returns: empty

Parameters:

Name	Description
pRelationA	Relationship Reference array or relationship name.

tblrelation_get

Type: function

Syntax: tblrelation_get(<pRelationA>,<pProp>,<pDBKey>)

Summary: Retrieves a property of a Relationship Object.

Returns: value

Parameters:

Name	Description
pRelationA	Relationship name.
pProp	Property to retrieve.
pDBKey	Database Object relationship is associated with. Default is name returned by sqlyoga_getDefaultDatabase(). Only applicable if pRelationA is not a reference array.

Description:

Property	Description
connection
cross-reference table	The name of the table that connects the left and right tables in a n-n (many-to-many) relationship.
cross-reference table key for left table	A comma delimited list of field(s) used as the key that connects the cross-reference table to the left table. You can pass in a hard coded value using the format COL=VALUE, escaping and quoting VALUES as needed.
cross-reference table key for left table without values	Returns the keys used for to link the cross-reference table to the left table with any hard coded column values stripped.
cross-reference table key for right table	A comma delimited list of field(s) used as the key that connects the cross-reference table to the right table. You can pass in a hard coded value using the format COL=VALUE, escaping and quoting VALUES as needed.
cross-reference table key for right table without values	Returns the keys used for to link the cross-reference table to the right table with any hard coded column values stripped.
database	The name of the database the Relationship object is associated with.
left table key	A comma delimited list of field(s) used as the key in the left table.
left table key	A comma delimited list of field(s) used as the key in the left table.
left table key without values	Returns the keys used for the left table with any hard coded column values stripped.
right table key without values	Returns the keys used for the right table with any hard coded column values stripped.
name	The name of the Relationship object.
order by	The column to sort the records in the relationship by when they are retrieved from the database.
owner
right table	The name of the right table in the relationship. This can also be thought of as the child table.
right table key	A comma delimited list of field(s) used as the key in the right table.
type	Type of relationship. n-n (many-to-many), 1-n (one-to-many) or 1-1 (one-to-one).

tblrelation_set

Type: command

Syntax: tblrelation_set <pRelationA>,<pProp>,<pValue>,<pDBKey>

Summary: Sets a property of a Relationship Object.

Returns: empty

Parameters:

Name	Description
pRelationA	Relationship Reference array or relationship name.
pProp	Property to retrieve.
pValue	Value to set property to.
pDBKey	Database Object relationship is associated with. Default is name returned by sqlyoga_getDefaultDatabase(). Only applicable if pRelationA is not a reference array.

Description:

Property	Description
cross-reference table	The name of the table that connects the left and right tables in a n-n (many-to-many) relationship.
cross-reference table key for left table	A comma delimited list of field(s) used as the key that connects the cross-reference table to the left table. You can pass in a hard coded value using the format COL=VALUE, escaping and quoting VALUES as needed.
cross-reference table key for right table	A comma delimited list of field(s) used as the key that connects the cross-reference table to the right table. You can pass in a hard coded value using the format COL=VALUE, escaping and quoting VALUES as needed.
left table	The name of the left table in the relationship. This can also be thought of as the parent table.
left table key	A comma delimited list of field(s) used as the key in the left table.
name	The name of the Relationship object.
order by	The column to sort the records in the relationship by when they are retrieved from the database.
owner
right table	The name of the right table in the relationship. This can also be thought of as the child table.
right table key	A comma delimited list of field(s) used as the key in the right table.
type	Type of relationship. 1-n (one-to-many), 1-1 (one-to-one) or n-n (many-to-many).

tblscope_createObject

Type: command

Syntax: tblscope_createObject <pTableA>,<pName>,<pDBKey>

Summary: Creates a Scope Object and attaches it to a Table Object.

Returns:
it: Scope reference array
the result: Error message

Parameters:

Name	Description
pTableA	The name of the table to attach the scope to.
pName	The name of the scope.
pDBKey	Database Object connection is associated with. Default is name returned by sqlyoga_getDefaultDatabase().

Description:

Create a Scope Object to help you generate searches dynamically.

tblscope_deleteObject

Type: command

Syntax: tblscope_deleteObject <pScopeA>

Summary: Deletes a Scope Object from a Table Object.

Returns: Empty.

Parameters:

Name	Description
pScopeA	Scope reference array. See tableobj_getScope().

tblscope_get

Type: function

Syntax: tblscope_get(<pScopeA>,<pProp>)

Summary: Retrieves properties of a Scope Object.

Returns: value

Parameters:

Name	Description
pScopeA	Scope reference array. See tableobj_getScope().
pProp	The property to get.

Description:

Property	Description
conditions	Search conditions for the Scope (as set using tableobj_set).
database	Database that Scope is associated with.
order by	ORDER BY fields.
sql conditions	The conditions as they will appear in the SQL statement. Any english operators used when setting the conditions will have been translated into SQL.
table	Table that Scope is associated with.
[related table] joins	If you haven't defined relationships between tables then this is the string you set the joins to. If you have defined relationships then you will get the internal array that represents the join.

tblscope_set

Type: command

Syntax: tblscope_set <pScopeA>,<pProp>,<pValue>

Summary: Sets properties of a Scope Object.

Returns: empty

Parameters:

Name	Description
pScopeA	Scope reference array. See tableobj_getScope().
pProp	The property to get.
pValue	Value to set property to.

Description:

Property	Description
conditions	Search conditions for the Scope. Use binding variables in order to set the value when adding the scope to a SQL Query Object.
[related table] joins	The related tables to that the scope requries in addition to the scopes table. You can simply pass in a string like LEFT JOIN related_table_name. The string will be parsed and the ON portion of the JOIN will be included.
order by	ORDER BY fields.