14. Database Access

14.1. Generic Sql API

14.1.1. Sql.Sql


MethodSql

ConnectionSql(__deprecated__(Connection) con)
ConnectionSql(__deprecated__(Connection) con, stringdb)

Description

Create a new generic SQL connection (DEPRECATED).

Parameter con

Use this connection to access the SQL-database.

Parameter db

Select this database.

Note

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

Returns con.

See also

8.0::Sql.Sql, Connection


MethodSql

ConnectionSql(stringhost)
ConnectionSql(stringhost, stringdb)
ConnectionSql(stringhost, mapping(string:int|string) options)
ConnectionSql(stringhost, stringdb, stringuser)
ConnectionSql(stringhost, stringdb, stringuser, stringpassword)
ConnectionSql(stringhost, stringdb, stringuser, stringpassword, mapping(string:int|string) options)

Description

Create a new generic SQL connection.

Parameter host
string

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 "" then the port can be a file name to access through a UNIX-domain socket or similar, e g "mysql://root@:/tmp/mysql.sock/".

There is a special dbtype "mysqls" which works like "mysql" but sets the CLIENT_SSL option and loads the /etc/my.cnf config file to find the SSL parameters. The same function can be achieved using the "mysql" dbtype.

int(0)

Access through a UNIX-domain socket or similar.

Parameter db

Select this database.

Parameter user

User name to access the database as.

Parameter password

Password to access the database.

Parameter options

Optional mapping of options. See the SQL-database documentation for the supported options. (eg Mysql.mysql()->create()).

Note

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.

Note

Exactly which databases are supported by pike depends on the installed set of client libraries when pike was compiled.

The possible ones are

mysql

libmysql based mysql connection

mysqls

libmysql based mysql connection, using SSL

dsn

ODBC based connection

msql

Msql

odbc

ODBC based connection

oracle

Oracle using oracle libraries

pgsql

PostgreSQL direct network access. This module is independent of any external libraries.

postgres

PostgreSQL libray access. Uses the Postgres module.

rsql

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.

sqlite

In-process SQLite database, uses the SQLite module

sybase

Uses the sybase module to access sybase

tds

Sybase and Microsoft SQL direct network access using the TDS protocol. This module is independent of any external libraries.

Note

Support for options was added in Pike 7.3.

Note

Use of an object host was deprecated in Pike 8.1.

Note

Prior to Pike 8.1 this was a wrapper class.

See also

8.0::Sql.Sql, Connection

14.1.2. Sql.Connection objects

Class Sql.Connection

Description

Base class for a connection to an SQL database.


InheritConnection

inherit __builtin.Sql.Connection : Connection

Class __builtin.Sql.Connection

Description

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.

Untyped and typed mode

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.

Note

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.

Note

Typed operation was not supported at all prior to Pike 7.8.363, and may not be supported for all databases.

See also

Sql.Connection, Sql.Sql(), Result


Variablecase_convert

bool __builtin.Sql.Connection.case_convert

Description

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).

0

No (default)

1

Yes


Variablemaster_sql

__deprecated__this_program __builtin.Sql.Connection.master_sql

Description

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.

Note

Read only


Methodbig_query

variant.Resultbig_query(object|stringq)

Description

Send an SQL query synchronously to the SQL-server and return the results in untyped mode.

Parameter q

Query to send to the SQL-server. This can either be a string with the query, or a previously compiled query (see compile_query()).

Returns

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.

Throws

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.

Note

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.

See also

query, streaming_query, big_typed_query, streaming_typed_query


Methodbig_query

variant.Resultbig_query(object|stringq, mapping(string|int:mixed) bindings)

Description

Send an SQL query synchronously to the SQL-server and return the results in untyped mode.

Parameter q

Query to send to the SQL-server. This can either be a string with the query, or a previously compiled query (see compile_query()).

Parameter bindings

A mapping containing bindings of variables used in the query. A variable is identified by a colon (:) followed by a name or number. Each index in the mapping corresponds to one such variable, and the value for that index is substituted (quoted) into the query wherever the variable is used.

res = query("SELECT foo FROM bar WHERE gazonk=:baz",([":baz":"value"]));

Binary values (BLOBs) may need to be placed in multisets.

Returns

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.

Throws

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.

Note

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.

See also

query, emulate_bindings, streaming_query, big_typed_query, streaming_typed_query


Methodbig_query

variant.Resultbig_query(object|stringq, string|multiset|int|float|objectextraarg, string|multiset|int|float|object ... extraargs)

Description

Send an SQL query synchronously to the SQL-server and return the results in untyped mode.

Parameter q

Query to send to the SQL-server. This can either be a string with the query, or a previously compiled query (see compile_query()).

Parameter extraarg
Parameter extraargs

Arguments as you would use in sprintf. They are automatically quoted.

res = query("select foo from bar where gazonk=%s","value");
Returns

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.

Note

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.

See also

query, handle_extraargs, streaming_query


Methodbig_typed_query

variant.Resultbig_typed_query(object|stringq)

Description

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.

Returns

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.

Note

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.

Note

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.

See also

query, typed_query, big_query, streaming_query


Methodbig_typed_query

variant.Resultbig_typed_query(object|stringq, mapping(string|int:mixed) bindings)

Description

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.

Note

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.

Note

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.

See also

query, typed_query, big_query, streaming_query


Methodbig_typed_query

variant.Resultbig_typed_query(object|stringq, string|multiset|int|float|objectextraarg, string|multiset|int|float|object ... extraargs)

Description

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.

Note

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.

Note

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.

See also

query, typed_query, big_query, streaming_query


Methodcompile_query

string|objectcompile_query(stringq)

Description

Compiles the query (if possible). Otherwise returns it as is. The resulting object can be used multiple times to the query functions.

Parameter q

SQL-query to compile.

Note

The default implementation just returns q unmodified.

See also

query, typed_query, big_query, big_typed_query, streaming_query, streaming_typed_query


Methodcreate

__builtin.Sql.Connection__builtin.Sql.Connection(stringhost)
__builtin.Sql.Connection__builtin.Sql.Connection(stringhost, stringdb)
__builtin.Sql.Connection__builtin.Sql.Connection(stringhost, mapping(string:int|string) options)
__builtin.Sql.Connection__builtin.Sql.Connection(stringhost, stringdb, stringuser)
__builtin.Sql.Connection__builtin.Sql.Connection(stringhost, stringdb, stringuser, stringpassword)
__builtin.Sql.Connection__builtin.Sql.Connection(stringhost, stringdb, stringuser, stringpassword, mapping(string:int|string) options)

Description

Create a new SQL connection.

Parameter host
string

Connect to the server specified.

int(0)

Access through a UNIX-domain socket or similar.

Parameter db

Select this database.

Parameter user

User name to access the database as.

Parameter password

Password to access the database.

Parameter options

Optional mapping of options. See the SQL-database documentation for the supported options. (eg Mysql.mysql()->create()).

Note

This function is typically called via Sql.Sql().

Note

Support for options was added in Pike 7.3.

Note

The base class (__builtin.Sql.Connection) only has a prototype.

See also

Sql.Sql()


Methodcreate_db

voidcreate_db(stringdb)

Description

Create a new database.

Parameter db

Name of database to create.


Methoddecode_date

intdecode_date(stringdatestr)

Description

Converts a database date-only spec to a system time value.

Parameter datestr

Date spec to decode.

Returns

Returns the number of seconds since 1970-01-01T00:00:00 UTC to 00:00:00 at the specified date in the current timezone.


Methoddecode_datetime

intdecode_datetime(stringdatetime)

Description

Converts a database date and time spec to a system time value.

Parameter datetime

Date and time spec to decode.

Returns

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.


Methoddecode_time

intdecode_time(stringtimestr, int|voiddate)

Description

Converts a database time spec to a system time value.

Parameter timestr

Time spec to decode.

Parameter date

Take the date part from this system time value. If zero, a seconds-since-midnight value is returned.

Returns

Returns the number of seconds since midnight.


Methoddrop_db

voiddrop_db(stringdb)

Description

Drop database

Parameter db

Name of database to drop.


Methodemulate_bindings

protectedstringemulate_bindings(stringquery, mapping(string|int:mixed) bindings)

Description

Build a raw SQL query, given the cooked query and the variable bindings It's meant to be used as an emulation engine for those drivers not providing such a behaviour directly (i.e. Oracle). The raw query can contain some variables (identified by prefixing a colon to a name or a number (i.e. ":var" or ":2"). They will be replaced by the corresponding value in the mapping.

Parameter query

The query.

Parameter bindings

Mapping containing the variable bindings. Make sure that no confusion is possible in the query. If necessary, change the variables' names.


Methodencode_date

stringencode_date(inttime)

Description

Converts a system time value to an appropriately formatted date-only spec for the database.

Parameter time

Time to encode.

The default implementation returns an ISO 9601 date.


Methodencode_datetime

stringencode_datetime(inttime)

Description

Converts a system time value to an appropriately formatted date and time spec for the database.

Parameter time

Time to encode.

The default implementation returns an ISO 9601 timestamp.


Methodencode_time

stringencode_time(inttime, int|voiddate)

Description

Converts a system time value to an appropriately formatted time spec for the database.

Parameter time

Time to encode.

Parameter date

If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.

The default implementation returns a colon-separated ISO 9601 time.


Methoderror

int|stringerror(void|intclear)

Description

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).

Note

The string returned is not newline-terminated.

Parameter clear

To clear the error, set it to 1.


Methodget_charset

stringget_charset()

Description

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).

See also

set_charset, Sql.mysql.get_charset


Methodhandle_extraargs

protectedarray(string|mapping(string|int:mixed)) handle_extraargs(stringquery, array(mixed) extraargs)

Description

Handle sprintf-based quoted arguments

Parameter query

The query as sent to one of the query functions.

Parameter extraargs

The arguments following the query.

Returns

Returns an array with two elements:

Array
string0

The query altered to use bindings-syntax.

mapping(string|int:mixed) 1

A bindings mapping. Not present if no bindings were added.


Methodhost_info

stringhost_info()

Description

Return info about the connection to the SQL-server.


Methodis_open

intis_open()

Description

Returns true if the connection seems to be open.

Note

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().

Note

The default implementation just returns the value 1.

See also

ping()


Methodlist_dbs

array(string) list_dbs(string|voidwild)

Description

List available databases on this SQL-server.

Parameter wild

Optional wildcard to match against.

This function calls low_list_dbs() and optionally performs wildcard filtering.

See also

low_list_dbs()


Methodlist_fields

array(mapping(string:mixed)) list_fields(stringtable, string|voidwild)

Description

List fields available in the specified table

Parameter table

Table to list the fields of.

Parameter wild

Optional wildcard to match against.

The default implementation calls low_list_fields() and applies the wild-card filter on the result.


Methodlist_tables

array(string) list_tables(string|voidwild)

Description

List tables available in the current database.

Parameter wild

Optional wildcard to match against.

The default implementation calls low_list_tables().


Methodlow_list_dbs

protectedarray(string)|zerolow_list_dbs()

Description

List available databases on this SQL-server.

Returns

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.

Note

The default implementation attempts the query "SHOW DATABASES".

See also

list_dbs()


Methodlow_list_fields

protectedarray(mapping(string:mixed))|zerolow_list_fields(stringtable)

Description

List fields available in the specified table

Parameter table

Table to list the fields of.

Returns

Returns an array of mappings with at least the fields:

"name" : string

The name of the field.

"table" : string

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.

Note

The default implementation attempts the query "SHOW FIELDS FROM 'table'", and then performs some normalization of the result.

See also

list_fields()


Methodlow_list_tables

protectedarray(string)|zerolow_list_tables()

Description

List tables available in the current database.

This function is intended for overriding by drivers not supporting wildcard filtering of table names.

Note

The default implementation attempts the query "SHOW TABLES".

See also

list_tables()


Methodping

intping()

Description

Check whether the connection is alive.

Returns

Returns one of the following:

0

Everything ok.

1

The connection reconnected automatically.

-1

The server has gone away, and the connection is dead.

The default implementation performs a trivial select to check the connection.

See also

is_open()


Methodpromise_query

variant.Promisepromise_query(stringq, void|mapping(string|int:mixed) bindings, void|function(array, .Result, array:array) map_cb)
variant.Promisepromise_query(stringq, function(array, .Result, array:array) map_cb)

Description

Sends a typed query to the database asynchronously.

Returns

An Sql.Promise object which can be used to obtain an Sql.FutureResult object to evaluate the query.

See also

streaming_typed_query(), Sql.Promise, Sql.FutureResult

Parameter map_cb

Callback 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.

Example
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);});

Methodquery

array(mapping(string:string|zero)) query(object|stringq, mixed ... extraargs)

Description

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

Returns one of the following on success:

array(mapping(string:string))

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.

zero

The value 0 (zero) if the query didn't return any result (eg INSERT or similar).

Throws

Throws an exception if the query fails.

Note

The default implementation calls big_query() and converts its result.

See also

typed_query, big_query, streaming_query


Methodquery_db

string|zeroquery_db()

Description

Query current database.

Returns

Returns the currently selected database.

See also

select_db()


Methodquote

stringquote(strings)

Description

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.


Methodreload

voidreload()

Description

Reload the tables.


Methodreset

voidreset()

Description

Reset connection state.


Methodselect_db

voidselect_db(stringdb)

Description

Select database to access.

See also

query_db()


Methodserver_info

stringserver_info()

Description

Return info about the current SQL-server.


Methodset_charset

voidset_charset(stringcharset)

Description

Changes the charset that the connection uses for queries and returned text strings.

Parameter charset

The charset to use. The valid values and their meanings depends on the database brand. However, the special value "unicode" (if supported) selects a mode where the query and result strings are unencoded (and possibly wide) unicode strings.

Throws

An error is thrown if the connection doesn't support the specified charset, or doesn't support charsets being set this way at all.

Note

See the set_charset functions for each database connection type for further details about the effects on the connection.

See also

get_charset, Sql.mysql.set_charset


Methodshutdown

voidshutdown()

Description

Shutdown a database server.


Methodsqlstate

stringsqlstate()

Description

Return last SQLSTATE.

The SQLSTATE error codes are specified in ANSI SQL.


Methodstreaming_query

variant.Resultstreaming_query(object|stringq)

Description

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.

Returns

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.

Note

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.

See also

big_query, streaming_typed_query


Methodstreaming_query

variant.Resultstreaming_query(object|stringq, mapping(string:mixed) bindings)

Description

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.

Returns

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.

See also

big_query, streaming_typed_query


Methodstreaming_query

variant.Resultstreaming_query(object|stringq, string|multiset|int|float|objectextraarg, string|multiset|int|float|object ... extraargs)

Description

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.

Returns

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.

See also

big_query, streaming_typed_query


Methodstreaming_typed_query

variant.Resultstreaming_typed_query(object|stringq)

Description

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.

Returns

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.

See also

big_query, streaming_query, big_typed_query


Methodstreaming_typed_query

variant.Resultstreaming_typed_query(object|stringq, mapping(string|int:mixed) bindings)

Description

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.

Returns

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.

See also

big_query, streaming_query, big_typed_query


Methodstreaming_typed_query

variant.Resultstreaming_typed_query(object|stringq, string|multiset|int|float|objectextraarg, string|multiset|int|float|object ... extraargs)

Description

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.

Returns

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.

See also

big_query, streaming_query, big_typed_query


Methodtyped_query

array(mapping(string:mixed)) typed_query(object|stringq, mixed ... extraargs)

Description

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

Returns one of the following on success:

array(mapping(string:mixed))

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.

zero

The value 0 (zero) if the query didn't return any result (eg INSERT or similar).

Note

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.

Note

The default implementation calls big_typed_query() and converts its result.

See also

query, big_typed_query

14.1.3. Sql.Result objects

Class Sql.Result

Description

Base class for the result from Connection.big_query() et al.


InheritResult

inherit __builtin.Sql.Result : Result

Class __builtin.Sql.Result

Description

Base class for results for the SQL-interface.

Used for return results from Sql.Connection->big_query().


Variableindex

int __builtin.Sql.Result.index

Description

This is the number of the current row. The actual semantics differs between different databases.


Variablemaster_res

__deprecated__array|this_program __builtin.Sql.Result.master_res

Description

Getter for the actual result object.

Returns

Returns the current object.

Note

Obsoleted in Pike 8.1 due to the wrapper class no longer existing, and this symbol thus essentially being a noop..

Deprecated

Replaced by __builtin.Sql.Result..

Note

Read only


Methodaffected_rows

intaffected_rows()

Returns

The number of affected rows by this query.

See also

status_command_complete(), num_rows()


Methodcreate

__builtin.Sql.Result__builtin.Sql.Result(mixedres)

Description

Create a new Sql.Result object

Parameter res

Result to use as base.


Methodeof

inteof()

Returns

Non-zero if there are no more rows.

Note

Not reliable! Some drivers have no support for checking whether there are more rows without also fetching them.


Methodfetch_fields

array(mapping(string:mixed)) fetch_fields()

Returns

Information about the available fields.


Methodfetch_json_result

stringfetch_json_result()

Description

Fetch remaining result as JSON-encoded data.


Methodfetch_row

array(mixed) fetch_row()

Description

Fetch the next row from the result.

Returns

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.

See also

fetch_row_array(), set_result_callback(), set_result_array_callback()


Methodfetch_row_array

array(array(mixed)) fetch_row_array()

Returns

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.

See also

fetch_row(), set_result_callback(), set_result_array_callback()


Methodincrement_index

protectedintincrement_index(int|voidval)

Description

Increment the index.

Parameter val

Value to increment the index with. Defaults to 1.

Returns

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().


Methodnext_result

this_programnext_result()

Description

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

Returns the Result object if there were more results, and 0 (zero) otherwise.

Throws

May throw the same errors as Sql.Connection()->big_query() et al.


Methodnum_fields

intnum_fields()

Returns

The number of fields in the result.


Methodnum_rows

intnum_rows()

Returns

The number of rows in the result.

Note

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.

See also

affected_rows(), eof()


Methodseek

voidseek(intskip)

Description

Skip past a number of rows.

Parameter skip

Number of rows to skip.


Methodset_result_array_callback

voidset_result_array_callback(function(this_program, array(array(mixed)), __unknown__ ... :void) callback, mixed ... args)

Description

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).

See also

fetch_row_array(), set_result_callback()


Methodset_result_callback

voidset_result_callback(function(this_program, array(mixed), __unknown__ ... :void) callback, mixed ... args)

Description

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).

See also

fetch_row(), set_result_array_callback()


Methodstatus_command_complete

stringstatus_command_complete()

Returns

The command-complete status for this query.

See also

affected_rows()

14.2. Sql protocol drivers

14.2.1. Mysql

Class Sql.mysql

Description

This class encapsulates a connection to a MySQL server, and implements the glue needed to access the Mysql module from the generic SQL module.

Typed mode

When query results are returned in typed mode, the MySQL data types are represented like this:

The NULL value

Returned as Val.null.

BIT, TINYINT, BOOL, SMALLINT, MEDIUMINT, INT, BIGINT

Returned as pike integers.

FLOAT, DOUBLE

Returned as pike floats.

DECIMAL

Returned as pike integers for fields that are declared to contain zero decimals, otherwise returned as Gmp.mpq objects.

DATE, DATETIME, TIME, YEAR

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.

TIMESTAMP

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.

String types

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.

See also

Sql.Connection, Sql.Sql()


Inheritmysql

inherit Mysql.mysql : mysql


Methodbig_query

variantResultbig_query(stringquery, mapping(string|int:mixed)|voidbindings, void|stringcharset)

Description

Sends a query to the server.

Parameter query

The SQL query.

Parameter bindings

An optional bindings mapping. See Sql.query for details about this.

Parameter charset

An optional charset that will be used temporarily while sending query to the server. If necessary, a query

SET character_set_client=charset

is 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.

Returns

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.

See also

Sql.big_query(), big_typed_query(), streaming_query()


Methodbig_typed_query

variantResultbig_typed_query(stringquery, mapping(string|int:mixed)|voidbindings, void|stringcharset)

Description

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.

See also

big_query(), streaming_typed_query()


Methodencode_date

stringencode_date(inttime)

Description

Converts a system time value to an appropriately formatted date-only spec for the database.

Parameter time

Time to encode.


Methodencode_datetime

stringencode_datetime(inttime)

Description

Converts a system time value to an appropriately formatted date and time spec for the database.

Parameter time

Time to encode.


Methodencode_time

stringencode_time(inttime, void|intdate)

Description

Converts a system time value to an appropriately formatted time spec for the database.

Parameter time

Time to encode.

Parameter date

If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.


Methodget_charset

stringget_charset()

Description

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).

Note

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).

Note

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.

See also

set_charset


Methodget_unicode_decode_mode

intget_unicode_decode_mode()

Description

Returns nonzero if unicode decode mode is enabled, zero otherwise.

See also

set_unicode_decode_mode


Methodget_unicode_encode_mode

intget_unicode_encode_mode()

Description

Returns nonzero if unicode encode mode is enabled, zero otherwise.

See also

set_unicode_encode_mode


Methodis_keyword

boolis_keyword(stringname)

Description

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.


Methodlatin1_to_utf8

stringlatin1_to_utf8(strings, intextended)

Description

Converts a string in MySQL latin1 format to UTF-8.


Methodquote

stringquote(strings)

Description

Quote a string so that it can safely be put in a query.

Parameter s

String to quote.


Methodset_charset

voidset_charset(stringcharset)

Description

Changes the connection charset. Works similar to sending the query SET NAMES charset but also records the charset on the client side so that various client functions work correctly.

charset is a MySQL charset name or the special value "unicode" (see below). You can use SHOW CHARACTER  SET to get a list of valid charsets.

Specifying "unicode" as charset is the same as "utf8" except that unicode encode and decode modes are enabled too. Briefly, this means that you can send queries as unencoded unicode strings and will get back non-binary text results as unencoded unicode strings. See set_unicode_encode_mode and set_unicode_decode_mode for further details.

Throws

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.

Note

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.

Note

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".

Note

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.

Note

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.

See also

get_charset, set_unicode_encode_mode, set_unicode_decode_mode


Methodset_unicode_decode_mode

voidset_unicode_decode_mode(intenable)

Description

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.

Parameter enable

Nonzero enables this feature, zero disables it.

Throws

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.

See also

set_unicode_encode_mode


Methodset_unicode_encode_mode

boolset_unicode_encode_mode(intenable)

Description

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.

Returns
1

Unicode encode mode is enabled.

0

Unicode encode mode couldn't be enabled because an incompatible connection charset is set. You need to do set_charset("latin1") or set_charset("unicode") to enable it.

Note

Note that this mode doesn't affect the MySQL system variable character_set_connection, i.e. it will still be set to latin1 by default which means server functions like UPPER() won't handle non-latin1 characters correctly in all cases.

To fix that, do set_charset("unicode"). That will allow unicode encode mode to work while utf8 is fully enabled at the server side.

Tip: If you enable utf8 on the server side, you need to send raw binary strings as _binary'...'. Otherwise they will get UTF-8 encoded by the server.

Note

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.

See also

set_unicode_decode_mode, set_charset


Methodstreaming_query

variantResultstreaming_query(stringquery, mapping(string|int:mixed)|voidbindings, void|stringcharset)

Description

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.

See also

big_query(), streaming_typed_query()


Methodstreaming_typed_query

variantResultstreaming_typed_query(stringquery, mapping(string|int:mixed)|voidbindings, void|stringcharset)

Description

Makes a streaming typed SQL query.

This function acts as the combination of streaming_query() and big_typed_query().

See also

big_typed_query(), streaming_typed_query()


Methodutf8_encode_query

stringutf8_encode_query(stringq, function(string, __unknown__ ... :string) encode_fn, mixed ... extras)

Description

Encodes the appropriate sections of the query with encode_fn. Everything except strings prefixed by an introducer (i.e. _something or N) is encoded.

Class Sql.mysqls

Description

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".

FIXME

Ought to load a suitable default configuration file for Win32 too.

Note

This connection method only exists if the Mysql-module has been compiled with SSL-support.


Inheritmysql

inherit Sql.mysql : mysql

Module Mysql

Description

This module enables access to the Mysql database from within Pike.

Note

You typically don't want to access this module directly, instead use Sql.Sql() with an "mysql://" URL.

See also

Mysql.mysql, Mysql.mysql()->Result, Sql.Sql


Methodclient_info

stringclient_info()

Description

Get some information about the Mysql-server client library.

See also

mysql()->statistics(), mysql()->server_info(), mysql()->protocol_info(), mysql()->info()

Class Mysql.SqlTable

Description

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".

Note

Although SQL is case insensitive on column names, this class isn't.

Note

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.

Note

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.


Variablecol_types

mapping(string:string) Mysql.SqlTable.col_types

Description

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.


Variableget_db

function(void:Sql.Sql) Mysql.SqlTable.get_db

Description

Callback to get a database connection.


Variableid_col

string Mysql.SqlTable.id_col

Description

The column containing the AUTO_INCREMENT values (if any).


Variablepk_cols

array(string) Mysql.SqlTable.pk_cols

Description

The column(s) containing the primary key, in order. Typically it is the same as ({id_col}).


Variableprop_col

string Mysql.SqlTable.prop_col

Description

The column containing miscellaneous properties. May be zero if this feature is disabled. Do not change.


Variableprop_col_max_length

int Mysql.SqlTable.prop_col_max_length

Description

Maximum length of the value prop_col can hold. Only applicable if prop_col is set. Do not change.


Variabletable

string Mysql.SqlTable.table

Description

The table to query or change. Do not change.


Methodconn_delete

voidconn_delete(Sql.Sqldb_conn, string|arraywhere, void|string|arrayrest)

Description

Like delete, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_get

mapping(string:mixed) conn_get(Sql.Sqldb_conn, mixedid, void|array(string|zero) fields)

Description

Like get, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_get_multi

Resultconn_get_multi(Sql.Sqldb_conn, array(mixed) ids, void|array(string) fields)

Description

Like get_multi, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_insert

intconn_insert(Sql.Sqldb_conn, mapping(string:mixed) ... records)

Description

Like insert, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_insert_ignore

intconn_insert_ignore(Sql.Sqldb_conn, mapping(string:mixed) ... records)

Description

Like insert_ignore, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_insert_or_update

intconn_insert_or_update(Sql.Sqldb_conn, mapping(string:mixed) record, void|int(0..2)clear_other_fields)

Description

Like insert_or_update, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_remove

voidconn_remove(Sql.Sqldb_conn, mixedid)

Description

Like remove, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_remove_multi

voidconn_remove_multi(Sql.Sqldb_conn, array(mixed) ids)

Description

Like remove_multi, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_replace

intconn_replace(Sql.Sqldb_conn, mapping(string:mixed) ... records)

Description

Like replace, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_select

Resultconn_select(Sql.Sqldb_conn, string|arraywhere, void|array(string) fields, void|string|arrayselect_exprs, void|stringtable_refs, void|string|arrayrest, void|stringselect_flags)

Description

Like select, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_select1

arrayconn_select1(Sql.Sqldb_conn, string|arrayselect_expr, string|arraywhere, void|stringtable_refs, void|string|arrayrest, void|stringselect_flags)

Description

Like select1, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodconn_update

voidconn_update(Sql.Sqldb_conn, mapping(string:mixed) record, void|int(0..2)clear_other_fields)

Description

Like update, but a database connection object is passed explicitly instead of being retrieved via get_db.


Methodcreate

Mysql.SqlTableMysql.SqlTable(function(void:Sql.Sql) get_db, stringtable, void|stringprop_col)

Description

Creates an SqlTable object for accessing (primarily) a specific table.

Parameter get_db

A function that will be called to get a connection to the database containing the table.

Parameter table

The name of the table.

Parameter prop_col

The 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.


Methoddelete

voiddelete(string|arraywhere, void|string|arrayrest)

Description

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.

Parameter where

The 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.

Parameter rest

Optional clauses that follows after the WHERE clause in a DELETE, i.e. ORDER BY and/or LIMIT.

See also

remove

FIXME

Add support for joins.


Methodget

mapping(string:mixed)|zeroget(mixedid, void|array(string) fields)

Description

Returns the record matched by a primary key value, or zero if there is no such record.

Parameter id

The 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.

Parameter fields

The 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.

See also

select, select1, get_multi


Methodget_multi

Resultget_multi(array(mixed) ids, void|array(string) fields)

Description

Retrieves multiple records selected by primary key values.

This function currently only works if the primary key is a single column.

Parameter id

Array 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.

Parameter fields

The 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

Returns a SqlTable.Result object from which the results can be retrieved. Zero is never returned.

Note

The result object implements an iterator, so it can be used directly in e.g. a foreach.

See also

get, select, select1


Methodhandle_argspec

localstringhandle_argspec(arrayargspec, mapping(string:mixed) bindings)

Description

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.

Note

The quote function can be used to quote string literals in the query, to avoid the array format.

Returns

Return the SQL snippet in string form, possibly with variable bindings referring to bindings.


Methodinsert

intinsert(mapping(string:mixed) ... records)

Description

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.

Returns

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.

See also

insert_ignore, replace, insert_or_update


Methodinsert_ignore

intinsert_ignore(mapping(string:mixed) ... records)

Description

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.

See also

insert, replace


Methodinsert_or_update

intinsert_or_update(mapping(string:mixed) record, void|int(0..2)clear_other_fields)

Description

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.

Returns

The value of the id_col column for the new or updated record. Zero is returned if there is no id_col column.

Note

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.


Methodquote

localstringquote(strings)

Description

Quotes a string literal for inclusion in an SQL statement, e.g. in a WHERE clause to select.

Note

Most functions here take raw string literals. Quoting is seldom necessary.


Methodremove

voidremove(mixedid)

Description

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.

See also

remove_multi, delete


Methodremove_multi

voidremove_multi(array(mixed) ids)

Description

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.

See also

remove


Methodreplace

intreplace(mapping(string:mixed) ... records)

Description

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.

See also

insert, insert_ignore


Methodselect

Resultselect(string|arraywhere, void|array(string) fields, void|string|arrayselect_exprs, void|stringtable_refs, void|string|arrayrest, void|stringselect_flags)

Description

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.

Parameter where

The 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.

Parameter fields

The 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.

Parameter select_exprs

Optional 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.

Parameter table_refs

Optional other tables to join into the SELECT. This is inserted between "FROM table" and "WHERE".

Parameter rest

Optional 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.

Parameter select_flags

Flags for the SELECT statement. If this string is given, it is simply inserted directly after the "SELECT" keyword.

Returns

Returns a SqlTable.Result object from which the results can be retrieved. Zero is never returned.

Note

The result object implements an iterator, so it can be used directly in e.g. a foreach.

Note

quote may be used to quote string literals if the sprintf-style formats aren't used.

See also

select1, get, get_multi


Methodselect1

arrayselect1(string|arrayselect_expr, string|arraywhere, void|stringtable_refs, void|string|arrayrest, void|stringselect_flags)

Description

Convenience variant of select for retrieving only a single column. The return value is an array containing the values in the select_expr column.

Parameter select_expr

The 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.

Parameter where

The 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.

Parameter table_refs

Optional other tables to join into the SELECT. This is inserted between "FROM table" and "WHERE".

Parameter rest

Optional 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.

Parameter select_flags

Flags for the SELECT statement. If this string is given, it is simply inserted directly after the "SELECT" keyword.

Returns

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.

See also

select, get, get_multi


Methodupdate

voidupdate(mapping(string:mixed) record, void|int(0..2)clear_other_fields)

Description

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.

See also

insert_or_update

Class Mysql.SqlTable.Result

Description

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.


Variableres

Sql.Result Mysql.SqlTable.Result.res

Description

The underlying result object from the db connection.


Method_get_iterator

Mysql.SqlTable.Resulta;
foreach( a; index; value ) or
protectedIterator_get_iterator()

Description

Returns an iterator for the result. Only one iterator may be created per Result object.


Methodcolumn_info

array(mapping(string:mixed)) column_info()

Description

Returns information about the columns in the result.


Methodeof

inteof()

Description

Returns nonzero if there are no more rows.


Methodfetch

mapping(string:mixed)|zerofetch()

Description

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.


Methodget_array

array(mapping(string:mixed)) get_array()

Description

Returns all the remaining records as an array of mappings. eof returns true after this.

Note

This is not a cast since it destructively modifies this object by fetching all remaining records.


Methodnum_rows

intnum_rows()

Description

Returns the number of rows in the result.

Class Mysql.SqlTable.Result.Iterator


Variablecached_num_rows

protectedint Mysql.SqlTable.Result.Iterator.cached_num_rows


Method__create__

protectedlocalvoid__create__(intcached_num_rows)


Methodcreate

Mysql.SqlTable.Result.IteratorMysql.SqlTable.Result.Iterator(intcached_num_rows)

Class Mysql.mysql

Description

Low level interface to the Mysql database.

This class enables access to the Mysql database from within Pike.

See also

Mysql.mysql()->Result, Sql.Sql


InheritConnection

inherit __builtin.Sql.Connection : Connection


Method_sprintf

stringsprintf(stringformat, ... Mysql.mysqlarg ... )


Methodaffected_rows

intaffected_rows()

Description

Returns the number of rows affected by the last query.


Methodbig_query

variantResultbig_query(stringquery)


Methodbig_typed_query

variantResultbig_typed_query(stringquery)


Methodbinary_data

intbinary_data()

Description

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.


Methodcreate

Mysql.mysqlMysql.mysql()
Mysql.mysqlMysql.mysql(stringhost)
Mysql.mysqlMysql.mysql(stringhost, stringdatabase)
Mysql.mysqlMysql.mysql(stringhost, stringdatabase, stringuser)
Mysql.mysqlMysql.mysql(stringhost, stringdatabase, stringuser, stringpassword)
Mysql.mysqlMysql.mysql(stringhost, stringdatabase, stringuser, stringpassword, mapping(string:string|int) options)

Description

Connect to a Mysql database.

To access the Mysql database, you must first connect to it. This is done with this function.

Parameter host

If you give no argument, or give "" as host it will connect with a UNIX-domain socket, which can be a big performance gain.

Parameter options

This optional mapping can contain zero or more of the following parameters:

"init_command" : string

Command to execute on connect.

"timeout" : int

Timeout in seconds.

"compress" : bool

Enable compressed protocol.

"mysql_config_file" : string

Change config file from "my.cnf".

"mysql_group" : string

Specify additional group to read from config file.

"mysql_named_pipe" : bool

Use named pipe to connect to server.

"mysql_local_infile" : bool

Enable use of LOCAL INFILE (security).

"mysql_charset_dir" : string

Change charset directory.

"mysql_charset_name" : string

Set connection charset - see set_charset for details. The default is "latin1". As opposed to set_charset, this way of specifying the connection charset doesn't require MySQL 4.1.0.

"unicode_decode_mode" : int

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 set_unicode_decode_mode.

"ssl_key" : string

Path to SSL-key for use in SSL-communication.

"ssl_cert" : string

Path to SSL-cert for use in SSL-communication.

"ssl_ca" : string

Path to SSL-CA for use in SSL-communication.

"ssl_capath" : string

Path to SSL-CAPATH for use in SSL-communication.

"ssl_cipher" : string

FIXME

"connect_options" : int

Options used when connecting to the server. See mysql documentation for more information.

Note

Some options may not be implemented. Unimplemented options are silently ignored.

Note

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.

Note

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).


Methodcreate_db

voidcreate_db(stringdatabase)

Description

Create a new database

This function creates a new database named database in the Mysql-server.

See also

select_db(), drop_db()


Methoddrop_db

voiddrop_db(stringdatabase)

Description

Drop a database

This function drops the database named database from the Mysql-server.

See also

create_db(), select_db()


Methoderrno

interrno()

Description

Returns an error code describing the last error from the Mysql-server.

Returns 0 (zero) if there was no error.


Methoderror

stringerror()

Description

Returns a string describing the last error from the Mysql-server.

Returns 0 (zero) if there was no error.


Methodhost_info

stringhost_info()

Description

Get information about the Mysql-server connection

See also

statistics(), server_info(), protocol_info(), info()


Methodinfo

stringinfo()

Description

Get information about the most recently executed statement.

See also

statistics(), server_info(), protocol_info(), host_info()


Methodinsert_id

intinsert_id()

Description

Returns the id of the last INSERT query into a table with an AUTO INCREMENT field.


Methodis_open

intis_open()

Description

Returns true if the connection seems to be open.

Note

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().

See also

ping()


Methodlist_dbs

Mysql.mysql.Resultlist_dbs()
Mysql.mysql.Resultlist_dbs(stringwild)

Description

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.

See also

list_tables(), list_fields(), list_processes(), Mysql.mysql()->Result


Methodlist_fields

array(int|mapping(string:mixed)) list_fields(stringtable)
array(int|mapping(string:mixed)) list_fields(stringtable, stringwild)

Description

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:

"name" : string

The name of the field.

"table" : string

The name of the table.

"default" : string

The default value for the field.

"type" : string

The SQL type of the field.

"length" : int

The length of the longest possible value that can be stored in the field. Note that this measures the display length in string form.

"flags" : multiset(string)

Some flags.

decimals : int

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:

"primary_key"

This field is part of the primary key for this table.

"unique"

This field is part of a unique key for this table.

"multiple_key"

This field is part of a nonunique key for this table.

"not_null"

This field cannot be NULL.

"blob"

This field is a BLOB or TEXT.

"auto_increment"

This field has the AUTO_INCREMENT attribute.

"zerofill"

This Field has the ZEROFILL attribute.

"binary"

This Field has the BINARY attribute.

"enum"

This Field is an ENUM.

"set"

This Field is a SET.

"unsigned"

This Field has the UNSIGNED attribute.

"numeric"

This Field is numeric.

Note

Michael Widenius recomends use of the following query instead: show fields in 'table' like "wild".

See also

list_dbs(), list_tables(), list_processes(), Mysql.mysql()->Result()->fetch_field()


Methodlist_processes

Mysql.mysql.Resultlist_processes()

Description

List all processes in the Mysql-server

Returns a table containing the names of all processes in the Mysql-server.

See also

list_dbs(), list_tables(), list_fields(), Mysql.mysql()->Result


Methodlist_tables

Mysql.mysql.Resultlist_tables()
Mysql.mysql.Resultlist_tables(stringwild)

Description

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.

See also

list_dbs(), list_fields(), list_processes(), Mysql.mysql()->Result


Methodping

intping()

Description

Check whether the connection is alive.

Returns

Returns one of the following:

0

Everything ok.

1

The connection reconnected automatically.

-1

The server has gone away, and the connection is dead.

See also

is_open()


Methodprotocol_info

intprotocol_info()

Description

Give the Mysql protocol version

This function returns the version number of the protocol the Mysql-server uses.

See also

statistics(), server_info(), host_info()


Methodquery_db

string|zeroquery_db()

Description

Returns the currently selected database.

See also

select_db()


Methodreload

voidreload()

Description

Reload security tables

This function causes the Mysql-server to reload its access tables.

See also

shutdown()


Methodreset

voidreset()

Description

Reset connection state.

Currently this just releases all table locks.


Methodselect_db

voidselect_db(stringdatabase)

Description

Select database.

The Mysql-server can hold several databases. You select which one you want to access with this function.

See also

create(), create_db(), drop_db(), query_db()


Methodserver_info

stringserver_info()

Description

Get the version number of the Mysql-server.

See also

statistics(), host_info(), protocol_info(), info()


Methodshutdown

voidshutdown()

Description

Shutdown the Mysql-server

This function shuts down a running Mysql-server.

See also

reload()


Methodsqlstate

stringsqlstate()

Description

Returns the SQLSTATE error code describing the last error.

The value "000000" means 'no error'. The SQLSTATE error codes are described in ANSI SQL.


Methodstatistics

stringstatistics()

Description

Some Mysql-server statistics

This function returns some server statistics.

See also

server_info(), host_info(), protocol_info(), info()


Methodstreaming_query

variantResultstreaming_query(stringquery)


Methodstreaming_typed_query

variantResultstreaming_typed_query(stringquery)

Class Mysql.mysql.Result

Description

Objects of this class contain the result from Mysql queries.

See also

Mysql.mysql, Mysql.mysql->big_query()


InheritResult

inherit __builtin.Sql.Result : Result


Methodcreate

Mysql.mysql.ResultMysql.mysql.Result(bool|voidtyped_mode)

Description

Make a new Mysql.mysql_result object.

See also

Mysql.mysql->big_query(), Mysql.mysql->list_dbs(), Mysql.mysql->list_tables(), Mysql.mysql->list_processes(), Mysql.mysql


Methodeof

booleof()

Description

Sense end of result table.

Returns 1 when all rows have been read, and 0 (zero) otherwise.

See also

fetch_row()


Methodfetch_field

int|mapping(string:mixed) fetch_field()

Description

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.

Note

This function is usually not enabled. To enable it SUPPORT_FIELD_SEEK must be defined when compiling the mysql-module.

See also

fetch_fields(), field_seek(), Mysql.mysql->list_fields()


Methodfetch_fields

array(int|mapping(string:mixed)) fetch_fields()

Description

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.

Note

Resets the field cursor to 0 (zero).

This function always exists even when fetch_field() and field_seek() don't.

See also

fetch_field(), field_seek(), Mysql.mysql->list_fields()


Methodfetch_json_result

stringfetch_json_result()

Description

Fetch all remaining rows and return them as JSON-encoded data.

See also

fetch_row()

Note

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.

See also

Standards.JSON.encode


Methodfetch_row

int|array(string) fetch_row()

Description

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.

See also

seek()


Methodfield_seek

voidfield_seek(intfield_no)

Description

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.

Note

This function is usually not enabled. To enable it SUPPORT_FIELD_SEEK must be defined when compiling the mysql-module.

See also

fetch_field(), fetch_fields()


Methodlow_seek

voidlow_seek(introw)

Description

Seek to the specified row.

Note

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.

See also

fetch_row(), seek()


Methodnum_fields

intnum_fields()

Description

Number of fields in the result.

See also

num_rows()


Methodnum_rows

intnum_rows()

Description

Number of rows in the result.

See also

num_fields()

14.2.2. Msql

Class Sql.msql

Description

Implements the glue needed to access the Msql-module from the generic SQL module.


Inheritmsql

inherit Msql.msql : msql

Module Msql

Description

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.*

Note

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.

FIXME

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.

See also

Sql.Sql


Constantversion

constant Msql.version

Description

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.

Class Msql.msql


InheritConnection

inherit __builtin.Sql.Connection : Connection


Methodaffected_rows

intaffected_rows()

Description

This function returns how many rows in the database were affected by our last SQL query.

Note

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


Methodbig_query

variantResultbig_query(string(8bit)sqlquery)

Description

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.

Throws

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

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.

See also

error


Methodcreate

Msql.msqlMsql.msql(void|stringdbserver, void|stringdbname, void|stringusername, void|stringpasswd)

Description

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.

Throws

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.

Note

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.

See also

select_db


Methodcreate_db

voidcreate_db(stringdbname)

Description

This function creates a new database with the given name (assuming we have enough permissions to do this).

See also

drop_db


Methoddrop_db

voiddrop_db(stringdbname)

Description

This function destroys a database and all the data it contains (assuming we have enough permissions to do so). USE WITH CAUTION!

See also

create_db


Methoderror

stringerror(void|intclear)

Description

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).

See also

query


Methodhost_info

stringhost_info()

Description

This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).


Methodlist_dbs

array(string) list_dbs(void|stringwild)

Description

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.


Methodlist_fields

mapping(string:mapping(string:mixed)) list_fields(stringtable)

Description

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:

"type" : string

Describes the field's mSQL data type ("char","integer",...)

"length" : int

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.

"table" : string

The table this field is in. Added only for interface compliancy.

"flags" : multiset(string)

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".

Note

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.

See also

query


Methodlist_index

arraylist_index(stringtablename, stringindexname)

Description

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).

Note

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.


Methodlist_tables

array(string) list_tables(void|stringwild)

Description

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.


Methodreload_acl

voidreload_acl()

Description

This function forces a server to reload its ACLs.

Note

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.

See also

create


Methodselect_db

voidselect_db(stringdbname)

Description

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.

Throws

This function CAN raise exceptions in case something goes wrong (for example: unexistant database, insufficient permissions, whatever).

See also

create, error


Methodserver_info

stringserver_info()

Description

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.


Methodshutdown

voidshutdown()

Description

This function shuts a SQL-server down.

Class Msql.msql.Result


InheritResult

inherit __builtin.Sql.Result : Result

14.2.3. Postgresql

Class Sql.pgsql

Description

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.

Note

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.

See also

Sql.Connection, Sql.postgres, https://www.postgresql.org/docs/current/static/


InheritConnection

inherit __builtin.Sql.Connection : Connection


Methodbig_query

finalvariant.pgsql_util.Resultbig_query(stringq, void|mapping(string|int:mixed) bindings, void|int_alltyped)

Description

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:

":_cache" : int

Forces caching on or off for the query at hand.

":_text" : int

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.

":_sync" : int

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.

":_debug" : int

Sets the debuglevel for query tracing.

Note

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).

Returns

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).

Note

This function can raise exceptions.

Note

This function supports multiple simultaneous queries (portals) on a single database connection. This is a feature not commonly supported by other database backends.

Note

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).

See also

big_typed_query(), Sql.Connection, Sql.Result, query(), Sql.pgsql_util.Result


Methodbig_typed_query

finalvariant.pgsql_util.Resultbig_typed_query(stringq, void|mapping(string|int:mixed) bindings)

Description

This function returns an object that allows streaming and typed results.

See also

big_query(), Sql.Connection, Sql.Result


Methodcancelquery

finalvoidcancelquery()

Description

Cancels all currently running queries in this session.

See also

reload(), resync()

Note

This function is PostgreSQL-specific.


Methodclose

finalvoidclose()

Description

Closes the connection to the database, any running queries are terminated instantly.

Note

This function is PostgreSQL-specific.


Methodcreate

Sql.pgsqlSql.pgsql(void|stringhost, void|stringdatabase, void|stringuser, void|stringpass, void|mapping(string:mixed) options)

Description

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.

Parameter host

Should 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.

Parameter database

Specifies 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".

Parameter options

Currently supports at least the following:

"reconnect" : int

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.

"use_ssl" : int

If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will fallback to plain unencrypted.

"force_ssl" : int

If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will abort.

"text_query" : int

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).

"sync_parse" : int

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.

"cache_autoprepared_statements" : int

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.

"client_encoding" : string

Character encoding for the client side, it defaults to using the default encoding specified by the database, e.g. "UTF8" or "SQL_ASCII".

"standard_conforming_strings" : string

When on, backslashes in strings must not be escaped any longer, quote() automatically adjusts quoting strategy accordingly.

"escape_string_warning" : string

When on, a warning is issued if a backslash (\) appears in an ordinary string literal and "standard_conforming_strings" is off, defaults to on.

For the numerous other options please check the PostgreSQL manual.

Note

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.

Note

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.

See also

Postgres.postgres, Sql.Connection, select_db(), https://www.postgresql.org/docs/current/static/runtime-config-client.html


Methodcreate_db

finalvoidcreate_db(stringdb)

Description

This function creates a new database (assuming we have sufficient privileges to do this).

Parameter db

Name of the new database.

See also

drop_db()


Methoddrop_db

finalvoiddrop_db(stringdb)

Description

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.

Parameter db

Name of the database to be deleted.

See also

create_db()


Methoderror

finalstringerror(void|intclear)

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).

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.

Note

The string returned is not newline-terminated.

Parameter clear

To clear the error, set it to 1.

See also

big_query()


Methodget_charset

finalstringget_charset()

Returns

The PostgreSQL name for the current connection charset.

See also

set_charset(), getruntimeparameters(), https://www.postgresql.org/docs/current/static/multibyte.html


Methodgetruntimeparameters

finalmapping(string:string) getruntimeparameters()

Returns

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:

"client_encoding" : string

Character encoding for the client side, e.g. "UTF8" or "SQL_ASCII".

"server_encoding" : string

Character encoding for the server side as determined when the database was created, e.g. "UTF8" or "SQL_ASCII".

"DateStyle" : string

Date parsing/display, e.g. "ISO, DMY".

"TimeZone" : string

Default timezone used by the database, e.g. "localtime".

"standard_conforming_strings" : string

When on, backslashes in strings must not be escaped any longer.

"session_authorization" : string

Displays the authorisationrole which the current session runs under.

"is_superuser" : string

Indicates if the current authorisationrole has database-superuser privileges.

"integer_datetimes" : string

Reports wether the database supports 64-bit-integer dates and times.

"server_version" : string

Shows the server version, e.g. "8.3.3".

The values can be changed during a session using SET commands to the database. For other runtimeparameters check the PostgreSQL documentation.

See also

https://www.postgresql.org/docs/current/static/runtime-config-client.html

Note

This function is PostgreSQL-specific.


Methodgetstatistics

finalmapping(string:mixed) getstatistics()

Returns

A set of statistics for the current session:

"warnings_dropped" : int

Number of warnings/notices generated by the database but not collected by the application by using error() after the statements that generated them.

"skipped_describe_count" : int

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.

"used_prepared_statements" : int

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.

"current_prepared_statements" : int

Cache size of currently prepared statements.

"current_prepared_statement_hits" : int

Sum of the number hits on statements in the current statement cache.

"prepared_statement_count" : int

Total number of prepared statements generated.

"portals_opened_count" : int

Total number of portals opened, i.e. number of statements issued to the database. Only available if PG_STATS is compile-time enabled.

"bytes_received" : int

Total number of bytes received from the database so far.

"messages_received" : int

Total number of messages received from the database (one SQL-statement requires multiple messages to be exchanged).

"portals_in_flight" : int

Currently still open portals, i.e. running statements.

Note

This function is PostgreSQL-specific.


Methodhost_info

finalstringhost_info()

Description

This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX socket).

See also

server_info()


Methodis_open

finalintis_open()

Description

Returns true if the connection seems to be open.

Note

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.

See also

ping()


Methodlist_dbs

finalarray(string) list_dbs(void|stringglob)

Returns

An array of the databases available on the server.

Parameter glob

If specified, list only those databases matching it.


Methodlist_fields

finalarray(mapping(string:mixed)) list_fields(void|stringtable, void|stringglob)

Returns

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" : string

Schema the table belongs to

"table" : string

Name of the table

"kind" : string

Type of table

"owner" : string

Tableowner

"rowcount" : int

Estimated rowcount of the table

"datasize" : int

Estimated total datasize of the table in bytes

"indexsize" : int

Estimated total indexsize of the table in bytes

"name" : string

Name of the column

"type" : string

A textual description of the internal (to the server) column type-name

"typeoid" : int

The OID of the internal (to the server) column type

"length" : string

Size of the columndatatype

"default" : mixed

Default value for the column

"is_shared" : int

If the table has any indices

"has_index" : int
"has_primarykey" : int

If the table has a primary key

Parameter glob

If specified, list only the tables with matching names. Setting it to * will include system columns in the list.


Methodlist_tables

finalarray(string) list_tables(void|stringglob)

Returns

An array containing the names of all the tables and views in the path in the currently selected database.

Parameter glob

If specified, list only the tables with matching names.


Methodping

finalintping()

Description

Check whether the connection is alive.

Returns

Returns one of the following:

0

Everything ok.

-1

The server has gone away, and the connection is dead.

See also

is_open()


Methodquote

finalstringquote(strings)

Returns

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.

Note

Quoting must not be done for parameters passed in bindings.

See also

big_query(), quotebinary(), create()


Methodquotebinary

finalstringquotebinary(strings)

Returns

The given string, but escapes/quotes all contained magic characters for binary (bytea) arguments in textual SQL-queries.

Note

Quoting must not be done for parameters passed in bindings.

See also

big_query(), quote()

Note

This function is PostgreSQL-specific.


Methodreload

finalvoidreload()

Description

For PostgreSQL this function performs the same function as resync().

See also

resync(), cancelquery()


Methodresync

finalvoidresync()

Description

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.

Note

This function can raise exceptions.

See also

cancelquery(), reload()

Note

This function is PostgreSQL-specific.


Methodselect_db

finalvoidselect_db(stringdbname)

Description

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.

See also

create()


Methodserver_info

finalstringserver_info()

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.

See also

host_info()


Methodset_charset

finalvoidset_charset(stringcharset)

Description

Changes the connection charset. When set to "UTF8", the query, parameters and results can be Pike-native wide strings.

Parameter charset

A PostgreSQL charset name.

See also

get_charset(), create(), https://www.postgresql.org/docs/current/static/multibyte.html


Methodset_notify_callback

finalvoidset_notify_callback(stringcondition, void|function(int, string, string, mixed ... :void) notify_cb, void|intselfnotify, mixed ... args)

Description

With PostgreSQL you can LISTEN to NOTIFY events. This function allows you to detect and handle such events.

Parameter condition

Name 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.

Parameter notify_cb

Function 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.

Parameter selfnotify

Normally notify events generated by your own session are ignored. If you want to receive those as well, set selfnotify to one.

Parameter args

Extra arguments to pass to notify_cb.

Note

This function is PostgreSQL-specific.


Methodsetcachedepth

finalintsetcachedepth(void|intnewdepth)

Parameter newdepth

Sets the new cachedepth for automatic caching of prepared statements.

Returns

The previous cachedepth.

Note

This function is PostgreSQL-specific.


Methodsetfetchlimit

finalintsetfetchlimit(void|intnewfetchlimit)

Parameter newfetchlimit

Sets the new fetchlimit to interleave queries.

Returns

The previous fetchlimit.

Note

This function is PostgreSQL-specific.


Methodsetportalbuffersize

finalintsetportalbuffersize(void|intnewportalbuffersize)

Parameter newportalbuffersize

Sets the new portalbuffersize for buffering partially concurrent queries.

Returns

The previous portalbuffersize.

Note

This function is PostgreSQL-specific.


Methodsettimeout

finalintsettimeout(void|intnewtimeout)

Parameter newtimeout

Sets the new timeout for long running queries.

Returns

The previous timeout.

Note

This function is PostgreSQL-specific.


Methodstatus_commit

finalstringstatus_commit()

Returns

The current commitstatus of the connection. Returns either one of:

idle
intransaction
infailedtransaction
Note

This function is PostgreSQL-specific.


Methodstreaming_query

finalvariant.pgsql_util.Resultstreaming_query(stringq, void|mapping(string|int:mixed) bindings)

Description

This is an alias for big_query(), since big_query() already supports streaming of multiple simultaneous queries through the same connection.

See also

big_query(), big_typed_query(), streaming_typed_query(), Sql.Connection, Sql.Result


Methodstreaming_typed_query

finalvariant.pgsql_util.Resultstreaming_typed_query(stringq, void|mapping(string|int:mixed) bindings)

Description

This function returns an object that allows streaming and typed results.

See also

big_query(), Sql.Connection, Sql.Result

Class Sql.pgsqls

Description

Implements SQL-urls for pgsqls://[user[:password]@][hostname][:port][/database]

Sets the connection to SSL-mode, otherwise identical to Sql.pgsql.

See also

Sql.pgsql, Sql.Sql


Inheritpgsql

inherit Sql.pgsql : pgsql

Class Sql.postgres

Description

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.

Note

This driver has been deprecated. You should use the more advanced driver Sql.pgsql to access PostgreSQL databases instead.

Note

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.

"PGHOST"

Sets the name of the default host to connect to. It defaults to "localhost".

"PGHOSTADDR"

Set the numeric IP address to connect to. This may be set instead of or in addition to PGHOST to avoid DNS lookups.

"PGPORT"

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).

"PGDATABASE"

Sets the default database to connect to.

"PGUSER"

Sets the default username used to connect to the database.

"PGPASSWORD"

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.

"PGSERVICE"

Sets the service name to be looked up in pg_service.conf. This is a shorter way to set all the parameters.

"PGSSLMODE"

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.

"PGOPTIONS"

Sets some extra flags for the frontend-backend connection. do not set unless you're sure of what you're doing.

"PGREALM"

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.

"PGTTY"

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.

See also

Sql.pgsql, Sql.Sql, Postgres.postgres, Sql.postgres_result


Inheritmo

inherit Postgres.postgres : mo


Inheritpgsql

inherit Sql.pgsql : pgsql

Description

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.

See also

Sql.pgsql, Sql.Sql


Variableversion

string Sql.postgres.version

Description

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.


Methodbig_query

int|objectbig_query(object|stringq, mapping(string|int:mixed)|voidbindings)

Description

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).

Note

This function can raise exceptions.

See also

Sql.Sql, Sql.sql_result


Methodcreate

Sql.postgresSql.postgres()
Sql.postgresSql.postgres(stringhost, void|stringdatabase, void|stringuser, void|stringpassword, void|mappingoptions)

Description

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.

Note

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.

See also

Sql.pgsql, Postgres.postgres, Sql.Sql, postgres->select_db


Methodcreate_db

voidcreate_db(stringdb)

Description

This function creates a new database with the given name (assuming we have enough permissions to do this).

See also

drop_db


Methoddrop_db

voiddrop_db(stringdb)

Description

This function destroys a database and all the data it contains (assuming we have enough permissions to do so).

See also

create_db


Methoderror

stringerror()

Description

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).

See also

big_query


Methodhost_info

stringhost_info()

Description

This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).


Methodlist_dbs

array(string) list_dbs(void|stringglob)

Description

Lists all the databases available on the server. If glob is specified, lists only those databases matching it.


Methodlist_fields

array(mapping(string:mixed)) list_fields(stringtable, void|stringwild)

Description

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:

"has_rules" : int 
"is_shared" : int 
"owner" : string

The textual representation of a Postgres uid.

"length" : string 
"text" : string

A textual description of the internal (to the server) type-name

"default" : mixed 
"expires" : string

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.


Methodlist_tables

array(string) list_tables(void|stringglob)

Description

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.


Methodreset

voidreset()

Description

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.

Note

This function is Postgres-specific, and thus it is not available through the generic SQL-interface.


Methodselect_db

voidselect_db(stringdbname)

Description

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.

Note

This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)

See also

create


Methodserver_info

stringserver_info()

Description

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.


Methodset_notify_callback

voidset_notify_callback()
voidset_notify_callback(function(:void) f)
voidset_notify_callback(function(:void) f, int|floatpoll_delay)

Description

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.

Note

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.

FIXME

An integer can be passed as first argument, but it's effect is not documented.


Methodstreaming_query

int|objectstreaming_query(object|stringq, mapping(string|int:mixed)|voidbindings)

Description

This is an alias for big_query(), since big_query() already supports streaming.

See also

big_query, Sql.Sql, Sql.sql_result

Module Postgres

Class Postgres.postgres

Description

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.

Note

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

Note

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.

Note

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.

"PGHOST"

Sets the name of the default host to connect to. It defaults to "localhost".

"PGHOSTADDR"

Set the numeric IP address to connect to. This may be set instead of or in addition to PGHOST to avoid DNS lookups.

"PGPORT"

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).

"PGDATABASE"

Sets the default database to connect to.

"PGUSER"

Sets the default username used to connect to the database.

"PGPASSWORD"

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.

"PGSERVICE"

Sets the service name to be looked up in pg_service.conf. This is a shorter way to set all the parameters.

"PGSSLMODE"

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.

"PGOPTIONS"

Sets some extra flags for the frontend-backend connection. do not set unless you're sure of what you're doing.

"PGREALM"

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.

"PGTTY"

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.

See also

Sql.Sql, Sql.postgres, Sql.postgres_result


Variableversion

string Postgres.postgres.version

Description

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.


Method_quote

string_quote(strings)

Description

Escape a string to prevent SQL injection, using the current connection's character encoding settings.


Method_set_notify_callback

void_set_notify_callback()
void_set_notify_callback(function(:void) f)

Description

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.

Note

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

See also

Sql.postgres


Methodaffected_rows

intaffected_rows()

Description

This function returns the number of rows affected by the last query.


Methodbig_query

Sql.postgres_resultbig_query(stringsqlquery)
Sql.postgres_resultbig_query(stringsqlquery, arraybindings)

Description

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).

Note

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.

See also

Sql.Sql, Sql.sql_result


Methodcreate

Postgres.postgresPostgres.postgres()
Postgres.postgresPostgres.postgres(stringhost, void|stringdatabase, void|intport)

Description

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.

Note

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.

See also

Sql.postgres, Sql.Sql, select_db


Methoderror

stringerror()

Description

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).

See also

big_query


Methodhost_info

stringhost_info()

Description

This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).


Methodreset

voidreset()

Description

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.

Note

This function is Postgres-specific, and thus it is not available through the generic SQL-interface.


Methodselect_db

voidselect_db(stringdbname)

Description

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.

Note

This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)

See also

create

Class Postgres.postgres_result

Description

Contains the result of a Postgres-query.

See also

Sql.postgres, Postgres.postgres, Sql.Sql, Sql.sql_result


Methodcreate

Postgres.postgres_resultPostgres.postgres_result(objecto)

Description

You can't create istances of this object yourself. The only way to create it is via a big_query to a Postgres database.


Methodfetch_fields

array(mapping(string:mixed)) fetch_fields()

Description

Returns an array with an entry for each field, each entry is a mapping with the following fields:

"name" : string

Name of the column

"type" : int

The type ID of the field. This is the database's internal representation type ID.

"length" : int|string

Can be an integer (the size of the contents in bytes) or the word "variable".

Note

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.


Methodfetch_row

array(string) fetch_row()

Description

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.

Bugs

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.

See also

seek()


Methodnum_fields

intnum_fields()

Description

Returns the number of fields in the result.


Methodnum_rows

intnum_rows()

Description

Returns the number of rows in the result.


Methodseek

voidseek(inthowmuch)

Description

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.

14.2.4. SQLite

Module SQLite


Methodlibrary_version

stringlibrary_version()

Description

Returns the version of the sqlite library used.

Class SQLite.SQLite

Description

Low-level interface to SQLite3 databases.

This class should typically not be accessed directly, but instead via Sql.Sql() with the scheme "sqlite://".


InheritConnection

inherit __builtin.Sql.Connection : Connection


Methodbig_query

Resultbig_query(stringquery, mapping(string|int:mixed)|voidbindings)

Description

Perform a streaming query against a SQLite database.

Note

In Pike 8.0 and earlier this function behaved as big_typed_query().

See also

Sql.Sql()->big_query(), big_typed_query()


Methodbig_typed_query

TypedResultbig_typed_query(stringquery, mapping(string|int:mixed)|voidbindings)

Description

Perform a streaming typed query against a SQLite database.

Note

This was the behavior of big_query() in Pike 8.0 and earlier.

See also

Sql.Sql()->big_typed_query(), big_query()


Methodchanges

intchanges()

Description

Get the number of changes.

FIXME

Document this function properly.


Methodcreate

SQLite.SQLiteSQLite.SQLite(stringpath, mixed ... ignored)

Description

Open the SQLite database stored at path.


Methodcreate_db

voidcreate_db(stringdb)

Note

This operation is not supported for SQLite.

See also

Sql.Sql()->create_db()


Methoddrop_db

voiddrop_db(stringdb)

Note

This operation is not supported for SQLite.

See also

Sql.Sql()->drop_db()


Methoderror

stringerror(void|intclear)

Description

Get the latest error message.

See also

Sql.Sql()->error()


Methodinsert_id

intinsert_id()

Description

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.


Methodinterrupt

voidinterrupt()

FIXME

Document this function.


Methodlist_dbs

array(string) list_dbs()

Note

This operation is not supported for SQLite.

See also

Sql.Sql()->list_dbs()


Methodquery

array|intquery(stringquery, mapping(string|int:mixed)|voidbindings)

Description

Perform a query against a SQLite database.

Note

In Pike 8.0 and earlier this function behaved as typed_query().

See also

Sql.Sql()->query(), typed_query()


Methodselect_db

voidselect_db(stringdb)

Note

This operation is not supported for SQLite.

See also

Sql.Sql()->select_db()


Methodserver_info

stringserver_info()

Description

Get information about the SQLite library version.

See also

Sql.Sql()->server_info()


Methodtotal_changes

inttotal_changes()

Description

Get the total number of changes for this session.

FIXME

Document this function properly.


Methodtyped_query

array|inttyped_query(stringquery, mapping(string|int:mixed)|voidbindings)

Description

Perform a typed_query against a SQLite database.

Note

This was the behavior of query() in Pike 8.0 and earlier.

See also

Sql.Sql()->query(), query()

Class SQLite.SQLite.Result

Description

Result object from big_query().


InheritTypedResult

inherit TypedResult : TypedResult


Methodfetch_row

arrayfetch_row()

See also

Sql.sql_result()->fetch_row()

Class SQLite.SQLite.TypedResult

Description

Result object from typed_big_query().


InheritResult

inherit __builtin.Sql.Result : Result


Methodeof

inteof()

See also

Sql.sql_result()->eof()


Methodfetch_fields

array(mapping(string:mixed)) fetch_fields()

See also

Sql.sql_result()->fetch_fields()


Methodfetch_row

arrayfetch_row()

See also

Sql.sql_result()->fetch_row()


Methodnum_fields

intnum_fields()

See also

Sql.sql_result()->num_fields()


Methodnum_rows

intnum_rows()

Note

This API is not supported for Sql.sqlite.

See also

Sql.sql_result()->num_rows()


Methodseek

voidseek(intskip)

See also

Sql.sql_result()->seek()

14.2.5. Oracle

Module Oracle

Description

Pike interface to Oracle databases.

Thanks

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.

Class Oracle.oracle

Description

Connection to an Oracle database server.

Note

You probably don't want to access this class directly, but rather via Sql.Sql.

See also

Sql.Sql, Sql.oracle


InheritConnection

inherit __builtin.Sql.Connection : Connection

Class Oracle.oracle.LOB

Description

Large OBject.

Class Oracle.oracle.NULL


InheritNull

inherit Val.Null : Null


Constantis_oracle_null

constant Oracle.oracle.NULL.is_oracle_null

Class Oracle.oracle.compile_query

Class Oracle.oracle.compile_query.TypedResult


InheritResult

inherit __builtin.Sql.Result : Result

14.2.6. Odbc

Class Sql.odbc


Inheritodbc

inherit Odbc.odbc : odbc

Class Sql.odbc.TypedResult


Inheritthis_program

inherit ::this_program : this_program


Variable_null_value

mixed Sql.odbc.TypedResult._null_value

Description

Value to use to represent NULL.


Variableuser_defined_cb

function(string(8bit), mapping(string:mixed), int:mixed) Sql.odbc.TypedResult.user_defined_cb

Description

Function called by user_defined_factory() to create values for custom types.


Methodscale_numeric

Gmp.mpq|intscale_numeric(intmantissa, intscale)

Description

Helper function that scales mantissa by a factor 10->pow(scale).

Returns

Returns an Gmp.mpq object if scale is negative, and otherwise an integer (bignum).


Methodtime_factory

TODtime_factory(inthour, intminute, intsecond, int|voidnanos)

Description

Function called to create time of day objects.

The default implementation just passes along its arguments to TOD.


Methodtimestamp_factory

Calendar.ISO.Day|Calendar.ISO.Fractiontimestamp_factory(intyear, intmonth, intday, int|voidhour, int|voidminute, int|voidsecond, int|voidnanos, int|voidtz_hour, int|voidtz_minute)

Description

Function called to create timestamp and date objects.

Note

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.


Methoduser_defined_factory

mixeduser_defined_factory(string(8bit)raw, mapping(string:mixed) field_info, intfield_number)

Description

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.


Methoduuid_factory

Standards.UUID.UUIDuuid_factory(string(8bit)raw_uuid)

Description

Function called to create UUID/GUID objects.

Class Sql.odbc.TypedResult.TOD

Description

Time of day.


Variablehour
Variableminute
Variablesecond
Variablenanos

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


Method__create__

protectedlocalvoid__create__(inthour, intminute, intsecond, int|voidnanos)


Methodcreate

Sql.odbc.TypedResult.TODSql.odbc.TypedResult.TOD(inthour, intminute, intsecond, int|voidnanos)

Module Odbc

Description

Low-level interface to Open DataBase Connectivity SQL-drivers.

Note

You typically don't want to access this module directly, but instead use Sql.Sql() with an "odbc://" or "dsn://" URL.

See also

Sql.Sql()


Methodconnect_lock

boolconnect_lock(void|intenable)

Description

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.

Parameter enable

Enables the mutex if nonzero, disables it otherwise. The state is not changed if this argument is left out.

Returns

The old state of the flag.

Note

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.


Methodlist_dbs

array(string) list_dbs()

Description

List the configured ODBC database sources.

Class Odbc.odbc

Description

Low-level connection to an ODBC or DSN database.

Note

You typically don't want to access this module directly, but instead use the Sql.odbc or Sql.dsn created by Sql.Sql().

See also

Sql.odbc, Sql.dsn


InheritConnection

inherit __builtin.Sql.Connection : Connection

Class Odbc.odbc.Result


InheritResult

inherit __builtin.Sql.Result : Result


Methodcreate

Odbc.odbc.ResultOdbc.odbc.Result()


Methodeof

inteof()


Methodexecute

intexecute(string(16bit)query)


Methodfetch_fields

array(int|mapping(string:mixed)) fetch_fields()


Methodfetch_row

int|array(string|float|int) fetch_row()


Methodlist_tables

intlist_tables(string|voidtable_name_pattern)


Methodnext_result

this_programnext_result()


Methodnum_fields

intnum_fields()


Methodnum_rows

intnum_rows()


Methodseek

voidseek()

Class Odbc.odbc.TypedResult


InheritResult

inherit Result : Result


Methodfetch_row

int|array(string|float|int|object) fetch_row()


Methodscale_numeric

Gmp.mpq|intscale_numeric(intmantissa, intscale)

Description

Function called to scale mantissa by a factor 10->pow(scale).


Methodtime_factory

TODtime_factory(inthour, intminute, intsecond, int|voidnanos)

Description

Function called to create time of day objects.


Methodtimestamp_factory

Calendar.ISO.Day|Calendar.ISO.Fractiontimestamp_factory(intyear, intmonth, intday, int|voidhour, int|voidminute, int|voidsecond, int|voidnanos, int|voidtz_hour, int|voidtz_minute)

Description

Function called to create timestamp and date objects.

Note

The tz_hour and tz_minute arguments are currently not generated by the low-level code.


Methoduser_defined_factory

mixeduser_defined_factory(string(8bit)raw, mapping(string:mixed) field_info, intfield_number)

Description

Function called to create representations of user-defined types.


Methoduuid_factory

Standards.UUID.UUIDuuid_factory(string(8bit)raw_uuid)

Description

Function called to create UUID/GUID objects.

14.2.7. Other

Module Sql

Description

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.

Example

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 * ","; }


VariableNULL

Val.Null Sql.NULL

Description

The SQL NULL value.

Deprecated

Replaced by Val.null.

See also

Val.null


Methodcensor_sql_url

stringcensor_sql_url(stringsql_url)

Description

Redact the password (if any) from an Sql-url.

Parameter sql_url

Sql-url possibly containing an unredacted password.

Returns

Returns the same Sql-url but with the password (if any) replaced by the string "CENSORED".

Class Sql.FutureResult

Description

The result from Promise.


InheritFutureResult

inherit __builtin.Sql.FutureResult : FutureResult

Class Sql.Null

Description

Class used to implement the SQL NULL value.

Deprecated

Replaced by Val.Null.

See also

Val.Null, Val.null

Class Sql.Promise

Description

The result from Connection.promise_query().


InheritPromise

inherit __builtin.Sql.Promise : Promise

Class Sql.mysql_result

Deprecated

Replaced by Result.

Class Sql.mysqls_result

Deprecated

Replaced by Result.

Class Sql.null

Description

The NULL Sql handler.

This is an empty Sql handler typically used to test other functionality of the Sql module.


InheritConnection

inherit __builtin.Sql.Connection : Connection


Methodbig_query

variantSql.Resultbig_query(stringquery)

Returns

Returns an array with a single element:

"query" : string

The query string before formating.

"bindings_query" : string

The query string before bindings having been inserted.

"formatted_query" : string

The formatted query.


Methodbig_query

variantSql.Resultbig_query(stringquery, mappingbindings)

Returns

Returns an array with a single element:

"query" : string

The query string before formating.

"bindings_query" : string

The query string before bindings having been inserted.

"bindings" : string

sprintf("%O", bindings).

"formatted_query" : string

The formatted query.


Methodbig_query

variantSql.Resultbig_query(stringquery, string|int|float|objectextraarg, string|int|float|object ... extraargs)

Returns

Returns an array with a single element:

"query" : string

The query string before formating.

"bindings_query" : string

The query string before bindings having been inserted.

"bindings" : string

sprintf("%O", bindings).

"formatted_query" : string

The formatted query.

"args" : string

sprintf("%O", ({extraarg}) + extraargs).


Methodquote

stringquote(strings)

Returns

sprintf("quote(%q)", s).

Class Sql.postgres_result

Description

Sql.postgres_result contains the result of a Postgres-query. See Sql.postgres for a description of this program's functions.


Inheritpostgres_result

inherit Postgres.postgres_result : postgres_result

Class Sql.sql_result

Description

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.


InheritResult

inherit __builtin.Sql.Result : Result


Variablemaster_res

array|__builtin.Sql.Result Sql.sql_result.master_res

Description

The actual result.


Methodfetch_json_result

stringfetch_json_result()

Description

Fetch remaining result as JSON-encoded data.


Methodseek

voidseek(intskip)

Description

Skip past a number of rows.

Parameter skip

Number of rows to skip.

Class Sql.sqlite

Description

Interface to SQLite3 databases.


InheritSQLite

inherit SQLite.SQLite : SQLite


Methodlist_fields

arraylist_fields(stringn, string|voidwild)


Methodlist_tables

arraylist_tables(string|voidn)

Class Sql.tds

Description

The TDS SQL-protocol.

This protocol is used by Sybase and Microsoft's SQL-servers.

Example

Sql.Sql con = Sql.Sql("tds://user:pass@host/database");

See also

Sql.Sql()


Methodcreate

Sql.tdsSql.tds(string|voidserver, string|voiddatabase, string|voiduser, string|voidpassword, mapping|voidoptions)

Description

Connect to a remote SQL server via the TDS protocol.

Parameter server

Server to connect to.

Parameter database

Database to connect to.

Parameter user

User to access as.

An explicit domain may be specified by preceeding the user name with the domain name and a '\\'.

Parameter password

Password to access with.

Usually accessed via Sql.Sql().

See also

Sql.Sql()


Methoderror

stringerror()

Description

Return the last error (or possibly the last warning or informational message).


Methodinsert_id

intinsert_id()

Description

Fetch the identity of the last insert (if available).

This performs the query "SELECT @@identity AS insert_id".

Returns

Returns the identity of the last insert as an integer if available. Otherwise returns 0 (zero).


Methodserver_info

stringserver_info()

Description

Return a string describing the server.


Methodtds_error

protectedvoidtds_error(stringmsg, mixed ... args)

Description

Format and report an error.

Enum Sql.tds.EnvType

Description

Environment types.

Used by TDS_ENV_CHANGE_TOKEN


ConstantTDS_ENV_DATABASE
ConstantTDS_ENV_LANG
ConstantTDS_ENV_CHARSET
ConstantTDS_ENV_PACKSIZE
ConstantTDS_ENV_LCID
ConstantTDS_ENV_SQLCOLLATION

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

Enum Sql.tds.FieldType

Description

Field types.


ConstantSYBBINARY
ConstantSYBBIT
ConstantSYBBITN
ConstantSYBCHAR
ConstantSYBDATETIME
ConstantSYBDATETIME4
ConstantSYBDATETIMN
ConstantSYBDECIMAL
ConstantSYBFLT8
ConstantSYBFLTN
ConstantSYBIMAGE
ConstantSYBINT1
ConstantSYBINT2
ConstantSYBINT4
ConstantSYBINT8
ConstantSYBINTN
ConstantSYBLONGBINARY
ConstantSYBMONEY
ConstantSYBMONEY4
ConstantSYBMONEYN
ConstantSYBNTEXT
ConstantSYBNUMERIC
ConstantSYBNVARCHAR
ConstantSYBREAL
ConstantSYBSINT1
ConstantSYBTEXT
ConstantSYBUINT2
ConstantSYBUINT4
ConstantSYBUINT8
ConstantSYBUNIQUE
ConstantSYBVARBINARY
ConstantSYBVARCHAR
ConstantSYBVARIANT
ConstantSYBVOID
ConstantXSYBBINARY
ConstantXSYBCHAR
ConstantXSYBNCHAR
ConstantXSYBNVARCHAR
ConstantXSYBVARBINARY
ConstantXSYBVARCHAR

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


ConstantTDS_UT_TIMESTAMP

constant Sql.tds.TDS_UT_TIMESTAMP

Enum Sql.tds.Token

Description

Tokens that may occur in the answers from the server.


ConstantTDS5_PARAMFMT2_TOKEN
ConstantTDS_LANGUAGE_TOKEN
ConstantTDS_ORDERBY2_TOKEN
ConstantTDS_ROWFMT2_TOKEN
ConstantTDS_LOGOUT_TOKEN
ConstantTDS_RETURNSTATUS_TOKEN
ConstantTDS_PROCID_TOKEN
ConstantTDS7_RESULT_TOKEN
ConstantTDS7_COMPUTE_RESULT_TOKEN
ConstantTDS_COLNAME_TOKEN
ConstantTDS_COLFMT_TOKEN
ConstantTDS_DYNAMIC2_TOKEN
ConstantTDS_TABNAME_TOKEN
ConstantTDS_COLINFO_TOKEN
ConstantTDS_OPTIONCMD_TOKEN
ConstantTDS_COMPUTE_NAMES_TOKEN
ConstantTDS_COMPUTE_RESULT_TOKEN
ConstantTDS_ORDERBY_TOKEN
ConstantTDS_ERROR_TOKEN
ConstantTDS_INFO_TOKEN
ConstantTDS_PARAM_TOKEN
ConstantTDS_LOGINACK_TOKEN
ConstantTDS_CONTROL_TOKEN
ConstantTDS_ROW_TOKEN
ConstantTDS_CMP_ROW_TOKEN
ConstantTDS5_PARAMS_TOKEN
ConstantTDS_CAPABILITY_TOKEN
ConstantTDS_ENVCHANGE_TOKEN
ConstantTDS_EED_TOKEN
ConstantTDS_DBRPC_TOKEN
ConstantTDS5_DYNAMIC_TOKEN
ConstantTDS5_PARAMFMT_TOKEN
ConstantTDS_AUTH_TOKEN
ConstantTDS_RESULT_TOKEN
ConstantTDS_DONE_TOKEN
ConstantTDS_DONEPROC_TOKEN
ConstantTDS_DONEINPROC_TOKEN

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


ConstantTDS_CURCLOSE_TOKEN
ConstantTDS_CURFETCH_TOKEN
ConstantTDS_CURINFO_TOKEN
ConstantTDS_CUROPEN_TOKEN
ConstantTDS_CURDECLARE_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


ConstantTDS_ERROR
ConstantTDS_DONT_RETURN

constant Sql.tds.TDS_ERROR
constant Sql.tds.TDS_DONT_RETURN

Class Sql.tds.Connection

Description

A connection to a TDS server.


Variablesocket

Stdio.File Sql.tds.Connection.socket

Description

The actual TCP connection.


Methodsend_packet

InPacket|zerosend_packet(Packetp, intflag, int|voidlast)

Description

Send a packet to the TDS server.

Note

May only be called when the connection is idle.

Returns

If last is true an InPacket with the result will be returned.

Class Sql.tds.Connection.InPacket

Description

An incoming packet from the TDS server.

Class Sql.tds.Connection.Packet

Description

An outgoing packet to the TDS server.

Class Sql.tds.big_query

Description

A query result set.


Methodcreate

Sql.tds.big_querySql.tds.big_query(string|compile_queryquery)

Description

Execute a query against the database.

Parameter query

The query to execute. This can either be a string, or a compiled query.

See also

compile_query()


Methodfetch_fields

array(mapping(string:mixed)) fetch_fields()

Description

Fetch a description of the fields in the result.

Returns

Returns an array with a mapping for each of the fields in the result.

The mappings contain the following information:

  • Standard fields:

    "name" : string

    The name of the field.

    "table" : string|void

    The name of the table (if available).

  • TDS-specific fields:

    "nullable" : bool

    1 if the field may contain NULL.

    "writeable" : bool

    1 if the field may be changed.

    "identity" : bool

    1 if the field is the identity for the row.

    "column_size" : int

    Width of the field.

    "timestamp" : bool

    Time stamp information for last change is available.

    "column_prec" : int|void

    Precision of the field.

    "column_scale" : int|void

    Scale exponent of the field.

    "usertype" : int

    Internal use only.

    "flags" : int
    "column_type" : int
    "cardinal_type" : int
    "varint_size" : int

Methodfetch_row

int|array(string|int) fetch_row()

Description

Fetch the next row from the result set.

Returns

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.

Class Sql.tds.compile_query

Description

A compiled query.


Methodcreate

Sql.tds.compile_querySql.tds.compile_query(stringquery)

Description

Compile a query.

See also

big_query()

Module Sql.pgsql_util

Description

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.

Note

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.


Variablelocal_backend

finalPike.Backend Sql.pgsql_util.local_backend

Description

The instance of the pgsql dedicated backend.


Methodregister_backend

finalvoidregister_backend(proxyclient)

Description

Registers yourself as a user of this backend. If the backend has not been started yet, it will be spawned automatically.

Class Sql.pgsql_util.Result

Description

The result object returned by Sql.pgsql()->big_query(), except for the noted differences it behaves the same as Sql.Result.

See also

Sql.Result, Sql.pgsql, Sql.Sql, Sql.pgsql()->big_query()


InheritResult

inherit __builtin.Sql.Result : Result


Methodaffected_rows

finalintaffected_rows()

Description

Returns the number of affected rows by this query.

See also

Sql.Result()->affected_rows()


Methodeof

finalinteof()

See also

Sql.Result()->eof()


Methodfetch_fields

finalarray(mapping(string:mixed)) fetch_fields()

See also

Sql.Result()->fetch_fields()


Methodfetch_row

finalarray(mixed)|zerofetch_row()

Returns

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.

See also

eof(), send_row()


Methodfetch_row_array

finalarray(array(mixed))|zerofetch_row_array()

Returns

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.

See also

eof(), fetch_row()


Methodnum_fields

finalintnum_fields()

See also

Sql.Result()->num_fields()


Methodnum_rows

finalintnum_rows()

Note

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.

See also

Sql.Result()->num_rows()


Methodsend_row

finalvoidsend_row(void|string|array(string) copydata)

Parameter copydata

When 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.

See also

fetch_row(), eof()


Methodset_result_array_callback

finalvoidset_result_array_callback(function(Result, array(array(mixed)), mixed ... :void) callback, mixed ... args)

Description

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).

See also

fetch_row()


Methodset_result_callback

finalvoidset_result_callback(function(Result, array(mixed), mixed ... :void) callback, mixed ... args)

Description

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).

See also

fetch_row()


Methodstatus_command_complete

finalstringstatus_command_complete()

Description

Returns the command-complete status for this query.

See also

Sql.Result()->status_command_complete()

Class Sql.pgsql_util.sql_result

Deprecated

Replaced by Result.

Module Sql.sql_util

Description

Some SQL utility functions


Variablezero

protectedZeroWrapper Sql.sql_util.zero

Description

Instance of ZeroWrapper used by handle_extraargs().


Methodemulate_bindings

stringemulate_bindings(stringquery, mapping(string|int:mixed)|voidbindings, void|objectdriver)

Description

Build a raw SQL query, given the cooked query and the variable bindings It's meant to be used as an emulation engine for those drivers not providing such a behaviour directly (i.e. Oracle). The raw query can contain some variables (identified by prefixing a colon to a name or a number (i.e. ":var" or ":2"). They will be replaced by the corresponding value in the mapping.

Parameter query

The query.

Parameter bindings

Optional mapping containing the variable bindings. Make sure that no confusion is possible in the query. If necessary, change the variables' names.


Methodfallback

voidfallback()

Description

Throw an error in case an unimplemented function is called.


Methodhandle_extraargs

array(string|mapping(string|int:mixed)) handle_extraargs(stringquery, array(mixed) extraargs, mapping(string|int:mixed)|voidbindings)

Description

Handle sprintf-based quoted arguments

Parameter query

The query as sent to one of the query functions.

Parameter extraargs

The arguments following the query.

Parameter bindings

Optional bindings mapping to which additional bindings will be added. It's returned as the second element in the return value. A new mapping is used if this isn't specified.

Returns

Returns an array with two elements:

Array
string0

The query altered to use bindings-syntax.

mapping(string|int:mixed) 1

A bindings mapping. Zero if no bindings were added.


Methodquote

stringquote(strings)

Description

Quote a string so that it can safely be put in a query.

Parameter s

String to quote.

Class Sql.sql_util.MySQLBrokenUnicodeWrapper

Description

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.


InheritUnicodeWrapper

inherit UnicodeWrapper : UnicodeWrapper

Class Sql.sql_util.MySQLUnicodeWrapper

Description

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.

Note

There's normally no need to use this class directly. It's used automatically when mysql.set_unicode_decode_mode is activated.


InheritUnicodeWrapper

inherit UnicodeWrapper : UnicodeWrapper

Class Sql.sql_util.UnicodeWrapper

Description

Result object wrapper performing utf8 decoding of all fields.


InheritResult

inherit Sql.Result : Result


Variablefield_info

protectedarray(int|mapping(string:mixed)) Sql.sql_util.UnicodeWrapper.field_info

Description

Cached fetch_fields() result.


Variablemaster_result

protectedobject Sql.sql_util.UnicodeWrapper.master_result


Method__create__

protectedlocalvoid__create__(objectmaster_result)


Methodcreate

Sql.sql_util.UnicodeWrapperSql.sql_util.UnicodeWrapper(objectmaster_result)


Methodeof

booleof()

Description

Returns 1 if there are no more rows in the result.


Methodfetch_fields

array(int|mapping(string:mixed)) fetch_fields()

Description

Returns Information about the fields in the result.

The following fields are converted from UTF8 if present:

"name" : string

The name of the field. Always present.

"table" : string

The table the field is from. Not present from all databases.

"default" : string

The default value for the column. Not available from all databases.


Methodfetch_json_result

int|stringfetch_json_result()

Description

JSON is always utf8 default, do nothing.


Methodfetch_row

int|array(string) fetch_row()

Description

Fetch the next row from the result.

All strings in the result are decoded from UTF8.


Methodnum_fields

intnum_fields()

Description

Returns the number of fields in the result.


Methodnum_rows

intnum_rows()

Description

Returns the number of rows in the result.


Methodseek

voidseek(introws)

Description

Skip ahead the specified number of rows.

Class Sql.sql_util.ZeroWrapper

Description

Wrapper to handle zero.

See also

zero


Method_sprintf

stringsprintf(stringformat, ... Sql.sql_util.ZeroWrapperarg ... )

Returns

Returns the following:

"NULL"

If fmt is 's'.

"ZeroWrapper()"

If fmt is 'O'.

Otherwise it formats a 0 (zero).

14.3. Key-value stores

14.3.1. Gdbm

Module Gdbm

Description

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.

Class Gdbm.DB


Method_indices

array(string(8bit)) indices(Gdbm.DBarg)

Description

Provides overloading of indices.

Note

Mainly useful when debugging, the returned list might not fit in memory for large databases.


Method_m_delete

string(8bit)m_delete(Gdbm.DBfrom, string(8bit)key)

Description

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.


Method_values

array(string(8bit)) values(Gdbm.DBarg)

Description

Provides overloading of values.

Note

Mainly useful when debugging, the returned list might not fit in memory for large databases.


Methodfetch
Method`[]

string(8bit)fetch(string(8bit)key)
string(8bit) res = Gdbm.DB()[ key ]

Description

Return the data associated with the key 'key' in the database. If there was no such key in the database, zero is returned.


Method`[]=

Gdbm.DB()[ key ] = data

Description

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.

Example

gdbm[key] = data;

Returns

Returns data on success.

See also

store()


Methodclose

voidclose()

Description

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)


Methodcreate

Gdbm.DBGdbm.DB(void|stringfile, void|string(99..119)mode)

Description

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:

r

Open database for reading

w

Open database for writing

c

Create database if it does not exist

t

Overwrite existing database

f

Fast mode

s

Synchronous mode

l

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".

Note

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.


Methoddelete

booldelete(stringkey)

Description

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.


Methodfirstkey

stringfirstkey()

Description

Return the first key in the database, this can be any key in the database.

Used together with nextkey the databse can be iterated.

Note

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.

Example
// Write the contents of the databasefor(key=gdbm->firstkey(); k; k=gdbm->nextkey(k))
   write(k+":"+gdbm->fetch(k)+"\n");

Or, using foreach

// Write the contents of the databaseforeach( db;string key;string value )
   write(key+":"+value+"\n");

Methodnextkey

string(8bit)nextkey(string(8bit)key)

Description

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.

Note

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.

Example
// Write the contents of the databasefor(key=gdbm->firstkey(); k; k=gdbm->nextkey(k))
   write(k+":"+gdbm->fetch(k)+"\n");

Or, using foreach

// Write the contents of the databaseforeach( db;string key;string value )
   write(key+":"+value+"\n");

Methodreorganize

intreorganize()

Description

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.


Methodstore

intstore(stringkey, stringdata)

Description

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.

Example

gdbm->store(key, data);

Returns

Returns 1 on success.

Note

Note that the returned value differs from that of `[]=().

See also

`[]=()


Methodsync

voidsync()

Description

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.

Class Gdbm.Iterator

Description

Object keeping track of an iteration over a DB

Note

Can not be usefully constructed manually, instead use the database as the first argument to foreach or predef::get_iterator


Inheritpredef::Iterator

inherit predef::Iterator : predef::Iterator

14.3.2. Yabu

Module Yabu

Description

Yabu is an all purpose transaction database written in pike, used to store data records associated with a unique key.

Class Yabu.DB

Description

A Yabu database instance


Method_indices

arrayindices(Yabu.DBarg)

Description

Return a list of all tables


Method_values

arrayvalues(Yabu.DBarg)

Description

Return all tables as an array


Method`[]

mixed res = Yabu.DB()[ handle ]

Description

Equivalent to table


Methodascii_statistics

stringascii_statistics()

Description

Return information about all tables in a human readable format


Methodclose

voidclose()

Description

Close the database.


Methodcreate

Yabu.DBYabu.DB(stringdir, stringmode)

Description

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".


Methodlist_tables

array(string) list_tables()

Description

Return a list of all tables in the database


Methodpurge

voidpurge()

Description

Delete the database.


Methodreorganize

intreorganize(float|voidratio)

Description

Call Table.reorganize in all tables


Methodstatistics

mapping(string:int) statistics()

Description

Return information about all tables


Methodsync

voidsync()

Description

Sync all tables


Methodtable

Tabletable(stringhandle)

Description

Return the Table object for the named table

Class Yabu.LookupDB

Description

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.


InheritDB

inherit DB : DB


Methodcreate

Yabu.LookupDBYabu.LookupDB(stringdir, stringmode, mapping|voidoptions)

Description

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

index_size : int(1..)

Class Yabu.Table

Description

The basic Yabu table


Method_indices

arrayindices(Yabu.Tablearg)

Description

Equivalent to list_keys()


Method_m_delete

mixedm_delete(Yabu.Tablefrom, stringhandle)

Description

Equivalent to delete


Method_values

arrayvalues(Yabu.Tablearg)

Description

Fetches all keys from disk


Method`[]

mixed res = Yabu.Table()[ handle ]

Description

Equivalent to get


Method`[]=

Yabu.Table()[ handle ] = x

Description

Equivalent to set


Methodascii_statistics

stringascii_statistics()

Description

Return information about all tables in a human redable format


Methodclose

voidclose()

Description

Close the table


Methoddelete

voiddelete(stringhandle)

Description

Remove a key


Methodget

mixedget(stringhandle)

Description

Get a key


Methodlist_keys

arraylist_keys()

Description

List all keys


Methodpurge

voidpurge()

Description

Close and delete the table from disk


Methodreorganize

intreorganize(float|voidratio)

Description

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.


Methodset

mixedset(stringhandle, mixedx)

Description

Set a key


Methodstatistics

mapping(string:string|int) statistics()

Description

Return information about the table.

"keys" : int

The number of keys

"size" : int

The on-disk space, in bytes

used : int 

Methodsync

voidsync()

Description

Synchronize. Usually done automatically


Methodtransaction

Transactiontransaction()

Description

Start a new transaction.

Class Yabu.Transaction

Description

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.


Method_indices

arrayindices(Yabu.Transactionarg)

Description

Identical to list_keys();


Method_m_delete

mixedm_delete(Yabu.Transactionfrom, stringhandle)

Description

Identical to delete


Method_values

arrayvalues(Yabu.Transactionarg)

Description

Identical to get(list_keys()][*]);


Method`[]

mixed res = Yabu.Transaction()[ handle ]

Description

Identical to get


Method`[]=

Yabu.Transaction()[ handle ] = x

Description

Identical to set


Methodcommit

voidcommit()

Description

Commit all changes done so far in the transaction to the table


Methoddelete

voiddelete(stringhandle)

Description

Delete handle from the database


Methodget

mixedget(stringhandle)

Description

Get the value of handle


Methodlist_keys

arraylist_keys()

Description

List all keys


Methodrollback

voidrollback()

Description

Undo all changes done so far in the transaction to the table


Methodset

mixedset(stringhandle, mixedx)

Description

Set handle to x