The simplest is:

array ( 'data_pool_id' => 3 )

This means set the select, insert, update and delete data pool arrays to a data pool ID of 3.

When 'data_pool_id' is set, this is shorthand for setting the select, insert, update and delete data pool arrays so you don't have to re-specify the same data pools for each data pool array type.

What if you had more than one data pool?

array ( 'data_pool_id' => array ( 3, 5, 8 ))

This would set the select, insert, update and delete data pool arrays equal to { 3, 5, 8 }.

What if one of the data pools is supposed to be read-only?

array (
	'data_pool_id' => array ( 3, 5 ),
	'select' => 8 )

This sets the select, insert, update and delete data pool arrays equal to { 3, 5 }.

An additional element is added to the select array for data pool ID 8. Notice that 8 wasn't referenced in 'data_pool_id' which makes it read-only.

A nonsense example

array (
	'data_pool_id' => array ( 3, 5 ),
	'select' => 8,
	'insert' => array ( 11, 15 ),
	'update' => array ( 1, 9 ),
	'delete' => 4 )

This sets the select data pool array to { 3, 5, 8 }, the insert data pool array to { 3, 5, 11, 15 }, the update data pool array to { 3, 5, 1, 9 }, and the delete data pool array to { 3, 5, 4 }.

Method Categories

• CONFIGURATION METHODS
• LINKED TABLE METHODS
• ROW POINTER METHODS
• META-DATA METHODS
• WORKAREA METHODS
• DATA SELECT/CHANGE METHODS
• POWER METHODS
• PRIVATE METHODS

Magic Methods

• __construct
• __destruct
• __sleep
• __wakeup

Private Methods

• _cast_type
• _delete_allowed
• _delim
• _empty_array
• _execute_query_and_populate_buffer
• _format_value
• _restore_row_state
• _set
• _settable
• _store_row_state
• _update_allowed
• backup_selected

C

• clear
• clear_all_rows
• clear_row

D, F

• delete
• delete_row
• delete_selected
• first_row

G

• generate_form
• get
• get_all_pk_values
• get_all_rows
• get_attr
• get_clear_row
• get_column
• get_column_values
• get_data_pool
• get_db
• get_default
• get_last_sql
• get_maxlength
• get_max
• get_min
• get_pk
• get_pk_values
• get_record_count
• get_row
• get_row_count
• get_row_index
• get_schema
• get_stmt
• get_struct
• get_sum
• get_table
• get_type

I

• init_data_pool
• init_table
• insert
• insert_or_update
• insert_row

L

• last_row
• link_detail_table
• link_master_table
• load

N, O, P, Q

• next_row
• offset_row
• prev_row
• qbe

R

• raw_sql
• recurse
• recurse_pk
• refresh_detail_tables
• revert_rows

S

• select
• select_all_rows
• select_row
• set
• set_attr
• set_insert_data_pool
• set_row
• set_row_index
• set_seq
• subquery

T, U

• toggle_row
• unselect_all_rows
• unselect_row
• update

Creates PDO object and sets attributes.

Void

Notes: The PDO attribute(s) set are PDO:ATTR_CASE=>PDO::CASE_LOWER so all the column names are all lower-cased. This makes it easier for the class to make all column references case-insensitive for those methods that refer to columns. You can further define the PDO object used with get_db.

Argument list:

• data_pool_config (array)
  • See init_data_pool
• db_connect (object)
  • By default, a new PDO object is created for each active_record object.
  • If you want to share a database connection between multiple active_record objects, you can set db_connect to a PDO or active_record object.
• db_cred (array, required)
  • db_database (string)
    • The database to use on the specified server
  • db_driver (string)
    • MySQL
      • native:mysql
    • SQL Server
    • odbc:sqlserver
    • odbc:sqlserver:SQL Server
      • The 3rd sub-parameter "SQL Server" is unnecessary as it defaults to "SQL Server", but if the name of your ODBC driver is not "SQL Server", specify its name here.
  • db_port (int)
    • MySQL
      • Defaults to 3306
    • SQL Server
      • Defaults to 1433
  • db_protocol (string)
    • SQL Server
      • Defaults to 'tcpip'
  • db_pw (string)
    • The database password to use
  • db_schema (string)
    • MySQL
      • Sets schema to use (init_table will need a schema if you don't specify this here)
    • SQL Server
      • Defaults to 'dbo'
  • db_server (string)
    • The database server to use:
      • 127.0.0.1
      • localhost
      • mysql.mydomain.com
      • sqlserver.mydomain.com
      • DEV-DB
      • PROD
  • db_un (string)
    • The database username to use
• attr (array)
  • This is a series of attributes that could be set via set_attr, but may be more conveniently set here. Some examples of attributes you might want to set this way would be attributes that are always the same, but always different from their default values (like sequence_table and sequence_function).

Destroys PDO object (unless borrowing a connection via constructor's db_connect argument)

Void

Serializes everything except the PDO object

Void

Re-creates PDO object (unless borrowing a connection via constructor's db_connect argument)

Void

Argument list (specify one or the other):

• org_cust_id
  • If this is 0, then read/write access to all data pools (as defined in account.data_pool) is granted.
    • select data_pool_id, 0 as read_only from account.data_pool
  • If this is a specific org_cust_id, then only the specific access granted to data pools for that org_cust_id is granted.
    • select data_pool_id, read_only from account.org_cust_data_pool where org_cust_id={args['org_cust_id']}
      
• data_pool_id
  • Sending a specific data pool id grants read/write access to that data pool.

Imports table structure, PK and sequences. Calls clear_all_rows.

If this table uses data pools and a sequence definition exists for data pool 0 but no sequence for this data pool exists, one is automatically created. This check occurs for every sequenced column in the sequence definition for data pool 0.

Argument list:

• schema (string)
  • This is not necessary if it's defaulted in the constructor (either by specifying it as an argument via db_schema or if it's defaulted automatically - as is the case with SQL Server, although you can still specify it to override the default).
• table (string, required)
  • The name of the table
• data_pool_config (array)
  • See init_data_pool

Sets the specified attribute with the specified value

• accumulate_results (boolean=false)
  • This causes every query's results to accumulate in the workarea, instead of replacing the prior contents.
• add_oper_column (string='addoper')
  • If a table contains a column matching this name, then anytime a record is inserted, the value of the this column will be $_SESSION['username'] if it exists, else 'SYSTEM'.
• add_date_column (string='adddate')
  • If a table contains a column matching this name, then anytime a record is inserted, the value of this column will be the server's date/time.
• auto_trim (boolean=false)
  • Applies to the set method and more specifically, executes at the _set method level.
  • If true, trims string values of excess preceding and suffixing spaces.
  • If true, executes prior to auto_trunc attribute.
• auto_trunc (boolean=false)
  • Applies to the set method and more specifically, executes at the _set method level.
  • If true, strings that are longer than the table can support are automatically truncated.
• concurrency_column (string='concurrency')
  • If your table has concurrency control, this indicates which column is in charge of maintaining that concurrency. This column must be numeric, preferably an integer.
• data_pool_id_column (string='data_pool_id')
  • If your table is segmented into data pools, this indicates which column is in charge of that segmentation.
• lazy_get (boolean)
  • This prevents an error from being thrown when reading columns that do not exist.
• load_defaults (boolean=false)
  • If true, the defaults specified by the table's metadata are automatically loaded whenever the following methods are called:
    • clear
    • clear_row
    • clear_all_rows
    • insert_row
• mod_oper_column (string='modoper')
  • If a table contains a column matching this name, then anytime a record is updated, it updates the value of this column to $_SESSION['username'] if it exists, else 'SYSTEM'.
• mod_date_column (string='moddate')
  • If a table contains a column matching this name, then anytime a record is updated, it updates the value of this column to the server's date/time.
• qbe_additional_criteria (array)
  • In cases where more than one qbe method is called and some criteria elements are always the same, this can reduce the size of the criteria specified in each qbe. This criteria automatically merges with every criteria specified in the qbe method. In cases where criteria column references match in the qbe_additional_criteria and qbe call, the criteria in the qbe will overwrite criteria in the qbe_additional_criteria. This allows for maximum readability and flexibility.
• read_only (array=array('get_attr:data_pool_id','get_attr:concurrency_column'))
  • The 'get_attr:' prefix indicates that every time get_attr is called for 'read_only' it will execute get_attr on the attributes that follow. In this way, you can reference other attributes within an attribute. Only the 'read_only' attribute supports this feature.
• select_suffix (string)
  • The intent of this attribute was to easily add "order by" clauses.

By default, the lowest value data pool ID in the "insert" data pool array is used for any insert operation.

If a different data pool ID is desired for inserts, use this method to set it.

Links another active_record object (detail) to this one (master).

If column_map is a string, then it is assumed we are only mapping a single column which is the same name in both tables.

If column_map is an array, it can consist of the following configurations per element:

• column name
  • This implies that both master and detail tables have the referenced column name.
• detail_column_name => master_column_name
  • These refer to different column names in the master and detail tables that must value match. These can of course be the same name, but it's more convenient to just specify the colum name once as described in the preceding bulletpoint.
• detail_column_name => /string_value/
  • This is useful if you want to match a column in the detail table for a particular string value. A forward-slash prefix and suffix are necessary to differentiate this from a column name.
• detail_column_name => value
  • If "value" is anything other than a string, then it is assumed to be a value you're attempting to match against.

Links another active_record object (master) to this one (detail). This is essentially the same thing as link_detail_table from the master table perspective.

If column_map is a string, then it is assumed we are only mapping a single column which is the same name in both tables.

If column_map is an array, it can consist of the following configurations per element:

• column name
  • This implies that both master and detail tables have the referenced column name.
• master_column_name => detail_column_name
  • NOTE: This column map option is the only difference in perspective from link_detail_table
  • These refer to different column names in the master and detail tables that must value match. These can of course be the same name, but it's more convenient to just specify the colum name once as described in the preceding bulletpoint.
• detail_column_name => /string_value/
  • NOTE: The reason this isn't from a master column perspective is because it's always the detail table that feeds off the master table, not the other way around.
  • This is useful if you want to match a column in the detail table for a particular string value. A forward-slash prefix and suffix are necessary to differentiate this from a column name.
• detail_column_name => value
  • NOTE: The reason this isn't from a master column perspective is because it's always the detail table that feeds off the master table, not the other way around.
  • If "value" is anything other than a string, then it is assumed to be a value you're attempting to match against.

Re-queries all linked detail tables based on data in the current row of the workarea.

This method is called from the following methods:

• init_data_pool
• link_detail_table (link_master_table calls link_detail_table)
• set_row_index
• first_row
• last_row
• prev_row
• next_row
• offset_row
• delete_row
• _execute_query_and_populate_buffers (which is called from select and qbe)

Each data pool array is modeled after various SQL command (like select, insert, etc.). One or more integers may be present in each array which indicate which data pools may be affected by the associated SQL command.

• select
• update
• delete
• insert

The data pool(s) associated with this active_record object, else false if there is no data pool column in this object's specified table.

If a specific data pool type is requested then an integer array is returned, else an array of integer arrays.

e.g. get_struct('email','character_maximum_length');

A normalized type is the mapping of a specific data type to a more general type. This allows for simpler, cross-database coding where data type has an effect and also makes it easier for 'apples-to-apples' comparisons between different databases.

The following shows the normalized data type followed by specific data types that are mapped to it. If the database already supports the normalized type as a specific type, the mapping is implied.

If column is null, returns all type information for all columns in an array of arrays. If column is specified, returns all type information for the specified column in an array, else if attribute is specified, only return the specified type attribute for the specified column.

• data_type
  • This is the native data type for the database
• normalized_type
  • This is a normalized version of the data type
• param_type
  • This is the type used in the bindvalue method.
    • Default is PDO::PARAM_STR
    • normalized_type ( int, real, decimal ) yields PDO::PARAM_INT
    • normalized_type ( boolean ) yields PDO::PARAM_INT (MySQL), PDO::PARAM_BOOL (SQL Server)

Clears the column at the specified row

Void

Replaces the workarea with a single, cleared row

Void

Clears the row specified

Void

The value of the specified column at the specified row

An array of the primary key values for all records

The entire workarea array

An array of all values for the specified column

Largest number in workarea for the specified column

Smallest number in workarea for the specified column

An array of the primary key values for the specified row

The workarea array at the specified row

Reverts the workarea to the state it was in after the last query. This could be helpful if changes were made to the workarea that were no longer desired (without having to re-query the information).

The "accumulate_results" attribute must be false for this to work (which it is by default).

The column is set if the following conditions are true.

1. It is not a read-only column that was just read from the database.
2. The column exists in the table

True if the column was set, else false

This method is special in that it refers to the general.get_seq custom database function to get the next sequence. Once that is retrieved, it optionally formats the value per general.seq.format string via sprintf formatting rules. This is useful (for example), to zero-pad the string automatically and consistently.

general.get_seq custom database function snippet:

update	general.seq
set	next_value=@retval:=next_value+1
where	data_pool_id=nDataPoolID
and	schema_name=tSchema
and	table_name=tTable
and	column_name=tColumn;

return @retval-1;

As you can see, while the update statement increments the value, it also sets the return value to that new value – all within the same SQL statement. It is a trivial thing to subtract one from this value – which now represents what the next value “used” to be. This seems to be a very clean way to guarantee that no 2 processes get the same “next value”.

Important: Keep in mind that you shouldn’t normally need to use the set_seq method. This is because the insert method automatically calls the set_seq method on sequenced columns that are null. If however, you plan on doing a lot of inserts – it may be more efficient (and clear) to call set_seq rather than nulling the column(s) or clearing the entire workarea with clear_all_rows.

The sequence value set to the specified column or a false if the column wasn’t settable (see set rules) or the column is not specified as a sequenced column

Inserts a new record with the information specified by the specified row in the workarea.

If a data pool column exists and is null, it will be populated with the assigned insert data pool id.

If the add operator column exists, it will be populated with the $_SESSION['username'] value if it exists, else 'SYSTEM'.

If the add date column exists, it will be populated with the server's date/time.

If the concurrency column exists, it will be populated with a 0 value.

Any sequenced columns that are null will be populated with the next sequenced value.

True on successful insert, else false

Since failure can only be determined when the SQL is executed, the workarea is saved prior to any content change (data pool, sequenced columns, add date, etc.) and restored on failure.

The caveat is even though the row is restored to its original contents prior to the call, sequences that would have been committed to the database on success are effectively lost.

If data pools are being used in this table, a check is made to see if the data pool represented in the row is a data pool we have rights to delete from.

The intended use of this method is to read the record in first, then issue a delete on it.

This method does not respect concurrency. If the concurrency read is different from the concurrency in the table, the record is deleted.

The basic criteria format is a column/value pair for equality comparisons.

qbe ( array (	
	'city' => 'Chicago',
	'state' => 'IL' ));

If a different operator is desired, the format is similar:

qbe ( array (
	'customer_id' => 2993,
	'amount' >= array ( 'operator' => '>', 'value' => 100 ),
	'order_date' => array ( 'operator' => '<', 'value' => new DateTime()),
	'status' => array ( 'operator' => '<>', 'value' => 'COMPLETED' ))) 

The order of inspection on the value:

1. If the value is a null, then substitute "is not" if the operator is '<>' -- else substitute "is".
2. If the value is an array, then wrap the deeper comparison in parenthesis and chain the deeper comparison(s) together with "or".
3. If the value has a % sign at the beginning and/or the end, then substitute "not like" if the operator is '<>' -- else substitute "like".

Useful for shooting a quick SQL statement through the existing PDO connection.

This bypasses most of the active_record functionality including the workarea. Use the return value or get_stmt method to fetch data. Works with get_last_sql method.

Selects data from the current table based on primary key values sent.

This method expects you to send the primary key values in their respective ordinal position, but does not expect more than one primary key value.

This method will interpret the % sign as a "like" operation if the value is prefixed and/or suffixed with it.

Updates an existing record with the information specified by the specified row in the workarea.

There must be at least one column in the workarea for the specified row that has changed. This might be a bad idea if you're checking the return code (false) and expecting that maybe the table was updated through an alternate means -- however, it's nice from the perspective of not performing an unnecessary update.

This method can handle a change in primary key values.

If the modified operator column exists, it will be populated with the $_SESSION['username'] value if it exists, else 'SYSTEM'.

If the modified date column exists, it will be populated with the server's date/time.

If the concurrency column exists, it will be populated with a 0 value.

True on successful update, else false

Since failure can only be determined when the SQL is executed, the workarea is saved prior to any content change (modified operator/date, concurrency, etc.) and restored on failure.

Arguments list:

• action (string)
  • The action attribute of the form tag.
  • If omitted and the current page ends with ".php" then it will be modified to include "_post" prior to the ".php" -- else the current page is used.
• attributes (array)
  • column_name (array)
    • This is an array of key/values where the key is the name of the attribute and the value is the value of it. Applies only to textarea fields which appear with normalized types of "text".
• current_row (int)
  • Defaults to 0.
• form_dependence_mode (boolean)
  • If true, then this is part of a larger form, else form tags will be generated.
• hidden_fields (array)
  • An array of column names that are not to be shown
• hidden_values (array)
  • A set of key/values that populate hidden fields
• html (array)
• labels (array)
  • column_name (array)
    • A set of key/value pairs where key=column_name and value="Label"
    • If a column is not represented here, the default representation is the english interpretation of the column.
• max_size (int)
  • Renders the size attribute of input tags of type text and password to a maximum size. If maximum size of the table column is smaller than this value, then the maximum size of the table column is used.
• mode (string)
  • "insert"
    • Form is for the purpose of creating a new record, so no fields are pre-populated with any data from an existing record in the workarea
  • "update"
    • Assumes a record update form so fields are pre-populated with existing data from a record in the workarea
  • "read_only"
    • This is a way to leverage the form generator method in a way that produces no form fields, just yields the values from an existing record in the workarea.
• multi_row
  • extra_rows
    • Adds extra rows (rows for inserting new data rows) to the end of the form
  • max_rows
    • Limits maximum number of rows - will enforce maximum row limit even if number of selected rows is greater - this could also result in 0 rows for inserting new data
  • max_rows_soft
    • Limits maximum number of rows, but if over maximum, still allows all selected rows + 1 insert row -- so this is not a true limit in that sense
  • min_rows
    • Ensures no matter how few selected rows, there will be a minimum number of rows - yields to max_rows
  • links
• omitted_fields
• read_only_fields
• redirect
  • success
• failure
• submit (string)
  • Determines text of submit button
  • If omitted, defaults to "Create Table Name" or "Update Table Name" (depending on the value of the mode argument) where "Table Name" is the english conversion of the actual table name.
• table_dependence_mode (boolean)
  • If true, then this is part of a larger table, else table tags will be generated.
• values
  • column_name

This is the gateway of data type conversion from the database to PHP. To simplify things, the normalized type is used to determine the conversion as follows:

• real
  • Cast to a PHP float
• decimal
  • Cast to a PHP float
• int
  • Cast to a PHP int
• datetime
  • If already a PHP DateTime object, then nothing is changed
  • If a string:
    • If the value equals 'getdate()' then creates a new DateTime object (the current datetime)
    • else, create DateTime object based on value sent
• boolean
  • If value equals 1, then set to True
  • Else, set to False
• all others
  • The value sent "as is"

A conversion of the value sent to the appropriate PHP type

See also: _format_value

The boolean value sent unless the table has a data pool column -- if a data pool column exists for table and the data pool configuration for that data pool does not allow deletes, then returns false, else returns the boolean value sent.

Used by: delete

MySQL uses an accent (`) for both left and right delimiters where SQL Server uses left and right brackets for delimiters.

Delimiters are used to wrap table and column names to ensure they do not get confused with any of the database's keywords.

The value sent wrapped by delimiters used by the database used

An array keyed by table column names populated by the value sent

Used by: get_clear_row, clear_row, clear_all_rows, insert_row, _execute_query_and_populate_buffers

This method deals with everything that needs to happen after a data selection query has been built, but not yet sent.

• accumulate_results attribute
• workarea array
• control array
• original primary key array
• counts (record count and total rows)
• refreshing detail tables

True if no errors and at least one record was returned, else False

Used by: select, qbe

This method converts a PHP type/value to an equivalent type/value the database would expect based on the normalized type:

• datetime
  • If DateTime object, then format as string via DateTime format method 'Y-m-d H:i:s'
• boolean
  • If True, then format as 1 (integer), else 0 (integer)
• all others
  • The value sent "as is"

The PHP value converted for the database

See also: _cast_type

Void

Used by: insert, set, set_seq, update

Determines set-ability based on the following criteria:

• Read-only columns are not settable if they have been selected from the database (control status='R').
• Columns that do not exist are not settable.

True if column is settable on that row, else false

Used by: set, set_seq

This is used by the insert method as a way to store the row's state prior to auto-setting various special column values (data pool, sequenced columns, date added, etc.) which occurs prior to attempting to perform the actual insert. If the insert fails for any reason, the insert method uses _restore_row_state with the return value of this method.

This method and its sister method (_restore_row_state) may be used by other methods for similar reasons.

An array holding the workarea and control information for the specified row.

• work
• control

The boolean value sent unless the table has a data pool column -- if a data pool column exists for table and the data pool configuration for that data pool does not allow updates, then returns false, else returns the boolean value sent.

Used by: update