def test_pg_select_count(orb, User, pg_sql, pg_db):
select_st = pg_sql.statement('SELECT')
select_sql, data = select_st(User, orb.Context())
conn = pg_db.connection()
records, count = conn.execute(select_sql, data)
select_count_st = pg_sql.statement('SELECT COUNT')
select_count_sql, data = select_count_st(User, orb.Context())
results, _ = conn.execute(select_count_sql, data)
assert results[0]['count'] == count
def test_pg_statement_create_table_in_namespace(User, pg_sql):
import orb
with orb.Context(namespace='custom'):
st = pg_sql.statement('CREATE')
assert st is not None
statement, data = st(User)
assert 'CREATE TABLE IF NOT EXISTS "custom"."users"' in statement
def test_pg_select_bob_and_sally(orb, pg_sql, pg_db, User):
st = pg_sql.statement('SELECT')
q = orb.Query('username') == 'bob'
q &= orb.Query('username') == 'sally'
sql, data = st(User, orb.Context(where=q))
conn = pg_db.connection()
_, count = conn.execute(sql, data)
assert count == 0
def test_my_select_bob_or_sally(orb, my_sql, my_db, User):
st = my_sql.statement('SELECT')
q = orb.Query('username') == 'bob'
q |= orb.Query('username') == 'sally'
sql, data = st(User, orb.Context(where=q))
conn = my_db.connection()
records, count = conn.execute(sql, data)
assert count == 2 and records[0]['username'] in ('bob', 'sally') and records[1]['username'] in ('bob', 'sally')
def test_pg_statement_insert_records_in_namespace(orb, User, pg_sql):
user_a = User(username='******')
user_b = User(username='******')
with orb.Context(namespace='custom'):
st = pg_sql.statement('INSERT')
assert st is not None
statement, data = st([user_a, user_b])
assert 'INSERT INTO "custom"."users"' in statement
def search(self, model, terms, **context):
search_context = context.get('context') or orb.Context(**context)
locale = search_context.locale
nodes = self.__parser.parseString(terms)
# separate into 2 categories general (searchable by any column) and specific (user gave a column)
general_nodes = [
node for node in nodes if not isinstance(node, ComparisonNode)
]
comparison_nodes = [
node for node in nodes if isinstance(node, ComparisonNode)
]
# build general search column matches
q = orb.Query()
if general_nodes:
expr = u'.*\s{0}'
pattern = u'(^|.*\s){0}'.format(general_nodes[0].pattern(
self.__thesaurus, locale))
pattern += ''.join(
expr.format(node.pattern(self.__thesaurus, locale))
for node in general_nodes[1:])
general_q = orb.Query()
searchable_columns = model.schema().columns(
flags=orb.Column.Flags.Searchable).values()
if not searchable_columns:
raise orb.errors.InvalidSearch(
'No searchable columns found for {0}'.format(
model.schema().name()))
for column in searchable_columns:
general_q |= orb.Query(column).asString().matches(
pattern, caseSensitive=False)
q &= general_q
# build comparison nodes
if comparison_nodes:
schema = model.schema()
for node in comparison_nodes:
column = schema.column(node[0])
value_node = node[-1]
value = value_node[0]
q &= orb.Query(column) == value
if not q.isNull():
context['where'] = q & context.get('where')
return model.select(**context)
def execute(self,
command,
data=None,
returning=True,
mapper=dict,
writeAccess=False,
dryRun=False,
locale=None):
"""
Executes the inputted command into the current \
connection cursor.
:param command | <str>
data | <dict> || None
autoCommit | <bool> | commit database changes immediately
autoClose | <bool> | closes connections immediately
returning | <bool>
mapper | <variant>
retries | <int>
:return [{<str> key: <variant>, ..}, ..], <int> rowcount
"""
command = command.strip()
if not command:
raise orb.errors.EmptyCommand()
elif dryRun:
print command % data
raise orb.errors.DryRun()
# define properties for execution
data = data or {}
command = command.strip()
data.setdefault('locale', locale or orb.Context().locale)
start = datetime.datetime.now()
try:
with self.native() as conn:
results, rowcount = self._execute(conn, command, data,
returning, mapper)
# always raise interruption errors as these need to be handled
# from a thread properly
except orb.errors.Interruption:
delta = datetime.datetime.now() - start
log.critical('Query took: %s' % delta)
raise
# handle any known a database errors with feedback information
except orb.errors.DatabaseError as err:
delta = datetime.datetime.now() - start
log.error(u'{0}: \n {1}'.format(err, command))
log.error('Query took: %s' % delta)
raise
# always raise any unknown issues for the developer
except StandardError as err:
delta = datetime.datetime.now() - start
log.error(u'{0}: \n {1}'.format(err, command))
log.error('Query took: %s' % delta)
raise
delta = (datetime.datetime.now() - start).total_seconds()
if delta * 1000 < 3000:
lvl = logging.DEBUG
elif delta * 1000 < 6000:
lvl = logging.WARNING
else:
lvl = logging.CRITICAL
log.log(lvl, 'Query took: %s' % delta)
return results, rowcount
def test_pg_select_bob(orb, User, pg_sql, pg_db):
st = pg_sql.statement('SELECT')
sql, data = st(User, orb.Context(where=orb.Query('username') == 'bob'))
conn = pg_db.connection()
records, count = conn.execute(sql, data)
assert count == 1 and records[0]['username'] == 'bob'
def test_pg_select_one(orb, User, pg_sql, pg_db):
st = pg_sql.statement('SELECT')
sql, data = st(User, orb.Context(limit=1))
conn = pg_db.connection()
records, count = conn.execute(sql, data)
assert count == 1
def test_pg_select_all(orb, User, pg_sql, pg_db):
st = pg_sql.statement('SELECT')
sql, data = st(User, orb.Context())
conn = pg_db.connection()
records, count = conn.execute(sql, data)
assert len(records) == count
def get_context(request, model=None):
"""
Extracts ORB context information from the request.
:param request: <pyramid.request.Request>
:param model: <orb.Model> || None
:return: {<str> key: <variant> value} values, <orb.Context>
"""
# convert request parameters to python
param_values = get_param_values(request, model=model)
# extract the full orb context if provided
context = param_values.pop('orb_context', {})
if isinstance(context, (unicode, str)):
context = projex.rest.unjsonify(context)
# otherwise, extract the limit information
has_limit = 'limit' in context or 'limit' in param_values
# create the new orb context
orb_context = orb.Context(**context)
# build up context information from the request params
used = set()
query_context = {}
for key in orb.Context.Defaults:
if key in param_values:
used.add(key)
query_context[key] = param_values.get(key)
# generate a simple query object
schema_values = {}
if model:
# extract match dict items
for key, value in request.matchdict.items():
if model.schema().column(key, raise_=False):
schema_values[key] = value
# extract payload items
for key, value in param_values.items():
schema_object = model.schema().column(
key, raise_=False) or model.schema().collector(key)
if schema_object:
value = param_values.pop(key)
if isinstance(
schema_object,
orb.Collector) and type(value) not in (tuple, list):
value = [value]
schema_values[key] = value
# generate the base context information
query_context['scope'] = {'request': request}
# include any request specific scoping information
try:
query_context['scope'].update(request.orb_scope)
except AttributeError:
pass
orb_context.update(query_context)
# set the default limit if none is provided
if not has_limit and orb_context.returning == 'records':
orb_context.limit = DEFAULT_MAX_LIMIT
return schema_values, orb_context