14. Database Access

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

  CLASS Sql.Sql

Description

Implements those functions that need not be present in all SQL-modules.


Variable master_sql

object master_sql

Description

Object to use for the actual SQL-queries.


Variable case_convert

int(0..1) 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



Method quote

string quote(string s)

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


Method encode_time

string encode_time(int t, int|void is_utc)

Description

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

Parameter t

Time to encode.

Parameter is_utc

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.


Method decode_time

int decode_time(string t, int|void want_utc)

Description

Converts a database time spec to a system time value.

Parameter t

Time spec to decode.

Parameter want_utc

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


Method encode_date

string encode_date(int t)

Description

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

Parameter t

Time to encode.


Method decode_date

int decode_date(string d)

Description

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

Parameter d

Date spec to decode.


Method encode_datetime

string encode_datetime(int t)

Description

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

Parameter t

Time to encode.


Method decode_datetime

int decode_datetime(string datetime)

Description

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

Parameter datetime

Date and time spec to decode.


Method create

void Sql.Sql(string host)
void Sql.Sql(string host, string db)
void Sql.Sql(string host, mapping(string:int|string) options)
void Sql.Sql(string host, string db, string user)
void Sql.Sql(string host, string db, string user, string password)
void Sql.Sql(string host, string db, string user, string password, mapping(string:int|string) options)
void Sql.Sql(object host)
void Sql.Sql(object host, string db)

Description

Create a new generic SQL object.

Parameter host
object

Use this object to access the SQL-database.

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

Support for options was added in Pike 7.3.


Method error

int|string error()

Description

Return last error message.


Method select_db

void select_db(string db)

Description

Select database to access.


Method compile_query

string|object compile_query(string q)

Description

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

Parameter q

SQL-query to compile.


Method handle_extraargs

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

Description

Handle sprintf-based quoted arguments


Method query

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

Description

Send an SQL query to the underlying SQL-server.

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 extraargs

This parameter, if specified, can be in two forms:

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

    mixed err = catch { query("SELECT foo FROM bar WHERE gazonk=:baz", ([":baz":"value"])); }; if(!intp(err)) werror("An error occured.");

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

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

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

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

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.


Method big_query

int|object big_query(object|string q, mixed ... extraargs)

Description

Send an SQL query to the underlying SQL-server. The result is returned as a Sql.sql_result object. This allows for having results larger than the available memory, and returning some more info about the result. Returns 0 if the query didn't return any result (e.g. INSERT or similar). For the other arguments, they are the same as for the query() function.


Method create_db

void create_db(string db)

Description

Create a new database.

Parameter db

Name of database to create.


Method drop_db

void drop_db(string db)

Description

Drop database

Parameter db

Name of database to drop.


Method shutdown

void shutdown()

Description

Shutdown a database server.


Method reload

void reload()

Description

Reload the tables.


Method server_info

string server_info()

Description

Return info about the current SQL-server.


Method host_info

string host_info()

Description

Return info about the connection to the SQL-server.


Method list_dbs

array(string) list_dbs(string|void wild)

Description

List available databases on this SQL-server.

Parameter wild

Optional wildcard to match against.


Method list_tables

array(string) list_tables(string|void wild)

Description

List tables available in the current database.

Parameter wild

Optional wildcard to match against.


Method list_fields

array(mapping(string:mixed)) list_fields(string table, string|void wild)

Description

List fields available in the specified table

Parameter table

Table to list the fields of.

Parameter wild

Optional wildcard to match against.

  CLASS Sql.msql

Description

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


Inherit msql

inherit Msql.msql : msql

  CLASS Sql.mysql

Description

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


Inherit mysql

inherit Mysql.mysql : mysql


Method quote

string quote(string s)

Description

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

Parameter s

String to quote.


Method encode_time

string encode_time(int time, void|int date)

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.


Method encode_date

string encode_date(int time)

Description

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

Parameter time

Time to encode.


Method encode_datetime

string encode_datetime(int time)

Description

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

Parameter time

Time to encode.


Method decode_time

int decode_time(string timestr, void|int date)

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.


Method decode_date

int decode_date(string datestr)

Description

Converts a database date-only spec to a system time value. Assumes 4-digit years.

Parameter datestr

Date spec to decode.


Method decode_datetime

int decode_datetime(string timestr)

Description

Converts a database date and time spec to a system time value. Can decode strings missing the time part.

Parameter datestr

Date and time spec to decode.


Method big_query

int|object big_query(string q, mapping(string|int:mixed)|void bindings)


Method is_keyword

int(0..1) is_keyword(string name)

Description

Return 1 if the argument name is a mysql keyword.

  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.


Inherit mysql

inherit Sql.mysql : mysql

  CLASS Sql.postgres

Description

This is an interface to the Postgres (Postgres95, pgsql) database server. This module may or may not be availible on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.

Note

Also note that this module uses blocking I/O I/O to connect to the server. Postgres is quite slow, and so you might want to consider this particular aspect. It is (at least should be) thread-safe, and so it can be used in a multithread environment.

The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the Pike interpreter.

"PGHOST"

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

"PGOPTIONS"

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

"PGPORT"

Sets the default port to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver).

"PGTTY"

Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing.

"PGDATABASE"

Sets the default database to connect to.

"PGREALM"

Sets the default realm for Kerberos authentication. I never used this, so I can't help you.


Refer to the Postgres documentation for further details.

See also

Sql.Sql , Postgres.postgres , Sql.postgres_result


Inherit mo

inherit Postgres.postgres : mo


Method select_db

void select_db(string dbname)

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


Method error

string error()

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


Method host_info

string host_info()

Description

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


Method reset

void reset()

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 availible through the generic SQL-interface.


Variable version

string 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 create

void Sql.postgres()
void Sql.postgres(string host, void|string database, void|string user, void|string password)

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.

The username and password arguments are silently ignored, since the Postgres C API doesn't allow to connect to the server as any user different than the user running the interface.

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

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


Method set_notify_callback

void set_notify_callback()
void set_notify_callback(function f)
void set_notify_callback(function f, int|float poll_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 occured. 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 availible through the generic SQL-interface.

FIXME

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


Method create_db

void create_db(string db)

Description

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

See also

drop_db


Method drop_db

void drop_db(string db)

Description

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

See also

create_db


Method server_info

string server_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.


Method list_dbs

array(string) list_dbs(void|string glob)

Description

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


Method list_tables

array(string) list_tables(void|string glob)

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.


Method list_fields

array(mapping(string:mixed)) list_fields(string table, void|string wild)

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.



Method big_query

int|object big_query(object|string q, mapping(string|int:mixed)|void bindings)

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.

See also

Sql.Sql , Sql.sql_result

  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.


Inherit postgres_result

inherit Postgres.postgres_result : postgres_result

  CLASS Sql.sql_result

Description

Implements the generic result of the SQL-interface. Used for return results from SQL.sql->big_query().


Variable master_res

object|array master_res

Description

The actual result.


Variable index

int index

Description

This is the number of the current row. (0 <= index < num_rows() )


Method create

void Sql.sql_result(object|array res)

Description

Create a new Sql.sql_result object

Parameter res

Result to use as base.


Method num_rows

int num_rows()

Description

Returns the number of rows in the result.


Method num_fields

int num_fields()

Description

Returns the number of fields in the result.


Method eof

int eof()

Description

Returns non-zero if there are no more rows.


Method fetch_fields

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

Description

Return information about the available fields.


Method seek

void seek(int skip)

Description

Skip past a number of rows.

Parameter skip

Number of rows to skip.


Method fetch_row

int|array(string|int) fetch_row()

Description

Fetch the next row from the result.

  Module Sql.sql_util

Description

Some SQL utility functions


Method quote

string Sql.sql_util.quote(string s)

Description

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

Parameter s

String to quote.


Method fallback

void Sql.sql_util.fallback()

Description

Throw an error in case an unimplemented function is called.


Method emulate_bindings

string Sql.sql_util.emulate_bindings(string query, mapping(string|int:mixed)|void bindings, void|object driver)

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.

  Module Mysql

Description

This module enables access to the Mysql database from within Pike. Use Sql.Sql for general database access.

Mysql is available from http://www.mysql.com.

See also

Mysql.mysql , Mysql.mysql_result , Sql.Sql

  CLASS Mysql.mysql

Description

Interface to the Mysql database.

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

Mysql is available from http://www.mysql.com.

See also

Mysql.mysql_result , Sql.Sql


Method create

void Mysql.mysql()
void Mysql.mysql(string host)
void Mysql.mysql(string host, string database)
void Mysql.mysql(string host, string database, string user)
void Mysql.mysql(string host, string database, string user, string password)
void Mysql.mysql(string host, string database, string user, string password, mapping(string:string|int) options)

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" : int(0..1)

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" : int(0..1)

Use named pipe to connect to server.

"mysql_local_infile" : int(0..1)

Enable use of LOCAL INFILE (security).

"mysql_charset_dir" : string

Change charset directory.

"mysql_charset_name" : string

Change charset name.

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

To use SSL-connections, set the SSL-parameters correctly. They corespond 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.


Method _sprintf

string _sprintf(int type, void|mapping flags)


Method affected_rows

int affected_rows()

Description

Returns the number of rows affected by the last query.


Method insert_id

int insert_id()

Description

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


Method error

string error()

Description

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

Returns 0 (zero) if there was no error.


Method select_db

void select_db(string database)

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


Method big_query

Mysql.mysql_result big_query(string query)

Description

Make an SQL query.

This function sends the SQL query query to the Mysql-server. The result of the query is returned as a Mysql.mysql_result object.

Returns 0 (zero) if the query didn't return any result (e.g. INSERT or similar).

See also

Mysql.mysql_result


Method create_db

void create_db(string database)

Description

Create a new database

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

See also

select_db() , drop_db()


Method drop_db

void drop_db(string database)

Description

Drop a database

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

See also

create_db() , select_db()


Method shutdown

void shutdown()

Description

Shutdown the Mysql-server

This function shuts down a running Mysql-server.

See also

reload()


Method reload

void reload()

Description

Reload security tables

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

See also

shutdown()


Method statistics

string statistics()

Description

Some Mysql-server statistics

This function returns some server statistics.

See also

server_info() , host_info() , protocol_info()


Method server_info

string server_info()

Description

Get the version number of the Mysql-server.

See also

statistics() , host_info() , protocol_info()


Method host_info

string host_info()

Description

Get information about the Mysql-server connection

See also

statistics() , server_info() , protocol_info()


Method protocol_info

int protocol_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()


Method list_dbs

Mysql.mysql_result list_dbs()
Mysql.mysql_result list_dbs(string wild)

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


Method list_tables

Mysql.mysql_result list_tables()
Mysql.mysql_result list_tables(string wild)

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


Method list_fields

array(int|mapping(string:mixed)) list_fields(string table)
array(int|mapping(string:mixed)) list_fields(string table, string wild)

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

"max_length" : int

The length of the longest element in this field.

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

"not_null"

This field may not be NULL.

"blob"

This field is a blob field.


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


Method list_processes

Mysql.mysql_result list_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


Method binary_data

int binary_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.

  CLASS Mysql.mysql_result

Description

Objects of this class contain the result from Mysql queries.

See also

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


Method create

void Mysql.mysql_result(Mysql.mysql connection)

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


Method num_rows

int num_rows()

Description

Number of rows in the result.

See also

num_fields()


Method num_fields

int num_fields()

Description

Number of fields in the result.

See also

num_rows()


Method field_seek

void field_seek(int field_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()


Method eof

int(0..1) eof()

Description

Sense end of result table.

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

See also

fetch_row()


Method fetch_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()


Method fetch_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()


Method seek

void seek(int rows)

Description

Skip ahead rows rows.

Note

Can only seek forward.

See also

fetch_row()


Method fetch_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()

  Module Postgres

  CLASS Postgres.postgres_result

Description

Contains the result of a Postgres-query.

See also

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


Method create

void Postgres.postgres_result(object o)

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.


Method num_rows

int num_rows()

Description

Returns the number of rows in the result.


Method num_fields

int num_fields()

Description

Returns the number of fields in the result.


Method fetch_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.


Method seek

void seek()

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.


Method fetch_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()

  CLASS Postgres.postgres

Description

This is an interface to the Postgres (Postgres95, pgsql) database server. This module may or may not be availible on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.

Please notice that unless you wish to specifically connect to a Postgres server, you'd better use the Sql.Sql , which is a server-independent sql-server-class. The interfaces to all existing sql-classes are consistent. Using Sql.Sql ensures that your Pike applications will run with any supported SQL server without changing a single line of code, at least for most common (and simple) operations.

The program Postgres.postgres provides the raw interface to the database. Many functions are not availible for this program. Therefore, its use is DEPRECATED. It is included in this documentation only for completeness' sake. Use Sql.postgres instead, or even better Sql.Sql

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.

Also note that this module uses blocking I/O I/O to connect to the server. Postgres is quite slow, and so you might want to consider this particular aspect. It is (at least should be) thread-safe, and so it can be used in a multithread environment.

The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the pike interpreter.

"PGHOST"

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

"PGOPTIONS"

Sets some extra flags for the frontend-backend connection. DO NOT SET unless you're sure of what you're doing.

"PGPORT"

Sets the default port to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver).

"PGTTY"

Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing.

"PGDATABASE"

Sets the default database to connect to.

"PGREALM"

Sets the default realm for Kerberos authentication. I never used this, so I can't help you.


Refer to the Postgres documentation for further details.

See also

Sql.Sql , Sql.postgres , Sql.postgres_result


Variable version

string 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 create

void Postgres.postgres()
void Postgres.postgres(string host, void|string database, void|int port)

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

You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.

You don't need bothering about syncronizing the connection to the database: it is automatically closed (and the database is sync-ed) when the object is destroyed.

See also

Sql.postgres , Sql.Sql , select_db


Method select_db

void select_db(string dbname)

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


Method big_query

Sql.postgres_result big_query(string sqlquery)

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 exactly the same as Postgres.postgres_result .

See also

Sql.Sql , Sql.sql_result


Method error

string error()

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


Method reset

void reset()

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 availible through the generic SQL-interface.


Method _set_notify_callback

void _set_notify_callback()
void _set_notify_callback(function 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 occured. 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 availible through the generic SQL-interface

See also

Sql.postgres


Method host_info

string host_info()

Description

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

  Module Mird

  CLASS Mird.Mird

Description

The Mird module doubles as the Mird database class.


Method create

void Mird.Mird(string filename, void|mapping options)

Description

Opens the database using the given filename. Note that the file filename +".journal" will be created too.

Possible options:

"flags" : string

Database flags, see below.

"block_size" : int

Database block size; must be even 2^n. Database size is limited to 2^32 blocks, ie block_size*2^32 (8192*2^(32-5) is approximately 1Tb) (2048)

"frag_bits" : int

This sets the number of fragments in a fragmented block, and address bits to locate them. The number of fragments in a fragmented block is equal to 2^frag_bits-1, by default 2^5-1=32-1=31.

This steals bits from the 32 bit address used to point out a chunk. (5)

"hashtrie_bits" : int

This is the number of bits in each hash-trie hash; 4*2^n must be a lot less than block_size. (5)

"cache_size" : int

This is the number of blocks cached from the database; this is used for both the read- and the write cache. Note that the memory usage is constant, approximately block_size*cache_size bytes. (32)

"cache_search_length" : int

This is the closed hash maximum search length to find a block in the cache; note that this will result in linear time usage. (8)

"max_free_frag_blocks" : int

This is how many blocks with free space that is kept in a list to be used when a transaction is running. The closest fit of usage of these blocks are where the data will be stored. (10)

"file_mode" : int

This is the default mode the files are opened with; although affected by UMASK (0666)

"journal_readback_n" : int

This is how many journal entries are read back at once at transaction finish, cancel or copy time. Bytes needed is approximately 24*this number (200)


"flags" is a string with any of these characters:

'r'

Readonly.

'R'

Readonly in a live system (one process writes, many reads).

'n'

Don't create if it doesn't exist.

'x'

Exclusive (always create).

's'

Call fsync(3) when finishing transactions.

'S'

Call sync(2) after fsync(3).

'j'

Complain if journal file is gone missing.



Method table

Table table(string name)

Description

Gives back the table object for the specified table in the database.


Method vtable

vTable vtable(string name)
vzTable vtable(string name)

Description

A vTable is just like a Table , except that the values can be anything, not just strings. The data is encode_value() 'ed before storage.

A vzTable is just like a vTable , except that the values are both encoded and then packed using Gz. Only available if Pike supports Gz.


Method table_name

string table_name(int id)

Description

Returns the name of the table with id id .


Method new_stringkey_table
Method new_hashkey_table

Table new_stringkey_table(string name)
Table new_hashkey_table(string name)

Description

Create a new table in the database.

Note

A transaction will be created when calling these on the Database level.


Method transaction

Transaction transaction()

Description

Creates a new transaction


Method tables

array(string) tables()

Description

Gives a list of all tables in the database.


Method sync
Method sync_please

object sync()
object sync_please()

Description

Syncs the database (syncs against disc and starts to reuse any space that is freed), sync_please() does this when the last living transaction is finished.


Method sync_loop

object sync_loop(int seconds)

Description

Starts a call_out loop that will call sync_please once every given interval. 0 or fewer seconds will shut down the loop. Default is 1 minute (60 seconds).


Method close
Method destroy

void close()
void destroy()

Description

Syncs and closes the database

  CLASS Mird.Mird.Table

Description

A Mird table has approximately the same characteristics as a Pike mapping. You store key:value pairs in it, you get values from keys, you delete keys.

For convinience, the Table object overloads `[](), `[]=(), _indices() and _values().


Method create

void Mird.Mird.Table(object parent, int table_id)


Method scanner

Mird.Glue.Scanner scanner()
Mird.Glue.Scanner scanner(int key)

Description

Creates a scanner over the called table; if key is given, continue at that key (as returned from Mird.Glue.Scanner.next_key() ).

A scanner can be used to loop over all elements in a table.


Method `[]
Method `[]=
Method _indices
Method _values

string `[](int|string key)
string `[]=(int|string key, string value)
array(int|string) _indices()
array(int|string) _values()

Description

Get and store key:value pairs, list the table.

Note

All storage in the table will create a transaction, if we're not already in a transaction.


Method cast

mapping cast("mapping")
array cast("array")

Description

It is possible to cast the Table to a mapping or array; this will read all contents of the table, so beware if you have a big table.

  CLASS Mird.Mird.vTable

Description

A vTable is just like a Table , except that the values can be anything, not just strings. The data is encode_value() 'ed before storage.


Inherit v0Table

inherit v0Table : v0Table

Description

inherits Table

  CLASS Mird.Mird.vzTable

Description

This is just like a normal table, but all data is encode_value()ed, and gzipped, and unpacked if reading, for convinience. Only available when Pike supports Gz.


Inherit v0Table

inherit v0Table : v0Table

Description

inherits Table

  CLASS Mird.Mird.Transaction

Description

A Transaction is the environment which in a change to the database can be made. Simplified, the database is not changed until the transaction is closed. More then one change to the database can be performed within one transaction.

If the same entries in the database were changed during the lifespan of the transaction, it cannot be closed but has to be cancelled.


Method table
Method vtable
Method vztable

Table table(string name)
vTable vtable(string name)
vzTable vztable(string name)

Description

Creates a Table , vTable or vzTable object for the specified table, which emulates a mapping and in which you can make changes to the database or do lookups.


Method new_stringkey_table
Method new_hashkey_table

Table new_stringkey_table(string name)
Table new_hashkey_table(string name)

Description

Creates a new table in the database. A stringkey table is a mapping from string to string, and a hashkey table is mapping from int to string.


Method delete_table

void delete_table(string name)

Description

Removes a table and all items in it


Method tables

array(string) tables()

Description

Returns the names of the tables in the database.


Method close

void close()

Description

Finishes a transaction. This throws exceptions if there were conflicts.


Method cancel
Method destroy

void cancel()
void destroy()

Description

Cancels (rewinds) a transaction.

  CLASS Mird.Mird.cTransaction

Description

Caching transaction


Inherit Transaction

inherit Transaction : Transaction

  Module Mird.Glue

  CLASS Mird.Glue.Mird


Method close
Method destroy

void close()
void destroy()

Description

This closes the database, ie

  1. cancels all ongoing transactions

  2. syncs the database (flushes caches, and frees all unused blocks)

  3. destroys the database object

Call this or destroy the database object when you want the database closed.


Method create

void Mird.Glue.Mird(string filename)
void Mird.Glue.Mird(string filename, mapping options)

Description

"flags" : string

database flags, see below

"block_size" : int

database block size; must be even 2^n database size is limited to 2^32 blocks, ie block_size*2^32 (8192*2^(32-5) is approximately 1Tb) (2048)

"frag_bits" : int

this sets the number of frags in a fragmented block, and address bits to locate them. the number of frags in a fragmented block is equal to 2^frag_bits-1, per default 2^5-1=32-1=31 This steals bits from the 32 bit address used to point out a chunk. (5)

"hashtrie_bits" : int

this is the number of bits in each hash-trie hash; 4*2^n must be a lot less then block_size (5)

"cache_size" : int

this is the number of blocks cached from the database; this is used for both read- and write cache. Note that the memory usage is constant, approximately block_size*cache_size bytes. (32)

"cache_search_length" : int

this is the closed hash maximum search length to find a block in the cache; note that this will result in linear time usage. (8)

"max_free_frag_blocks" : int

this is how many blocks with free space that is kept in a list to be used when a transaction is running. The closest fit of usage of these blocks are where the data will be stored. (10)

"file_mode" : int

this is the default mode the files are opened with; although affected by UMASK (0666)

"journal_readback_n" : int

this is how many journal entries are read back at once at transaction finish, cancel or copy time bytes needed is approximately 24*this number (200)


flags is a string with any of these characters:
"r" - readonly
"R" - readonly in a live system (one process writes, many reads)
"n" - don't create if it doesn't exist
"x" - exclusive (always create)
"s" - call fsync when finishing transactions
"S" - call sync(2) after fsync
"j" - complain if journal file is gone missing


Method fetch

zero|string fetch(int table_id, int|string key)

Returns

the data value or zero_type if key wasn't found


Method first_unused_key
Method first_unused_table
Method _debug_cut

int first_unused_key(int table_id)
int first_unused_key(int table_id, int start_key)
int first_unused_table()
int first_unused_table(int start_table_id)
void _debug_cut()

Description

This closes the database without flushing or syncing. This should never be used and exist only for testing purposes.


Method sync
Method sync_please

object sync()
object sync_please()

Description

Syncs the database; this flushes all eventual caches and frees all unused blocks.

sync() can only be called when there is no ongoing transactions. sync_please() sets a marker that the database should be synced when the last transaction is finished or cancelled, which will eventually sync the database.

The usage could be in a call_out-loop,

   [...]
   call_out(sync_the_database,5*60);
   [...]
void sync_the_database()
{
   call_out(sync_please,5*60);
   my_mird->sync_please();
}

Returns

the object being called


Method _debug_check_free

void _debug_check_free()
void _debug_check_free(int(0..1) silent)

Description

this syncs the database and verifies the database free list. It prints stuff on stderr. It exists only for debug purpose and has no other use.


Method _debug_syscalls

int _debug_syscalls()

Returns

the number of syscalls the database has done so far

  CLASS Mird.Glue.Transaction

Description

A transaction object is enclosing a change in the database. It can either succeed in full or fail in full. If some other transaction has changed the same data as the current, the transaction closed last will fail (conflict).


Method cancel
Method destroy

void cancel()
void destroy()

Description

cancels (rewinds) a transaction


Method close

void close()

Description

closes a transaction; may cast exceptions if there are conflicts


Method create

void Mird.Glue.Transaction(Mird parent)

Description

Creates a new transaction within the given database.


Method store
Method delete
Method fetch

object store(int table_id, int|string key, string data)
object delete(int table_id, int|string key)
zero|string fetch(int table_id, int|string key)

Returns

the data value or zero_type if key wasn't found


Method delete_table

object delete_table(int table_id)

Description

delets a table from the database

Note

this can take some time, depending on how much data that is in that table


Method depend_table
Method first_unused_key
Method first_unused_table

object depend_table(int table_id)
int first_unused_key(int table_id)
int first_unused_key(int table_id, int start_key)
int first_unused_table()
int first_unused_table(int start_table_id)


Method new_hashkey_table
Method new_stringkey_table

object new_hashkey_table(int table_id)
object new_stringkey_table(int table_id)

Description

creates a table in the database


Method resolve

object resolve()

Description

Tries to resolve a transaction; casts an exception if there is a conflict. May be called more then once.

Returns

the object being called

  CLASS Mird.Glue.Scanner

Description

Objects of this class is used to read off all contents of a table in the database.


Method create

void Mird.Glue.Scanner(Mird database, int table_id)
void Mird.Glue.Scanner(Transaction transaction, int table_id)

Description

Creates a new scanner object, tuned to the given table and database or transaction.


Method next_key

int next_key()

Description

Gives back a possible argument to the constructor of Scanner ; allows the possibility to continue to scan even if the Scanner object is lost.


Method read

zero|mapping(string|int:string) read(int n)

Description

Reads some tupels from the table the scanner is directed against; the size of the resulting mapping is close to this number.

Returns

a mapping of the next (about) n tupels in the table, or zero if there is no more tupels in the table

Note

a buffer directly depending on this size is allocated; it's not recommended doing a "read(0x7fffffff)".

  Module Gdbm

  CLASS Gdbm.gdbm


Method create

void Gdbm.gdbm(void|string file, void|string 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


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. This will probably be done automatically in the future.


Method fetch
Method `[]

string fetch(string key)
string `[](string 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 delete

int(0..1) delete(string key)

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.


Method firstkey

string firstkey()

Description

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


Method nextkey

string nextkey(string 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.

Example

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


Method store
Method `[]=

int store(string key, string data)
int `[]=(string key, string 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;


Method reorganize

int reorganize()

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.


Method sync

void sync()

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.


Method close

void close()

Description

Closes the database.