Base

Database feature.


  • Private repository access.

Download Buy

5 stars out of 5

2 0 0 0 0
  • Author Taufik Nurrohman
  • Maintainer 1
  • Member 1
  • Version 1.0.0
Table of Contents
  1. WAL Mode
  2. Usage
  3. Tables
    1. Asset
    2. Comment
    3. Page
    4. State
    5. Tag
    6. User
    7. View
  4. Panel
    1. Search

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.

blob A column with BLOB data type.
data A column with JSON data type. Value must be a valid JSON string or NULL.
id∗ A column with INTEGER data type. This is the primary key.
route∗ A column with TEXT data type. Value must start with /. This column is also the primary key.
size A column with INTEGER data type.
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.

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.
data A column with JSON data type. Value must be a valid JSON string or NULL.
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. If this column is not populated during the insert process, its value will be populated with 1.
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.
x A column with TEXT data type. Historically, the value of a page property with this column name was an extension of the page file. It was usually set to 'archive', 'draft', or 'page'. If this column is not populated during the insert process, its value will be populated with 'page'.

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.
data A column with JSON data type. Value must be a valid JSON string or NULL.
description A column with TEXT data type.
id∗ A column with INTEGER data type. This is the primary key.
kind A column with JSON data type. Value must be a valid JSON (array) string or NULL.
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.
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.
x A column with TEXT data type. Historically, the value of a page property with this column name was an extension of the page file. It was usually set to 'archive', 'draft', or 'page'. If this column is not populated during the insert process, its value will be populated with 'page'.

State

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

key∗ A column with TEXT data type. This is the primary key.
type A column with TEXT data type.
value A column with TEXT data type.

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.

author A column with TEXT data type.
content A column with TEXT data type.
data A column with JSON data type. Value must be a valid JSON string or NULL.
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.
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.
x A column with TEXT data type. Historically, the value of a page property with this column name was an extension of the page file. It was usually set to 'archive', 'draft', or 'page'. If this column is not populated during the insert process, its value will be populated with 'page'.

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.
data A column with JSON data type. Value must be a valid JSON string or NULL.
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. If this column is not populated during the insert process, its value will be populated with 3.
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.
user A column with INTEGER data type. This column is associated with the id column of the same table.
x A column with TEXT data type. Historically, the value of a page property with this column name was an extension of the page file. It was usually set to 'archive', 'draft', or 'page'. If this column is not populated during the insert process, its value will be populated with 'page'.

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.
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 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.

QueryCommand
abcPerform a data search on all columns (except columns of type BLOB) that contain the word “abc” in them.
123Perform a data search on all columns (except columns of type BLOB) that contain the number “123” in them.

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

QueryCommand
name:abcPerform a data search on column name that contains the word “abc” in it.
name:!abcPerform a data search on column name that does not contain the word “abc” in it.
name:=abcPerform a data search on column name that contains the exact value of “abc” in it.
name:==abcThis is the same as query name:=abc.
name:!=abcPerform a data search on column name that does not contain the exact value of “abc” in it.
id:123Perform a data search on column id that contains the number “123” in it.
id:=123Perform a data search on column id that contains the exact value of “123” in it.
id:!123Perform a data search on column id that does not contain the number “123” in it.
id:==123This is the same as query id:=123.
id:!=123Perform a data search on column id that does not contain the exact value of “123” in it.
id:<123Perform a data search on column id that contains the number value that is less than “123” in it.
id:<=123Perform a data search on column id that contains the number value that is less than or equal to “123” in it.
id:>123Perform a data search on column id that contains the number value that is greater than “123” in it.
id:>=123Perform a data search on column id that contains the number value that is greater than or equal to “123” in it.
id:<>123This is the same as query id:!=123.
id:>123 name:abcPerform a data search on column id that contains the number value that is greater than “123” in it, and on column name that contains the word “abc” in it.

0 Comments

No comments yet.