This project is currently a work in progress. Adding an SQLite feature is actually very easy. Almost all PHP installations will include the sqlite3 module by default, so you can use it right away without needing to do any special configuration. You pay for the database management system that integrates directly with the control panel. This extension is intended to help you to focus more on the technical things, such as integrating the data into the web site, or creating a more user-friendly data entry form for the web site in the front end.

SQLite database is stored as a single disk file. You can back up and move them as easily as you can copy and paste other files: a form of convenience that also bring some drawbacks. One of the most obvious is the read/write concurrency. Since the database is stored as a single file, writing can only be done once per user. Because when other users want to write data at the same time, the database file is already locked by the first user before he/she finished writing the data. This makes SQLite not ideal for recording data simultaneously by multiple external users that we usually have no control over.

But for reading, SQLite is actually more reliable than regular files, especially when the amount of data can become very large over time. The process of filtering and searching is not ideal when the data is stored as regular files and folders. Moving several number of small files and folders also takes more time compared to moving one very big file.

SQLite isn’t as robust as other database models which will usually have their own server to connect to, but it’s still better than plain text files when it comes to storing large amounts of data.

In conclusion, use file and folder structure to store the data if that suffices for your needs. If your needs in processing the data are increasingly complex so that it makes your application performance decreases over time, then switch to SQLite. Until SQLite is no longer able to process your data efficiently, then move on to a better database model. Or better yet, try using a third party database service that allows you to access the data using a ReST API. That way, your web site will get minimal work-load, and the data processing tasks can be assigned to other servers that host your databases.

WAL Mode

SQLite 3.7.0 introduced WAL mode feature which provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. The drawback of changing the default database journal mode to WAL is that it will create some kind of queue database file to be written once to the real database at certain intervals. This will probably make the transaction process a bit more complex.

As the developer of this extension, I would advise you to create at least two main databases where one uses the default journal mode (the DELETE journal mode), and the other uses the WAL journal mode.

Then, if you are going to create a table with a plan or assumption that the data in it will be read more often, add it to the database with the default journal mode. And if you are going to create a table with a plan or assumption that the data in it will be written more often, add it to the database with the WAL journal mode.

Usage

$row = $base->row('SELECT *, 1 AS "exist" FROM "page" WHERE "name" = ?', ['article']);
// Create a page object with the row data
$parent = Page::from($row);
// Use the page object as usual
echo '<h2>' . $parent->title . '</h2>';
echo '<p>' . $parent->description . '</p>';
foreach ($base->rows('SELECT *, 1 AS "exist" FROM "page" WHERE "parent" = ?', [$parent->id]) as $row) {
    // Create a page object with the row data
    $page = Page::from($row);
    // Use the page object as usual
    echo '<h3>' . $page->title . '</h3>';
    echo '<p>' . $page->description . '</p>';
}

Tables

Some databases will be created automatically the first time you install this extension. Tables will also be created automatically into the main database with some sample data to give you a little idea of how you should populate data into the tables. The tables are asset, comment, page, state, tag, user, and view. Their presence should be enough to create a personal blog or news type web site.

Asset

A table with two primary keys that you can use to store files.

Comment

A table with two primary keys that you can use to store comments in connection with a page.

Page

A table with a primary key that you can use to store pages.

State

A table with a primary key that you can use to store states data.

Nested key can be expressed using dot notation syntax, but this does not automatically result in a deep array/object when called with the SQL SELECT command. Application developers are free to transform the key structures into deep array/object data in any way they like. The same goes for the dot notation syntax used. Some may prefer the a[b][c] syntax over the a.b.c syntax, etc. There is also a type column to specify how the value of the value column (which is of type TEXT) should be transformed. There is no specific standard for naming the type, this is left entirely up to the data consumer. However, a generic suggestion for naming the type can be expressed using names as specified in the JSON and/or SQLite data types:

$state = [];
foreach ($base->rows('SELECT * FROM "state" ORDER BY "key"') as $row) {
    $key = $row['key'];
    $type = $row['type'];
    $value = $row['value'];
    if ('BOOL' === $type || 'BOOLEAN' === $type) {
        $value = strtolower($value);
        $value = 'true' === $value ? true : ('false' === $value ? false : (bool) $value);
    } else if ('FLOAT' === $type || 'REAL' === $type) {
        $value = (float) $value;
    } else if ('INTEGER' === $type) {
        $value = (int) $value;
    } else if ('JSON' === $type) {
        $value = From::JSON($value, true);
    } else if ('STRING' === $type || 'TEXT' === $type) {
        $value = (string) $value;
    } else {
        $value = Is::JSON($value) ? From::JSON($value, true) : e($value); // Automatic
    }
    set($state, $key, $value);
}

test($state);

Tag

A table with a primary key that you can use to store tags.

User

A table with a primary key that you can use to store users.

View

A table with a primary key that you can use to store page views, where the primary key is associated with a page.

There are currently no specific extensions and layouts that use those tables. They are only made as a starting point to make it easier for you to create other tables by imitating the table structure I recommend through those examples. The structure of the tables may change with every version update of the extension, but it will not change the existing structure of the tables unless you have deleted them before re-installing the extension.

Panel

Search

You can use the built-in search form in the main bar to filter the results when selecting a table. By default, you can type anything and it will try to match what you’re looking for in all columns except those of type BLOB. The search is also performed in columns of type INTEGER and REAL.

You can search for specific data in specific columns using advanced search syntax in the search form.