z_db (zotonic_core v1.0.0-rc.17)
Interface to database, uses database definition from Context.
Summary
Functions
Alter a table so that it matches the given column definitions. If the table doesn't exist then it is created. RESTRICTIONS: does NOT change the primary key and unique constraint of existing columns. If the table doesn't exist then it is created. Be careful when adding columns that are not nullable, if the table contains data then adding those columns will fail.
Assert that the table name is safe to use. Crashes if the table name is not safe.
Return property lists of the results of a query on the database in the Context
Return the column definition of the given column.
Check if a column exists in a table.
Return a list with the column (atom) names of a table. The names are sorted.
Return a list with all (binary) columns names of a table. The names are not sorted.
Return a list of column definitions for all columns of the table.
Return a property list with all columns of the table. (example: [{id,int4,modifier},...])
Check if a named constraint exists on a table.
Ensure that a table with the given columns exists, if the table exists then add, modify or drop columns. The 'id' (with type serial) column _must_ be defined when creating the table.
Return the version of the database. Returns {postgres, Major, Minor} for the database being used.
Return the version of the database. This is the long string describing the database version. Returns the empty string if there is no database.
Return the database name used for the current site.
Return the schema name used for the current site.
Return the user name used for the current site.
Delete a row from a table, the row must have a column with the name 'id'
Make sure that a table is dropped, only if the table exists
Do an SQL query with empty parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
Do an SQL query with parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
Do an SQL query empty parameters, returns the result without mapping from the database driver. The given timeout is in milliseconds.
Estimate the number of rows matching a query. This uses the PostgreSQL query planner to return an estimate of the number of rows.
Execute the same SQL statement for a list of parameters. Default timeout of 30 seconds.
Execute the same SQL statement for a list of parameters.
Flush all cached information about the database.
Return a list with all foreign keys on a table.
Check if a function is defined in the current schema.
Check if we have database connection up and runnng
Insert a new row in a table, use only default values and return the new record id. If the table has an 'id' column then the new id is returned. The 'id' column shoud be the primary key column and have type 'serial' (or bigserial) if it is not given in the insert statement. All columns must have a default value or be nullable.
Insert a new row in a table and return the new record id. Unknown columns are serialized in the props or props_json column. If the table has an 'id' column then the new id is returned. The 'id' column shoud be the primary key column and have type 'serial' (or bigserial) if it is not given in the passed parameters.
Merge the contents of the props column into the result rows
Check if all cols are valid columns in the target table, move unknown properties to the props column (if exists).
Create database and schema if they do not yet exist
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. Crash if the query errors. There is a query timeout of 30 seconds.
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. The parameters are used for argument $1 etc. Crash if the query errors. There is a query timeout of 30 seconds.
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. Crash if the query errors. The parameters are used for argument $1 etc. in the query. Supply a timeout in milliseconds after which the query is canceled.
Do an SQL query, return its results. Throws if the query errors. There is a query timeout of 30 seconds.
Do an SQL query, return its results. Throws if the query errors. The parameters are used for argument $1 etc. in the query. Query timeout of 30 seconds.
Do an SQL query, return its results. Throws if the query errors. The parameters are used for argument $1 etc. in the query. Supply a timeout in milliseconds after which the query is canceled.
Do an SQL query, return the first row or undefined if no rows are returned. Crash if the query errors. There is a query timeout of 30 seconds.
Do an SQL query, return the first row or undefined if no rows are returned. Crash if the query errors. There is a query timeout of 30 seconds.
Quote a table name so that it is safe to use in SQL queries.
Check whether schema exists
Read a row from a table, the row must have a column with the name 'id'. The props column contents is merged with the other properties returned.
Do an SQL query without parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
Do an SQL query without parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
Check the information schema if a certain table exists in the context database.
Return a map of all indices of a table. The key is the name of the index, the value is the definition of the index.
Convert a value so that it is compatible with the column type
Perform a function inside a transaction, do a rollback on exceptions
Perform a transaction with extra options. Default retry on deadlock
Clear any transaction in the context, useful when starting a thread with this context.
Update a row in a table, merging the properties with any new property values. The table must have a column id of some integer type. If there is no matching column then 0 is returned for the number of updated columns. The update is done within a transaction, first the old values are read and then merged with the new values.
Update the sequence of the ids in the table. They will be renumbered according to their position in the id list.
Types
-type database_server() :: postgresql.
-type id() :: pos_integer().
-type id_key() :: binary().
-type parameters() :: [parameter()].
-type props() :: proplists:proplist() | props_map().
-type qmap_option() :: {keys, binary | atom} | {timeout, non_neg_integer()}.
-type qmap_options() :: [qmap_option()].
-type query_error() :: nodb | enoent | epgsql:query_error() | term().
-type query_result() :: {ok, Columns :: list(), Rows :: list()} | {ok, Count :: non_neg_integer(), Columns :: list(), Rows :: list()} | {ok, Count :: non_neg_integer()} | {error, term()}.
-type query_timeout() :: integer().
-type table_name() :: atom() | nonempty_string() | nonempty_binary().
Functions
-spec alter_table(Table, Columns, Context) -> ok | {error, Reason} when Table :: table_name(), Columns :: [#column_def{name :: atom(), type :: string() | binary(), length :: non_neg_integer() | undefined, is_nullable :: boolean(), is_array :: boolean(), default :: term(), primary_key :: boolean(), unique :: boolean()}], Context :: z:context(), Reason :: term().
Alter a table so that it matches the given column definitions. If the table doesn't exist then it is created. RESTRICTIONS: does NOT change the primary key and unique constraint of existing columns. If the table doesn't exist then it is created. Be careful when adding columns that are not nullable, if the table contains data then adding those columns will fail.
-spec assert_table_name(table_name()) -> true.
Assert that the table name is safe to use. Crashes if the table name is not safe.
-spec assoc(sql(), z:context()) -> [proplists:proplist()].
Return property lists of the results of a query on the database in the Context
-spec assoc(sql(), list(), z:context()) -> [proplists:proplist()].
-spec assoc(sql(), list(), z:context(), integer()) -> [proplists:proplist()].
-spec assoc_props(sql(), z:context()) -> [proplists:proplist()].
-spec assoc_props(sql(), list(), z:context()) -> [proplists:proplist()].
-spec assoc_props(sql(), list(), z:context(), integer()) -> [proplists:proplist()].
-spec assoc_props_row(sql(), z:context()) -> proplists:proplist() | undefined.
-spec assoc_props_row(sql(), list(), z:context()) -> proplists:proplist() | undefined.
-spec assoc_row(sql(), z:context()) -> proplists:proplist() | undefined.
-spec assoc_row(sql(), parameters(), z:context()) -> proplists:proplist() | undefined.
-spec column(table_name(), column_name(), z:context()) -> {ok, #column_def{name :: atom(), type :: string() | binary(), length :: non_neg_integer() | undefined, is_nullable :: boolean(), is_array :: boolean(), default :: term(), primary_key :: boolean(), unique :: boolean()}} | {error, enoent}.
Return the column definition of the given column.
-spec column_exists(table_name(), column_name(), z:context()) -> boolean().
Check if a column exists in a table.
-spec column_names(table_name(), z:context()) -> [atom()].
Return a list with the column (atom) names of a table. The names are sorted.
-spec column_names_bin(table_name(), z:context()) -> [binary()].
Return a list with all (binary) columns names of a table. The names are not sorted.
-spec columns(table_name(), z:context()) -> [#column_def{name :: atom(), type :: string() | binary(), length :: non_neg_integer() | undefined, is_nullable :: boolean(), is_array :: boolean(), default :: term(), primary_key :: boolean(), unique :: boolean()}].
Return a list of column definitions for all columns of the table.
-spec columns(schema_name(), table_name(), z:context()) -> [#column_def{name :: atom(), type :: string() | binary(), length :: non_neg_integer() | undefined, is_nullable :: boolean(), is_array :: boolean(), default :: term(), primary_key :: boolean(), unique :: boolean()}].
Return a property list with all columns of the table. (example: [{id,int4,modifier},...])
-spec constraint_exists(Table, Constraint, Context) -> boolean() when Table :: table_name(), Constraint :: binary() | string() | atom(), Context :: z:context().
Check if a named constraint exists on a table.
-spec create_table(Table, Columns, Context) -> ok | {error, Reason} when Table :: table_name(), Columns :: [#column_def{name :: atom(), type :: string() | binary(), length :: non_neg_integer() | undefined, is_nullable :: boolean(), is_array :: boolean(), default :: term(), primary_key :: boolean(), unique :: boolean()}], Context :: z:context(), Reason :: term().
Ensure that a table with the given columns exists, if the table exists then add, modify or drop columns. The 'id' (with type serial) column _must_ be defined when creating the table.
-spec database_version(z:context()) -> {ok, {database_server(), non_neg_integer(), non_neg_integer()}} | {error, no_database_connection}.
Return the version of the database. Returns {postgres, Major, Minor} for the database being used.
Return the version of the database. This is the long string describing the database version. Returns the empty string if there is no database.
-spec dbdatabase(Context) -> Database when Context :: z:context(), Database :: string() | undefined.
Return the database name used for the current site.
Return the schema name used for the current site.
-spec dbusername(Context) -> Username when Context :: z:context(), Username :: string() | undefined.
Return the user name used for the current site.
-spec delete(Table, Id, Context) -> {ok, RowsDeleted} | {error, Reason} when Table :: table_name(), Id :: id() | id_key(), Context :: z:context(), RowsDeleted :: non_neg_integer(), Reason :: term().
Delete a row from a table, the row must have a column with the name 'id'
-spec drop_table(table_name(), z:context()) -> ok.
Make sure that a table is dropped, only if the table exists
-spec equery(SQL, Context) -> Result when SQL :: sql(), Context :: z:context(), Result :: query_result().
Do an SQL query with empty parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
-spec equery(SQL, Parameters, Context) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Result :: query_result().
Do an SQL query with parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
-spec equery(SQL, Parameters, Context, Timeout) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Timeout :: pos_integer(), Result :: query_result().
Do an SQL query empty parameters, returns the result without mapping from the database driver. The given timeout is in milliseconds.
-spec estimate_rows(Query, Args, Context) -> {ok, Rows} | {error, term()} when Query :: string() | binary(), Args :: list(), Context :: z:context(), Rows :: non_neg_integer().
Estimate the number of rows matching a query. This uses the PostgreSQL query planner to return an estimate of the number of rows.
-spec execute_batch(SQL, ParametersList, Context) -> Result when SQL :: sql(), ParametersList :: [parameters()], Context :: z:context(), Result :: {ok, [query_result()]} | {error, term()}.
Execute the same SQL statement for a list of parameters. Default timeout of 30 seconds.
-spec execute_batch(SQL, ParametersList, Context, Timeout) -> Result when SQL :: sql(), ParametersList :: [parameters()], Context :: z:context(), Timeout :: pos_integer(), Result :: {ok, [query_result()]} | {error, term()}.
Execute the same SQL statement for a list of parameters.
-spec flush(Context) -> ok when Context :: z:context().
Flush all cached information about the database.
-spec foreign_keys(table_name(), z:context()) -> {ok, [map()]} | {error, Reason} when Reason :: enoent | term().
Return a list with all foreign keys on a table.
-spec function_exists(FunctionName, Context) -> boolean() when FunctionName :: binary() | string() | atom(), Context :: z:context().
Check if a function is defined in the current schema.
Check if we have database connection up and runnng
-spec insert(Table, Context) -> {ok, NewId | undefined} | {error, Reason} when Table :: table_name(), Context :: z:context(), NewId :: id(), Reason :: term().
Insert a new row in a table, use only default values and return the new record id. If the table has an 'id' column then the new id is returned. The 'id' column shoud be the primary key column and have type 'serial' (or bigserial) if it is not given in the insert statement. All columns must have a default value or be nullable.
-spec insert(Table, Parameters, Context) -> {ok, NewId | undefined} | {error, Reason} when Table :: table_name(), Parameters :: props(), Context :: z:context(), NewId :: id(), Reason :: term().
Insert a new row in a table and return the new record id. Unknown columns are serialized in the props or props_json column. If the table has an 'id' column then the new id is returned. The 'id' column shoud be the primary key column and have type 'serial' (or bigserial) if it is not given in the passed parameters.
-spec merge_props([proplists:proplist() | map()]) -> list().
Merge the contents of the props column into the result rows
Check if all cols are valid columns in the target table, move unknown properties to the props column (if exists).
Create database and schema if they do not yet exist
-spec q1(SQL, Context) -> Result when SQL :: sql(), Context :: z:context(), Result :: term() | undefined.
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. Crash if the query errors. There is a query timeout of 30 seconds.
-spec q1(SQL, Parameters, Context) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Result :: term() | undefined; (SQL, Context, Timeout) -> Result when SQL :: sql(), Context :: z:context(), Timeout :: pos_integer(), Result :: term() | undefined.
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. The parameters are used for argument $1 etc. Crash if the query errors. There is a query timeout of 30 seconds.
-spec q1(SQL, Parameters, Context, Timeout) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Timeout :: pos_integer(), Result :: term() | undefined.
Do an SQL query, returns the first result of the first row or undefined if there are no returned rows. Crash if the query errors. The parameters are used for argument $1 etc. in the query. Supply a timeout in milliseconds after which the query is canceled.
-spec q(SQL, Context) -> Result when SQL :: sql(), Context :: z:context(), Result :: list() | non_neg_integer().
Do an SQL query, return its results. Throws if the query errors. There is a query timeout of 30 seconds.
-spec q(SQL, Parameters, Context) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Result :: term(); (SQL, Context, Timeout) -> Result when SQL :: sql(), Context :: z:context(), Timeout :: pos_integer(), Result :: list() | non_neg_integer().
Do an SQL query, return its results. Throws if the query errors. The parameters are used for argument $1 etc. in the query. Query timeout of 30 seconds.
-spec q(SQL, Parameters, Context, Timeout) -> Result when SQL :: sql(), Parameters :: parameters(), Context :: z:context(), Timeout :: pos_integer(), Result :: list() | non_neg_integer().
Do an SQL query, return its results. Throws if the query errors. The parameters are used for argument $1 etc. in the query. Supply a timeout in milliseconds after which the query is canceled.
-spec q_row(SQL, Context) -> Row | undefined when SQL :: sql(), Context :: z:context(), Row :: tuple() | undefined.
Do an SQL query, return the first row or undefined if no rows are returned. Crash if the query errors. There is a query timeout of 30 seconds.
-spec q_row(SQL, Parameters, Context) -> Row | undefined when SQL :: sql(), Parameters :: parameter(), Context :: z:context(), Row :: tuple() | undefined.
Do an SQL query, return the first row or undefined if no rows are returned. Crash if the query errors. There is a query timeout of 30 seconds.
-spec qmap(sql(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap(sql(), parameters(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap(sql(), parameters(), qmap_options(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap_props(sql(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap_props(sql(), parameters(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap_props(sql(), parameters(), list(), z:context()) -> {ok, [map()]} | {error, query_error()}.
-spec qmap_props_row(sql(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec qmap_props_row(sql(), parameters(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec qmap_props_row(sql(), parameters(), qmap_options(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec qmap_row(sql(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec qmap_row(sql(), parameters(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec qmap_row(sql(), parameters(), qmap_options(), z:context()) -> {ok, map()} | {error, query_error()}.
-spec quoted_table_name(table_name()) -> {default | string(), string(), string()}.
Quote a table name so that it is safe to use in SQL queries.
-spec schema_exists_conn(epgsql:connection(), string()) -> boolean().
Check whether schema exists
-spec select(Table, Id, Context) -> {ok, Row} | {error, Reason} when Table :: table_name(), Id :: id() | id_key(), Context :: z:context(), Row :: map(), Reason :: term().
Read a row from a table, the row must have a column with the name 'id'. The props column contents is merged with the other properties returned.
-spec squery(SQL, Context) -> Result when SQL :: sql(), Context :: z:context(), Result :: query_result().
Do an SQL query without parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
-spec squery(SQL, Context, Timeout) -> Result when SQL :: sql(), Context :: z:context(), Timeout :: pos_integer(), Result :: query_result().
Do an SQL query without parameters, returns the result without mapping from the database driver. There is a query timeout of 30 seconds.
-spec table_exists(table_name(), z:context()) -> boolean().
Check the information schema if a certain table exists in the context database.
-spec table_keys(table_name(), z:context()) -> {ok, Indices} when Indices :: #{IndexName := IndexDef}, IndexName :: binary(), IndexDef :: binary().
Return a map of all indices of a table. The key is the name of the index, the value is the definition of the index.
-spec to_column_value(table_name(), column_name(), term(), z:context()) -> {ok, term()} | {error, term()}.
Convert a value so that it is compatible with the column type
-spec transaction(transaction_fun(), z:context()) -> any() | {error, term()}.
Perform a function inside a transaction, do a rollback on exceptions
-spec transaction(transaction_fun(), list(), z:context()) -> any() | {error, term()}.
Perform a transaction with extra options. Default retry on deadlock
Clear any transaction in the context, useful when starting a thread with this context.
-spec update(Table, Id, Props, Context) -> {ok, RowsUpdated} | {error, Reason} when Table :: table_name(), Id :: id() | id_key(), Props :: props(), Context :: z:context(), RowsUpdated :: non_neg_integer(), Reason :: term().
Update a row in a table, merging the properties with any new property values. The table must have a column id of some integer type. If there is no matching column then 0 is returned for the number of updated columns. The update is done within a transaction, first the old values are read and then merged with the new values.
-spec update_sequence(table_name(), [integer()], z:context()) -> any().
Update the sequence of the ids in the table. They will be renumbered according to their position in the id list.