Cyclone3 Skin

DATA standard

From Cyclone3 Wiki

Standard of the data model for Cyclone3 applications for MySQL

Contents

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()
  1. 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
SELECT *
FROM a00_object
WHERE
   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),"%")
   )
   ...
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.

Additional

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.


Extended

cvml

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

attr_*

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

Idexes

Indexes are different for application tables and journals

Facts

  • 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(): ID

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.

UNI_0

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.

SEL_0

Indexes that can speed up frequent select types.

Selects

Record selection from main table:

SELECT
     *,
     *
FROM  a020_object
WHERE
    (
     (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'
LIMIT 1

Journaling

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
SET
   datetime_create=NOW(),
   name='blah, blah...'
WHERE ID=4
LIMIT 1
  • Journal the last modification.
REPLACE INTO a020_object_cat_j
SELECT * FROM a020_object_cat
WHERE ID=4
LIMIT 1

History

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

User Log

Counting

Counting records

SELECT
   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
(
   a00_object.ID_entity=a00_object_j.ID_entity
)
WHERE a00_object.ID_entity=$ID_entity

User Rights

X-Related

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

API

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"
  }
);

Examples

Select a record

SELECT
  *
FROM a020_object
WHERE
  status='Y'

See Also