NAME

HDB::CMDS - Hybrid DataBase Commands

DESCRIPTION

This are the commands/methods to access/manage the databases.

select

Make a SQL select query.

Example:

my @sel = $HDB->select('users' , 'name =~ joe' , '@%') ; ## table , where , return

## ... or ...

my @sel = $HDB->select('users' , 'name =~ joe' , cols => 'name,user,id' , limit => 1 , '@%') ;

## ... or ...

my @sel = $HDB->select(
table  => 'users' ,           ## Need to start with 'table' to paste a full HASH of arguments!
where  => 'name == joe' ,
col    => 'name, user , id' ,
limit  => '1' ,
sort   => 'id' ,
group  => 'name' ,
cache  => '0' ,
return => '@%' ,  
) ;

Arguments:

TABLE

The table name.

WHERE

Where condintion. See the topic 'WHERE' for format.

COL

Columns to return and order, separated by ','.

Example:

col => 'city'        # Return only the column city.
col => 'city,state'  # Return the column city and state in this order.
col => '>ID'         # Return the max ID.
col => '<ID'         # Return the min ID.

LIMIT

Limit of return or/and start of returns.

Example:

limit => '10'    # Make the limit of returns to 10.
limit => '10,2'  # Make the limit of returns to 10 and the returns will start from 2.
limit => '0,2'   # Returns will start from 2.

SORT

Column to use for sort (order). If > or < is used in the beggin set the ascending or descending sort.

Example:

sort => 'ID'   # Sort by ID in the ascending order.
sort => '>ID'  # Sort by ID in the ascending order.
sort => '<ID'  # Sort by ID in the *descending order.

GROUP

Column(s) to group.

Example:

group => 'city'          # Group only the col city
group => 'city , state'  # Group the col city and state.

CACHE

Turn on/off the cache of sth and col names.

RETURN

The return type. See the topic "RETURN" for format.

insert ( table , data )

Insert data inside a table.

You can call it sending the data by column order or hash (by column name):

# Cols of table users: name , email , id

$HDB->insert( 'users' , 'joe' , 'joe@mail.com' , 1 );

# Or with a hash:

$HDB->insert( 'users' , {
'name' => 'joe' ,
'email' => 'joe@mail.com' ,
'id' => 1 ,
} );

update ( table , where , data )

Update a table. The data need to be a HASH or a ref to a HASH:

$HDB->update( 'users' , 'user == joe' , {
name => 'Joe Tribiany' ,
email => 'foo@mail.com' ,
} );

# Or:

$HDB->update( 'users' , 'user == joe' , name => 'Joe Tribiany' , email => 'foo@mail.com' );

delete ( table , where )

Delete entrys of a table:

$HDB->delete( 'users' , 'user == joe' );

create ( table , columns )

Create a new tables. You send the columns in the order that they will be in the table, and the TYPES are based in the size:

$HDB->create( 'users' ,
user  => 100 ,            # A col for strings, with the max size 100.
name  => 150 ,            # A col for strings, with the max size 150.
more  => 4096 ,           # A col for strings, with the max size 4Kb.
more2 => 1048576          # A col for strings, with the max size 1Mb.
more3 => '*'              # A col for strings.
age   => 'int(200)' ,     # A col for numbers, with the max number 200.
numb  => 'float' ,        # A floating point with normal precision.
numb1 => 'double' ,       # A floating point with big precision.
numb2 => 'float(10)' ,    # A floating point with precision 10.
numb3 => '+float(10,4)' , # for floating points. This will be unsigned (only positive values).
                          # 10 is the max digit size (including decimal).
                          # 4 is the precision (number digits after decimal point).
adm   => bool ,           # For boolean entrys.
);

** FLOAT is not enable in any database, and can be changed to INTEGER. The precision and UNSIGNED options are not enabled for all too.
** Use FLOAT for normal precision, and DOUBLE for big precision for portable way.

In MySQL the cols type will be:

$HDB->create( 'users' ,
user  => 100 ,            # VARCHAR(100)
name  => 150 ,            # VARCHAR(150)
more  => 4096 ,           # TEXT
more2 => 1048576          # MEDIUMTEXT
more3 => '*'              # TEXT
age   => 'int(200)' ,     # SMALLINT
numb  => 'float' ,        # FLOAT
numb1 => 'double' ,       # DOUBLE
numb2 => 'float(10)' ,    # FLOAT(10)
numb3 => '+float(10,4)' , # FLOAT(10,4) UNSIGNED
adm   => bool ,           # BOOLEAN
);

In SQLite the cols type will be:

$HDB->create( 'users' ,
user  => 100 ,            # VARCHAR(100)
name  => 150 ,            # VARCHAR(150)
more  => 4096 ,           # TEXT
more2 => 1048576          # TEXT
more3 => '*'              # TEXT
age   => 'int(200)' ,     # INTEGER
numb  => 'float' ,        # FLOAT
numb1 => 'double' ,       # FLOAT
numb2 => 'float(10)' ,    # FLOAT
numb3 => '+float(10,4)' , # FLOAT
adm   => bool ,           # BOOLEAN  
);

** Note that the column ID will be always created and will be AUTOINCREMENT, unless you set the type by your self.

You can use predefined col names (templates) for the columns. This is good if you don't want to think in the size that the type of data can have:

$HDB->create( 'users' ,
user  => 100 ,
['email'] ,      # The predefined col name. Same as:   email => 50
name  => 150 ,
);

drop ( table )

Drop (remove) a table:

$HDB->drop( 'users' );

cmd ( SQL , RETURN )

Send a SQL query to the database. The return will be in the format of the argument RETURN:

my @sel = $HDB->cmd('select * from users','@%');

dump_table

Return a string with the table dumped.

tables

Return an ARRAY with tables of the database.

table_exists ( TABLE )

Return TRUE if TABLE exists.

tables_hash

Same as tables(), but return a HASH, with the tables as keys and 1 as values. Good if you want to make: if ($tables{users}) {...}

table_columns

Return a HASH with the columns and respective type (based in the DB type).

names

Return an ARRAY with the names of the columns in the table, with the respective order.

sql

Return the last SQL command sent to the DB.

flush_cache

Clean the HDB cache (in the HDB object, not in the database).

flush_table_cache

Clean the HDB cache of a table (in the HDB object, not in the database).

get_type

Convert the HDB type to the database type:

my $db_col_type = $HDB->get_type( 1000 );

...

my $db_col_type = $HDB->get_type( 'int(200)' );

default_types

Return a list of the default types of HDB.

predefined_columns

Return a HASH with the predefined columns and sets.

default_mod

Return a HASH with the HDB::MOD installed by default in this version.

The HASH:

keys   => full name.
values => id for HDB.

RETURN

Commands like select has a return argument, that will tell how to format the results and the type of the variable.

The return has 2 parts.

First, the type of the variable (@|$):

@ >> Will return an array.
$ >> Will return the first line of the results (row), or the parsed reference of the 2nd part.

** If the first part is omitted, @ will be used.

Second, the format ($|@|%):

$ >> The rows will have the columns separated by '::', like:  joe::joe@mail.com::1
@ >> The cols of each row will be inside an ARRAY.
% >> The cols of each row will be inside a HASH.

Examples:

return => '@$'   # Will return an ARRAY, with the cols in each line of the array separated by '::'.
return => '@@'   # Will return an ARRAY of ARRAYS (with the cols in the SUB-ARRAY).
return => '@%'   # Will return an ARRAY of HASHES (with the cols in the HASH).

return => '$$'   # Will return the cols of the first result (row) separated by '::'. (return a sinlge SCALAR)
return => '$@'   # Will return the cols of the first result (row) inside an ARRAY. (return a sinlge ARRAY)
return => '$%'   # Will return the cols of the first result (row) inside a HASH. (return a sinlge HASH)

return => '$$@'  # Special case: Parse the encoded ref of the first col in the first row. See HDB::Encode.
return => '$$%'  # Same as $$@.

return => '<$>'  # Special: Parse sth row by row, returning a SCALAR, with the columns separated by '::'.
return => '<@>'  # Special: Parse sth row by row, returning a ARRAY with the values of the columns.
return => '<%>'  # Special: Parse sth row by row, returning a HASH of columns and values.

return => '$'    # Like @$
return => '@'    # Like @@
return => '%'    # Like @%

return => '%%'   # Like $%

If you want the list of columns returned, put NAMES in the begin of return:

return => 'NAMES,@@'  # Will return the reference to an ARRAY with the columns names and the ARRAY of ARRAYS.

## USAGE:
my ($cols_names , @sel) = $HDB->select('users','NAMES,@%') ;
my @cols_names = @$cols_names ;

The best way to think in the RETURN type is to think in what variable type will receive the result, and in what type you want the informations inside the result.

## For an ARRAY receiveing and for informations in HASH type: @%
my @sel = $HDB->select('users','@%') ;
print "$sel[0]{name}\n" ;

## For a SCALAR receiveing and for informations in ARRAY type: $@
my $name = $HDB->select('users', col => 'name' , '$@') ;

## For a HASH receiveing and for informations in HASH type: %%
my %user = $HDB->select('users', "id == 1" '%%') ; ## return only 1 row!
print "$user{name}\n" ;

RETURN USAGE (Code Examples):

my @sel = $HDB->select( table => users , return => '@$' );

foreach my $sel_i ( @sel ) {
  my @cols = split("::" , $sel_i) ;
  ...
}

##########################################################

my @sel = $HDB->select( table => users , return => '@@' );

foreach my $sel_i ( @sel ) {
  my @cols = @$sel_i ;
  ...
}

##########################################################

my @sel = $HDB->select( table => users , return => '@%' );

foreach my $sel_i ( @sel ) {
  my %cols = %$sel_i ;
  ...
}

##########################################################

my ($names,@sel) = $HDB->select( table => users , return => 'NAMES,@%' );
my @names = @$names ;

foreach my $sel_i ( @sel ) {
  my %cols = %$sel_i ;
  ...
}

##########################################################

my %hash = $HDB->select( table => users , col => 'encoded' , return => '$$@' );

foreach my $Key ( keys %hash ) {
  my $Value = $hash{$Key} ;
  print "$Key = $Value\n" ;
}

##########################################################

my $hdbhandle = $HDB->select( table => users , return => '<$>' );

while( my $row = <$hdbhandle> ) {
  my @cols = split("::" , $row) ;
  ...
}

##########################################################

my $hdbhandle = $HDB->select( table => users , return => '<@>' );

while( my @cols = <$hdbhandle> ) {
  ...
}

##########################################################

my $hdbhandle = $HDB->select( table => users , return => '<%>' );

while( my %cols = <$hdbhandle> ) {
  foreach my $Key ( keys %cols ) { print "$Key = $cols{$Key}\n" ;}
  ...
}

##########################################################

my $sel_row_0 = $HDB->select( table => users , return => '$$' );
my @cols = split("::" , $sel_row_0) ;

##########################################################

my @cols = $HDB->select( table => users , return => '$@' ); ## Return ROW 0.

##########################################################

my %cols = $HDB->select( table => users , return => '$%' ); ## Return ROW 0.

SEE ALSO

HDB, HDB::Encode, HDB::sqlite, HDB::mysql.

AUTHOR

Graciliano M. P. <gm@virtuasites.com.br>

COPYRIGHT

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm HDB

perl -MCPAN -e shell
install HDB