ActionApps AAdb Database Description

This is the commented aadb database dump (see AAdb E-R diagram).

Most important tables

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.

CREATE TABLE items (

id varchar(16) NOT NULL,
Unique id of item
master_id varchar(16) NOT NULL,
Id of base item (if this item is fed). master_id = id for base items (not fed).
slice_id varchar(16) NOT NULL,
Id of slice in which this item belongs.
category_id varchar(16) NOT NULL,
Id of category in which this item belongs. (It should be changed in future - item should belongs to more than one category, so we have to create table item_category - M:N relation)
status_code smallint(5) unsigned DEFAULT '0' NOT NULL,
Three state status of this item 1 - Approved, 2 - in holding bin, 3 - in trash bin
language_code varchar(8) NOT NULL,
Like EN, DE, HU - see lt_langs table
cp_code varchar(32) NOT NULL,
Codepage for character encoding like iso8859-1, windows-1250 - see lt_cps
headline varchar(255) NOT NULL,
Title
hl_href varchar(255) NOT NULL,
URL of external link - used as link to external page if fulltext is (or can't be) filled - link_only must be set then.
link_only smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean 1 or 0 - determines if use fulltext or hl_href
post_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
date of filling
publish_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
The date, when item is shown on public web page
expiry_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
The date, when item is removed from public web page
abstract text,
Summary
full_text_old mediumtext,
not used - should be deleted
img_src varchar(255),
Name of image related to the item
img_width varchar(32),
Image width
img_height varchar(32),
Image height
html_formatted smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean value 1 / 0 - if html_formatted, fulltext is written on page as is. In opposite case all special characters are substituted.
source varchar(255),
Name of source of item
source_href varchar(255),
URL of source of item
redirect varchar(255),
URL for view this item - item is shown on hl_href if link_only=true or on redirect url (if specified) or on the same page
place varchar(255),
Related locality to item content
highlight smallint(5) unsigned DEFAULT '0' NOT NULL,
Imprtant item - should be displayed on homepage ...
posted_by varchar(255),
author
e_posted_by varchar(255),
email of author
created_by varchar(60) NOT NULL,
edited_by varchar(60),
last_edit datetime,
contact1 varchar(16),
not used - related stories (the purpose of this field) will be provided by another table (reletion M:N)
contact2 varchar(16),
-"-
contact3 varchar(16),
-"-
edit_note varchar(255),
Editor's note
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)

CREATE TABLE fulltexts (

ft_id varchar(16) NOT NULL,
Foreign key items.id - link to id (master_id respectively - see above) of item in items table
full_text mediumtext,
Text of item
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.

CREATE TABLE slices (

id varchar(16) NOT NULL,
Slice id
headline varchar(255) NOT NULL,
the full name of slice
short_name varchar(255) NOT NULL,
Short name of slice used in administration interface (in select lists ...)
type varchar(20) DEFAULT 'en_news' NOT NULL,
Type of this slice (like "News EN") - on this field depends which aplication language file is loaded (see en_news_lang.php for example)
slice_url varchar(255),
The url of public - live page. It is used from admin interface to preview slice.
grab_len smallint(5) unsigned DEFAULT '0' NOT NULL,
The number of characters to be grabed from fulltext in user do not fill abstract
post_enabled smallint(5) unsigned DEFAULT '0' NOT NULL,
not used (should be removed)
created_by varchar(60),
user's (creator's) id
created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
time of creation of this slice
res_persID varchar(60),
not used (should be removed)
export_to_all smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean value - "1" means "enable export items from this slice to any slice".
deleted smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean value - completely disables the slice (user can't switch to this slice - it is acessible only for super users
fulltext_format mediumtext,
HTML code with aliases for fulltext view of live page
odd_row_format mediumtext,
HTML code with aliases for odd rows of compact (list) view of items.
even_row_format mediumtext,
HTML code with aliases for even rows of compact (list) view of items. Will be used only if even_odd_differ field is set to true
even_odd_differ smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean value - is set if even rows of compact view should look different from odd rows of compact view (other background ...)
compact_top varchar(255),
HTML code which is prior to all rows in compact view.
compact_bottom varchar(255),
HTML code which is below the last row in compact view.
category_sort smallint(5) unsigned DEFAULT '0' NOT NULL,
Boolean value - set if compact view should be sorted by category.
category_format mediumtext,
HTML code with aliases for category headline. This HTML code is shown only if category_sort field is set to 1 (true)
compact_remove varchar(255),
The remove strings for compact view. The purpose of remove string is to remove rubbish from result live page - like empty braces etc. The way how it works is:
fulltext_remove varchar(255),
The remove strings for fulltext view. (see compact_remove)
edit_fields varchar(40),
This is the string field, but the sense of this string should be interpretted as array of boolean values. Each boolean value ("y" / "n") corresponds to one field of item table. The value of "y" (yes) means "show corresponding field on itemedit.php page (input item form)". For corresponding fields see en_news_lang.php.
needed_fields varchar(40),
Boolean array as in case edit_fields field (see above). The value of "y" (yes) means "corresponding field will be required on itemedit.php3 page (input item form) - user must fill it in". For corresponding fields see en_news_lang.php.
search_show varchar(15),
The same as edit_fields but for search form. For corresponding fields see en_news_lang.php.
search_default varchar(10),
The same as search_show. Each value sets a field which is searched by default (title, abstract, fulltext, editor's note). For corresponding fields see en_news_lang.php.
d_link_only smallint(5) unsigned DEFAULT '0',
d_highlight smallint(5) unsigned DEFAULT '0',
d_language_code varchar(8) NOT NULL,
default value (see items table)
d_cp_code varchar(32) NOT NULL,
default value (see items table)
d_category_id varchar(16) NOT NULL,
default value (see items table)
d_status_code smallint(5) unsigned DEFAULT '0' NOT NULL,
default value (see items table)
d_expiry_limit smallint(5) unsigned DEFAULT '0' NOT NULL,
default value (see items table)
d_expiry_date datetime,
default value (see items table)
d_hl_href varchar(255) NOT NULL,
default value (see items table)
d_source varchar(255),
default value (see items table)
d_source_href varchar(255),
default value (see items table)
d_redirect varchar(255),
default value (see items table) - new added 8/17/2000
d_place varchar(255),
default value (see items table)
d_listlen smallint(5) unsigned DEFAULT '0' NOT NULL,
default value (see items table)
d_html_formatted smallint(5) unsigned DEFAULT '0' NOT NULL,
default value (see items table)
d_img_src varchar(255),
default value (see items table)
d_img_width varchar(32),
default value (see items table)
d_img_height varchar(32),
default value (see items table)
d_posted_by varchar(255) NOT NULL,
default value (see items table)
d_e_posted_by varchar(255) NOT NULL,
default value (see items table)
PRIMARY KEY (id)
);

Constants tables

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.

CREATE TABLE categories (

id varchar(16) NOT NULL,
Category id
name varchar(255) NOT NULL,
Category name
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).

CREATE TABLE lt_cps (

code varchar(32) NOT NULL,
Name of codepage.
w32cp varchar(64),
PRIMARY KEY (code)
);

lt_langs holds language names in which each item can be written.

CREATE TABLE lt_langs (

code varchar(8) NOT NULL,
Language code like EN, CS, HU ...
name varchar(64) NOT NULL,
Name of language (English, Czech, Hungarian ...)
altcode varchar(8),
PRIMARY KEY (code)
);

Relation tables

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.

CREATE TABLE catbinds (

slice_id varchar(16) NOT NULL,
Foreign key from table slice (slice.id)
category_id varchar(16) NOT NULL,
Foreign key from table categories (categories.id)
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.

CREATE TABLE feedperms (

from_id varchar(16) NOT NULL,
Slices.id of slice from which is the feeding permited
to_id varchar(16) NOT NULL
Slices.id of slice into which is the feeding permited
);

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.

CREATE TABLE feeds (

from_id varchar(16) NOT NULL,
Slices.id of slice from which the items are automaticaly fed into slices.id defined in to_id field.
to_id varchar(16) NOT NULL,
Slices.id of slice into which are items automaticaly fed.
category_id varchar(16),
If we want to restrict feedind just from one category of from_id slice, we must set category_id (Of course there is possibility to export more than one specific category - we just add anoher record to this table). In oposite case we should have all_categories field set to true.
all_categories smallint(5),
If all categories is set to true, all categories of souce slice (from_id) are fed into destination slice (to_id)
to_category_id varchar(16),
The id of category into which the item should be fed. If to_category_id is not set, category_id remains the same as in source slice (see option "The same" in admin interface for feeding setting)
to_approved smallint(5),
If you want to automaticaly fed items to be i approved bin (items.status = 1) then this boolean field is set to true. Else is the item fed into holding bin (items.status = 2).
KEY from_id (from_id)
);

MySQL permission tables

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.

CREATE TABLE users (

id int(11) DEFAULT '0' NOT NULL auto_increment,
User/group id
type char(10) NOT NULL,
User or Group - two state field
password char(30) NOT NULL,
Password for user
uid char(40) NOT NULL,
User id
mail char(40) NOT NULL,
e-mail adres of user
name char(80) NOT NULL,
name of group or user (name of user = givenname + sn)
description char(255) NOT NULL,
dascription of group
givenname char(40) NOT NULL,
Given name of user
sn char(40) NOT NULL,
Surname of user
last_mod timestamp(14),
Time of last modification
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

CREATE TABLE membership (

groupid int(11) DEFAULT '0' NOT NULL,
Group id. Foreign key from users table.
memberid int(11) DEFAULT '0' NOT NULL,
User id. Foreign key from users table. We use the term member instead of user, because it is posible to have group id in this field, which implements group of groups. For this situation are prepared LDAP permissions too, even if this possibility is not used in current version of AA.
last_mod timestamp(14),
Time of last modification.
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.

CREATE TABLE perms (

object_type char(30) NOT NULL,
Type of permited object "aa" or "slice"
objectid char(32) NOT NULL,
Id of permited object
userid int(11) DEFAULT '0' NOT NULL,
User/group id to whom belongs perm
perm char(32) NOT NULL,
Permission string
last_mod timestamp(14),
Time of last modification.
PRIMARY KEY (objectid, userid, object_type),
KEY userid (userid)
);

Other documentation