Base · Mecha CMS

Table of Contents

WAL Mode
Usage
Tables
1. Comment
2. Page
3. Tag
4. User
5. View

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. With this feature, you can assist and teach your clients to manage their own databases. Once they are well educated, you can hand over the tasks to your clients, so you can focus more on technical things like integrating the data with their web sites, or creating a more user friendly data entry form for the web site.

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

foreach ($base->rows('SELECT * FROM "page" WHERE "parent" = ?', [$_GET['parent']]) as $row) {
    // Make it look like a file that exists
    $row['exist'] = true;
    // Use the `status` value to create a fake file extension
    $row['x'] = [
        0 => 'draft',
        1 => 'page',
        2 => 'archive'
    ][$row['status'] ?? 0] ?? 'draft';
    // Create a page object from the raw data
    $page = Page::from($row);
    // Use the page object in the usual way
    echo '<h3>' . $page->title . '</h3>';
    echo '<div>' . $page->content . '</div>';
}

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 comment, page, tag, user, and view. Their presence should be enough to create a personal blog or news type web site.

Comment

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

`author`	A column with `TEXT` data type.
`content`	A column with `TEXT` data type.
`email`	A column with `TEXT` data type.
`id∗`	A column with `INTEGER` data type. This is the primary key.
`link`	A column with `TEXT` data type.
`page∗`	A column with `INTEGER` data type. This column is associated with the `id` column of the `page` table. This column is also the primary key.
`parent`	A column with `INTEGER` data type. This column is associated with the `id` column of the same table.
`status`	A column with `INTEGER` data type. Assume the value in it as a constant. You can use any number but they must function as the status of the comment. For example, a status of `0` indicates that the comment is currently on hold, a status of `1` indicates that the comment has been published, a status of `2` indicates that the comment has been archived, and a status of `-1` indicates that the comment has been marked as spam.
`time`	A column with `TEXT` data type. If this column is not populated during the insert process, its value will be populated with the current time stamp using the local time zone.
`type`	A column with `TEXT` data type.
`user`	A column with `INTEGER` data type. This column is associated with the `id` column of the `user` table.

Page

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

`author`	A column with `TEXT` data type.
`content`	A column with `TEXT` data type.
`description`	A column with `TEXT` data type.
`id∗`	A column with `INTEGER` data type. This is the primary key.
`link`	A column with `TEXT` data type.
`name`	A column with `TEXT` data type. Its value must be unique.
`parent`	A column with `INTEGER` data type. This column is associated with the `id` column of the same table.
`status`	A column with `INTEGER` data type. Assume the value in it as a constant. You can use any number but they must function as the status of the page. For example, a status of `0` indicates that the page is currently on hold, a status of `1` indicates that the page has been published, and a status of `2` indicates that the page has been archived.
`time`	A column with `TEXT` data type. If this column is not populated during the insert process, its value will be populated with the current time stamp using the local time zone.
`title`	A column with `TEXT` data type.
`type`	A column with `TEXT` data type.
`user`	A column with `INTEGER` data type. This column is associated with the `id` column of the `user` table.

Tag

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

`author`	A column with `TEXT` data type.
`content`	A column with `TEXT` data type.
`description`	A column with `TEXT` data type.
`id∗`	A column with `INTEGER` data type. This is the primary key.
`name`	A column with `TEXT` data type. Its value must be unique.
`parent`	A column with `INTEGER` data type. This column is associated with the `id` column of the same table.
`status`	A column with `INTEGER` data type. Assume the value in it as a constant. You can use any number but they must function as the status of the tag. For example, a status of `0` indicates that the tag is currently on hold, a status of `1` indicates that the tag has been published, and a status of `2` indicates that the tag has been archived.
`time`	A column with `TEXT` data type. If this column is not populated during the insert process, its value will be populated with the current time stamp using the local time zone.
`title`	A column with `TEXT` data type.
`type`	A column with `TEXT` data type.
`user`	A column with `INTEGER` data type. This column is associated with the `id` column of the `user` table.

User

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

`author`	A column with `TEXT` data type.
`content`	A column with `TEXT` data type.
`description`	A column with `TEXT` data type.
`email`	A column with `TEXT` data type.
`id∗`	A column with `INTEGER` data type. This is the primary key.
`link`	A column with `TEXT` data type.
`name`	A column with `TEXT` data type. Its value must be unique.
`pass`	A column with `TEXT` data type.
`status`	A column with `INTEGER` data type. Assume the value in it as a constant. You can use any number but they must function as the status of the user. For example, a status of `0` indicates that the user is currently on hold, a status of `1` indicates that the user is an administrator, a status of `2` indicates that the user is an editor, and a status of `3` indicates that the user is a guest.
`time`	A column with `TEXT` data type. If this column is not populated during the insert process, its value will be populated with the current time stamp using the local time zone.
`token`	A column with `TEXT` data type.
`type`	A column with `TEXT` data type.

View

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

`count`	A column with `INTEGER` data type. If this column is not populated during the insert process, its value will be populated with `1` as the default value.
`id∗`	A column with `INTEGER` data type. This column is associated with the `id` column of the `page` table. This is the primary key.

There are currently no specific extensions and layouts that will use those tables. They are made only as a starting point to make it easier for you to create other tables by imitating the table structure that 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.