DATA Standard

Standard of the data model for Cyclone3 applications for MySQL
date:$Date: 2008-02-15 12:47:34 +0100 (Fri, 15 Feb 2008) $
editor: $Author: comsultia $
releaseinfo:$Rev: 3000 $

Standard

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

      Example:
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 requred 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.

Indexes

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, datetime_change

    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.

  • UNI_0

    Default unique index. This index determines unique rows.

    UNI_* indexes are created only for main table not for journal tables.

    eg.:

    • 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:

  1. 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

  2. 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

Implementation in generic a020

Example of database structure in generic a020

User Comments

Add your own comment (or send us private feedback/question)

AddThis Social Bookmark Button RSS

This content is generated from file 'http://svn.cyclone3.org/trunk/doc/standard/DATA-en.docbook'

Cyclone3 XULadmin is the first open source content management GUI built upon Mozilla.