PDO ALTITUDE

INTRODUCTION

Inclusion and inheritance

Object Infos extends the object Listing (class Infos extends Listing), so both classes files must be included at the same time:

require("Listing.class.php");
require("Infos.class.php");

It is of course possible to use the spl_autoload_register() function to include the classes files automatically when they are called in code. Example of an autolaod function:

function autoload ($classname) {
    require_once('classes/'.$classname.'.class.php');
}
spl_autoload_register('autoload');

Instanciation and prerequisites

Altitude uses PHP's PDO extension to work. So you must ensure of its activation within the php.ini file of your server.

Both objects Listing and Infos use an instance of PDO object to communicate with database server. This instance is created automatically, with some default options, when instantiating Listing or Infos object. However, if you want to set specific options, it can be defined outside, then passed to the object when created.
Anyway, in order to use a connection with the database, it is imperative to define the following five constants:

define ("HOST", "localhost");				// MySQL server host name
define ("USER", "username");				// SQL user
define ("PASS", "********");				// Password
define ("BASE", "database");				// MySQL database name
define ("DSN",  "mysql:dbname=".BASE.";host=".HOST);	// Or, for an SQLite database:
// define("DSN","sqlite:path/to/altitude-example.sqlite");

By default the PDO instance is created with the option PDO::ATTR_ERRMODE on PDO::ERRMODE_EXCEPTION. For MySQL only, the attribute PDO::ATTR_PERSISTENT is set to True, and a request "SET NAMES 'utf8'" is done when instanciating. (see static protected function newPDO() line 426 of class "Listing").

In short, when the five constants are defined with the right connection settings for the database, you're good to use objects Listing and Infos.

Important concepts

IDENTIFICATION: We assume that all the tables of the database contain a column named "id" (lowercase).
JOINTS : The joints can work even with the 'MyISAM' type of table. For this reason we don't use the SQL command 'JOIN', but a quick system of detection, defined in the configuration.
In order to retreive the joints, the constant FOREIGNKEYS_PREFIX must be defined. You can give the prefix you want. For example:
```
define ("FOREIGNKEYS_PREFIX", "FK_");
```
Then, an array describing the relationships between columns of different tables is needed. This array must be named $RELATIONS (uppercase). For instance:
```
$RELATIONS = Array(
    "FK_user_ID"	=> Array('table' => "users",	'alias' => 'user'),
    "FK_item_ID"	=> Array('table' => "items",	'alias' => 'item'),
    "FK_comment_ID"	=> Array('table' => "comments",	'alias' => 'comment')
);
```
DATES : Altitude also needs to know which columns could contain dates, in order to reformat them with ISO 8601 format. So we must define the array $DATE_FIELDS in the configuration, containing a list of names for the fields which may be dates. Example:
```
$DATE_FIELDS = Array(
    "date",
    "last_action",
    "date_creation"
);
```
Next, it's possible to automatically update two columns when we save an entry. These columns correspond to the creation date, and the last update date. For that you need to define the two contants DATE_CREATION and LAST_UPDATE, containing the names of the wolumns to update. For instance:
```
define ("DATE_CREATION", "date_creation");
define ("LAST_UPDATE", "last_action");
```
Please note that if they are not defined, no error will be throw, but the automation won't work.

L'objet "LISTING"

Properties

$request

STRING — Result of the SQL request string compiled by getList() at request time

$result

ARRAY — Result of all entries returned by getList()

$pdo

OBJECT — PDO instance for SQL connection

$pdoDriver

STRING — Name of the driver in use for PDO connection ('mysql' or 'sqlite')

$table

STRING — Name of the current table

$cols

STRING — Names of the columns to get, comma-separated, or * for all columns.

$sortBy

STRING — Name of the column used to sort results.

$order

STRING — Sorting direction: 'ASC' for ascending, 'DESC' for descending.

$filter_key

STRING — Name of the column used for filtering results

$filter_val

STRING — Value to use for filtering results

$lastFilterLogic

STRING — Last type of operand used for multiple filtering (AND, OR, NAND, NOR...)

$isFiltred

BOOLEAN — True if the result is filterd, False otherwise

$filters

ARRAY — Array containing all SQL filters

$filterSQL

STRING — Part of the SQL request string which contains the filter (to write if "by hand")

Methods

__construct()

LISTING of SQL table: initialization

__construct(
	OBJECT $pdoInstance = false
)

Parameters

OBJECT

$pdoInstance

Predefined PDO instance (optional)

getList()

Initialize a list of data to get for a given table.

getList(
	STRING $table, STRING $want = '*', STRING $tri = 'id', STRING $ordre = 'ASC',
	STRING $filter_key = false, STRING $filter_comp = '=', STRING $filter_val = null,
	INT $limit = false, BOOLEAN $withFK = true, BOOLEAN $decodeJson = true,
	BOOLEAN $parseDatesJS = true
) : ARRAY

Parameters

STRING	$table	Name of the table
STRING	$want	A list of columns names to get, comma separated (default '*' (all))
STRING	$sortBy	Column name to use for sorting (default 'id')
STRING	$order	Sorting direction (default 'ASC')
STRING	$filter_key	Column name to use for filtering results (default FALSE (no filter))
STRING	$filter_comp	Comparison operand to use for filtering (default '=')
STRING	$filter_val	Value to use for filtering results (default null)
INT	$limit	Maximum number of data to return (default FALSE (no limit)
BOOLEAN	$withFK	TRUE to get the JOINT data (see important concepts) (default TRUE)
BOOLEAN	$decodeJson	TRUE to automatically decode fields containing JSON strings and get an array. FALSE to get the raw string (default TRUE)
BOOLEAN	$parseDatesJS	TRUE to format date fields (see important concepts) as ISO 8601 for use with javascript (default TRUE)

Returns

ARRAY — The results array, or FALSE if no data.

countResults()

Get the number of entries found.

countResults() : INT

Returns

INT — The number of entries found.

addFilter()

Add a condition to the filter for SQL request.

addFilter(
	STRING $filter_key = false, STRING $filter_comp = '=',
	STRING $filter_val = false, STRING $logic = 'AND'
)

Parameters

STRING	$filter_key	Column name for filter
STRING	$filter_comp	Comparaison to use for filter (default "=")
STRING	$filter_val	Value to compare
STRING	$logic	The logic type of operand to use with potential previous filters (default "AND")

addFilterRaw()

Add a condition to the filter for SQL request, in a less secure way, to allow SQL functions instead of a simple string for $filter_val.

addFilterRaw(
	STRING $filter_key = false, STRING $filter_comp = '=',
	STRING $filter_val = false, STRING $logic = 'AND'
)

Parameters

STRING	$filter_key	Column name for filter
STRING	$filter_comp	Comparaison to use for filter (default "=")
STRING	$filter_val	Value to compare
STRING	$logic	The logic type of operand to use with potential previous filters (default "AND")

resetFilter()

Reset the filtering (to make another request, for instance).

resetFilter()

setFilterSQL()

Define a filter "by hand" in pure SQL.

setFilterSQL(
	STRING $filtre
)

Parameters

STRING $filtre The SQL filter (ie. "id >= 30 AND date <= NOW()")

reindexList()

Reformat the results with an associative array, where index is $wantedIndex instead of 0,1,2,3,...

reindexList(
	STRING $wantedIndex = null
) : ARRAY

Paramètres

STRING

$wantedIndex

Column name to use for indexing the array

Retourne

ARRAY — The new array with replaced index, FALSE if error.

getCols()

STATIC Returns an array with all the column names of a table.

Listing::getCols(
	STRING $table = false
) : ARRAY

Parameters

STRING

$table

Name of the table

Returns

ARRAY — An array with all the column names of a table, FALSE if error

getMax()

STATIC Utility static function to get the max value of a column in a table.

Listing::getMax(
	STRING $table, STRING $column
) : MIXED

Parameters

STRING	$table	Name of the table
STRING	$column	Name of the column

Returns

MIXED — The highest value (most long string, highest number, most recent date...) of the column, or FALSE if no result found.

getAIval()

STATIC Returns the next auto-increment value of a table.

Listing::getAIval(
	STRING $table
) : INT

Parameters

STRING

$table

Name of the table

Returns

INT — The next auto-increment value of a table.

array_reindex_by()

STATIC Utility static function to reindex an array with a column name.

Listing::array_reindex_by(
	ARRAY $array, STRING $colIndex = 'id', ARRAY $includeCols = null
) : ARRAY

Parameters

ARRAY	$array	The original array
STRING	$colIndex	The column name to use for array index
ARRAY	$includeCols	Column values to put back in array. Use a list (array) of columns names (default null -> all columns)

Returns

ARRAY — The reindexed array according to a column, or FALSE if error.

newPDO()

STATIC Statique protected method to create an instance od PDO.

Listing::newPDO()

initPDO()

Initialization of the PDO object.

initPDO()

check_table_exists()

Check if a table exists in the database.

check_table_exists(
	STRING $table
) : BOOLEAN

Parameters

STRING

$table

Name of the table

Returns

BOOLEAN — True if the table exists in the DB.

check_col_exists()

Check if a column exists in the current table.

check_col_exists(
	STRING $column
) : BOOLEAN

Parameters

STRING

$column

Name of the column

Returns

BOOLEAN — True if the column exists in the table.

getForeignKey()

Get the joint data, according to configuration (see important concepts).

getForeignKey(
	STRING $k, INT $v, BOOLEAN $decodeJson = true, BOOLEAN $parseDatesJS = true
) : ARRAY

Parameters

STRING	$k	Column name of the joint (origin)
INT	$v	Value to find (destination's ID)
BOOLEAN	$decodeJson	TRUE to automatically decode JSON. FALSE to have JSON data as string (default TRUE).
BOOLEAN	$parseDatesJS	TRUE to format dates in ISO 8601 for javascript (default TRUE).

Returns

ARRAY — A tuple (key, value) of the joint found. Key is the joint's alias (see important concepts), and value is an array containing data of the joint entry found. FALSE if no joint found.

Exceptions

Here is a list of exceptions which can be thrown when using Listing object, and their signification.

Exceptions for getList()

`Table '$table' doesn't exists`

This means that the specified table doesn't exists in the database. Check the parameter $table you give to getList().

Exceptions for addFilter()

`Listing::addFilter() : Missing column name for filter`

This means that the column name for filter is missing. Choose a column in the current table, and give its name for parameter $filter_key of addFilter().

`Listing::addFilter() : Missing value for filter search`

This means that the value to search in filter for the specified column in current table is missing. Give a value for parameter $filter_val of addFilter().

Exceptions for addFilterRaw()

`Listing::addFilterRaw() : Missing column name for filter`

This means that the column name for filter is missing. Choose a column in the current table, and give its name for parameter $filter_key of addFilterRaw().

`Listing::addFilterRaw() : Missing value for filter search`

This means that the value to search in filter for the specified column in current table is missing. Give a value for parameter $filter_val of addFilterRaw().

Exceptions for reindexList()

`Listing::reindexList() : '$wantedIndex' is not an unique index for table '$this->table'`

This means that the choosen column for reindexation has no unique index. It may overwrite some values in the returned array, so Altitude throw an exception to avoid that. Choose a column which have a unique index (like 'ID' for instance) and give its name for parameter $wantedIndex of reindexList().

Other exceptions may appear, they are probably thrown by PDO himself.

L'objet "INFOS"

Below are the properties and methods specific to the object Infos:

Properties

The class Infos inherits properties and methods of the class Listing, so it's useless to remind them here.

$data

ARRAY — Array containing data of the entry found.

$loaded

BOOLEAN — TRUE if database has already been read (to check before 'update' or 'insert' -> see method save()).

Methods

__construct()

GETTING content of an entry of an SQL table: Initialization

__construct(
	STRING $table, OBJECT $pdoInstance = false
)

Parameters

STRING	$table	SQL table name
OBJECT	$pdoInstance	Predefined PDO instance (optional)

setTable()

Definition of the table where to find / add an entry.

setTable(
	STRING $table
)

Parameters

STRING

$table

SQL table name

getTable()

Get the current table name.

getTable() : STRING

Returns

STRING — Name of the current table.

loadInfos()

Load an entry according to a basic filter. Throws an error if several entries found (and not only one).

loadInfos(
	STRING $filtreKey, STRING $filtreVal,
	BOOLEAN $withFK = true, BOOLEAN $decodeJson = true, BOOLEAN $parseDatesJS = true
)

Parameters

STRING	$filtreKey	Filter column name
STRING	$filtreVal	Filter value for the entry
BOOLEAN	$withFK	TRUE to get JOINED data (default TRUE)
BOOLEAN	$decodeJson	TRUE to automatically decode fileds containing JSON. FALSE to get JSON fields as STRING (default TRUE)
BOOLEAN	$parseDatesJS	TRUE to format dates as ISO 8601 for javascript (default TRUE)

isLoaded()

Check if data has been loaded.

isLoaded() : BOOLEAN

Returns

BOOLEAN — TRUE if data has already been loaded from database.

getInfo()

Get the data of a specified column.

getInfo(
	STRING $column = "*"
) : MIXED

Parameters

STRING

$column

Name of the column which we want the data

Returns

MIXED — Value of the column, FALSE if no data found.

getManyInfos()

Get several data of specified columns, or every data of the entry in memory.

getManyInfos(
	ARRAY|STRING $columns = "*"
) : ARRAY

Parameters

ARRAY|STRING

$columns

Columns names which we want the data, in an array, or a string comma-separated (default '*' -> all columns)

Returns

ARRAY — An array containing asked data (empty if no data found).

countInfos()

Counts the number of fields of the entry in memory.

countInfos() : INT

Returns

INT — The number of fields (columns).

setInfo()

Add / Update a field for the entry in memory.

(
	STRING $key, STRING $val
)

Parameters

STRING	$key	The column name
STRING	$val	The value for the column

setManyInfos()

Update several fields of an entry in memory at once (allows to check integrity of the entry, in term of columns).

setManyInfos(
	ARRAY $newInfos,
	BOOLEAN $allowAddCol = false, BOOLEAN $checkMissing = false, BOOLEAN $forceID = false
)

Parameters

ARRAY	$newInfos	An array containing the new values for the columns of the entry
BOOLEAN	$allowAddCol	TRUE to ignore extra columns, FALSE to allow the addition of inexistent columns (default FALSE)
BOOLEAN	$checkMissing	TRUE to check if all columns are defined $newInfos (throws an error), FALSE to let MySQL feed missing values with defaults (default FALSE)
BOOLEAN	$forceID	TRUE to force the definition of the column "id", FALSE to ignore it and let MySQL do its auto-increment (default FALSE)

save()

SAVE an entry of the current table in database.

save(
	STRING $filterKey = 'id', STRING $filterVal = 'this',
	BOOLEAN $autoAddCol = true, BOOLEAN $autoDate = true
) : STRING

Parameters

STRING	$filterKey	Name of the column used to identify the entry (default 'id')
STRING	$filterVal	The identifier value (default 'this' -> the current entry)
BOOLEAN	$autoAddCol	TRUE to agg the column(s) if it (they) doesn't exist
BOOLEAN	$autoDate	TRUE to update the field of last modification with the current date, (or creation date in case of an INSERT, if the column is present. (default TRUE)

Returns

STRING — The type of the SQL request which has just been used to save ('UPDATE', ou 'INSERT')

delete()

Delete one (or several) entry(ies) from the database.

delete(
	STRING $filterKey = 'id', STRING $filterVal = 'this', STRING $filtrePlus = null
) : INT

Parameters

STRING	$filterKey	Name of the column used to identify the entry (default 'id')
STRING	$filterVal	The identifier value (default 'this' -> the current entry)
STRING	$filtrePlus	Additionnal filter for the SQL request allowing best identification of the entry (optionnel)

Returns

INT — Number of entries actually deleted.

colExists()

STATIC Check if a column exists in the current table.

Infos::colExists(
	STRING $table, STRING $colName
) : BOOLEAN

Parameters

STRING	$table	Name of the table
STRING	$colName	Name of the column

Returns

BOOLEAN — TRUE if the column exists, FALSE otherwise.

colIndex_isUnique()

STATIC Check if a column has a UNIQUE index (i.e. if it can share the same value for several entries).

Infos::colIndex_isUnique(
	STRING $table, STRING $colName
) : BOOLEAN

Parameters

STRING	$table	Name of the table
STRING	$colName	Name of the column to check

Returns

BOOLEAN — TRUE if the column has a UNIQUE index, FALSE otherwise.

addNewCol()

STATIC Add a column in a table of the database.

Infos::addNewCol(
	STRING $table, STRING $colName, STRING $colType = 'VARCHAR(64)', STRING $defaultVal = ''
) : BOOLEAN

Parameters

STRING	$table	Name of the table
STRING	$colName	Name of the new column
STRING	$colType	Type of the column to add (default "VARCHAR(64)")
STRING	$defaultVal	Default value for the column (optional, and useless for type "TEXT")

Returns

BOOLEAN — TRUE if success, FALSE if error.

removeCol()

STATIC Remove a column from a table of the database. Warning: incompatible with SQLite at the moment.

Infos::removeCol(
	STRING $table, STRING $colName
) : BOOLEAN

Parameters

STRING	$table	Name of the table
STRING	$colName	Name of the column to remove

Returns

BOOLEAN — TRUE if success, FALSE if error.

createMissingCols()

Check if all fields exist, otherwise create the columns on the fly.

createMissingCols()

autoAddCol()

Add a column to the current table, with automatic choice of the column type.

autoAddCol(
	STRING $colName, STRING $val
) : BOOLEAN

Parameters

STRING	$colName	Name of the column
STRING	$val	Value of the column (in order to auto-check the value type)

Returns

BOOLEAN — TRUE if success, FALSE if fail.

Exceptions

Here is a list of exceptions which can be thrown when using Infos object, and their signification.

Exceptions for __construct()

`Infos::__construct() : missing table name`

This means that the table used to find the entry is missing. Choose a table in the database, and give its name to the parameter $table of new Infos().

Exceptions for setTable()

`Infos::setTable() : Table '$table' doesn't exists`

This means that the specified table doesn't exists in database. Choose a table in the database, and give its name to parameter $table of setTable().

Exceptions for loadInfos()

`Infos::loadInfos() : Several entries ($number) found for '$filtreKey = $filtreVal'! Please refine your filter`

This means that more than one entry were found for the search. The 'Infos' object have been designed to work on a precise entry of a table, it throw an exception when the result is multiple. Choose a more precise filtering to find the entry, changing the parameters $filtreKey and $filtreVal for loadInfos().

Exceptions for getInfo()

`Infos::getInfo() : Missing column name`

This means that the column name is missing, and it's needed to get a specific value of the current entry. Choose a column in the current entry's table, and give its name to parameter $column of getInfo().

Exceptions for setManyInfos()

`Infos::setManyInfos() : 'newInfos' must be an array ($type found)`

This means that the variable type of parameter $newInfos is not an array. To modify the current entry with method setManyInfos(), you must give an associative array, which contain the values to change. Each array's key being the column name, and its value being the new value for the column.

`Infos::setManyInfos() : missing $number columns in array 'newInfos', compared to current table ('$table'). List of missing columns: json_encode($missingRows)`

When parameter $checkMissing of setManyInfos() is True, the method will perform a verficiation on the list of the $newInfos array's keys to check if some are missing. This exception is thrown when some columns are missing in the array, and give a list of those missing columns.

Exceptions for save()

Infos::save() : Duplicate entry for `$key`="$val" in table '$table'.

This means that one of the values of the entry to be saved already exists in the database, because of a column that has a unique index. Change the new value of the entry before calling save().

`Infos::save() : table '$table' -> $msg.`

`Infos::save() : '.$error`

These two exceptions are thrown when PDO encounter an error. Detail of this error is specified in the message.

Exceptions for addNewCol()

`Infos::addNewCol() : Missing table name`

This means that the table's name is missing to add a column into.

`Infos::addNewCol() : Missing column name`

This means that the column's name to add in table is missing.

`Infos::addNewCol() : This column already exists`

This means that the column is already present in the table. Choose another name for the column.

Exceptions for removeCol()

`Infos::removeCol() : SQLite3 limitation: you can't drop a column from a table with 'ALTER TABLE' statement.`

This exception is thrown when PDO driver 'sqlite' is in use. Because of a limitation in ALTER functions in SQLite, DROP being unavailable, this method is at the moment unusable. You'll have to do this operation "by hand" on and SQLite console.

Other exceptions may appear, they are probably thrown by PDO himself.

Licence

This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with this program. If not, see www.gnu.org/licenses/agpl-3.0.html.

PDO AltitudeDocumentation

INTRODUCTION

Inclusion and inheritance

Instanciation and prerequisites

Important concepts

L'objet "LISTING"

Properties

$request

$result

$pdo

$pdoDriver

$table

$cols

$sortBy

$order

$filter_key

$filter_val

$lastFilterLogic

$isFiltred

$filters

$filterSQL

Methods

__construct()

LISTING of SQL table: initialization

Parameters

getList()

Initialize a list of data to get for a given table.

Parameters

Returns

countResults()

Get the number of entries found.

Returns

addFilter()

Add a condition to the filter for SQL request.

Parameters

addFilterRaw()

Add a condition to the filter for SQL request, in a less secure way, to allow SQL functions instead of a simple string for $filter_val.

Parameters

resetFilter()

Reset the filtering (to make another request, for instance).

setFilterSQL()

Define a filter "by hand" in pure SQL.

Parameters

reindexList()

Reformat the results with an associative array, where index is $wantedIndex instead of 0,1,2,3,...

Paramètres

Retourne

getCols()

STATIC Returns an array with all the column names of a table.

Parameters

Returns

getMax()

STATIC Utility static function to get the max value of a column in a table.

Parameters

Returns

getAIval()

STATIC Returns the next auto-increment value of a table.

Parameters

Returns

array_reindex_by()

STATIC Utility static function to reindex an array with a column name.

Parameters

Returns

newPDO()

STATIC Statique protected method to create an instance od PDO.

initPDO()

Initialization of the PDO object.

check_table_exists()

Check if a table exists in the database.

Parameters

Returns

check_col_exists()

Check if a column exists in the current table.

Parameters

Returns

getForeignKey()

Get the joint data, according to configuration (see important concepts).

Parameters

Returns

Exceptions

PDO Altitude
Documentation

`Table '$table' doesn't exists`

`Listing::addFilter() : Missing column name for filter`

`Listing::addFilter() : Missing value for filter search`

`Listing::addFilterRaw() : Missing column name for filter`

`Listing::addFilterRaw() : Missing value for filter search`

`Listing::reindexList() : '$wantedIndex' is not an unique index for table '$this->table'`