Nearly all fields of each item are stored in items table. The fulltext field is stored in separate table linked via items.id = fulltexts.ft_id. This is true for base items - items which is not feeded. When we fed item, we copy whole record in items table, but we do not copy fulltext (it means that it is possible to set title, summary ... in feeded item, but it is imposible to change fulltext - it is shared). The link to appropriate fulltext of fed items is provided by master_id. It points to id of base item so then (fed)items.master_id = fulltexts.ft_id. Because for base items is items.id = items.master_id (master_id points to itself), the relation items.master_id = fulltexts.ft_id is true for all items.
It is important to say, that all ids is 16 characters long and can contain any character except zero and ' character. Internaly we use this ids unpacked to 32 place hexadecimal number
Maybe you find strange we don't use boolean data types and many others. We only use smallint, datetime, varchar and mediumtext datatypes. The reason is based on Jirka Hejsek research on databases. This types are common to most database engines, so it should be easy to switch from one database to another one.
id varchar(16) NOT NULL,
master_id varchar(16) NOT NULL,
slice_id varchar(16) NOT NULL,
category_id varchar(16) NOT NULL,
status_code smallint(5) unsigned DEFAULT '0' NOT NULL,
language_code varchar(8) NOT NULL,
cp_code varchar(32) NOT NULL,
headline varchar(255) NOT NULL,
hl_href varchar(255) NOT NULL,
link_only smallint(5) unsigned DEFAULT '0' NOT NULL,
post_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
publish_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
expiry_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
abstract text,
full_text_old mediumtext,
img_src varchar(255),
img_width varchar(32),
img_height varchar(32),
html_formatted smallint(5) unsigned DEFAULT '0' NOT NULL,
source varchar(255),
source_href varchar(255),
redirect varchar(255),
place varchar(255),
highlight smallint(5) unsigned DEFAULT '0' NOT NULL,
posted_by varchar(255),
e_posted_by varchar(255),
created_by varchar(60) NOT NULL,
edited_by varchar(60),
last_edit datetime,
contact1 varchar(16),
contact2 varchar(16),
contact3 varchar(16),
edit_note varchar(255),
PRIMARY KEY (id),
KEY slice_id (slice_id),
KEY publish_date (publish_date)
Fulltexts table stores full texts of items. Tables are related by fulltexts.ft_id which points to items.id. fulltexts.ft_id always ponits to id of base item (base = not fed)
ft_id varchar(16) NOT NULL,
full_text mediumtext,
PRIMARY KEY (ft_id),
KEY id (ft_id),
UNIQUE id_2 (ft_id)
Slices table holds values for slices data (interesting, isn't?). There are two types of such data.
id varchar(16) NOT NULL,
headline varchar(255) NOT NULL,
short_name varchar(255) NOT NULL,
type varchar(20) DEFAULT 'en_news' NOT NULL,
slice_url varchar(255),
grab_len smallint(5) unsigned DEFAULT '0' NOT NULL,
post_enabled smallint(5) unsigned DEFAULT '0' NOT NULL,
created_by varchar(60),
created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
res_persID varchar(60),
export_to_all smallint(5) unsigned DEFAULT '0' NOT NULL,
deleted smallint(5) unsigned DEFAULT '0' NOT NULL,
fulltext_format mediumtext,
odd_row_format mediumtext,
even_row_format mediumtext,
even_odd_differ smallint(5) unsigned DEFAULT '0' NOT NULL,
compact_top varchar(255),
compact_bottom varchar(255),
category_sort smallint(5) unsigned DEFAULT '0' NOT NULL,
category_format mediumtext,
compact_remove varchar(255),
fulltext_remove varchar(255),
edit_fields varchar(40),
needed_fields varchar(40),
search_show varchar(15),
search_default varchar(10),
d_link_only smallint(5) unsigned DEFAULT '0',
d_highlight smallint(5) unsigned DEFAULT '0',
d_language_code varchar(8) NOT NULL,
d_cp_code varchar(32) NOT NULL,
d_category_id varchar(16) NOT NULL,
d_status_code smallint(5) unsigned DEFAULT '0' NOT NULL,
d_expiry_limit smallint(5) unsigned DEFAULT '0' NOT NULL,
d_expiry_date datetime,
d_hl_href varchar(255) NOT NULL,
d_source varchar(255),
d_source_href varchar(255),
d_redirect varchar(255),
d_place varchar(255),
d_listlen smallint(5) unsigned DEFAULT '0' NOT NULL,
d_html_formatted smallint(5) unsigned DEFAULT '0' NOT NULL,
d_img_src varchar(255),
d_img_width varchar(32),
d_img_height varchar(32),
d_posted_by varchar(255) NOT NULL,
d_e_posted_by varchar(255) NOT NULL,
PRIMARY KEY (id)
Constants tables are tables where several constants are stored. Most of these constants are shown to users in listboxes and most of these tables can't be changed by user - their modification is occasional only.
Each rule has its exception, so special constant table is categories. Categories table contains set of categories in which each item can belong. The slice administrator can set the subset of all categories, which will be used in the slice (see catbinds table). There will be set the set of common categoris. All slice administrators are recommended to use preset categories (the data exchange between slices and servers would be easier then), but there is possibility to create the new categories if slice administrator wants to.
id varchar(16) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
lt_cps is constant table which holds set of codepages names. Codepage should be used in HTML meta tag for character encoding settings (iso8859-1, windows-1250).
code varchar(32) NOT NULL,
w32cp varchar(64),
PRIMARY KEY (code)
lt_langs holds language names in which each item can be written.
code varchar(8) NOT NULL,
name varchar(64) NOT NULL,
altcode varchar(8),
PRIMARY KEY (code)
The relation tables keeps relation between two tables. Mostly they represents M:N relation between two tables like in case table slices and categories - one slice uses more categories, but one category can be used by more than one slice. This M:N relation is stored in catbinds table.
slice_id varchar(16) NOT NULL,
category_id varchar(16) NOT NULL,
KEY slice_id (slice_id)
Feedperms table is special type of relational table, because it keeps relation between records of the same table slices. This table holds kind of permission information - which slice allows exporting its items to another slice (see feeding setting in administrators slice setting). This is just permission - so administrator of such slice can set automatical feeding. This table don't tell us from which slice is items realy fed to another one. For information on real feeding settings see feeds table.
There is one exception for feeding permission settings - enabling export of items to all other slices. This information is not in feedperms table, but it is in slices.export_to_all field of slices table.
from_id varchar(16) NOT NULL,
to_id varchar(16) NOT NULL
Real setting of automatical items feeding is in feeds table. Again, it is relation between the records of the same table - slices. In contrast to feedperms table there are additional parameters for each relation, which restrict the feeding.
The automatical feeding process is raised after any item comes to approved bin of the slice. This is the case of new item is written and set as approved, case of change status of any item from holding bin (2) or trash bin (3) to approved (1) and the case of an item is fed into approved bin of the slice.
from_id varchar(16) NOT NULL,
to_id varchar(16) NOT NULL,
category_id varchar(16),
all_categories smallint(5),
to_category_id varchar(16),
to_approved smallint(5),
KEY from_id (from_id)
Next three tables are used only if you want to use MySQL permission sysstem. The switching the permission system can be done in config.php file (see constankt PERM_LIB). The default permission system is based on LDAP but it's up to you which one to use. If LDAP permission systerm is used, those tables are unnecessary.
The user table holds both user data and group data. The benefit of this approach is that we have distinct ids set for users and groups. Field specific for users are: password, uid, mail, name (= givenname + sn), givenname and sn (= surname). Field specific to groups are: name and description.
id int(11) DEFAULT '0' NOT NULL auto_increment,
type char(10) NOT NULL,
password char(30) NOT NULL,
uid char(40) NOT NULL,
mail char(40) NOT NULL,
name char(80) NOT NULL,
description char(255) NOT NULL,
givenname char(40) NOT NULL,
sn char(40) NOT NULL,
last_mod timestamp(14),
PRIMARY KEY (id),
KEY type (type),
KEY mail (mail),
KEY name (name),
KEY sn (sn)
Relational table - holds informations about which user belongs to which group. Each record means: User memberid belongs to group groupid
groupid int(11) DEFAULT '0' NOT NULL,
memberid int(11) DEFAULT '0' NOT NULL,
last_mod timestamp(14),
PRIMARY KEY (groupid, memberid),
KEY memberid (memberid)
Table perms sets permission for an object to some user/group. The object is defined by object_type and objectid. There are only two objects types in AA - object "slice" and object "aa" which is superrior to "slice" object.
object_type char(30) NOT NULL,
objectid char(32) NOT NULL,
userid int(11) DEFAULT '0' NOT NULL,
perm char(32) NOT NULL,
last_mod timestamp(14),
PRIMARY KEY (objectid, userid, object_type),
KEY userid (userid)