def testTypedSet(self):
handle = config.handle_factory()
SimpleReturn.register_composite('public.test_table', handle)
f = FunctionTyped('test',[])
cur = f()
for item in cur.fetchall():
self.assertTrue(isinstance(item, SimpleReturn), 'Each "row" in FunctionTyped cursor result is a single model object')
def setUpModule():
cfg = configparser.ConfigParser()
test_ini = os.path.join(os.path.dirname(__file__),"test.ini")
ini = cfg.read(test_ini)
try:
assert(ini)
except AssertionError:
print("Could not read test.ini in the simpycity.test module")
raise
config.database = cfg.get("simpycity","database")
config.port = cfg.get("simpycity","port")
config.user = cfg.get("simpycity","user")
config.host = cfg.get("simpycity","host")
config.password = cfg.get("simpycity","password")
config.debug = cfg.getboolean("simpycity","debug")
config.handle_factory = test_handle_factory
# clean up state
h = open( os.path.join(os.path.dirname(__file__),"sql", "test_unload.sql"),"r")
destroy_sql = h.read()
h.close()
handle = config.handle_factory() #, isolation_level=0)
with handle.transaction():
try:
handle.execute(destroy_sql)
except psycopg2.ProgrammingError:
pass
def testQueryTyped(self):
handle = config.handle_factory()
QueryTypedModel.register_composite('public.test_table', handle)
model = QueryTypedModel(id=1, handle=handle)
self.assertEqual(model.value, 'one', 'QueryTypedModel loads correctly')
cur = model.get(id=2, options={'handle': handle})
o = cur.fetchone()
self.assertEqual(o.value, 'two', 'QueryTyped returns correctly')
def setUp(self):
setUpModule()
h = open( os.path.join(os.path.dirname(__file__),"sql", "test.sql"),"r")
create_sql = h.read()
h.close()
handle = config.handle_factory()
with handle.transaction():
handle.execute(create_sql)
handle.commit()
def tearDown(self):
handle = config.handle_factory()
handle.rollback()
#handle.close()
h = open( os.path.join(os.path.dirname(__file__),"sql", "test_unload.sql"),"r")
destroy_sql = h.read()
h.close()
with handle.transaction():
handle.execute(destroy_sql)
def testTypeRegistrationLazy(self):
handle = config.handle_factory()
SimpleLazyLoaderModel.register_composite('public.test_table', handle)
f = FunctionTypedSingle('test_get',['id'], handle=handle)
model = f(id=1)
self.assertTrue(isinstance(model, SimpleLazyLoaderModel), 'result row is registered class instance')
self.assertEqual(
model.value,
"one",
"Model value is not 'Test row', got %s" % model.value
)
def testNestedModel(self):
handle = config.handle_factory()
SimpleLazyLoaderModel.register_composite('public.test_table', handle)
NestedModel.register_composite('public.nested', handle)
model = NestedModel(id=1)
self.assertTrue(isinstance(model, NestedModel), 'result row is registered class instance')
self.assertEqual(
model.value,
"one",
"Model value is not 'Test row', got %s" % model.value
)
self.assertTrue(isinstance(model.others, list), 'model.others is a list')
def __execute__(self,
columns,
call_list,
handle=None,
callback=None,
extra_opt={}):
'''
Runs the stored query in a psycopg2 cursor based on the arguments provided to
*__call__*.
If the instance handle is ``None`` and also the handle parameter is ``None``, a handle
is created from *simpycity.config.handle_factory*.
:param dict extra_opt: a dict passed to *form_query*
:return: psycopg2 cursor
'''
query = self.form_query(columns, options=extra_opt)
d_out("meta_query __execute__: Handle is %s" % handle)
if handle is None:
if self.__attr__['handle'] is None:
d_out(
"meta_query.__execute__: Did not find handle, creating new.. "
)
handle = config.handle_factory()
self.__attr__['handle'] = handle
d_out("meta_query.__execute__: Handle is %s" %
self.__attr__['handle'])
else:
d_out("meta_query.__execute__: Found object handle.. ")
handle = self.__attr__['handle']
cursor = handle.cursor(cursor_factory=self.cursor_factory,
callback=callback)
d_out("meta_query.__execute__: Cursor is %s" % cursor)
d_out("meta_query.__execute__: Query: %s" % (query))
d_out("meta_query.__execute__: Call List: %s" % (call_list))
try:
cursor.execute(query, call_list)
except psycopg2.OperationalError as e:
# retry query on stale connection error
d_out("OperationalError: %s" % e)
cursor = handle.cursor()
cursor.execute(query, call_list)
return cursor
def testFunctionCallback(self):
callback_value = 'value one'
def test_callback(row):
print('executing callback on row: {0}'.format(repr(row)))
model = row[0]
model.callback_attrib = callback_value
return row
handle = config.handle_factory()
SimpleReturn.register_composite('public.test_table', handle)
f = FunctionTyped('test',[], callback=test_callback)
cur = f()
for model in cur.fetchall():
self.assertTrue(model.callback_attrib == callback_value, 'Function-initiallized callback was executed on row with id={0}'.format(model.id))
callback_value = 'value two'
cur = f(options={'callback': test_callback})
for model in cur:
self.assertTrue(model.callback_attrib == callback_value, 'Function call callback was executed on row with id={0}'.format(model.id))
def __execute__(self, columns, call_list, handle=None, callback=None, extra_opt={}):
'''
Runs the stored query in a psycopg2 cursor based on the arguments provided to
*__call__*.
If the instance handle is ``None`` and also the handle parameter is ``None``, a handle
is created from *simpycity.config.handle_factory*.
:param dict extra_opt: a dict passed to *form_query*
:return: psycopg2 cursor
'''
query = self.form_query(columns, options=extra_opt)
d_out("meta_query __execute__: Handle is %s" % handle)
if handle is None:
if self.__attr__['handle'] is None:
d_out("meta_query.__execute__: Did not find handle, creating new.. ")
handle = config.handle_factory()
self.__attr__['handle'] = handle
d_out("meta_query.__execute__: Handle is %s" % self.__attr__['handle'])
else:
d_out("meta_query.__execute__: Found object handle.. ")
handle = self.__attr__['handle']
cursor = handle.cursor(cursor_factory=self.cursor_factory, callback=callback)
d_out("meta_query.__execute__: Cursor is %s" % cursor)
d_out("meta_query.__execute__: Query: %s" % ( query ) )
d_out("meta_query.__execute__: Call List: %s" % ( call_list ) )
try:
cursor.execute(query, call_list)
except psycopg2.OperationalError as e:
# retry query on stale connection error
d_out("OperationalError: %s" % e)
cursor = handle.cursor()
cursor.execute(query, call_list)
return cursor
def register_composite(cls, name, handle=None, factory=None):
"""
Maps a Postgresql type to this class. If the class's *table* attribute
is empty, and the class has an attribute *pg_type* of tuple (schema, type),
it is calculated and set by querying Postgres. Register inherited/inheriting
classes in heirarchical order.
Every time a SQL function returns a registered type (including array
elements and individual columns, recursively), this class
will be instantiated automatically.
The object attributes will be passed to the provided callable in
a form of keyword arguments.
:param str name: the name of a PostgreSQL composite type, e.g. created using
the *CREATE TYPE* command
:param simpycity.handle.Handle handle:
:param psycopg2.extras.CompositeCaster factory: use
it to customize how to cast composite types
:return: the registered *CompositeCaster* instance
responsible for the conversion
"""
class CustomCompositeCaster(psycopg2.extras.CompositeCaster):
def make(self, values):
d_out("CustomCompositeCaster.make: cls={0} values={1}".format(
repr(cls), repr(values)))
return cls(**dict(list(zip(self.attnames, values))))
PG_TYPE_SQL = """SELECT array_agg(attname)
FROM
(
SELECT attname
FROM
pg_type t
JOIN pg_namespace ns ON typnamespace = ns.oid
JOIN pg_attribute a ON attrelid = typrelid
WHERE nspname = %s AND typname = %s
AND attnum > 0 AND NOT attisdropped
ORDER BY attnum
) sub;"""
if handle is None:
handle = g_config.handle_factory()
d_out("SimpleModel.register_composite: before: table for {0} is {1}".
format(repr(cls.pg_type), cls.table))
if cls.pg_type is not None:
super_table = cls.__mro__[1].table if hasattr(
cls.__mro__[1], 'table') else []
if cls.table == [] or cls.table is super_table:
cursor = handle.cursor()
cursor.execute(PG_TYPE_SQL, cls.pg_type)
row = cursor.fetchone()
d_out("SimpleModel.register_composite: row={0}".format(row))
row[0] = [_ for _ in row[0] if _ != 'base_']
cls.table = cls.table + row[0]
d_out(
"SimpleModel.register_composite: after: table for {0} is {1}"
.format(repr(cls.pg_type), cls.table))
if factory is None:
factory = CustomCompositeCaster
if sys.version_info[0] < 3:
name = str(name)
return psycopg2.extras.register_composite(
name,
handle.conn,
globally=True, # in case of reconnects
factory=factory)
def register_composite(cls, name, handle=None, factory=None):
"""
Maps a Postgresql type to this class. If the class's *table* attribute
is empty, and the class has an attribute *pg_type* of tuple (schema, type),
it is calculated and set by querying Postgres. Register inherited/inheriting
classes in heirarchical order.
Every time a SQL function returns a registered type (including array
elements and individual columns, recursively), this class
will be instantiated automatically.
The object attributes will be passed to the provided callable in
a form of keyword arguments.
:param str name: the name of a PostgreSQL composite type, e.g. created using
the *CREATE TYPE* command
:param simpycity.handle.Handle handle:
:param psycopg2.extras.CompositeCaster factory: use
it to customize how to cast composite types
:return: the registered *CompositeCaster* instance
responsible for the conversion
"""
class CustomCompositeCaster(psycopg2.extras.CompositeCaster):
def make(self, values):
d_out("CustomCompositeCaster.make: cls={0} values={1}".format(repr(cls), repr(values)))
return cls(**dict(list(zip(self.attnames, values))))
PG_TYPE_SQL = """SELECT array_agg(attname)
FROM
(
SELECT attname
FROM
pg_type t
JOIN pg_namespace ns ON typnamespace = ns.oid
JOIN pg_attribute a ON attrelid = typrelid
WHERE nspname = %s AND typname = %s
AND attnum > 0 AND NOT attisdropped
ORDER BY attnum
) sub;"""
if handle is None:
handle = g_config.handle_factory()
d_out("SimpleModel.register_composite: before: table for {0} is {1}".format(repr(cls.pg_type), cls.table))
if cls.pg_type is not None:
super_table = cls.__mro__[1].table if hasattr(cls.__mro__[1], 'table') else []
if cls.table == [] or cls.table is super_table:
cursor = handle.cursor()
cursor.execute(PG_TYPE_SQL, cls.pg_type)
row = cursor.fetchone()
d_out("SimpleModel.register_composite: row={0}".format(row))
row[0] = [_ for _ in row[0] if _ != 'base_']
cls.table = cls.table + row[0]
d_out("SimpleModel.register_composite: after: table for {0} is {1}".format(repr(cls.pg_type), cls.table))
if factory is None:
factory = CustomCompositeCaster
if sys.version_info[0] < 3:
name = str(name)
return psycopg2.extras.register_composite(
name,
handle.conn,
globally=True, # in case of reconnects
factory=factory
)
def testDynamicModel(self):
handle = config.handle_factory()
DynamicModel.register_composite('public.test_table', handle)
model = DynamicModel()
self.assertEqual(model.table, SimpleReturn.table, 'table is determined automatically')