Describing the process of preparing tables in the database, creating a data schema for automatic migrations and functions for working with data.
Preparing tables and data schema
Hacks automatically keep their tables in the database up to date. The description of the tables is in the schema.php file in the hack folder. You do not need to create it manually, use the built-in toolkit:
- Connect to your network database using phpMyAdmin or MySQL WorkBench.
- Be sure to deactivate the hack if it is active!
- Create the required tables with the desired indexes. Use the Aria engine.
- Be sure to follow the table naming conventions in the next section.
- Go to the "Data Schema Generator" section by clicking the "Files" button
- Check the list of tables and create a new schema. It will be saved in the hack automatically.
- Activate the hack after updating the schema file.
Important! Work on hack tables should only be done on a disabled hack! Any changes can be erased in accordance with the data scheme at the time of system check or updates!
Table naming principles
Tables created and used for hacks must begin with the name of the hack, prefixed with x. The full name of the table will look like cpa_xhack_table, where hack will be the name of the hack, and table - the name of the table itself. Table names are always indicated in small Latin letters. For example, a table with a list of addresses from the proxy hack would be called cpa_xproxy_addr.
Table names are recommended to be set in the start.php file of your hack in special constants with the DB_ prefix. For example, the table cpa_xproxy_addr can be described by the constant DB_XPROXY_ADDR or DB_XPROXY_ADDR_ADDR. The table name itself is specified with SQL_PREF at the beginning.
define( 'DB_XPROXY_ADDR', SQL_PREF . 'xproxy_addr' );
Database functions
Working with the database is done through the $core->db object. All data for the functions to work must be prepared and cleared. The data coming into $core->get and $core->post is already prepared for work. Data from API functions or created within the application is recommended to be pre-processed with the addslashes() function.
$core->db->query( $sql )
Executes a text query from $sql and returns true on success or false on failure.
$core->db->query( "UPDATE ".DB_XPROXY_ADDR." SET proxy_fail = 1 WHERE proxy_id = '$id'" );
$core->db->row( $sql )
Retrieves a string from the database at the request $sql as an associative array.
$core->db->row( "SELECT * FROM ".DB_XPROXY_ADDR." WHERE proxy_id = '$id' LIMIT 1" );
$core->db->field( $sql )
Retrieves one field from the database by request $sql. Useful for fetching the number of elements.
$core->db->data( "SELECT COUNT(*) FROM ".DB_XPROXY_ADDR." WHERE proxy_status = 1" );
$core->db->data( $sql )
Retrieves an array of strings from the database at the request $sql as an associative array. Recommended when working with small datasets up to hundreds of items.
$core->db->data( "SELECT proxy_id, proxy_status, proxy_ip, proxy_port FROM ".DB_XPROXY_ADDR." WHERE proxy_check = 1" );
$core->db->col( $sql )
Retrieves a column from the database on request $sql as a flat array.
$core->db->col( "SELECT proxy_id FROM ".DB_XPROXY_ADDR." WHERE proxy_check = 0" );
$core->db->icol( $sql )
Extracts from the base an associative key-value array from the first and second elements of the selection by request $sql. Useful for retrieving a list of element names.
$core->db->icol( "SELECT proxy_id, proxy_name FROM ".DB_XPROXY_ADDR." WHERE proxy_status = 1" );
$core->db->it( $sql )
Retrieves an array of strings from the base using an iterator. Similar to $core->db->data() for large amounts of data. Recommended for working with statistics.
$data = $core->db->it( "SELECT * FROM ".DB_XPROXY_ADDR ); foreach ( $data as $d ) …
$core->db->ic( $sql )
Retrieves a column from the database using an iterator. An analogue of $core->db->col() for large amounts of data.
$data = $core->db->ic( "SELECT proxy_token FROM ".DB_XPROXY_ADDR ); foreach ( $data as $d ) …
$core->db->ii( $sql )
Retrieves an associative key-value array from the base via an iterator. An analogue of $core->db->icol() for large amounts of data.
$data = $core->db->ii( "SELECT proxy_id, proxy_name FROM ".DB_XPROXY_ADDR ); foreach ( $data as $id => $v ) …
$core->db->add( $table, $data )
Adds an element from the $data array to the table named $table. Returns true on success and false on failure. Important! The data in the array must be manually "protected" beforehand.
$core->db->add( DB_XPROXY_ADDR, [ 'proxy_name' => 'Meow', 'proxy_ip' => '127.0.0.1' ] );
$core->db->lastid()
Returns the ID of the last item added to the database.
$core->db->replace( $table, $data )
Similar to $core->db->add but works with the REPLACE command.
$core->db->addupd( $table, $data )
Similar to $core->db->add that updates the data if there is an entry with the same key. A $core->db->replace replacement that does not change the element ID when requested.
$core->db->edit( $table, $data, $where )
Changes the data $data in the table named $table with the condition $where. Returns true on success and false on failure. The $where condition can be passed either as a string or as a key-value array. Array values are added via AND. Important! The data in the array must be manually "protected" beforehand.
$core->db->edit( DB_XPROXY_ADDR, [ 'proxy_status' => 1], "proxy_id = '$id'" ] ); $core->db->edit( DB_XPROXY_ADDR, [ 'proxy_status' => 1 ], [ 'proxy_id' => $id ] );
$core->db->del( $table, $where )
Deletes the data in the table named $table by the condition $where. Returns true on success and false on failure. The $where condition works the same as in the edit function.
$core->db->del( DB_XPROXY_ADDR, [ 'proxy_id' => $id ] );
$core->db->get( $table, $where )
Returns one row from the table $table given the condition $where as an associative array. Wrapper over the $core->db->row() function. The $where condition works the same as in the edit function.
$core->db->get( DB_XPROXY_ADDR, [ 'proxy_id' => $id ] );
$core->db->all( $table, $where )
Returns all rows from the table $table by the condition $where as an associative array. Wrapper over the $core->db->data() function. The $where condition works the same as in the edit function.
$core->db->all( DB_XPROXY_ADDR, [ 'proxy_status' => 1 ] );