App::020::SQL::functions

new()

Function creates new row into main table and initializes it ( creates ID and ID_entity ).

This function makes automatically journalization copy of every created new row, when -journalize is enabled (table with suffix '_j' must exists).

my $ID=App::020::SQL::functions::new
(
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns'=>
 {
  'column1' => "'value'",
  'column2' => "NOW()",
  'column3' => "NULL"
 },
 '-journalize' => 1,
 '-posix' => 1,
 '-replace' => 0 # use REPLACE INTO instead of INSERT INTO
)

Please don't forget that this funcion is low-level, also is not escaping column values, that's your work!

Function returns ID, which is in new() same as ID_entity!

new_initialize()

This function is called from new() function. Finds all rows where ID_entity IS NULL and initializes this rows with setting ID_entity=ID, datetime_create=NOW()

Function can be called with one param - ID;

App::020::SQL::functions::new_initialize(
 ID => $ID, # or undef
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
);

get_ID()

Function returns one row in %hash from main table ( also actual row, not journalized ).

my %hash=App::020::SQL::functions::get_ID
(
 ID => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns' =>
 {
  'column1' => 1, # return value of this column
  'column2' => 1, # same
 }
 '-slave' => 1, # select data from slave servers
)

Into 'columns' is automatically added ID, ID_entity, datetime_create and status.

Into 'columns' you are able set '*' => 1

get_ID_entity()

Function returns list of get_ID()'s used by one ID_entity

my @IDs=App::020::SQL::functions::get_ID_entity
(
 ID_entity => $ID_entity, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns' =>
 {
  'column1' => 1, # return value of this column
  'column2' => 1, # same
 }
)

Into 'columns' is automatically added ID, ID_entity, datetime_create and status.

Into 'columns' you are able set '*' => 1

Get unique ID when you know only ID_entity

my %hash=(
  App::020::SQL::functions::get_ID_entity
  (
   ID_entity => $ID_entity, # must be defined
   'db_h' => 'main', # name of database handler
   'db_name' => 'domain_tld', # name of database
   'tb_name' => 'a020_object', # name of main table
   'columns' =>
   {
    'column1' => 1, # return value of this column
    'column2' => 1, # same
   }
  )
)[0];

update()

Updates one row ( also one ID ) in main table.

my $retcode=App::020::SQL::functions::update(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns' =>
 {
  'column1' => "'string'", # set value of this column
  'column2' => "number", # same
 },
 '-journalize' => 1, # create journal copy of this update
 '-historical' => 'datetime', # update historical version of data (not yet implemented)
 '-posix' => 1, # posix enhanced table (set posix_modified to $main::USRM{'ID_user'})
)

Please do not set column datetime_create. datetime_create is updated automatically.

journalize()

Copies actual row from main table into journal table.

App::020::SQL::functions::journalize
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
)

Please do not execute this function alone. After this function must be in main table updated column datetime_create with same ID.

clone()

Makes copy of given ID, into new ID, but with same ID_entity. Also makes new version/modification of ID_entity.

For example, when ID_entity is like 'article', and one ID is language version of this article, then making clones is as making new language version of this article.

my $new_ID=App::020::SQL::functions::clone
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns' => # list of columns which are changed above old ID
 {
  'column1' => "'string'", # set value of this column
  'column2' => "number", # same
 },
 '-journalize' => 1, # create journal copy of this clone
);

Clone can be created only from enabled, disabled or trashed rows, not from deleted.

copy()

Makes copy of given ID, into new ID and new ID_entity. Also makes new entity

my $new_ID=App::020::SQL::functions::copy
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 'columns' => # list of columns which are changed above old ID
 {
  'column1' => "'string'", # set value of this column
  'column2' => "number", # same
 },
 '-journalize' => 1, # create journal copy of this copy
);

Clone can be created only from enabled or disabled rows, not from deleted or trashed.

to_trash()

Moves one row ( also ID ) into trash. Physically only changes status of this row to 'T'.

my $retcode=App::020::SQL::functions::to_trash
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status Y or N can be moved into trash.

trash_restore()

Restores one row ( also ID ) from trash. Physically only changes status of this row to 'N'.

my $retcode=App::020::SQL::functions::trash_restore
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status T can be restored

trash_delete()

Delete one row ( also ID ) from trash. Physically only changes status of this row to 'D' and moves it from main table into journal table ( when journalize is enabled ).

my $retcode=App::020::SQL::functions::trash_delete
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status T can be deleted

trash_empty()

Empty trash with all trashed ID's. Use carefully. When journalize is not enabled, all this rows is gone and can't be returned.

my $retcode=App::020::SQL::functions::trash_empty
(
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status T will be deleted

delete()

Deletes one row ( also ID ) from main table. Physically only changes status of this row to 'D' and moves it into journal table ( if enabled ).

my $retcode=App::020::SQL::functions::delete
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

disable()

Sets one row ( also ID ) as disabled ( not active ). Physically only changes status of this row to 'N'.

my $retcode=App::020::SQL::functions::disable
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status Y can be disabled

enable()

Sets one row ( also ID ) as enabled ( active ). Physically only changes status of this row to 'Y'.

my $retcode=App::020::SQL::functions::enable
(
 'ID' => $ID, # must be defined
 'db_h' => 'main', # name of database handler
 'db_name' => 'domain_tld', # name of database
 'tb_name' => 'a020_object', # name of main table
 '-journalize' => 1, # create journal copy of this action
);

Only rows with status N can be enabled