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

    Variable NULL

      ``Val.Null` Sql.NULL`

    Description

      The SQL NULL value.

    Deprecated

      Replaced by `Val.null`.

    See also

      `Val.null`

    Method censor_sql_url

      ``string` censor_sql_url(`string` `sql_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.Null

      Description

        Class used to implement the SQL NULL value.

      Deprecated

        Replaced by `Val.Null`.

      See also

        `Val.Null`, `Val.null`

    ## Class Sql.Sql

      Description

        This class encapsulates a connection to an SQL server. It is a generic interface on top of the DB server specific implementations. That doesn't mean that there aren't plenty of server specific characteristics that still shine through, though.

        This class also 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.

      Method affected_rows

        ``int` affected_rows()`

      Method big_query

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

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns the results in untyped mode.

        For the arguments, please see the `query()` function.

        The result is returned as an `Sql.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. Returns `0` if the query didn't return any result (e.g. `INSERT` or similar).

      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`

      Method big_typed_query

        ``int`|`object` big_typed_query(`object`|`string` `q`, `mixed` ... `extraargs`)`

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns the results in typed mode.

        For the arguments, please see the `query()` function.

        The result is returned as an `Sql.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. Returns `0` if the query didn't return any result (e.g. `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

        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`

      Variable case_convert

        ``bool` Sql.Sql.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 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 to the query functions.

      Parameter `q`

        SQL-query to compile.

      See also

        `query`, `typed_query`, `big_query`, `big_typed_query`, `streaming_query`, `streaming_typed_query`

      Method create

        `Sql.Sql Sql.Sql(`string` `host`)`
        `Sql.Sql Sql.Sql(`string` `host`, `string` `db`)`
        `Sql.Sql Sql.Sql(`string` `host`, `mapping`(`string`:`int`|`string`) `options`)`
        `Sql.Sql Sql.Sql(`string` `host`, `string` `db`, `string` `user`)`
        `Sql.Sql Sql.Sql(`string` `host`, `string` `db`, `string` `user`, `string` `password`)`
        `Sql.Sql Sql.Sql(`string` `host`, `string` `db`, `string` `user`, `string` `password`, `mapping`(`string`:`int`|`string`) `options`)`
        `Sql.Sql Sql.Sql(`object` `host`)`
        `Sql.Sql 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

        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.

      Method create_db

        ``void` create_db(`string` `db`)`

      Description

        Create a new database.

      Parameter `db`

        Name of database to create.

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

        ``void` drop_db(`string` `db`)`

      Description

        Drop database

      Parameter `db`

        Name of database to drop.

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

        ``int`|`string` error()`

      Description

        Return last error message.

      Method get_charset

        ``string` get_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`

      Method host_info

        ``string` host_info()`

      Description

        Return info about the connection to the SQL-server.

      Method insert_id

        ``int` insert_id()`

      Method is_open

        ``int` is_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()`

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

      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.

      Variable master_sql

        ``object` Sql.Sql.master_sql`

      Description

        Server specific connection object used for the actual SQL queries.

      Method ping

        ``int` ping()`

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

      Method query

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

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns 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 `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.

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

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

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

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

      See also

        `typed_query`, `big_query`, `streaming_query`

      Method query_db

        ``string` query_db()`

      Description

        Get the currently selected db if possible.

      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 reload

        ``void` reload()`

      Description

        Reload the tables.

      Method select_db

        ``void` select_db(`string` `db`)`

      Description

        Select database to access.

      Method server_info

        ``string` server_info()`

      Description

        Return info about the current SQL-server.

      Method set_charset

        ``void` set_charset(`string` `charset`)`

      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`

      Method shutdown

        ``void` shutdown()`

      Description

        Shutdown a database server.

      Method sqlstate

        ``string` sqlstate()`

      Description

        Return last SQLSTATE.

        The SQLSTATE error codes are specified in ANSI SQL.

      Method streaming_query

        ``int`|`object` streaming_query(`object`|`string` `q`, `mixed` ... `extraargs`)`

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns the results streaming in untyped mode.

        For the arguments, please see the `query()` function.

        The result is returned as a streaming `Sql.sql_result` object in untyped mode. 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.

      Note

        Streaming operation is not supported by all sql databases. If not supported, this function will fall back to calling `big_query()`.

      See also

        `big_query`, `streaming_typed_query`

      Method streaming_typed_query

        ``int`|`object` streaming_typed_query(`object`|`string` `q`, `mixed` ... `extraargs`)`

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns the results streaming in typed mode.

        For the arguments, please see the `query()` function.

        The result is returned as a streaming `Sql.sql_result` object in typed mode. 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).

      Note

        Neither streaming operation nor typed results are supported by all sql databases. If not supported, this function will fall back to calling `big_typed_query()`.

      See also

        `streaming_query`, `big_typed_query`

      Method typed_query

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

      Description

        Sends an SQL query synchronously to the underlying SQL-server and returns 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.

      See also

        `query`, `big_typed_query`

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

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

      Method big_query

        ``Mysql.mysql_result` big_query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`, `void`|`string` `charset`)`

      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 `Mysql.mysql_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()`

      Method big_typed_query

        ``Mysql.mysql_result` big_typed_query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`, `void`|`string` `charset`)`

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

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

        ``protected` `array`(`string(8bit)`) fix_query_charset(`string` `query`, `string`|`void` `charset`)`

      Returns

        Returns an array:

        Array
        ``string` `0`` | The adjusted query.
        ``string`|`zero` `1`` | The charset to restore after performing the query (if any). This is only set when `charset` has been set.

      Method get_charset

        ``string` get_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`

      Method get_unicode_decode_mode

        ``int` get_unicode_decode_mode()`

      Description

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

      See also

        `set_unicode_decode_mode`

      Method get_unicode_encode_mode

        ``int` get_unicode_encode_mode()`

      Description

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

      See also

        `set_unicode_encode_mode`

      Inherit mysql

        `inherit Mysql.mysql : mysql`

      Method is_keyword

        ``bool` is_keyword(`string` `name`)`

      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.

      Method latin1_to_utf8

        ``string` latin1_to_utf8(`string` `s`, `int` `extended`)`

      Description

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

      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 set_charset

        ``void` set_charset(`string` `charset`)`

      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`

      Method set_unicode_decode_mode

        ``void` set_unicode_decode_mode(`int` `enable`)`

      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`

      Method set_unicode_encode_mode

        ``bool` set_unicode_encode_mode(`int` `enable`)`

      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`

      Method streaming_query

        ``Mysql.mysql_result` streaming_query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`, `void`|`string` `charset`)`

      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 `Mysql.mysql_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()`

      Method streaming_typed_query

        ``Mysql.mysql_result` streaming_typed_query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`, `void`|`string` `charset`)`

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

      Method utf8_encode_query

        ``string` utf8_encode_query(`string` `q`, `function`(`string`, `mixed`|`void` ... :`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.

      Inherit mysql

        `inherit Sql.mysql : mysql`

    ## Class Sql.null

      Description

        The NULL Sql handler.

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

      Method query

        ``array`(`mapping`(`string`:`mixed`)) query(`string` `query`, `mapping`(`string`:`mixed`)|`void` `bindings`)`

      Returns

        Returns an array with a single element:

        ``"query"` : `string`` | The query string before formating.
        ``"bindings"` : `string`` | ``sprintf`("%O", `bindings`)`.
        ``"formatted_query"` : `string`` | The formatted query.

      Method quote

        ``string` quote(`string` `s`)`

      Returns

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

    ## Class Sql.odbc

      Inherit odbc

        `inherit Odbc.odbc : odbc`

      ## Class Sql.odbc.typed_result

        Variable _null_value

          ``mixed` Sql.odbc.typed_result._null_value`

        Description

          Value to use to represent NULL.

        Inherit this_program

          `inherit ::this_program : this_program`

        Method scale_numeric

          ``Gmp.mpq`|`int` scale_numeric(`int` `mantissa`, `int` `scale`)`

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

        Method time_factory

          ``TOD` time_factory(`int` `hour`, `int` `minute`, `int` `second`, `int`|`void` `nanos`)`

        Description

          Function called to create time of day objects.

          The default implementation just passes along its arguments to `TOD`.

        Method timestamp_factory

          ``Calendar.ISO.Day`|`Calendar.ISO.Fraction` timestamp_factory(`int` `year`, `int` `month`, `int` `day`, `int`|`void` `hour`, `int`|`void` `minute`, `int`|`void` `second`, `int`|`void` `nanos`, `int`|`void` `tz_hour`, `int`|`void` `tz_minute`)`

        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.

        Method uuid_factory

          ``Standards.UUID.UUID` uuid_factory(`string(8bit)` `raw_uuid`)`

        Description

          Function called to create UUID/GUID objects.

        ## Class Sql.odbc.typed_result.TOD

          Description

            Time of day.

          Method create

            `Sql.odbc.typed_result.TOD Sql.odbc.typed_result.TOD(`int` `hour`, `int` `minute`, `int` `second`, `int`|`void` `nanos`)`

          Variable hour
          Variable minute
          Variable second
          Variable nanos

            ``int` Sql.odbc.typed_result.TOD.hour`
            ``int` Sql.odbc.typed_result.TOD.minute`
            ``int` Sql.odbc.typed_result.TOD.second`
            ``int`|`void` Sql.odbc.typed_result.TOD.nanos`

    ## 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.Sql`, `Sql.postgres`, https://www.postgresql.org/docs/current/static/

      Method big_query

        ``final` `.pgsql_util.sql_result` big_query(`string` `q`, `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.sql_result` object (which conforms to the `Sql.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.Sql`, `Sql.sql_result`, `query()`, `Sql.pgsql_util.sql_result`

      Method big_typed_query

        ``final` `.pgsql_util.sql_result` big_typed_query(`string` `q`, `void`|`mapping`(`string`|`int`:`mixed`) `bindings`)`

      Description

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

      See also

        `big_query()`, `Sql.Sql`, `Sql.sql_result`

      Method cancelquery

        ``final` `void` cancelquery()`

      Description

        Cancels all currently running queries in this session.

      See also

        `reload()`, `resync()`

      Note

        This function is PostgreSQL-specific.

      Method close

        ``final` `void` close()`

      Description

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

      Note

        This function is PostgreSQL-specific.

      Method create

        `Sql.pgsql Sql.pgsql(`void`|`string` `host`, `void`|`string` `database`, `void`|`string` `user`, `void`|`string` `pass`, `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.Sql`, `select_db()`, https://www.postgresql.org/docs/current/static/runtime-config-client.html

      Method create_db

        ``final` `void` create_db(`string` `db`)`

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

      Method drop_db

        ``final` `void` drop_db(`string` `db`)`

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

      Method error

        ``final` `string` error(`void`|`int` `clear`)`

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

      Method get_charset

        ``final` `string` get_charset()`

      Returns

        The PostgreSQL name for the current connection charset.

      See also

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

      Method getruntimeparameters

        ``final` `mapping`(`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.

      Method getstatistics

        ``final` `mapping`(`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.

      Method host_info

        ``final` `string` host_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()`

      Method is_open

        ``final` `int` is_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()`

      Method list_dbs

        ``final` `array`(`string`) list_dbs(`void`|`string` `glob`)`

      Returns

        An array of the databases available on the server.

      Parameter `glob`

        If specified, list only those databases matching it.

      Method list_fields

        ``final` `array`(`mapping`(`string`:`mixed`)) list_fields(`void`|`string` `table`, `void`|`string` `glob`)`

      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.

      Method list_tables

        ``final` `array`(`string`) list_tables(`void`|`string` `glob`)`

      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.

      Method ping

        ``final` `int` ping()`

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

      Method quote

        ``final` `string` quote(`string` `s`)`

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

      Method quotebinary

        ``final` `string` quotebinary(`string` `s`)`

      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.

      Method reload

        ``final` `void` reload()`

      Description

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

      See also

        `resync()`, `cancelquery()`

      Method resync

        ``final` `void` resync()`

      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.

      Method select_db

        ``final` `void` select_db(`string` `dbname`)`

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

      Method server_info

        ``final` `string` server_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()`

      Method set_charset

        ``final` `void` set_charset(`string` `charset`)`

      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

      Method set_notify_callback

        ``final` `void` set_notify_callback(`string` `condition`, `void`|`function`(`int`, `string`, `string`, `mixed` ... :`void`) `notify_cb`, `void`|`int` `selfnotify`, `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.

      Method setcachedepth

        ``final` `int` setcachedepth(`void`|`int` `newdepth`)`

      Parameter `newdepth`

        Sets the new cachedepth for automatic caching of prepared statements.

      Returns

        The previous cachedepth.

      Note

        This function is PostgreSQL-specific.

      Method setfetchlimit

        ``final` `int` setfetchlimit(`void`|`int` `newfetchlimit`)`

      Parameter `newfetchlimit`

        Sets the new fetchlimit to interleave queries.

      Returns

        The previous fetchlimit.

      Note

        This function is PostgreSQL-specific.

      Method setportalbuffersize

        ``final` `int` setportalbuffersize(`void`|`int` `newportalbuffersize`)`

      Parameter `newportalbuffersize`

        Sets the new portalbuffersize for buffering partially concurrent queries.

      Returns

        The previous portalbuffersize.

      Note

        This function is PostgreSQL-specific.

      Method settimeout

        ``final` `int` settimeout(`void`|`int` `newtimeout`)`

      Parameter `newtimeout`

        Sets the new timeout for long running queries.

      Returns

        The previous timeout.

      Note

        This function is PostgreSQL-specific.

      Method status_commit

        ``final` `string` status_commit()`

      Returns

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

        ``idle``
        ``intransaction``
        ``infailedtransaction``

      Note

        This function is PostgreSQL-specific.

      Method streaming_query

        ``final` `.pgsql_util.sql_result` streaming_query(`string` `q`, `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()`, `Sql.Sql`, `Sql.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`

      Inherit pgsql

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

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

      Method create

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

      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`

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

      Inherit mo

        `inherit Postgres.postgres : mo`

      Inherit pgsql

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

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

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

        ``void` set_notify_callback()`
        ``void` set_notify_callback(`function`(:`void`) `f`)`
        ``void` set_notify_callback(`function`(:`void`) `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 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.

      Method streaming_query

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

      Description

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

      See also

        `big_query`, `Sql.Sql`, `Sql.sql_result`

      Variable version

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

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

      Method create

        `Sql.sql_result Sql.sql_result(`mixed` `res`)`

      Description

        Create a new Sql.sql_result object

      Parameter `res`

        Result to use as base.

      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 fetch_json_result

        ``string` fetch_json_result()`

      Description

        Fetch remaining result as `JSON`-encoded data.

      Method fetch_row

        ``int`|`array`(`string`|`int`|`float`) 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.Sql` class documentation for more details on how different data types are represented.

      Variable index

        ``int` Sql.sql_result.index`

      Description

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

      Variable master_res

        ``mixed` Sql.sql_result.master_res`

      Description

        The actual result.

      Method next_result

        ``this_program` next_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 `sql_result` object if there were more results, and `0` (zero) otherwise.

      Throws

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

      Method num_fields

        ``int` num_fields()`

      Description

        Returns the number of fields in the result.

      Method num_rows

        ``int` num_rows()`

      Description

        Returns the number of rows in the result.

      Method seek

        ``void` seek(`int` `skip`)`

      Description

        Skip past a number of rows.

      Parameter `skip`

        Number of rows to skip.

    ## Class Sql.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://"`.

      Method big_query

        ``ResObj` big_query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`)`

      Description

        Perform a streaming query against a SQLite database.

      See also

        `Sql.Sql()->big_query()`

      Method changes

        ``int` changes()`

      Description

        Get the number of changes.

      FIXME

        Document this function properly.

      Method create

        `Sql.sqlite Sql.sqlite(`string` `path`, `mixed` ... `ignored`)`

      Description

        Open the SQLite database stored at `path`.

      Method create_db

        ``void` create_db(`string` `db`)`

      Note

        This operation is not supported for SQLite.

      See also

        `Sql.Sql()->create_db()`

      Method drop_db

        ``void` drop_db(`string` `db`)`

      Note

        This operation is not supported for SQLite.

      See also

        `Sql.Sql()->drop_db()`

      Method error

        ``string` error()`

      Description

        Get the latest error message.

      See also

        `Sql.Sql()->error()`

      Method insert_id

        ``int` insert_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.

      Method interrupt

        ``void` interrupt()`

      FIXME

        Document this function.

      Method list_dbs

        ``array`(`string`) list_dbs()`

      Note

        This operation is not supported for SQLite.

      See also

        `Sql.Sql()->list_dbs()`

      Method query

        ``array`|`int` query(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`)`

      Description

        Perform a query against a SQLite database.

      See also

        `Sql.Sql()->query()`

      Method select_db

        ``void` select_db(`string` `db`)`

      Note

        This operation is not supported for SQLite.

      See also

        `Sql.Sql()->select_db()`

      Method server_info

        ``string` server_info()`

      Description

        Get information about the SQLite library version.

      See also

        `Sql.Sql()->server_info()`

      Method total_changes

        ``int` total_changes()`

      Description

        Get the total number of changes for this session.

      FIXME

        Document this function properly.

      ## Class Sql.sqlite.ResObj

        Description

          Result object from `big_query()`.

        Method eof

          ``int` eof()`

        See also

          `Sql.sql_result()->eof()`

        Method fetch_fields

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

        See also

          `Sql.sql_result()->fetch_fields()`

        Method fetch_row

          ``array` fetch_row()`

        See also

          `Sql.sql_result()->fetch_row()`

        Method num_fields

          ``int` num_fields()`

        See also

          `Sql.sql_result()->num_fields()`

        Method num_rows

          ``int` num_rows()`

        Note

          This API is not supported for `Sql.sqlite`.

        See also

          `Sql.sql_result()->num_rows()`

        Method seek

          ``void` seek(`int` `skip`)`

        See also

          `Sql.sql_result()->seek()`

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

      Method create

        `Sql.tds Sql.tds(`string`|`void` `server`, `string`|`void` `database`, `string`|`void` `user`, `string`|`void` `password`, `mapping`|`void` `options`)`

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

      Method error

        ``string` error()`

      Description

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

      Method insert_id

        ``int` insert_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).

      Method server_info

        ``string` server_info()`

      Description

        Return a string describing the server.

      Method tds_error

        ``protected` `void` tds_error(`string` `msg`, `mixed` ... `args`)`

      Description

        Format and report an error.

      ## Enum Sql.tds.EnvType

        Description

          Environment types.

          Used by `TDS_ENV_CHANGE_TOKEN`

      ## Enum Sql.tds.FieldType

        Description

          Field types.

      ## Enum Sql.tds.Token

        Description

          Tokens that may occur in the answers from the server.

      ## Class Sql.tds.Connection

        Description

          A connection to a TDS server.

        Method send_packet

          ``InPacket` send_packet(`Packet` `p`, `int` `flag`, `int`|`void` `last`)`

        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.

        Variable socket

          ``Stdio.File` Sql.tds.Connection.socket`

        Description

          The actual TCP connection.

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

        Method create

          `Sql.tds.big_query Sql.tds.big_query(`string`|`compile_query` `query`)`

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

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

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

        Method create

          `Sql.tds.compile_query Sql.tds.compile_query(`string` `query`)`

        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.

      Variable local_backend

        ``final` `Pike.Backend` Sql.pgsql_util.local_backend`

      Description

        The instance of the pgsql dedicated backend.

      Method register_backend

        ``final` `void` register_backend(`proxy` `client`)`

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

        Description

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

        See also

          `Sql.sql_result`, `Sql.pgsql`, `Sql.Sql`, `Sql.pgsql()->big_query()`

        Method affected_rows

          ``final` `int` affected_rows()`

        Description

          Returns the number of affected rows by this query.

        Note

          This function is PostgreSQL-specific.

        See also

          `num_rows()`, `status_command_complete()`

        Method eof

          ``final` `int` eof()`

        See also

          `Sql.sql_result()->eof()`

        Method fetch_fields

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

        See also

          `Sql.sql_result()->fetch_fields()`

        Method fetch_row

          ``final` `array`(`mixed`) fetch_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()`

        Method fetch_row_array

          ``final` `array`(`array`(`mixed`)) fetch_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()`

        Method num_fields

          ``final` `int` num_fields()`

        See also

          `Sql.sql_result()->num_fields()`

        Method num_rows

          ``final` `int` num_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.sql_result()->num_rows()`

        Method send_row

          ``final` `void` send_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()`

        Method set_result_array_callback

          ``final` `void` set_result_array_callback(`function`(`sql_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()`

        Method set_result_callback

          ``final` `void` set_result_callback(`function`(`sql_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()`

        Method status_command_complete

          ``final` `string` status_command_complete()`

        Description

          Returns the command-complete status for this query.

        Note

          This function is PostgreSQL-specific.

        See also

          `affected_rows()`

    ## Module Sql.sql_util

      Description

        Some SQL utility functions

      Method emulate_bindings

        ``string` emulate_bindings(`string` `query`, `mapping`(`string`|`int`:`mixed`)|`void` `bindings`, `void`|`object` `driver`, `void`|`string` `charset`)`

      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.

      Method fallback

        ``void` fallback()`

      Description

        Throw an error in case an unimplemented function is called.

      Method handle_extraargs

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

      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
        ``string` `0`` | The query altered to use bindings-syntax.
        ``mapping`(`string`|`int`:`mixed`) `1`` | A bindings mapping. Zero if it would be empty.

      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.

      Variable zero

        ``protected` `ZeroWrapper` Sql.sql_util.zero`

      Description

        Instance of `ZeroWrapper` used by `handle_extraargs()`.

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

        Inherit UnicodeWrapper

          `inherit UnicodeWrapper : UnicodeWrapper`

      ## Class Sql.sql_util.UnicodeWrapper

        Description

          Result object wrapper performing utf8 decoding of all fields.

        Method create

          `Sql.sql_util.UnicodeWrapper Sql.sql_util.UnicodeWrapper(`object` `master_result`)`

        Method eof

          ``bool` eof()`

        Description

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

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

        Method fetch_json_result

          ``int`|`string` fetch_json_result()`

        Description

          JSON is always utf8 default, do nothing.

        Method fetch_row

          ``int`|`array`(`string`) fetch_row()`

        Description

          Fetch the next row from the result.

          All strings in the result are decoded from UTF8.

        Variable field_info

          ``protected` `array`(`int`|`mapping`(`string`:`mixed`)) Sql.sql_util.UnicodeWrapper.field_info`

        Description

          Cached `fetch_fields()` result.

        Variable master_result

          ``protected` `object` Sql.sql_util.UnicodeWrapper.master_result`

        Method num_fields

          ``int` num_fields()`

        Description

          Returns the number of fields in the result.

        Method num_rows

          ``int` num_rows()`

        Description

          Returns the number of rows in the result.

        Method seek

          ``void` seek(`int` `rows`)`

        Description

          Skip ahead the specified number of rows.

      ## Class Sql.sql_util.ZeroWrapper

        Description

          Wrapper to handle zero.

        See also

          `zero`

        Method _sprintf

          `string sprintf(string format, ... Sql.sql_util.ZeroWrapper arg ... )`

        Returns

          Returns the following:

          ``"NULL"`` | If `fmt` is `'s'`.
          ``"ZeroWrapper()"`` | If `fmt` is `'O'`.

          Otherwise it formats a `0` (zero).

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

    Method client_info

      ``string` client_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.

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

      Method conn_delete

        ``void` conn_delete(`Sql.Sql` `db_conn`, `string`|`array` `where`, `void`|`string`|`array` `rest`)`

      Description

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

      Method conn_get

        ``mapping`(`string`:`mixed`) conn_get(`Sql.Sql` `db_conn`, `mixed` `id`, `void`|`array`(`string`) `fields`)`

      Description

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

      Method conn_get_multi

        ``Result` conn_get_multi(`Sql.Sql` `db_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`.

      Method conn_insert

        ``int` conn_insert(`Sql.Sql` `db_conn`, `mapping`(`string`:`mixed`) ... `records`)`

      Description

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

      Method conn_insert_ignore

        ``int` conn_insert_ignore(`Sql.Sql` `db_conn`, `mapping`(`string`:`mixed`) ... `records`)`

      Description

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

      Method conn_insert_or_update

        ``int` conn_insert_or_update(`Sql.Sql` `db_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`.

      Method conn_remove

        ``void` conn_remove(`Sql.Sql` `db_conn`, `mixed` `id`)`

      Description

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

      Method conn_remove_multi

        ``void` conn_remove_multi(`Sql.Sql` `db_conn`, `array`(`mixed`) `ids`)`

      Description

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

      Method conn_replace

        ``int` conn_replace(`Sql.Sql` `db_conn`, `mapping`(`string`:`mixed`) ... `records`)`

      Description

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

      Method conn_select

        ``Result` conn_select(`Sql.Sql` `db_conn`, `string`|`array` `where`, `void`|`array`(`string`) `fields`, `void`|`string`|`array` `select_exprs`, `void`|`string` `table_refs`, `void`|`string`|`array` `rest`, `void`|`string` `select_flags`)`

      Description

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

      Method conn_select1

        ``array` conn_select1(`Sql.Sql` `db_conn`, `string`|`array` `select_expr`, `string`|`array` `where`, `void`|`string` `table_refs`, `void`|`string`|`array` `rest`, `void`|`string` `select_flags`)`

      Description

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

      Method conn_update

        ``void` conn_update(`Sql.Sql` `db_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`.

      Method create

        `Mysql.SqlTable Mysql.SqlTable(`function`(`void`:`Sql.Sql`) `get_db`, `string` `table`, `void`|`string` `prop_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.

      Method delete

        ``void` delete(`string`|`array` `where`, `void`|`string`|`array` `rest`)`

      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.

      Method get

        ``mapping`(`string`:`mixed`) get(`mixed` `id`, `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`

      Variable get_db

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

      Description

        Callback to get a database connection.

      Method get_multi

        ``Result` get_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`

      Method handle_argspec

        ``local` `string` handle_argspec(`array` `argspec`, `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`.

      Variable id_col

        ``string` Mysql.SqlTable.id_col`

      Description

        The column containing the AUTO_INCREMENT values (if any).

      Method insert

        ``int` insert(`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`

      Method insert_ignore

        ``int` insert_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`

      Method insert_or_update

        ``int` insert_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.

      Variable pk_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`})`.

      Variable prop_col

        ``string` Mysql.SqlTable.prop_col`

      Description

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

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

      Method quote

        ``local` `string` quote(`string` `s`)`

      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.

      Method remove

        ``void` remove(`mixed` `id`)`

      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`

      Method remove_multi

        ``void` remove_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`

      Method replace

        ``int` replace(`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`

      Method select

        ``Result` select(`string`|`array` `where`, `void`|`array`(`string`) `fields`, `void`|`string`|`array` `select_exprs`, `void`|`string` `table_refs`, `void`|`string`|`array` `rest`, `void`|`string` `select_flags`)`

      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`

      Method select1

        ``array` select1(`string`|`array` `select_expr`, `string`|`array` `where`, `void`|`string` `table_refs`, `void`|`string`|`array` `rest`, `void`|`string` `select_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`

      Variable table

        ``string` Mysql.SqlTable.table`

      Description

        The table to query or change. Do not change.

      Method update

        ``void` update(`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`.

        Method _get_iterator

          `Mysql.SqlTable.Result a;
          foreach( a; index; value ) or
          ```Iterator` _get_iterator()`

        Description

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

        Method column_info

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

        Description

          Returns information about the columns in the result.

        Method eof

          ``int` eof()`

        Description

          Returns nonzero if there are no more rows.

        Method fetch

          ``mapping`(`string`:`mixed`) fetch()`

        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.

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

        Method num_rows

          ``int` num_rows()`

        Description

          Returns the number of rows in the result.

        Variable res

          ``Sql.mysql_result` Mysql.SqlTable.Result.res`

        Description

          The underlying result object from the db connection.

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

      Method _sprintf

        `string sprintf(string format, ... Mysql.mysql arg ... )`

      Method affected_rows

        ``int` affected_rows()`

      Description

        Returns the number of rows affected by the last query.

      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.

      Method create

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

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

      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 errno

        ``int` errno()`

      Description

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

        Returns `0` (zero) if there was no error.

      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 host_info

        ``string` host_info()`

      Description

        Get information about the Mysql-server connection

      See also

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

      Method info

        ``string` info()`

      Description

        Get information about the most recently executed statement.

      See also

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

      Method insert_id

        ``int` insert_id()`

      Description

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

      Method is_open

        ``int` is_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()`

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

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

        ``int` ping()`

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

      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 query_db

        ``string`|`zero` query_db()`

      Description

        Returns the currently selected database.

      See also

        `select_db()`

      Method reload

        ``void` reload()`

      Description

        Reload security tables

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

      See also

        `shutdown()`

      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()`, `query_db()`

      Method server_info

        ``string` server_info()`

      Description

        Get the version number of the Mysql-server.

      See also

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

      Method shutdown

        ``void` shutdown()`

      Description

        Shutdown the Mysql-server

        This function shuts down a running Mysql-server.

      See also

        `reload()`

      Method sqlstate

        ``string` sqlstate()`

      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.

      Method statistics

        ``string` statistics()`

      Description

        Some Mysql-server statistics

        This function returns some server statistics.

      See also

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

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

        `Mysql.mysql_result 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 eof

        ``bool` 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 fetch_json_result

        ``string` fetch_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`

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

      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 num_fields

        ``int` num_fields()`

      Description

        Number of fields in the result.

      See also

        `num_rows()`

      Method num_rows

        ``int` num_rows()`

      Description

        Number of rows in the result.

      See also

        `num_fields()`

      Method seek

        ``void` seek(`int` `rows`)`

      Description

        Skip ahead `rows` rows.

      Note

        Can only seek forward.

      See also

        `fetch_row()`

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

    Constant version

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

      Method affected_rows

        ``int` affected_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

      Method create

        `Msql.msql Msql.msql(`void`|`string` `dbserver`, `void`|`string` `dbname`, `void`|`string` `username`, `void`|`string` `passwd`)`

      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`

      Method create_db

        ``void` create_db(`string` `dbname`)`

      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` `dbname`)`

      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`

      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

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

        ``array` list_dbs(`void`|`string` `wild`)`

      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.

      Method list_fields

        ``mapping`(`string`:`mapping`(`string`:`mixed`)) list_fields(`string` `table`, `void`|`string` `glob`)`

      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.The glob argument, if present, filters out the fields not matching the glob. 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`

      Method list_index

        ``array` list_index(`string` `tablename`, `string` `indexname`)`

      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.

      Method list_tables

        ``array` list_tables(`void`|`string` `wild`)`

      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.

      Method query

        ``array`(`mapping`(`string`:`mixed`)) query(`string` `sqlquery`)`

      Description

        This is all you need to query the database. It takes as argument an SQL query string (i.e.: "SELECT foo,bar FROM baz WHERE name like '%kinkie%'" or "INSERT INTO baz VALUES ('kinkie','made','this')") and returns a data structure containing the returned values. The structure is an array (one entry for each returned row) of mappings which have the column name as index and the column contents as data. So to access a result from the first example you would have to do "results[0]->foo".

        A query which returns no data results in an empty array (and NOT in a 0). Also notice that when there is a column name clash (that is: when you join two or more tables which have columns with the same name), the clashing columns are renamed to <tablename>+"."+<column name>. To access those you'll have to use the indexing operator '[] (i.e.: results[0]["foo.bar"]).

      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 an empty array. This is not an error. Errors are reported only via exceptions.

      See also

        `error`

      Method reload_acl

        ``void` reload_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`

      Method select_db

        ``void` select_db(`string` `dbname`)`

      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`

      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 shutdown

        ``void` shutdown()`

      Description

        This function shuts a SQL-server down.

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

      Method _quote

        ``string` _quote(`string` `s`)`

      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

      Method affected_rows

        ``int` affected_rows()`

      Description

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

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

      Method create

        `Postgres.postgres Postgres.postgres()`
        `Postgres.postgres 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

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

      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

      Variable version

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

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

        `Postgres.postgres_result 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 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 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()`

      Method num_fields

        ``int` num_fields()`

      Description

        Returns the number of fields in the result.

      Method num_rows

        ``int` num_rows()`

      Description

        Returns the number of rows in the result.

      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.

  ## 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.DB arg )`

      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.DB from, `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.DB arg )`

      Description

        Provides overloading of `values`.

      Note

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

      Method fetch
      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()`

      Method close

        ``void` close()`

      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)

      Method create

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

      Method delete

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

        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 database
             for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k))
               write(k+":"+gdbm->fetch(k)+"\n");
            Or, using foreach
            // Write the contents of the database
            foreach( db; string key; string value )
               write(key+":"+value+"\n");

      Method nextkey

        ``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 database
             for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k))
               write(k+":"+gdbm->fetch(k)+"\n");
            Or, using foreach
            // Write the contents of the database
            foreach( db; string key; string value )
               write(key+":"+value+"\n");

      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 if the database is big.

      Method store

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

      Returns

        Returns `1` on success.

      Note

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

      See also

        ``[]=()`

      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.

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

      Inherit predef::Iterator

        `inherit predef::Iterator : predef::Iterator`

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

        ``array` indices( Yabu.DB arg )`

      Description

        Return a list of all tables

      Method _values

        ``array` values( Yabu.DB arg )`

      Description

        Return all tables as an array

      Method `[]

        ``mixed` res = `Yabu.DB()`[ `handle` ]`

      Description

        Equivalent to `table`

      Method ascii_statistics

        ``string` ascii_statistics()`

      Description

        Return information about all tables in a human readable format

      Method close

        ``void` close()`

      Description

        Close the database.

      Method create

        `Yabu.DB Yabu.DB(`string` `dir`, `string` `mode`)`

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

      Method list_tables

        ``array`(`string`) list_tables()`

      Description

        Return a list of all tables in the database

      Method purge

        ``void` purge()`

      Description

        Delete the database.

      Method reorganize

        ``int` reorganize(`float`|`void` `ratio`)`

      Description

        Call `Table.reorganize` in all tables

      Method statistics

        ``mapping`(`string`:`int`) statistics()`

      Description

        Return information about all tables

      Method sync

        ``void` sync()`

      Description

        Sync all tables

      Method table

        ``Table` table(`string` `handle`)`

      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.

      Method create

        `Yabu.LookupDB Yabu.LookupDB(`string` `dir`, `string` `mode`, `mapping`|`void` `options`)`

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

      Inherit DB

        `inherit DB : DB`

    ## Class Yabu.Table

      Description

        The basic Yabu table

      Method _indices

        ``array` indices( Yabu.Table arg )`

      Description

        Equivalent to list_keys()

      Method _m_delete

        ``mixed` m_delete(Yabu.Table from, `string` `handle`)`

      Description

        Equivalent to `delete`

      Method _values

        ``array` values( Yabu.Table arg )`

      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`

      Method ascii_statistics

        ``string` ascii_statistics()`

      Description

        Return information about all tables in a human redable format

      Method close

        ``void` close()`

      Description

        Close the table

      Method delete

        ``void` delete(`string` `handle`)`

      Description

        Remove a key

      Method get

        ``mixed` get(`string` `handle`)`

      Description

        Get a key

      Method list_keys

        ``array` list_keys()`

      Description

        List all keys

      Method purge

        ``void` purge()`

      Description

        Close and delete the table from disk

      Method reorganize

        ``int` reorganize(`float`|`void` `ratio`)`

      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.

      Method set

        ``mixed` set(`string` `handle`, `mixed` `x`)`

      Description

        Set a key

      Method statistics

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

      Method sync

        ``void` sync()`

      Description

        Synchronize. Usually done automatically

      Method transaction

        ``Transaction` transaction()`

      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

        ``array` indices( Yabu.Transaction arg )`

      Description

        Identical to list_keys();

      Method _m_delete

        ``mixed` m_delete(Yabu.Transaction from, `string` `handle`)`

      Description

        Identical to `delete`

      Method _values

        ``array` values( Yabu.Transaction arg )`

      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

      Method commit

        ``void` commit()`

      Description

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

      Method delete

        ``void` delete(`string` `handle`)`

      Description

        Delete `handle` from the database

      Method get

        ``mixed` get(`string` `handle`)`

      Description

        Get the value of `handle`

      Method list_keys

        ``array` list_keys()`

      Description

        List all keys

      Method rollback

        ``void` rollback()`

      Description

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

      Method set

        ``mixed` set(`string` `handle`, `mixed` `x`)`

      Description

        Set `handle` to x