2.3 Database model |
The database contains 5 tables: nodes, node_types, node_data, node_data_types and node_data_type_data_types.
This schema does not describe Kaolak in a physical way but it does reflect its abstraction and flexibility.
It could even be smaller by taking off table node_types and using a node and its node_data to define node types but I felt it would be harder to manage.
Below are description of tables with row examples. |
| Table nodes |
| Field |
Type |
Info |
Use |
| id |
smallint(6) unsigned |
primary key, not null, auto_increment |
|
| user_id |
smallint(5) unsigned |
index, default 0 |
node creator |
| ts |
int(20) unsigned |
default null |
unix_timestamp, node creation date |
|
| Table node_types |
| Field |
Type |
Info |
Use |
| id |
smallint(4) unsigned |
primary key, not null, auto_increment |
|
| name |
varchar(40) |
index, not null, unique |
|
|
| Table node_data |
| Table gets altered on table node_data_type_data_types inserts. See Kaolak::addNodeType() in API - Object model |
| Field |
Type |
Info |
Use |
| node_id |
smallint(6) unsigned |
primary key, not null default 0 |
foreign key: nodes.id |
| data_type_id |
smallint(4) unsigned |
primary key, not null, default 0 |
foreign key: node_data_types.id |
| lang_id |
smallint(4) unsigned |
primary key, not null, default 0 |
foreign key: nodes.id where nodes[id][type]=lang |
| user_id |
smallint(6) unsigned |
index, not null, default 0 |
foreign key: nodes.id where nodes[id][type]=user; user_id is last modificator |
| sort_order |
smallint(2) unsigned |
not null, default 0 |
when data has several values (eg parent) sort_order is used |
| ts |
int(20) |
|
unix_timestamp, last modification date |
| comment |
varchar(100) |
|
might be of some use |
|
| After running /kaolak/setup/kaolak-create.php, the following columns are added, with a single one of them containig data on inserts: |
| Field |
Type |
Info |
Use |
| data_int |
int(11) |
|
|
| data_float |
float |
|
|
| data_bool |
tinyint(2) |
|
|
| data_str |
varchar(255) |
|
|
| data_date |
int(20) |
|
unix_timestamp |
|
| Table node_data_types |
| Field |
Type |
Info |
Use |
| id |
smallint(4) unsigned |
primary key, not null, auto_increment |
|
| name |
varchar(40) |
index, not null, unique |
|
type_id |
smallint(4) unsigned |
not null, default 0 |
foreign key: node_types.id |
| type_data_type_id |
smallint(4) unsigned |
not null, default 0 |
foreign key: node_data_type_data_types.id |
|
| Table node_data_type_data_types |
| Field |
Type |
Info |
Use |
| id |
smallint(4) unsigned |
primary key, not null, auto_increment |
|
| name |
varchar(40) |
index, not null, unique |
column name in table node_data |
|
| Examples |
| Below is some of the data inserted through /kaolak/setup/kaolak-create.php : |
| table nodes |
| id |
user_id |
| 1 |
0 |
| 2 |
1 |
| 3 |
1 |
| 4 |
1 |
| 5 |
1 |
| 6 |
1 |
| 7 |
1 |
| 8 |
1 |
| 9 |
1 |
| 10 |
1 |
|
| table node_types |
| id |
name |
| 1 |
user |
| 2 |
module |
| 3 |
message |
| 4 |
branch |
| 5 |
lang |
| 6 |
session |
|
| table node_data_types |
| id |
name |
type_id |
data_type_id |
| 1 |
type |
0 |
2 |
| 2 |
is_group |
0 |
1 |
| 3 |
permission_read |
0 |
2 |
| 4 |
permission_write |
0 |
2 |
| 6 |
parent |
0 |
2 |
| 7 |
branch |
0 |
2 |
| 8 |
name |
0 |
4 |
| 10 |
username |
1 |
4 |
| 30 |
max_size_MB |
4 |
2 |
| 34 |
session_timeout |
4 |
2 |
|
| table node_data_type_data_types |
| id |
name |
| 1 |
data_bool |
| 2 |
data_int |
| 3 |
data_float |
| 4 |
data_str |
| 5 |
data_txt |
| 6 |
data_date |
|
|
| table node_data |
| node_id |
data_type_id |
lang_id |
user_id |
sort_order |
ts |
data_int |
data_str |
data_bool |
| 1 |
1 |
0 |
0 |
0 |
1055174215 |
1 |
null |
null |
| 1 |
10 |
0 |
0 |
0 |
1055174215 |
null |
kaolak |
null |
| 1 |
11 |
0 |
0 |
0 |
1055174215 |
null |
26c300617946c8d1 |
null |
| 11 |
1 |
0 |
1 |
0 |
1055174216 |
4 |
null |
null |
| 11 |
3 |
0 |
1 |
0 |
1055174216 |
0 |
null |
null |
| 11 |
4 |
0 |
1 |
0 |
1055174216 |
1 |
null |
null |
| 11 |
4 |
0 |
1 |
0 |
1055174216 |
13 |
null |
null |
| 11 |
7 |
0 |
1 |
0 |
1055174216 |
11 |
null |
null |
| 11 |
8 |
0 |
1 |
0 |
1055174216 |
null |
test |
null |
| 11 |
30 |
0 |
1 |
0 |
1055174216 |
14 |
null |
null |
| 11 |
34 |
0 |
1 |
0 |
1055174216 |
20 |
null |
null |
| 13 |
1 |
0 |
1 |
0 |
1055174216 |
1 |
null |
null |
| 13 |
2 |
0 |
1 |
0 |
1055174216 |
null |
null |
1 |
| 13 |
3 |
0 |
1 |
0 |
1055174216 |
0 |
null |
null |
| 13 |
4 |
0 |
1 |
0 |
1055174216 |
1 |
null |
null |
| 13 |
7 |
0 |
1 |
0 |
1055174216 |
11 |
null |
null |
| 13 |
8 |
0 |
1 |
0 |
1055174216 |
null |
web_publishers |
null |
|
|
From the data contained above we get:
node 1 :
row 1: type = 'user'
row 2: username = 'kaolak'
row 3: password (encrypted) = '26c300617946c8d1'
node 11 :
row 1: type = 'branch'; created by = 'kaolak' on 'June 7 2003 16:34:49'
row 2: read permission = 0 (all)
row 3: write permission = 1 (superadmin only)
row 4: write permission = 13 (publishers group see below)
row 5: branch = 11 (redundant but usefull - see API)
row 6: name = 'test'
row 7: maximum size = 14MB
node 13 :
row 1: type = 'user'; created by = 'kaolak' on 'June 7 2003 16:34:49'
row 2: is group = 1
row 3: read permission = 0 (all)
row 4: write permission = 1 (kaolak only)
row 5: branch = 11
row 6: name = 'web_publishers'
|
