Database

The Database repository is separately maintained at https://github.com/PhpGt/Database

The database layer of PHP.Gt encloses your application's database scripts within a simple and standardised interface, separating database access from your page logic. Write your queries in plain SQL or use the SQL Builder as an object oriented representation of an SQL query, with the benefit of inheritance.

Connecting to a database

A layer on top of PDO is used for database access, so the following database engines are supported:

• CUBRID
• Sybase
• Firebird
• IBM
• Informix
• MySQL
• MS SQL Server
• Oracle
• ODBC and DB2
• PostgreSQL
• SQLite
• 4D

All database connection settings should be stored in your project's configuration. By default, SQLite is used as it does not require a server to be running.

Connection settings in config.ini

The config.ini configuration file of your project stores all of the database connection settings within the [database] section. Read more about project config in the Configuration section.

Example config.ini for connecting to a local MySQL database:

[database]
driver=mysql
host=localhost
schema=MyApp
port=3306
username=app_user
password=app_pass

You can connect to an existing database, or use database migrations to create a new one. See below for details on migrations.

Performing queries

A reference to the Database object can only be obtained from a Logic class, via $this->database. This allows you, the developer, to control which areas of the codebase have access to the database. Using QueryCollections further enhances your organisation and control over data, by allowing certain areas of the codebase to have access to only certain areas of the database. See the section on QueryConnections below for more information.

To perform a query, a query file should be placed within the query/ directory. For example query/getArticleById.sql can be called from a Logic class like this:

public function go() {
	$id = 123;
	$row = $this->database->fetch("getArticleById", $id);
	// Do something with $row.
}

Database operation functions

• fetch - returns a Row object, or null if there is no row returned by the database when executing the provided query.
• fetchAll - returns a ResultSet object, which is a representation of zero or more Row objects that match the provided query.
• insert - returns an int of the number of affected rows.
• update - returns an int of the number of affected rows.
• delete - returns an int of the number of affected rows.

There is also an executeSql function that makes it possible to pass in a string of SQL to execute, rather than the name of a query, however this is discouraged as it breaks the separation of concerns that a Page Logic and Query have.

Query collections

Rather than storing all queries directly inside the query directory, query files can be bundled together in directories called "Query Collections" which keeps things tidy, but also provides the benefit of passing classes reference to individual collections, rather than providing all classes access to the whole database.

// TODO.

Binding data parameters

There are two mechanisms for binding data to parameters in your queries: named and ordered parameter binding.

Named parameter binding is where the SQL has named placeholders where dynamic content is required, such as select email from user where username like :searchTerm limit :limit offset :offset. Named parameters are indicated by a colon character before the word.

Ordered parameter binding is where the SQL has question mark placeholders where dynamic content is required, such as select email from user where region = ?.

All database operation functions have the same parameter rules:

• There must always be at least one parameter: the query name
• For queries with named parameters, the second parameter is a key-value data structure
• For ordered parameters, the function takes variable arguments and binds the parameters in order

// TODO: Bind iterable key-value pairs to named parameters or single variables to question marks within query.

// TODO: Named parameters bound using key-value pairs.

// TODO: Question marks bound in order, passed into query function as variable arguments.

// TODO: Use of :orderBy and :limit, and why these have been implemented by Gt and not PDO.

Working with ResultSet objects

// TODO: Iterating, counting, etc.

// TODO: Casting ResultSets to custom objects (manual for now, as issue #80 is still in question form).

Migrations

// TODO: numbered queries

// TODO: migration integrity hashes

SQL Builder

You may choose to write all queries as SQL files, but this is sometimes quite limiting and can lead to repetitive queries. Instead, SQL can be represented in object oriented form using the SQL Builder.

// TODO.