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
void init__database()
Standard code module initialisation function.
Parameters…
(No return value)
function init__database()
{
if (defined('DB_MAX_KEY_SIZE')) return;
global $QUERY_LIST,$QUERY_COUNT,$NO_QUERY_LIMIT,$NO_DB_SCOPE_CHECK,$QUERY_FILE_LOG,$SITE_INFO;
$QUERY_LIST=array();
$QUERY_COUNT=0;
$NO_QUERY_LIMIT=false;
$NO_DB_SCOPE_CHECK=false;
if (((!isset($SITE_INFO['no_extra_logs'])) || ($SITE_INFO['no_extra_logs']=='0')) && (is_file(get_custom_file_base().'/data_custom/queries.log')))
{
$QUERY_FILE_LOG=fopen(get_custom_file_base().'/data_custom/queries.log','at');
} else
{
$QUERY_FILE_LOG=NULL;
}
// This will be generalised some-time
require_code('database/'.get_db_type());
$GLOBALS['DB_STATIC_OBJECT']=object_factory('Database_Static_'.get_db_type());
// Create our main database objects
global $TABLE_LANG_FIELDS;
$TABLE_LANG_FIELDS=array();
if ((array_key_exists('db_site',$SITE_INFO)) || (array_key_exists('db_site_user',$SITE_INFO)))
{
$GLOBALS['SITE_DB']=new database_driver(get_db_site(),get_db_site_host(),get_db_site_user(),get_db_site_password(),get_table_prefix());
}
// Limits (we also limit field names to not conflict with keywords - those defined in database_action.php)
define('DB_MAX_KEY_SIZE',500);
define('DB_MAX_PRIMARY_KEY_SIZE',251);
define('DB_MAX_ROW_SIZE',8000);
define('DB_MAX_FIELD_IDENTIFIER_SIZE',31);
define('DB_MAX_IDENTIFIER_SIZE',32);
// We have to take into account that chars might take 3 bytes - but we'll assume unicode is only used on db's with higher limits
define('DB_MAX_KEY_SIZE_UNICODE',1000);
define('DB_MAX_ROW_SIZE_UNICODE',24000);
global $UPON_QUERY_HOOKS;
$UPON_QUERY_HOOKS=NULL;
}
void _general_db_init()
Called once our DB connection becomes active.
Parameters…
(No return value)
function _general_db_init()
{
global $TABLE_LANG_FIELDS;
if (count($TABLE_LANG_FIELDS)>0) return;
$TABLE_LANG_FIELDS=function_exists('persistant_cache_get')?persistant_cache_get('TABLE_LANG_FIELDS'):NULL;
if ($TABLE_LANG_FIELDS===NULL)
{
$TABLE_LANG_FIELDS=array();
$_table_lang_fields=$GLOBALS['SITE_DB']->query('SELECT m_name,m_table FROM '.get_table_prefix().'db_meta WHERE '.db_string_equal_to('m_type','SHORT_TRANS').' OR '.db_string_equal_to('m_type','LONG_TRANS').' OR '.db_string_equal_to('m_type','*SHORT_TRANS').' OR '.db_string_equal_to('m_type','*LONG_TRANS').' OR '.db_string_equal_to('m_type','?SHORT_TRANS').' OR '.db_string_equal_to('m_type','?LONG_TRANS'),NULL,NULL,true);
if ($_table_lang_fields!==NULL)
{
foreach ($_table_lang_fields as $lang_field)
{
if (!isset($TABLE_LANG_FIELDS[$lang_field['m_table']]))
$TABLE_LANG_FIELDS[$lang_field['m_table']]=array();
$TABLE_LANG_FIELDS[$lang_field['m_table']][]=$lang_field['m_name'];
}
}
if (function_exists('persistant_cache_set'))
persistant_cache_set('TABLE_LANG_FIELDS',$TABLE_LANG_FIELDS);
}
}
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 |
function can_arbitrary_groupby()
{
if (!method_exists($GLOBALS['DB_STATIC_OBJECT'],'can_arbitrary_groupby')) return false;
return $GLOBALS['DB_STATIC_OBJECT']->can_arbitrary_groupby();
}
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 |
function db_full_text_assemble($content,$boolean)
{
return $GLOBALS['DB_STATIC_OBJECT']->db_full_text_assemble($content,$boolean);
}
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 |
function db_get_first_id()
{
return $GLOBALS['DB_STATIC_OBJECT']->db_get_first_id();
}
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 |
function db_string_equal_to($attribute,$compare)
{
return $GLOBALS['DB_STATIC_OBJECT']->db_string_equal_to($attribute,$compare);
}
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 |
function db_string_not_equal_to($attribute,$compare)
{
return $GLOBALS['DB_STATIC_OBJECT']->db_string_not_equal_to($attribute,$compare);
}
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 |
function db_encode_like($pattern)
{
return $GLOBALS['DB_STATIC_OBJECT']->db_encode_like($pattern);
}
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 |
function db_has_full_text($db)
{
if (count($db)>4) // Okay, we can't be lazy anymore
{
$db=call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'],'db_get_connection'),$db);
_general_db_init();
}
return $GLOBALS['DB_STATIC_OBJECT']->db_has_full_text($db);
}
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 |
function db_has_subqueries($db)
{
if (!method_exists($GLOBALS['DB_STATIC_OBJECT'],'db_has_subqueries')) return true;
if (count($db)>4) // Okay, we can't be lazy anymore
{
$db=call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'],'db_get_connection'),$db);
_general_db_init();
}
return $GLOBALS['DB_STATIC_OBJECT']->db_has_subqueries($db);
}
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 |
function db_has_expression_ordering($db)
{
if (count($db)>4) // Okay, we can't be lazy anymore
{
$db=call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'],'db_get_connection'),$db);
_general_db_init();
}
if (!method_exists($GLOBALS['DB_STATIC_OBJECT'],'db_has_expression_ordering')) return false;
return $GLOBALS['DB_STATIC_OBJECT']->db_has_expression_ordering($db);
}
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 |
function db_escape_string($string)
{
return $GLOBALS['DB_STATIC_OBJECT']->db_escape_string($string);
}
string get_db_type()
Get the type of database installed, such as MySQL, or Oracle.
Parameters…
Returns…
| Description |
The database type |
| Type |
string |
function get_db_type()
{
global $SITE_INFO;
if (!isset($SITE_INFO['db_type'])) return is_dir(get_custom_file_base().'/uploads/website_specific/'.get_db_site())?'xml':'mysql';
return $SITE_INFO['db_type'];
}
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 |
function get_use_persistent()
{
global $SITE_INFO;
return array_key_exists('use_persistent',$SITE_INFO)?($SITE_INFO['use_persistent']=='1'):false;
}
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 |
function get_table_prefix()
{
global $SITE_INFO;
if (!isset($SITE_INFO['table_prefix'])) return 'ocp'.strval(ocp_version()).'_';
return $SITE_INFO['table_prefix'];
}
string get_db_site_host()
Get the host of the database ('localhost', for example).
Parameters…
Returns…
| Description |
The database host |
| Type |
string |
function get_db_site_host()
{
global $SITE_INFO;
return array_key_exists('db_site_host',$SITE_INFO)?$SITE_INFO['db_site_host']:'localhost';
}
string get_db_site()
Get the name of the database.
Parameters…
Returns…
| Description |
The database site |
| Type |
string |
function get_db_site()
{
global $SITE_INFO;
if ((!array_key_exists('db_site',$SITE_INFO)) || ($SITE_INFO['db_site']===NULL)) return basename(get_file_base());
return $SITE_INFO['db_site'].(($GLOBALS['CURRENT_SHARE_USER']===NULL)?'':('_'.$GLOBALS['CURRENT_SHARE_USER']));
}
string get_db_site_user()
Get the database username.
Parameters…
Returns…
| Description |
The database username |
| Type |
string |
function get_db_site_user()
{
global $SITE_INFO;
if ($GLOBALS['CURRENT_SHARE_USER']!==NULL) return substr(md5($SITE_INFO['db_forums_user'].'_'.$GLOBALS['CURRENT_SHARE_USER']),0,16);
return ((array_key_exists('db_site_user',$SITE_INFO)) && ($SITE_INFO['db_site_user']!==NULL))?$SITE_INFO['db_site_user']:'root';
}
string get_db_site_password()
Get the database password.
Parameters…
Returns…
| Description |
The database password |
| Type |
string |
function get_db_site_password()
{
global $SITE_INFO;
return array_key_exists('db_site_password',$SITE_INFO)?$SITE_INFO['db_site_password']:'';
}
string get_db_forums_host()
Get the host of the forum database ('localhost', for example).
Parameters…
Returns…
| Description |
The database host |
| Type |
string |
function get_db_forums_host()
{
global $SITE_INFO;
return array_key_exists('db_forums_host',$SITE_INFO)?$SITE_INFO['db_forums_host']:(array_key_exists('db_site_host',$SITE_INFO)?$SITE_INFO['db_site_host']:'localhost');
}
string get_db_forums()
Get the name of the forum database.
Parameters…
Returns…
| Description |
The forum database site |
| Type |
string |
function get_db_forums()
{
global $SITE_INFO;
if (!array_key_exists('db_forums',$SITE_INFO)) return get_db_site();
return $SITE_INFO['db_forums'].(($GLOBALS['CURRENT_SHARE_USER']===NULL)?'':('_'.$GLOBALS['CURRENT_SHARE_USER']));
}
string get_db_forums_user()
Get the forum database username.
Parameters…
Returns…
| Description |
The forum database username |
| Type |
string |
function get_db_forums_user()
{
global $SITE_INFO;
if (!array_key_exists('db_forums_user',$SITE_INFO)) return get_db_site_user();
if ($GLOBALS['CURRENT_SHARE_USER']!==NULL) return substr(md5($SITE_INFO['db_forums_user'].'_'.$GLOBALS['CURRENT_SHARE_USER']),0,16);
return $SITE_INFO['db_forums_user'];
}
string get_db_forums_password()
Get the forum database password.
Parameters…
Returns…
| Description |
The forum database password |
| Type |
string |
function get_db_forums_password()
{
global $SITE_INFO;
if (!array_key_exists('db_forums_password',$SITE_INFO)) return get_db_site_password();
return $SITE_INFO['db_forums_password'];
}
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 |
function microtime_diff($a,$b)
{
$x=explode(' ',$a);
$a_micro=floatval($x[0]);
$a_int=(float)intval($x[1]);
$y=explode(' ',$b);
$b_micro=floatval($y[0]);
$b_int=(float)intval($y[1]);
if ($a_int>$b_int)
{
return ($a_int-$b_int)+($a_micro-$b_micro);
}
elseif ($a_int==$b_int)
{
if ($a_micro>$b_micro)
{
return ($a_int-$b_int)+($a_micro-$b_micro);
}
elseif ($a_micro<$b_micro)
{
return ($b_int-$a_int)+($b_micro-$a_micro);
}
else
{
return 0.0;
}
}
else
{ // $a_int<$b_int
return ($b_int-$a_int)+($b_micro-$a_micro);
}
}
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)
function database_driver($db_name,$db_host,$db_user,$db_password,$table_prefix,$fail_ok=false,$static=NULL)
{
$this->text_lookup_original_cache=array();
$this->text_lookup_cache=array();
$this->table_exists_cache=array();
$servers=explode(',',$db_host);
if (count($servers)==1)
{
$this->connection_write=array(get_use_persistent(),$db_name,$db_host,$db_user,$db_password,$fail_ok);
$this->connection_read=$this->connection_write;
} else
{
$this->connection_write=array(get_use_persistent(),$db_name,$servers[0],$db_user,$db_password,$fail_ok);
$min=(count($servers)==2)?0:1;
$this->connection_read=array(get_use_persistent(),$db_name,$servers[mt_rand($min,count($servers)-1)],$db_user,$db_password,$fail_ok);
}
$this->table_prefix=$table_prefix;
if ($static!==NULL)
{
$this->static_ob=$static;
} else
{
$this->static_ob=$GLOBALS['DB_STATIC_OBJECT'];
}
}
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 |
function get_table_prefix()
{
return $this->table_prefix;
}
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 |
function query_insert($table,$map,$ret=false,$fail_ok=false,$save_as_volatile=false)
{
if (($table=='cache') && (get_db_type()!='xml') && (get_option('filesystem_caching',true)==='1'))
{
global $FILECACHE_OBJECT;
if ($FILECACHE_OBJECT===NULL) $this->initialise_filesystem_db();
if ($FILECACHE_OBJECT!=$this)
return $FILECACHE_OBJECT->query_insert($table,$map,$ret,$fail_ok,$save_as_volatile);
}
$keys='';
$all_values=array();
$eis=$this->static_ob->db_empty_is_null();
foreach ($map as $key=>$value)
{
if ($keys!='') $keys.=', ';
$keys.=$key;
$_value=(!is_array($value))?array($value):$value;
$v=mixed();
foreach ($_value as $i=>$v)
{
if (!isset($all_values[$i])) $all_values[$i]='';
$values=$all_values[$i];
if ($values!='') $values.=', ';
if ($value===NULL)
{
if (($eis) && (is_string($v)) && ($v=='')) $values.='\' \''; else $values.='NULL';
}
else
{
if (($eis) && (is_string($v)) && ($v==''))
{
$v=' ';
}
if (is_integer($v)) $values.=strval($v);
elseif (is_float($v)) $values.=float_to_raw_string($v);
elseif (($key=='begin_num') || ($key=='end_num')) $values.=$v; // Fudge, for all our known large unsigned integers
else $values.='\''.$this->static_ob->db_escape_string($v).'\'';
}
$all_values[$i]=$values; // essentially appends, as $values was loaded from former $all_values[$i] value
}
}
if (count($all_values)==1) // usually $all_values only has length of 1
{
$query='INSERT INTO '.$this->table_prefix.$table.' ('.$keys.') VALUES ('.$all_values[0].')';
} else
{
// So we can do batch inserts...
$all_v='';
foreach ($all_values as $v)
{
if ($all_v!='') $all_v.=', ';
$all_v.='('.$v.')';
}
$query='INSERT INTO '.$this->table_prefix.$table.' ('.$keys.') VALUES '.$all_v;
}
return $this->_query($query,NULL,NULL,$fail_ok,$ret,NULL,'',$save_as_volatile);
}
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 |
function table_exists($tablename)
{
/*
// Just works with MySQL (too complex to do for all SQL's http://forums.whirlpool.net.au/forum-replies-archive.cfm/523219.html)
$full_tablename=$this->get_table_prefix().$tablename;
$rows=$this->query("SHOW TABLES LIKE '".$full_tablename."'");
foreach ($rows as $row)
foreach ($row as $field)
if ($field==$full_tablename) return true;
return false;
*/
if (array_key_exists($tablename,$this->table_exists_cache))
{
return $this->table_exists_cache[$tablename];
}
$test=$this->query_value_null_ok('db_meta','m_name',array('m_table'=>$tablename));
$this->table_exists_cache[$tablename]=($test!==NULL);
return $this->table_exists_cache[$tablename];
}
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 |
function query_value($table,$select,$where_map=NULL,$end='')
{
$values=$this->query_select($table,array($select),$where_map,$end,1,NULL);
if ($values===NULL) return NULL; // error
if (!array_key_exists(0,$values)) fatal_exit(do_lang_tempcode('QUERY_NULL',escape_html($this->_get_where_expand($this->table_prefix.$table,array($select),$where_map,$end)))); // No result found
return $this->_query_value($values);
}
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 |
function _query_value($values)
{
if (!array_key_exists(0,$values)) return NULL; // No result found
$first=$values[0];
$v=current($first); // Result found. Maybe a value of 'null'
return $v;
}
?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 |
function query_value_null_ok($table,$select,$where_map=NULL,$end='',$fail_ok=false)
{
$values=$this->query_select($table,array($select),$where_map,$end,1,NULL,$fail_ok);
if ($values===NULL) return NULL; // error
return $this->_query_value($values);
}
?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 |
function query_value_null_ok_full($query,$fail_ok=false,$skip_safety_check=false)
{
$values=$this->query($query,1,NULL,$fail_ok,$skip_safety_check);
if ($values===NULL) return NULL; // error
return $this->_query_value($values);
}
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)
function query_delete($table,$where_map=NULL,$end='',$max=NULL,$start=NULL,$fail_ok=false)
{
if (($table=='cache') && (get_db_type()!='xml') && (get_option('filesystem_caching',true)==='1'))
{
global $FILECACHE_OBJECT;
if ($FILECACHE_OBJECT===NULL) $this->initialise_filesystem_db();
if ($FILECACHE_OBJECT!=$this)
{
$FILECACHE_OBJECT->query_delete($table,$where_map,$end,$max,$start,$fail_ok);
return;
}
}
if ($where_map===NULL)
{
$this->_query('DELETE FROM '.$this->table_prefix.$table.' '.$end,$max,$start,$fail_ok);
return;
}
$where='';
foreach ($where_map as $key=>$value)
{
if ($where!='') $where.=' AND ';
if (is_float($value)) $where.=$key.'='.float_to_raw_string($value);
elseif (is_integer($value)) $where.=$key.'='.strval($value);
elseif (($key=='begin_num') || ($key=='end_num')) $where.=$key.'='.$value; // Fudge, for all our known large unsigned integers
else
{
if ($value===NULL) $where.=$key.' IS NULL';
else
{
if ((is_string($value)) && ($value=='') && ($this->static_ob->db_empty_is_null())) $where.=$key.' IS NULL'; //$value=' ';
else $where.=db_string_equal_to($key,$value);
}
}
}
$query='DELETE FROM '.$this->table_prefix.$table.' WHERE ('.$where.') '.$end;
$this->_query($query,$max,$start,$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)
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 |
function query_update($table,$update_map,$where_map=NULL,$end='',$max=NULL,$start=NULL,$num_touched=false,$fail_ok=false)
{
$where='';
$update='';
$value=mixed();
if ($where_map!==NULL)
{
foreach ($where_map as $key=>$value)
{
if ($where!='') $where.=' AND ';
if (is_float($value)) $where.=$key.'='.float_to_raw_string($value);
elseif (is_integer($value)) $where.=$key.'='.strval($value);
elseif (($key=='begin_num') || ($key=='end_num')) $where.=$key.'='.$value; // Fudge, for all our known large unsigned integers
else
{
if ($value===NULL) $where.=$key.' IS NULL';
else
{
if ((is_string($value)) && ($value=='') && ($this->static_ob->db_empty_is_null())) /*$where.=$key.' IS NULL';*/ $value=' ';
/*else */$where.=db_string_equal_to($key,$value);
}
}
}
}
foreach ($update_map as $key=>$value)
{
if (($value===STRING_MAGIC_NULL) || ($value===INTEGER_MAGIC_NULL)) continue;
if ($update!='') $update.=', ';
if ($value===NULL) $update.=$key.'=NULL';
else
{
if (is_float($value)) $update.=$key.'='.float_to_raw_string($value);
elseif (is_integer($value)) $update.=$key.'='.strval($value);
elseif (($key=='begin_num') || ($key=='end_num')) $where.=$key.'='.$value; // Fudge, for all our known large unsigned integers
else $update.=$key.'=\''.$this->static_ob->db_escape_string($value).'\'';
}
}
if ($update=='') return;
if ($where=='')
{
return $this->_query('UPDATE '.$this->table_prefix.$table.' SET '.$update.' '.$end,$max,$start,$fail_ok,$num_touched);
} else
{
return $this->_query('UPDATE '.$this->table_prefix.$table.' SET '.$update.' WHERE ('.$where.') '.$end,$max,$start,$fail_ok,$num_touched);
}
}
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 |
function _get_where_expand($table,$select_map=NULL,$where_map=NULL,$end='')
{
if ($select_map===NULL) $select_map=array('*');
$select='';
foreach ($select_map as $key)
{
if (!is_string($key)) $key=strval($key);
if ($select!='') $select.=',';
$select.=$key;
}
$where='';
if (($where_map!==NULL) && ($where_map!=array()))
{
foreach ($where_map as $key=>$value)
{
if (!is_string($key)) fatal_exit('Parameters to the database API given in the wrong order. Please check the function call.');
if ($where!='') $where.=' AND ';
if (is_float($value)) $where.=$key.'='.float_to_raw_string($value);
elseif (is_integer($value)) $where.=$key.'='.strval($value);
elseif (($key=='begin_num') || ($key=='end_num')) $where.=$key.'='.$value; // Fudge, for all our known large unsigned integers
else
{
if ($value===NULL) $where.=$key.' IS NULL';
else
{
if (($value==='') && ($this->static_ob->db_empty_is_null())) /*$where.=$key.' IS NULL';*/ $value=' ';
/*else */
if ($key=='text_original') $table=str_replace(' LEFT JOIN '.$this->get_table_prefix().'translate ',' JOIN '.$this->get_table_prefix().'translate ',$table);
$where.=db_string_equal_to($key,$value);
}
}
}
return 'SELECT '.$select.' FROM '.$table.' WHERE ('.$where.') '.$end;
}
return 'SELECT '.$select.' FROM '.$table.' '.$end;
}
void initialise_filesystem_db()
Initialise a filesystem DB that we can use for caching.
Parameters…
(No return value)
function initialise_filesystem_db()
{
global $FILECACHE_OBJECT;
require_code('database/xml');
$chain_db=new database_driver(get_custom_file_base().'/persistant_cache','','','',get_table_prefix(),false,object_factory('Database_Static_xml'));
$chain_connection=&$chain_db->connection_write;
if (count($chain_connection)>4) // Okay, we can't be lazy anymore
{
$chain_connection=call_user_func_array(array($chain_db->static_ob,'db_get_connection'),$chain_connection);
_general_db_init();
}
$FILECACHE_OBJECT=$chain_db;
}
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 |
function query_select($table,$select=NULL,$where_map=NULL,$end='',$max=NULL,$start=NULL,$fail_ok=false,$lang_fields=NULL)
{
if (($table=='cache') && (get_db_type()!='xml') && (get_option('filesystem_caching',true)==='1'))
{
global $FILECACHE_OBJECT;
if ($FILECACHE_OBJECT===NULL) $this->initialise_filesystem_db();
if ($FILECACHE_OBJECT!=$this)
return $FILECACHE_OBJECT->query_select($table,$select,$where_map,$end,$max,$start,$fail_ok,$lang_fields);
}
$full_table=$this->table_prefix.$table;
$field_prefix='';
if ($select===NULL) $select=array('*');
// Optimisation for entirely automatic translate table linkage (only done on non-joins, as this removes a whole lot of potential complexities -- if people are doing joins they go a little further to do this manually anyway; also we make sure we're operating on our site's table prefix so we don't collect meta info for the wrong table set)
if ($lang_fields===NULL)
{
if (($table!='translate') && (strpos($table,' ')===false) && (isset($GLOBALS['SITE_DB'])) && ($this->table_prefix==$GLOBALS['SITE_DB']->table_prefix))
{
global $TABLE_LANG_FIELDS;
$lang_fields_provisional=isset($TABLE_LANG_FIELDS[$table])?$TABLE_LANG_FIELDS[$table]:array();
$lang_fields=array();
if ($lang_fields_provisional!=array())
{
$full_table.=' main';
foreach ($select as $i=>$s)
{
if (!is_string($s))
{
$lang_fields_provisional=array();
break; // Bad API call, but we'll let it fail naturally
}
if (preg_match('#^[A-Za-z\_\*]+$#',$s)!=0)
$select[$i]='main.'.$s;
}
if ($where_map!==NULL)
{
foreach ($where_map as $i=>$s)
{
if (!is_string($i))
{
$lang_fields_provisional=array();
break; // Bad API call, but we'll let it fail naturally
}
if (preg_match('#^[A-Za-z\_]+$#',$i)!=0)
{
unset($where_map[$i]);
$where_map['main.'.$i]=$s;
}
}
}
if ($end!='')
{
$end=preg_replace('#(^|,|\s)([a-z]+)($|,|\s)#','${1}main.${2}${3}',$end);
}
$field_prefix='main.';
foreach ($lang_fields_provisional as $lang_field)
{
if ((in_array($field_prefix.$lang_field,$select)) || (in_array($field_prefix.'*',$select)))
{
$lang_fields[]=$lang_field;
}
}
}
}
}
return $this->_query($this->_get_where_expand($full_table,$select,$where_map,$end),$max,$start,$fail_ok,false,$lang_fields,$field_prefix);
}
?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 |
function query($query,$max=NULL,$start=NULL,$fail_ok=false,$skip_safety_check=false,$lang_fields=NULL,$field_prefix='')
{
if (!$skip_safety_check)
{
$_query=strtolower($query);
$queries=1;//substr_count($_query,'insert into ')+substr_count($_query,'replace into ')+substr_count($_query,'update ')+substr_count($_query,'select ')+substr_count($_query,'delete from '); Not reliable
if ((strpos(preg_replace('#\'[^\']*\'#','\'\'',str_replace('\\\'','',$_query)),' union ')!==false) || ($queries>1)) log_hack_attack_and_exit('SQL_INJECTION_HACK',$query);
}
return $this->_query($query,$max,$start,$fail_ok,false,$lang_fields,$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)
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 |
function _query($query,$max=NULL,$start=NULL,$fail_ok=false,$get_insert_id=false,$lang_fields=NULL,$field_prefix='',$save_as_volatile=false)
{
global $QUERY_COUNT,$NO_QUERY_LIMIT,$QUERY_LOG,$QUERY_LIST,$DEBUG_MODE,$IN_MINIKERNEL_VERSION,$QUERY_FILE_LOG,$UPON_QUERY_HOOKS;
if ($QUERY_FILE_LOG!==NULL)
{
fwrite($QUERY_FILE_LOG,$query.';'.chr(10).chr(10));
}
if ($DEBUG_MODE)
{
if ((get_forum_type()!='none') && (strpos($query,get_table_prefix().'f_')!==false) && (strpos($query,get_table_prefix().'f_')<100) && (strpos($query,'f_welcome_emails')===false) && ($this->connection_write===$GLOBALS['SITE_DB']->connection_write) && (isset($GLOBALS['FORUM_DB'])) && ($GLOBALS['SITE_DB']->connection_write!==$GLOBALS['FORUM_DB']->connection_write) && (!$GLOBALS['NO_DB_SCOPE_CHECK']))
{
/*file_put_contents(get_file_base().'/uploads/downloads/test.txt',var_export(debug_backtrace(),true));
@exit($query);
@debug_print_backtrace();*/
fatal_exit('Using OCF queries on the wrong driver');
}
}
if (!$NO_QUERY_LIMIT)
{
$QUERY_COUNT++;
//@exit('!');
//if ($QUERY_COUNT>10) @ob_end_clean();@print('Query: '.$query.chr(10));
}
static $fb=NULL;
if ($fb===NULL) $fb=function_exists('fb');
if (($fb) && (!headers_sent()) && (get_param_integer('keep_firephp_queries',0)==1) && (function_exists('fb')))
{
fb('Query: '.$query);
}
if (($QUERY_COUNT==68) && (get_param_integer('keep_no_query_limit',0)==0) && (count($_POST)==0) && (get_page_name()!='admin_importer') && ($IN_MINIKERNEL_VERSION==0) && (get_param('special_page_type','')!='query'))
{
$NO_QUERY_LIMIT=true;
$log_path=get_custom_file_base().'/data_custom/big_query_screens.log';
if (is_writable_wrap($log_path))
{
$myfile=fopen($log_path,'at');
fwrite($myfile,get_self_url_easy().chr(10));
fclose($myfile);
}
if ($DEBUG_MODE)
{
$QUERY_COUNT=0;
fatal_exit(do_lang_tempcode('TOO_MANY_QUERIES'));
}
}
$lang_strings_expecting=array();
if ((isset($lang_fields[0])) && (function_exists('user_lang')))
{
$lang=user_lang(); // We can we assume this, as we will cache against it -- if subsequently code wants something else it'd be a cache miss which is fine
foreach ($lang_fields as $i=>$field)
{
$_i=strval($i);
$join=' LEFT JOIN '.$this->table_prefix.'translate t'.$_i.' ON t'.$_i.'.id='.$field_prefix.$field.' AND '.db_string_equal_to('t'.$_i.'.language',$lang);
$_query=strtoupper($query);
$from_pos=strpos($_query,' FROM ');
$where_pos=strpos($_query,' WHERE ');
if ($where_pos===false)
{
$_where_pos=0;
do
{
$_where_pos=strpos($_query,' GROUP BY ',$_where_pos+1);
if ($_where_pos!==false) $where_pos=$_where_pos;
}
while ($_where_pos!==false);
}
if ($where_pos===false)
{
$_where_pos=0;
do
{
$_where_pos=strpos($_query,' ORDER BY ',$_where_pos+1);
if ($_where_pos!==false) $where_pos=$_where_pos;
}
while ($_where_pos!==false);
}
if ($where_pos!==false)
{
$query=substr($query,0,$where_pos).$join.substr($query,$where_pos);
} else
{
$query.=$join;
}
$original='t'.$_i.'.text_original AS t'.$_i.'__text_original';
$parsed='t'.$_i.'.text_parsed AS t'.$_i.'__text_parsed';
$query=substr($query,0,$from_pos).','.$original.','.$parsed.substr($query,$from_pos);
$lang_strings_expecting[]=array($field,'t'.$_i.'__text_original','t'.$_i.'__text_parsed');
}
}
if ($start<0) $start=0;
if ($max<0) $max=1;
if ($QUERY_LOG)
{
$before=microtime(false);
}
if (substr(strtoupper($query),0,7)=='SELECT ')
{
$connection=&$this->connection_read;
} else
{
$connection=&$this->connection_write;
}
if (isset($connection[4])) // Okay, we can't be lazy anymore
{
$connection=call_user_func_array(array($this->static_ob,'db_get_connection'),$connection);
_general_db_init();
}
$ret=$this->static_ob->db_query($query,$connection,$max,$start,$fail_ok,$get_insert_id,false,$save_as_volatile);
if ($QUERY_LOG)
{
$after=microtime(false);
$text=(!is_null($max))?$query.' ('.strval((integer)$start).'-'.strval((integer)$start+$max).')':$query;
$out=array('time'=>microtime_diff($after,$before),'text'=>$text);
$QUERY_LIST[]=$out;
}
// Run hooks, if any exist
if ($UPON_QUERY_HOOKS===NULL)
{
if (!function_exists('find_all_hooks')) return $ret;
$UPON_QUERY_HOOKS=array();
$hooks=find_all_hooks('systems','upon_query');
foreach (array_keys($hooks) as $hook)
{
require_code('hooks/systems/upon_query/'.filter_naughty($hook));
$UPON_QUERY_HOOKS[$hook]=object_factory('upon_query_'.filter_naughty($hook),true);
}
}
foreach ($UPON_QUERY_HOOKS as $ob)
{
if ($ob!==NULL)
$ob->run($this,$query,$max,$start,$fail_ok,$get_insert_id,$ret);
}
// Copy results to lang cache, but only if not null AND unset to avoid any confusion
if ($ret!==NULL)
{
foreach ($lang_strings_expecting as $bits)
{
list($field,$original,$parsed)=$bits;
foreach ($ret as $row)
{
$entry=$row[$field];
if (($row[$original]!==NULL) && (count($this->text_lookup_original_cache)<=1000))
$this->text_lookup_original_cache[$entry]=$row[$original];
if (($row[$parsed]!==NULL) && (count($this->text_lookup_cache)<=1000))
$this->text_lookup_cache[$entry]=$row[$parsed];
unset($row[$original]);
unset($row[$parsed]);
}
}
}
return $ret;
}
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)
function create_table($table_name,$fields,$skip_size_check=false,$skip_null_check=false)
{
require_code('database_helper');
_helper_create_table($this,$table_name,$fields,$skip_size_check,$skip_null_check);
}
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)
function create_index($table_name,$index_name,$fields,$unique_key_field='id')
{
require_code('database_helper');
_helper_create_index($this,$table_name,$index_name,$fields,$unique_key_field);
}
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)
function delete_index_if_exists($table_name,$index_name)
{
require_code('database_helper');
_helper_delete_index_if_exists($this,$table_name,$index_name);
}
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)
function drop_if_exists($table)
{
require_code('database_helper');
_helper_drop_if_exists($this,$table);
}
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)
function rename_table($old,$new)
{
require_code('database_helper');
_helper_rename_table($this,$old,$new);
}
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)
function add_table_field($table_name,$name,$_type,$default=NULL)
{
require_code('database_helper');
_helper_add_table_field($this,$table_name,$name,$_type,$default);
}
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)
function alter_table_field($table_name,$name,$_type,$new_name=NULL)
{
require_code('database_helper');
_helper_alter_table_field($this,$table_name,$name,$_type,$new_name);
}
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)
function change_primary_key($table_name,$new_key)
{
require_code('database_helper');
_helper_change_primary_key($this,$table_name,$new_key);
}
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)
function promote_text_field_to_comcode($table_name,$name,$key='id',$level=2,$in_assembly=false)
{
require_code('database_helper');
_helper_promote_text_field_to_comcode($this,$table_name,$name,$key,$level,$in_assembly);
}
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)
function delete_table_field($table_name,$name)
{
require_code('database_helper');
_helper_delete_table_field($this,$table_name,$name);
}
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)
function refresh_field_definition($type)
{
require_code('database_helper');
_helper_refresh_field_definition($this,$type);
}
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 |
function get_db_keywords()
{
$words=array(
'ABSOLUTE','ACCESS','ACCESSIBLE','ACTION','ACTIVE','ADA','ADD','ADMIN',
'AFTER','ALIAS','ALL','ALLOCATE','ALLOW','ALPHANUMERIC','ALTER','ANALYSE',
'ANALYZE','AND','ANY','APPLICATION','ARE','ARITH_OVERFLOW','ARRAY','AS',
'ASC','ASCENDING','ASENSITIVE','ASSERTION','ASSISTANT','ASSOCIATE','ASUTIME','ASYMMETRIC',
'ASYNC','AT','ATOMIC','AUDIT','AUTHORIZATION','AUTO','AUTODDL','AUTOINCREMENT',
'AUX','AUXILIARY','AVG','BACKUP','BASED','BASENAME','BASE_NAME','BEFORE',
'BEGIN','BETWEEN','BIGINT','BINARY','BIT','BIT_LENGTH','BLOB','BLOBEDIT',
'BOOLEAN','BOTH','BOTTOM','BREADTH','BREAK','BROWSE','BUFFER','BUFFERPOOL',
'BULK','BY','BYTE','CACHE','CALL','CALLED','CAPABILITY','CAPTURE',
'CASCADE','CASCADED','CASE','CAST','CATALOG','CCSID','CHANGE','CHAR',
'CHARACTER','CHARACTER_LENGTH','CHAR_CONVERT','CHAR_LENGTH','CHECK','CHECKPOINT','CHECK_POINT_LEN','CHECK_POINT_LENGTH',
'CLOB','CLOSE','CLUSTER','CLUSTERED','COALESCE','COLLATE','COLLATION','COLLECTION',
'COLLID','COLUMN','COLUMNS','COMMENT','COMMIT','COMMITTED','COMPACTDATABASE','COMPILETIME',
'COMPLETION','COMPRESS','COMPUTE','COMPUTED','CONCAT','CONDITION','CONDITIONAL','CONFIRM',
'CONFLICT','CONNECT','CONNECTION','CONSTRAINT','CONSTRAINTS','CONSTRUCTOR','CONTAINER','CONTAINING',
'CONTAINS','CONTAINSTABLE','CONTINUE','CONTROLROW','CONVERT','CORRESPONDING','COUNT','COUNTER',
'CREATE','CREATEDATABASE','CREATEFIELD','CREATEGROUP','CREATEINDEX','CREATEOBJECT','CREATEPROPERTY','CREATERELATION',
'CREATETABLEDEF','CREATEUSER','CREATEWORKSPACE','CROSS','CSTRING','CUBE','CURRENCY','CURRENT',
'CURRENTUSER','CURRENT_DATE','CURRENT_DEFAULT_TRANSFORM_GROUP','CURRENT_LC_CTYPE','CURRENT_PATH','CURRENT_ROLE','CURRENT_TIME','CURRENT_TIMESTAMP',
'CURRENT_TRANSFORM_GROUP_FOR_TYPE','CURRENT_USER','CURSOR','CYCLE','DATA','DATABASE','DATABASES','DATA_PGS',
'DATE','DATETIME','DAY',/*'DAYS',*/'DAY_HOUR','DAY_MICROSECOND','DAY_MINUTE','DAY_SECOND',
'DB2SQL','DBCC','DBINFO','DBSPACE','DB_KEY','DEALLOCATE','DEBUG','DEC',
'DECIMAL','DECLARE','DEFAULT','DEFERRABLE','DEFERRED','DELAYED','DELETE','DELETING',
'DENY','DEPTH','DEREF','DESC','DESCENDING','DESCRIBE',/*'DESCRIPTION',*/'DESCRIPTOR',
'DETERMINISTIC','DIAGNOSTICS','DICTIONARY','DISALLOW','DISCONNECT','DISK','DISPLAY','DISTINCT',
'DISTINCTROW','DISTRIBUTED','DIV','DO','DOCUMENT','DOMAIN','DOUBLE','DROP',
'DSNHATTR','DSSIZE','DUAL','DUMMY','DUMP','DYNAMIC','EACH','ECHO',
'EDIT','EDITPROC','ELEMENT','ELSE','ELSEIF','ENCLOSED','ENCODING','ENCRYPTED',
'ENCRYPTION','END','END-EXEC','ENDIF','ENDING','ENDTRAN','ENTRY_POINT','EQUALS',
'EQV','ERASE','ERRLVL','ERROR','ERROREXIT','ESCAPE','ESCAPED','EVENT',
'EXCEPT','EXCEPTION','EXCLUSIVE','EXEC','EXECUTE','EXISTING','EXISTS','EXIT',
'EXPLAIN','EXTERN','EXTERNAL','EXTERNLOGIN','EXTRACT','FALSE','FENCED','FETCH',
'FIELD','FIELDPROC','FIELDS','FILE','FILLCACHE','FILLFACTOR','FILTER','FINAL',
'FIRST','FLOAT','FLOAT4','FLOAT8','FLOPPY','FOR','FORCE','FOREIGN',
'FORM','FORMS','FORTRAN','FORWARD','FOUND','FREE','FREETEXT','FREETEXTTABLE',
'FREEZE','FREE_IT','FROM','FULL','FULLTEXT','FUNCTION','GDSCODE','GENERAL',
'GENERATED','GENERATOR','GEN_ID',/*'GET',*/'GETOBJECT','GETOPTION','GLOB','GLOBAL',
'GO','GOTO','GOTOPAGE','GRANT','GROUP','GROUPING','GROUP_COMMIT_WAIT','GROUP_COMMIT_WAIT_TIME',
'GUID','HANDLER','HAVING','HELP','HIGH_PRIORITY','HOLD','HOLDLOCK','HOUR',
'HOURS','HOUR_MICROSECOND','HOUR_MINUTE','HOUR_SECOND','IDENTIFIED','IDENTITY','IDENTITYCOL','IDENTITY_INSERT',
'IDLE','IEEEDOUBLE','IEEESINGLE','IF','IGNORE','ILIKE','IMMEDIATE','IMP',
'IN','INACTIVE','INCLUDE','INCLUSIVE','INCREMENT','INDEX','INDEXES','INDEX_LPAREN',
'INDICATOR','INFILE','INHERIT','INIT','INITIAL','INITIALLY','INNER','INOUT',
'INPUT','INPUT_TYPE','INSENSITIVE','INSERT','INSERTING','INSERTTEXT','INSTALL','INSTEAD',
'INT','INT1','INT2','INT3','INT4','INT8','INTEGER','INTEGER1',
'INTEGER2','INTEGER4','INTEGRATED','INTERSECT','INTERVAL','INTO','IQ','IS',
'ISNULL','ISOBID','ISOLATION','ISQL','ITERATE','JAR','JAVA','JOIN',
'KEY','KEYS','KILL','LABEL',/*'LANGUAGE',*/'LARGE','LAST','LASTMODIFIED',
'LATERAL','LC_CTYPE','LC_MESSAGES','LC_TYPE','LEADING','LEAVE','LEFT','LENGTH',
'LESS','LEV','LEVEL','LIKE','LIMIT','LINEAR','LINENO','LINES',
'LOAD','LOCAL','LOCALE','LOCALTIME','LOCALTIMESTAMP','LOCATOR','LOCATORS','LOCK',
'LOCKMAX','LOCKSIZE','LOGFILE','LOGICAL','LOGICAL1','LOGIN','LOG_BUFFER_SIZE','LOG_BUF_SIZE',
'LONG','LONGBINARY','LONGBLOB','LONGTEXT','LOOP','LOWER','LOW_PRIORITY','MACRO',
'MAINTAINED','MANUAL','MAP','MATCH','MATERIALIZED','MAX','MAXEXTENTS','MAXIMUM',
'MAXIMUM_SEGMENT','MAX_SEGMENT','MEDIUMBLOB','MEDIUMINT','MEDIUMTEXT','MEMBER','MEMBERSHIP','MEMO',
'MERGE','MESSAGE','METHOD','MICROSECOND','MICROSECONDS','MIDDLEINT','MIN','MINIMUM',
'MINUS','MINUTE','MINUTES','MINUTE_MICROSECOND','MINUTE_SECOND','MIRROR','MIRROREXIT','MLSLABEL',
'MOD','MODE','MODIFIES','MODIFY','MODULE','MODULE_NAME','MONEY','MONTH',
'MONTHS','MOVE','MULTISET',/*'NAME',*/'NAMES','NATIONAL','NATURAL','NCHAR',
'NCLOB','NEW','NEWPASSWORD','NEXT','NEXTVAL','NO','NOAUDIT','NOAUTO',
'NOCHECK','NOCOMPRESS','NOHOLDLOCK','NONCLUSTERED','NONE','NOT','NOTIFY','NOTNULL',
'NOWAIT','NO_WRITE_TO_BINLOG','NULL','NULLIF','NULLS','NUMBER','NUMERIC','NUMERIC_TRUNCATION',
'NUMPARTS','NUM_LOG_BUFFERS','NUM_LOG_BUFS','OBID','OBJECT','OCTET_LENGTH','OF','OFF',
'OFFLINE','OFFSET','OFFSETS','OID','OLD','OLEOBJECT','ON','ONCE',
'ONLINE','ONLY','OPEN','OPENDATASOURCE','OPENQUERY','OPENRECORDSET','OPENROWSET','OPENXML',
'OPERATION','OPERATORS','OPTIMIZATION','OPTIMIZE','OPTION','OPTIONALLY','OPTIONS','OR',
'ORDER','ORDINALITY','OTHERS','OUT','OUTER','OUTFILE','OUTPUT','OUTPUT_TYPE',
'OVER','OVERFLOW','OVERLAPS','OWNERACCESS','PACKAGE','PAD','PADDED','PAGE',
'PAGELENGTH',/*'PAGES',*/'PAGE_SIZE','PARAMETER','PARAMETERS','PART','PARTIAL','PARTITION',
'PARTITIONED','PARTITIONING','PASCAL','PASSTHROUGH','PASSWORD',/*'PATH',*/'PCTFREE','PENDANT',
'PERCENT','PERM','PERMANENT','PIECESIZE','PIPE','PIVOT','PLACING','PLAN',
'POSITION','POST_EVENT','PRECISION','PREORDER','PREPARE','PRESERVE','PREVVAL','PRIMARY',
'PRINT','PRIOR','PRIQTY','PRIVATE','PRIVILEGES','PROC','PROCEDURE','PROCESSEXIT',
'PROGRAM','PROPERTY','PROTECTED','PSID','PUBLIC','PUBLICATION','PURGE','QUERIES',
'QUERY','QUERYNO','QUIT','RAID0','RAISERROR','RANGE','RAW','RAW_PARTITIONS',
'READ','READS','READTEXT','READ_ONLY','READ_WRITE','REAL','RECALC','RECONFIGURE',
'RECORDSET','RECORD_VERSION','RECURSIVE','REF','REFERENCE','REFERENCES','REFERENCING','REFRESH',
'REFRESHLINK','REGEXP','REGISTERDATABASE','RELATION','RELATIVE','RELEASE','REMOTE','REMOVE',
'RENAME','REORGANIZE','REPAINT','REPAIRDATABASE','REPEAT','REPEATABLE','REPLACE','REPLICATION',
'REPORT','REPORTS','REQUERY','REQUIRE','RESERV','RESERVED_PGS','RESERVING','RESIGNAL',
'RESOURCE','RESTORE','RESTRICT','RESULT','RESULT_SET_LOCATOR','RETAIN','RETURN','RETURNING_VALUES',
'RETURNS','REVOKE','RIGHT','RLIKE','ROLE','ROLLBACK','ROLLUP','ROUTINE',
'ROW','ROWCNT','ROWCOUNT','ROWGUIDCOL','ROWID','ROWLABEL','ROWNUM','ROWS',
'ROWSET','RULE','RUN','RUNTIME','SAVE','SAVEPOINT','SCHEMA','SCHEMAS',
'SCOPE','SCRATCHPAD','SCREEN','SCROLL','SEARCH','SECOND','SECONDS','SECOND_MICROSECOND',
'SECQTY',/*'SECTION',*/'SECURITY','SELECT','SENSITIVE','SEPARATOR','SEQUENCE','SERIALIZABLE',
'SESSION','SESSION_USER','SET','SETFOCUS','SETOPTION','SETS','SETUSER','SHADOW',
'SHARE','SHARED','SHELL','SHORT','SHOW','SHUTDOWN','SIGNAL','SIMILAR',
'SIMPLE','SINGLE','SINGULAR','SIZE','SMALLINT','SNAPSHOT','SOME','SONAME',
'SORT','SOURCE','SPACE','SPATIAL','SPECIFIC','SPECIFICTYPE','SQL','SQLCA',
'SQLCODE','SQLERROR','SQLEXCEPTION','SQLSTATE','SQLWARNING','SQL_BIG_RESULT','SQL_CALC_FOUND_ROWS','SQL_SMALL_RESULT',
'SSL','STABILITY','STANDARD','START','STARTING','STARTS','STATE','STATEMENT',
'STATIC','STATISTICS','STAY','STDEV','STDEVP','STOGROUP','STOP','STORES',
'STRAIGHT_JOIN','STRING','STRIPE','STRUCTURE','STYLE','SUBMULTISET','SUBPAGES','SUBSTRING',
'SUBTRANS','SUBTRANSACTION','SUB_TYPE','SUCCESSFUL','SUM','SUMMARY','SUSPEND','SYB_IDENTITY',
'SYB_RESTREE','SYMMETRIC','SYNCHRONIZE','SYNONYM','SYNTAX_ERROR','SYSDATE','SYSFUN','SYSIBM',
'SYSPROC','SYSTEM','SYSTEM_USER','TABLE','TABLEDEF','TABLEDEFS','TABLEID','TABLES',
'TABLESAMPLE','TABLESPACE','TAPE','TEMP','TEMPORARY','TERMINATED','TERMINATOR','TEST',
'TEXT','TEXTSIZE','THEN','THERE','TIME','TIMESTAMP','TIMEZONE_HOUR','TIMEZONE_MINUTE',
'TINYBLOB','TINYINT','TINYTEXT','TO','TOP','TRAILING','TRAN','TRANSACTION',
'TRANSFORM','TRANSLATE','TRANSLATION','TREAT','TRIGGER','TRIM','TRUE','TRUNCATE',
'TSEQUAL','TYPE','UID','UNBOUNDED','UNCOMMITTED','UNDER','UNDO','UNION',
'UNIQUE','UNIQUEIDENTIFIER','UNKNOWN','UNLOCK','UNNEST','UNSIGNED','UNTIL','UPDATE',
'UPDATETEXT','UPDATING','UPGRADE','UPPER','USAGE','USE','USED_PGS','USER',
'USER_OPTION','USING','UTC_DATE','UTC_TIME','UTC_TIMESTAMP','VALIDATE','VALIDPROC','VALUE',
'VALUES','VAR','VARBINARY','VARCHAR','VARCHAR2','VARCHARACTER','VARIABLE','VARIANT',
'VARP','VARYING','VCAT','VERBOSE','VERSION','VIEW','VIRTUAL','VISIBLE',
'VOLATILE','VOLUMES','WAIT','WAITFOR','WEEKDAY','WHEN','WHENEVER','WHERE',
'WHILE','WINDOW','WITH','WITHIN','WITHOUT','WITH_CUBE','WITH_LPAREN','WITH_ROLLUP',
'WLM','WORK','WORKSPACE','WRITE','WRITETEXT','X509','XMLELEMENT','XOR',
'YEAR','YEARDAY','YEARS','YEAR_MONTH','YES','YESNO','ZEROFILL','ZONE',
);
return $words;
}
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 |
function get_false_permissions()
{
return array( array('GENERAL_SETTINGS','bypass_flood_control'),
array('_COMCODE','allow_html'),
array('GENERAL_SETTINGS','remove_page_split'),
array('STAFF_ACTIONS','access_closed_site'),
array('STAFF_ACTIONS','bypass_bandwidth_restriction'),
array('_COMCODE','comcode_dangerous'),
array('_COMCODE','comcode_nuisance'),
array('STAFF_ACTIONS','see_php_errors'),
array('STAFF_ACTIONS','see_stack_dump'),
array('GENERAL_SETTINGS','bypass_word_filter'),
array('STAFF_ACTIONS','view_profiling_modes'),
array('STAFF_ACTIONS','access_overrun_site'),
array('SUBMISSION','bypass_validation_highrange_content'),
array('SUBMISSION','bypass_validation_midrange_content'),
array('SUBMISSION','edit_highrange_content'),
array('SUBMISSION','edit_midrange_content'),
array('SUBMISSION','edit_lowrange_content'),
array('SUBMISSION','edit_own_highrange_content'),
array('SUBMISSION','edit_own_midrange_content'),
array('SUBMISSION','delete_highrange_content'),
array('SUBMISSION','delete_midrange_content'),
array('SUBMISSION','delete_lowrange_content'),
array('SUBMISSION','delete_own_highrange_content'),
array('SUBMISSION','delete_own_midrange_content'),
array('SUBMISSION','delete_own_lowrange_content'),
array('SUBMISSION','can_submit_to_others_categories'),
array('SUBMISSION','search_engine_links'),
array('STAFF_ACTIONS','view_content_history'),
array('STAFF_ACTIONS','restore_content_history'),
array('STAFF_ACTIONS','delete_content_history'),
array('SUBMISSION','submit_cat_highrange_content'),
array('SUBMISSION','submit_cat_midrange_content'),
array('SUBMISSION','submit_cat_lowrange_content'),
array('SUBMISSION','edit_cat_highrange_content'),
array('SUBMISSION','edit_cat_midrange_content'),
array('SUBMISSION','edit_cat_lowrange_content'),
array('SUBMISSION','delete_cat_highrange_content'),
array('SUBMISSION','delete_cat_midrange_content'),
array('SUBMISSION','delete_cat_lowrange_content'),
array('SUBMISSION','edit_own_cat_highrange_content'),
array('SUBMISSION','edit_own_cat_midrange_content'),
array('SUBMISSION','edit_own_cat_lowrange_content'),
array('SUBMISSION','delete_own_cat_highrange_content'),
array('SUBMISSION','delete_own_cat_midrange_content'),
array('SUBMISSION','delete_own_cat_lowrange_content'),
array('SUBMISSION','mass_import'),
);
}
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 |
function config_option_exists($name)
{
$test=get_option($name,true);
return !is_null($test);
}
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 |
function permission_exists($name)
{
$test=$GLOBALS['SITE_DB']->query_value_null_ok('sp_list','the_name',array('the_name'=>$name));
return !is_null($test);
}
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)
function add_config_option($human_name,$name,$type,$eval,$category,$group,$shared_hosting_restricted=0,$data='')
{
if (!in_array($type,array('float','integer','tick','line','text','transline','transtext','list','date','?forum','forum','category','usergroup','colour')))
fatal_exit('Invalid config option type');
$map=array('c_set'=>0,'config_value'=>'','the_name'=>$name,'human_name'=>$human_name,'the_type'=>$type,'eval'=>$eval,'the_page'=>$category,'section'=>$group,'explanation'=>'CONFIG_OPTION_'.$name,'shared_hosting_restricted'=>$shared_hosting_restricted,'c_data'=>$data);
if ($GLOBALS['IN_MINIKERNEL_VERSION']==0)
{
$GLOBALS['SITE_DB']->query_insert('config',$map,false,true); // Allow failure in case the config option got auto-installed through searching (can happen if the option is referenced efore the module installs right)
} else
{
$GLOBALS['SITE_DB']->query_insert('config',$map); // From installer we want to know if there are errors in our install cycle
}
if (function_exists('persistant_cache_delete')) persistant_cache_delete('OPTIONS');
global $OPTIONS;
if ($OPTIONS==array()) // Installer might not have loaded any yet
{
load_options();
} else
{
$OPTIONS[$name]=$map;
if (multi_lang())
{
unset($OPTIONS[$name]['config_value_translated']);
}
}
}
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)
function delete_config_option($name)
{
$rows=$GLOBALS['SITE_DB']->query_select('config',array('*'),array('the_name'=>$name),'',1);
if (array_key_exists(0,$rows))
{
$myrow=$rows[0];
if ((($myrow['the_type']=='transline') || ($myrow['the_type']=='transtext')) && (is_numeric($myrow['config_value'])))
{
delete_lang($myrow['config_value']);
}
$GLOBALS['SITE_DB']->query_delete('config',array('the_name'=>$name),'',1);
/*global $OPTIONS; Don't do this, it will cause problems in some parts of the code
unset($OPTIONS[$name]);*/
}
if (function_exists('persistant_cache_delete')) persistant_cache_delete('OPTIONS');
}
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)
function add_specific_permission($section,$name,$default=false,$not_even_mods=false)
{
if (!$not_even_mods) // NB: Don't actually need to explicitly give admins privileges
{
$usergroups=$GLOBALS['FORUM_DRIVER']->get_usergroup_list(false,true);
$admin_groups=array_merge($GLOBALS['FORUM_DRIVER']->get_super_admin_groups(),$GLOBALS['FORUM_DRIVER']->get_moderator_groups());
foreach (array_keys($usergroups) as $id)
{
if (($default) || (in_array($id,$admin_groups)))
{
$GLOBALS['SITE_DB']->query_insert('gsp',array('specific_permission'=>$name,'group_id'=>$id,'the_page'=>'','module_the_name'=>'','category_name'=>'','the_value'=>1));
}
}
}
$GLOBALS['SITE_DB']->query_insert('sp_list',array('p_section'=>$section,'the_name'=>$name,'the_default'=>($default?1:0)));
}
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)
function set_specific_permission($group_id,$permission,$value,$page=NULL,$category_type=NULL,$category_name=NULL)
{
if (is_null($page)) $page='';
if (is_null($category_type)) $category_type='';
if (is_null($category_name)) $category_name='';
$GLOBALS['SITE_DB']->query_delete('gsp',array('specific_permission'=>$permission,'group_id'=>$group_id,'the_page'=>$page,'module_the_name'=>$category_type,'category_name'=>$category_name),'',1);
$GLOBALS['SITE_DB']->query_insert('gsp',array('specific_permission'=>$permission,'group_id'=>$group_id,'the_page'=>$page,'module_the_name'=>$category_type,'category_name'=>$category_name,'the_value'=>$value?1:0));
}
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)
function delete_specific_permission($name)
{
$GLOBALS['SITE_DB']->query_delete('sp_list',array('the_name'=>$name),'',1);
$GLOBALS['SITE_DB']->query('DELETE FROM '.get_table_prefix().'gsp WHERE '.db_string_not_equal_to('module_the_name','forums').' AND '.db_string_equal_to('specific_permission',$name));
}
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)
function delete_attachments($type,$connection=NULL)
{
if (is_null($connection)) $connection=$GLOBALS['SITE_DB'];
require_code('attachments2');
require_code('attachments3');
// Clear any de-referenced attachments
$before=$connection->query_select('attachment_refs',array('a_id','id'),array('r_referer_type'=>$type));
foreach ($before as $ref)
{
// Delete reference (as it's not actually in the new comcode!)
$connection->query_delete('attachment_refs',array('id'=>$ref['id']),'',1);
// Was that the last reference to this attachment? (if so -- delete attachment)
$test=$connection->query_value_null_ok('attachment_refs','id',array('a_id'=>$ref['a_id']));
if (is_null($test))
{
_delete_attachment($ref['a_id'],$connection);
}
}
}
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)
function mass_delete_lang($table,$attrs,$connection)
{
if (count($attrs)==0) return;
if (is_null($connection)) $connection=$GLOBALS['SITE_DB'];
$rows=$connection->query_select($table,$attrs,NULL,'',NULL,NULL,true);
if (!is_null($rows))
{
foreach ($rows as $row)
{
foreach ($attrs as $attr)
{
delete_lang($row[$attr],$connection);
}
}
}
}
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)
function init__database_search()
{
$GLOBALS['TOTAL_RESULTS']=0;
}
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 |
function generate_text_summary($_temp_summary,$words_searched)
{
require_code('xhtml');
$summary='';
global $SEARCH__CONTENT_BITS;
$_temp_summary_lower=strtolower($_temp_summary);
// Add in some highlighting direct to XHTML
$all_occurrences=array();
foreach (is_null($SEARCH__CONTENT_BITS)?array():$SEARCH__CONTENT_BITS as $content_bit)
{
if ($content_bit=='') continue;
$last_pos=0;
$content_bit_pos=0;
do
{
$content_bit_matched=$content_bit;
if (strtoupper($content_bit)==$content_bit) // all upper case so don't want case sensitive
{
$content_bit_pos=strpos($_temp_summary,$content_bit,$last_pos);
} else
{
$content_bit_pos=strpos($_temp_summary_lower,strtolower($content_bit),$last_pos);
if (strpos($content_bit,'-')!==false)
{
$content_bit_pos_2=strpos($_temp_summary_lower,strtolower(str_replace('-','',$content_bit)),$last_pos);
if (($content_bit_pos_2!==false) && (($content_bit_pos===false) || ($content_bit_pos_2<$content_bit_pos)))
{
$content_bit_pos=$content_bit_pos_2;
$content_bit_matched=str_replace('-','',$content_bit);
}
}
}
if ($content_bit_pos!==false)
{
$last_gt=strrpos(substr($_temp_summary,0,$content_bit_pos),'>');
$last_lt=strrpos(substr($_temp_summary,0,$content_bit_pos),'<');
if (($last_gt!==false) && ($last_gt>$last_lt))
{
$extra_pre='<span class="comcode_highlight">';
$extra_post='</span>';
$_temp_summary=substr($_temp_summary,0,$content_bit_pos).
$extra_pre.
substr($_temp_summary,$content_bit_pos,strlen($content_bit_matched)).
$extra_post.
substr($_temp_summary,$content_bit_pos+strlen($content_bit_matched));
$_temp_summary_lower=strtolower($_temp_summary);
$last_pos=$content_bit_pos+strlen($extra_pre)+strlen($content_bit_matched)+strlen($extra_post);
// Adjust all stores occurrence offsets
foreach ($all_occurrences as $i=>$occ)
{
if ($occ[0]>$last_pos)
{
$all_occurrences[$i][0]+=strlen($extra_pre)+strlen($extra_post);
$all_occurrences[$i][1]+=strlen($extra_pre)+strlen($extra_post);
}
elseif ($occ[0]>$content_bit_pos)
{
$all_occurrences[$i][0]+=strlen($extra_pre);
$all_occurrences[$i][1]+=strlen($extra_pre);
}
}
$all_occurrences[]=array($content_bit_pos,$last_pos);
} else
{
$last_pos=$content_bit_pos+strlen($content_bit_matched);
}
}
}
while ($content_bit_pos!==false);
}
if (strlen($_temp_summary)<500)
{
$summary=$_temp_summary;
} else
{
// Find optimal position
$len=strlen($_temp_summary);
$best_yet=0;
$best_pos_min=250;
$best_pos_max=250;
if (count($all_occurrences)<60) // Only bother doing this if we need to dig for the keyword
{
for ($i=250;$i<$len-250;$i++) // Move window along all possible positions
{
$count=0;
$i_pre=$i-250;
$i_post=$i+250;
foreach ($all_occurrences as $occ)
{
$occ_pre=$occ[0];
$occ_post=$occ[1];
if (($occ_pre>=$i_pre) && ($occ_pre<=$i_post) && ($occ_post>=$i_pre) && ($occ_post<=$i_post))
{
$count++;
if ($count>5) break; // Good enough
}
}
if (($count>$best_yet) || (($best_yet==$count) && ($i-500<$best_pos_min)))
{
if ($best_yet==$count)
{
$best_pos_max=$i;
} else
{
$best_yet=$count;
$best_pos_min=$i;
$best_pos_max=$i;
}
if ($count>5) break; // Good enough
}
}
$best_pos=intval(floatval($best_pos_min+$best_pos_max)/2.0)-250; // Move it from center pos, to where we want to start from
} else
{
$best_pos=0;
}
// Render (with ellipses if required)
if (false)
{ // Far far too slow
$summary=xhtml_substr($_temp_summary,$best_pos,min(500,$len-$best_pos),true,true);
} else
{
$summary=substr($_temp_summary,$best_pos,min(500,$len-$best_pos));
$summary=xhtmlise_html($summary,true);
if ($best_pos>0) $summary='…'.$summary;
if ($best_pos+500<strlen($_temp_summary)) $summary.='…';
}
}
return $summary;
}
void opensearch_script()
Server opensearch requests.
Parameters…
(No return value)
function opensearch_script()
{
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
if (!has_actual_page_access(get_member(),'search')) return; // No access
$type=get_param('type','misc');
switch ($type)
{
// Make a search suggestion (like Google Suggest)
case 'suggest':
header('Content-type: text/plain; charset='.get_charset());
$request=get_param('request',false,true);
if (strlen($request)<3) return;
$suggestions=array();
$q='SELECT s_primary,COUNT(*) as cnt,MAX(s_num_results) AS s_num_results FROM '.get_table_prefix().'searches_logged WHERE ';
if ((db_has_full_text($GLOBALS['SITE_DB']->connection_read)) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_full_text_boolean')) && ($GLOBALS['SITE_DB']->static_ob->db_has_full_text_boolean()) && (!is_under_radar($request)))
{
$q.=preg_replace('#\?(.*)#','s_primary${1}',db_full_text_assemble($request,false));
} else
{
$q.='s_primary LIKE \''./*ideally we would put an % in front, but too slow*/db_encode_like($request.'%').'\'';
}
$q.=' AND s_primary NOT LIKE \''.db_encode_like('%<%').'\' AND '.db_string_not_equal_to('s_primary','').' GROUP BY s_primary ORDER BY cnt DESC';
$past_searches=$GLOBALS['SITE_DB']->query($q,20);
foreach ($past_searches as $search)
{
if ($search['cnt']>5)
$suggestions[$search['s_primary']]=$search['s_num_results'];
}
require_lang('search');
@ini_set('ocproducts.xss_detect','0');
// JSON format
echo '['.chr(10);
// Original request
echo '"'.php_addslashes($request).'",'.chr(10);
// Suggestions
echo '[';
foreach (array_keys($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
echo '"'.php_addslashes($suggestion).'"';
}
echo '],'.chr(10);
// Descriptions of suggestions
echo '[';
foreach (array_values($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
echo '"'.php_addslashes(do_lang('NUM_RESULTS',integer_format($suggestion))).'"';
}
echo '],'.chr(10);
// URLs to search suggestions
$filter=get_param('filter','');
$filter_map=array();
if ($filter!='')
{
foreach (explode(':',$filter) as $f)
{
if ($f!='')
{
$parts=explode('=',$f,2);
if (count($parts)==1) $parts=array($parts[0],'1');
$filter_map[$parts[0]]=$parts[1];
}
}
}
echo '[';
foreach (array_keys($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
$map=array('page'=>'search','type'=>'results','content'=>$suggestion)+$filter_map;
$_search_url=build_url($map,get_param('zone',get_module_zone('search')));
$search_url=$_search_url->evaluate();
echo '"'.php_addslashes($search_url).'"';
}
echo ']'.chr(10);
echo ']'.chr(10);
break;
// Provide details about the site search engine
default:
//header('Content-Type: application/opensearchdescription+xml');
header('Content-Type: text/xml');
$tpl=do_template('OPENSEARCH',array('DESCRIPTION'=>get_option('description')));
$tpl->evaluate_echo();
break;
}
}
?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 |
function build_search_submitter_clauses($member_field_name,$member_id,$author,$author_field_name=NULL)
{
$clauses='';
// Member ID
if ((!is_null($member_id)) && (!is_null($member_field_name)))
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=$member_field_name.'='.strval($member_id);
}
// Groups
if ((!is_null($member_field_name)) && ($author!=''))
{
$all_usergroups=$GLOBALS['FORUM_DRIVER']->get_usergroup_list(true);
foreach ($all_usergroups as $usergroup=>$usergroup_name)
{
if ($usergroup_name==$author)
{
$members_in_group=$GLOBALS['FORUM_DRIVER']->member_group_query(array($usergroup),50);
if (count($members_in_group)<50) // Let's be reasonable with how long the SQL could get!
{
foreach (array_keys($members_in_group) as $group_member_id)
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=$member_field_name.'='.strval($group_member_id);
}
}
break;
}
}
}
// Author
if ((!is_null($author_field_name)) && ($author!=''))
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=db_string_equal_to($author_field_name,$author);
}
if ($clauses=='')
{
if ($author!='') return NULL; // Query should never succeed
return '';
}
return ' AND ('.$clauses.')';
}
?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 |
function exact_match_sql($row,$i,$type='short',$param=NULL)
{
$table=' LEFT JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'catalogue_efv_'.$type.' f'.strval($i).' ON (f'.strval($i).'.ce_id=r.id AND f'.strval($i).'.cf_id='.strval($row['id']).')';
$search_field='f'.strval($i).'.cv_value';
if (is_null($param)) $param=get_param('option_'.strval($row['id']),'');
$where_clause='';
if ($param!='')
{
if ($type=='float')
{
$where_clause.=$search_field.'='.$param;
}
elseif ($type=='integer')
{
$where_clause.=$search_field.'='.$param;
} else
{
$where_clause=db_string_equal_to($search_field,$param);
}
}
return array(array(),array('f'.strval($i).'.cv_value'),$table,$search_field,$where_clause);
}
?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 |
function nl_delim_match_sql($row,$i,$type='short',$param=NULL)
{
$table=' LEFT JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'catalogue_efv_'.$type.' f'.strval($i).' ON (f'.strval($i).'.ce_id=r.id AND f'.strval($i).'.cf_id='.strval($row['id']).')';
$search_field='f'.strval($i).'.cv_value';
if (is_null($param)) $param=get_param('option_'.strval($row['id']),'');
$where_clause='';
if ($param!='') $where_clause='('.$search_field.' LIKE \''.db_encode_like($param).'\' OR '.$search_field.' LIKE \''.db_encode_like('%'.chr(10).$param).'\' OR '.$search_field.' LIKE \''.db_encode_like($param.chr(10).'%').'\' OR '.$search_field.' LIKE \''.db_encode_like('%'.chr(10).$param.chr(10).'%').'\')';
return array(array(),array('f'.strval($i).'.cv_value'),$table,$search_field,$where_clause);
}
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 |
function get_search_rows($meta_type,$meta_id_field,$content,$boolean_search,$boolean_operator,$only_search_meta,$direction,$max,$start,$only_titles,$table,$fields,$where_clause,$content_where,$order,$select='*',$raw_fields=NULL,$permissions_module=NULL,$permissions_field=NULL,$permissions_field_is_string=false)
{
if (substr($where_clause,0,5)==' AND ') $where_clause=substr($where_clause,5);
if (substr($where_clause,-5)==' AND ') $where_clause=substr($where_clause,0,strlen($where_clause)-5);
$where_alternative_matches=array();
$had_limit_imposed=false;
if ((!is_null($permissions_module)) && (!$GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())))
{
$g_or=_get_where_clause_groups(get_member());
// this destroys mysqls query optimiser by forcing complexed OR's into the join, so we'll do this in PHP code
// $table.=' LEFT JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'group_category_access z ON ('.db_string_equal_to('z.module_the_name',$permissions_module).' AND z.category_name='.$permissions_field.(($g_or!='')?(' AND '.str_replace('group_id','z.group_id',$g_or)):'').')';
// $where_clause.=' AND ';
// $where_clause.='z.category_name IS NOT NULL';
$cat_access=list_to_map('category_name',$GLOBALS['SITE_DB']->query('SELECT category_name FROM '.$GLOBALS['SITE_DB']->get_table_prefix().'group_category_access WHERE '.db_string_equal_to('module_the_name',$permissions_module).(($g_or!='')?(' AND ('.$g_or.')'):'')));
}
if (($only_titles) && (array_key_exists(0,$fields)) && ($fields[0]=='')) return array();
if (is_null($raw_fields)) $raw_fields=array();
$db=(substr($table,0,2)!='f_')?$GLOBALS['SITE_DB']:$GLOBALS['FORUM_DB'];
// This is so for example catalogue_entries.php can use brackets in it's table specifier whilst avoiding the table prefix after the first bracket. A bit weird, but that's our convention and it does save a small amount of typing
$table_clause=$db->get_table_prefix().(($table[0]=='(')?(substr($table,1)):$table);
if ($table[0]=='(') $table_clause='('.$table_clause;
$t_rows=array();
$t_count=0;
// Rating ordering, via special encoding
if (substr($order,0,7)=='_rating')
{
list(,$rating_type,$meta_rating_id_field)=explode(':',$order);
$select.=',(SELECT AVG(rating) FROM '.get_table_prefix().'rating WHERE '.db_string_equal_to('rating_for_type',$rating_type).' AND rating_for_id='.$meta_rating_id_field.') AS compound_rating';
$order='compound_rating';
}
// Defined-keywords/tags search
if ((get_param_integer('keep_just_show_query',0)==0) && (!is_null($meta_type)) && ($content!=''))
{
$keywords_where=preg_replace('#\?#','tm.text_original',build_content_where($content,$boolean_search,$boolean_operator,true));
if ($keywords_where!='')
{
if ($meta_id_field=='the_zone:the_page') // Special case
{
$meta_join='m.meta_for_id=CONCAT(r.the_zone,\':\',r.the_page)';
} else
{
$meta_join='m.meta_for_id=r.'.$meta_id_field;
}
$extra_join='';
foreach ($fields as $i=>$field) // Translatable fields present in 'select'
{
if (($field=='') || ($field=='!') || (strpos($select,'t1.text_original')===false)) continue;
$extra_join.=' LEFT JOIN '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
}
if (!db_has_subqueries($db->connection_read) || true /* Forced this old code to run because the "optimisation" does not work for larger result sets */)
{
$_keywords_query=$table_clause.' LEFT JOIN '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') LEFT JOIN '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).$extra_join;
$_keywords_query.=' WHERE '.$keywords_where;
$_keywords_query.=(($where_clause!='')?(' AND '.$where_clause):'');
} else
{
$_keywords_query=/*str_replace(' LEFT JOIN ',' JOIN ',*/$table_clause/*)*/.' LEFT JOIN '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') LEFT JOIN '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).$extra_join;
$_keywords_query.=' WHERE '.$keywords_where;
$_keywords_query.=(($where_clause!='')?(' AND tm.id IN (SELECT m.id FROM '.$table_clause.' LEFT JOIN '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') LEFT JOIN '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).' WHERE '.$where_clause.' AND '.$keywords_where.')'):'');
}
$keywords_query='SELECT '.$select.' FROM '.$_keywords_query;
$_count_query_keywords_search='SELECT COUNT(*) FROM '.$_keywords_query;
$group_by_ok=(can_arbitrary_groupby() && $meta_id_field==='id');
$keywords_query.=($group_by_ok?' GROUP BY r.id':'');
if ($order!='')
{
$keywords_query.=' ORDER BY '.$order;
if ($direction=='DESC') $keywords_query.=' DESC';
}
if ($group_by_ok)
{
$_count_query_keywords_search=str_replace('COUNT(*)','COUNT(DISTINCT r.id)',$_count_query_keywords_search);
}
$t_keyword_search_rows_count=$db->query_value_null_ok_full($_count_query_keywords_search);
if ($t_keyword_search_rows_count>500) // Too much to sort in memory, so we will just put up with overlapping content types that aren't sorted together right
{
$t_keyword_search_rows=$db->query($keywords_query,$max+$start);
$had_limit_imposed=true;
} else
{
$t_keyword_search_rows=$db->query($keywords_query);
}
$t_count+=$t_keyword_search_rows_count;
$t_rows=array_merge($t_rows,$t_keyword_search_rows);
} else $_count_query_keywords_search=NULL;
} else $_count_query_keywords_search=NULL;
$orig_table_clause=$table_clause;
// Main content search
if (!$only_search_meta)
{
if (($content_where!='') || (preg_match('#t\d+\.text_original#',$where_clause)!=0) || (preg_match('#t\d+\.text_original#',$select)!=0))
{
// Each of the fields represents an 'OR' match, so we put it together into a list ($where_alternative_matches) of specifiers for each. Hopefully we will 'UNION' them rather than 'OR' them as it is much more efficient in terms of table index usage
$where_alternative_matches=array();
foreach ($fields as $i=>$field) // Referenced fields in where condition must result in the shared table clause having a reference to the translate for that
{
if ((strpos($select,'t'.strval($i).'.text_original')!==false) || (strpos($where_clause,'t'.strval($i).'.text_original')!==false))
{
$tc_add=' LEFT JOIN '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
$orig_table_clause.=$tc_add;
}
}
foreach ($fields as $i=>$field) // Translatable fields
{
if (($field=='') || ($field=='!')) continue;
if ($field==$order) $order='t'.$i.'.text_original'; // Ah, remap to the textual equivalent then
$tc_add=' LEFT JOIN '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
if (strpos($orig_table_clause,$tc_add)!==false) $tc_add='';
if (($only_titles) && ($i!=0)) break;
$where_clause_2=preg_replace('#\?#','t'.strval($i).'.text_original',$content_where);
$where_clause_3=$where_clause;
if (($table=='f_members') && (substr($field,0,6)=='field_') && (db_has_subqueries($db->connection_read)))
$where_clause_3.=(($where_clause=='')?'':' AND ').'NOT EXISTS (SELECT * FROM '.$db->get_table_prefix().'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id='.substr($field,6).' AND cpfp.guest_view=0)';
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$_select=preg_replace('#\?#','t'.strval($i).'.text_original',$content_where).' AS contextual_relevance';
} else
{
$_select='';
}
$_table_clause=$orig_table_clause.$tc_add;
$where_alternative_matches[]=array($where_clause_2,$where_clause_3,$_select,$_table_clause,'t'.strval($i));
}
if ($content_where!='') // Non-translatable fields
{
foreach ($raw_fields as $i=>$field)
{
if (($only_titles) && ($i!=0)) break;
$where_clause_2=preg_replace('#\?#',$field,$content_where);
$where_clause_3=$where_clause;
if (($table=='f_members') && (substr($field,0,6)=='field_') && (db_has_subqueries($db->connection_read)))
$where_clause_3.=(($where_clause=='')?'':' AND ').'NOT EXISTS (SELECT * FROM '.$db->get_table_prefix().'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id='.substr($field,6).' AND cpfp.guest_view=0)';
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$_select=preg_replace('#\?#',$field,$content_where).' AS contextual_relevance';
} else
{
$_select='';
}
$_table_clause=$orig_table_clause;
$where_alternative_matches[]=array($where_clause_2,$where_clause_3,$_select,$_table_clause,NULL);
}
}
}
if (count($where_alternative_matches)==0)
{
$where_alternative_matches[]=array($where_clause,'','',$table_clause,NULL);
} else
{
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$order='contextual_relevance DESC';
}
}
// Work out main query
global $SITE_INFO;
if (((isset($SITE_INFO['mysql_old'])) && ($SITE_INFO['mysql_old']=='1')) || ((!isset($SITE_INFO['mysql_old'])) && (is_file(get_file_base().'/mysql_old'))))
{
$_query='';
foreach ($where_alternative_matches as $parts)
{
list($where_clause_2,$where_clause_3,$_select,,)=$parts;
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$select.=(($_select=='')?'':',').$_select;
$_query.=(($where_clause_3!='')?((($_query=='')?' WHERE ':' OR ').$where_clause_3):'');
}
$query='SELECT '.$select.' FROM '.$table_clause.$_query;
} else
{
$query='';
foreach ($where_alternative_matches as $parts) // We UNION them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
{
list($where_clause_2,$where_clause_3,$_select,$_table_clause,$tid)=$parts;
if ($query!='') $query.=' UNION ';
if ((!db_has_subqueries($db->connection_read)) || (is_null($tid)) || ($content_where=='') || true)
{
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$query.='SELECT '.$select.(($_select=='')?'':',').$_select.' FROM '.$_table_clause.(($where_clause_3=='')?'':' WHERE '.$where_clause_3);
} else // Optimised using subqueries. We need the fulltext search to run first to avoid non-bounded joins over potentially huge tables
{
$query.='SELECT '.$select.(($_select=='')?'':',').$_select.' FROM './*str_replace(' LEFT JOIN ',' JOIN ',*/$_table_clause/*)*/;
if (($where_clause_2!='') || ($where_clause_3!=''))
{
$query.=' WHERE '.$where_clause_2;
$query.=(($where_clause_3!='')?((($where_clause_2=='')?'':' AND ').$tid.'.id IN (SELECT '.$tid.'.id FROM '.$_table_clause.' WHERE '.$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3)).')'):'');
}
}
}
}
// Work out COUNT(*) query using one of a few possible methods. It's not efficient and stops us doing proper merge-sorting between content types (and possible not accurate - if we use an efficient but non-deduping COUNT strategy) if we have to use this, so we only do it if there are too many rows to fetch in one go.
$_query='';
if (((isset($SITE_INFO['mysql_old'])) && ($SITE_INFO['mysql_old']=='1')) || ((!isset($SITE_INFO['mysql_old'])) && (is_file(get_file_base().'/mysql_old'))) || (strpos(get_db_type(),'mysql')===false))
{
foreach ($where_alternative_matches as $parts)
{
list($where_clause_2,$where_clause_3,,$_table_clause,$tid)=$parts;
if ((!db_has_subqueries($db->connection_read)) || (is_null($tid)) || ($content_where=='') || true)
{
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$_query.=(($where_clause_3!='')?((($_query=='')?' WHERE ':' OR ').$where_clause_3):'');
} else
{
if (($where_clause_2!='') || ($where_clause_3!=''))
{
$_query.=(($_query=='')?' WHERE ':' OR ').$where_clause_2;
$_query.=(($where_clause_3!='')?((($where_clause_2=='')?'':' AND ').$tid.'.id IN (SELECT '.$tid.'.id FROM '.$_table_clause.' WHERE '.$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3)).')'):'');
}
}
}
$_count_query_main_search='SELECT COUNT(*) FROM '.$table_clause.$_query;
} else // This is inaccurate (does ot filter dupes from each +'d query) but much more efficient on MySQL
{
foreach ($where_alternative_matches as $parts) // We "+" them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
{
list($where_clause_2,$where_clause_3,$_select,$_table_clause,$tid)=$parts;
if ($_query!='') $_query.='+';
if ((!db_has_subqueries($db->connection_read)) || (is_null($tid)) || ($content_where=='') || true)
{
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$_query.='(SELECT COUNT(*) FROM '.$_table_clause.(($where_clause_3=='')?'':' WHERE '.$where_clause_3).')';
} else // Optimised using subqueries. We need the fulltext search to run first to avoid non-bounded joins over potentially huge tables
{
$_query.='(SELECT COUNT(*) FROM './*str_replace(' LEFT JOIN ',' JOIN ',*/$_table_clause/*)*/;
if (($where_clause_2!='') || ($where_clause_3!=''))
{
$_query.=' WHERE '.$where_clause_2;
$_query.=(($where_clause_3!='')?((($where_clause_2=='')?'':' AND ').$tid.'.id IN (SELECT '.$tid.'.id FROM '.$_table_clause.' WHERE '.$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3)).')'):'');
}
$_query.=')';
}
}
$_count_query_main_search='SELECT ('.$_query.')';
}
$group_by_ok=(can_arbitrary_groupby() && $meta_id_field==='id');
$query.=($group_by_ok?' GROUP BY r.id':'');
if ($order!='')
{
$query.=' ORDER BY '.$order;
if (($direction=='DESC') && (substr($order,-4)!=' ASC') && (substr($order,-5)!=' DESC')) $query.=' DESC';
}
if (get_param_integer('keep_show_query',0)==1)
{
attach_message($query,'inform');
}
if (get_param_integer('keep_just_show_query',0)==1)
{
@ini_set('ocproducts.xss_detect','0');
header('Content-type: text/plain; charset='.get_charset());
exit($query);
}
if ($group_by_ok)
{
$_count_query_main_search=str_replace('COUNT(*)','COUNT(DISTINCT r.id)',$_count_query_main_search);
}
$t_main_search_rows_count=$db->query_value_null_ok_full($_count_query_main_search);
if ($t_main_search_rows_count>500) // Too much to sort in memory, so we will just put up with overlapping content types that aren't sorted together right
{
$t_main_search_rows=$db->query($query,$max+$start,NULL,false,true);
$had_limit_imposed=true;
} else
{
$t_main_search_rows=$db->query($query,NULL,NULL,false,true);
}
$t_count+=$t_main_search_rows_count;
$t_rows=array_merge($t_rows,$t_main_search_rows);
} else
{
$t_main_search_rows=array();
}
// Clean results and return
// NB: We don't use the count_query's any more (except when using huge data sets, see above), because you can't actually just add them because they overlap. So instead we fetch all results and throw some away.
$t_rows=array_merge($t_rows,$t_main_search_rows);
if (count($t_rows)>0)
{
$t_rows_new=array();
if ((array_key_exists('id',$t_rows[0])) || (array_key_exists('_primary_id',$t_rows[0])))
{
$done=array();
foreach ($t_rows as $t_row)
{
if (array_key_exists('id',$t_row))
{
if (array_key_exists($t_row['id'],$done)) continue;
$done[$t_row['id']]=1;
}
elseif (array_key_exists('_primary_id',$t_row))
{
if (array_key_exists($t_row['_primary_id'],$done)) continue;
$done[$t_row['_primary_id']]=1;
}
$t_rows_new[]=$t_row;
}
} else
{
foreach ($t_rows as $t_row)
{
unset($t_row['contextual_relevance']);
foreach ($t_rows_new as $_t_row)
{
if (($_t_row==$t_row) || ((array_key_exists('id',$t_row)) && (array_key_exists('id',$_t_row)) && (!array_key_exists('_primary_id',$t_row)) && (!array_key_exists('_primary_id',$_t_row)) && ($t_row['id']==$_t_row['id'])) || ((array_key_exists('_primary_id',$t_row)) && (array_key_exists('_primary_id',$_t_row)) && ($t_row['_primary_id']==$_t_row['_primary_id'])))
continue 2;
}
$t_rows_new[]=$t_row;
}
}
$t_rows=$t_rows_new;
}
if (get_param_integer('keep_show_query',0)==1)
{
if ((array_key_exists(0,$t_rows)) && (array_key_exists('id',$t_rows[0])))
{
$results=var_export(array_unique(collapse_1d_complexity('id',$t_rows)),true);
} else
{
$results=var_export($t_rows,true);
}
attach_message(do_lang('_RESULTS').': '.$results,'inform');
}
if (isset($cat_access))
{
$before=count($t_rows);
foreach ($t_rows as $i=>$row)
{
if (!array_key_exists(strval($row[$permissions_field]),$cat_access)) unset($t_rows[$i]);
}
}
$final_result_rows=$t_rows;
if (!$had_limit_imposed) // More accurate, as filtered for dupes
$t_count=count($t_rows);
$GLOBALS['TOTAL_RESULTS']+=$t_count;
array_splice($final_result_rows,$max*2+$start); // We return more than max in case our search hook does some extra in-code filtering (Catalogues, Comcode pages). It shouldn't really but sometimes it has to, and it certainly shouldn't filter more than 50%. Also so our overall ordering can be better.
return $final_result_rows;
}
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 |
function _boolean_search_prepare($search_filter)
{
$search_filter=str_replace('?','_',$search_filter);
$search_filter=str_replace('*','%',$search_filter);
$content_explode=explode(' ',$search_filter);
$body_words=array();
$include_words=array();
$disclude_words=array();
for ($i=0;$i<count($content_explode);$i++)
{
$word=trim($content_explode[$i]);
if (($word=='') || ($word=='+') || ($word=='-')) continue;
// Handle quotes
if ($word[0]=='"')
{
$i++;
while ($i<count($content_explode))
{
$word2=trim($content_explode[$i]);
if ($word2!='')
{
$word.=' '.$word2;
}
if (substr($word2,-1)=='"')
{
break;
} else
{
$i++;
}
}
if (substr($word,-1)!='"') $word.='"';
$word=substr($word,1,strlen($word)-2);
}
if ($word[0]=='+') $include_words[]=$word;
elseif ($word[0]=='-') $disclude_words[]=$word;
else $body_words[]=$word;
}
return array($body_words,$include_words,$disclude_words);
}
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 |
function in_memory_search_match($filter,$title,$post=NULL)
{
if ((!array_key_exists('content',$filter)) || ($filter['content']=='')) return true;
$search_filter=$filter['content'];
if (((array_key_exists('only_titles',$filter)) && ($filter['only_titles']==1)) || (is_null($post)))
{
$context=$title;
} else
{
$context=$title.' '.$post;
}
$boolean_operator=array_key_exists('conjunctive_operator',$filter)?$filter['conjunctive_operator']:'OR';
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($search_filter);
foreach ($include_words as $word)
{
if (!simulated_wildcard_match($context,$word)) return false;
}
foreach ($disclude_words as $word)
{
if (simulated_wildcard_match($context,$word)) return false;
}
if ($boolean_operator=='OR')
{
$count=0;
foreach ($body_words as $word)
{
if (simulated_wildcard_match($context,$word)) $count++;
}
if ($count==0) return false;
} else
{
foreach ($body_words as $word)
{
if (!simulated_wildcard_match($context,$word)) return false;
}
}
return true;
}
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 |
function is_under_radar($test)
{
if (get_value('disable_boolean_search')==='1') return false;
static $min_word_length=NULL;
if (is_null($min_word_length))
{
$min_word_length=4;
if (substr(get_db_type(),0,5)=='mysql')
{
$_min_word_length=$GLOBALS['SITE_DB']->query('SHOW VARIABLES LIKE \'ft_min_word_len\'',NULL,NULL);
if (array_key_exists(0,$_min_word_length))
$min_word_length=intval($_min_word_length[0]['Value']);
}
}
return ((strlen($test)<$min_word_length) && ($test!=''));
}
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 |
function build_content_where($content,$boolean_search,&$boolean_operator,$full_coverage=false)
{
$content=str_replace('?','',$content);
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($content);
$under_radar=false;
if ((is_under_radar($content)) && ($content!='')) $under_radar=true;
if (($under_radar) || ($boolean_search) || (!db_has_full_text($GLOBALS['SITE_DB']->connection_read)))
{
if (!in_array(strtoupper($boolean_operator),array('AND','OR'))) log_hack_attack_and_exit('ORDERBY_HACK');
if ($content=='')
{
$content_where='';
} else
{
if ((db_has_full_text($GLOBALS['SITE_DB']->connection_read)) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_full_text_boolean')) && ($GLOBALS['SITE_DB']->static_ob->db_has_full_text_boolean()) && (!$under_radar))
{
$content_where=db_full_text_assemble($content,true);
} else
{
$content_where=db_like_assemble($content,$boolean_operator,$full_coverage);
if ($content_where=='') $content_where='1=1';
}
}
} else
{
if ($content=='')
{
$content_where='';
} else
{
$content_where=db_full_text_assemble($content,false);
}
$boolean_operator='OR';
}
return $content_where;
}
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 |
function db_like_assemble($content,$boolean_operator='AND',$full_coverage=false)
{
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($content);
$fc_before=$full_coverage?'':'%';
$fc_after=$full_coverage?'':'%';
$body_where='';
foreach ($body_words as $word)
{
if ($body_where!='') $body_where.=' '.$boolean_operator.' ';
if ((strtoupper($word)==$word) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word)))
{
$body_where.='CONVERT(? USING latin1) LIKE _latin1\''.db_encode_like($fc_before.$word.$fc_after).'\' COLLATE latin1_general_cs';
} else
{
$body_where.='? LIKE \''.db_encode_like($fc_before.$word.$fc_after).'\'';
}
}
$include_where='';
foreach ($include_words as $word)
{
if ($include_where!='') $include_where.=' AND ';
if ((strtoupper($word)==$word) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word)))
{
$include_where.='CONVERT(? USING latin1) LIKE _latin1\''.db_encode_like($fc_before.$word.$fc_after).'\' COLLATE latin1_general_cs';
} else
{
$include_where.='? LIKE \''.db_encode_like($fc_before.$word.$fc_after).'\'';
}
}
$disclude_where='';
foreach ($disclude_words as $word)
{
if ($disclude_where!='') $disclude_where.=' AND ';
if ((strtoupper($word)==$word) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word)))
{
$disclude_where.='CONVERT(? USING latin1) NOT LIKE _latin1\''.db_encode_like($fc_before.$word.$fc_after).'\' COLLATE latin1_general_cs';
} else
{
$disclude_where.='? NOT LIKE \''.db_encode_like($fc_before.$word.$fc_after).'\'';
}
}
$content_where='';
if ($body_where!='') $content_where.='('.$body_where.')';
if ($include_where!='')
{
if ($content_where!='') $content_where.=' AND ';
$content_where.='('.$include_where.')';
}
if ($disclude_where!='')
{
if ($content_where!='') $content_where.=' AND ';
$content_where.='('.$disclude_where.')';
}
return $content_where;
}
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 |
function sort_search_results($hook_results,$results,$direction)
{
// Do a merge sort
$results_position=0;
$done_all=false;
foreach ($hook_results as $i=>$result)
{
while (true)
{
if (!array_key_exists($results_position,$results)) // If we've run off the end of our current results
{
$results=array_merge($results,array_slice($hook_results,$i));
$done_all=true;
break;
}
if ((array_key_exists('orderer',$result)) && (array_key_exists('orderer',$results[$results_position])) && ((($direction=='ASC') && ($result['orderer']<=$results[$results_position]['orderer'])) || (($direction=='DESC') && ($result['orderer']>=$results[$results_position]['orderer'])))) // If it definitely beats, put in front. If it's unknown (no orderer on one - which is very common) it has to go on the end so FIFO is preserved
{
$results=array_merge(array_slice($results,0,$results_position),array($result),array_slice($results,$results_position));
break;
}
$results_position++;
}
if ($done_all)
{
break;
}
}
return $results;
}
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 |
function build_search_results_interface($results,$start,$max,$direction,$general_search=false)
{
$out=new ocp_tempcode();
$i=0;
global $CATALOGUE_ENTRIES_BUILDUP;
$CATALOGUE_ENTRIES_BUILDUP=array();
$tabular_results=array();
foreach ($results as $result)
{
if (array_key_exists('restricted',$result)) continue; // This has been blanked out due to insufficient access permissions or some other reason
if ($i>=$start+$max) break;
if ($i>=$start)
{
if (array_key_exists('template',$result))
{
$rendered_result=$result['template'];
} else
{
if ((!$general_search) && (get_param_integer('force_non_tabular',0)==0) && (method_exists($result['object'],'render_tabular')))
{
$rendered_result=$result['object']->render_tabular($result['data']);
} else
{
$rendered_result=$result['object']->render($result['data']);
}
}
if (!is_null($rendered_result))
{
if (is_array($rendered_result))
{
$class=get_class($result['object']);
if (!array_key_exists($class,$tabular_results)) $tabular_results[$class]=array();
$tabular_results[$class][]=$rendered_result;
} else
{
$out->attach(do_template('SEARCH_RESULT',array('_GUID'=>'47da093f9ace87819e246f0cec1402a9','CONTENT'=>$rendered_result)));
}
}
}
$i++;
}
foreach ($tabular_results as $tabular_type=>$types_results)
{
// Normalisation process
$ultimate_field_map=array();
foreach ($types_results as $r)
$ultimate_field_map+=$r;
//ksort($ultimate_field_map);
$ultimate_field_map=array_keys($ultimate_field_map);
foreach ($types_results as $i=>$r)
{
$r2d2=array();
foreach ($ultimate_field_map as $key)
{
if (!array_key_exists($key,$r)) $r[$key]='';
$r2d2[$key]=$r[$key];
}
//ksort($r);
$r=$r2d2;
$types_results[$i]=array('R'=>$r);
}
// Output
$out->attach(do_template('SEARCH_RESULT_TABLE',array('HEADERS'=>$ultimate_field_map,'ROWS'=>$types_results)));
}
if (count($CATALOGUE_ENTRIES_BUILDUP)!=0)
{
global $SEARCH_CATALOGUE_ENTRIES_CATALOGUES;
foreach ($CATALOGUE_ENTRIES_BUILDUP as $catalogue_name=>$catalogue_entries)
{
$tpl_set=$catalogue_name;
$buildup=get_catalogue_category_entry_buildup(NULL,$catalogue_name,$SEARCH_CATALOGUE_ENTRIES_CATALOGUES[$catalogue_name],'SEARCH',$tpl_set,NULL,NULL,NULL,NULL,NULL,false,$catalogue_entries);
$out->attach(do_template('SEARCH_RESULT_CATALOGUE_ENTRIES',array('_GUID'=>'dd0045ac291275b2d822d40b28182a28','BUILDUP'=>$buildup[0],'NAME'=>$catalogue_name,'TITLE'=>get_translated_text($SEARCH_CATALOGUE_ENTRIES_CATALOGUES[$catalogue_name]['c_title']))));
}
}
$GLOBALS['META_DATA']+=array(
'opensearch_totalresults'=>strval($i),
'opensearch_startindex'=>strval($start),
'opensearch_itemsperpage'=>strval($max),
);
$SEARCH__CONTENT_BITS=NULL;
return $out;
}
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.
- Create a new PHP file
- 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.
0 reviews: Unrated (average)
There have been no comments yet