database - PhpGt/WebEngine GitHub Wiki

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.

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.

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

Available configuration keys:

• driver - the name of the driver to connect to. Available options: cubrid, dblib, sybase, firebird, ibm, informix, mysql, sqlsrv, oci, odbc, pgsql, sqlite, 4D.
• host - the host name of the database to connect to e.g. localhost, or db.example.com
• schema - the name of the schema within the database to use, e.g. my_app_db
• port - the port number to connect with
• username - the username to connect with
• password - the password for the supplied username

// TODO: Confirm these keys in unit tests:

• query_directory - The base directory that contains all query files, relative to the project root
• migration_path - The directory that contains all database migrations, relative to the query_directory
• migration_table - The name of the table within the database to store information about the current migrations

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

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

All query files should be located within the base query/ directory, unless configured elsewhere. 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.
}

• 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.
• 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, traversed as an iterable.

If your query only returns one column, it is possible to return the individual column in a type safe way using the following functions:

• fetchBool
• fetchString
• fetchInt
• fetchFloat
• fetchDateTime

The following similar functions will return an array of typed values for queries that return a result set of rows with a single column:

• fetchAllBool
• fetchAllString
• fetchAllInt
• fetchAllFloat
• fetchAllDateTime

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.

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 database encapsulation by passing references to individual collections, rather than providing access to the whole database to all application code.

A query collection can have any name and contain any queries, because it is just a directory containing files, but a good convention is to name a query collection according to the tables that are manipulated by the contained queries.

For example, a "user" query collection can contain queries such as "getById", "insert", "setLoggedIn" that interact with the "user" table, and an "order" query collection can contain queries such as "getById", "create", and "getForUser", but this is only by convention.

When performing database operation functions, queries within query collections are referenced by using dot-separation or slash-separation. For example:

public function go() {
	$id = 123;
	$userRow = $this->database->fetch("user.getById", $id);
}

The above example will execute the query at query/user/getById.sql, passing in the value of $id as the first unnamed bind parameter (indicated by a ? in the SQL).

One benefit of working with query collections is that database access can be provided to other classes so that they only have access to a certain query collection, rather than the whole database. This promotes the object-oriented concept of encapsulation to the database layer, which typically doesn't work in an object-oriented manner.

To obtain a reference to a QueryCollection, call $database->queryCollection("example");. The returned object can now be passed to a class that requires access to the queries located within the "example" query collection, and the fetch functions can be called as usual: The database object provides the functions fetch, fetchAll, etc. but these functions are available on the individual QueryCollection objects themselves.

function go() {
	// Obtain a reference to the encapsulated "user" QueryCollection:
	$userDb = $this->database->queryCollection("user");
	// Pass it to an object that deals with database access for the user: 
	$this->userRepository->signup($userDb);
	// That object can access the "user" part of the database, 
	// but can't execute any other queries outside of that collection.
}

Passing QueryCollection objects is particularly useful when working with the Repository-Entity pattern.

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 in the SQL.

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

All database operation functions (fetch, fetchAll, etc.) 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, such as an associative array
• 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.

// TODO: Iterating, counting, etc.

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

// TODO: numbered queries

// TODO: migration integrity hashes

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.