database Package

database Package

Database abstraction layer

admin Module

Administration

Helper functions related to administration of the database, creating tables, removing tables and configuring administration user.

stoqlib.database.admin.ensure_admin_user(administrator_password)

stoqlib.database.admin.create_main_branch(store, name)

Creates a new branch and sets it as the main branch for the system :param store: a store :param name: name of the new branch

stoqlib.database.admin.populate_initial_data(store)

stoqlib.database.admin.register_payment_methods(store)

Registers the payment methods and creates persistent domain classes associated with them.

stoqlib.database.admin.register_accounts(store)

stoqlib.database.admin.get_admin_user(store)

Retrieves the current administrator user for the system :param store: store :returns: the admin user for the system

stoqlib.database.admin.ensure_sellable_constants(store)

Create native sellable constants.

stoqlib.database.admin.user_has_usesuper(store)

This method checks if the currently logged in postgres user has usesuper access which is necessary for certain operations

Parameters:store – a store Returns:if the user has usesuper access

stoqlib.database.admin.create_database_functions()

Create some functions we define on the database

This will simply read data/sql/functions.sql and execute it

stoqlib.database.admin.create_base_schema()

stoqlib.database.admin.create_default_profiles()

stoqlib.database.admin.create_default_profile_settings()

stoqlib.database.admin.initialize_system(password=None, testsuite=False, force=False, empty=False)

Call all the necessary methods to startup Stoq applications for every purpose: production usage, testing or demonstration :param force: When False, we will ask the user if he really wants to replace

the existing database.

Parameters:empty – If we should create the database without any data. When we do this the database will not be really usable by stoq. This should be used to create a database for the syncronization server.

debug Module

stoqlib.database.debug.getTerminalSize()

class stoqlib.database.debug.MyReindentFilter(max_width)

Bases: sqlparse.filters.ReindentFilter

stoqlib.database.debug.format_sql(statement, prefix_length=0)

class stoqlib.database.debug.StoqlibDebugTracer(stream=None)

Bases: storm.tracer.BaseStatementTracer

ATTRIBUTES = {'dark': 2, 'concealed': 8, 'reverse': 7, 'bold': 1, 'underline': 4, 'blink': 5}

COLORS = {'blue': 34, 'grey': 30, 'yellow': 33, 'green': 32, 'cyan': 36, 'white': 37, 'magenta': 35, 'red': 31}

RESET = '\x1b[0m'

write(msg)

header(pid, color, header, tail='\n')

connection_raw_execute_success(transaction, raw_cursor, statement, params)

transaction_create(store)

transaction_commit(store)

transaction_rollback(store, xid=None)

transaction_close(store)

stoqlib.database.debug.enable()

exceptions Module

Database exceptions

This is just a layer on top of the Python DBAPI we’re using to access the database

exception stoqlib.database.exceptions.SQLError

Bases: exceptions.Exception

exception stoqlib.database.exceptions.ORMObjectNotFound

Bases: storm.exceptions.StormError

exception stoqlib.database.exceptions.ORMTestError

Bases: exceptions.Exception

expr Module

Database expressions.

This contains a list of expressions that are unsupported by Storm. Most of them are specific to PostgreSQL

class stoqlib.database.expr.Age(*args)

Bases: storm.expr.NamedFunc

Given two datetimes, defines how the first is older than the second

name = 'AGE'

class stoqlib.database.expr.Round(*args)

Bases: storm.expr.NamedFunc

Rounds takes two arguments, first is numeric and second is integer, first one is the number to be round and the second is the requested precision.

name = 'ROUND'

class stoqlib.database.expr.NullIf(*args)

Bases: storm.expr.NamedFunc

Returns null if first argument matches second argument

e.g. NULLIF(x, ‘’) could be written in python like (read None as NULL):

x if x != ‘’ else None

name = 'NULLIF'

class stoqlib.database.expr.Date(*args)

Bases: storm.expr.NamedFunc

Extract the date part of a timestamp

name = 'DATE'

class stoqlib.database.expr.DateTrunc(*args)

Bases: storm.expr.NamedFunc

Truncates a part of a datetime

name = 'DATE_TRUNC'

class stoqlib.database.expr.Distinct(*args)

Bases: storm.expr.NamedFunc

name = 'DISTINCT'

class stoqlib.database.expr.Field(table, column)

Bases: storm.expr.SQL

class stoqlib.database.expr.Interval(expr)

Bases: storm.expr.PrefixExpr

Defines a datetime interval

prefix = 'INTERVAL'

class stoqlib.database.expr.TransactionTimestamp(*args)

Bases: storm.expr.NamedFunc

Current date and time at the start of the current transaction

name = 'TRANSACTION_TIMESTAMP'

date()

class stoqlib.database.expr.StatementTimestamp(*args)

Bases: storm.expr.NamedFunc

Current date and time at the start of the current statement

name = 'STATEMENT_TIMESTAMP'

date()

class stoqlib.database.expr.CharLength(*args)

Bases: storm.expr.NamedFunc

The size of the char, just like len() in python

name = 'CHAR_LENGTH'

class stoqlib.database.expr.LPad(*args)

Bases: storm.expr.NamedFunc

Fill up the string to length by prepending the characters fill

name = 'LPAD'

class stoqlib.database.expr.SplitPart(*args)

Bases: storm.expr.NamedFunc

Split string on delimiter and return the given field

name = 'split_part'

class stoqlib.database.expr.ArrayAgg(*args)

Bases: storm.expr.NamedFunc

name = 'array_agg'

class stoqlib.database.expr.Contains(expr1, expr2)

Bases: storm.expr.BinaryOper

oper = ' @> '

class stoqlib.database.expr.IsContainedBy(expr1, expr2)

Bases: storm.expr.BinaryOper

oper = ' <@ '

stoqlib.database.expr.compile_contains(expr_compile, expr, state)

class stoqlib.database.expr.NotIn(expr1, expr2)

Bases: storm.expr.BinaryOper

oper = ' NOT IN '

stoqlib.database.expr.compile_in(expr_compile, expr, state)

class stoqlib.database.expr.StoqNormalizeString(*args)

Bases: storm.expr.NamedFunc

This removes accents and other modifiers from a charater, it’s similar to NLKD normailzation in unicode, but it is run inside the database.

Note, this is very slow and should be avoided. In the future this will be replaced by fulltext search which does normalization in a cheaper way.

name = 'stoq_normalize_string'

class stoqlib.database.expr.Case(condition, result, else_=None)

Bases: storm.expr.ComparableExpr

Works like a Python’s if-then-else clause.

CASE WHEN <condition> THEN <result>

[WHEN <condition> THEN <result>]

END

prefix = '(unknown)'

condition

result

else_

stoqlib.database.expr.compile_case(compile, expr, state)

class stoqlib.database.expr.Trim(op, character, column)

Bases: storm.expr.ComparableExpr

Remove the longest string containing the given characters.

prefix = '(unknown)'

op

character

column

stoqlib.database.expr.compile_trim(compile, expr, state)

class stoqlib.database.expr.Concat(*inputs)

Bases: storm.expr.Expr

Concatenates string together using the || operator.

prefix = '(unknown)'

inputs

stoqlib.database.expr.compile_concat(compile, expr, state)

class stoqlib.database.expr.Between(value, start, end)

Bases: storm.expr.Expr

Check if value is between start and end

value

start

end

stoqlib.database.expr.compile_between(compile, expr, state)

class stoqlib.database.expr.GenerateSeries(start, end, step=Undef)

Bases: storm.expr.FromExpr

start

end

step

stoqlib.database.expr.compile_generate_series(compile, expr, state)

class stoqlib.database.expr.UnionAll(*exprs, **kwargs)

Bases: storm.expr.SetExpr

Union all the results

UNION is to UNION ALL what a python’s set is to a list. UNION will remove duplicates from the resulting rows while UNION ALL will just join all data, making it a little bit faster but possibly with more rows.

oper = ' UNION ALL '

stoqlib.database.expr.is_sql_identifier(identifier)

class stoqlib.database.expr.Over(attribute, partitions=None, orders=None)

Bases: storm.expr.ComparableExpr

Check if value is between start and end

Usage:

Over(attr, [partitions], [order by])

e.g.:

Considering the query:

SELECT sale.total_amount OVER (ORDER BY sale.confirm_date DESC) FROM sale;

The window function gets described as:

Over(Sale.total_amount, [], [Desc(Sale.confirm_date)])

attribute

partitions

orders

stoqlib.database.expr.compile_over(compile, expr, state)

stoqlib.database.expr.is_safe_token()

match(string[, pos[, endpos]]) –> match object or None. Matches zero or more characters at the beginning of the string

interfaces Module

Database Interfaces: Connection, Settings etc

interface stoqlib.database.interfaces.ICurrentBranch

Bases: zope.interface.Interface

This is a mainly a marker for the current branch of type stoqlib.domain.person.Branch It’s mainly used by get_current_branch()

interface stoqlib.database.interfaces.ICurrentBranchStation

Bases: zope.interface.Interface

This is a mainly a marker for the current branch station. It’s mainly used by get_current_station()

interface stoqlib.database.interfaces.ICurrentUser

Bases: zope.interface.Interface

This is a mainly a marker for the current user. It’s mainly used by get_current_user()

username = <zope.interface.interface.Attribute object>

pw_hash = <zope.interface.interface.Attribute object>

A hash of the user password

profile = <zope.interface.interface.Attribute object>

A profile represents a colection of information which represents what this user can do in the system

interface stoqlib.database.interfaces.ISearchFilter

Bases: zope.interface.Interface

get_state()

Gets the state. :rtype: QueryState

migration Module

Schema migration

class stoqlib.database.migration.Patch(filename, migration)

Bases: object

A Database Patch

Attribute filename:  patch filename Attribute level:  database level

apply(store)

Apply the patch :param store: a store

get_version()

Returns the patch version :returns: a tuple with the patch generation and level

class stoqlib.database.migration.SchemaMigration

Bases: object

Schema migration management

Is currently doing the following things:

• Applies database patches
• Makes sure that all parameters are present
• Makes sure that all applications are present

patch_resource_domain = None

patch_resource = None

patch_patterns = ['patch*.sql', 'patch*.py']

check(check_plugins=True)

check_uptodate()

Verify if the schema is up to date. :returns: True or False.

apply_all_patches()

Apply all available patches

update()

Updates the database schema

get_current_version()

This method is revision for returning the database schema version for a migration subclass

This must be implemented in a subclass :returns: the current database patch version

generate_sql_for_patch(patch)

This method is responsible for creating an SQL statement which is used to update the migration versioning information

This must be implemented in a subclass :param patch: the patch that was applied :returns: an SQL string

after_update()

This can be implemented in a subclass, but it is not mandatory. It’ll be called after applying all patches

class stoqlib.database.migration.StoqlibSchemaMigration

Bases: stoqlib.database.migration.SchemaMigration

This is a SchemaMigration subclass used by Stoqlib. It’s responsible for migrating the data for stoqlib itself and all its plugins

patch_resource_domain = 'stoq'

patch_resource = 'sql'

update(plugins=True, backup=True, check_database=True)

update_plugins()

check_plugins()

get_current_version()

after_update()

generate_sql_for_patch(patch)

class stoqlib.database.migration.PluginSchemaMigration(plugin_name, resource_domain, resource, patterns)

Bases: stoqlib.database.migration.SchemaMigration

This is a SchemaMigration class which is suitable for use within a plugin

generate_sql_for_patch(patch)

get_current_version()

stoqlib.database.migration.needs_schema_update()

orm Module

Simple ORM abstraction layer

class stoqlib.database.orm.SQLObjectBase

Bases: storm.base.Storm

The root class of all SQLObject-emulating classes in your application.

The general strategy for using Storm’s SQLObject emulation layer is to create an application-specific subclass of SQLObjectBase (probably named “SQLObject”) that provides an implementation of get_store to return an instance of storm.store.Store. It may even be implemented as returning a global Store instance. Then all database classes should subclass that class.

classmethod get(obj_id, store=None)

sync()

classmethod delete(id, store=None)

class stoqlib.database.orm.ORMObject(store=None, **kwargs)

Bases: stoqlib.database.orm.SQLObjectBase

store

properties Module

class stoqlib.database.properties.Identifier

Bases: int

class stoqlib.database.properties.IdentifierCol(primary=False)

Bases: storm.properties.Int

A numeric identifier for an object

This should be using when defining an identifier column to have some facilities, like formating it to a predefined pattern when converted to str/unicode. For instance:

>>> from stoqlib.domain.base import Domain
>>> from stoqlib.database.runtime import new_store
>>>
>>> class TestProduct(Domain):
...     identifier = IdentifierCol()
>>>
>>> store = new_store()
>>> p = TestProduct(store=store)
>>> p.identifier = 666
>>>
>>> p.identifier
666
>>> str(p.identifier)
'00666'
>>> unicode(p.identifier)
u'00666'
>>>
>>> store.rollback(close=True)

variable_class

alias of _IdentifierVariable

class stoqlib.database.properties.PriceVariable

Bases: storm.variables.DecimalVariable

parse_set(value, from_db)

class stoqlib.database.properties.PriceCol(name=None, primary=False, **kwargs)

Bases: storm.properties.Decimal

variable_class

alias of PriceVariable

class stoqlib.database.properties.QuantityVariable

Bases: storm.variables.DecimalVariable

parse_set(value, from_db)

class stoqlib.database.properties.QuantityCol(name=None, primary=False, **kwargs)

Bases: storm.properties.Decimal

variable_class

alias of QuantityVariable

class stoqlib.database.properties.PercentCol(name=None, primary=False, **kwargs)

Bases: storm.properties.Decimal

class stoqlib.database.properties.EnumVariable

Bases: storm.variables.Variable

parse_set(set, value, from_db)

parse_get(value, to_db)

class stoqlib.database.properties.EnumCol(name=None, primary=False, **kwargs)

Bases: storm.properties.SimpleProperty

variable_class

alias of EnumVariable

class stoqlib.database.properties.MyDateTimeVariable(*args, **kwargs)

Bases: storm.variables.DateTimeVariable, storm.variables.DateVariable

parse_set(value, from_db)

class stoqlib.database.properties.DateTimeCol(name=None, primary=False, **kwargs)

Bases: storm.properties.DateTime

variable_class

alias of MyDateTimeVariable

class stoqlib.database.properties.UUIDVariable

Bases: storm.variables.Variable

parse_set(value, from_db)

class stoqlib.database.properties.UUIDCol(name=None, primary=False, **kwargs)

Bases: storm.properties.SimpleProperty

variable_class

alias of UUIDVariable

class stoqlib.database.properties.XmlVariable(*args, **kwargs)

Bases: storm.variables.EncodedValueVariable

class stoqlib.database.properties.XmlCol(name=None, primary=False, **kwargs)

Bases: storm.properties.SimpleProperty

variable_class

alias of XmlVariable

stoqlib.database.properties.IdCol

alias of UUIDCol

queryexecuter Module

Kiwi integration for Stoq/Storm

class stoqlib.database.queryexecuter.QueryState(search_filter)

Bases: object

class stoqlib.database.queryexecuter.NumberQueryState(filter, value, mode=0)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new NumberQueryState object. :cvar value: number

DIFFERENT = 1

EQUALS = 0

class stoqlib.database.queryexecuter.NumberIntervalQueryState(filter, start, end)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new NumberIntervalQueryState object. :cvar start: number :cvar end: number

class stoqlib.database.queryexecuter.StringQueryState(filter, text, mode=3)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new StringQueryState object. :cvar text: string

CONTAINS_ALL = 3

CONTAINS_EXACTLY = 0

IDENTICAL_TO = 1

NOT_CONTAINS = 2

class stoqlib.database.queryexecuter.DateQueryState(filter, date)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new DateQueryState object. :cvar date: date

class stoqlib.database.queryexecuter.DateIntervalQueryState(filter, start, end)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new DateIntervalQueryState object. :cvar start: start of interval :cvar end: end of interval

class stoqlib.database.queryexecuter.BoolQueryState(filter, value)

Bases: stoqlib.database.queryexecuter.QueryState

Create a new BoolQueryState object. :cvar value: value of the query state

class stoqlib.database.queryexecuter.MultiQueryState(filter, values)

Bases: stoqlib.database.queryexecuter.QueryState

Query state for objects.

class stoqlib.database.queryexecuter.AsyncResultSet(resultset, result)

Bases: object

Resultset returned by AsyncQueryOperation.

This should perform exactly like a stoqlib.database.runtime.StoqlibResultSet. Some methods that are not defined here will be forwarded to it.

The original resultset can be accessed by resultset

class stoqlib.database.queryexecuter.AsyncQueryOperation(store, resultset, expr)

Bases: gobject._gobject.GObject

execute(async_conn)

Executes a query within an asyncronous psycopg2 connection

get_result()

Get operation result.

Note that this can only be called when the finish signal has been emitted.

Returns:a AsyncResultSet containing the result

cancel()

Cancel the operation scheduling

STATUS_CANCELLED = 3

STATUS_EXECUTING = 1

STATUS_FINISHED = 2

STATUS_WAITING = 0

class stoqlib.database.queryexecuter.QueryExecuter(store=None)

Bases: object

A QueryExecuter is responsible for taking the state (as in QueryState) objects from search filters and construct a query. The query is constructed using storm.

Variables:default_search_limit – The default search limit.

search(states=None, resultset=None, limit=None)

Execute a search.

Parameters:

• resultset – resultset to use, if None we will just execute a normal store.find() on the search_spec set in .set_search_spec()
• states –
• limit – use this limit instead of the one defined by set_limit()

search_async(states=None, resultset=None, limit=None)

Execute a search asynchronously. This uses a separate psycopg2 connection which is lazily created just before executing the first async query. This method returns an operation for which a signal finish is emitted when the query has finished executing. In that callback, AsyncQueryOperation.finish() should be called, eg:

>>> from stoqlib.api import api
>>> from stoqlib.domain.person import Person

>>> default_store = api.get_default_store()
>>> resultset = default_store.find(Person)

>>> qe = QueryExecuter(store=default_store)
>>> operation = qe.search_async(resultset=resultset)

>>> def finished(operation, loop):
...     operation.get_result()
...     # use result
...     loop.quit()

Create a loop for testing

>>> loop = glib.MainLoop()
>>> sig_id = operation.connect('finish', finished, loop)
>>> loop.run()

Parameters:

• states –
• resultset – a resultset or None

Returns:

a query operation

set_limit(limit)

Set the maximum number of result items to return in a search query. :param limit:

get_limit()

set_filter_columns(search_filter, columns, use_having=False)

Set what columns should be filtered for the search_filter

Parameters:columns – Should be a list of column names or properties to be used in the query. If they are column names (strings), we will call getattr on the search_spec to get the property for the query construction.

set_search_spec(search_spec)

Sets the Storm search_spec for this executer

Parameters:search_spec – a Storm search_spec

add_query_callback(callback)

Adds a generic query callback

Parameters:callback – a callable

add_filter_query_callback(search_filter, callback, use_having=False)

Adds a query callback for the filter search_filter

Parameters:

• search_filter – a search filter
• callback – a callable

set_query(callback)

Overrides the default query mechanism.

Parameters:callback – a callable which till take two arguments (query, store)

get_post_result(result)

get_ordered_result(result, attribute)

parse_states(states)

Parses the state given and return a tuple where the first element is the queries that should be used, and the second is a ‘having’ that should be used with the query.

runtime Module

Runtime routines for applications

stoqlib.database.runtime.autoreload_object(obj, obj_store=False)

Autoreload object in any other existing store.

This will go through every open store and see if the object is alive in the store. If it is, it will be marked for autoreload the next time its used.

Parameters:obj_store – if we should also autoreload the current store of the object

class stoqlib.database.runtime.StoqlibResultSet(store, find_spec, where=Undef, tables=Undef, select=Undef)

Bases: storm.store.ResultSet

avg(attribute)

set_viewable(viewable)

Configures this result set to load the results as instances of the given viewable.

Parameters:viewable – A Viewable

find(*args, **kwargs)

fast_iter()

class stoqlib.database.runtime.StoqlibStore(database=None, cache=None)

Bases: storm.store.Store

The Stoqlib Store.

This is the Stoqlib API to access a database. It represents more or less a database transaction, after modifying an object you need to either commit() or rollback() the store.

The primary way of querying object from a store is via the find() method, but you can also use Store.get() if you know the id of the object. find returns a ResultSet, see the Storm documentation for information about that.

Objects needs to be added to a store. This can either be done via StoqlibStore.add() or passing in the store parameter to a ORMObject/Domain object.

If you want to delete an object you use StoqlibStore.remove()

You normally create a store using new_store(), it needs to be close() when you’re done or a database connection will be leaked.

See also: storm manual storm tutorial

Attribute retval:  The return value of a operation this transaction is covering. Usually a domain object that was modified. By default it’s True, but can be set to False to do a rollback instead of a commit on stoqlib.api.StoqApi.trans()

find(cls_spec, *args, **kwargs)

get_lock_database_query()

Fetch a database query that needs to be executed to lock the database, suitable for applying migration patches.

Returns:a database query in string form

lock_database()

Tries to lock the database.

Raises an DatabaseError if the locking has failed (ie, other clients are using the database).

unlock_database()

Unlock a previously locked database.

table_exists(table_name)

Check if a table exists

Parameters:table_name – name of the table to check for Returns:True if the table exists

list_references(column)

Returns a list of columns that reference the givem column

This will return a list of tuples (source table, source column, dest table, dest column, update, delete)

where:

• source table and column: The column that reference the given column
• dest table and column: The referenced column (the same as the given column argument)
• update : The ON UPDATE action for the reference. ‘a’ for ‘NO ACTION’, ‘c’ for CASCADE
• delete: The same as update.

quote_query(query, args=())

Prepare a query for executing it. This is suitable for serializing a query to disk so we can pass it in to a database command line tool. It basically just escaped the arguments and generates a query that can be executed

Parameters:

• query – the database query, a string
• args – args that are to be escaped.

Returns:

database statement

maybe_remove(obj)

Maybe remove an object from the database

This will depend on the parameter SYNCHRONIZED_MODE. When working with synchronized databases, we should be very carefull when removing objects, since they will not be removed from the remote database (at least until we fix bug 5581)

get_pending_count()

Get the quantity of pending changes

Every time add_created_object(), add_deleted_object() or add_modified_object() gets called, this will increase by 1.

Note that this is in sync with savepoints so, if before doing a savepoint there was 10 pending changes, then 2 more are done, when rolling back to it it will be 10 again. The same applies to a full rollback where this will go to 0.

commit(close=False)

Commits a database. This needs to be done to submit the actually inserts to the database.

Parameters:close – If True, the store will also be closed after committed.

flush()

Flush the transaction to the database

This will transform all modifications done on domain objs in an sql command and execute them on the database. Note that this will execute the sql on the transaction, but only will be commited when commit() is called.

rollback(name=None, close=True)

Rollback the transaction

Parameters:

• name – If supplied limit changes to the last savepoint
• close – If True, the connection will also be closed and will not be available for use anymore. If False, only a rollback is done and it will still be possible to use it for other queries.

close()

Close the store.

Closes the socket that represents that database connection, this needs to be called when you finished using the store.

fetch(obj)

Fetches an existing object in the context of this store.

This is useful to ‘move’ an object from one store to another.

Parameters:obj – object to fetch Returns:the object in the context of this store

remove(obj)

Remove an objet from the store

The associated row will be deleted from the database.

savepoint(name)

Creates a database savepoint. This can be rolled back to using rollback_to_savepoint().

Parameters:name – name of the savepoint

rollback_to_savepoint(name)

Rollsback the store to a previous savepoint that was saved using savepoint()

Parameters:name – savepoint to move back to

savepoint_exists(name)

Checks if the given savepoint’s name exists

Parameters:name – the name of the savepoint Returns:True if the savepoint exists on this store, False otherwise.

confirm(commit)

Encapsulated method for committing/aborting changes in models.

Parameters:commit – True for commit, False for rollback Returns:True if it was committed, False otherwise

stoqlib.database.runtime.get_default_store()

This function returns the default/primary store. Notice that this store is considered read-only inside Stoqlib applications. Only transactions can modify objects and should be created using new_store(). This store should not be closed, it will only close when we the application is shutdown.

Returns:default store

stoqlib.database.runtime.set_default_store(store)

This sets a new default store and closes the existing one if any.

This is only called during Startup and should not be used elsewhere :param store: the new store to set

stoqlib.database.runtime.new_store()

Create a new transaction. :returns: a transaction

stoqlib.database.runtime.set_current_branch_station(store, station_name)

Registers the current station and the branch of the station as the current branch for the system :param store: a store :param station_name: name of the station to register

stoqlib.database.runtime.get_current_user(store)

Fetch the user which is currently logged into the system or None None means that there are no utilities available which in turn should only happens during startup, for example when creating a new database or running the migration script, at that point no users are logged in

Parameters:store – a store Returns:currently logged in user or None Return type:a LoginUser or None

stoqlib.database.runtime.get_current_branch(store)

Fetches the current branch company.

Parameters:store – a store Returns:the current branch Return type:a branch or None

stoqlib.database.runtime.get_current_station(store)

Fetches the current station (computer) which we are running on

Parameters:store – a store Param:current station Return type:BranchStation or None

settings Module

Settings required to access the database, hostname, username etc

stoqlib.database.settings.DB_NAME_RE = <_sre.SRE_Pattern object>

We only allow alpha-numeric and underscores in database names

stoqlib.database.settings.validate_database_name(dbname)

Verifies that a database name does not contain any invalid characters.

Parameters:dbname – name of a database Returns:True if it’s valid, False otherwise

stoqlib.database.settings.check_extensions(cursor=None, store=None)

Check if all required extensions can be installed.

Parameters:

• cursor – a cursor or None
• store – a store or None

stoqlib.database.settings.test_local_database()

Check and see if we postgres running locally

Returns:(hostname, port)

stoqlib.database.settings.get_database_version(store)

Gets the database version as a tuple

Parameters:store – a store Returns:the version as a 3 item tuple

class stoqlib.database.settings.DatabaseSettings(rdbms=None, address=None, port=None, dbname=None, username=None, password='')

Bases: object

DatabaseSettings contains all the information required to connect to a database, such as hostname, username and password.

It also provides helpers on top of ORMObject to return a database connection using the settings inside the object.

get_store_uri(filter_password=False)

Returns a uri representing the current database settings. It’s used by the orm to connect to a database. :param filter_password: if the password should be filtered out :returns: a string like postgresql://username@localhost/dbname

get_store_dsn()

Get a dsn that can be used to connect to the database

Unlike get_store_uri(), this is supported by all PostgreSQL versions when used by psycopg2.connect.

Returns:a string like “dbname=stoq host=localhost port=5432”

create_store()

Creates a store using the provided default settings. store.close() needs to be called when usage of this store is completed.

Returns:the new store

create_super_store()

Creates a store to the default database, note that this different from the configred. This method is mainly here to able to create other databases, which will need a connection, Be careful when using this method.

Returns:a store

copy()

check_database_address()

has_database()

Checks if the database specified in the settings exists :returns: if the database exists

get_command_line_arguments()

Get a list of command line arguments suitable to send into stoqdbadmin

get_tool_args()

Return a list of arguments suitable for sending in to the command line tool of a database such as psql

drop_database(dbname)

Drops a database.

Parameters:dbname – the name of the database to be dropped.

database_exists(dbname)

database_exists_and_should_be_dropped(dbname, force)

Return False if it is safe to drop the database

clean_database(dbname, force=False)

Cleans a database. If the database does not exist, it will be created.

Parameters:dbname – name of the database.

execute_sql(filename, lock_database=False)

Inserts raw SQL commands into the database read from a file.

Parameters:

• filename – filename with SQL commands
• lock_database – If the existing tables in the database should be locked

Returns:

return code, 0 if succeeded, positive integer for failure

start_shell(command=None, quiet=False)

Runs a database shell

Parameters:

• command – tell psql to execute the command string
• quiet – sets psql quiet option (-q)

test_connection()

Test for database connectivity using command line tools

Returns:True if the database connection succeeded.

dump_database(filename, schema_only=False, gzip=False, format='custom')

Dump the contents of the current database

Parameters:

• filename – filename to write the database dump to
• schema_only – If only the database schema will be dumped
• gzip – if the dump should be compressed using gzip -9
• format – database dump format, defaults to custom

restore_database(dump, new_name=None, clean_first=True)

Restores the current database.

Parameters:

• dump – a database dump file to be used to restore the database.
• new_name – optional name for the new restored database.
• clean_first – if a clean_database will be performed before restoring.

dump_table(table, filename=None)

Dump the contents of a table. Note this does not include the schema itself, just the data. To get the data call .read() on the returned object.

Parameters:

• table – table to write
• proc – a Process instance

check_version(store)

Verify that the database version is recent enough to be supported by stoq. Emits a warning if the version isn’t recent enough, suitable for usage by an installer.

Parameters:store – a store

tables Module

A list of all tables in database and a way to get them.

Add new tables here: (‘domain.modulo’) : [‘classA’, ‘classB’, ...],

module is the domain module which lives the classes in the list (classA, classB, ...).

stoqlib.database.tables.get_table_type_by_name(table_name)

Gets a table by name.

Parameters:table_name – name of the table

stoqlib.database.tables.get_table_types()

testsuite Module

Database routines which are used by the testsuite

class stoqlib.database.testsuite.StoqlibTestsuiteTracer

Bases: object

install()

remove()

reset()

connection_raw_execute_success(connection, raw_cursor, statement, params)

connection_raw_execute_error(connection, raw_cursor, statement, params, error)

class stoqlib.database.testsuite.TestsuiteNotifier

Bases: stoqlib.lib.message.DefaultSystemNotifier

reset()

message(name, short, description)

error(short, description)

stoqlib.database.testsuite.provide_database_settings(dbname=None, address=None, port=None, username=None, password=None, createdb=True)

Provide database settings. :param dbname: :param address: :param port: :param username: :param password: :param create: Create a new empty database if one is missing

stoqlib.database.testsuite.provide_utilities(station_name, branch_name=None)

Provide utilities like current user and current station. :param station_name: :param branch_name:

stoqlib.database.testsuite.bootstrap_suite(address=None, dbname=None, port=5432, username=None, password=u'', station_name=None, quick=False)

Test. :param address: :param dbname: :param port: :param username: :param password: :param station_name: :param quick:

viewable Module

Viewable implementation using python

Using Viewable, you can create an special object that will have properties from different tables, for instance, given this to ORM classes:

>>> from storm.expr import LeftJoin, Count, Sum
>>> from stoqlib.api import api
>>> from stoqlib.database.orm import ORMObject
>>> from stoqlib.database.properties import DecimalCol, DateTimeCol
>>> from stoqlib.database.properties import IntCol, UnicodeCol, IdCol

>>> class Person(ORMObject):
...     __storm_table__ = 'person'
...     id = IdCol(primary=True)
...     name = UnicodeCol()

>>> class Client(ORMObject):
...     __storm_table__ = 'client'
...     id = IdCol(primary=True)
...     person_id = IdCol()
...     salary = DecimalCol()
...     status = IntCol()

You can create a viewable like this:

>>> class ClientView(Viewable):
...     id = Client.id
...     name = Person.name
...     salary = Client.salary
...
...     tables = [Client,
...               LeftJoin(Person, Person.id == Client.person_id)]

And use it like a regular table with storm:

>>> store = api.new_store()
>>> for v in store.find(ClientView).order_by(Person.name):
...     print v.name, v.salary
Alessandra Almeida Itaberá 0.00
Franciso Elisio de Lima Junior 0.00
Luis Sergio da Silva Marin 0.00
Vitalina Claudino 0.00

You can also define another class as properties of the viewable. For instance:

>>> class ClientView(Viewable):
...     client = Client
...     person = Person
...
...     name = Person.name
...     nick = Client.salary
...
...     tables = [Client,
...               LeftJoin(Person, Person.id == Client.person_id)]

When you query using this viewable, not only the name and nick properties will be fetched, but the whole Client and Person objects will be also fetched (on the same sql select), and the objects will be added to the cache, so you can use them later, without going to the database for another query.

Another interesting feature is the possiblity to use aggregates in the viewable. Lets consider this sales table:

>>> class Sale(ORMObject):
...     __storm_table__ = 'sale'
...     id = IdCol(primary=True)
...     client_id = IdCol()
...     total_amount = DecimalCol()
...     status = IntCol()

Now we can create this viewable:

>>> class ClientSalesView(Viewable):
...    id = Client.id
...    name = Person.name
...    total_sales = Count(Sale.id)
...    total_value = Sum(Sale.total_amount)
...
...    tables = [Client,
...              LeftJoin(Person, Person.id == Client.person_id),
...              LeftJoin(Sale, Sale.client_id == Client.id)]
...
...    group_by = [id, name]

>>> store = api.new_store()
>>> for v in store.find(ClientSalesView).order_by(Person.name):
...     print v.name, v.total_sales, v.total_value
Alessandra Almeida Itaberá 1 706.00
Franciso Elisio de Lima Junior 0 None
Luis Sergio da Silva Marin 1 873.00
Vitalina Claudino 1 436.00

>>> store.close()

class stoqlib.database.viewable.Viewable

Bases: kiwi.python.ClassInittableObject

cls_spec = ()

This is the cls_spec that should be used with store.find(). Will be created by StoqlibStore when the viewable is first used.

cls_attributes = []

Corresponding attributes for each cls_spec. Will be created by StoqlibStore when the viewable is first used.

tables = []

A list of tables that will be queried, Viewable subclasses should

group_by = []

If any property defined in this viewable is an aggregate funcion (that needs grouping), this should have all the columns or table that should be grouped.

clause = None

If not None, this will be appended to the query passed to store.find()

having = None

If not None, this will be used to filter the query using HAVING

hidden_columns = []

This is a list of column names that should not be selected, but should still be possible to filter by.

store

sync()

Update the values of this object from the database

classmethod extend_viewable(new_attrs, new_joins=None)

Creates a subclass of this extended with the given columns and joins

This method will return a new Viewable class that is a subclass of the current viewable, extended with the new attributes and joins.

Parameters:

• new_attrs – A dictionary with the attributes that should be added to the new viewable
• new_joins – A list of new joins that should be appended to the new viewable

classmethod has_join_with(table)

Checks if this view has a join with some table.

This will look at all the joins of the viewable and return True if the given table is in one of the joins, and False otherwise

Parameters:table – Table that is performed Join.

classmethod has_column(column)

Checks if the given column is selected in this view.

This will look at all the selected columns and return True if the given one is one of them, False otherwise. Note that this will not work for storm expressions (like Sum or Count)

Parameters:column – The column to be searched.

highjacked = {}

Subpackages

• test Package
  • test Package