l.s.d.sqlbase : module documentation

Part of lp.services.database

No module docstring
Class StupidCache A Storm cache that never evicts objects except on clear().
Class LaunchpadStyle A SQLObject style for launchpad.
Class SQLBase Base class emulating SQLObject for legacy database classes.
Function clear_current_connection_cache Clear SQLObject's object cache. SQLObject compatibility - DEPRECATED.
Function get_transaction_timestamp Get the timestamp for the current transaction on store.
Function quote Quote a variable ready for inclusion into an SQL statement.
Function quote_like Quote a variable ready for inclusion in a SQL statement's LIKE clause
Function sqlvalues Return a tuple of converted sql values for each value in some_tuple.
Function quote_identifier Quote an identifier, such as a table name.
Function convert_storm_clause_to_string Convert a Storm expression into a plain string.
Function flush_database_updates Flushes all pending database updates.
Function flush_database_caches Flush all database caches.
Function block_implicit_flushes A decorator that blocks implicit flushes on the main store.
Function reset_store Function decorator that resets the main store.
Function connect Return a fresh DB-API connection to the MAIN MASTER database.
Function connect_string Return a PostgreSQL connection string.
Class cursor A DB-API cursor-like object for the Storm connection.
Function session_store Return a store connected to the session DB.
Function _get_sqlobject_store Return the store used by the SQLObject compatibility layer.
def _get_sqlobject_store():
Return the store used by the SQLObject compatibility layer.
def clear_current_connection_cache():
Clear SQLObject's object cache. SQLObject compatibility - DEPRECATED.
def get_transaction_timestamp(store):
Get the timestamp for the current transaction on store.
def quote(x):

Quote a variable ready for inclusion into an SQL statement. Note that you should use quote_like to create a LIKE comparison.

Basic SQL quoting works

>>> quote(1)
'1'
>>> quote(1.0)
'1.0'
>>> quote("hello")
"E'hello'"
>>> quote("'hello'")
"E'''hello'''"
>>> quote(r"\'hello")
"E'\\\\''hello'"

Note that we need to receive a Unicode string back, because our query will be a Unicode string (the entire query will be encoded before sending across the wire to the database).

>>> quote(u"\N{TRADE MARK SIGN}")
u"E'\u2122'"

Timezone handling is not implemented, since all timestamps should be UTC anyway.

>>> from datetime import datetime, date, time
>>> quote(datetime(2003, 12, 4, 13, 45, 50))
"'2003-12-04 13:45:50'"
>>> quote(date(2003, 12, 4))
"'2003-12-04'"
>>> quote(time(13, 45, 50))
"'13:45:50'"

This function special cases datetime objects, due to a bug that has since been fixed in SQLOS (it installed an SQLObject converter that stripped the time component from the value). By itself, the sqlrepr function has the following output:

>>> sqlrepr(datetime(2003, 12, 4, 13, 45, 50), 'postgres')
"'2003-12-04T13:45:50'"

This function also special cases set objects, which SQLObject's sqlrepr() doesn't know how to handle.

>>> quote(set([1,2,3]))
'(1, 2, 3)'
>>> quote(frozenset([1,2,3]))
'(1, 2, 3)'
def quote_like(x):

Quote a variable ready for inclusion in a SQL statement's LIKE clause

XXX: StuartBishop 2004-11-24: Including the single quotes was a stupid decision.

To correctly generate a SELECT using a LIKE comparision, we need to make use of the SQL string concatination operator '||' and the quote_like method to ensure that any characters with special meaning to the LIKE operator are correctly escaped.

>>> "SELECT * FROM mytable WHERE mycol LIKE '%%' || %s || '%%'" \
...     % quote_like('%')
"SELECT * FROM mytable WHERE mycol LIKE '%' || E'\\\\%' || '%'"

Note that we need 2 backslashes to quote, as per the docs on the LIKE operator. This is because, unless overridden, the LIKE operator uses the same escape character as the SQL parser.

>>> quote_like('100%')
"E'100\\\\%'"
>>> quote_like('foobar_alpha1')
"E'foobar\\\\_alpha1'"
>>> quote_like('hello')
"E'hello'"

Only strings are supported by this method.

>>> quote_like(1)
Traceback (most recent call last):
    [...]
TypeError: Not a string (<type 'int'>)
def sqlvalues(*values, **kwvalues):

Return a tuple of converted sql values for each value in some_tuple.

This safely quotes strings, or gives representations of dbschema items, for example.

Use it when constructing a string for use in a SELECT. Always use %s as the replacement marker.

('SELECT foo from Foo where bar = %s and baz = %s'
% sqlvalues(BugTaskSeverity.CRITICAL, 'foo'))
>>> sqlvalues()
Traceback (most recent call last):
...
TypeError: Use either positional or keyword values with sqlvalue.
>>> sqlvalues(1)
('1',)
>>> sqlvalues(1, "bad ' string")
('1', "E'bad '' string'")

You can also use it when using dict-style substitution.

>>> sqlvalues(foo=23)
{'foo': '23'}

However, you cannot mix the styles.

>>> sqlvalues(14, foo=23)
Traceback (most recent call last):
...
TypeError: Use either positional or keyword values with sqlvalue.
def quote_identifier(identifier):

Quote an identifier, such as a table name.

In SQL, identifiers are quoted using " rather than ' which is reserved for strings.

>>> print quoteIdentifier('hello')
"hello"
>>> print quoteIdentifier("'")
"'"
>>> print quoteIdentifier('"')
""""
>>> print quoteIdentifier("\\")
"\"
>>> print quoteIdentifier('\\"')
"\"""
def convert_storm_clause_to_string(storm_clause):

Convert a Storm expression into a plain string.

A helper function allowing to use a Storm expressions in old-style code which builds for example WHERE expressions as plain strings.

>>> from lp.bugs.model.bug import Bug
>>> from lp.bugs.model.bugtask import BugTask
>>> from lp.bugs.interfaces.bugtask import BugTaskImportance
>>> from storm.expr import And, Or
>>> print convert_storm_clause_to_string(BugTask)
BugTask
>>> print convert_storm_clause_to_string(BugTask.id == 16)
BugTask.id = 16
>>> print convert_storm_clause_to_string(
...     BugTask.importance == BugTaskImportance.UNKNOWN)
BugTask.importance = 999
>>> print convert_storm_clause_to_string(Bug.title == "foo'bar'")
Bug.title = E'foo''bar'''
>>> print convert_storm_clause_to_string(
...     Or(BugTask.importance == BugTaskImportance.UNKNOWN,
...        BugTask.importance == BugTaskImportance.HIGH))
BugTask.importance = 999 OR BugTask.importance = 40
>>> print convert_storm_clause_to_string(
...    And(Bug.title == 'foo', BugTask.bug == Bug.id,
...        Or(BugTask.importance == BugTaskImportance.UNKNOWN,
...           BugTask.importance == BugTaskImportance.HIGH)))
Bug.title = E'foo' AND BugTask.bug = Bug.id AND
(BugTask.importance = 999 OR BugTask.importance = 40)
Parametersstorm_clauseA Storm expression
def flush_database_updates():

Flushes all pending database updates.

When SQLObject's _lazyUpdate flag is set, then it's possible to have changes written to objects that aren't flushed to the database, leading to inconsistencies when doing e.g.:

# Assuming the Beer table already has a 'Victoria Bitter' row...
assert Beer.select("name LIKE 'Vic%'").count() == 1  # This will pass
beer = Beer.byName('Victoria Bitter')
beer.name = 'VB'
assert Beer.select("name LIKE 'Vic%'").count() == 0  # This will fail

To avoid this problem, use this function:

# Assuming the Beer table already has a 'Victoria Bitter' row...
assert Beer.select("name LIKE 'Vic%'").count() == 1  # This will pass
beer = Beer.byName('Victoria Bitter')
beer.name = 'VB'
flush_database_updates()
assert Beer.select("name LIKE 'Vic%'").count() == 0  # This will pass
def flush_database_caches():
Flush all database caches.

SQLObject caches field values from the database in SQLObject instances. If SQL statements are issued that change the state of the database behind SQLObject's back, these cached values will be invalid.

This function iterates through all the objects in the SQLObject connection's cache, and synchronises them with the database. This ensures that they all reflect the values in the database.

def block_implicit_flushes(func):
A decorator that blocks implicit flushes on the main store.
def reset_store(func):
Function decorator that resets the main store.
def connect(user=None, dbname=None, isolation=ISOLATION_LEVEL_DEFAULT):
Return a fresh DB-API connection to the MAIN MASTER database.

Can be used without first setting up the Component Architecture, unlike the usual stores.

Default database name is the one specified in the main configuration file.

def connect_string(user=None, dbname=None):
Return a PostgreSQL connection string.

Allows you to pass the generated connection details to external programs like pg_dump or embed in slonik scripts.

def session_store():
Return a store connected to the session DB.
API Documentation for Launchpad, generated by pydoctor at 2018-11-19 00:00:14.