14. Database Access |
Module Sql |
The SQL module is a unified interface between pike and all its supported databases. The parts of this module that is usuable for all normal uses is the Sql class and the sql_result class.
string people_in_group(string group) { Sql.Sql db = Sql.Sql("mysql://localhost/testdb"); return db->query("SELECT name FROM users WHERE " "group=%s", group)->name * ","; }
CLASS Sql.msql |
Implements the glue needed to access the Msql-module from the generic SQL module.
inherit Msql.msql : msql
CLASS Sql.postgres |
This is an interface to the Postgres (Postgres95, pgsql) database server. This module may or may not be availible on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.
Also note that this module uses blocking I/O I/O to connect to the server. Postgres is quite slow, and so you might want to consider this particular aspect. It is (at least should be) thread-safe, and so it can be used in a multithread environment.
The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the Pike interpreter.
|
Refer to the Postgres documentation for further details.
Sql.Sql , Postgres.postgres , Sql.postgres_result
inherit Postgres.postgres : mo
void select_db(string dbname)
This function allows you to connect to a database. Due to restrictions of the Postgres frontend-backend protocol, you always have to be connected to a database, so in fact this function just allows you to connect to a different database on the same server.
This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)
create
string error()
This function returns the textual description of the last
server-related error. Returns 0
if no error has occurred
yet. It is not cleared upon reading (can be invoked multiple
times, will return the same result until a new error occurs).
big_query
string host_info()
This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).
void reset()
This function resets the connection to the backend. Can be used for a variety of reasons, for example to detect the status of a connection.
This function is Postgres-specific, and thus it is not availible through the generic SQL-interface.
string version
Should you need to report a bug to the author, please submit along with the report the driver version number, as returned by this call.
void Sql.postgres()
void Sql.postgres(string host, void|string database, void|string user, void|string password)
With no arguments, this function initializes (reinitializes if a connection had been previously set up) a connection to the Postgres backend. Since Postgres requires a database to be selected, it will try to connect to the default database. The connection may fail however for a variety of reasons, in this case the most likely of all is because you don't have enough authority to connect to that database. So use of this particular syntax is discouraged.
The host argument can have the syntax "hostname"
or
"hostname:portname"
. This allows to specify the TCP/IP
port to connect to. If it is 0
or ""
, it will try
to connect to localhost, default port.
The database argument specifies the database to connect to. If
0
or ""
, it will try to connect to the specified
database.
The username and password arguments are silently ignored, since the Postgres C API doesn't allow to connect to the server as any user different than the user running the interface.
You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.
You don't need bothering about syncronizing the connection to the database: it is automatically closed (and the database is sync-ed) when the object is destroyed.
Postgres.postgres , Sql.Sql , postgres->select_db
void set_notify_callback()
void set_notify_callback(function f)
void set_notify_callback(function f, int|float poll_delay)
With Postgres you can associate events and notifications to tables. This function allows you to detect and handle such events.
With no arguments, resets and removes any callback you might have put previously, and any polling cycle.
With one argument, sets the notification callback (there can be only one for each sqlobject).
With two arguments, sets a notification callback and sets a polling cycle.
The polling cycle is necessary because of the way notifications are delivered, that is piggyback with a query result. This means that if you don't do any query, you'll receive no notification. The polling cycle starts a call_out cycle which will do an empty query when the specified interval expires, so that pending notifications may be delivered.
The callback function must return no value, and takes a string argument, which will be the name of the table on which the notification event has occured. In future versions, support for user-specified arguments will be added.
The polling cycle can be run only if your process is in "event-driven mode" (that is, if 'main' has returned a negative number).
This function is Postgres-specific, and thus it is not availible through the generic SQL-interface.
An integer can be passed as first argument, but it's effect is not documented.
void create_db(string db)
This function creates a new database with the given name (assuming we have enough permissions to do this).
drop_db
void drop_db(string db)
This function destroys a database and all the data it contains (assuming we have enough permissions to do so).
create_db
string server_info()
This function returns a string describing the server we are
talking to. It has the form "servername/serverversion"
(like the HTTP protocol description) and is most useful in
conjunction with the generic SQL-server module.
array(string) list_dbs(void|string glob)
Lists all the databases available on the server. If glob is specified, lists only those databases matching it.
array(string) list_tables(void|string glob)
Returns an array containing the names of all the tables in the currently selected database. If a glob is specified, it will return only those tables whose name matches it.
array(mapping(string:mixed)) list_fields(string table, void|string wild)
Returns a mapping, indexed on the column name, of mappings describing the attributes of a table of the current database. If a glob is specified, will return descriptions only of the columns matching it.
The currently defined fields are:
|
int|object big_query(object|string q, mapping(string|int:mixed)|void bindings)
This is the only provided interface which allows you to query the database. If you wish to use the simpler "query" function, you need to use the Sql.Sql generic SQL-object.
It returns a postgres_result object (which conforms to the Sql.sql_result standard interface for accessing data). I recommend using query() for simpler queries (because it is easier to handle, but stores all the result in memory), and big_query() for queries you expect to return huge amounts of data (it's harder to handle, but fectches results on demand).
This function can raise exceptions.
Sql.Sql , Sql.sql_result
CLASS Sql.mysql |
Implements the glue needed to access the Mysql-module from the generic SQL module.
inherit Mysql.mysql : mysql
int(0..1) set_unicode_encode_mode(int enable)
Enables or disables unicode encode mode.
In this mode, if the server supports UTF-8 and the connection
charset is latin1
(the default) or unicode
then
big_query handles wide unicode queries. Enabled by default.
Unicode encode mode works as follows: Eight bit strings are sent
as latin1
and wide strings are sent using utf8
.
big_query sends SET character_set_client
statements as
necessary to update the charset on the server side. If the server
doesn't support that then it fails, but the wide string query
would fail anyway.
To make this transparent, string literals with introducers (e.g.
_binary 'foo'
) are excluded from the UTF-8 encoding. This
means that big_query needs to do some superficial parsing of
the query when it is a wide string.
|
Note that this mode doesn't affect the MySQL system variable
character_set_connection
, i.e. it will still be set to
latin1
by default which means server functions like
UPPER()
won't handle non-latin1
characters
correctly in all cases.
To fix that, do set_charset ("unicode")
. That will
allow unicode encode mode to work while utf8
is fully
enabled at the server side.
Tip: If you enable utf8
on the server side, you need to
send raw binary strings as _binary'...'
. Otherwise they
will get UTF-8 encoded by the server.
When unicode encode mode is enabled and the connection charset
is latin1
, the charset accepted by big_query is not
quite Unicode since latin1
is based on cp1252
.
The differences are in the range 0x80..0x9f
where
Unicode have control chars.
This small discrepancy is not present when the connection
charset is unicode
.
set_unicode_decode_mode , set_charset
int get_unicode_encode_mode()
Returns nonzero if unicode encode mode is enabled, zero otherwise.
set_unicode_encode_mode
void set_unicode_decode_mode(int enable)
Enable or disable unicode decode mode.
In this mode, if the server supports UTF-8 then non-binary text strings in results are are automatically decoded to (possibly wide) unicode strings. Not enabled by default.
The statement "SET character_set_results = utf8
" is sent
to the server to enable the mode. When the mode is disabled,
"SET character_set_results = xxx
" is sent, where
xxx
is the connection charset that get_charset returns.
Nonzero enables this feature, zero disables it.
Throws an exception if the server doesn't support this, i.e. if
the statement above fails. The MySQL system variable
character_set_results
was added in MySQL 4.1.1.
This mode is not compatible with earlier pike versions. You need to run in compatibility mode <= 7.6 to have it disabled by default.
set_unicode_encode_mode
int get_unicode_decode_mode()
Returns nonzero if unicode decode mode is enabled, zero otherwise.
set_unicode_decode_mode
void set_charset(string charset)
Changes the connection charset. Works similar to sending the query
SET NAMES charset
but also records the charset on the
client side so that various client functions work correctly.
charset is a MySQL charset name or the special value
"unicode"
(see below). You can use SHOW CHARACTER
SET
to get a list of valid charsets.
Specifying "unicode"
as charset is the same as
"utf8"
except that unicode encode and decode modes are
enabled too. Briefly, this means that you can send queries as
unencoded unicode strings and will get back non-binary text
results as unencoded unicode strings. See
set_unicode_encode_mode and set_unicode_decode_mode for
further details.
Throws an exception if the server doesn't support this, i.e. if
the statement SET NAMES
fails. Support for it was added
in MySQL 4.1.0.
If charset is "latin1"
and unicode encode mode is
enabled (the default) then big_query can send wide unicode
queries transparently if the server supports UTF-8. See
set_unicode_encode_mode .
If unicode decode mode is already enabled (see
set_unicode_decode_mode ) then this function won't affect the
result charset (i.e. the MySQL system variable
character_set_results
).
Actually, a query SET character_set_results = utf8
will
be sent immediately after setting the charset as above if
unicode decode mode is enabled and charset isn't
"utf8"
.
You should always use either this function or the
"mysql_charset_name"
option to create to set the
connection charset, or more specifically the charset that the
server expects queries to have (i.e. the MySQL system variable
character_set_client
). Otherwise big_query might not
work correctly.
Afterwards you may change the system variable
character_set_connection
, and also
character_set_results
if unicode decode mode isn't
enabled.
The MySQL latin1
charset is close to Windows
cp1252
. The difference from ISO-8859-1 is a bunch of
printable chars in the range 0x80..0x9f
(which contains
control chars in ISO-8859-1). For instance, the euro currency
sign is 0x80
.
You can use the mysql-latin1
encoding in the
Locale.Charset module to do conversions, or just use the
special "unicode"
charset instead.
get_charset , set_unicode_encode_mode , set_unicode_decode_mode
string get_charset()
Returns the MySQL name for the current connection charset.
Returns "unicode"
if unicode encode mode is enabled and
UTF-8 is used on the server side (i.e. in
character_set_connection
).
In servers with full charset support (i.e. MySQL 4.1.0 or
later), this corresponds to the MySQL system variable
character_set_client
(with one exception - see next
note) and thus controls the charset in which queries are sent.
The charset used for text strings in results might be something
else (and typically is if unicode decode mode is enabled; see
set_unicode_decode_mode ).
If the returned charset is latin1
or unicode
and
unicode encode mode is enabled (the default) then
character_set_client
in the server might be either
latin1
or utf8
, depending on the last sent
query. See set_unicode_encode_mode for more info.
set_charset
string quote(string s)
Quote a string so that it can safely be put in a query.
String to quote.
string latin1_to_utf8(string s)
Converts a string in MySQL latin1
format to UTF-8.
string utf8_encode_query(string q, function(string:string) encode_fn)
Encodes the appropriate sections of the query with encode_fn .
Everything except strings prefixed by an introducer (i.e.
_something
or N
) is encoded.
string encode_time(int time, void|int date)
Converts a system time value to an appropriately formatted time spec for the database.
Time to encode.
If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.
string encode_date(int time)
Converts a system time value to an appropriately formatted date-only spec for the database.
Time to encode.
string encode_datetime(int time)
Converts a system time value to an appropriately formatted date and time spec for the database.
Time to encode.
int decode_time(string timestr, void|int date)
Converts a database time spec to a system time value.
Time spec to decode.
Take the date part from this system time value. If zero, a seconds-since-midnight value is returned.
int decode_date(string datestr)
Converts a database date-only spec to a system time value. Assumes 4-digit years.
Date spec to decode.
int decode_datetime(string timestr)
Converts a database date and time spec to a system time value. Can decode strings missing the time part.
Date and time spec to decode.
int(0..1) is_keyword(string name)
Return 1 if the argument name is a mysql keyword.
CLASS Sql.mysqls |
Implements SQL-urls for mysqls://[user[:password]@][hostname][:port][/database]
Sets the connection to SSL-mode, and sets the default configuration
file to "/etc/my.cnf"
.
Ought to load a suitable default configuration file for Win32 too.
This connection method only exists if the Mysql-module has been compiled with SSL-support.
inherit Sql.mysql : mysql
CLASS Sql.postgres_result |
Sql.postgres_result contains the result of a Postgres-query. See Sql.postgres for a description of this program's functions.
inherit Postgres.postgres_result : postgres_result
CLASS Sql.Sql |
Implements those functions that need not be present in all SQL-modules.
object master_sql
Object to use for the actual SQL-queries.
int(0..1) case_convert
Convert all field names in mappings to lower_case. Only relevant to databases which only implement big_query(), and use upper/mixed-case fieldnames (eg Oracle).
|
string quote(string s)
Quote a string s so that it can safely be put in a query.
All input that is used in SQL-querys should be quoted to prevent SQL injections.
Consider this harmfull code:
string my_input = "rob' OR name!='rob";
string my_query = "DELETE FROM tblUsers WHERE name='"+my_input+"'";
my_db->query(my_query);
This type of problems can be avoided by quoting my_input. my_input would then probably read something like rob\' OR name!=\'rob
Usually this is done - not by calling quote explicitly - but through using a sprintf like syntax
string my_input = "rob' OR name!='rob";
my_db->query("DELETE FROM tblUsers WHERE name=%s",my_input);
string encode_time(int t, int|void is_utc)
Converts a system time value to an appropriately formatted time spec for the database.
Time to encode.
If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.
int decode_time(string t, int|void want_utc)
Converts a database time spec to a system time value.
Time spec to decode.
Take the date part from this system time value. If zero, a seconds-since-midnight value is returned.
string encode_date(int t)
Converts a system time value to an appropriately formatted date-only spec for the database.
Time to encode.
int decode_date(string d)
Converts a database date-only spec to a system time value.
Date spec to decode.
string encode_datetime(int t)
Converts a system time value to an appropriately formatted date and time spec for the database.
Time to encode.
int decode_datetime(string datetime)
Converts a database date and time spec to a system time value.
Date and time spec to decode.
void Sql.Sql(string host)
void Sql.Sql(string host, string db)
void Sql.Sql(string host, mapping(string:int|string) options)
void Sql.Sql(string host, string db, string user)
void Sql.Sql(string host, string db, string user, string password)
void Sql.Sql(string host, string db, string user, string password, mapping(string:int|string) options)
void Sql.Sql(object host)
void Sql.Sql(object host, string db)
Create a new generic SQL object.
|
Select this database.
User name to access the database as.
Password to access the database.
Optional mapping of options. See the SQL-database documentation for the supported options. (eg Mysql.mysql()->create() ).
In versions of Pike prior to 7.2 it was possible to leave out the dbtype, but that has been deprecated, since it never worked well.
Support for options was added in Pike 7.3.
void set_charset(string charset)
Changes the charset that the connection uses for queries and returned text strings.
The charset to use. The valid values and their meanings depends
on the database brand. However, the special value
"unicode"
(if supported) selects a mode where the query
and result strings are unencoded (and possibly wide) unicode
strings.
An error is thrown if the connection doesn't support the specified charset, or doesn't support charsets being set this way at all.
See the set_charset
functions for each database
connection type for further details about the effects on the
connection.
get_charset , Sql.mysql.set_charset
string get_charset()
Returns the (database dependent) name of the charset used for (at least) query strings. Returns zero if the connection doesn't support charsets this way (typically means that a call to set_charset will throw an error).
set_charset , Sql.mysql.get_charset
int|string error()
Return last error message.
void select_db(string db)
Select database to access.
string|object compile_query(string q)
Compiles the query (if possible). Otherwise returns it as is. The resulting object can be used multiple times in query() and big_query().
SQL-query to compile.
array(string|mapping(string|int:mixed)) handle_extraargs(string query, array(mixed) extraargs)
Handle sprintf-based quoted arguments
array(mapping(string:mixed)) query(object|string q, mixed ... extraargs)
Send an SQL query to the underlying SQL-server.
Query to send to the SQL-server. This can either be a string with the query, or a previously compiled query (see compile_query()).
This parameter, if specified, can be in two forms:
A mapping containing bindings of variables used in the query. A variable is identified by a colon (:) followed by a name or number. Each index in the mapping corresponds to one such variable, and the value for that index is substituted (quoted) into the query wherever the variable is used.
res = query("SELECT foo FROM bar WHERE gazonk=:baz",
([":baz":"value"]));
Binary values (BLOBs) may need to be placed in multisets.
Arguments as you would use in sprintf. They are automatically quoted.
res = query("select foo from bar where gazonk=%s","value");
Returns one of the following on success:
|
Throws an exception if the query fails.
int|object big_query(object|string q, mixed ... extraargs)
Send an SQL query to the underlying SQL-server. The result is returned as a Sql.sql_result object. This allows for having results larger than the available memory, and returning some more info about the result. Returns 0 if the query didn't return any result (e.g. INSERT or similar). For the other arguments, they are the same as for the query() function.
void create_db(string db)
Create a new database.
Name of database to create.
void drop_db(string db)
Drop database
Name of database to drop.
void shutdown()
Shutdown a database server.
void reload()
Reload the tables.
string server_info()
Return info about the current SQL-server.
string host_info()
Return info about the connection to the SQL-server.
array(string) list_dbs(string|void wild)
List available databases on this SQL-server.
Optional wildcard to match against.
array(string) list_tables(string|void wild)
List tables available in the current database.
Optional wildcard to match against.
array(mapping(string:mixed)) list_fields(string table, string|void wild)
List fields available in the specified table
Table to list the fields of.
Optional wildcard to match against.
CLASS Sql.sql_result |
Implements the generic result of the SQL-interface. Used for return results from SQL.sql->big_query().
object|array master_res
The actual result.
int index
This is the number of the current row. (0 <= index < num_rows() )
void Sql.sql_result(object|array res)
Create a new Sql.sql_result object
Result to use as base.
int num_rows()
Returns the number of rows in the result.
int num_fields()
Returns the number of fields in the result.
int eof()
Returns non-zero if there are no more rows.
array(mapping(string:mixed)) fetch_fields()
Return information about the available fields.
void seek(int skip)
Skip past a number of rows.
Number of rows to skip.
int|array(string|int) fetch_row()
Fetch the next row from the result.
Module Sql.sql_util |
Some SQL utility functions
string Sql.sql_util.quote(string s)
Quote a string so that it can safely be put in a query.
String to quote.
void Sql.sql_util.fallback()
Throw an error in case an unimplemented function is called.
string Sql.sql_util.emulate_bindings(string query, mapping(string|int:mixed)|void bindings, void|object driver)
Build a raw SQL query, given the cooked query and the variable bindings It's meant to be used as an emulation engine for those drivers not providing such a behaviour directly (i.e. Oracle). The raw query can contain some variables (identified by prefixing a colon to a name or a number (i.e. ":var" or ":2"). They will be replaced by the corresponding value in the mapping.
The query.
Optional mapping containing the variable bindings. Make sure that no confusion is possible in the query. If necessary, change the variables' names.
CLASS Sql.sql_util.UnicodeWrapper |
Result object wrapper performing utf8 decoding of all fields.
Useful for eg Mysql connections which have been set to utf8-mode
using eg "SET NAMES 'utf8'"
.
static object master_resultvoid Sql.sql_util.UnicodeWrapper(object master_result)
int num_rows()
Returns the number of rows in the result.
int num_fields()
Returns the number of fields in the result.
int(0..1) eof()
Returns 1
if there are no more rows in the result.
static array(int|mapping(string:mixed)) field_info
Cached fetch_fields() result.
array(int|mapping(string:mixed)) fetch_fields()
Returns Information about the fields in the result.
The following fields are converted from UTF8 if present:
|
void seek(int rows)
Skip ahead the specified number of rows.
int|array(string) fetch_row()
Fetch the next row from the result.
All strings in the result are decoded from UTF8.
Module Mysql |
This module enables access to the Mysql database from within Pike. Use Sql.Sql for general database access.
Mysql is available from http://www.mysql.com.
Mysql.mysql , Mysql.mysql_result , Sql.Sql
CLASS Mysql.mysql_result |
Objects of this class contain the result from Mysql queries.
Mysql.mysql , Mysql.mysql->big_query()
void Mysql.mysql_result(Mysql.mysql connection)
Make a new Mysql.mysql_result object.
Mysql.mysql->big_query() , Mysql.mysql->list_dbs() , Mysql.mysql->list_tables() , Mysql.mysql->list_processes() , Mysql.mysql
int num_rows()
Number of rows in the result.
num_fields()
int num_fields()
Number of fields in the result.
num_rows()
void field_seek(int field_no)
Skip to specified field.
Places the field cursor at the specified position. This affects which field mysql_result->fetch_field() will return next.
Fields are numbered starting with 0.
This function is usually not enabled. To enable it SUPPORT_FIELD_SEEK must be defined when compiling the mysql-module.
fetch_field() , fetch_fields()
int(0..1) eof()
Sense end of result table.
Returns 1
when all rows have been read, and 0
(zero)
otherwise.
fetch_row()
int|mapping(string:mixed) fetch_field()
Return specification of the current field.
Returns a mapping with information about the current field, and
advances the field cursor one step. Returns 0
(zero) if
there are no more fields.
The mapping contains the same entries as those returned by
Mysql.mysql->list_fields() , except that the entry "default"
is missing.
This function is usually not enabled. To enable it SUPPORT_FIELD_SEEK must be defined when compiling the mysql-module.
fetch_fields() , field_seek() , Mysql.mysql->list_fields()
array(int|mapping(string:mixed)) fetch_fields()
Get specification of all remaining fields.
Returns an array with one mapping for every remaining field in the result table.
The returned data is similar to the data returned by
Mysql.mysql->list_fields() , except for that the entry
"default"
is missing.
Resets the field cursor to 0
(zero).
This function always exists even when fetch_field() and field_seek() don't.
fetch_field() , field_seek() , Mysql.mysql->list_fields()
void seek(int rows)
Skip ahead rows rows.
Can only seek forward.
fetch_row()
int|array(string) fetch_row()
Fetch the next row from the result.
Returns an array with the contents of the next row in the result. Advances the row cursor to the next now.
Returns 0
(zero) at the end of the table.
seek()
CLASS Mysql.mysql |
Interface to the Mysql database.
This class enables access to the Mysql database from within Pike.
Mysql is available from http://www.mysql.com.
Mysql.mysql_result , Sql.Sql
void Mysql.mysql()
void Mysql.mysql(string host)
void Mysql.mysql(string host, string database)
void Mysql.mysql(string host, string database, string user)
void Mysql.mysql(string host, string database, string user, string password)
void Mysql.mysql(string host, string database, string user, string password, mapping(string:string|int) options)
Connect to a Mysql database.
To access the Mysql database, you must first connect to it. This is done with this function.
If you give no argument, or give ""
as host it will connect
with a UNIX-domain socket, which can be a big performance gain.
This optional mapping can contain zero or more of the following parameters:
|
Some options may not be implemented. Unimplemented options are silently ignored.
To use SSL-connections, set the SSL-parameters correctly. They correspond to the parameters given to the mysql-client with the same name so make sure that the mysql-client works with SSL and set these parameters to the same values and everything should work. If SSL-options are loaded from a config-file, one may set the connect_options to include CLIENT_SSL.
If Pike has been built with an old MySQL client lib then it
might not be possible to specify some charsets that the server
supports with the "mysql_charset_name"
option. In such
cases it's possible that set_charset works better (provided
the server is 4.1 or newer).
string _sprintf(int type, void|mapping flags)
int affected_rows()
Returns the number of rows affected by the last query.
int insert_id()
Returns the id of the last INSERT query into a table with an AUTO INCREMENT field.
string error()
Returns a string describing the last error from the Mysql-server.
Returns 0
(zero) if there was no error.
void select_db(string database)
Select database.
The Mysql-server can hold several databases. You select which one you want to access with this function.
create() , create_db() , drop_db()
void create_db(string database)
Create a new database
This function creates a new database named database in the Mysql-server.
select_db() , drop_db()
void drop_db(string database)
Drop a database
This function drops the database named database from the Mysql-server.
create_db() , select_db()
void shutdown()
Shutdown the Mysql-server
This function shuts down a running Mysql-server.
reload()
void reload()
Reload security tables
This function causes the Mysql-server to reload its access tables.
shutdown()
string statistics()
Some Mysql-server statistics
This function returns some server statistics.
server_info() , host_info() , protocol_info()
string server_info()
Get the version number of the Mysql-server.
statistics() , host_info() , protocol_info()
string host_info()
Get information about the Mysql-server connection
statistics() , server_info() , protocol_info()
int protocol_info()
Give the Mysql protocol version
This function returns the version number of the protocol the Mysql-server uses.
statistics() , server_info() , host_info()
Mysql.mysql_result list_dbs()
Mysql.mysql_result list_dbs(string wild)
List databases
Returns a table containing the names of all databases in the Mysql-server. If the argument wild is specified, only those matching it will be returned.
list_tables() , list_fields() , list_processes() , Mysql.mysql_result
Mysql.mysql_result list_tables()
Mysql.mysql_result list_tables(string wild)
List tables in the current database
Returns a table containing the names of all tables in the current database. If the argument wild is given, only those matching it will be returned.
list_dbs() , list_fields() , list_processes() , Mysql.mysql_result
array(int|mapping(string:mixed)) list_fields(string table)
array(int|mapping(string:mixed)) list_fields(string table, string wild)
List all fields.
Returns an array of mappings with information about the fields in the table named table . If the argument wild is given, only those fields matching it will be returned
The mappings contain the following entries:
|
The type of the field can be any of:
"decimal"
, "char"
, "short"
, "long"
,
"float"
, "double"
, "null"
, "time"
,
"longlong"
, "int24"
, "tiny blob"
,
"medium blob"
,
"long blob"
, "var string"
, "string"
or
"unknown"
.
The flags multiset can contain any of:
|
Michael Widenius recomends use of the following query instead: show fields in 'table' like "wild".
list_dbs() , list_tables() , list_processes() , Mysql.mysql_result.fetch_field()
Mysql.mysql_result list_processes()
List all processes in the Mysql-server
Returns a table containing the names of all processes in the Mysql-server.
list_dbs() , list_tables() , list_fields() , Mysql.mysql_result
int binary_data()
Inform if this version of Mysql.mysql supports binary data
This function returns non-zero if binary data can be reliably stored and retreived with this version of the mysql-module.
Usually, there is no problem storing binary data in mysql-tables,
but data containing '\0'
(NUL) couldn't be fetched with old
versions (prior to 3.20.5) of the mysql-library.
Module Postgres |
CLASS Postgres.postgres |
This is an interface to the Postgres (Postgres95, pgsql) database server. This module may or may not be availible on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.
Please notice that unless you wish to specifically connect to a Postgres server, you'd better use the Sql.Sql , which is a server-independent sql-server-class. The interfaces to all existing sql-classes are consistent. Using Sql.Sql ensures that your Pike applications will run with any supported SQL server without changing a single line of code, at least for most common (and simple) operations.
The program Postgres.postgres provides the raw interface to the database. Many functions are not availible for this program. Therefore, its use is DEPRECATED. It is included in this documentation only for completeness' sake. Use Sql.postgres instead, or even better Sql.Sql
There is no testsuite for this module, since to test anything would require a working Postgres server. You can try to use the included scripts in the "pike/src/modules/Postgres/extras" directory but you'll probably have to patch them to reflect your site's settings.
Also note that this module uses blocking I/O I/O to connect to the server. Postgres is quite slow, and so you might want to consider this particular aspect. It is (at least should be) thread-safe, and so it can be used in a multithread environment.
The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the pike interpreter.
|
Refer to the Postgres documentation for further details.
Sql.Sql , Sql.postgres , Sql.postgres_result
string version
Should you need to report a bug to the author, please submit along with the report the driver version number, as returned by this call.
void Postgres.postgres()
void Postgres.postgres(string host, void|string database, void|int port)
With no arguments, this function initializes (reinitializes if a connection had been previously set up) a connection to the Postgres backend. Since Postgres requires a database to be selected, it will try to connect to the default database. The connection may fail however for a variety of reasons, in this case the most likely of all is because you don't have enough authority to connect to that database. So use of this particular syntax is discouraged.
The host argument allows you to connect to databases residing on different hosts. If it is 0 or "", it will try to connect to localhost.
The database argument specifies the database to connect to. If 0 or "", it will try to connect to the default database.
You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.
You don't need bothering about syncronizing the connection to the database: it is automatically closed (and the database is sync-ed) when the object is destroyed.
Sql.postgres , Sql.Sql , select_db
void select_db(string dbname)
This function allows you to connect to a database. Due to restrictions of the Postgres frontend-backend protocol, you always have to be connected to a database, so in fact this function just allows you to connect to a different database on the same server.
This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)
create
Sql.postgres_result big_query(string sqlquery)
This is the only provided interface which allows you to query the database. If you wish to use the simpler "query" function, you need to use the Sql.Sql generic sql-object.
It returns a postgres_result object (which conforms to the Sql.sql_result standard interface for accessing data). I recommend using query() for simpler queries (because it is easier to handle, but stores all the result in memory), and big_query for queries you expect to return huge amounts of data (it's harder to handle, but fectches results on demand).
This function can raise exceptions.
The program Sql.postgres_result is exactly the same as Postgres.postgres_result .
Sql.Sql , Sql.sql_result
string error()
This function returns the textual description of the last server-related error. Returns 0 if no error has occurred yet. It is not cleared upon reading (can be invoked multiple times, will return the same result until a new error occurs).
big_query
void reset()
This function resets the connection to the backend. Can be used for a variety of reasons, for example to detect the status of a connection.
This function is Postgres-specific, and thus it is not availible through the generic SQL-interface.
void _set_notify_callback()
void _set_notify_callback(function f)
With Postgres you can associate events and notifications to tables. This function allows you to detect and handle such events.
With no arguments, resets and removes any callback you might have put previously, and any polling cycle.
With one argument, sets the notification callback (there can be only one for each sqlobject).
The callback function must return no value, and takes a string argument, which will be the name of the table on which the notification event has occured. In future versions, support for user-specified arguments will be added.
The Sql.postgres program adds support for automatic delivery of messages (see it for explanation on the inner workings of this feature).
This function is Postgres-specific, and thus it is not availible through the generic SQL-interface
Sql.postgres
string host_info()
This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).
string _quote(string s)
Escape a string to prevent SQL injection, using the current connection's character encoding settings.
CLASS Postgres.postgres_result |
Contains the result of a Postgres-query.
Sql.postgres, Postgres.postgres, Sql.Sql, Sql.sql_result
void Postgres.postgres_result(object o)
You can't create istances of this object yourself. The only way to create it is via a big_query to a Postgres database.
int num_rows()
Returns the number of rows in the result.
int num_fields()
Returns the number of fields in the result.
array(mapping(string:mixed)) fetch_fields()
Returns an array with an entry for each field, each entry is a mapping with the following fields:
|
For char() fields, length is to be intended as the MAXIMUM length of the field. This is not part of the interface specifications in fact, but a driver-choice. In fact char() fields are for Postgres _FIXED_ length fields, and are space-padded. If CUT_TRAILING_SPACES is defined when the driver is compiled (default behavior) it will cut such spaces.
void seek()
Moves the result cursor (ahead or backwards) the specified number of rows. Notice that when you fetch a row, the cursor is automatically moved forward one slot.
array(string) fetch_row()
Returns an array with the contents of the next row in the result. Advances the row cursor to the next row. Returns 0 at end of table.
Since there's no generic way to know whether a type is numeric or not in Postgres, all results are returned as strings. You can typecast them in Pike to get the numeric value.
seek()
Module Mird |
CLASS Mird.Mird |
The Mird module doubles as the Mird database class.
void Mird.Mird(string filename, void|mapping options)
Opens the database using the given filename. Note that the file filename +".journal" will be created too.
Possible options:
|
"flags"
is a string with any of these characters:
|
Table table(string name)
Gives back the table object for the specified table in the database.
vTable vtable(string name)
vzTable vtable(string name)
A vTable is just like a Table , except that the values can be anything, not just strings. The data is encode_value() 'ed before storage.
A vzTable is just like a vTable , except that the values are both encoded and then packed using Gz. Only available if Pike supports Gz.
string table_name(int id)
Returns the name of the table with id id .
Table new_stringkey_table(string name)
Table new_hashkey_table(string name)
Create a new table in the database.
A transaction will be created when calling these on the Database level.
Transaction transaction()
Creates a new transaction
array(string) tables()
Gives a list of all tables in the database.
object sync()
object sync_please()
Syncs the database (syncs against disc and starts to reuse any space that is freed), sync_please() does this when the last living transaction is finished.
object sync_loop(int seconds)
Starts a call_out loop that will call sync_please once every given interval. 0 or fewer seconds will shut down the loop. Default is 1 minute (60 seconds).
void close()
void destroy()
Syncs and closes the database
CLASS Mird.Mird.Table |
A Mird table has approximately the same characteristics as a Pike mapping. You store key:value pairs in it, you get values from keys, you delete keys.
For convinience, the Table object overloads `[]()
,
`[]=()
, _indices()
and _values()
.
void Mird.Mird.Table(object parent, int table_id)
Mird.Glue.Scanner scanner()
Mird.Glue.Scanner scanner(int key)
Creates a scanner over the called table; if key is given, continue at that key (as returned from Mird.Glue.Scanner.next_key() ).
A scanner can be used to loop over all elements in a table.
string `[](int|string key)
string `[]=(int|string key, string value)
array(int|string) _indices()
array(int|string) _values()
Get and store key:value pairs, list the table.
All storage in the table will create a transaction, if we're not already in a transaction.
mapping cast("mapping")
array cast("array")
It is possible to cast the Table to a mapping or array; this will read all contents of the table, so beware if you have a big table.
CLASS Mird.Mird.vTable |
A vTable is just like a Table , except that the values can be anything, not just strings. The data is encode_value() 'ed before storage.
inherit v0Table : v0Table
inherits Table
CLASS Mird.Mird.vzTable |
This is just like a normal table, but all data is encode_value()ed, and gzipped, and unpacked if reading, for convinience. Only available when Pike supports Gz.
inherit v0Table : v0Table
inherits Table
CLASS Mird.Mird.Transaction |
A Transaction is the environment which in a change to the database can be made. Simplified, the database is not changed until the transaction is closed. More then one change to the database can be performed within one transaction.
If the same entries in the database were changed during the lifespan of the transaction, it cannot be closed but has to be cancelled.
Table table(string name)
vTable vtable(string name)
vzTable vztable(string name)
Creates a Table , vTable or vzTable object for the specified table, which emulates a mapping and in which you can make changes to the database or do lookups.
Table new_stringkey_table(string name)
Table new_hashkey_table(string name)
Creates a new table in the database. A stringkey table is a mapping from string to string, and a hashkey table is mapping from int to string.
void delete_table(string name)
Removes a table and all items in it
array(string) tables()
Returns the names of the tables in the database.
void close()
Finishes a transaction. This throws exceptions if there were conflicts.
void cancel()
void destroy()
Cancels (rewinds) a transaction.
CLASS Mird.Mird.cTransaction |
Caching transaction
inherit Transaction : Transaction
Module Mird.Glue |
CLASS Mird.Glue.Mird |
void close()
void destroy()
This closes the database, ie
cancels all ongoing transactions
syncs the database (flushes caches, and frees all unused blocks)
destroys the database object
void Mird.Glue.Mird(string filename)
void Mird.Glue.Mird(string filename, mapping options)
|
flags is a string with any of these characters:
"r" - readonly
"R" - readonly in a live system (one process writes, many reads)
"n" - don't create if it doesn't exist
"x" - exclusive (always create)
"s" - call fsync when finishing transactions
"S" - call sync(2) after fsync
"j" - complain if journal file is gone missing
zero|string fetch(int table_id, int|string key)
the data value or zero_type if key wasn't found
int first_unused_key(int table_id)
int first_unused_key(int table_id, int start_key)
int first_unused_table()
int first_unused_table(int start_table_id)
void _debug_cut()
This closes the database without flushing or syncing. This should never be used and exist only for testing purposes.
object sync()
object sync_please()
Syncs the database; this flushes all eventual caches and frees all unused blocks.
sync() can only be called when there is no ongoing transactions. sync_please() sets a marker that the database should be synced when the last transaction is finished or cancelled, which will eventually sync the database.
The usage could be in a call_out-loop,
[...]
call_out(sync_the_database,5*60);
[...]
void sync_the_database()
{
call_out(sync_please,5*60);
my_mird->sync_please();
}
the object being called
void _debug_check_free()
void _debug_check_free(int(0..1) silent)
this syncs the database and verifies the database free list. It prints stuff on stderr. It exists only for debug purpose and has no other use.
int _debug_syscalls()
the number of syscalls the database has done so far
CLASS Mird.Glue.Transaction |
A transaction object is enclosing a change in the database. It can either succeed in full or fail in full. If some other transaction has changed the same data as the current, the transaction closed last will fail (conflict).
void cancel()
void destroy()
cancels (rewinds) a transaction
void close()
closes a transaction; may cast exceptions if there are conflicts
void Mird.Glue.Transaction(Mird parent)
Creates a new transaction within the given database.
object store(int table_id, int|string key, string data)
object delete(int table_id, int|string key)
zero|string fetch(int table_id, int|string key)
the data value or zero_type if key wasn't found
object delete_table(int table_id)
delets a table from the database
this can take some time, depending on how much data that is in that table
object depend_table(int table_id)
int first_unused_key(int table_id)
int first_unused_key(int table_id, int start_key)
int first_unused_table()
int first_unused_table(int start_table_id)
object new_hashkey_table(int table_id)
object new_stringkey_table(int table_id)
creates a table in the database
object resolve()
Tries to resolve a transaction; casts an exception if there is a conflict. May be called more then once.
the object being called
CLASS Mird.Glue.Scanner |
Objects of this class is used to read off all contents of a table in the database.
void Mird.Glue.Scanner(Mird database, int table_id)
void Mird.Glue.Scanner(Transaction transaction, int table_id)
Creates a new scanner object, tuned to the given table and database or transaction.
int next_key()
Gives back a possible argument to the constructor of Scanner ; allows the possibility to continue to scan even if the Scanner object is lost.
zero|mapping(string|int:string) read(int n)
Reads some tupels from the table the scanner is directed against; the size of the resulting mapping is close to this number.
a mapping of the next (about) n tupels in the table, or zero if there is no more tupels in the table
a buffer directly depending on this size is allocated; it's not recommended doing a "read(0x7fffffff)".
Module Gdbm |
CLASS Gdbm.gdbm |
void Gdbm.gdbm(void|string file, void|string mode)
Without arguments, this function does nothing. With one argument it opens the given file as a gdbm database, if this fails for some reason, an error will be generated. If a second argument is present, it specifies how to open the database using one or more of the follow flags in a string:
|
The fast mode prevents the database from syncronizing each change in the database immediately. This is dangerous because the database can be left in an unusable state if Pike is terminated abnormally.
The default mode is "rwc"
.
The gdbm manual states that it is important that the database is closed properly. Unfortunately this will not be the case if Pike calls exit() or returns from main(). You should therefore make sure you call close or destruct your gdbm objects when exiting your program. This will probably be done automatically in the future.
string fetch(string key)
string `[](string key)
Return the data associated with the key 'key' in the database. If there was no such key in the database, zero is returned.
int(0..1) delete(string key)
Remove a key from the database. Returns 1 if successful, otherwise 0, e.g. when the item is not present or the database is read only.
string firstkey()
Return the first key in the database, this can be any key in the database.
string nextkey(string key)
This returns the key in database that follows the key 'key' key. This is of course used to iterate over all keys in the database.
// Write the contents of the database for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k)) write(k+":"+gdbm->fetch(k)+"\n");
int store(string key, string data)
int `[]=(string key, string data)
Associate the contents of 'data' with the key 'key'. If the key 'key' already exists in the database the data for that key will be replaced. If it does not exist it will be added. An error will be generated if the database was not open for writing.
gdbm[key] = data;
int reorganize()
Deletions and insertions into the database can cause fragmentation which will make the database bigger. This routine reorganizes the contents to get rid of fragmentation. Note however that this function can take a LOT of time to run.
void sync()
When opening the database with the 'f' flag writings to the database can be cached in memory for a long time. Calling sync will write all such caches to disk and not return until everything is stored on the disk.
void close()
Closes the database.