HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

ocPortal Developer's Guide: Database access and manipulation

» Return to Contents



The database abstraction and caching systems are one of the most complex and powerful parts of ocPortal. They literally drive the website, using a chain of abstraction functions, they allow you to use any database system you have a driver for.

Some of the functions described on this page are part of the database driver, and some are part of the database abstraction layer. If you wish to implement a database driver, basing it off an existing driver is the easiest way to proceed (if it is an ODBC driver, base it off another ODBC driver).

ocPortal defines a large number of field types:
  • AUTO, an auto incrementing unique key. This should always be used as a key field (*AUTO), and never as part of a combined key. It should never be inserted manually - i.e. the database should always be allowed to increment it as it chooses.
  • AUTO_LINK, a foreign key link to the above
  • INTEGER, an integer
  • SHORT_INTEGER, a very short (don't assume a wider range than 0-127) integer
  • BINARY, 0 or 1 (i.e. a representation for a Boolean)
  • MEMBER, a link to a member
  • GROUP, a link to group
  • TIME, a date and time (output of the PHP time() function)
  • LONG_TRANS, a long piece of text stored in the language/comcode translation table
  • SHORT_TRANS, a short piece of text stored in the language/comcode translation table (255 length maximum)
  • SHORT_TEXT, a short non-translatable piece of text (255 length maximum)
  • LONG_TEXT, a long non-translatable piece of text
  • ID_TEXT, a very short piece of text (about 50 length maximum)
  • IP, an IP address in string form
  • LANGUAGE_NAME, a language identifier (e.g. EN)
  • EMAIL, an e-mail address
  • URLPATH, a URL or file path
  • MD5, a MD5 hash, stored in base64encoded form (the output of the md5() function is as such)

String attributes should never be made optional, as some database systems cannot distinguish between an empty string and a non-existent string. ocPortal has to assume that a NULL string is in fact an empty one, and thus true NULL strings cannot be allowed.

Default values are not employed in ocPortal. You should always give full specifications for all except AUTO fields when performing an insert. Essentially, the insert commands should define their own defaults - this is a much cleaner and more compatible approach.

Because certain highly accessible database systems require that numeric attribute values not be enclosed in quotes, it is crucial that ocPortal be able to determine the data type of your data.
This means that you should not use strings and integers interchangeably or ocPortal will try and insert the integer as a string and potentially result in a database query error.
Integers should not be defined in quotes, and strings converted to integers should be forcibly type-converted using intval. Integers should be read using get/post/either_param_integer from the outset… good practice results in simple and trouble-free performance.

When tables are created, a special meta table is updated. This meta table stores the database schema in a database independent manner that allows the backup/restore system to work. Because of this, you should never manually change the database structure outside the context of the ocPortal database functions.

More info on table design:
  • Don't use text fields in a key
  • Always choose names that won't conflict with SQL keywords (e.g. not 'name', 'value', 'datetime', 'time', 'key', 'order ',…). Try to use prefixing so this doesn't become an issue
  • Don't pack more than two LONG_TEXT's into a row

Other tips:
  • When you do a select, make sure if there is ORDER BY x, that the x is selected (required for some DB's)

sources/database.php

Global_functions_database.php

Function summary

void init__database ()
void _general_db_init ()
boolean can_arbitrary_groupby ()
string db_full_text_assemble (string content, boolean boolean)
integer db_get_first_id ()
string db_string_equal_to (ID_TEXT attribute, string compare)
string db_string_not_equal_to (ID_TEXT attribute, string compare)
string db_encode_like (string pattern)
boolean db_has_full_text (array db)
boolean db_has_subqueries (array db)
boolean db_has_expression_ordering (array db)
string db_escape_string (string string)
string get_db_type ()
boolean get_use_persistent ()
string get_table_prefix ()
string get_db_site_host ()
string get_db_site ()
string get_db_site_user ()
string get_db_site_password ()
string get_db_forums_host ()
string get_db_forums ()
string get_db_forums_user ()
string get_db_forums_password ()
float microtime_diff (string a, string b)

void init__database()

Standard code module initialisation function.

Parameters…

(No return value)


Return to the function index for this class Expand: View the source code to this function

void _general_db_init()

Called once our DB connection becomes active.

Parameters…

(No return value)


Return to the function index for this class Expand: View the source code to this function

boolean can_arbitrary_groupby()

Find whether the database may run GROUP BY unfettered with restrictions on the SELECT'd fields having to be represented in it or aggregate functions

Parameters…

Returns…

Description Whether it can
Type boolean

Return to the function index for this class Expand: View the source code to this function

string db_full_text_assemble(string content, boolean boolean)

Assemble part of a WHERE clause for doing full-text search

Parameters…

Name content
Description Our match string
Type string

Name boolean
Description Whether to do a boolean full text search
Type boolean

Returns…

Description Part of a WHERE clause for doing full-text search
Type string

Return to the function index for this class Expand: View the source code to this function

integer db_get_first_id()

Get the ID of the first row in an auto-increment table (used whenever we need to reference the first).

Parameters…

Returns…

Description First ID used
Type integer

Return to the function index for this class Expand: View the source code to this function

string db_string_equal_to(ID_TEXT attribute, string compare)

Encode an SQL statement fragment for a conditional to see if two strings are equal.

Parameters…

Name attribute
Description The attribute
Type ID_TEXT

Name compare
Description The comparison
Type string

Returns…

Description The SQL
Type string

Return to the function index for this class Expand: View the source code to this function

string db_string_not_equal_to(ID_TEXT attribute, string compare)

Encode an SQL statement fragment for a conditional to see if two strings are not equal.

Parameters…

Name attribute
Description The attribute
Type ID_TEXT

Name compare
Description The comparison
Type string

Returns…

Description The SQL
Type string

Return to the function index for this class Expand: View the source code to this function

string db_encode_like(string pattern)

Encode a LIKE string comparision fragement for the database system. The pattern is a mixture of characters and ? and % wilcard symbols.

Parameters…

Name pattern
Description The pattern
Type string

Returns…

Description The encoded pattern
Type string

Return to the function index for this class Expand: View the source code to this function

boolean db_has_full_text(array db)

Find whether full-text-search is present

Parameters…

Name db
Description A DB connection
Type array

Returns…

Description Whether it is
Type boolean

Return to the function index for this class Expand: View the source code to this function

boolean db_has_subqueries(array db)

Find whether subquery support is present

Parameters…

Name db
Description A DB connection
Type array

Returns…

Description Whether it is
Type boolean

Return to the function index for this class Expand: View the source code to this function

boolean db_has_expression_ordering(array db)

Find whether expression ordering support is present

Parameters…

Name db
Description A DB connection
Type array

Returns…

Description Whether it is
Type boolean

Return to the function index for this class Expand: View the source code to this function

string db_escape_string(string string)

Escape a string so it may be inserted into a query. If SQL statements are being built up and passed using db_query then it is essential that this is used for security reasons. Otherwise, the abstraction layer deals with the situation.

Parameters…

Name string
Description The string
Type string

Returns…

Description The escaped string
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_type()

Get the type of database installed, such as MySQL, or Oracle.

Parameters…

Returns…

Description The database type
Type string

Return to the function index for this class Expand: View the source code to this function

boolean get_use_persistent()

Find ocPortal was installed to use persistent database connections or not.

Parameters…

Returns…

Description Whether to use persistent database connections
Type boolean

Return to the function index for this class Expand: View the source code to this function

string get_table_prefix()

Get the table prefixes used for all ocPortal tables, commonly used when you are installing ocPortal in the same database as your forums. The default table prefix is 'ocp4_'. Note that anything that might write to an arbitrary db, must ask that db for it's table prefix (if it needs it of course… the db abstracts away most needs for it)

Parameters…

Returns…

Description The table prefix
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_site_host()

Get the host of the database ('localhost', for example).

Parameters…

Returns…

Description The database host
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_site()

Get the name of the database.

Parameters…

Returns…

Description The database site
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_site_user()

Get the database username.

Parameters…

Returns…

Description The database username
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_site_password()

Get the database password.

Parameters…

Returns…

Description The database password
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_forums_host()

Get the host of the forum database ('localhost', for example).

Parameters…

Returns…

Description The database host
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_forums()

Get the name of the forum database.

Parameters…

Returns…

Description The forum database site
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_forums_user()

Get the forum database username.

Parameters…

Returns…

Description The forum database username
Type string

Return to the function index for this class Expand: View the source code to this function

string get_db_forums_password()

Get the forum database password.

Parameters…

Returns…

Description The forum database password
Type string

Return to the function index for this class Expand: View the source code to this function

float microtime_diff(string a, string b)

Get the time difference in microseconds between two PHP microtimes.Original source: php.net

Parameters…

Name a
Description First microtime
Type string

Name b
Description Second microtime
Type string

Returns…

Description The time difference
Type float

Return to the function index for this class Expand: View the source code to this function

database_driver

Function summary

void database_driver (string db_name, string db_host, string db_user, string db_password, string table_prefix, boolean fail_ok, ?object static)
string get_table_prefix ()
integer query_insert (string table, array map, boolean ret, boolean fail_ok, boolean save_as_volatile)
boolean table_exists (ID_TEXT tablename)
mixed query_value (string table, string select, ?array where_map, string end)
mixed _query_value (array values)
?mixed query_value_null_ok (string table, string select, ?array where_map, string end, boolean fail_ok)
?mixed query_value_null_ok_full (string query, boolean fail_ok, boolean skip_safety_check)
void query_delete (string table, ?array where_map, string end, ?integer max, ?integer start, boolean fail_ok)
?integer query_update (string table, array update_map, ?array where_map, string end, ?integer max, ?integer start, boolean num_touched, boolean fail_ok)
string _get_where_expand (string table, ?array select_map, ?array where_map, string end)
void initialise_filesystem_db ()
array query_select (string table, ?array select, ?array where_map, string end, ?integer max, ?integer start, boolean fail_ok, ?array lang_fields)
?mixed query (string query, ?integer max, ?integer start, boolean fail_ok, boolean skip_safety_check, ?array lang_fields, string field_prefix)
?mixed _query (string query, ?integer max, ?integer start, boolean fail_ok, boolean get_insert_id, ?array lang_fields, string field_prefix, boolean save_as_volatile)
void create_table (ID_TEXT table_name, array fields, boolean skip_size_check, boolean skip_null_check)
void create_index (ID_TEXT table_name, ID_TEXT index_name, array fields, ID_TEXT unique_key_field)
void delete_index_if_exists (ID_TEXT table_name, ID_TEXT index_name)
void drop_if_exists (ID_TEXT table)
void rename_table (ID_TEXT old, ID_TEXT new)
void add_table_field (ID_TEXT table_name, ID_TEXT name, ID_TEXT _type, ?mixed default)
void alter_table_field (ID_TEXT table_name, ID_TEXT name, ID_TEXT _type, ?ID_TEXT new_name)
void change_primary_key (ID_TEXT table_name, array new_key)
void promote_text_field_to_comcode (ID_TEXT table_name, ID_TEXT name, ID_TEXT key, integer level, boolean in_assembly)
void delete_table_field (ID_TEXT table_name, ID_TEXT name)
void refresh_field_definition (ID_TEXT type)

void database_driver(string db_name, string db_host, string db_user, string db_password, string table_prefix, boolean fail_ok, ?object static)

Construct a database driver from connection parameters.

Parameters…

Name db_name
Description The database name
Type string

Name db_host
Description The database server
Type string

Name db_user
Description The connection username
Type string

Name db_password
Description The connection password
Type string

Name table_prefix
Description The table prefix
Type string

Name fail_ok
Description Whether to on error echo an error and return with a NULL, rather than giving a critical error
Default value boolean-false
Type boolean

Name static
Description Static call object (NULL: use global static call object)
Default value
Type ?object

(No return value)


Return to the function index for this class Expand: View the source code to this function

string get_table_prefix()

Get the table prefixes used for all ocPortal tables, commonly used when you are installing ocPortal in the same database as your forums. The default table prefix is 'ocp4_'.

Parameters…

Returns…

Description The table prefix
Type string

Return to the function index for this class Expand: View the source code to this function

integer query_insert(string table, array map, boolean ret, boolean fail_ok, boolean save_as_volatile)

Insert a row.

Parameters…

Name table
Description The table name
Type string

Name map
Description The insertion map
Type array

Name ret
Description Whether to return the auto-insert-id
Default value boolean-false
Type boolean

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

Name save_as_volatile
Description Whether we are saving as a 'volatile' file extension (used in the XML DB driver, to mark things as being non-syndicated to subversion)
Default value boolean-false
Type boolean

Returns…

Description The id of the new row
Type integer

Return to the function index for this class Expand: View the source code to this function

boolean table_exists(ID_TEXT tablename)

Check if a table exists.

Parameters…

Name tablename
Description The table name
Type ID_TEXT

Returns…

Description Whether it exists
Type boolean

Return to the function index for this class Expand: View the source code to this function

mixed query_value(string table, string select, ?array where_map, string end)

Get the specified value from the database. This is a first value of the first row returned.

Parameters…

Name table
Description The table name
Type string

Name select
Description The field to select
Type string

Name where_map
Description The WHERE map [will all be AND'd together] (NULL: no where conditions)
Default value
Type ?array

Name end
Description Something to tack onto the end
Default value
Type string

Returns…

Description The first value of the first row returned
Type mixed

Return to the function index for this class Expand: View the source code to this function

mixed _query_value(array values)

Extract the first of the first of the list of maps.

Parameters…

Name values
Description The list of maps
Type array

Returns…

Description The first value of the first row in the list
Type mixed

Return to the function index for this class Expand: View the source code to this function

?mixed query_value_null_ok(string table, string select, ?array where_map, string end, boolean fail_ok)

Get the specified value from the database, or NULL if it is not there (or if the value itself is NULL). This is good for detection existence of records, or for use if they might may or may not be present.

Parameters…

Name table
Description The table name
Type string

Name select
Description The field to select
Type string

Name where_map
Description The WHERE map [will all be AND'd together] (NULL: no where conditions)
Default value
Type ?array

Name end
Description Something to tack onto the end
Default value
Type string

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

Returns…

Description The first value of the first row returned (NULL: nothing found, or null value found)
Type ?mixed

Return to the function index for this class Expand: View the source code to this function

?mixed query_value_null_ok_full(string query, boolean fail_ok, boolean skip_safety_check)

This function is a variant of query_value_null_ok, by the fact that it only accepts a complete (and perfect) SQL query, instead of assembling one itself from the specified parameters.

Parameters…

Name query
Description The complete SQL query
Type string

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

Name skip_safety_check
Description Whether to skip the query safety check
Default value boolean-false
Type boolean

Returns…

Description The first value of the first row returned (NULL: nothing found, or null value found)
Type ?mixed

Return to the function index for this class Expand: View the source code to this function

void query_delete(string table, ?array where_map, string end, ?integer max, ?integer start, boolean fail_ok)

Deletes rows from the specified table, that match the specified conditions (if any). It may be limited to a row range (it is likely, only a maximum, of 1, will be used, if any kind of range at all).

Parameters…

Name table
Description The table name
Type string

Name where_map
Description The WHERE map [will all be AND'd together] (NULL: no conditions)
Default value
Type ?array

Name end
Description Something to tack onto the end of the statement
Default value
Type string

Name max
Description The maximum number of rows to delete (NULL: no limit)
Default value
Type ?integer

Name start
Description The starting row to delete (NULL: no specific start)
Default value
Type ?integer

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

(No return value)


Return to the function index for this class Expand: View the source code to this function

?integer query_update(string table, array update_map, ?array where_map, string end, ?integer max, ?integer start, boolean num_touched, boolean fail_ok)

Update (edit) a row in the database.

Parameters…

Name table
Description The table name
Type string

Name update_map
Description The UPDATE map
Type array

Name where_map
Description The WHERE map [will all be AND'd together] (NULL: no conditions)
Default value
Type ?array

Name end
Description Something to tack onto the end of the statement
Default value
Type string

Name max
Description The maximum number of rows to update (NULL: no limit)
Default value
Type ?integer

Name start
Description The starting row to update (NULL: no specific start)
Default value
Type ?integer

Name num_touched
Description Whether to get the number of touched rows. WARNING: Do not use in core ocPortal code as it does not work on all database drivers
Default value boolean-false
Type boolean

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

Returns…

Description The number of touched records (NULL: hasn't been asked / error)
Type ?integer

Return to the function index for this class Expand: View the source code to this function

string _get_where_expand(string table, ?array select_map, ?array where_map, string end)

Create a SELECT query from some abstract data.

Parameters…

Name table
Description The table to select from
Type string

Name select_map
Description List of field selections (NULL: all fields)
Default value
Type ?array

Name where_map
Description Map of conditions to enforce (NULL: no conditions)
Default value
Type ?array

Name end
Description Additional stuff to tack onto the query
Default value
Type string

Returns…

Description SQL query
Type string

Return to the function index for this class Expand: View the source code to this function

void initialise_filesystem_db()

Initialise a filesystem DB that we can use for caching.

Parameters…

(No return value)


Return to the function index for this class Expand: View the source code to this function

array query_select(string table, ?array select, ?array where_map, string end, ?integer max, ?integer start, boolean fail_ok, ?array lang_fields)

Get the DB results found from the specified parameters.

Parameters…

Name table
Description The table name
Type string

Name select
Description The SELECT map (NULL: all fields)
Default value
Type ?array

Name where_map
Description The WHERE map [will all be AND'd together] (NULL: no conditions)
Default value
Type ?array

Name end
Description Something to tack onto the end of the SQL query
Default value
Type string

Name max
Description The maximum number of rows to select (NULL: get all)
Default value
Type ?integer

Name start
Description The starting row to select (NULL: start at first)
Default value
Type ?integer

Name fail_ok
Description Whether to allow failure (outputting a message instead of exiting completely)
Default value boolean-false
Type boolean

Name lang_fields
Description Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (NULL: none)
Default value
Type ?array

Returns…

Description The results
Type array

Return to the function index for this class Expand: View the source code to this function

?mixed query(string query, ?integer max, ?integer start, boolean fail_ok, boolean skip_safety_check, ?array lang_fields, string field_prefix)

This function is a very basic query executor. It shouldn't usually be used by you, as there are abstracted versions available (see below).

Parameters…

Name query
Description The complete SQL query
Type string

Name max
Description The maximum number of rows to affect (NULL: no limit)
Default value
Type ?integer

Name start
Description The start row to affect (NULL: no specification)
Default value
Type ?integer

Name fail_ok
Description Whether to output an error on failure
Default value boolean-false
Type boolean

Name skip_safety_check
Description Whether to skip the query safety check
Default value boolean-false
Type boolean

Name lang_fields
Description Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (NULL: none)
Default value
Type ?array

Name field_prefix
Description All the core fields have a prefix of this on them, so when we fiddle with language lookup we need to use this (only consider this if you're setting $lang_fields)
Default value
Type string

Returns…

Description The results (NULL: no results)
Type ?mixed

Return to the function index for this class Expand: View the source code to this function

?mixed _query(string query, ?integer max, ?integer start, boolean fail_ok, boolean get_insert_id, ?array lang_fields, string field_prefix, boolean save_as_volatile)

This function is a very basic query executor. It shouldn't usually be used by you, as there are specialised abstracted versions available.

Parameters…

Name query
Description The complete SQL query
Type string

Name max
Description The maximum number of rows to affect (NULL: no limit)
Default value
Type ?integer

Name start
Description The start row to affect (NULL: no specification)
Default value
Type ?integer

Name fail_ok
Description Whether to output an error on failure
Default value boolean-false
Type boolean

Name get_insert_id
Description Whether to get an insert ID
Default value boolean-false
Type boolean

Name lang_fields
Description Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (NULL: none)
Default value
Type ?array

Name field_prefix
Description All the core fields have a prefix of this on them, so when we fiddle with language lookup we need to use this (only consider this if you're setting $lang_fields)
Default value
Type string

Name save_as_volatile
Description Whether we are saving as a 'volatile' file extension (used in the XML DB driver, to mark things as being non-syndicated to subversion)
Default value boolean-false
Type boolean

Returns…

Description The results (NULL: no results)
Type ?mixed

Return to the function index for this class Expand: View the source code to this function

void create_table(ID_TEXT table_name, array fields, boolean skip_size_check, boolean skip_null_check)

Create a table with the given name and the given array of field name to type mappings.If a field type starts '*', then it is part of that field's key. If it starts '?', then it is an optional field.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name fields
Description The fields
Type array

Name skip_size_check
Description Whether to skip the size check for the table (only do this for addon modules that don't need to support anything other than mySQL)
Default value boolean-false
Type boolean

Name skip_null_check
Description Whether to skip the check for NULL string fields
Default value boolean-false
Type boolean

(No return value)


Return to the function index for this class Expand: View the source code to this function

void create_index(ID_TEXT table_name, ID_TEXT index_name, array fields, ID_TEXT unique_key_field)

Add an index to a table without disturbing the contents, after the table has been created.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name index_name
Description The index name
Type ID_TEXT

Name fields
Description The fields
Type array

Name unique_key_field
Description The name of the unique key field for the table
Default value id
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void delete_index_if_exists(ID_TEXT table_name, ID_TEXT index_name)

Delete an index from a table.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name index_name
Description The index name
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void drop_if_exists(ID_TEXT table)

Drop the given table, or if it doesn't exist, silently return.

Parameters…

Name table
Description The table name
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void rename_table(ID_TEXT old, ID_TEXT new)

Rename the given table.

Parameters…

Name old
Description The old table name
Type ID_TEXT

Name new
Description The new table name
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void add_table_field(ID_TEXT table_name, ID_TEXT name, ID_TEXT _type, ?mixed default)

Adds a field to an existing table.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name name
Description The field name
Type ID_TEXT

Name _type
Description The field type
Type ID_TEXT

Name default
Description The default value (NULL: no default)
Default value
Type ?mixed

(No return value)


Return to the function index for this class Expand: View the source code to this function

void alter_table_field(ID_TEXT table_name, ID_TEXT name, ID_TEXT _type, ?ID_TEXT new_name)

Change the type of a DB field in a table. Note: this function does not support ascession/decession of translatability

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name name
Description The field name
Type ID_TEXT

Name _type
Description The new field type
Type ID_TEXT

Name new_name
Description The new field name (NULL: leave name)
Default value
Type ?ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void change_primary_key(ID_TEXT table_name, array new_key)

Change the primary key of a table.

Parameters…

Name table_name
Description The name of the table to create the index on
Type ID_TEXT

Name new_key
Description A list of fields to put in the new key
Type array

(No return value)


Return to the function index for this class Expand: View the source code to this function

void promote_text_field_to_comcode(ID_TEXT table_name, ID_TEXT name, ID_TEXT key, integer level, boolean in_assembly)

If a text field has picked up Comcode support, we will need to run this.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name name
Description The field name
Type ID_TEXT

Name key
Description The tables key field name
Default value id
Type ID_TEXT

Name level
Description The translation level to use
Default value 2
Type integer
Values restricted to 1 2 3 4

Name in_assembly
Description Whether our data is already stored in Tempcode assembly format
Default value boolean-false
Type boolean

(No return value)


Return to the function index for this class Expand: View the source code to this function

void delete_table_field(ID_TEXT table_name, ID_TEXT name)

Delete the specified field from the specified table.

Parameters…

Name table_name
Description The table name
Type ID_TEXT

Name name
Description The field name
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void refresh_field_definition(ID_TEXT type)

If we've changed what $type is stored as, this function will need to be called to change the typing in the DB.

Parameters…

Name type
Description The field type
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

sources/database_action.php

Global_functions_database_action.php

Function summary

array get_db_keywords ()
array get_false_permissions ()
boolean config_option_exists (ID_TEXT name)
boolean permission_exists (ID_TEXT name)
void add_config_option (ID_TEXT human_name, ID_TEXT name, ID_TEXT type, SHORT_TEXT eval, ID_TEXT category, ID_TEXT group, BINARY shared_hosting_restricted, SHORT_TEXT data)
void delete_config_option (ID_TEXT name)
void add_specific_permission (ID_TEXT section, ID_TEXT name, boolean default, boolean not_even_mods)
void set_specific_permission (GROUP group_id, ID_TEXT permission, boolean value, ?ID_TEXT page, ?ID_TEXT category_type, ?ID_TEXT category_name)
void delete_specific_permission (ID_TEXT name)
void delete_attachments (ID_TEXT type, ?object connection)
void mass_delete_lang (ID_TEXT table, array attrs, ?object connection)

array get_db_keywords()

Returns a list of keywords for all databases we might some day support.

Parameters…

Returns…

Description List of pairs
Type array

Return to the function index for this class Expand: View the source code to this function

array get_false_permissions()

Returns a list of pairs, for which permissions are false by default for ordinary usergroups.

Parameters…

Returns…

Description List of pairs
Type array

Return to the function index for this class Expand: View the source code to this function

boolean config_option_exists(ID_TEXT name)

Check if a config option exists.

Parameters…

Name name
Description The name of the option
Type ID_TEXT

Returns…

Description Whether it exists
Type boolean

Return to the function index for this class Expand: View the source code to this function

boolean permission_exists(ID_TEXT name)

Check if a privilege exists.

Parameters…

Name name
Description The name of the option
Type ID_TEXT

Returns…

Description Whether it exists
Type boolean

Return to the function index for this class Expand: View the source code to this function

void add_config_option(ID_TEXT human_name, ID_TEXT name, ID_TEXT type, SHORT_TEXT eval, ID_TEXT category, ID_TEXT group, BINARY shared_hosting_restricted, SHORT_TEXT data)

Add a configuration option into the database, and initialise it with a specified value.

Parameters…

Name human_name
Description The language code to the human name of the config option
Type ID_TEXT

Name name
Description The codename for the config option
Type ID_TEXT

Name type
Description The type of the config option
Type ID_TEXT
Values restricted to float integer tick line text transline transtext list date forum category usergroup colour

Name eval
Description The PHP code to execute to get the default value for this option. Be careful not to make a get_option loop.
Type SHORT_TEXT

Name category
Description The language code for the option category to store the option in
Type ID_TEXT

Name group
Description The language code for the option group to store the option in
Type ID_TEXT

Name shared_hosting_restricted
Description Whether the option is not settable when on a shared ocportal-hosting environment
Default value 0
Type BINARY

Name data
Description Extra data for the option
Default value
Type SHORT_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void delete_config_option(ID_TEXT name)

Deletes a specified config option permanently from the database.

Parameters…

Name name
Description The codename of the config option
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void add_specific_permission(ID_TEXT section, ID_TEXT name, boolean default, boolean not_even_mods)

Add a privilege, and apply it to every usergroup.

Parameters…

Name section
Description The section the privilege is filled under
Type ID_TEXT

Name name
Description The codename for the privilege
Type ID_TEXT

Name default
Description Whether this permission is granted to all usergroups by default
Default value boolean-false
Type boolean

Name not_even_mods
Description Whether this permission is not granted to supermoderators by default (something very sensitive)
Default value boolean-false
Type boolean

(No return value)


Return to the function index for this class Expand: View the source code to this function

void set_specific_permission(GROUP group_id, ID_TEXT permission, boolean value, ?ID_TEXT page, ?ID_TEXT category_type, ?ID_TEXT category_name)

Sets the privilege of a usergroup

Parameters…

Name group_id
Description The usergroup having the permission set
Type GROUP

Name permission
Description The codename of the permission
Type ID_TEXT

Name value
Description Whether the usergroup has the permission
Type boolean

Name page
Description The ID code for the page being checked (NULL: current page)
Default value
Type ?ID_TEXT

Name category_type
Description The category-type for the permission (NULL: none required)
Default value
Type ?ID_TEXT

Name category_name
Description The category-name/value for the permission (NULL: none required)
Default value
Type ?ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void delete_specific_permission(ID_TEXT name)

Delete a privilege, and every usergroup is then relaxed from the restrictions of this permission.

Parameters…

Name name
Description The codename of the permission
Type ID_TEXT

(No return value)


Return to the function index for this class Expand: View the source code to this function

void delete_attachments(ID_TEXT type, ?object connection)

Delete attachments solely used by the specified hook.

Parameters…

Name type
Description The hook
Type ID_TEXT

Name connection
Description The database connection to use (NULL: standard site connection)
Default value
Type ?object

(No return value)


Return to the function index for this class Expand: View the source code to this function

void mass_delete_lang(ID_TEXT table, array attrs, ?object connection)

Deletes all language codes linked to by the specified table and attribute identifiers, if they exist.

Parameters…

Name table
Description The table
Type ID_TEXT

Name attrs
Description The attributes
Type array

Name connection
Description The database connection to use (NULL: standard site connection)
Type ?object

(No return value)


Return to the function index for this class Expand: View the source code to this function

sources/database_search.php

Global_functions_database_search.php

Function summary

void init__database_search ()
string generate_text_summary (string _temp_summary, array words_searched)
void opensearch_script ()
?string build_search_submitter_clauses (?ID_TEXT member_field_name, ?MEMBER member_id, ID_TEXT author, ?ID_TEXT author_field_name)
?array exact_match_sql (array row, integer i, ID_TEXT type, ?string param)
?array nl_delim_match_sql (array row, integer i, ID_TEXT type, ?string param)
array get_search_rows (?ID_TEXT meta_type, ?ID_TEXT meta_id_field, string content, boolean boolean_search, ID_TEXT boolean_operator, boolean only_search_meta, ID_TEXT direction, integer max, integer start, boolean only_titles, ID_TEXT table, array fields, string where_clause, string content_where, ID_TEXT order, string select, ?array raw_fields, ?string permissions_module, ?string permissions_field, boolean permissions_field_is_string)
array _boolean_search_prepare (string search_filter)
boolean in_memory_search_match (array filter, string title, ?string post)
boolean is_under_radar (string test)
string build_content_where (string content, boolean boolean_search, string boolean_operator, boolean full_coverage)
string db_like_assemble (string content, string boolean_operator, boolean full_coverage)
array sort_search_results (array hook_results, array results, string direction)
tempcode build_search_results_interface (array results, integer start, integer max, string direction, boolean general_search)

void init__database_search()

Standard code module initialisation function.

Parameters…

(No return value)


Return to the function index for this class Expand: View the source code to this function

string generate_text_summary(string _temp_summary, array words_searched)

Highlight keywords in an extracted portion of a piece of text.

Parameters…

Name _temp_summary
Description What was searched
Type string

Name words_searched
Description List of words searched
Type array

Returns…

Description Highlighted portion
Type string

Return to the function index for this class Expand: View the source code to this function

void opensearch_script()

Server opensearch requests.

Parameters…

(No return value)


Return to the function index for this class Expand: View the source code to this function

?string build_search_submitter_clauses(?ID_TEXT member_field_name, ?MEMBER member_id, ID_TEXT author, ?ID_TEXT author_field_name)

Build up a submitter search clause, taking into account members, authors, usernames, and usergroups.

Parameters…

Name member_field_name
Description The field name for member IDs (NULL: Cannot match against member IDs)
Type ?ID_TEXT

Name member_id
Description Member ID (NULL: Unknown, so cannot search)
Type ?MEMBER

Name author
Description Author
Type ID_TEXT

Name author_field_name
Description The field name for authors (NULL: Cannot match against member IDs)
Default value
Type ?ID_TEXT

Returns…

Description An SQL fragment (NULL: block query)
Type ?string

Return to the function index for this class Expand: View the source code to this function

?array exact_match_sql(array row, integer i, ID_TEXT type, ?string param)

Get special SQL from POSTed parameters for a catalogue search field that is to be exact-matched.

Parameters…

Name row
Description The row for the field to input
Type array

Name i
Description We're processing for the ith row
Type integer

Name type
Description Table type
Default value short
Type ID_TEXT
Values restricted to short long

Name param
Description Search term (NULL: lookup from environment)
Default value
Type ?string

Returns…

Description Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (NULL: nothing special)
Type ?array

Return to the function index for this class Expand: View the source code to this function

?array nl_delim_match_sql(array row, integer i, ID_TEXT type, ?string param)

Get special SQL from POSTed parameters for a catalogue search field for a multi-input field that is to be exact-matched.

Parameters…

Name row
Description The row for the field to input
Type array

Name i
Description We're processing for the ith row
Type integer

Name type
Description Table type
Default value short
Type ID_TEXT
Values restricted to short long

Name param
Description Search term (NULL: lookup from environment)
Default value
Type ?string

Returns…

Description Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (NULL: nothing special)
Type ?array

Return to the function index for this class Expand: View the source code to this function

array get_search_rows(?ID_TEXT meta_type, ?ID_TEXT meta_id_field, string content, boolean boolean_search, ID_TEXT boolean_operator, boolean only_search_meta, ID_TEXT direction, integer max, integer start, boolean only_titles, ID_TEXT table, array fields, string where_clause, string content_where, ID_TEXT order, string select, ?array raw_fields, ?string permissions_module, ?string permissions_field, boolean permissions_field_is_string)

Get some rows, queried from the database according to the search parameters.

Parameters…

Name meta_type
Description The META type used by our content (NULL: Cannot support META search)
Type ?ID_TEXT

Name meta_id_field
Description The name of the field that retrieved META IDs will relate to (NULL: Cannot support META search)
Type ?ID_TEXT

Name content
Description Search string
Type string

Name boolean_search
Description Whether to do a boolean search.
Type boolean

Name boolean_operator
Description Boolean operator
Type ID_TEXT
Values restricted to OR AND

Name only_search_meta
Description Whether to only do a META (tags) search
Type boolean

Name direction
Description Order direction
Type ID_TEXT

Name max
Description Start position in total results
Type integer

Name start
Description Maximum results to return in total
Type integer

Name only_titles
Description Whether to only search titles (as opposed to both titles and content)
Type boolean

Name table
Description The table name
Type ID_TEXT

Name fields
Description The translateable fields to search over (or an ! which is skipped). The first of these must be the title field or an '!'; if it is '!' then the title field will be the first raw-field
Type array

Name where_clause
Description The WHERE clause
Type string

Name content_where
Description The WHERE clause that applies specifically for content (this will be duplicated to check against multiple fields). ? refers to the yet-unknown field name
Type string

Name order
Description What to order by
Type ID_TEXT

Name select
Description What to select
Default value *
Type string

Name raw_fields
Description The non-translateable fields to search over (NULL: there are none)
Default value
Type ?array

Name permissions_module
Description The permission module to check category access for (NULL: none)
Default value
Type ?string

Name permissions_field
Description The field that specifies the permissions ID to check category access for (NULL: none)
Default value
Type ?string

Name permissions_field_is_string
Description Whether the permissions field is a string
Default value boolean-false
Type boolean

Returns…

Description The rows found
Type array

Return to the function index for this class Expand: View the source code to this function

array _boolean_search_prepare(string search_filter)

Take a search string and find boolean search parameters from it.

Parameters…

Name search_filter
Description The search string
Type string

Returns…

Description Words to search under the boolean operator, words that must be included, words that must not be included.
Type array

Return to the function index for this class Expand: View the source code to this function

boolean in_memory_search_match(array filter, string title, ?string post)

Perform a database-style in-memory boolean search on single item.

Parameters…

Name filter
Description A map of POST data in search-form style. May contain 'only_titles', 'content' (the critical one!) and 'conjunctive_operator'
Type array

Name title
Description The title to try and match
Type string

Name post
Description The post to try and match (NULL: not used)
Default value
Type ?string

Returns…

Description Whether we have a match
Type boolean

Return to the function index for this class Expand: View the source code to this function

boolean is_under_radar(string test)

Find whether a phrase is too small for fulltext search.

Parameters…

Name test
Description The phrase
Type string

Returns…

Description Whether it is
Type boolean

Return to the function index for this class Expand: View the source code to this function

string build_content_where(string content, boolean boolean_search, string boolean_operator, boolean full_coverage)

Build a fulltext query WHERE clause from given content.

Parameters…

Name content
Description The search content
Type string

Name boolean_search
Description Whether it's a boolean search
Type boolean

Name boolean_operator
Description Boolean operation to use
Type string
Values restricted to AND OR

Name full_coverage
Description Whether we can assume we require full coverage
Default value boolean-false
Type boolean

Returns…

Description WHERE clause
Type string

Return to the function index for this class Expand: View the source code to this function

string db_like_assemble(string content, string boolean_operator, boolean full_coverage)

Generate SQL for a boolean search.

Parameters…

Name content
Description Boolean search string
Type string

Name boolean_operator
Description Boolean operator to use
Default value AND
Type string
Values restricted to AND OR

Name full_coverage
Description Whether we can assume we require full coverage
Default value boolean-false
Type boolean

Returns…

Description The SQL (may be blank)
Type string

Return to the function index for this class Expand: View the source code to this function

array sort_search_results(array hook_results, array results, string direction)

Sort search results as returned by the search hook.

Parameters…

Name hook_results
Description Search results from the search hook, assumed already sorted
Type array

Name results
Description Existing array of results (originally starts blank)
Type array

Name direction
Description Sort direction
Type string

Returns…

Description Sorted results
Type array
Values restricted to ASC DESC

Return to the function index for this class Expand: View the source code to this function

tempcode build_search_results_interface(array results, integer start, integer max, string direction, boolean general_search)

Build a templated list of the given search results, for viewing.

Parameters…

Name results
Description Search results
Type array

Name start
Description Start index
Type integer

Name max
Description Maximum index
Type integer

Name direction
Description Sort direction
Type string
Values restricted to ASC DESC

Name general_search
Description Whether this is a general search, rather than a search for a specific result-type (such as all members)
Default value boolean-false
Type boolean

Returns…

Description Interface
Type tempcode

Return to the function index for this class Expand: View the source code to this function

Tutorial - Database Basics


In this tutorial, we are going to add a table to the database, populate it with data, then retrieve a row and delete it.

  1. Create a new PHP file
  2. Insert the following code to create a table:

Code (php)

        db_create_table('testing_table',array(
      'id'=>'*AUTO',
      'name'=>'SHORT_TEXT',
      'age'=>'?INTEGER',
      'description'=>'LONG_TEXT',
      'language'=>'LANGUAGE_NAME'
   ));

This code will produce a table named 'testing_table', with the fields of 'id', 'name', 'age', 'description' and 'language'.

3) Then use the following code to fill the table with data:

Code (php)

$GLOBALS['SITE_DB']->query_insert('testing_table',array('name'=>'Bob','language'=>get_lang()));

Even though we didn't specify values for 'age' and 'description', this query is perfectly valid, because the fields of 'age' and 'description' were created as optional.
To make a field optional when creating a table, place a question mark ('?') before the field type in the db_create_table function.

4) We will now add some more entries:

Code (php)

$name='Albert';
$GLOBALS['SITE_DB']->query_insert('testing_table',array('name'=>'Foo','language'=>'EN','age'=>M_PI));
$GLOBALS['SITE_DB']->query_insert('testing_table',array('name'=>$name,'language'=>get_lang(),'description'=>'Annoying'));
$GLOBALS['SITE_DB']->query_insert('testing_table',array('description'=>'Kind','language'=>'EN','name'=>'Bar','age'=>31));

All this code will insert 3 more rows into the table. As you can see, a variety of values can be put into cells: strings, constants, function returns, integers, even defined variables!
The third query_insert function also shows that the fields don't have to be in any specific order.
All of these functions demonstrate a critical observation: you don't need to specify a value for the 'id' field, as it is auto-incrementing.
The 'id' field was set to auto-increment when we created the table, using the type of '*AUTO'. Remember this, as you will likely be using a lot of auto-inrementing ID fields.

5) We will now select a row out of the table:

Code (php)

$row=$GLOBALS['SITE_DB']->query_select('testing_table',array('*'),array('language'=>'EN','name'=>'Bar'),'',1);

This code returns to us the first row it finds that has an language of 'EN', and a name of 'Bar', luckily for us, there should only be one row like this.

6) Finally, we will delete the row found in the previous step:

Code (php)

$GLOBALS['SITE_DB']->query_delete('testing_table', $row,'',1);

This code uses the data found in the previous step as a $where_map to delete the specified row.