Cyclone3 Skin

DATA standard

From Cyclone3 Wiki

(Difference between revisions)
(See Also)
(List of indexes)
Line 151: Line 151:
=== List of indexes ===
=== List of indexes ===
'''PRIMARY(): ID, datetime_change'''
'''PRIMARY(): ID'''
:Primary index which must consist of '''ID''' and '''datetime_create'''. This key is required for both, main table and journal even if it does not follow any practical use.
:Primary index which must consist of '''ID''' column. This key is required. In journal table is required primary key from '''ID, datetime_create''' and in history table from '''ID, datetime_apply''' columns.

Revision as of 20:10, 29 September 2008

Standard of the data model for Cyclone3 applications for MySQL


Table names

  • Prefix consists of a letter that indicates table purpose, "a" stands for aplication. (eg. a400 - article table)
  • A keyword best defining the table purpose is used to describe the content of the table. It can be singular or plural noun depending on ID_entity. If ID_entity stores one or more data elements for the application. (eg. a400_atricle)
  • For addirional application tables append another noun to the main table name describing the relation between them. (eg. a400_article_cat for article categories)
  • Journal tables are marked by tailing "_j" (eg. a400_article_j)

List of columns

If a column is NULL it must not be a part of UNIQUE key.

Trash records are kept in the main table so they may conflict with other rows depending on the UNIQUE key. Users must be informed that there is a conflict with a trashed item.

Deleted records are stored only in journals. Restoring these records is for now manual only because there may be UNIQUE key conflicts.

Fields are created in the following order.

Identification part (required)

ID - bigint(20) unsigned NOT NULL auto_increment

Coupled with datetime_create creates unique table row.

ID_entity - bigint(20) unsigned default NULL

Identifier of information stored in the table. ID_entity may be used for more typed/alternations of the same entity. For example language versions of the same article share the same ID_entity but have different ID and lng.
This column is NULL because new entities are created without identifiers which are updated directly after insertion with an UPDATE query.
ID_entity must be created in the following way:
  1. New a00_object record where ID_entity is NULL
    my $query=$main::DB{main}->Query("INSERT INTO a00_object(name,name2) VALUES("a","b")"); $ID=$query->insertid()
  2. Now ID_entity must be set according to ID
    UPDATE a00_object SET ID_entity=ID, datetime_create=NOW() WHERE ID_entity IS NUL LIMIT 1

Identification part (not required)

ID_charindex - varchar(64) character set ascii collate ascii_bin default NULL

Field determining placement in tree structure. This is reqired for all "category" type tables. The content is simple "[ID_charindex of parent]:[subindex]". The ":" character splits tree levels.

ID_category - bigint(20) unsigned NOT NULL auto_increment

ID used to bind entities to category. Required for all categorized items. References ID_entity of "category" type tables
FROM a00_object
   ID_category IN
     SELECT ID_entity
     FROM a00_object_cat
     WHERE ID_charindex LIKE
     CONCAT ((SELECT ID_charindex FROM a00_object_cat WHERE ID_entity=$IDcategory LIMIT 1),"%")

Application part

Any field required by the application

Domain part

Only used for global/master applications used between more domains.

domain - varchar(64) character set ascii collate ascii_bin NOT NULL default

master domain ($tom::Hm)

domain_sub - varchar(64) character set ascii collate ascii_bin NOT NULL default

local domain ($tom::H)

Time Planning part

datetime_start - datetime NOT NULL default '0000-00-00 00:00:00'

Time defining the start of record in time. (eg. Time to publish article)

datetime_stop - datetime default NULL

Time defining the end of record in time. (eg. Time to end publishing of article)

Time part

datetime_create - datetime NOT NULL default '0000-00-00 00:00:00'

Time of row creation.

datetime_lastuse - datetime default NULL

Time of last use of row. DO NOT USE for application requiring performance.

datetime_nextuse - datetime default NULL

Time defining the next use of the row - for planning purposes. Used for data where planing of use is required.


lng - char(2) character set ascii NOT NULL default

Language code by ISO-639, 2char version.

status - char(1) character set ascii NOT NULL default 'N'

Status of the row in database:
  • Y - yes ( active )
  • N - no ( deactivated, do not publish )
  • T - Trash ( Is in Trash )
  • D - Delete ( Deleted, experts can manually restore )

Move to Trash - If a row is trashed the application just sets its status to T.

Delete - Records are not directly deleted. Similary to Move to Trash we set the row status to D. Is there is a journal table the record is moved there and deleted from the source table.

Cleaning - Refers to deleting of rows marked as D and T and cleaning of associated data such as files on the disk.



CVML is a special XML like format used to store metadata, content is handles by the application. Please refer to CVML documentation.


Additional fields not initially required by the application and used for customization purposes. The table cloning scripts ignores these fields.


Indexes are different for application tables and journals


  • The main table must keep the aplication data unique.
This means when a column is unique there cannot be other records with same values. This is different four journals as there can be more deleted rows with same idenifiers that were created deleted, created and again deleted.
Because of this deleted items are moved to journals if possible.
  • Journals do not need to keep the unique keys of the application.

List of indexes


Primary index which must consist of ID column. This key is required. In journal table is required primary key from ID, datetime_create and in history table from ID, datetime_apply columns.


Default unique index. This index determines unique rows.
UNI_* indexes are created only for main table not for journal tables.
UNI_0 ( ID_entity, lng )
Rows are unique if they have special ID_entity and lng, this is used for language versions of the same article defined by ID_entity.
UNI_0 ( ID_entity, domain, domain_sub, lng )
Unique index for domain dependent application.


Indexes that can speed up frequent select types.


Record selection from main table:

FROM  a020_object
     (domain= OR domain='$tom::Hm')
     OR (domain_sub= OR domain_sub='$tom::H')
    AND datetime_start<=$datetime
    AND (datetime_stop IS NULL OR datetime_stop>=$datetime)
    AND (lng= OR lng='$env{lng}')
    AND status='Y'


The main purpose of journaling is to store deleted data to prevent unintentional deletion. Additionaly it can be used to track chronological changes in the application records.

This is ensured by the pripary index PRIMARY(ID,datetime_create). ID is the modification ID of any ID_entity while datetime_create keeps the modification date. datetime_create must be updated for each change.

Journaling process:

  • Change row by ID, note that datetime_create=NOW().
UPDATE a020_object_cat
   name='blah, blah...'
  • Journal the last modification.
REPLACE INTO a020_object_cat_j
SELECT * FROM a020_object_cat


Journaling keeps the track of operations in time. Historical tables can be reconstructed from journals.

User Log


Counting records

   SUM(a00_object.datetime_create) AS count,
   SUM(a00_object_j.datetime_create) AS count_journal
FROM a00_object
LEFT JOIN a00_object_j ON
WHERE a00_object.ID_entity=$ID_entity

User Rights


Relations to other applicarions should be handled explicitely using a160 - X-related.


Low level API implementing the DATA standard

This api does not distinguish between QUERY strings, functions or numbers therefore it uses 'columns' entrys without quoting the content. Note the "'string'" in the example.

Create new record

my $ID=App::020::SQL::functions::new(
  'db_h' => "main",
  'db_name' => "example_tld",
  'tb_name' => "a020_object",
  'columns' => {
    'name' => "'string'"
    'ID_number' => "5"


Select a record

FROM a020_object

See Also