DataDictionary View code

A class defining methods to work directly with database tables.

This file is based on the DataDictionary base class from the adodb_lite library which was in turn a fork of the adodb library at approximately 2004.

Credits and kudos to the authors of those packages.

package

CMS
author

Robert Campbell
copyright

Copyright (c) 2015, Robert Campbell calguy1000@cmsmadesimple.org
since 2.2

Methods

__construct(\CMSMS\Database\Connection $conn)

Constructor

Arguments

\CMSMS\Database\Connection $conn

MetaTables() : array<mixed,string>

Return the list of tables in the currently connected database.

abstract

Response

array<mixed,string> 

MetaColumns(string $table) : array<mixed,string>

Return the list of columns in a table within the currently connected database.

abstract

Arguments

string $table

The table name.

Response

array<mixed,string> 

ExecuteSQLArray(array<mixed,string> $sql, boolean $continueOnError = true) : integer

Given an array of SQL commands execute them in sequence.

Arguments

array<mixed,string> $sql

An array of sql commands.

boolean $continueOnError

wether to continue on errors or not.

Response

integer

2 for no errors, 1 if an error occured.

CreateDatabase(string $dbname,  $options = false) : array<mixed,string>

Create the SQL commands that will result in a database being created.

Arguments

string $dbname

$options

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

CreateIndexSQL(string $idxname, string $tabname, string|array<mixed,string> $flds,  $idxoptions = false) : array<mixed,string>

Generate the SQL to create an index.

Arguments

string $idxname

The index name

string $tabname

The table name

string|array<mixed,string> $flds

A list of the table fields to create the index with. Either an array of strings or a comma separated list.

$idxoptions

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

DropIndexSQL(string $idxname, string $tabname = NULL) : array<mixed,string>

Generate the SQL to drop an index

Arguments

string $idxname

The index name

string $tabname

The table name

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

AddColumnSQL(string $tabname, string $flds) : array<mixed,string>

Generate the SQL to add columns to a table.

Arguments

string $tabname

The Table name.

string $flds

The column definitions (using DataDictionary meta types)

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

AlterColumnSQL(string $tabname, string $flds, string $tableflds = '', \CMSMS\Database\array/string $tableoptions = '') : array<mixed,string>

Change the definition of one column

Arguments

string $tabname

table-name

string $flds

column-name and type for the changed column.

string $tableflds

complete defintion of the new table, eg. for postgres, default ''

\CMSMS\Database\array/string $tableoptions

options for the new table see CreateTableSQL, default ''

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

RenameColumnSQL(string $tabname, string $oldcolumn, string $newcolumn, string $flds = '') : array<mixed,string>

Rename one column in a table.

Arguments

string $tabname

table-name

string $oldcolumn

column-name to be renamed

string $newcolumn

new column-name

string $flds

complete column-defintion-string like for AddColumnSQL, only used by mysql atm., default=''

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

DropColumnSQL(string $tabname, string $flds, string $tableflds = '', \CMSMS\Database\array/string $tableoptions = '') : array<mixed,string>

Drop one column from a table.

Arguments

string $tabname

table-name

string $flds

column-name and type for the changed column

string $tableflds

complete defintion of the new table, eg. for postgres, default ''

\CMSMS\Database\array/string $tableoptions

options for the new table see CreateTableSQL, default ''

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

DropTableSQL(string $tabname) : array<mixed,string>

Drop one table, and all of it's indexes

Arguments

string $tabname

The table name to drop.

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

RenameTableSQL(string $tabname, string $newname) : array<mixed,string>

Rename a table.

Arguments

string $tabname

The table name

string $newname

The new table name

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

CreateTableSQL(string $tabname, string $flds, mixed $tableoptions = false) : array<mixed,string>

Generate the SQL to create a new table.

The flds string is a comma separated of field definitions, where each definition is of the form fieldname type columnsize otheroptions

The type fields are codes that map to real database types as follows:

C
Varchar, capped to 255 characters.
X
Text
XL
LongText
C2
Varchar, capped to 255 characters
XL
LongText
B
LongBlob
D
Date
DT
DateTime
T
Time
TS
Timestamp
L
TinyInt
R / I4 / I
Integer
I1
TinyInt
I2
SmallInt
I4
BigInt
F
Double
N
Numeric

The otheroptions field includes the following options:

AUTO
Auto increment. Also sets NOTNULL.
AUTOINCREMENT
Same as AUTO
KEY
Primary key field. Also sets NOTNULL. Compound keys are supported.
PRImARY
Same as KEY
DEFAULT
The default value. Character strings are auto-quoted unless the string begins with a space. i.e: ' SYSDATE '.
DEF
Same as DEFAULT
CONSTRAINTS
Additional constraints defined at the end of the field definition.

Arguments

string $tabname

The table name

string $flds

a comma separated list of field definitions using datadictionary syntax.

mixed $tableoptions

A string specifying table options (database driver specific) for the table creation command. Or an associative array of table options, keys being the database type (as available).

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

ChangeTableSQL(string $tablename, string $flds, array $tableoptions = false) : array<mixed,string>

Add, drop or change columns within a table.

This function changes/adds new fields to your table. You don't have to know if the col is new or not. It will check on its own.

Arguments

string $tablename

The table name

string $flds

The field definitions

array $tableoptions

Table options

Response

array<mixed,string>

An array of strings suitable for use with the ExecuteSQLArray method

Back to top