PostgreSqlAdvanced

PostgreSQL advanced class. To create instances of "IDbAbstract" for connecting and accessing PostgreSQL databases. Plus implementing the advanced database access methods of "IDbAdvanced" such as creating/altering tables and getting meta data.
The classes that implement the "IDbAdvanced" interface create a uniform cross DBMS meta-data access and table create/alter platform to let the Artaengine data modeling system have a robust control over the database.
It is possible to conver an "IDbAbstract" instance to "IDbAdvanced":
$advancedDbObj = Database::upgrade($abstractDbObj);
Or to create an "IDbAdvanced" with the same configs as an existing "IDbAbstract" instance:
$advancedDbObj = PostgreSqlAdvanced($abstractDbObj);
This class is not included in the autoload as it is recommended to create a database connection and grab a database access instance from the "arta\Database" object factory.
Source
PostgreSqlAdvanced.php
Implements
IDbAdvanced
Depends on
DatabaseError
Extends
PostgreSqlAbstract

Constants

DBMS = 'postgresql'

Database system name

ENGINE = 'PostgreSQL'

PHP extension name

MAX_IDENTIFIER_NAME = 63

Max number of chars allowed for identifier names by this database system

Properties

__activeQue__ = 'default'

$obj->__activeQue__ = 'default'

Identifies the active queue index. The queue related methods (queue add, commit, etc. SQLs) will perform their action on the active queue.

__que__ = array

$obj->__que__ = array()

Holds SQL queues and their SQLs, SQLs inside each queue can to executed in a transaction or at once. Thought this property is for managed by the methods related to queues, it is made public to be hackable.

pg

$obj->pg

PostgreSQL connection resource

nameConstraint = true

$obj->nameConstraint = true

Put meaningful consraint names

sortMeta = true

$obj->sortMeta = true

true=sort the output of "metaTable()" and "metaView()" by dependency

Methods

__construct

PostgreSqlAdvanced $obj = new PostgreSqlAdvanced(IDbAbstract | array param)

Connect to a database. Either connection configs or an "DBIAbstract" instance must be passed. If an instance of "DBIAbstract" is passed a new database connection will not be made but the old connection related to the instance will be set for the new "DBIAdvanced" object.

Arguments

    param (IDbAbstract | array)
    An instance of "IDbAbstract" or a connection configs array
    {
        string server   : localhost database server address,
        string port     : 5432 database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced : false
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    }
    

Returns

void

Throws

__get

mixed __get(string col)

Get data of a column. You can get the data immediately after a query, if no row has been fetched yet, this will fetch a row to get the column data.

Arguments

    col (string)
    Column name

Returns

mixed
Data

__tostring

string $obj->__tostring()

Get info about the connection and database engine.

Returns

string
Info about the connection and database engine

add

IDbAbstract $obj->add(string sql, array | [mixed] params=null)

Add an SQL to the active queue.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

arrayWhere

string $obj->arrayWhere(array array, string op, string prefix=null)

Creates an SQL condition from a map. This method is used by the Model class. Turns {column1: value1, column2: value2} into column1=value1 AND column2=value2

Arguments

    array (array)
    {column1: value1,}
    op (string)
    Operator to be used between each part
    prefix=null (string)
    Prefix to be added to each column name

Returns

string
SQL condition to be used in WHERE

begin

IDbAbstract $obj->begin(string q='default')

Create a new SQL queue and set it as the active queue. You can have one or more queues, you can make a queue active by method "toggle()" and add SQL statements to the active queue using the ("add()", "update(), insert()" and "delete()") methods and finally rollback or commit a queue as a database transaction or one by one. If you do not need more than one queues you do not have to use this method and toggle, simply use the action methods and when it's call "commit()" or "rollback()".

Arguments

    q='default' (string)
    Queue ID. You can use this ID to address a specific queue, if you never created new queues then there is only one queue named "default"

Returns

IDbAbstract
For method chaining

connection

array $obj->connection()

Get connection configs.

Returns

array
Connection configs.
{
    string server   : localhost database server address,
    string port     : depends on the engine database server port,
    string dbname   : database name,
    string user     : database user-name,
    string password : database password,
    string advanced : false
        false=create an IDbAbstract instance
        true=create an IDbAdvanced instance,
}

delete

IDbAbstract $obj->delete(
    string table,
    string where=null,
    array | [mixed] params=null
)

Add an DELETE statement to the active SQL queue.

Arguments

    table (string)
    Table name
    where=null (string)
    SQL condition string (may contain params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided in the next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    An array can be passes instead of a string:
    {column: value,} e.g.
    {age: 24, id: [1, 2, 10,],} means "age = 24 AND id IN (1, 2, 10)"
    
    params=null (array | [mixed])
    Param values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

insert

IDbAbstract $obj->insert(string table, array newVals, array newValsX=null)

Add an INSERT statement to the active SQL queue.

Arguments

    table (string)
    Table name
    newVals (array)
    Array of columns and their values. Values will be SQL escaped. To not escape a value put an ! at the beginning of the column name. {col-name: val-do-escaped, !col-name: val-do-not-escape,}
    newValsX=null (array)
    Array of columns and their values. Values will NOT be SQL escaped. An alternative way to adding ! to the col-name in newVals {col: val-do-not-escape,}

Returns

IDbAbstract
For method chaining

inspect

void|string|array $obj->inspect(bool printI=true)

Inspect/debug the query and the transaction queue.

Arguments

    printI=true (bool)
    true=print the debug info and die, false=return the debug info

Returns

void|string|array
Debug info or nothing based on arguments.

page

array $obj->page(string sql, array | [mixed] params=null, array configs)

To query a page of data. A page is a subset of the result consisting of a max_number or rows, each page is addressed by a number starting from 1.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    
    configs (array)
    Page configs.
    {
        int  page     :
            Page index to query,
        bool exec     : true
            true=execute the query
            false=return the query SQL string,
        int  max_rows : 30
            Max number of rows per page,
        int  count    : null
            Query's total row count (for all pages). If this value is not
            provided it will be calculated (by executing a second count query).
            To avoid this extra query provide this value when possible,
    }
    

Returns

array
This array can be fed to an instance of "Paging" to get the paging links
{
    string sqlstr   :
        The SQL query to get that page of data,
    int    page     :
        Page index which was queried,
    bool   exec     :
        true=the query was executed successfully
        false=the query was not executed,
    int    max_rows :
        Max number of rows per page,
    int    count    :
        Query's total row count (for all pages). To avoid extra count
        queries when querying next pages, try to keep this value and
        reuse it if possible.
}

Throws

que

array|string $obj->que(string | false q=null, int i=null)

Get queue(s) (SQL list(s)).

Arguments

    q=null (string | false)
    Queue ID to be returned, false= return all queues, null=return the active queue
    i=null (int)
    Return only the i'th SQL of the queue

Returns

array|string
An SQL string or a list of SQL strings

rollback

IDbAbstract $obj->rollback(string q=null)

Rollback, cancel and remove a queue.

Arguments

    q=null (string)
    Queue ID to be canceled, null=cancel the active queue

Returns

IDbAbstract
For method chaining

toggle

IDbAbstract $obj->toggle(string q='default')

Toggle queues (activate a queue).

Arguments

    q='default' (string)
    Queue ID to become active

Returns

IDbAbstract
For method chaining

transaction

IDbAbstract $obj->transaction(string sql, array | [mixed] params=null)

Add an SQL to the active queue. Alias for "add()".

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

update

IDbAbstract $obj->update(
    string table,
    array newVals,
    string where=null,
    array | [mixed] params=null
)

Add an UPDATE statement to the active SQL queue.

Arguments

    table (string)
    Table name
    newVals (array)
    Array of columns and their update values. Values will be SQL escaped. To not escape a value put an ! at the beginning of the column name. {col-name: val-do-escaped, !col-name: val-do-not-escape,}
    where=null (string)
    SQL condition string (may contain params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided in the next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    An array can be passes instead of a string:
    {column: value,} e.g.
    {age: 24, id: [1, 2, 10,],} means "age = 24 AND id IN (1, 2, 10)"
    
    params=null (array | [mixed])
    Param values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

affected

int $obj->affected()

Affected row count.

Returns

int
Row count

castTime

string $obj->castTime(string val, string type=TIMESTAMP, bool isCol=false)

Get cast date/time sentence.

Arguments

    val (string)
    A date/time string or a table column name
    type=TIMESTAMP (string)
    Cast: TIMESTAMP, DATETIME, TIME or DATE
    isCol=false (bool)
    Is "val" a string or a column name

Returns

string
Cast sentence

close

void $obj->close()

Close database connection.

Returns

void

commit

bool $obj->commit(bool transaction=true, string q=null)

Commit the active SQL queue. A queue ID can be passed explicitly to commit a specific queue.

Arguments

    transaction=true (bool)
    true=commit the queue SQLs in a database transaction, false=run queue SQLs one by one and not in a database transaction
    q=null (string)
    Queue ID. null=commit the active queue. Provide this argument to commit a specific queue by it's ID

Returns

bool
State of success

Throws

concat

string $obj->concat(array strs)

Make a database concat sentence out of the passed values.

Arguments

    strs (array)
    Strings to be concated

Returns

string
Concated values.

connect

void $obj->connect(array configs=array())

Connect or reconnect to a database.

Arguments

    configs=array() (array)
    Connection configs, if empty then connect using the previous connection configs.
    {
        string server   : localhost database server address,
        string port     : 5432 database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced : false
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    }
    

Returns

void

Throws

count

int $obj->count(string sql, array | [mixed] params=null)

Get row count of the last query (without arguments) or a specified a query.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

int
Row count

esc

string $obj->esc(string val)

Escapes non SQL safe characters.

Arguments

    val (string)
    Value to be escaped

Returns

string
Escaped value

exists

bool $obj->exists(string table, string sql, array | [mixed] params=null)

Check if a table(when only the first argument is provided) exists or if a query has results.

Arguments

    table (string)
    Database table name or FROM clause
    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

bool
Table or result existence

Throws

fieldCount

int $obj->fieldCount()

Query field count.

Returns

int
Field count

fields

array|string $obj->fields(string glue=null, string esc='"')

Get query columns.

Arguments

    glue=null (string)
    null=return an array of column names, string=return a string of column names glued with the string
    esc='"' (string)
    Column name escape character

Returns

array|string
A list of column names or a string of glued column names

free

void $obj->free()

Free query result resource.

Returns

void

getResult

IDbResult $obj->getResult()

Get the query result as an IDbResult.

Returns

IDbResult
An MySqliResult instance

info

array $obj->info()

Get info about the dbms and class.

Since
Artaengine 1.1.0

Returns

array
Info array
{
    string server     : PostgreSQL server version,
    string client     : PostgreSQL client version,
    string class      : "Arta.PostgreSqlAdvanced",
    string version    : Class version,
    string extension  : "postgresql",
    string dbms       : "PostgreSQL",
    array  connection : {
        string server   : database server address,
        string port     : database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced :
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    },
}

isSerial

bool $obj->isSerial(string table, string col=id)

Check if a column is serial(auto increment).

Arguments

    table (string)
    Database table name
    col=id (string)
    Serial(auto increment) column name

Returns

bool
true=the column is serial

isnull

string $obj->isnull(string col, string | array val)

Wrap values inside COALESCE.

Arguments

    col (string)
    Column name
    val (string | array)
    Value(s)

Returns

string
COALESCE(col, vals, ...)

limit

string $obj->limit(string sql, int limit=10, int offset=0)

Pass an SQL query and get a limited SQL.

Arguments

    sql (string)
    SQL query to put limit on
    limit=10 (int)
    Number of rows to limit query result to
    offset=0 (int)
    Row number to start from (zero based)

Returns

string
The SQL with LIMIT clause

next

object|array $obj->next(bool object=true)

Fetch row as object (default) or array.

Arguments

    object=true (bool)
    true=fetch object, false=fetch array

Returns

object|array
The fetched row

query

bool $obj->query(string sql, array | [mixed] params=null)

Execute an SQL query. Use parameters for for values which need to be SQL escaped/safe.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

bool
State of success

Throws

row

array $obj->row()

Fetch row as array.

Returns

array
The fetched row

rows

array $obj->rows()

Fetch all rows in an arrays.

Returns

array
List of array rows [{row-array},]

seek

void $obj->seek(int row=0)

Move to a row.

Arguments

    row=0 (int)
    Zero based row index

Returns

void

serial

mixed $obj->serial(string table, string col=id, string position='current')

Get value of auto increment(serial) column.

Arguments

    table (string)
    Database table name
    col=id (string)
    Serial(auto increment) column name
    position='current' (string)
    'current' or 'next'

Returns

mixed
Value of the next/current auto increment(serial) column

serialSequence

void $obj->serialSequence()

For library uses only

Returns

void

toBool

string $obj->toBool(bool | int val)

Convert a value to an acceptable boolean value for the database.

Arguments

    val (bool | int)
    Source value

Returns

string
Database false/true

toTimestamp

string $obj->toTimestamp(int | array | string val)

Convert a value to a valid database timestamp value.

Arguments

    val (int | array | string)
    int=timestamp, array={y:, m:, d:, h:, i:, s:} or a subset of it, string=date/time timestamp | array: {y:, m:, d:, h:, i:, s:} or a subset | string: date/time

Returns

string
Database timestamp value

alterTable

string $obj->alterTable(
    string table,
    array cols=null,
    array constraints=null,
    string | array extra=null,
    bool exe=true
)

Alter table.

Arguments

    table (string)
    Table name
    cols=null (array)
    Column definitions
    {
        array add : {
            array column-name : {
                bool   key       : false true=column is the primary key,
                const  type      : Artaengine data type constant,
                int    len       : 255   string-column max length,
                bool   unsigned  : false numeric-column is unsigned,
                bool   required  : false
                    true=column can't be null (or use nullable),
                bool   nullable  : true
                    false=column can't be null (or use required),
                string default   : false column default value,
                bool   unique    : false true=column values must be unique,
                string check     : check constraint expression,
                array  reference : column is a foreign key {
                    string table : reference table-name,
                    string col   : reference column-name,
                }
            }
        }
        array alter : {
            array column-name : {
                const  type     : Artaengine data type constant,
                int    len      : string-column max length,
                bool   unsigned : numeric-column is unsigned,
                bool   required :
                    true=column can't be null (or use nullable),
                bool   nullable :
                    false=column can't be null (or use required),
                string default  : column default value,
            }
        }
        array drop : [
            columns-to-drop,
        ]
        array rename : {
            string column-name : new-column-name,
            int    0 : new-table-name,
        }
    }
    
    constraints=null (array)
    Constraint definitions
    {
        array add : [
            {
                string type : "PRIMARY KEY",
                string name : constraint name,
                array  cols : [
                    "column-name",
                ]
            },
    
            {
                string type : "UNIQUE",
                string name : constraint name,
                array  cols : [
                    "column-name",
                ]
            },
    
            {
                string type     : "CHECK",
                string name     : constraint name,
                string col_name : column name,
                string exp      : check constraint expression,
            },
    
            {
                string type      : "FOREIGN KEY",
                string name      : constraint name,
                bool   delete    : false true=cascade on delete,
                bool   update    : false true=cascade on update,
                array  reference : {
                    string table : reference table-name,
                    array  cols  : {
                        string column-name : reference column-name,
                    },
                }
            },
    
            {
                string type      : "FOREIGN KEY",
                string name      : constraint name,
                bool   delete    : false true=cascade on delete,
                bool   update    : false true=cascade on update,
                string table     : reference table-name,
                array  reference : {
                    string column-name : reference column-name,
                }
            },
        ]
        array alter : {
            string constraint-name : {
                new-constraint-definitions
            },
        }
        array drop : [
            columns-to-drop,
        ]
    }
    
    extra=null (string | array)
    Extra DBMS specific table parameters
    exe=true (bool)
    true=execute the ALTER TABLE on database

Returns

string
ALTER TABLE SQL

Throws

artaType2DbType

array $obj->artaType2DbType()

Get a dictionary of Artaengine data types mapped to database data types.

Returns

array
{T::TYPE_CONST: db-type-string,}

backup

void $obj->backup(string | bool filename=null, array configs=array)

Dump database to file. (Experimental).

Arguments

    filename=null (string | bool)
    File-path to write dump to or false=return dump or null=dump to file in temp directory as "database-name.sql"
    configs=array (array)
    [@PostgeSQLAdvanced.backup.configs]

Returns

void

checkConstraint

string $obj->checkConstraint(string | array def)

Create a check constraints.

Arguments

    def (string | array)
    SQL or [exp: [], col_name: name, name(optional - con name) ]

Returns

string
Constraint definition

constraintName

string $obj->constraintName(string prefix, string table, string name)

Make a valid database constraint name.

Arguments

    prefix (string)
    Such as 'PK', 'FK', 'CK', etc.
    table (string)
    Table name
    name (string)
    Constraint main name

Returns

string
Constraint name

copyA2T

void $obj->copyA2T(array rows, string table, string delimiter="\t", string null='\N')

Copy data of an array into a table.

Arguments

    rows (array)
    Data rows
    table (string)
    Table name
    delimiter="\t" (string)
    Separates col data in each row
    null='\N' (string)
    Uses this string instead of null values

Returns

void

copyT2A

array $obj->copyT2A(string table, string delimiter="\t", string null='\N')

Copy table data to an array.

Arguments

    table (string)
    Table name
    delimiter="\t" (string)
    Separates col data in each row
    null='\N' (string)
    Uses this string instead of null values

Returns

array
Table data

createTable

string $obj->createTable(
    string table,
    array cols=null,
    array constraints=null,
    string | array extra=null,
    bool exe=true
)

Create table.

Arguments

    table (string)
    Table name
    cols=null (array)
    Column definitions
    {
        string column-name : {
            bool   key       : false true=column is the primary key,
            const  type      : Artaengine data type constant,
            int    len       : 255   string-column max length,
            bool   unsigned  : false numeric-column is unsigned,
            bool   required  : false
                true=column can't be null (or use nullable),
            bool   nullable  : true
                false=column can't be null (or use required),
            string default   : false column default value,
            bool   unique    : false true=column values must be unique,
            string check     : check constraint expression,
            array  reference : column is a foreign key {
                string table : reference table-name,
                string col   : reference column-name,
            }
        }
    }
    
    constraints=null (array)
    Constraint definitions
    [
        {
            string type : "PRIMARY KEY",
            string name : constraint name,
            array  cols : [
                "column-name",
            ]
        },
    
        {
            string type : "UNIQUE",
            string name : constraint name,
            array  cols : [
                "column-name",
            ]
        },
    
        {
            string type     : "CHECK",
            string name     : constraint name,
            string col_name : column name,
            string exp      : check constraint expression,
        },
    
        {
            string type      : "FOREIGN KEY",
            string name      : constraint name,
            bool   delete    : false true=cascade on delete,
            bool   update    : false true=cascade on update,
            array  reference : {
                string table : reference table-name,
                array  cols  : {
                    string column-name : reference column-name,
                },
            }
        },
    
        {
            string type      : "FOREIGN KEY",
            string name      : constraint name,
            bool   delete    : false true=cascade on delete,
            bool   update    : false true=cascade on update,
            string table     : reference table-name,
            array  reference : {
                string column-name : reference column-name,
            }
        },
    ]
    
    extra=null (string | array)
    Extra DBMS specific table parameters
    exe=true (bool)
    true=execute the CREATE TABLE on database

Returns

string
CREATE TABLE SQL

Throws

dbType2ArtaType

array $obj->dbType2ArtaType()

Get a dictionary of database data types mapped to Artaengine data types.

Returns

array
{db-type-string: T::TYPE_CONST,}

getMetaConstraints

array $obj->getMetaConstraints(string table=null, string constraint=null)

Get table constraints meta data.

Arguments

    table=null (string)
    Table name, null=get all constraints that exist in the database
    constraint=null (string)
    Constraint name to get info about, null=get info about all constraints

Returns

array
table=null then [{def},] or table!=null then {constraint-name: {def},} if constraint!=null then {def} def=
{
    string name  : constraint name,
    string type  : constraint type,
    string table : table name,
    array  cols  : [
        list of table columns affected by the constraint
    ],
    string delete : action on delete,
    string update : action on update,
    array  reference : {
        string table : reference table name,
        array  cols  : [
            list of columns referenced by the constraint
        ],
    },
    string match              : only for foreign keys,
    string is_deferrable      : only for foreign keys,
    string initially_deferred : only for foreign keys,
    string defer              : only for foreign keys,
    string definition         : only for check constraints,
}

getMetaIndexes

array $obj->getMetaIndexes(string table)

Get table indexes meta data (Experimental - subject to change).

Arguments

    table (string)
    table name, null=return all

Returns

array
array {index-name: {meta},} meta=
{
    string name       : index name,
    string definition : index definition,
}

getMetaProcedures

array $obj->getMetaProcedures(string proc=null)

Get Procedure(s)/Function(s) meta data (Experimental - subject to change).

Arguments

    proc=null (string)
    Procedure/Function name

Returns

array
{
    string schema     : database schema,
    string name       : routine name,
    string type       : routine type,
    string language   : external language,
    string udt_type   : type udt name,
    string data_type  : data type,
    string body       : routine body,
    string def        : routine definition,
    string definition : routine definition,
}

getMetaTable

array $obj->getMetaTable(string table=null, int shape=2)

Get metadata of tables.

Arguments

    table=null (string)
    Table name, null=return a list of database tables
    shape=2 (int)
    1=return column definitions only, 2=return column definitions and constrain statuses, 3=return column definitions and constraint definitions

Returns

array
if table=null {table-name: table-name,} else {col-name: {meta},} where meta is: [DbAdvanced.getMetaTable]

getMetaTableStatus

array $obj->getMetaTableStatus(string table=null)

Get information about a table. The result is DBMS specific.

Arguments

    table=null (string)
    Table name

Returns

array
[@PostgeSql.getMetaTableStatus]

getMetaTriggers

array $obj->getMetaTriggers(string table=null)

Get trigger(s) meta data (Experimental - subject to change).

Arguments

    table=null (string)
    Table name, null=return all triggers

Returns

array
{trigger-name: {meta},} meta=

getMetaUsers

array $obj->getMetaUsers()

Get database user's meta data.

Returns

array
{user-name: {meta},} meta=

getMetaViews

array $obj->getMetaViews(string view=null, bool thin=false)

Get database view list or view(s) meta data.

Arguments

    view=null (string)
    View name, null=return for all views
    thin=false (bool)
    true=return only a list of view names

Returns

array
if thin=true return {view-name: view-name,} if view name is specified return {meta} if view name is not specified return {view-name: {meta},} meta=
{
    string schema     : database schema,
    string name       : view name,
    string owner      : database user,
    string definition : view definition,
    array cols        : [
        list of view columns
    ],
}

getTablePrimaryKeys

array $obj->getTablePrimaryKeys(string table)

Get table primary keys.

Arguments

    table (string)
    Table name

Returns

array
Array of primary key column names {pk-col-name: pk-col-name,}

realType

const|array $obj->realType(const type)

Get the database data type for virtual data types such as PASSWORD/EMAIL, etc.

Arguments

    type (const)
    T::TYPE_CONST (An Artaengine data type)

Returns

const|array
Artaengine data type | [artaengine-data-type, len]

restore

void $obj->restore(string filename=null)

Restore dump/SQL file to database.

Arguments

    filename=null (string)
    Filename to open or null=assume the filename is "TEMP_DIR/database-name.sql"

Returns

void

toType

string $obj->toType(const type, int | array length=null, bool unsigned=false)

Returns the database type definition of a T::TYPE_CONST (Artaengine data type).

Arguments

    type (const)
    T::TYPE_CONST (Artaengine data type)
    length=null (int | array)
    Max length or [min-length, max-length]
    unsigned=false (bool)
    Signed or Unsigned (if supported by database)

Returns

string
Database column type definition

truncate

string $obj->truncate(bool delete=false)

Get truncate/delete SQL string for all database tables.

Arguments

    delete=false (bool)
    bool false=TRUNCATE TABLE, true=DELETE FROM

Returns

string
TRUNCATE/DELETE SQL