ONO Admin

ONO_DB is responsible for all database. All DB related I/O should always be handled by ONO_DB, you should not use DBI/DBD or any low level Perl DB I/O stuff in your ONO projects.

Important DB calls include:

connect(), command(), select(), readcols()

SQL databases should be listed in /etc/sql, note that database_name doesn't need to be the actual name of the SQL database:

/etc/sql/database_name.conf

The content of the file looks more or less like this, please adjust to your needs:

driver mysql
host localhost
database your_db_name
username your_db_username
password your_db_password

You may then connect to be database and execute commands:

my $db = ONO_DB->connect('database_name');
ONO_DB->command($db,"UPDATE ...");

To read data from a table:

foreach my $line (ONO_DB->select($db,$table,$where,$order)) {
my @col = ONO_DB->readcols($line);
my $first_column_data = $col[0];
...
}

Functions

bad_sql_bot();

Returns 1 of ONO thinks that the web client is an evil web bot, or 0 if ONO thinks it's a human or a friendly bot.

ONO::DB->bad_sql_bot( );

bad_sql_ip();

Returns 1 of ONO thinks that the web client uses an evil IP, or 0 if ONO thinks that the IP doesn't look suspicious.

ONO::DB->bad_sql_ip( );

returns: ONO::Lib::Web::MaliciousIP->evil_ip_simple(ONO::Lib::Web::Client->ip())

clean();

Clean up a table or column name. Mostly used internally by the module, to make sure no bad code will be passed to functions.

ONO::DB->clean( );

returns: $code

command();

Execute an SQL command. In case of INSERT, UPDATE or DELETE this may also return data in array format.

ONO::IO->command() is usually used to store, update or delete data from tables. To read data, use select() and readcols().

If $dump is true, then the status will be saved into a file named __SQL_ERR_DUMP.txt on development stations.

When storing binary data, ODBC may require $no_encode to be set to 1. In this case, data may have be be decoded when reading later on.

ONO::DB->command( $db, $command, $dump, $no_encode );

returns: @data

command_update();

Like command(), additionally this will automatically update modification_username and modification_timestamp (will only work with tables offering those).

ONO::DB->command_update( $db,$command,$user );

confcheck();

Check if configuration data is complete.

ONO::DB->confcheck( );

connect();

Connect to a database, and return a DB handle. Note that ONO can connect to multiple databases at once, as lons as they use the same driver.

Databases should be configured in /etc/sql (on the document root level, NOT in the UNIX /etc directory), so that you can simply connect to database_name by creating the file /etc/sql/database_name.conf and then using the ONO_DB->connect('database_name') command.

Alternatively, you may also use the -d switch to pass username and password information directly to the connact() function.

Switches:

-d use username and password input (only DBI, not ODBC)

-u use username and password input (only DBI, not ODBC) (onsolete, use -d)

-s store status

ONO::DB->connect( $database, $switches, $username, $password );

connection_error();

Error message to be displayed in case of a connection error.

ONO::DB->connection_error( $db,$mode,$lang );

returns: ($ERR,$mode)

copy_record();

Copy a record, which means that a table record will be duplicated, using a new primary key.

ONO::DB->copy_record( $db, $table, $primkey, $old, $new );

returns: $RET

count();

Count the number of rows / records in a table.

ONO::DB->count( $db,$table,$options );

decode();

Decode data from UTF-8 to Latin1, which may be required on ODBC installations in some cases.

ONO::DB->decode( $data );

returns: $data

delete();

Delete a table row / record.

ONO::DB->delete( $db,$table,$where );

returns: $command

describe_table();

Return the names of all table columns as an array. The driver (MySQL or ODBC) is being automatically detected, but you may also force using ODBC by indicating it manually.

ONO::DB->describe_table( $db,$table,$driver );

returns: @columns

driver();

Returns the name / ID of the current driver, this will either be 'mysql' or 'odbc'. MySQL is the default DB used by ONO, you may access Microsoft SQL server databases by adding a file named etc/sql/drivers/odbc-mssql.conf.

Note that ONO cannot use MySQL and MS SQL simultaneously, you will need to opt for one single solution at a time.

ONO::DB->driver( );

returns: $db_driver

dump();

Make a backup of a specific MySQL database table. Note that this will not work with ODBC databases.

ONO::DB->dump( $vpath,$community,$table,$dir );

returns: "$vpath/$dir/$table.sql"

get();

Returns a specific column from a specific row.

ONO::DB->get( $db,$column,$table,$where,$order,$limit );

returns: $stat->fetchrow_array()

get_auto_inc();

Get the next automatic increment id from a table. Also see get_highest_primkey() and get_next_primkey().

ONO::DB->get_auto_inc( $db,$table );

returns: $stat->fetchrow_array()

get_highest_primkey();

Detect the highest primary key in a table. Also see get_next_primkey() and get_auto_inc().

ONO::DB->get_highest_primkey( $db, $primkey, $table, $where );

get_next_primkey();

Detect the next primary key that can be used in a table. Also see get_highest_primkey() and get_auto_inc().

ONO::DB->get_next_primkey( );

returns: &get_highest_primkey("",$_[1],$_[2],$_[3])+1

inc();

Increment the value of a database table column.

ONO::DB->inc( $db, $column, $table, $where );

returns: $stat->fetchrow_array()

query_umlaut();

Umlaut compatibility for ODBC databases. Simply processes and returns the string.

ONO::DB->query_umlaut( );

returns: $query

read();

Read a configuration file in /etc/sql and return its data as an array.

ONO::DB->read( );

returns: ($db_name,$db_user,$db_pass,$db_driver)

readcols();

Will turn the output of select() into readable arrays.

ONO::DB->readcols( );

select();

Read data from a table, which should always be used along with the readcols() function. $select will filter results, $order can be used to sort results, $limitoffset allows to limit the number of rows returned an to set an offset.

If debug is true, then select() will not return the data, but the generated SQL command.

ONO::DB->select( $db, $table, $select, $order, $limitoffset, $debug );