Database

[taufik-nurrohman]

In the future, if page’s data have become standard and familiar, I want to develop Mecha further by providing an optional database feature. Considering that page’s data are very dynamic, I want a database system that supports dynamic column features like this one.

This kind of feature allows me to add custom fields as I like, while still being able to have default columns whose the length will not change. I will use the standard column names for sorting and filtering purposes.

create table comment (
  `author`,
  `content`,
  `email`,
  `id` primary key,
  `link`,
  `name`,
  `parent`,
  `status`,
  `time`,
  `type`,
  `x`,
  `data` blob -- Here comes the custom fields storage!
);

create table page (
  `author`,
  `chunk`,
  `content`,
  `description`,
  `id` primary key,
  `kind`,
  `link`,
  `name`,
  `sort`,
  `state`,
  `time`,
  `title`,
  `type`,
  `x`,
  `data` blob -- Here comes the custom fields storage!
);

create table tag (
  `author`,
  `content`,
  `description`,
  `id` primary key,
  `name`,
  `time`,
  `title`,
  `type`,
  `x`,
  `data` blob -- Here comes the custom fields storage!
);

create table user (
  `author`,
  `content`,
  `description`,
  `email`,
  `id` primary key,
  `link`,
  `name`,
  `pass`,
  `state`,
  `status`,
  `time`,
  `title`,
  `token`,
  `type`,
  `x`,
  `data` blob -- Here comes the custom fields storage!
);

[taufik-nurrohman]

As times goes by, I realized that I can just add/remove/rename a table and its columns as I like. Starting from SQLite v3.25.0 the feature is available via ALTER TABLE.

Why SQLite? I decided to use SQLite for some reasons:

1. It shipped by default with the PHP system. No need to install the feature manually.
2. No need to provide user and password data to access the database.
3. Database is embedded in the file system. Backing up a SQLite database system should be easy as we can just copy and paste the database just like regular files.

[yuliakbard]

kalau kita buat tombol like dislike itu, masuk ke penggunaan database ya kang?

[taufik-nurrohman]

Pakai flat-file bisa mas. Dulu ada ekstensinya namanya @mecha-cms/x.poll. Sampai sekarang belum Saya perbarui karena masih tidak ada ide untuk lebih menyederhanakannya lagi.

[taufik-nurrohman]

There are many third-party alternative features that allow me to form a beautiful SQL query like this:

$page = Base::table('page')->where('id', '=', 24)->first();

Or like this:

$page = $base->table('page')->where('id', '=', 24)->first();

But after I did a reflection back on my decision when releasing version 2.2.0 in the past is that this feature might trap me in a situation like when there is still a class Form available in the core.

SQLite is not that complicated and I might be able to create a database extension that provides simply a static function to query the database:

$base = new SQLite3('.\lot\base\table.db');

function base(string $query, array $lot = []) {
    $base = $GLOBALS['base'];
    $q = $base->prepare($query);
    if (array_is_list($lot)) {
        foreach ($lot as $k => $v) {
            // Positional numbering starts at `1`
            $q->bindValue($k + 1, is_array($v) ? json_encode($v) : $v);
        }
    } else {
        foreach ($lot as $k => $v) {
            $q->bindValue(':' . $k, is_array($v) ? json_encode($v) : $v);
        }
    }
    $rows = $q->execute();
    while ($row = $rows->fetchArray(SQLITE3_ASSOC)) {
        yield $row;
    }
}

Usage would be something like this:

$pages = new Pages(y(base('SELECT * FROM "page" WHERE "route" LIKE ? ORDER BY "time" DESC LIMIT 20 OFFSET ?', ['/article/%', 0]))); // First 20 pages chunk

$page = new Page(null, base('SELECT * FROM "page" WHERE "route" = ?', ['/article/lorem-ipsum'])->current() ?? []); // Single page

Table would look like this:

CREATE TABLE "page" (
  "content" TEXT,
  "description" TEXT,
  "id" INTEGER PRIMARY KEY,
  "link" TEXT,
  "route" TEXT,
  "time" TEXT,
  "title" TEXT,
  "type" TEXT
)

content	description	id	link	route	time	title	type
<p>Lorem ipsum dolor sit amet.</p>	Lorem ipsum dolor sit amet.	1		/article/lorem-ipsum	2022-02-27 12:52:00	Lorem Ipsum	HTML
…	…	…	…	…	…	…	…
…	…	…	…	…	…	…	…
…	…	…	…	…	…	…	…

[yuliakbard]

apakah nanti extension ini memungkinkan menyimpan tags, category, fitur pencarian secara global ya kang? penyimpanannya gak berdasarkan folder-folder-an lagi ya kang. karena mecha versi yang sekarang masih terbatas di lingkup folder page nya aja

[taufik-nurrohman]

Bisa mas, tapi ya itu... semasa dev harus bikin semuanya sendiri. DIY.