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 TfDatabase 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 TfCriteria and TfCriteriaItem objects, which are used to compose queries. Basically you start by instantiating a TfCriteria object via a factory, then adding conditions to it as TfCriteriaItem objects from another factory. 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 criteria object via $tfCriteriaFactory.
$criteria = $tfCriteriaFactory->getCriteria();

// 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($tfCriteriaFactory->getItem('type', 'TfArticle'));
$criteria->add($tfCriteriaFactory->getItem('online', '1'));

// Limit results to 10 records.
$criteria->setLimit(10);

// Sort records by submissionTime in descending order (for ascending use 'ASC').
$criteria->setOrder('submissionTime');
$criteria->setOrdertype('DESC');

// Pass criteria to the article-specific content handler and execute the query.
$articles = array();
$contentHandler = $contentFactory->getContentHandler('content');
$articles = $contentHandler->getObjects($criteria);

/** If you want to specifically retrive blocks, collections or tags only use a specific handler subclass instead **/

It is also possible to dispense with handlers and write custom queries at a lower level using the methods offered by the TfDatabase 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 TfContentHandler) for examples.

So long as you stick to the methods in TfDatabase 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.