Connection Sql(__deprecated__(Connection) con)
Connection Sql(__deprecated__(Connection) con, string db)
Create a new generic SQL connection (DEPRECATED).
conUse this connection to access the SQL-database.
dbSelect this database.
In Pike 8.1 and later this function is essentially a noop;
if you actually need it, you may want to use 8.0::Sql.Sql.
Returns con.
8.0::Sql.Sql, Connection
Connection Sql(string host)
Connection Sql(string host, string db)
Connection Sql(string host, mapping(string:int|string) options)
Connection Sql(string host, string db, string user)
Connection Sql(string host, string db, string user, string password)
Connection Sql(string host, string db, string user, string password, mapping(string:int|string) options)
Create a new generic SQL connection.
host | Connect to the server specified. The string should be on the
format:
dbtype://[user[:password]@]hostname[:port][/database]
Use the dbtype protocol to connect to the database
server on the specified host. If the hostname is There is a special dbtype |
| Access through a UNIX-domain socket or similar. |
dbSelect this database.
userUser name to access the database as.
passwordPassword to access the database.
optionsOptional 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.
Exactly which databases are supported by pike depends on the installed set of client libraries when pike was compiled.
The possible ones are
libmysql based mysql connection
libmysql based mysql connection, using SSL
ODBC based connection
Msql
ODBC based connection
Oracle using oracle libraries
PostgreSQL direct network access. This module is independent of any external libraries.
PostgreSQL libray access. Uses the Postgres module.
Remote SQL api, requires a rsql server running on another host. This is an API that uses sockets to communicate with a remote pike running pike -x rsqld on another host.
In-process SQLite database, uses the SQLite module
Uses the sybase module to access sybase
Sybase and Microsoft SQL direct network access using the TDS protocol. This module is independent of any external libraries.
Support for options was added in Pike 7.3.
Use of an object host was deprecated in Pike 8.1.
Prior to Pike 8.1 this was a wrapper class.
8.0::Sql.Sql, Connection
Base class for a connection to an SQL database.
inherit __builtin.Sql.Connection : Connection
This class is the base class for connections to SQL servers. It is a generic interface on top of which the DB server specific implement their specifics.
This class thus serves as an interface guideline for the DB server specific connection classes.
The query results are returned in different ways depending on the query functions used: The ..typed_query functions select typed mode, while the other query functions uses the older untyped mode.
In untyped mode, all values except SQL NULL are returned as strings in their display representation, and SQL NULL is returned as zero.
In typed mode, values are returned in pike native form where it
works well. That means at least that SQL integer fields are
returned as pike integers, floats as floats, SQL NULL as
Val.null, and of course strings still as strings. The
representation of other SQL types depends on the capabilities of
the server specific backends. It's also possible that floats in
some cases are represented in other ways if too much precision is
lost in the conversion to pike floats.
For historical reasons, there may be server specific backends that operate differently from what is described here, e.g. some that return a bit of typed data in untyped mode.
Typed operation was not supported at all prior to Pike 7.8.363, and may not be supported for all databases.
Sql.Connection, Sql.Sql(), Result
bool __builtin.Sql.Connection.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).
| No (default) |
| Yes |
__deprecated__ this_program __builtin.Sql.Connection.master_sql
Compatibility interface.
This used to be a variable containing the server specific connection object used for the actual SQL queries.
As the wrapper no longer exists, this symbol now just evaluates to the object.
Read only
variant .Result big_query(object|string q)
Send an SQL query synchronously to the SQL-server and return the results in untyped mode.
qQuery to send to the SQL-server. This can either be a string with the
query, or a previously compiled query (see compile_query()).
An Sql.Result object in untyped
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
Might throw an exception if the query fails. In some cases, the exception is delayed, because the database reports the error a (little) while after the query has already been started.
This prototype function is the base variant and is intended to be overloaded by actual drivers.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than query also for
ones that return only a few rows.
query, streaming_query, big_typed_query, streaming_typed_query
variant .Result big_query(object|string q, mapping(string|int:mixed) bindings, void|__deprecated__(string) charset)
Send an SQL query synchronously to the SQL-server and return the results in untyped mode.
qQuery to send to the SQL-server. This can either be a string with the
query, or a previously compiled query (see compile_query()).
bindingsA 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 = big_query("SELECT foo FROM bar WHERE gazonk=:baz",
([":baz":"value"]));
Binary values (BLOBs) may need to be placed in multisets.
Mapping entries prefixed with an asterisk ('*') are reserved
for for database-specific query options (like eg QUERY_OPTION_CHARSET).
An Sql.Result object in untyped
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
Might throw an exception if the query fails. In some cases, the exception is delayed, because the database reports the error a (little) while after the query has already been started.
Calls the base variant of big_query() after having inserted
the bindings into the query (using emulate_bindings()).
Drivers that actually support bindings should overload this variant in addition to the base variant.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than query also for
ones that return only a few rows.
Support for database-specific query options was added in Pike 9.0.
query, emulate_bindings, streaming_query, big_typed_query,
streaming_typed_query
variant .Result big_query(object|string q, string|multiset|int|float|object extraarg, string|multiset|int|float|object|mapping ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results in untyped mode.
qQuery to send to the SQL-server. This can either be a string with the
query, or a previously compiled query (see compile_query()).
extraargextraargsArguments as you would use in sprintf. They are automatically
quoted. After the sprintf-style options a mapping with options
may be specified.
res = query("select foo from bar where gazonk=%s","value");
An Sql.Result object in untyped
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
The default implementation normalizes q and extraargs to
use the bindings mapping (via handle_extraargs()), and calls
one of the other variants of big_query() with the result.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than query also for
ones that return only a few rows.
Support for specifying an options mapping was added in Pike 9.0.
query, handle_extraargs, streaming_query
variant .Result big_typed_query(object|string q)
Send an SQL query synchronously to the SQL-server and return the results in typed mode.
For the argument, please see the big_query() function.
An Sql.Result object in typed
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
Typed mode support varies per database and per datatype. SQL datatypes which the current database cannot return as a native Pike type, will be returned as (untyped) strings.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than typed_query also
for ones that return only a few rows.
Drivers should override this prototype function.
query, typed_query, big_query, streaming_query
variant .Result big_typed_query(object|string q, mapping(string|int:mixed) bindings, void|__deprecated__(string) charset)
Send an SQL query synchronously to the SQL-server and return the results in typed mode.
For the arguments, please see the big_query() function.
The result is returned as an Sql.Result object in typed
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
Typed mode support varies per database and per datatype. SQL datatypes which the current database cannot return as a native Pike type, will be returned as (untyped) strings.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than typed_query also
for ones that return only a few rows.
Drivers that actually support bindings should overload this variant in addition to the base variant.
query, typed_query, big_query, streaming_query
variant .Result big_typed_query(object|string q, string|multiset|int|float|object extraarg, string|multiset|int|float|object|mapping ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results in typed mode.
For the arguments, please see the big_query() function.
The result is returned as an Sql.Result object in typed
mode. This allows for having some more info about the result as
well as processing the result in a streaming fashion, although the
result itself wasn't obtained streamingly from the server.
Typed mode support varies per database and per datatype. SQL datatypes which the current database cannot return as a native Pike type, will be returned as (untyped) strings.
Despite the name, this function is not only useful for "big"
queries. It typically has less overhead than typed_query also
for ones that return only a few rows.
query, typed_query, big_query, streaming_query
string|object compile_query(string q)
Compiles the query (if possible). Otherwise returns it as is. The resulting object can be used multiple times to the query functions.
qSQL-query to compile.
The default implementation just returns q unmodified.
query, typed_query, big_query, big_typed_query,
streaming_query, streaming_typed_query
__builtin.Sql.Connection __builtin.Sql.Connection(string host)
__builtin.Sql.Connection __builtin.Sql.Connection(string host, string db)
__builtin.Sql.Connection __builtin.Sql.Connection(string host, mapping(string:int|string) options)
__builtin.Sql.Connection __builtin.Sql.Connection(string host, string db, string user)
__builtin.Sql.Connection __builtin.Sql.Connection(string host, string db, string user, string password)
__builtin.Sql.Connection __builtin.Sql.Connection(string host, string db, string user, string password, mapping(string:int|string) options)
Create a new SQL connection.
host | Connect to the server specified. |
| Access through a UNIX-domain socket or similar. |
dbSelect this database.
userUser name to access the database as.
passwordPassword to access the database.
optionsOptional mapping of options.
See the SQL-database documentation for the supported options.
(eg Mysql.mysql()->create()).
This function is typically called via Sql.Sql().
Support for options was added in Pike 7.3.
The base class (__builtin.Sql.Connection) only has a prototype.
Sql.Sql()
void create_db(string db)
Create a new database.
dbName of database to create.
int decode_date(string datestr)
Converts a database date-only spec to a system time value.
datestrDate spec to decode.
Returns the number of seconds since 1970-01-01T00:00:00 UTC to 00:00:00 at the specified date in the current timezone.
int decode_datetime(string datetime)
Converts a database date and time spec to a system time value.
datetimeDate and time spec to decode.
Returns the number of seconds since 1970-01-01T00:00:00 UTC to the specified date and time in the current timezone.
The default implementation decodes an ISO 9601 timestamp.
int decode_time(string timestr, int|void date)
Converts a database time spec to a system time value.
timestrTime spec to decode.
dateTake the date part from this system time value. If zero, a seconds-since-midnight value is returned.
Returns the number of seconds since midnight.
void drop_db(string db)
Drop database
dbName of database to drop.
protected string emulate_bindings(string query, mapping(string|int:mixed) bindings, __deprecated__(string)|void charset)
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 that do
not provide such behaviour directly (like eg Oracle).
The raw query can contain some variables (identified by prefixing
a colon to a name or a number (eg ":var" or ":2")).
They will be replaced by the corresponding value in the mapping.
queryThe query.
bindingsMapping containing the variable bindings. Make sure that no confusion is possible in the query. If necessary, change the variables' names.
charsetQuery charset. Compatibility with Pike 8.0 Mysql API.
Use the QUERY_OPTION_CHARSET entry in bindings instead.
string encode_date(int time)
Converts a system time value to an appropriately formatted date-only spec for the database.
timeTime to encode.
The default implementation returns an ISO 9601 date.
string encode_datetime(int time)
Converts a system time value to an appropriately formatted date and time spec for the database.
timeTime to encode.
The default implementation returns an ISO 9601 timestamp.
string encode_time(int time, int|void date)
Converts a system time value to an appropriately formatted time spec for the database.
timeTime to encode.
dateIf 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.
The default implementation returns a colon-separated ISO 9601 time.
int|string error(void|int clear)
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).
The string returned is not newline-terminated.
clearTo clear the error, set it to 1.
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
array(string|mapping(string|int:mixed)) handle_extraargs(string query, array(mixed) extraargs)
Handle sprintf-based quoted arguments
queryThe query as sent to one of the query functions.
extraargsThe arguments following the query. The last element of the array may be a bindings mapping.
Returns an array with up to two elements:
| Array | |
| The query altered to use bindings-syntax. |
| A bindings mapping. Not present if no bindings were added. |
Support for specifying an initial options mapping was added in Pike 9.0.
string host_info()
Return info about the connection to the SQL-server.
int is_open()
Returns true if the connection seems to be open.
This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.
For a more reliable check of whether the connection
is alive, please use ping().
The default implementation just returns the value 1.
ping()
array(string) list_dbs(string|void wild)
List available databases on this SQL-server.
wildOptional wildcard to match against.
This function calls low_list_dbs() and optionally
performs wildcard filtering.
low_list_dbs()
array(mapping(string:mixed)) list_fields(string table, string|void wild)
List fields available in the specified table
tableTable to list the fields of.
wildOptional wildcard to match against.
The default implementation calls low_list_fields()
and applies the wild-card filter on the result.
array(string) list_tables(string|void wild)
List tables available in the current database.
wildOptional wildcard to match against.
The default implementation calls low_list_tables().
protected array(string)|zero low_list_dbs()
List available databases on this SQL-server.
Returns an array with database names on success and 0
(zero) on failure.
Called by list_dbs().
This function is intended for overriding by drivers not supporting wildcard filtering of database names.
The default implementation attempts the query
"SHOW DATABASES".
list_dbs()
protected array(mapping(string:mixed))|zero low_list_fields(string table)
List fields available in the specified table
tableTable to list the fields of.
Returns an array of mappings with at least the fields:
| The name of the field. |
| The name of the table. |
Typically there are also entries for the field types, field widths and nullability.
This function is intended for overriding by drivers not supporting wildcard filtering of field names.
The default implementation attempts the query
"SHOW FIELDS FROM 'table'", and then
performs some normalization of the result.
list_fields()
protected array(string)|zero low_list_tables()
List tables available in the current database.
This function is intended for overriding by drivers not supporting wildcard filtering of table names.
The default implementation attempts the query
"SHOW TABLES".
list_tables()
int ping()
Check whether the connection is alive.
Returns one of the following:
| Everything ok. |
| The connection reconnected automatically. |
| The server has gone away, and the connection is dead. |
The default implementation performs a trivial select to check the connection.
is_open()
variant __experimental__ Concurrent.Future promise_query(string q, void|mapping(string|int:mixed) bindings, void|function(array, .Result, array:array) map_cb)
variant __experimental__ Concurrent.Future promise_query(string q, function(array, .Result, array:array) map_cb)
Sends a typed query to the database asynchronously.
An Sql.Promise object which can be used to obtain
an Sql.FutureResult object to evaluate the query.
streaming_typed_query(), Sql.Promise, Sql.FutureResult
map_cbCallback function which is called for every row returned.
First parameter is the row, second parameter is the result object
being processed, and the third parameter is the array of result rows
already collected so far. The function should return the modified
version of the row that needs to be stored, or it should return
0 to discard the row.
Sql.Connection db = Sql.Connection("..."); Sql.Promise q1 = db->promise_query("SELECT 42")->max_records(10); Sql.Promise q2 = db->promise_query("SELECT :foo::INT", (["foo":2])); array(Concurrent.Future) all = ({ q1, q2 })->future(); // To get a callback for each of the requests all->on_success(lambda (Sql.FutureResult resp) { werror("Got result %O from %O\n", resp->get(), resp->query); }); all->on_failure(lambda (Sql.FutureResult resp) { werror("Request %O failed: %O\n", resp->query, resp->status_command_complete); }); // To get a callback when all of the requests are done. In this case // on_failure will be called if any of the requests fails. Concurrent.Future all2 = Concurrent.results(all); all2->on_success(lambda (array(Sql.FutureResult) resp) { werror("All requests were successful: %O\n", resp); }); all->on_failure(lambda (Sql.FutureResult resp) { werror("Requests %O failed with %O.\n", resp->query, resp->status_command_complete); });
This is an experimental API, and is likely to be changed to return other objects in future releases of Pike.
array(mapping(string:string|zero)) query(object|string q, mixed ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results in untyped mode.
For the arguments, please see the big_query() function.
Returns one of the following on success:
| The result as an array of mappings indexed on the name of the columns. The values are either strings with the display representations or zero for the SQL NULL value. |
| The value |
Throws an exception if the query fails.
The default implementation calls big_query()
and converts its result.
typed_query, big_query, streaming_query
string|zero query_db()
Query current database.
Returns the currently selected database.
select_db()
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);
The default implementation quotes single quotes by doubling them.
void reload()
Reload the tables.
void reset()
Reset connection state.
void select_db(string db)
Select database to access.
query_db()
string server_info()
Return info about the current SQL-server.
void set_charset(string charset)
Changes the charset that the connection uses for queries and returned text strings.
charsetThe 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
void shutdown()
Shutdown a database server.
string sqlstate()
Return last SQLSTATE.
The SQLSTATE error codes are specified in ANSI SQL.
variant .Result streaming_query(object|string q)
Send an SQL query synchronously to the SQL-server and return the results streaming in untyped mode.
For the arguments, please see the big_query() function.
A streaming Sql.Result object in
untyped mode. This allows for having results larger than the
available memory, and returning some more info about the result.
Drivers should override this prototype function.
Typed mode support varies per database and per datatype. SQL datatypes which the current database cannot return as a native Pike type, will be returned as (untyped) strings.
big_query, streaming_typed_query
variant .Result streaming_query(object|string q, mapping(string:mixed) bindings, void|__deprecated__(string) charset)
Send an SQL query synchronously to the SQL-server and return the results streaming in untyped mode.
For the arguments, please see the big_query() function.
A streaming Sql.Result object in
untyped mode. This allows for having results larger than the
available memory, and returning some more info about the result.
Drivers that implement bindings should override this prototype function.
big_query, streaming_typed_query
variant .Result streaming_query(object|string q, string|multiset|int|float|object extraarg, string|multiset|int|float|object|mapping ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results streaming in untyped mode.
For the arguments, please see the big_query() function.
A streaming Sql.Result object in
untyped mode. This allows for having results larger than the
available memory, and returning some more info about the result.
big_query, streaming_typed_query
variant .Result streaming_typed_query(object|string q)
Send an SQL query synchronously to the SQL-server and return the results streaming in typed mode.
For the arguments, please see the big_query() function.
A streaming Sql.Result object in
typed mode. This allows for having results larger than the
available memory, and returning some more info about the result.
Drivers should override this prototype function.
big_query, streaming_query, big_typed_query
variant .Result streaming_typed_query(object|string q, mapping(string|int:mixed) bindings, void|__deprecated__(string) charset)
Send an SQL query synchronously to the SQL-server and return the results streaming in typed mode.
For the arguments, please see the big_query() function.
A streaming Sql.Result object in
typed mode. This allows for having results larger than the
available memory, and returning some more info about the result.
Drivers should override this prototype function.
big_query, streaming_query, big_typed_query
variant .Result streaming_typed_query(object|string q, string|multiset|int|float|object extraarg, string|multiset|int|float|object|mapping ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results streaming in typed mode.
For the arguments, please see the query() function.
A streaming Sql.Result object in
typed mode. This allows for having results larger than the
available memory, and returning some more info about the result.
big_query, streaming_query, big_typed_query
array(mapping(string:mixed)) typed_query(object|string q, mixed ... extraargs)
Send an SQL query synchronously to the SQL-server and return the results in typed mode.
For the arguments, please see the query() function.
Returns one of the following on success:
| The result as an array of mappings indexed on the name of the columns. The values have the appropriate native pike types where they fit the SQL data types - see the class doc for details on typed mode. |
| The value |
Typed mode support varies per database and per datatype. SQL datatypes which the current database cannot return as a native Pike type, will be returned as (untyped) strings.
The default implementation calls big_typed_query()
and converts its result.
query, big_typed_query
Base class for the result from Connection.big_query() et al.
inherit __builtin.Sql.Result : Result
Base class for results for the SQL-interface.
Used for return results from Sql.Connection->big_query().
int __builtin.Sql.Result.index
This is the number of the current row. The actual semantics differs between different databases.
__deprecated__ array|this_program __builtin.Sql.Result.master_res
Getter for the actual result object.
Returns the current object.
Obsoleted in Pike 8.1 due to the wrapper class no longer existing, and this symbol thus essentially being a noop..
Replaced by __builtin.Sql.Result..
Read only
int affected_rows()
The number of affected rows by this query.
status_command_complete(), num_rows()
__builtin.Sql.Result __builtin.Sql.Result(mixed res)
Create a new Sql.Result object
resResult to use as base.
int eof()
Non-zero if there are no more rows.
Not reliable! Some drivers have no support for checking whether there are more rows without also fetching them.
array(mapping(string:mixed)) fetch_fields()
Information about the available fields.
string fetch_json_result()
Fetch remaining result as JSON-encoded data.
array(mixed) fetch_row()
Fetch the next row from the result.
Returns an array with one element per field in the same order as
reported by fetch_fields(). See the Sql.Connection class
documentation for more details on how different data types are
represented.
On EOF it returns 0.
fetch_row_array(),
set_result_callback(), set_result_array_callback()
array(array(mixed)) fetch_row_array()
Multiple result rows at a time (at least one).
Every result row with one element per field in the same order as
reported by fetch_fields(). See the Sql.Connection class
documentation for more details on how different data types are
represented.
On EOF it returns 0.
fetch_row(),
set_result_callback(), set_result_array_callback()
protected int increment_index(int|void val)
Increment the index.
valValue to increment the index with. Defaults to 1.
Returns the new value of the index.
This is a helper function for implementations to update the index.
It is typically called from fetch_row().
this_program next_result()
Switch to the next set of results.
Some databases support returning more than one set of results. This function terminates the current result and switches to the next (if any).
Returns the Result object if there were more results,
and 0 (zero) otherwise.
May throw the same errors as Sql.Connection()->big_query() et al.
int num_fields()
The number of fields in the result.
int num_rows()
The number of rows in the result.
Depending on the database implementation, this number can still increase between subsequent calls if the results from the query are not complete yet. This function is only guaranteed to return the correct count after EOF has been reached.
affected_rows(), eof()
void seek(int skip)
Skip past a number of rows.
skipNumber of rows to skip.
void set_result_array_callback(function(this_program, array(array(mixed)), __unknown__ ... :void) callback, mixed ... args)
Sets up a callback for sets of rows returned from the database. First argument passed is the resultobject itself, second argument is the array of result rows (zero on EOF).
fetch_row_array(), set_result_callback()
void set_result_callback(function(this_program, array(mixed), __unknown__ ... :void) callback, mixed ... args)
Sets up a callback for every row returned from the database. First argument passed is the resultobject itself, second argument is the result row (zero on EOF).
fetch_row(), set_result_array_callback()
string status_command_complete()
The command-complete status for this query.
affected_rows()
This class encapsulates a connection to a MySQL server, and implements the glue needed to access the Mysql module from the generic SQL module.
When query results are returned in typed mode, the MySQL data types are represented like this:
Returned as Val.null.
Returned as pike integers.
Returned as pike floats.
Returned as pike integers for fields that are declared to
contain zero decimals, otherwise returned as Gmp.mpq objects.
Returned as strings in their display representation (see the MySQL manual).
Calendar objects are not used partly because they always
represent a specific point or range in time, which these MySQL
types do not.
Also returned as strings in the display representation.
The reason is that it's both more efficient and more robust (wrt time zone interpretations) to convert these to unix timestamps on the MySQL side rather than in the client glue. I.e. use the UNIX_TIMESTAMP function in the queries to retrieve them as unix timestamps on integer form.
All string types are returned as pike strings. The MySQL glue
can handle charset conversions for text strings - see
set_charset and set_unicode_decode_mode.
Sql.Connection, Sql.Sql()
inherit Mysql.mysql : mysql
variant Result big_query(string query, mapping(string|int:mixed)|void bindings, void|__deprecated__(string) charset)
Sends a query to the server.
queryThe SQL query.
bindingsAn optional bindings mapping. See Sql.query for details about
this. The mapping may contain a QUERY_OPTION_CHARSET entry with
the same semantics as the deprecated charset parameter below.
charsetDEPRECATED An optional charset that will be used temporarily
while sending query to the server. If necessary, a query
SET character_set_client=charsetis sent to the server first, then query is sent as-is, and then
the connection charset is restored again (if necessary).
Primarily useful with charset set to "latin1" if
unicode encode mode (see set_unicode_encode_mode) is enabled
(the default) and you have some large queries (typically blob
inserts) where you want to avoid the query parsing overhead.
Deprecated; use the entry QUERY_OPTION_CHARSET in bindings instead.
A Result object is returned if the query is of a
kind that returns a result. Zero is returned otherwise.
The individual fields are returned as strings except for NULL,
which is returned as UNDEFINED.
Sql.big_query(), big_typed_query(), streaming_query()
variant Result big_typed_query(string query, mapping(string|int:mixed)|void bindings, void|__deprecated__(string) charset)
Makes a typed SQL query.
This function sends the SQL query query to the MySQL server and
returns a result object in typed mode, which means that the types
of the result fields depend on the corresponding SQL types. See
the class docs for details.
In all other respects, it behaves like big_query.
big_query(), streaming_typed_query()
string encode_date(int time)
Converts a system time value to an appropriately formatted date-only spec for the database.
timeTime to encode.
string encode_datetime(int time)
Converts a system time value to an appropriately formatted date and time spec for the database.
timeTime to encode.
string encode_time(int time, void|int date)
Converts a system time value to an appropriately formatted time spec for the database.
timeTime to encode.
dateIf 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 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
int get_unicode_decode_mode()
Returns nonzero if unicode decode mode is enabled, zero otherwise.
set_unicode_decode_mode
int get_unicode_encode_mode()
Returns nonzero if unicode encode mode is enabled, zero otherwise.
set_unicode_encode_mode
bool is_keyword(string name)
Return 1 if the argument name is a mysql keyword that needs to
be quoted in a query. The list is currently up-to-date with MySQL
5.1.
string latin1_to_utf8(string s, int extended)
Converts a string in MySQL latin1 format to UTF-8.
string quote(string s)
Quote a string so that it can safely be put in a query.
sString to quote.
void set_charset(string charset)
Changes the connection charset. Works similar to sending the query
SET NAMES but also records the charset on the
client side so that various client functions work correctly.charset
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 Charset
module to do conversions, or just use the special
"unicode" charset instead.
get_charset, set_unicode_encode_mode, set_unicode_decode_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 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.
enableNonzero 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.
An error is also thrown if Pike has been compiled with a MySQL client library older than 4.1.0, which lack the necessary support for this.
set_unicode_encode_mode
bool 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.
| Unicode encode mode is enabled. |
| Unicode encode mode couldn't be enabled because an
incompatible connection charset is set. You need to do
|
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")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 has control chars.
This small discrepancy is not present when the connection
charset is unicode.
set_unicode_decode_mode, set_charset
variant Result streaming_query(string query, mapping(string|int:mixed)|void bindings, void|__deprecated__(string) charset)
Makes a streaming SQL query.
This function sends the SQL query query to the Mysql-server.
The result of the query is streamed through the returned
Result object. Note that the involved database
tables are locked until all the results has been read.
In all other respects, it behaves like big_query.
big_query(), streaming_typed_query()
variant Result streaming_typed_query(string query, mapping(string|int:mixed)|void bindings, void|__deprecated__(string) charset)
Makes a streaming typed SQL query.
This function acts as the combination of streaming_query()
and big_typed_query().
big_typed_query(), streaming_typed_query()
string utf8_encode_query(string q, function(string, __unknown__ ... :string) encode_fn, mixed ... extras)
Encodes the appropriate sections of the query with encode_fn.
Everything except strings prefixed by an introducer (i.e.
_something or N) is encoded.
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
This module enables access to the Mysql database from within Pike.
You typically don't want to access this module directly, instead
use Sql.Sql() with an "mysql://" URL.
Mysql.mysql, Mysql.mysql()->Result, Sql.Sql
string client_info()
Get some information about the Mysql-server client library.
mysql()->statistics(), mysql()->server_info(),
mysql()->protocol_info(), mysql()->info()
This class provides some abstractions on top of an SQL table.
At the core it is generic for any SQL database, but the current
implementation is MySQL specific on some points, notably the
semantics of AUTO_INCREMENT, the quoting method, knowledge about
column types, and some conversion functions. Hence the location in
the Mysql module.
Among other things, this class handles some convenient conversions between SQL and pike data types:
Similar to Sql.big_typed_query, SQL integer and floating
point columns are converted to/from pike ints and floats, and
SQL NULLs are converted to/from the Val.null object.
MySQL DECIMAL columns are converted to/from Gmp.mpq objects
if they have one or more decimal places, otherwise they are
converted to/from ints.
MySQL TIMESTAMP columns are converted to/from pike ints containing unix timestamps. This conversion is done on the MySQL side using the UNIX_TIMESTAMP and FROM_UNIXTIME functions, which means that the conversion is not susceptible to offsets due to time zone differences etc. There is however one special case here that MySQL doesn't handle cleanly - see note below.
Other SQL types are kept in string form. That includes DATE, TIME, and DATETIME, which are returned as MySQL formats them.
Note that Sql.mysql can handle conversions to/from Unicode
strings for text data types. If that is enabled then this class
also supports that conversion.
There are debug checks (with the DEBUG define) that verify the incoming pike types, to avoid bugs which could otherwise be hidden by implicit casts on the SQL side. The date and time types (except TIMESTAMP) can be sent either as strings or integers (e.g. either "2010-01-01" or 20100101).
This class can also optionally simulate an arbitrary set of fields in each table row: If a field name is the same as a column then the column is accessed, otherwise it accesses an entry in a mapping stored in a special BLOB column which is usually called "properties".
Although SQL is case insensitive on column names, this class isn't.
The generated SQL queries always quote table and column names according to MySQL syntax using backticks (`). However, literal backticks in names are not quoted and might therefore lead to SQL syntax errors. This might change if it becomes a problem.
The handling of TIMESTAMP columns in MySQL (as of 5.1 at least) through UNIX_TIMESTAMP and FROM_UNIXTIME has one problem if the active time zone uses daylight-saving time:
Apparently FROM_UNIXTIME internally formats the integer to a MySQL date/time string, which is then parsed again to set the unix timestamp in the TIMESTAMP column. The formatting and the parsing uses the same time zone, so the conversions generally cancel themselves out. However, there is one exception with the 1 hour overlap in the fall when going from summer time to normal time.
E.g. if the active time zone on the connection is Central European Time, which uses DST, then setting 1130630400 (Sun 30 Oct 2005 2:00:00 CEST) through "INSERT INTO foo SET ts = FROM_UNIXTIME(1130630400)" actually sets the ts column to 1130634000 (Sun 30 Oct 2005 2:00:00 CET).
The only way around that problem is apparently to ensure that the time zone used on the connection is one which doesn't use DST. E.g. UTC is a reasonable choice, which can be set on the connection through "SET time_zone = '+00:00'". That is not done automatically by this class.
mapping(string:string) Mysql.SqlTable.col_types
Maps the names of the table columns to the types SqlTable will
handle them as. This is queried from the database in create. Do
not change.
function(void:Sql.Sql) Mysql.SqlTable.get_db
Callback to get a database connection.
string Mysql.SqlTable.id_col
The column containing the AUTO_INCREMENT values (if any).
array(string) Mysql.SqlTable.pk_cols
The column(s) containing the primary key, in order. Typically it
is the same as ({.id_col})
string Mysql.SqlTable.prop_col
The column containing miscellaneous properties. May be zero if this feature is disabled. Do not change.
int Mysql.SqlTable.prop_col_max_length
Maximum length of the value prop_col can hold. Only applicable
if prop_col is set. Do not change.
string Mysql.SqlTable.table
The table to query or change. Do not change.
void conn_delete(Sql.Sql db_conn, string|array where, void|string|array rest)
Like delete, but a database connection object is passed
explicitly instead of being retrieved via get_db.
mapping(string:mixed) conn_get(Sql.Sql db_conn, mixed id, void|array(string|zero) fields)
Like get, but a database connection object is passed explicitly
instead of being retrieved via get_db.
Result conn_get_multi(Sql.Sql db_conn, array(mixed) ids, void|array(string) fields)
Like get_multi, but a database connection object is passed
explicitly instead of being retrieved via get_db.
int conn_insert(Sql.Sql db_conn, mapping(string:mixed) ... records)
Like insert, but a database connection object is passed
explicitly instead of being retrieved via get_db.
int conn_insert_ignore(Sql.Sql db_conn, mapping(string:mixed) ... records)
Like insert_ignore, but a database connection object is passed
explicitly instead of being retrieved via get_db.
int conn_insert_or_update(Sql.Sql db_conn, mapping(string:mixed) record, void|int(0..2) clear_other_fields)
Like insert_or_update, but a database connection object is
passed explicitly instead of being retrieved via get_db.
void conn_remove(Sql.Sql db_conn, mixed id)
Like remove, but a database connection object is passed
explicitly instead of being retrieved via get_db.
void conn_remove_multi(Sql.Sql db_conn, array(mixed) ids)
Like remove_multi, but a database connection object is passed
explicitly instead of being retrieved via get_db.
int conn_replace(Sql.Sql db_conn, mapping(string:mixed) ... records)
Like replace, but a database connection object is passed
explicitly instead of being retrieved via get_db.
Result conn_select(Sql.Sql db_conn, string|array where, void|array(string) fields, void|string|array select_exprs, void|string table_refs, void|string|array rest, void|string select_flags)
Like select, but a database connection object is passed
explicitly instead of being retrieved via get_db.
array conn_select1(Sql.Sql db_conn, string|array select_expr, string|array where, void|string table_refs, void|string|array rest, void|string select_flags)
Like select1, but a database connection object is passed
explicitly instead of being retrieved via get_db.
void conn_update(Sql.Sql db_conn, mapping(string:mixed) record, void|int(0..2) clear_other_fields)
Like update, but a database connection object is passed
explicitly instead of being retrieved via get_db.
Mysql.SqlTable Mysql.SqlTable(function(void:Sql.Sql) get_db, string table, void|string prop_col)
Creates an SqlTable object for accessing (primarily) a specific
table.
get_dbA function that will be called to get a connection to the database containing the table.
tableThe name of the table.
prop_colThe column in which all fields which don't have explicit columns
are stored. It has to be a non-null blob or varbinary column. If
this isn't specified and there is such a column called
"properties" then it is used for this purpose. Set to "-"
to force this feature to be disabled.
void delete(string|array where, void|string|array rest)
Deletes records from the table that matches a condition.
Both where and rest may be given as arrays to use bindings
or sprintf-style formatting - see handle_argspec for
details.
whereThe match condition, on the form of a WHERE expression.
A WHERE clause will always be generated, but you can put e.g. "TRUE" in the match condition to select all records.
restOptional clauses that follows after the WHERE clause in a DELETE, i.e. ORDER BY and/or LIMIT.
remove
Add support for joins.
mapping(string:mixed)|zero get(mixed id, void|array(string) fields)
Returns the record matched by a primary key value, or zero if there is no such record.
idThe value for the primary key.
If the table has a multicolumn primary key then id must be an
array which has the values for the primary key columns in the same
order as pk_cols. Otherwise id is taken directly as the
value of the single primary key column.
fieldsThe fields to retrieve. All fields are retrieved if it's zero or left out.
For columns, the result mappings always have corresponding
entries. Other fields, i.e. properties, only occur in the result
mappings when they match fields in the prop_col column.
A 0 (zero) entry can be used in fields to return all
properties without filtering.
select, select1, get_multi
Result get_multi(array(mixed) ids, void|array(string) fields)
Retrieves multiple records selected by primary key values.
This function currently only works if the primary key is a single column.
idArray containing primary key values.
The number of returned records might be less than the number of
entries here if some of them don't match any record. Also, the
order of the returned records has no correlation to the order in
the id array.
fieldsThe fields to retrieve. All fields are retrieved if it's zero or left out.
For columns, the result mappings always have corresponding
entries. Other fields, i.e. properties, only occur in the result
mappings when they match fields in the prop_col column.
A 0 (zero) entry can be used in fields to return all
properties without filtering.
Returns a SqlTable.Result object from which the results can be
retrieved. Zero is never returned.
The result object implements an iterator, so it can be used
directly in e.g. a foreach.
get, select, select1
local string handle_argspec(Sql.Sql|zero db_conn, array argspec, mapping(string:mixed) bindings)
Helper function for use with array style arguments.
Many functions in this class can take WHERE expressions etc either
as plain strings or as arrays. In array form, they work like when
Sql.Sql.query is called with more than one argument:
The first element in the array is a string containing the SQL
snippet. If the second element is a mapping, it's taken as a
bindings mapping, otherwise the remaining elements are formatted
using the first in sprintf fashion. See Sql.Sql.query
for further details.
This function reduces an argument on array form to a simple
string, combined with bindings. bindings is a mapping that is
modified to contain the new bindings.
The quote function can be used to quote string literals in the
query, to avoid the array format.
Return the SQL snippet in string form, possibly with variable
bindings referring to bindings.
int insert(mapping(string:mixed) ... records)
Inserts one or more records into the table using an INSERT command. An SQL error is thrown if a record conflicts with an existing one.
A record is represented as a mapping with one entry for each
column or property (if prop_col is used). The values must be of
the right type for the column: Integers for integer columns,
floats for floating point columns, strings for all other data
types, and Val.null for the SQL NULL value.
If the property feature is used (i.e. if prop_col is set) then
any entries in the record mapping that don't match a column are
treated as properties and are stored encoded in the prop_col
column. Note that column names are matched with case sensitivity.
Properties may store any pike data type (as long as it is accepted
by encode_value_canonic).
If id_col is set and that column doesn't exist in a record
mapping then the field is added to the mapping with the value that
the record got. This currently only works for the first record
mapping if there are several.
The value of the id_col column for the new record. If several
records are inserted at once then the value for the first one is
returned. Zero is returned if there is no id_col column.
insert_ignore, replace, insert_or_update
int insert_ignore(mapping(string:mixed) ... records)
Inserts one or more records into the table using an INSERT IGNORE command. If some of the given records conflict with existing records then they are ignored.
Zero is returned if all records were ignored. The record mapping
is updated with the id_col record id only if a single record is
given. Otherwise this function behaves like insert.
insert, replace
int insert_or_update(mapping(string:mixed) record, void|int(0..2) clear_other_fields)
Insert a record into the table using an INSERT ... ON DUPLICATE
KEY UPDATE command: In case record conflicts with an existing
record then it is updated like the update function would do,
otherwise it is inserted like insert would do.
If id_col is set and that column doesn't exist in record
then the field is added to the mapping with the value that the
inserted or updated record got.
The value of the id_col column for the new or updated record.
Zero is returned if there is no id_col column.
This function isn't atomic if clear_other_fields is unset and
record contains fields which do not correspond to real columns,
i.e. if the prop_col column may need to be updated.
local string quote(string s)
Quotes a string literal for inclusion in an SQL statement, e.g. in
a WHERE clause to select.
Most functions here take raw string literals. Quoting is seldom necessary.
void remove(mixed id)
Removes the record matched by the primary key value in id.
Nothing happens if there is no such record.
If the table has a multicolumn primary key then id must be an
array which has the values for the primary key columns in the same
order as pk_cols. Otherwise id is taken directly as the
value of the single primary key column.
remove_multi, delete
void remove_multi(array(mixed) ids)
Removes multiple records selected by primary key values. It is not
an error if some of the ids elements don't match any records.
This function currently only works if the primary key is a single column.
remove
int replace(mapping(string:mixed) ... records)
Inserts one or more records into the table using a REPLACE command. If some of the given records conflict with existing records then they are replaced.
Otherwise this function behaves like insert.
insert, insert_ignore
Result select(string|array where, void|array(string) fields, void|string|array select_exprs, void|string table_refs, void|string|array rest, void|string select_flags)
Retrieves all records that matches a condition.
This function sends a SELECT statement, and it gives full expressive power to send any SELECT that is based on this table.
The only functionality this function adds over
Sql.big_typed_query is conversion of TIMESTAMP values (see the
class doc), and the (optional) handling of arbitrary properties in
addition to the SQL columns. fields may list such properties,
and they are returned alongside the real columns. Properties
cannot be used in WHERE expressions etc, though.
Joins with other tables are possible through table_refs, but
property columns in those tables aren't decoded.
whereThe match condition, on the form of a WHERE expression. It may be
given as an array to use bindings or sprintf-style
formatting - see handle_argspec for details.
A WHERE clause will always be generated, but you can put e.g. "TRUE" in the match condition to select all records.
fieldsThe fields to retrieve. All fields are retrieved if it's zero or left out.
For columns, the result mappings always have corresponding
entries. Other fields, i.e. properties, only occur in the result
mappings when they match fields in the prop_col column.
A 0 (zero) entry can be used in fields to return all
properties without filtering.
select_exprsOptional extra select expression besides the requested columns. This is just added to the list of selected columns, after a separating ",".
select_exprs may be given as an array to use bindings or
sprintf-style formatting - see handle_argspec for
details.
Note that expressions in select_exprs that produce TIMESTAMP
values are not converted to unix time integers - they are instead
returned as formatted date/time strings.
table_refsOptional other tables to join into the SELECT. This is inserted
between "FROM table" and "WHERE".
restOptional clauses that follows after the WHERE clause, e.g. ORDER
BY, GROUP BY, and LIMIT. It may be given as an array to use
bindings or sprintf-style formatting - see
handle_argspec for details.
select_flagsFlags for the SELECT statement. If this string is given, it is simply inserted directly after the "SELECT" keyword.
Returns a SqlTable.Result object from which the results can be
retrieved. Zero is never returned.
The result object implements an iterator, so it can be used
directly in e.g. a foreach.
quote may be used to quote string literals if the
sprintf-style formats aren't used.
select1, get, get_multi
array select1(string|array select_expr, string|array where, void|string table_refs, void|string|array rest, void|string select_flags)
Convenience variant of select for retrieving only a single
column. The return value is an array containing the values in the
select_expr column.
select_exprThe field to retrieve. It may name a column or a property, or it
may be a select expression like "SHA1(x)". It may be given as an
array to use bindings or sprintf-style formatting - see
handle_argspec for details.
whereThe match condition, on the form of a WHERE expression. A WHERE clause will always be generated, but you can put e.g. "TRUE" in the match condition to select all records.
where may be given as an array to use bindings or
sprintf-style formatting - see handle_argspec for
details.
table_refsOptional other tables to join into the SELECT. This is inserted
between "FROM table" and "WHERE".
restOptional clauses that follows after the WHERE clause, e.g. ORDER
BY, GROUP BY, and LIMIT. It may be given as an array to use
bindings or sprintf-style formatting - see
handle_argspec for details.
select_flagsFlags for the SELECT statement. If this string is given, it is simply inserted directly after the "SELECT" keyword.
Returns an array with the values in the selected column. If a
property is retrieved and some rows don't have the wanted property
then UNDEFINED is put into those elements.
select, get, get_multi
void update(mapping(string:mixed) record, void|int(0..2) clear_other_fields)
Updates an existing record. This requires a primary key and that
record contains values for all primary key columns. If
record doesn't correspond to any existing record then nothing
happens.
Updating a record normally means that all fields in record
override those stored in the table row, while all other fields
keep their values.
It's the same for properties (i.e. fields that don't correspond to
columns) which are stored in the prop_col column. If that
column needs to be updated then by default the old value is
fetched first, which means the update isn't atomic in that case. A
property can be removed altogether by giving it the value
Val.null in record.
If clear_other_fields is 1 then all old properties are replaced
by the new ones instead of merged with them, which avoids the
extra fetch. If clear_other_fields is 2 then additionally all
unmentioned columns are reset to their default values.
For more details about the record mapping, see insert.
insert_or_update
Result object returned by e.g. select. This is similar in
function to an Sql.sql_result object. It also implements the
iterator interface and can therefore be used directly in e.g.
foreach.
Sql.Result Mysql.SqlTable.Result.res
The underlying result object from the db connection.
Mysql.SqlTable.Result a;
foreach( a; index; value ) orprotected Iterator _get_iterator()
Returns an iterator for the result. Only one iterator may be
created per Result object.
array(mapping(string:mixed)) column_info()
Returns information about the columns in the result.
int eof()
Returns nonzero if there are no more rows.
mapping(string:mixed)|zero fetch()
Fetches the next record from the result and advance the cursor. Returns zero if there are no more records.
The record is returned as a mapping. It is similar to the
mappings returned by Sql.query, except that native pike types
and Val.null are used. If prop_col is used then properties
from that column can be returned as mapping entries alongside
the columns, and those values can be any pike data type.
As opposed to the Sql.query mappings, the returned mapping
has a single entry for each field - there are no duplicate
entries prefixed with the table name.
array(mapping(string:mixed)) get_array()
Returns all the remaining records as an array of mappings.
eof returns true after this.
This is not a cast since it destructively modifies this object by fetching all remaining records.
int num_rows()
Returns the number of rows in the result.
protected int Mysql.SqlTable.Result.Iterator.cached_num_rows
protected local void __create__(int cached_num_rows)
Mysql.SqlTable.Result.Iterator Mysql.SqlTable.Result.Iterator(int cached_num_rows)
Low level interface to the Mysql database.
This class enables access to the Mysql database from within Pike.
Mysql.mysql()->Result, Sql.Sql
inherit __builtin.Sql.Connection : Connection
string sprintf(string format, ... Mysql.mysql arg ... )
int affected_rows()
Returns the number of rows affected by the last query.
variant Result big_query(string query)
variant Result big_typed_query(string query)
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.
Mysql.mysql Mysql.mysql()
Mysql.mysql Mysql.mysql(string host)
Mysql.mysql Mysql.mysql(string host, string database)
Mysql.mysql Mysql.mysql(string host, string database, string user)
Mysql.mysql Mysql.mysql(string host, string database, string user, string password)
Mysql.mysql 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.
hostIf you give no argument, or give "" as host it will connect
with a UNIX-domain socket, which can be a big performance gain.
optionsThis optional mapping can contain zero or more of the following parameters:
| Command to execute on connect. |
| Timeout in seconds. |
| Enable compressed protocol. |
| Change config file from |
| Specify additional group to read from config file. |
| Use named pipe to connect to server. |
| Enable use of LOCAL INFILE (security). |
| Change charset directory. |
| Set connection charset - see |
| Enable unicode decode mode for the connection if nonzero. In
this mode non-binary string results are automatically
converted to (possibly wide) unicode strings. An error is
thrown if the server doesn't support this. See
|
| Path to SSL-key for use in SSL-communication. |
| Path to SSL-cert for use in SSL-communication. |
| Path to SSL-CA for use in SSL-communication. |
| Path to SSL-CAPATH for use in SSL-communication. |
| FIXME |
| Options used when connecting to the server. See mysql documentation for more information. |
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).
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()
int errno()
Returns an error code describing the last error from the Mysql-server.
Returns 0 (zero) if there was no error.
string error()
Returns a string describing the last error from the Mysql-server.
Returns 0 (zero) if there was no error.
string host_info()
Get information about the Mysql-server connection
statistics(), server_info(), protocol_info(), info()
string info()
Get information about the most recently executed statement.
statistics(), server_info(), protocol_info(), host_info()
int insert_id()
Returns the id of the last INSERT query into a table with an AUTO INCREMENT field.
int is_open()
Returns true if the connection seems to be open.
This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.
For a more reliable check of whether the connection
is alive, please use ping().
ping()
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
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 name of the field. |
| The name of the table. |
| The default value for the field. |
| The SQL type of the field. |
| The length of the longest possible value that can be stored in the field. Note that this measures the display length in string form. |
| Some flags. |
| The number of decimalplaces. |
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:
| This field is part of the primary key for this table. |
| This field is part of a unique key for this table. |
| This field is part of a nonunique key for this table. |
| This field cannot be NULL. |
| This field is a BLOB or TEXT. |
| This field has the AUTO_INCREMENT attribute. |
| This Field has the ZEROFILL attribute. |
| This Field has the BINARY attribute. |
| This Field is an ENUM. |
| This Field is a SET. |
| This Field has the UNSIGNED attribute. |
| This Field is numeric. |
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
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
int ping()
Check whether the connection is alive.
Returns one of the following:
| Everything ok. |
| The connection reconnected automatically. |
| The server has gone away, and the connection is dead. |
is_open()
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()
string|zero query_db()
Returns the currently selected database.
select_db()
void reload()
Reload security tables
This function causes the Mysql-server to reload its access tables.
shutdown()
void reset()
Reset connection state.
Currently this just releases all table locks.
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(), query_db()
string server_info()
Get the version number of the Mysql-server.
statistics(), host_info(), protocol_info(), info()
void shutdown()
Shutdown the Mysql-server
This function shuts down a running Mysql-server.
reload()
string sqlstate()
Returns the SQLSTATE error code describing the last error.
The value "000000" means 'no error'. The SQLSTATE error codes are
described in ANSI SQL.
string statistics()
Some Mysql-server statistics
This function returns some server statistics.
server_info(), host_info(), protocol_info(), info()
variant Result streaming_query(string query)
variant Result streaming_typed_query(string query)
Objects of this class contain the result from Mysql queries.
Mysql.mysql, Mysql.mysql->big_query()
inherit __builtin.Sql.Result : Result
Mysql.mysql.Result Mysql.mysql.Result(bool|void typed_mode)
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
bool 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()
string fetch_json_result()
Fetch all remaining rows and return them as JSON-encoded data.
fetch_row()
This function passes on string values without any charset
conversions. That means the result is correct JSON only if the
result charset is UTF-8 (which includes if unicode decode mode
is enabled - see set_unicode_decode_mode).
For many other charsets it is possible to do charset conversion afterwards on the result string, since all markup is in the ASCII range, which is typically invariant. However, that won't work if binary and text results are returned at the same time.
Also note that the characters U+2028 (LINE SEPARATOR) and U+2029
(PARAGRAPH SEPARATOR) are passed through without being converted
to \uxxxx escapes. Those two characters can cause trouble
with some Javascript based JSON parsers since they aren't
allowed in Javascript string literals. It is possible to use
replace on the returned string to escape them, though.
Standards.JSON.encode
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()
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()
void low_seek(int row)
Seek to the specified row.
In Pike 8.0 and earlier this function was named seek(), but
as it has a conflicting behavior vis-a-vis Sql.Result()->seek(),
and was shadowed by a function that did use relative row addressing,
it has been renamed in Pike 8.1.
fetch_row(), seek()
int num_fields()
Number of fields in the result.
num_rows()
int num_rows()
Number of rows in the result.
num_fields()
Implements the glue needed to access the Msql-module from the generic SQL module.
inherit Msql.msql : msql
This is an interface to the mSQL database server. This module may or may not be available on your Pike, depending whether the appropriate include and library files (msql.h and libmsql.a respectively) could be found at compile-time. Note that you do not need to have a mSQL 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 mSQL
server, you'd better use the Sql.Sql program instead. Using Sql.Sql
ensures that your Pike applications will run with any supported SQL
server without changing a single line of code.
Also notice that some functions may be mSQL/2.0-specific, and thus missing on hosts running mSQL/1.0.*
The mSQL C API has some extermal dependencies. They take the form of certain environment variables which, if defined in the environment of the pike interpreter, influence the interface's behavior. Those are "MSQL_TCP_PORT" which forces the server to connect to a port other than the default, "MSQL_UNIX_PORT", same as above, only referring to the UNIX domain sockets. If you built your mSQL server with the default setttings, you shouldn't worry about these. The variable MINERVA_DEBUG can be used to debug the mSQL API (you shouldn't worry about this either). Refer to the mSQL documentation for further details.
Also note that THIS MODULE USES BLOCKING I/O to connect to the server. mSQL should be reasonably fast, but you might want to consider this particular aspect. It is thread-safe, and so it can be used in a multithread environment.
Although it seems that mSQL/2.0 has some support for server statistics, it's really VERY VERY primitive, so it won't be added for now.
Sql.Sql
constant Msql.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.
inherit __builtin.Sql.Connection : Connection
int affected_rows()
This function returns how many rows in the database were affected by our last SQL query.
This function is available only if you're using mSQL version 2 or later. (That means: if the includes and library of version 2 of mSQL were available when the module was compiled).
This function is not part of the standard interface, so it is not
available through the Sql.Sql interface, but only through Sql.msql and
Msql.msql programs
variant Result big_query(string(8bit) sqlquery)
This is all you need to query the database. It takes as argument an SQL query string (e.g.: "SELECT foo,bar FROM baz WHERE name like '%kinkie%'" or "INSERT INTO baz VALUES ('kinkie','made','this')") and returns an object containing the returned values.
Errors (both from the interface and the SQL server) are reported via
exceptions, and you definitely want to catch them. Error messages are
not particularly verbose, since they account only for errors inside the
driver. To get server-related error messages, you have to use the
error function.
Note that if the query is NOT a of SELECT type, but UPDATE or
MODIFY, the returned value is a 0 (zero).
This is not an error. Errors are reported only via exceptions.
error
Msql.msql Msql.msql(void|string dbserver, void|string dbname, void|string username, void|string passwd)
With one argument, this function
tries to connect to the specified (use hostname or IP address) database
server. To connect to a server running on the local host via UNIX domain
sockets use "localhost". To connect to the local host via TCP/IP
sockets
you have to use the IP address "127.0.0.1".
With two arguments it also selects a database to use on the server.
With no arguments it tries to connect to the server on localhost, using
UNIX sockets.
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 msql object is destroyed.
select_db
void create_db(string dbname)
This function creates a new database with the given name (assuming we have enough permissions to do this).
drop_db
void drop_db(string dbname)
This function destroys a database and all the data it contains (assuming we have enough permissions to do so). USE WITH CAUTION!
create_db
string error(void|int clear)
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).
query
string host_info()
This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).
array(string) list_dbs(void|string wild)
Returns an array containing the names of all databases available on the system. Will throw an exception if there is no server connected. If an argument is specified, it will return only those databases whose name matches the given glob.
mapping(string:mapping(string:mixed)) list_fields(string table)
Returns a mapping describing the fields of a table in the database. The returned value is a mapping, indexed on the column name, of mappings. These contain currently the fields:
| Describes the field's mSQL data type ("char","integer",...) |
| It describes the field's length. It is only interesting for char() fields, in fact. Also notice that the strings returned by msql->query() have the correct length. This field only specifies the _maximum_ length a "char()" field can have. |
| The table this field is in. Added only for interface compliancy. |
| It's a multiset containing textual descriptions of the server's flags associated with the current field. Currently it can be empty, or contain "unique" or "not null". |
The version of this function in the Msql.msql() program is not
sql-interface compliant (this is the main reason why using that program
directly is deprecated). Use Sql.Sql instead.
query
array list_index(string tablename, string indexname)
This function returns an array describing the index structure for the given table and index name, as defined by the non-standard SQL query 'create index' (see the mSQL documentation for further informations). More than one index can be created for a table. There's currently NO way to have a listing of the indexes defined for a table (blame it on the mSQL API).
This function is available if you're using mSQL version 2 or later.
This function is not part of the standard interface, so it is not
available through the Sql.Sql interface, but only through Sql.msql and
Msql.msql programs.
array(string) list_tables(void|string wild)
Returns an array containing the names of all the tables in the currently selected database. Will throw an exception if we aren't connected to a database. If an argument is specified, it will return only those tables whose name matches the given glob.
void reload_acl()
This function forces a server to reload its ACLs.
This function is not part of the standard interface, so it is
not available through the Sql.Sql interface, but only through
Sql.msql and Msql.msql programs.
create
void select_db(string dbname)
Before querying a database you have to select it. This can be accomplished
in two ways: the first is calling the create function with two arguments,
another is calling it with one or no argument and then calling select_db.
You can also use this function to change the database you're querying,
as long as it is on the same server you are connected to.
This function CAN raise exceptions in case something goes wrong (for example: unexistant database, insufficient permissions, whatever).
create, error
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.
void shutdown()
This function shuts a SQL-server down.
inherit __builtin.Sql.Result : Result
This is an interface to the PostgreSQL database server. This module is independent of any external libraries. Note that you do not need to have a PostgreSQL server running on your host to use this module: you can connect to the database over a TCP/IP socket on a different host.
This module replaces the functionality of the older Sql.postgres
and Postgres.postgres modules.
This module supports the following features:
PostgreSQL network protocol version 3, authentication methods currently supported are: cleartext, md5 and scram (recommended).
Optional asynchronous query interface through callbacks.
Streaming queries which do not buffer the whole resultset in memory.
Automatic binary transfers to and from the database for most common datatypes (amongst others: integer, text and bytea types).
Automatic character set conversion and native wide string support. Supports UTF8/Unicode for multibyte characters, and all single-byte character sets supported by the database.
SQL-injection protection by allowing just one statement per query and ignoring anything after the first (unquoted) semicolon in the query.
COPY support for streaming up- and download.
Accurate error messages.
Automatic precompilation of complex queries (session cache).
Multiple simultaneous queries on the same database connection.
Cancelling of long running queries by force or by timeout.
Event driven NOTIFY.
SSL encrypted connections (optional or forced).
Check the PostgreSQL documentation for further details.
Multiple simultaneous queries on the same database connection are a feature that none of the other database drivers for Pike support. So, although it's efficient, its use will make switching database drivers difficult.
Sql.Connection, Sql.postgres,
https://www.postgresql.org/docs/current/static/
inherit __builtin.Sql.Connection : Connection
final variant .pgsql_util.Result big_query(string q, void|mapping(string|int:mixed) bindings, void|int _alltyped)
This is the only provided direct interface which allows you to query the
database. A simpler synchronous interface can be used through query().
Bindings are supported natively straight across the network. Special bindings supported are:
| Forces caching on or off for the query at hand. |
| Forces text mode in communication with the database for queries on or off for the query at hand. Potentially more efficient than the default binary method for simple queries with small or no result sets. Note that this mode causes all but the first query result of a list of semicolon separated statements to be discarded. |
| Forces synchronous parsing on or off for statements. Setting this to off can cause surprises because statements could be parsed before the previous statements have been executed (e.g. references to temporary tables created in the preceding statement), but it can speed up parsing due to increased parallelism. |
| Sets the debuglevel for query tracing. |
The parameters referenced via the bindings-parameter-mapping
passed to this function must remain unaltered
until the parameters have been sent to the database. The driver
currently does not expose this moment, but to avoid a race condition
it is sufficient to keep them unaltered until the first resultrow
has been fetched (or EOF is reached, in case of no resultrows).
A Sql.pgsql_util.Result object (which conforms to the
Sql.Result standard interface for accessing data). It is
recommended to use 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 fetches results on demand).
This function can raise exceptions.
This function supports multiple simultaneous queries (portals) on a single database connection. This is a feature not commonly supported by other database backends.
This function, by default, does not support multiple queries in one
querystring.
I.e. it allows for but does not require a trailing semicolon, but it
simply ignores any commands after the first unquoted semicolon. This can
be viewed as a limited protection against SQL-injection attacks.
To make it support multiple queries in one querystring, use the
:_text option (not recommended).
big_typed_query(), Sql.Connection, Sql.Result,
query(), Sql.pgsql_util.Result
final variant .pgsql_util.Result big_typed_query(string q, void|mapping(string|int:mixed) bindings)
This function returns an object that allows streaming and typed results.
big_query(), Sql.Connection, Sql.Result
final void cancelquery()
Cancels all currently running queries in this session.
reload(), resync()
This function is PostgreSQL-specific.
final void close()
Closes the connection to the database, any running queries are terminated instantly.
This function is PostgreSQL-specific.
Sql.pgsql Sql.pgsql(void|string host, void|string database, void|string user, void|string pass, void|mapping(string:mixed) options)
With no arguments, this function initialises a connection to the PostgreSQL backend. Since PostgreSQL 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 reason is because you don't have sufficient privileges to connect to that database. So use of this particular syntax is discouraged.
hostShould either contain "hostname" or
"hostname:portname". This allows you to specify the TCP/IP
port to connect to. If the parameter is 0 or "",
it will try to connect to localhost, default port.
databaseSpecifies the database to connect to. Not specifying this is
only supported if the PostgreSQL backend has a default database
configured. If you do not want to connect to any live database,
you can use "template1".
optionsCurrently supports at least the following:
| Set it to zero to disable automatic reconnects upon losing the connection to the database. Not setting it, or setting it to one, will cause one timed reconnect to take place. Setting it to -1 will cause the system to try and reconnect indefinitely. |
| If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will fallback to plain unencrypted. |
| If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will abort. |
| Send queries to and retrieve results from the database using text instead of the, generally more efficient, default native binary method. Turning this on will allow multiple statements per query separated by semicolons (not recommended). |
| Set it to zero to turn synchronous parsing off for statements. Setting this to off can cause surprises because statements could be parsed before the previous statements have been executed (e.g. references to temporary tables created in the preceding statement), but it can speed up parsing due to increased parallelism. |
| If set to one, it enables the automatic statement prepare and cache logic; caching prepared statements can be problematic when stored procedures and tables are redefined which leave stale references in the already cached prepared statements. The default is off, because PostgreSQL 10.1 (at least) has a bug that makes it spike to 100% CPU sometimes when this is on. |
| Character encoding for the client side, it defaults to using
the default encoding specified by the database, e.g.
|
| When on, backslashes in strings must not be escaped any longer,
|
| When on, a warning is issued if a backslash (\) appears in an
ordinary string literal and |
For the numerous other options please check the PostgreSQL manual.
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 to you.
It is possible that the exception from a failed connect will not be triggered on this call (because the connect proceeds asynchronously in the background), but on the first attempt to actually use the database instead.
Postgres.postgres, Sql.Connection, select_db(),
https://www.postgresql.org/docs/current/static/runtime-config-client.html
final void create_db(string db)
This function creates a new database (assuming we have sufficient privileges to do this).
dbName of the new database.
drop_db()
final void drop_db(string db)
This function destroys a database and all the data it contains (assuming
we have sufficient privileges to do so). It is not possible to delete
the database you're currently connected to. You can connect to database
"template1" to avoid connecting to any live database.
dbName of the database to be deleted.
create_db()
final string error(void|int clear)
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).
During the execution of a statement, this function accumulates all non-error messages (notices, warnings, etc.). If a statement does not generate any errors, this function will return all collected messages since the last statement.
The string returned is not newline-terminated.
clearTo clear the error, set it to 1.
big_query()
final string get_charset()
The PostgreSQL name for the current connection charset.
set_charset(), getruntimeparameters(),
https://www.postgresql.org/docs/current/static/multibyte.html
final mapping(string:string) getruntimeparameters()
Currently active runtimeparameters for the open session; these are initialised by the options parameter during session creation, and then processed and returned by the server. Common values are:
| Character encoding for the client side, e.g.
|
| Character encoding for the server side as determined when the
database was created, e.g. |
| Date parsing/display, e.g. |
| Default timezone used by the database, e.g. |
| When on, backslashes in strings must not be escaped any longer. |
| Displays the authorisationrole which the current session runs under. |
| Indicates if the current authorisationrole has database-superuser privileges. |
| Reports wether the database supports 64-bit-integer dates and times. |
| Shows the server version, e.g. |
The values can be changed during a session using SET commands to the database. For other runtimeparameters check the PostgreSQL documentation.
https://www.postgresql.org/docs/current/static/runtime-config-client.html
This function is PostgreSQL-specific.
final mapping(string:mixed) getstatistics()
A set of statistics for the current session:
| Number of warnings/notices generated by the database but not
collected by the application by using |
| Number of times the driver skipped asking the database to describe the statement parameters because it was already cached. Only available if PG_STATS is compile-time enabled. |
| Number of times prepared statements were used from cache instead of reparsing in the current session. Only available if PG_STATS is compile-time enabled. |
| Cache size of currently prepared statements. |
| Sum of the number hits on statements in the current statement cache. |
| Total number of prepared statements generated. |
| Total number of portals opened, i.e. number of statements issued to the database. Only available if PG_STATS is compile-time enabled. |
| Total number of bytes received from the database so far. |
| Total number of messages received from the database (one SQL-statement requires multiple messages to be exchanged). |
| Currently still open portals, i.e. running statements. |
This function is PostgreSQL-specific.
final string host_info()
This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX socket).
server_info()
final int is_open()
Returns true if the connection seems to be open.
This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.
For a more reliable check of whether the connection
is alive, please use ping() instead.
ping()
final array(string) list_dbs(void|string glob)
An array of the databases available on the server.
globIf specified, list only those databases matching it.
final array(mapping(string:mixed)) list_fields(void|string table, void|string glob)
A mapping, indexed on the column name, of mappings describing the attributes of a table of the current database. The currently defined fields are:
| Schema the table belongs to |
| Name of the table |
| Type of table |
| Tableowner |
| Estimated rowcount of the table |
| Estimated total datasize of the table in bytes |
| Estimated total indexsize of the table in bytes |
| Name of the column |
| A textual description of the internal (to the server) column type-name |
| The OID of the internal (to the server) column type |
| Size of the columndatatype |
| Default value for the column |
| If the table has any indices |
| |
| If the table has a primary key |
globIf specified, list only the tables with matching names.
Setting it to * will include system columns in the list.
final array(string) list_tables(void|string glob)
An array containing the names of all the tables and views in the path in the currently selected database.
globIf specified, list only the tables with matching names.
final int ping()
Check whether the connection is alive.
Returns one of the following:
| Everything ok. |
| The server has gone away, and the connection is dead. |
is_open()
final string quote(string s)
The given string, but escapes/quotes all contained magic characters according to the quoting rules of the current session for non-binary arguments in textual SQL-queries.
Quoting must not be done for parameters passed in bindings.
big_query(), quotebinary(), create()
final string quotebinary(string s)
The given string, but escapes/quotes all contained magic characters for binary (bytea) arguments in textual SQL-queries.
Quoting must not be done for parameters passed in bindings.
big_query(), quote()
This function is PostgreSQL-specific.
final void reload()
For PostgreSQL this function performs the same function as resync().
resync(), cancelquery()
final void resync()
Resyncs the database session; typically used to make sure the session is not still in a dangling transaction.
If called while the connection is in idle state, the function is lightweight and briefly touches base with the database server to make sure client and server are in sync.
If issued while inside a transaction, it will rollback the transaction, close all open cursors, drop all temporary tables and reset all session variables to their default values.
This function can raise exceptions.
cancelquery(), reload()
This function is PostgreSQL-specific.
final void select_db(string dbname)
Due to restrictions of the Postgres frontend-backend protocol, you always already have to be connected to a database. To connect to a different database you have to select the right database while connecting instead. This function is a no-op when specifying the same database, and throws an error otherwise.
create()
final string server_info()
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.
host_info()
final void set_charset(string charset)
Changes the connection charset. When set to "UTF8", the query,
parameters and results can be Pike-native wide strings.
charsetA PostgreSQL charset name.
get_charset(), create(),
https://www.postgresql.org/docs/current/static/multibyte.html
final void set_notify_callback(string condition, void|function(int, string, string, mixed ... :void) notify_cb, void|int selfnotify, mixed ... args)
With PostgreSQL you can LISTEN to NOTIFY events. This function allows you to detect and handle such events.
conditionName of the notification event we're listening
to. A special case is the empty string, which matches all events,
and can be used as fallback function which is called only when the
specific condition is not handled. Another special case is
"_lost" which gets called whenever the connection
to the database unexpectedly drops.
notify_cbFunction to be called on receiving a notification-event of
condition condition.
The callback function is invoked with
void notify_cb(pid,condition,extrainfo, .. args);
pid is the process id of the database session that originated
the event. condition contains the current condition.
extrainfo contains optional extra information specified by
the database.
The rest of the arguments to notify_cb are passed
verbatim from args.
The callback function must return no value.
selfnotifyNormally notify events generated by your own session are ignored.
If you want to receive those as well, set selfnotify to one.
argsExtra arguments to pass to notify_cb.
This function is PostgreSQL-specific.
final int setcachedepth(void|int newdepth)
newdepthSets the new cachedepth for automatic caching of prepared statements.
The previous cachedepth.
This function is PostgreSQL-specific.
final int setfetchlimit(void|int newfetchlimit)
newfetchlimitSets the new fetchlimit to interleave queries.
The previous fetchlimit.
This function is PostgreSQL-specific.
final int setportalbuffersize(void|int newportalbuffersize)
newportalbuffersizeSets the new portalbuffersize for buffering partially concurrent queries.
The previous portalbuffersize.
This function is PostgreSQL-specific.
final int settimeout(void|int newtimeout)
newtimeoutSets the new timeout for long running queries.
The previous timeout.
This function is PostgreSQL-specific.
final string status_commit()
The current commitstatus of the connection. Returns either one of:
|
|
|
This function is PostgreSQL-specific.
final variant .pgsql_util.Result streaming_query(string q, void|mapping(string|int:mixed) bindings)
This is an alias for big_query(), since big_query() already supports
streaming of multiple simultaneous queries through the same connection.
big_query(), big_typed_query(), streaming_typed_query(),
Sql.Connection, Sql.Result
final variant .pgsql_util.Result streaming_typed_query(string q, void|mapping(string|int:mixed) bindings)
This function returns an object that allows streaming and typed results.
big_query(), Sql.Connection, Sql.Result
Implements SQL-urls for pgsqls://[user[:password]@][hostname][:port][/database]
Sets the connection to SSL-mode, otherwise identical to Sql.pgsql.
Sql.pgsql, Sql.Sql
inherit Sql.pgsql : pgsql
This is an interface to the Postgres (Postgres95, PostgreSQL) database server. This module may or may not be available on your Pike, depending on whether or not 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.
This driver has been deprecated. You should use the
more advanced driver Sql.pgsql to access PostgreSQL databases instead.
Also note that this module uses blocking I/O to connect to the server, but it is thread-safe, and so it can be used in a multithreaded environment.
The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the Pike interpreter; some notice and warning notifications might are dumped on stderr.
| Sets the name of the default host to connect to. It defaults
to |
| Set the numeric IP address to connect to. This may be set instead of or in addition to PGHOST to avoid DNS lookups. |
| Sets the default port or unix domain socket file extension to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver). |
| Sets the default database to connect to. |
| Sets the default username used to connect to the database. |
| Set the default password used to connect to the database. This is not recommended for security reasons, some operating systems allow non-root users to see process environment variables. Use your ~/.pgpass file instead. |
| Sets the service name to be looked up in pg_service.conf. This is a shorter way to set all the parameters. |
| This determines how SSL connections will be negotiated. If set to disable, it will require an unencrypted connection; allow will negotiate a non-SSL connection, and if it fails try an SSL connection; prefer will attempt SSL connections first, falling back to non-SSL if SSL fails; and require will force an SSL connection or cause an error if SSL is not available. |
| Sets some extra flags for the frontend-backend connection. do not set unless you're sure of what you're doing. |
| Sets the Kerberos realm for authentication, if it is different from the local realm. PostgreSQL will attempt to authenticate with servers for this realm and use seperate ticket files to avoid conflicts with local ticket files. This variable is only used if Keberos authentication is selected in PostgreSQL. |
| Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing. This variable is ignored in recent versions of PostgreSQL. |
Refer to the Postgres documentation for further details.
Sql.pgsql, Sql.Sql, Postgres.postgres, Sql.postgres_result
inherit Postgres.postgres : mo
inherit Sql.pgsql : pgsql
Maps SQL-urls for postgres://[user[:password]@][hostname][:port][/database] onto pgsql://[user[:password]@][hostname][:port][/database]
This only happens if Pike was compiled without libpq
support, therefore Pike falls back to the faster, smaller memory
footprint, more advanced and native PostgreSQL driver called Sql.pgsql.
Sql.pgsql, Sql.Sql
string Sql.postgres.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.
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 Sql.Sql()->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 fetches results on demand).
This function can raise exceptions.
Sql.Sql, Sql.sql_result
Sql.postgres Sql.postgres()
Sql.postgres Sql.postgres(string host, void|string database, void|string user, void|string password, void|mapping options)
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.
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.pgsql, Postgres.postgres, Sql.Sql, postgres->select_db
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 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).
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(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:
| |
| |
| The textual representation of a Postgres uid. |
| |
| A textual description of the internal (to the server) type-name |
| |
| The "relexpires" attribute for the table. Obsolescent; modern versions of Postgres don't seem to use this feature, so don't count on this field to contain any useful value. |
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.
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 available through the generic SQL-interface.
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 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.
void set_notify_callback()
void set_notify_callback(function(:void) f)
void set_notify_callback(function(:void) 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 occurred. 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 available through the generic SQL-interface.
An integer can be passed as first argument, but it's effect is not documented.
int|object streaming_query(object|string q, mapping(string|int:mixed)|void bindings)
This is an alias for big_query(), since big_query() already supports
streaming.
big_query, Sql.Sql, Sql.sql_result
This is an interface to the Postgres (Postgres95, PostgreSQL) database server using libpq.
This module may or may not be available on your Pike, depending on whether or not 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 available
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
This driver is based on libpq and is DEPRECATED. There is a newer driver
called Sql.pgsql which is faster and more robust than this driver
and does not depend on any libraries.
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.
The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the pike interpreter.
| Sets the name of the default host to connect to. It defaults
to |
| Set the numeric IP address to connect to. This may be set instead of or in addition to PGHOST to avoid DNS lookups. |
| Sets the default port or unix domain socket file extension to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver). |
| Sets the default database to connect to. |
| Sets the default username used to connect to the database. |
| Set the default password used to connect to the database. This is not recommended for security reasons, some operating systems allow non-root users to see process environment variables. Use your ~/.pgpass file instead. |
| Sets the service name to be looked up in pg_service.conf. This is a shorter way to set all the parameters. |
| This determines how SSL connections will be negotiated. If set to disable, it will require an unencrypted connection; allow will negotiate a non-SSL connection, and if it fails try an SSL connection; prefer will attempt SSL connections first, falling back to non-SSL if SSL fails; and require will force an SSL connection or cause an error if SSL is not available. |
| Sets some extra flags for the frontend-backend connection. do not set unless you're sure of what you're doing. |
| Sets the Kerberos realm for authentication, if it is different from the local realm. PostgreSQL will attempt to authenticate with servers for this realm and use seperate ticket files to avoid conflicts with local ticket files. This variable is only used if Keberos authentication is selected in PostgreSQL. |
| Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing. This variable is ignored in recent versions of PostgreSQL. |
Refer to the libpq documentation for further details.
Sql.Sql, Sql.postgres, Sql.postgres_result
string Postgres.postgres.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.
string _quote(string s)
Escape a string to prevent SQL injection, using the current connection's character encoding settings.
void _set_notify_callback()
void _set_notify_callback(function(:void) 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 occurred. 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 available through the generic SQL-interface
Sql.postgres
int affected_rows()
This function returns the number of rows affected by the last query.
Sql.postgres_result big_query(string sqlquery)
Sql.postgres_result big_query(string sqlquery, array 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.
The program Sql.postgres_result is a superset of
the raw Postgres.postgres_result which does not do chunking to
avoid excessive memory usage in case of large resultsets.
Sql.Sql, Sql.sql_result
Postgres.postgres Postgres.postgres()
Postgres.postgres 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.
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
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 available through the generic SQL-interface.
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
Contains the result of a Postgres-query.
Sql.postgres, Postgres.postgres, Sql.Sql, Sql.sql_result
Postgres.postgres_result 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.
array(mapping(string:mixed)) fetch_fields()
Returns an array with an entry for each field, each entry is a mapping with the following fields:
| Name of the column |
| The type ID of the field. This is the database's internal representation type ID. |
| Can be an integer (the size of the contents in bytes) or the word "variable". |
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.
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()
int num_fields()
Returns the number of fields in the result.
int num_rows()
Returns the number of rows in the result.
void seek(int howmuch)
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.
string library_version()
Returns the version of the sqlite library used.
int(0..) library_version_number()
Returns the version of the sqlite library used as an integer.
Low-level interface to SQLite3 databases.
This class should typically not be accessed directly, but instead
via Sql.Sql() with the scheme "sqlite://".
inherit __builtin.Sql.Connection : Connection
Result big_query(string query, mapping(string|int:mixed)|void bindings)
Perform a streaming query against a SQLite database.
In Pike 8.0 and earlier this function behaved as big_typed_query().
Sql.Sql()->big_query(), big_typed_query()
TypedResult big_typed_query(string query, mapping(string|int:mixed)|void bindings)
Perform a streaming typed query against a SQLite database.
This was the behavior of big_query() in Pike 8.0 and earlier.
Sql.Sql()->big_typed_query(), big_query()
int changes()
Get the number of changes.
Document this function properly.
SQLite.SQLite SQLite.SQLite(string path, mixed ... ignored)
Open the SQLite database stored at path.
void create_db(string db)
This operation is not supported for SQLite.
Sql.Sql()->create_db()
void drop_db(string db)
This operation is not supported for SQLite.
Sql.Sql()->drop_db()
string error(void|int clear)
Get the latest error message.
Sql.Sql()->error()
int insert_id()
Returns the value of the ROWID (aka OID, aka _ROWID_,
or declared INTEGER PRIMARY KEY) column for the most recent
successful INSERT operation, or 0 (zero) if no INSERT
operations have been performed on the connection yet.
void interrupt()
Document this function.
array(string) list_dbs()
This operation is not supported for SQLite.
Sql.Sql()->list_dbs()
array|int query(string query, mapping(string|int:mixed)|void bindings)
Perform a query against a SQLite database.
In Pike 8.0 and earlier this function behaved as typed_query().
Sql.Sql()->query(), typed_query()
void select_db(string db)
This operation is not supported for SQLite.
Sql.Sql()->select_db()
string server_info()
Get information about the SQLite library version.
Sql.Sql()->server_info()
int total_changes()
Get the total number of changes for this session.
Document this function properly.
array|int typed_query(string query, mapping(string|int:mixed)|void bindings)
Perform a typed_query against a SQLite database.
This was the behavior of query() in Pike 8.0 and earlier.
Sql.Sql()->query(), query()
Result object from big_query().
inherit TypedResult : TypedResult
array fetch_row()
Sql.sql_result()->fetch_row()
Result object from typed_big_query().
inherit __builtin.Sql.Result : Result
int eof()
Sql.sql_result()->eof()
array(mapping(string:mixed)) fetch_fields()
Sql.sql_result()->fetch_fields()
array fetch_row()
Sql.sql_result()->fetch_row()
int num_fields()
Sql.sql_result()->num_fields()
int num_rows()
This API is not supported for Sql.sqlite.
Sql.sql_result()->num_rows()
void seek(int skip)
Sql.sql_result()->seek()
Pike interface to Oracle databases.
Original design by Marcus Comstedt.
Re-written for Oracle 8.x and later by Fredrik Hubinette.
Re-structured into a CMOD by Henrik Grubbström.
Connection to an Oracle database server.
You probably don't want to access this class directly, but
rather via Sql.Sql.
Sql.Sql, Sql.oracle
inherit __builtin.Sql.Connection : Connection
Large OBject.
inherit Val.Null : Null
constant Oracle.oracle.NULL.is_oracle_null
inherit __builtin.Sql.Result : Result
inherit Odbc.odbc : odbc
inherit ::this_program : this_program
mixed Sql.odbc.TypedResult._null_value
Value to use to represent NULL.
function(string(8bit), mapping(string:mixed), int:mixed) Sql.odbc.TypedResult.user_defined_cb
Function called by user_defined_factory() to create values for
custom types.
Gmp.mpq|int scale_numeric(int mantissa, int scale)
Helper function that scales mantissa by a
factor 10->pow(scale).
Returns an Gmp.mpq object if scale is negative,
and otherwise an integer (bignum).
TOD time_factory(int hour, int minute, int second, int|void nanos)
Function called to create time of day objects.
The default implementation just passes along its
arguments to TOD.
Calendar.ISO.Day|Calendar.ISO.Fraction timestamp_factory(int year, int month, int day, int|void hour, int|void minute, int|void second, int|void nanos, int|void tz_hour, int|void tz_minute)
Function called to create timestamp and date objects.
The tz_hour and tz_minute arguments are currently
neither generated by the low-level code, nor used by
the current implementation of the function.
mixed user_defined_factory(string(8bit) raw, mapping(string:mixed) field_info, int field_number)
Function called to create representations of user-defined types.
The default implementation just calls user_defined_cb if
it has been set, and otherwise returns raw.
Standards.UUID.UUID uuid_factory(string(8bit) raw_uuid)
Function called to create UUID/GUID objects.
Time of day.
int Sql.odbc.TypedResult.TOD.hour
int Sql.odbc.TypedResult.TOD.minute
int Sql.odbc.TypedResult.TOD.second
int|void Sql.odbc.TypedResult.TOD.nanos
protected local void __create__(int hour, int minute, int second, int|void nanos)
Sql.odbc.TypedResult.TOD Sql.odbc.TypedResult.TOD(int hour, int minute, int second, int|void nanos)
Low-level interface to Open DataBase Connectivity SQL-drivers.
You typically don't want to access this module directly, but
instead use Sql.Sql() with an "odbc://" or
"dsn://" URL.
Sql.Sql()
bool connect_lock(void|int enable)
Enable or disable a mutex that serializes all ODBC SQLConnect calls (i.e. when ODBC connections are created). This lock might be necessary to work around bugs in ODBC drivers.
enableEnables the mutex if nonzero, disables it otherwise. The state is not changed if this argument is left out.
The old state of the flag.
This is currently enabled by default due to bugs in the current FreeTDS library (version 0.63), but that might change if the demand for this kludge ceases in the future. Therefore, if this setting is important to you then always set it explicitly. Hopefully most users don't need to bother with it.
array(string) list_dbs()
List the configured ODBC database sources.
Low-level connection to an ODBC or DSN database.
You typically don't want to access this module directly, but
instead use the Sql.odbc or Sql.dsn created by Sql.Sql().
Sql.odbc, Sql.dsn
inherit __builtin.Sql.Connection : Connection
inherit __builtin.Sql.Result : Result
Odbc.odbc.Result Odbc.odbc.Result()
int eof()
int execute(string(16bit) query)
array(int|mapping(string:mixed)) fetch_fields()
int|array(string|float|int) fetch_row()
int list_tables(string|void table_name_pattern)
this_program next_result()
int num_fields()
int num_rows()
void seek()
inherit Result : Result
int|array(string|float|int|object) fetch_row()
Gmp.mpq|int scale_numeric(int mantissa, int scale)
Function called to scale mantissa by a
factor 10->pow(scale).
TOD time_factory(int hour, int minute, int second, int|void nanos)
Function called to create time of day objects.
Calendar.ISO.Day|Calendar.ISO.Fraction timestamp_factory(int year, int month, int day, int|void hour, int|void minute, int|void second, int|void nanos, int|void tz_hour, int|void tz_minute)
Function called to create timestamp and date objects.
The tz_hour and tz_minute arguments are currently
not generated by the low-level code.
mixed user_defined_factory(string(8bit) raw, mapping(string:mixed) field_info, int field_number)
Function called to create representations of user-defined types.
Standards.UUID.UUID uuid_factory(string(8bit) raw_uuid)
Function called to create UUID/GUID objects.
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 * ","; }
constant Sql.QUERY_OPTION_CHARSET
Field to set in the query bindings mapping to set a character set for just the current query. Only supported by some databases.
Val.Null Sql.NULL
The SQL NULL value.
Replaced by Val.null.
Val.null
string censor_sql_url(string sql_url)
Redact the password (if any) from an Sql-url.
sql_urlSql-url possibly containing an unredacted password.
Returns the same Sql-url but with the password (if any)
replaced by the string "CENSORED".
The result from Promise.
inherit __builtin.Sql.FutureResult : FutureResult
Class used to implement the SQL NULL value.
Replaced by Val.Null.
Val.Null, Val.null
The result from Connection.promise_query().
inherit __builtin.Sql.Promise : Promise
Replaced by Result.
Replaced by Result.
The NULL Sql handler.
This is an empty Sql handler typically used to test other functionality of the Sql module.
inherit __builtin.Sql.Connection : Connection
variant Sql.Result big_query(string query)
Returns an array with a single element:
| The query string before formating. |
| The query string before bindings having been inserted. |
| The formatted query. |
variant Sql.Result big_query(string query, mapping bindings, mixed ... extras)
Returns an array with a single element:
| The query string before formating. |
| The query string before bindings having been inserted. |
|
|
| The formatted query. |
variant Sql.Result big_query(string query, string|int|float|object extraarg, string|int|float|object|mapping ... extraargs)
Returns an array with a single element:
| The query string before formating. |
| The query string before bindings having been inserted. |
|
|
| The formatted query. |
|
|
string quote(string s)
sprintf("quote(%q)", s)
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
Implements the generic result base class of the SQL-interface. Used for return results from SQL.sql->big_query().
You typically don't get a direct clone of this class,
but of a class that inherits it, like sql_array_result
or sql_object_result.
inherit __builtin.Sql.Result : Result
array|__builtin.Sql.Result Sql.sql_result.master_res
The actual result.
string fetch_json_result()
Fetch remaining result as JSON-encoded data.
void seek(int skip)
Skip past a number of rows.
skipNumber of rows to skip.
Interface to SQLite3 databases.
inherit SQLite.SQLite : SQLite
array list_fields(string n, string|void wild)
array list_tables(string|void n)
The TDS SQL-protocol.
This protocol is used by Sybase and Microsoft's SQL-servers.
Sql.Sql con = Sql.Sql("tds://user:pass@host/database");
Sql.Sql()
Sql.tds Sql.tds(string|void server, string|void database, string|void user, string|void password, mapping|void options)
Connect to a remote SQL server via the TDS protocol.
serverServer to connect to.
databaseDatabase to connect to.
userUser to access as.
An explicit domain may be specified by preceeding the user name
with the domain name and a '\\'.
passwordPassword to access with.
Usually accessed via Sql.Sql().
Sql.Sql()
string error()
Return the last error (or possibly the last warning or informational message).
int insert_id()
Fetch the identity of the last insert (if available).
This performs the query "SELECT @@identity AS insert_id".
Returns the identity of the last insert as an integer if available.
Otherwise returns 0 (zero).
string server_info()
Return a string describing the server.
protected void tds_error(string msg, mixed ... args)
Format and report an error.
Environment types.
Used by TDS_ENV_CHANGE_TOKEN
constant Sql.tds.TDS_ENV_DATABASE
constant Sql.tds.TDS_ENV_LANG
constant Sql.tds.TDS_ENV_CHARSET
constant Sql.tds.TDS_ENV_PACKSIZE
constant Sql.tds.TDS_ENV_LCID
constant Sql.tds.TDS_ENV_SQLCOLLATION
Field types.
constant Sql.tds.SYBBINARY
constant Sql.tds.SYBBIT
constant Sql.tds.SYBBITN
constant Sql.tds.SYBCHAR
constant Sql.tds.SYBDATETIME
constant Sql.tds.SYBDATETIME4
constant Sql.tds.SYBDATETIMN
constant Sql.tds.SYBDECIMAL
constant Sql.tds.SYBFLT8
constant Sql.tds.SYBFLTN
constant Sql.tds.SYBIMAGE
constant Sql.tds.SYBINT1
constant Sql.tds.SYBINT2
constant Sql.tds.SYBINT4
constant Sql.tds.SYBINT8
constant Sql.tds.SYBINTN
constant Sql.tds.SYBLONGBINARY
constant Sql.tds.SYBMONEY
constant Sql.tds.SYBMONEY4
constant Sql.tds.SYBMONEYN
constant Sql.tds.SYBNTEXT
constant Sql.tds.SYBNUMERIC
constant Sql.tds.SYBNVARCHAR
constant Sql.tds.SYBREAL
constant Sql.tds.SYBSINT1
constant Sql.tds.SYBTEXT
constant Sql.tds.SYBUINT2
constant Sql.tds.SYBUINT4
constant Sql.tds.SYBUINT8
constant Sql.tds.SYBUNIQUE
constant Sql.tds.SYBVARBINARY
constant Sql.tds.SYBVARCHAR
constant Sql.tds.SYBVARIANT
constant Sql.tds.SYBVOID
constant Sql.tds.XSYBBINARY
constant Sql.tds.XSYBCHAR
constant Sql.tds.XSYBNCHAR
constant Sql.tds.XSYBNVARCHAR
constant Sql.tds.XSYBVARBINARY
constant Sql.tds.XSYBVARCHAR
constant Sql.tds.TDS_UT_TIMESTAMP
Tokens that may occur in the answers from the server.
constant Sql.tds.TDS5_PARAMFMT2_TOKEN
constant Sql.tds.TDS_LANGUAGE_TOKEN
constant Sql.tds.TDS_ORDERBY2_TOKEN
constant Sql.tds.TDS_ROWFMT2_TOKEN
constant Sql.tds.TDS_LOGOUT_TOKEN
constant Sql.tds.TDS_RETURNSTATUS_TOKEN
constant Sql.tds.TDS_PROCID_TOKEN
constant Sql.tds.TDS7_RESULT_TOKEN
constant Sql.tds.TDS7_COMPUTE_RESULT_TOKEN
constant Sql.tds.TDS_COLNAME_TOKEN
constant Sql.tds.TDS_COLFMT_TOKEN
constant Sql.tds.TDS_DYNAMIC2_TOKEN
constant Sql.tds.TDS_TABNAME_TOKEN
constant Sql.tds.TDS_COLINFO_TOKEN
constant Sql.tds.TDS_OPTIONCMD_TOKEN
constant Sql.tds.TDS_COMPUTE_NAMES_TOKEN
constant Sql.tds.TDS_COMPUTE_RESULT_TOKEN
constant Sql.tds.TDS_ORDERBY_TOKEN
constant Sql.tds.TDS_ERROR_TOKEN
constant Sql.tds.TDS_INFO_TOKEN
constant Sql.tds.TDS_PARAM_TOKEN
constant Sql.tds.TDS_LOGINACK_TOKEN
constant Sql.tds.TDS_CONTROL_TOKEN
constant Sql.tds.TDS_ROW_TOKEN
constant Sql.tds.TDS_CMP_ROW_TOKEN
constant Sql.tds.TDS5_PARAMS_TOKEN
constant Sql.tds.TDS_CAPABILITY_TOKEN
constant Sql.tds.TDS_ENVCHANGE_TOKEN
constant Sql.tds.TDS_EED_TOKEN
constant Sql.tds.TDS_DBRPC_TOKEN
constant Sql.tds.TDS5_DYNAMIC_TOKEN
constant Sql.tds.TDS5_PARAMFMT_TOKEN
constant Sql.tds.TDS_AUTH_TOKEN
constant Sql.tds.TDS_RESULT_TOKEN
constant Sql.tds.TDS_DONE_TOKEN
constant Sql.tds.TDS_DONEPROC_TOKEN
constant Sql.tds.TDS_DONEINPROC_TOKEN
constant Sql.tds.TDS_CURCLOSE_TOKEN
constant Sql.tds.TDS_CURFETCH_TOKEN
constant Sql.tds.TDS_CURINFO_TOKEN
constant Sql.tds.TDS_CUROPEN_TOKEN
constant Sql.tds.TDS_CURDECLARE_TOKEN
constant Sql.tds.TDS_ERROR
constant Sql.tds.TDS_DONT_RETURN
A connection to a TDS server.
Stdio.File Sql.tds.Connection.socket
The actual TCP connection.
InPacket|zero send_packet(Packet p, int flag, int|void last)
Send a packet to the TDS server.
May only be called when the connection is idle.
If last is true an InPacket with the result
will be returned.
An incoming packet from the TDS server.
An outgoing packet to the TDS server.
A query result set.
Sql.tds.big_query Sql.tds.big_query(string|compile_query query)
Execute a query against the database.
queryThe query to execute. This can either be a string, or a compiled query.
compile_query()
array(mapping(string:mixed)) fetch_fields()
Fetch a description of the fields in the result.
Returns an array with a mapping for each of the fields in the result.
The mappings contain the following information:
Standard fields:
| The name of the field. |
| The name of the table (if available). |
TDS-specific fields:
|
|
|
|
|
|
| Width of the field. |
| Time stamp information for last change is available. |
| Precision of the field. |
| Scale exponent of the field. |
| Internal use only. |
| |
| |
| |
|
int|array(string|int) fetch_row()
Fetch the next row from the result set.
Returns 0 (zero) if all rows have been returned.
Otherwise returns an array(string|int) with one
entry for each field. If the field is NULL the
entry will be 0 (zero), otherwise the entry
will contain a string representing the value.
A compiled query.
Sql.tds.compile_query Sql.tds.compile_query(string query)
Compile a query.
big_query()
The pgsql backend, shared between all connection instances. It runs even in non-callback mode in a separate thread and makes sure that communication with the database is real-time and event driven at all times.
Callbacks running from this backend directly determine the latency in reacting to communication with the database server; so it would be prudent not to block in these callbacks.
final Pike.Backend Sql.pgsql_util.local_backend
The instance of the pgsql dedicated backend.
final void register_backend(proxy client)
Registers yourself as a user of this backend. If the backend has not been started yet, it will be spawned automatically.
The result object returned by Sql.pgsql()->big_query(), except for
the noted differences it behaves the same as Sql.Result.
Sql.Result, Sql.pgsql, Sql.Sql, Sql.pgsql()->big_query()
inherit __builtin.Sql.Result : Result
final int affected_rows()
Returns the number of affected rows by this query.
Sql.Result()->affected_rows()
final int eof()
Sql.Result()->eof()
final array(mapping(string:mixed)) fetch_fields()
Sql.Result()->fetch_fields()
final array(mixed)|zero fetch_row()
One result row at a time.
When using COPY FROM STDOUT, this method returns one row at a time as a single string containing the entire row.
eof(), send_row()
final array(array(mixed))|zero fetch_row_array()
Multiple result rows at a time (at least one).
When using COPY FROM STDOUT, this method returns one row at a time as a single string containing the entire row.
eof(), fetch_row()
final int num_fields()
Sql.Result()->num_fields()
final int num_rows()
This method returns the number of rows already received from the database for the current query. Note that this number can still increase between subsequent calls if the results from the query are not complete yet. This function is only guaranteed to return the correct count after EOF has been reached.
Sql.Result()->num_rows()
final void send_row(void|string|array(string) copydata)
copydataWhen using COPY FROM STDIN, this method accepts a string or an array of strings to be processed by the COPY command; when sending the amount of data sent per call does not have to hit row or column boundaries.
The COPY FROM STDIN sequence needs to be completed by either explicitly or implicitly destroying the result object, or by passing no argument to this method.
fetch_row(), eof()
final void set_result_array_callback(function(Result, array(array(mixed)), mixed ... :void) callback, mixed ... args)
Sets up a callback for sets of rows returned from the database. First argument passed is the resultobject itself, second argument is the array of result rows (zero on EOF).
fetch_row()
final void set_result_callback(function(Result, array(mixed), mixed ... :void) callback, mixed ... args)
Sets up a callback for every row returned from the database. First argument passed is the resultobject itself, second argument is the result row (zero on EOF).
fetch_row()
final string status_command_complete()
Returns the command-complete status for this query.
Sql.Result()->status_command_complete()
Replaced by Result.
Some SQL utility functions
void fallback()
Throw an error in case an unimplemented function is called.
string quote(string s)
Quote a string so that it can safely be put in a query.
sString to quote.
This one is used to get a buggy unicode support when compiled with
an old MySQL client lib that doesn't have the charsetnr property in
the field info. It looks at the binary flag instead, which is set
for binary fields but might also be set for text fields (e.g. with
a definition like "VARCHAR(255) BINARY").
I.e. the effect of using this one is that text fields with the binary flag won't be correctly decoded in unicode decode mode.
This has to be enabled either by passing "broken-unicode" as
charset to Sql.mysql.create or Sql.mysql.set_charset, by calling
Sql.mysql.set_unicode_decode_mode(-1), or by defining the
environment variable PIKE_BROKEN_MYSQL_UNICODE_MODE. That will
cause this buggy variant to be used if and only if the MySQL client
lib doesn't support the charsetnr property.
inherit UnicodeWrapper : UnicodeWrapper
Result wrapper for MySQL that performs UTF-8 decoding of all nonbinary fields. Useful if the result charset of the connection has been set to UTF-8.
There's normally no need to use this class directly. It's used
automatically when mysql.set_unicode_decode_mode is activated.
inherit UnicodeWrapper : UnicodeWrapper
Result object wrapper performing utf8 decoding of all fields.
inherit Sql.Result : Result
protected array(int|mapping(string:mixed)) Sql.sql_util.UnicodeWrapper.field_info
Cached fetch_fields() result.
protected object Sql.sql_util.UnicodeWrapper.master_result
protected local void __create__(object master_result)
Sql.sql_util.UnicodeWrapper Sql.sql_util.UnicodeWrapper(object master_result)
bool eof()
Returns 1 if there are no more rows in the 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:
| The name of the field. Always present. |
| The table the field is from. Not present from all databases. |
| The default value for the column. Not available from all databases. |
int|string fetch_json_result()
JSON is always utf8 default, do nothing.
int|array(string) fetch_row()
Fetch the next row from the result.
All strings in the result are decoded from UTF8.
int num_fields()
Returns the number of fields in the result.
int num_rows()
Returns the number of rows in the result.
void seek(int rows)
Skip ahead the specified number of rows.
This module provides an interface to the GNU dbm database.
The basic use of GDBM is to store key/data pairs in a data file. Each key must be unique and each key is paired with only one data item.
The library provides primitives for storing key/data pairs, searching and retrieving the data by its key and deleting a key along with its data. It also support sequential iteration over all key/data pairs in a database.
The DB class also overloads enough operators to make it behave
a lot like a mapping(string(8bit):string(8bit)), you can index it,
assign indices and loop over it using foreach.
array(string(8bit)) indices( Gdbm.DB arg )
Provides overloading of indices.
Mainly useful when debugging, the returned list might not fit in memory for large databases.
string(8bit) m_delete(Gdbm.DB from, string(8bit) key)
Provides overloading of the m_delete function.
Will return the value the key had before it was removed, if any
If the key exists but deletion fails (usually due to a read only database) this function will throw an error.
array(string(8bit)) values( Gdbm.DB arg )
Provides overloading of values.
Mainly useful when debugging, the returned list might not fit in memory for large databases.
string(8bit) fetch(string(8bit) key)
string(8bit) res = Gdbm.DB()[ key ]
Return the data associated with the key 'key' in the database. If there was no such key in the database, zero is returned.
Gdbm.DB()[ key ] = 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;
Returns data on success.
store()
void close()
Closes the database. The object is no longer usable after this function has been called.
This is also done automatically when the object is destructed for any reason (running out of references or explicit destruct, as an example)
Gdbm.DB Gdbm.DB(void|string file, void|string(99..119) 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:
| Open database for reading |
| Open database for writing |
| Create database if it does not exist |
| Overwrite existing database |
| Fast mode |
| Synchronous mode |
| Locking mode |
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.
atexit might be useful.
This is very important if the database is used with the 'l' flag.
bool 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.
Used together with nextkey the databse can be iterated.
The database also works as an Iterator, and can be used as the
first argument to foreach.
Adding or removing keys will change the iteration order, this can cause keys to be skipped while iterating.
// Write the contents of the database for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k)) write(k+":"+gdbm->fetch(k)+"\n");Or, using foreach
// Write the contents of the database foreach( db; string key; string value ) write(key+":"+value+"\n");
string(8bit) nextkey(string(8bit) 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.
Changing (adding or removing keys) the database while iterating can cause keys to be skipped.
The database also works as an Iterator, and can be used as the
first argument to foreach.
// Write the contents of the database for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k)) write(k+":"+gdbm->fetch(k)+"\n");Or, using foreach
// Write the contents of the database foreach( db; string key; string value ) write(key+":"+value+"\n");
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 if the database is big.
int store(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->store(key, data);
Returns 1 on success.
Note that the returned value differs from that of `[]=().
`[]=()
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.
Object keeping track of an iteration over a DB
Can not be usefully constructed manually, instead use the database
as the first argument to foreach or predef::get_iterator
inherit predef::Iterator : predef::Iterator
Yabu is an all purpose transaction database written in pike, used to store data records associated with a unique key.
A Yabu database instance
array indices( Yabu.DB arg )
Return a list of all tables
array values( Yabu.DB arg )
Return all tables as an array
mixed res = Yabu.DB()[ handle ]
Equivalent to table
string ascii_statistics()
Return information about all tables in a human readable format
void close()
Close the database.
Yabu.DB Yabu.DB(string dir, string mode)
Open a new or existing databse.
The dir is the directory the database should be stored in.
It will be created if it does not exist.
Only one database can be in any given directory.
The mode specifies the operation mode, and
is a string made up of the desired modes, 'r'=read, 'w'=write
and 'c'=create.
To open an existing database in read only mode, use "r".
To open an existing database in read/write mode, use "rw".
To create a new database, or open an existing one in read write mode, use "rwc".
array(string) list_tables()
Return a list of all tables in the database
void purge()
Delete the database.
int reorganize(float|void ratio)
Call Table.reorganize in all tables
mapping(string:int) statistics()
Return information about all tables
void sync()
Sync all tables
Table table(string handle)
Return the Table object for the named table
This database is optimized for lots of very small data records (a
few bytes each, usually), but the API is otherwise identical to
the normal DB API.
It will perform badly if used with big records. You also need to know in advance aproximately how many keys there will be, the performance will degrade if way more than the expected number of keys are present.
inherit DB : DB
Yabu.LookupDB Yabu.LookupDB(string dir, string mode, mapping|void options)
Construct a new lookup table.
The options, if present, can be used to specify the index
hash size. The bigger that number is, the less memory will be
used given a certain number of actual keys. In general, using
the expected number of entries in the database divided by
100-1000 is reasonable.
The supported options are
|
The basic Yabu table
array indices( Yabu.Table arg )
Equivalent to list_keys()
mixed m_delete(Yabu.Table from, string handle)
Equivalent to delete
array values( Yabu.Table arg )
Fetches all keys from disk
mixed res = Yabu.Table()[ handle ]
Equivalent to get
Yabu.Table()[ handle ] = x
Equivalent to set
string ascii_statistics()
Return information about all tables in a human redable format
void close()
Close the table
void delete(string handle)
Remove a key
mixed get(string handle)
Get a key
array list_keys()
List all keys
void purge()
Close and delete the table from disk
int reorganize(float|void ratio)
Reorganize the on-disk storage, compacting it.
If ratio is given it is the lowest ratio of useful/total disk
usage that is allowed.
As an example, if ratio is 0.7 at lest 70% of the on-disk storage must be live data, if not the reoganization is done.
mixed set(string handle, mixed x)
Set a key
mapping(string:string|int) statistics()
Return information about the table.
| The number of keys |
| The on-disk space, in bytes |
|
void sync()
Synchronize. Usually done automatically
Transaction transaction()
Start a new transaction.
A transaction. Created by calling transaction() in the table object.
It provides the same functions the table does, in addition to
commit and rollback. Changes done using the transaction
object will not be in the actual table commit is called in the
transaction.
array indices( Yabu.Transaction arg )
Identical to list_keys();
mixed m_delete(Yabu.Transaction from, string handle)
Identical to delete
array values( Yabu.Transaction arg )
Identical to get(list_keys()][*]);
mixed res = Yabu.Transaction()[ handle ]
Identical to get
Yabu.Transaction()[ handle ] = x
Identical to set
void commit()
Commit all changes done so far in the transaction to the table
void delete(string handle)
Delete handle from the database
mixed get(string handle)
Get the value of handle
array list_keys()
List all keys
void rollback()
Undo all changes done so far in the transaction to the table
mixed set(string handle, mixed x)
Set handle to x