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 \Tfish\Database 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, interaction with the database is confined to the model classes, which should contain methods to prepare and despatch queries to the database for a given route. The viewmodel classes call methods on the models, retrieve data for insertion to the templates.

The normal way of setting conditions on database operations is through the \Tfish\Criteria and \Tfish\CriteriaItem objects, which are used to compose queries. Basically you start by instantiating a Criteria object via a call to \Tfish\CriteriaFactory, then adding conditions to it as CriteriaItem objects from the same 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, to return the last ten articles from the database:

/** Preparing criteria to retrieve the last ten online articles from the database. **/
$criteria = $this->criteriaFactory->criteria();
$criteria->add($this->criteriaFactory->item('type', 'TfArticle')); // Select articles.
$criteria->add($this->criteriaFactory->item('onlineStatus')); // Select online content only.
$criteria->setSort('submissionTime'); // Sort results by submission time...
$criteria->setOrder('DESC'); // ...in descending order.
$criteria->setLimit(10); // Retrieve maximum 10 items.

// Prepare the statement for execution on the content table.
$statement = $this->database->select('content', $criteria);

// Execute the statement reading the rows into instances of the \Tfish\Content\Entity\Content class.
// This essentially returns an array of content objects.
return $statement->fetchAll(\PDO::FETCH_CLASS, '\Tfish\Content\Entity\Content');

Retrieving a single object is even simpler:

/** Preparing criteria to retrieve a single content item based on its ID, eg. '52'. **/
$criteria = $this->criteriaFactory->criteria();
$criteria->add($this->criteriaFactory->item('id', 52)); // Select content item with id 52.
$criteria->add($this->criteriaFactory->item('onlineStatus', 1)); // Only return content if it is marked online.

// Prepare the statement for execution on the content table.
$statement = $this->database->select('content', $criteria);

// Execute the statement and return results as an instance of the \Tfish\Content\Entity\Content class.
$content = $statement->fetchObject('\Tfish\Content\Entity\Content');

// If you subsequently want to perform a write operation (eg. counter) close the cursor first.
$statement->closeCursor();

That's the basics of it. I suggest you have a look through \Tfish\Content\Model\Listing for a more comprehensive example, which includes validation of parameters and other operations such as counts and writes. You should also take a look at the methods available in the \Tfish\Database class to get a sense of what functionality is available (basically all your standard CRUD operations plus a few utilities).

It is also possible to write custom queries at a lower level using the native functionality of PDO, or in combination with \Tfish\Database methods. This should not usually be necessary as the query composer infrastructure (criteria) are sufficient to prepare most queries, but if you want to do something complicated, you can.

So long as you stick to the methods in \Tfish\Database you should be safe from SQL injection as they exclusively use PDO with bound parameters, 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.

While working with your database I suggest setting up DB Browser for SQLite. It will let you see and modify the contents of your database tables directly.