One of the reasons I created my own CMS is because of the complex database of existing CMSs. Therefore, when I designed this CMS, I aimed to make the underlying database structure simple, portable and manageable.

Bearweb CMS is database-driven, each module has its own private database. Bearweb CMS is designed with SQLite in mind, but should be easily adapt to other relational DBMS. For SQLite, each database is a file; therefore, using dedicated database (file) for each module should minimize congestion.

Bearweb CMS has a very simple database structure. It has 3 modules (sitemap, session and transaction, user), it relies on only 4 databases in 3 SQLite database files:

• Sitemap - Each record represents a resource, it can be a webpage, a file, or an API.
• Session - A session is a series of requests from one client to the server.
• Transaction - A transaction represents a HTTP request from client to server and a response from the server to the client.
• User - Each record represents a registered user.

In Bearweb CMS, modules are database-backed classes instances. An instance is a row in the table. Each variable in the instance can be mapped to a field in the module's database table, with the same field name for variable name. int will be saved as integer in the database, human-readable string will be saved as text, array and object will be saved as JSON text, binary string will be saved as blob.

Bearweb CMS is portable. It is designed to use a portable DBMS, the SQLite. Transferring the website from one machine to another requires copying and pasting the PHP files (the CMS) and the database files (the data).

Although it is recommended to modify the website through APIs, we all like the quick-and-dirty way. The database is very managementable, by design, there is no foreign key or trigger. You can directly make modifications to the database to make changes to the website, as long as you:

• Respect the data type. When making changes to a field, you should use the same data type. For example, Sitemap.create wants a timestamp in int, you should provide a timestamp in int (for example: 0) instead of a string (for example: 1970-01-01 00:00:00).
• Respect the JSON format. Bearweb CMS relies heavily on JSON to store key-value pairs, such as Sitemap.meta and User.group. If the JSON format is damaged, Bearweb CMS cannot parse it correctly.
• I recommend using DB browser for SQLite to manage the database. It prevents changing variable type or breaking JSON format by accident. It provides syntax highlighting, formatting and compressing (remove formatting spaces) for JSON.

Don't worry. If Brarweb CMS senses a data in bad format, it will use the default value.

Requesting a webpage, a file (for example, an image), or any other resource is in fact fetching data from the database, with access control and some data manipulation. This database is called Sitemap because the table is a map of the website.

CREATE TABLE "Sitemap" (
	"url"		TEXT NOT NULL UNIQUE,
	"category"	TEXT NOT NULL DEFAULT '',
	"template"	TEXT NOT NULL DEFAULT '["object", "blob"]',
	"owner"		TEXT NOT NULL DEFAULT '',
	"create"	INTEGER NOT NULL DEFAULT 0,
	"modify"	INTEGER NOT NULL DEFAULT 0,
	"meta"		TEXT NOT NULL DEFAULT '{}',
	"content"	BLOB DEFAULT '',
	"aux"		TEXT NOT NULL DEFAULT '{}',
	PRIMARY KEY("url")
) WITHOUT ROWID
	

meta and aux store optional data. It doesn't make sense to create seperate database field for each of those optional data (which will make the table extremely wide and unmanageable, and create a lot of unused field); therefore, they are aggregated into JSON objects.

All those data are optional. Bearweb will use default value or skip it if a specific data is not given.

All those data are optional. Bearweb will use default value or skip it if a specific data is not given.

A transaction represents a HTTP request from the client-side to the server-side and the corresponding response from the server-side to the client-side.

For Bearweb CMS, a request can be divided into 3 parts:

• URL: A request must include a URL (this is also mandatory for HTTP, the first line of a HTTP request is always METHOD URL VERSION, for example: GET /bearweb-resource-optimization/zh HTTP/1.1). It tells the server what resource (or API) the client wants.
• Session token: HTTP is stateless, which means that the server cannot know who sent the request. To differentiate clients, the server will issue a session token (a cookie) in the first transaction. The client should always include this token in all subsequent transactions to identify the client’s session. If the client disables the cookie, or omits the session token cookie, the server will issue a new one.
• Data: The request can optionally include some data. This is used in APIs.

For Bearweb CMS, a session is a series of transactions from the same client.

• Session token in cookie is used to identify the transaction’s corresponding session. As discussed above.

Bearweb CMS uses two databases to record transactions and sessions.

CREATE TABLE "Transaction" (
	"id"		TEXT NOT NULL UNIQUE,
	"create"	INTEGER NOT NULL DEFAULT -1,
	"ip"		TEXT NOT NULL DEFAULT '',
	"url"		TEXT NOT NULL DEFAULT '',
	"status"	INTEGER NOT NULL DEFAULT 0,
	"time"		INTEGER NOT NULL DEFAULT -1,
	"memory"	INTEGER NOT NULL DEFAULT -1,
	"session"	TEXT NOT NULL DEFAULT '',
	"log"		TEXT NOT NULL DEFAULT '',
	PRIMARY KEY("id")
) WITHOUT ROWID,STRICT
	

CREATE TABLE "Session" (
	"id"		TEXT NOT NULL UNIQUE,
	"create"	INTEGER NOT NULL DEFAULT -1,
	"lastuse"	INTEGER NOT NULL DEFAULT -1,
	"user"		TEXT NOT NULL DEFAULT '',
	"key"		TEXT NOT NULL DEFAULT '',
	PRIMARY KEY("id")
) WITHOUT ROWID,STRICT
	

Session and Transaction Database Maintenance

To view the transaction and its corresponding session, use a view:

CREATE VIEW `Transaction_View` AS
	select all
		t.`id`,
		datetime(t.`create`, 'unixepoch') as `create`,
		t.`ip`,
		t.`url`,
		t.`status`,
		t.`time` as `time (us)`,
		t.`memory` as `memory (kB)`,
		t.`log`,
		t.`session`,
		datetime(s.`create`, 'unixepoch') as `firstuse`,
		datetime(s.`lastuse`, 'unixepoch') as `lastuse`,
		s.`user`,
		s.`key`
	from `Transaction` t join `Session` s where t.`session` = s.`id`
order by `create` desc
	

The transaction and session database grows over time. You may want to occasionally clean expired sessions and old transactions:

delete from `Session` where strftime('%s', 'now') - `lastuse` > 30 * 24 * 3600;
delete from `Transaction` where strftime('%s', 'now') - `create` > 30 * 24 * 3600;
vacuum;
	

Bearweb saves user in a database.

CREATE TABLE "User" (
	"id"		TEXT NOT NULL UNIQUE,
	"name"		TEXT NOT NULL DEFAULT '',
	"salt"		TEXT NOT NULL DEFAULT ':',
	"password"	TEXT NOT NULL DEFAULT ':',
	"registertime"	INTEGER NOT NULL DEFAULT 0,
	"lastactive"	INTEGER NOT NULL DEFAULT 0,
	"group"		TEXT NOT NULL DEFAULT '{}',
	"data"		TEXT NOT NULL DEFAULT '{}',
	PRIMARY KEY("id")
) WITHOUT ROWID,STRICT