Querying the database

5 February 2018 | 40 views | Tags: Documentation

The database

Tuskfish uses SQLite, which is embedded within PHP so there isn't a separate database server. The only requirement is that the PDO_SQLITE driver is installed. The database is stored as a simple text file in trust_path/database/your_database.db, so when you backup the file system your database will be backed up with it.

Access to SQLite databases is controlled solely by file permissions, so you must set them correctly (chmod 0600): Only the server needs to be able to read and write to the database. As per the installation instructions, you should also ensure that your trust_path folder lies outside of your web root, to prevent people from trying to access the database file via their browser.

Composing queries

Database access is handled by the TfishDatabase class. Queries are exclusively constructed via PDO, using prepared statements with bound values to mitigate SQL injection attacks. PDO allows you to prepare queries using placeholders instead of actual parameters, ensuring that the data you pass in is always escaped properly.

Usually there is no need to interact with TfishDatabase directly as the object handler classes will "handle" that within their own methods. For example a handler will usually come with a getObjects($criteria) method, and you just need to tell it what sort of things you want.

The normal (indirect) way of setting conditions on database operations is through the TfishCriteria and TfishCriteriaItem objects, which are used to compose queries. Basically you start by instantiating a TfishCriteria object, then adding conditions to it as TfishCriteriaItem objects. You can also set some properties directly on the criteria such as a query limit and offset, sort order or subject tag filter. For example:

/** Preparing criteria to retrieve the last ten articles from the database. **/

// Initialise a new TfishCriteria object to hold query conditions.
$criteria = new TfishCriteria();

// Add conditions to retrieve online articles. The third parameter is the operator, if you do
// not specify one (as is the case here) it defaults to '=', see permittedOperators() for a list.
$criteria->add(new TfishCriteriaItem('type', 'TfishArticle'));
$criteria->add(new TfishCriteriaItem('online', '1'));

// Limit results to 10 records.
$criteria->limit = 10;

// Sort records by submission_time in descending order (for ascending use 'ASC').
$criteria->order = 'submission_time';
$criteria->ordertype = 'DESC';

// Pass criteria to the article-specific content handler and execute the query.
$articles = array();
$articles = TfishArticleHandler::getObjects($criteria);

/** If you want to retrive mixed content types use the generic TfishContentHandler instead **/

It is also possible to dispense with handlers and write custom queries at a lower level using the methods offered by the TfishDatabase class. This should not be necessary as object handlers should provide most of the functionality that you need, but you may find it useful in some circumstances. Please refer to the API documentation and handlers (especially TfishContentHandler) for examples.

So long as you stick to the methods in TfishDatabase you should be safe, but if you want to start writing your own queries I recommend having a very careful read of (The only proper) PDO tutorial and The Hitchhiker's Guide to SQL Injection Prevention. Given that PDO is so important, it's kind of amazing how poorly documented it is.

While working with your database I suggest setting up phpLiteAdmin, which is basically like myPhpAdmin for SQLite. It will let you see and modify the contents of your database tables directly.

Copyright, all rights reserved.

Related

Tuskfish CMS Developer Guide

This guide will give you an overview of the architecture of Tuskfish CMS, how to write code to perform common operations and how to extend the system to suit yourself. The guide accompanies theĀ Tuskfish API documentation. Keep a copy handy as you read this guide. It is best to review links to the API where provided, as not every detail will be discussed in the text. This is the first version of the guide, so it is still a work in progress.