def __init__(self,server,sitename=None,storage_path=None):
self.server = server
self.page_register = PageRegister(self)
self.connection_register = ConnectionRegister(self)
self.user_register = UserRegister(self)
self.remotebag_handler = RemoteStoreBagHandler(self)
self.server.daemon.register(self.remotebag_handler,'RemoteData')
self.last_cleanup = time.time()
self.sitename = sitename
self.storage_path = storage_path
self.catalog = GnrClassCatalog()
self.maintenance = False
self.allowed_users = None
def __init__(self,
implementation='sqlite',
dbname='mydb',
host=None,
user=None,
password=None,
port=None,
main_schema=None,
debugger=None,
application=None,
allow_eager_many=False,
allow_eager_one=False):
"""This is the constructor method of the GnrSqlDb class.
:param implementation: 'sqlite', 'postgres' or other sql implementations.
:param dbname: the name for your db.
:param host: the database server host (for sqlite is None)
:param user: a database user's name (for sqlite is None)
:param password: the user's password (for sqlite is None)
:param port: the connection port (for sqlite is None)
:param main_schema: the database main_schema. Default value is ``None``
:param debugger: the database main_schema. Default value is ``None``
:param application: the database main_schema. Default value is ``None``
:param allow_eager_many: the database main_schema. Default value is ``False``
:param allow_eager_one: the database main_schema. Default value is ``False``"""
self.implementation = implementation
self.dbname = dbname
self.host = host
self.port = port
self.user = user
self.password = password
self.typeConverter = GnrClassCatalog()
self.debugger = debugger
self.application = application
self.model = self.createModel()
self.adapter = importModule('gnr.sql.adapters.gnr%s' %
implementation).SqlDbAdapter(self)
self.whereTranslator = self.adapter.getWhereTranslator()
if main_schema is None:
main_schema = self.adapter.defaultMainSchema()
self.main_schema = main_schema
self._connections = {}
self.started = False
self._currentEnv = {}
self.allow_eager_one = allow_eager_one
self.allow_eager_many = allow_eager_many
self.stores_handler = DbStoresHandler(self)
def __init__(self, implementation='sqlite', dbname='mydb',
host=None, user=None, password=None, port=None,
main_schema=None, debugger=None, application=None, read_only=None,**kwargs):
"""
This is the constructor method of the GnrSqlDb class.
:param implementation: 'sqlite', 'postgres' or other sql implementations
:param dbname: the name for your database
:param host: the database server host (for sqlite is None)
:param user: the username (for sqlite is None)
:param password: the username's password (for sqlite is None)
:param port: the connection port (for sqlite is None)
:param main_schema: the database main schema
:param debugger: TODO
:param application: TODO
"""
self.implementation = implementation
self.dbname = self.dbpar(dbname)
self.host = self.dbpar(host)
self.port = self.dbpar(str(port) if port else None)
self.user = self.dbpar(user)
self.password = self.dbpar(password)
self.read_only = read_only
self.typeConverter = GnrClassCatalog()
self.debugger = debugger
self.application = application
self.model = self.createModel()
self.adapter = importModule('gnr.sql.adapters.gnr%s' % implementation).SqlDbAdapter(self)
self.whereTranslator = self.adapter.getWhereTranslator()
if main_schema is None:
main_schema = self.adapter.defaultMainSchema()
self.main_schema = main_schema
self._connections = {}
self.started = False
self._currentEnv = {}
self.stores_handler = DbStoresHandler(self)
def __init__(self, db):
self.db = db
self.catalog = GnrClassCatalog()
self.opDict = dict([(k[3:], None) for k in dir(self)
if k.startswith('op_')])
class GnrWhereTranslator(object):
def __init__(self, db):
self.db = db
self.catalog = GnrClassCatalog()
self.opDict = dict([(k[3:], None) for k in dir(self)
if k.startswith('op_')])
def __call__(self, tblobj, wherebag, sqlArgs, customOpCbDict=None):
if sqlArgs is None:
sqlArgs = {}
self.customOpCbDict = customOpCbDict
result = self.innerFromBag(tblobj, wherebag, sqlArgs, 0)
return '\n'.join(result)
def opCaption(self, op, localize=False):
h = getattr(self, 'op_%s' % op.lower(), None)
if not h and op.startswith('not_'):
return 'Not %s' % getattr(self, 'op_%s' % op[4:].lower()).__doc__
result = h.__doc__
if localize and self.db.localizer:
result = self.db.localizer.translateText(result)
return result
def toText(self, tblobj, wherebag, level=0, decodeDate=False):
result = []
for k, node in enumerate(wherebag.getNodes()):
attr = node.getAttr()
value = node.getValue()
if k:
jc = attr.get('jc_caption', '')
else:
jc = ''
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = ('\n' + ' ' * level).join(
self.toText(tblobj, value, level + 1))
onecondition = '(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op_caption')
column = attr.get('column_caption')
if not op or not column:
continue
if decodeDate:
if tblobj.column(attr.get('column')).dtype in ('D', 'DH',
'DHZ'):
value, op = self.decodeDates(value, op, 'D')
op = self.opCaption(op, localize=True)
op = op.lower()
onecondition = '%s %s %s' % (column, op, value)
if onecondition:
if negate:
onecondition = ' %s %s ' % (attr.get('not_caption',
''), onecondition)
result.append(' %s %s' % (jc, onecondition))
return result
def toHtml(self, tblobj, wherebag, level=0, decodeDate=False):
result = []
for k, node in enumerate(wherebag.getNodes()):
attr = node.getAttr()
value = node.getValue()
if k:
jc = attr.get('jc_caption', '')
else:
jc = ''
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = '<div class="slqnested"> %s </div>' % (
self.toHtml(tblobj, value, level + 1)
) #'(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op_caption')
column = attr.get('column_caption')
if not op or not column:
continue
if decodeDate:
if tblobj.column(attr.get('column')).dtype in ('D', 'DH',
'DHZ'):
value, op = self.decodeDates(value, op, 'D')
op = self.opCaption(op, localize=True)
op = op.lower()
onecondition = '<span class="sqlcol">%s</span> <span class="sqlop">%s</span> <span class="sqlvalue">%s</span>' % (
column, op, value)
if onecondition:
if negate:
onecondition = ' <span class="sqlnot">%s</span> %s ' % (
attr.get('not_caption', ''), onecondition)
result.append(
'<div class="sqlcondition"> <span class="sqljc">%s</span> %s </div>'
% (jc, onecondition))
return ''.join(result)
def innerFromBag(self, tblobj, wherebag, sqlArgs, level):
"""<condition column="fattura_num" op="ISNULL" rem='senza fattura' />
<condition column="@anagrafica.provincia" op="IN" jc='AND'>MI,FI,TO</condition>
<group not="true::B" jc='AND'>
<condition column="" op=""/>
<condition column="" op="" jc='OR'/>
</group>"""
result = []
for node in wherebag:
attr = node.getAttr()
value = node.getValue()
if isinstance(value, basestring) and value.startswith('?'):
value = sqlArgs.get(value[1:])
jc = attr.get('jc', '').upper()
if not result:
jc = ''
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = ('\n' + ' ' * level).join(
self.innerFromBag(tblobj, value, sqlArgs, level + 1))
onecondition = '(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op')
column = attr.get('column')
if not op or not column:
#ingnoring empty query lines
continue
colobj = tblobj.column(column)
if colobj is None:
raise tblobj.exception('not_existing_column',
column=column)
dtype = colobj.dtype
if value is None and attr.get('value_caption'):
value = sqlArgs.pop(attr['value_caption'])
onecondition = self.prepareCondition(column,
op,
value,
dtype,
sqlArgs,
tblobj=tblobj)
if onecondition:
if negate:
onecondition = ' NOT %s ' % onecondition
result.append(' %s ( %s )' % (jc, onecondition))
return result
def prepareCondition(self, column, op, value, dtype, sqlArgs, tblobj=None):
if not dtype:
dtype = tblobj.column(column).dtype
if not column[0] in '@$':
column = '$%s' % column
if dtype in ('D', 'DH', 'DHZ'):
value, op = self.decodeDates(value, op, 'D')
if dtype == 'DH':
column = 'CAST (%s AS date)' % column
if not dtype in ('A', 'T') and op in ('contains', 'notcontains',
'startswith', 'endswith',
'regex', 'wordstart'):
value = str(value)
column = 'CAST (%s as text)' % column
dtype = 'A'
ophandler = getattr(self, 'op_%s' % op, None)
if ophandler:
result = ophandler(column=column,
value=value,
dtype=dtype,
sqlArgs=sqlArgs,
tblobj=tblobj)
else:
ophandler = self.customOpCbDict.get(op)
assert ophandler, 'undefined ophandler'
result = ophandler(column=column,
value=value,
dtype=dtype,
sqlArgs=sqlArgs,
whereTranslator=self,
tblobj=tblobj)
return result
def decodeDates(self, value, op, dtype):
if op == 'isnull':
return value, op
if op == 'in' and ',' in value: # is a in search with multiple (single date) arguments: don't use periods!!!
value = ','.join([
decodeDatePeriod(v,
workdate=self.db.workdate,
locale=self.db.locale,
dtype=dtype) for v in value.split(',')
])
return value, op
value = decodeDatePeriod(value,
workdate=self.db.workdate,
locale=self.db.locale,
dtype=dtype)
mode = None
if value.startswith(';'):
mode = 'to'
elif value.endswith(';'):
mode = 'from'
elif ';' in value:
mode = 'period'
if op in ('greater', 'greatereq'): # keep the higher date
if mode in ('period', 'to'):
value = value.split(';')[1]
else:
value = value.strip(';')
elif op in ('less', 'lesseq'): # keep the lower date
value = value.split(';')[0]
else:
# equal, between and textual operators are ignored
# the right operator is choosen according to the value to find
if mode == 'to': # find data lower then value
op = 'lesseq'
value = value.strip(';')
elif mode == 'from': # find data higher then value
op = 'greatereq'
value = value.strip(';')
elif mode == 'period': # find data between period (value)
op = 'between'
else: # value is a single date
op = 'equal'
return value, op
def storeArgs(self, value, dtype, sqlArgs):
if not dtype in ('A', 'T'):
if isinstance(value, list):
value = [self.catalog.fromText(v, dtype) for v in value]
elif isinstance(value, basestring):
value = self.catalog.fromText(value, dtype)
argLbl = 'v_%i' % len(sqlArgs)
sqlArgs[argLbl] = value
return argLbl
def op_startswithchars(self, column, value, dtype, sqlArgs, tblobj):
"!!Starts with Chars"
return self.unaccentTpl(tblobj, column, 'LIKE') % (
column, self.storeArgs('%s%%' % value, dtype, sqlArgs))
def op_equal(self, column, value, dtype, sqlArgs, tblobj):
"!!Equal to"
return self.unaccentTpl(tblobj, column, '=') % (
column, self.storeArgs(value, dtype, sqlArgs))
def op_startswith(self, column, value, dtype, sqlArgs, tblobj):
"!!Starts with"
return self.unaccentTpl(tblobj, column, 'ILIKE') % (
column, self.storeArgs('%s%%' % value, dtype, sqlArgs))
def op_wordstart(self, column, value, dtype, sqlArgs, tblobj):
"!!Word start"
value = value.replace('(', '\(').replace(')', '\)').replace(
'[', '\[').replace(']', '\]')
return self.unaccentTpl(tblobj, column, '~*') % (
column, self.storeArgs('(^|\\W)%s' % value, dtype, sqlArgs))
def op_contains(self, column, value, dtype, sqlArgs, tblobj):
"!!Contains"
return self.unaccentTpl(tblobj, column, 'ILIKE') % (
column, self.storeArgs('%%%s%%' % value, dtype, sqlArgs))
def op_greater(self, column, value, dtype, sqlArgs, tblobj):
"!!Greater than"
return self.unaccentTpl(tblobj, column, '>') % (
column, self.storeArgs(value, dtype, sqlArgs))
def op_greatereq(self, column, value, dtype, sqlArgs, tblobj):
"!!Greater or equal to"
return self.unaccentTpl(tblobj, column, '>=') % (
column, self.storeArgs(value, dtype, sqlArgs))
def op_less(self, column, value, dtype, sqlArgs, tblobj):
"!!Less than"
return self.unaccentTpl(tblobj, column, '<') % (
column, self.storeArgs(value, dtype, sqlArgs))
def op_lesseq(self, column, value, dtype, sqlArgs, tblobj):
"!!Less or equal to"
return self.unaccentTpl(tblobj, column, '<=') % (
column, self.storeArgs(value, dtype, sqlArgs))
def op_between(self, column, value, dtype, sqlArgs, tblobj):
"!!Between"
v1, v2 = value.split(';')
return '%s BETWEEN :%s AND :%s' % (column,
self.storeArgs(v1, dtype, sqlArgs),
self.storeArgs(v2, dtype, sqlArgs))
def op_isnull(self, column, value, dtype, sqlArgs, tblobj):
"!!Is null"
return '%s IS NULL' % column
def op_istrue(self, column, value, dtype, sqlArgs, tblobj):
"!!Is true"
return '%s IS TRUE' % column
def op_isfalse(self, column, value, dtype, sqlArgs, tblobj):
"!!Is false"
return '%s IS FALSE' % column
def op_nullorempty(self, column, value, dtype, sqlArgs, tblobj):
"!!Is null or empty"
if dtype in ('L', 'N', 'M', 'R'):
return self.op_isnull(column, value, dtype, sqlArgs)
return " (%s IS NULL OR %s ='')" % (column, column)
def op_in(self, column, value, dtype, sqlArgs, tblobj):
"!!In"
values_string = self.storeArgs(value.split(','), dtype, sqlArgs)
return '%s IN :%s' % (column, values_string)
def op_regex(self, column, value, dtype, sqlArgs, tblobj):
"!!Regular expression"
return '%s ~* :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
#def whereFromText(self, table, whereTxt, customColumns=None):
# result = []
# sqlArgs = {}
# tblobj = self.db.table(table)
# pattern = '(AND|OR)'
# whereList = re.compile(pattern).split(whereTxt)
# condList = [cond for cond in whereList if cond not in ('AND', 'OR')]
def unaccentTpl(self, tblobj, column, token):
return ' '.join(['%s', token, ':%s'])
def whereFromDict(self, table, whereDict, customColumns=None):
result = []
sqlArgs = {}
tblobj = self.db.table(table)
for k, v in whereDict.items():
negate = ''
op = 'equal'
ksplit = k.split('_')
if ksplit[-1].lower() in self.opDict:
op = ksplit.pop().lower()
if ksplit[-1].lower() == 'not':
negate = ' NOT '
ksplit.pop()
column = '_'.join(ksplit)
if customColumns and column in customColumns:
custom = customColumns[column]
if callable(custom):
condition = custom(column, sqlArgs)
if isinstance(custom, basestring):
dtype = tblobj.column(custom).dtype
column = custom
elif isinstance(custom, tuple):
column, dtype = custom
else:
raise
else:
colobj = tblobj.column('$%s' % column)
if colobj is None:
raise
dtype = colobj.dtype
condition = self.prepareCondition(column,
op,
v,
dtype,
sqlArgs,
tblobj=tblobj)
result.append('%s%s' % (negate, condition))
return result, sqlArgs
def __init__(self, db):
self.db = db
self.catalog = GnrClassCatalog()
self.opDict = dict([(k[3:], None) for k in dir(self) if k.startswith('op_')])
class GnrWhereTranslator(object):
def __init__(self, db):
self.db = db
self.catalog = GnrClassCatalog()
self.opDict = dict([(k[3:], None) for k in dir(self) if k.startswith('op_')])
def __call__(self, tblobj, wherebag, sqlArgs, customOpCbDict=None):
if sqlArgs is None:
sqlArgs = {}
self.customOpCbDict = customOpCbDict
result = self.innerFromBag(tblobj, wherebag, sqlArgs, 0)
return '\n'.join(result)
def opCaption(self, op, localize=False):
h = getattr(self, 'op_%s' % op.lower(), None)
if not h and op.startswith('not_'):
return 'Not %s' % getattr(self, 'op_%s' % op[4:].lower()).__doc__
result = h.__doc__
if localize and self.db.localizer:
result = self.db.localizer.translateText(result)
return result
def toText(self, tblobj, wherebag, level=0, decodeDate=False):
result = []
for k, node in enumerate(wherebag.getNodes()):
attr = node.getAttr()
value = node.getValue()
if k:
jc = attr.get('jc_caption', '')
else:
jc = ''
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = ('\n' + ' ' * level).join(self.toText(tblobj, value, level + 1))
onecondition = '(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op_caption')
column = attr.get('column_caption')
if not op or not column:
continue
if decodeDate:
if tblobj.column(attr.get('column')).dtype in('D', 'DH'):
value, op = self.decodeDates(value, op, 'D')
op = self.opCaption(op, localize=True)
op = op.lower()
onecondition = '%s %s %s' % (column, op, value)
if onecondition:
if negate:
onecondition = ' %s %s ' % (attr.get('not_caption', ''), onecondition)
result.append(' %s %s' % (jc, onecondition ))
return result
def toHtml(self, tblobj, wherebag, level=0, decodeDate=False):
result = []
for k, node in enumerate(wherebag.getNodes()):
attr = node.getAttr()
value = node.getValue()
if k:
jc = attr.get('jc_caption', '')
else:
jc = ''
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = '<div class="slqnested"> %s </div>' %(self.toHtml(tblobj, value, level + 1))#'(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op_caption')
column = attr.get('column_caption')
if not op or not column:
continue
if decodeDate:
if tblobj.column(attr.get('column')).dtype in('D', 'DH'):
value, op = self.decodeDates(value, op, 'D')
op = self.opCaption(op, localize=True)
op = op.lower()
onecondition = '<span class="sqlcol">%s</span> <span class="sqlop">%s</span> <span class="sqlvalue">%s</span>' % (column, op, value)
if onecondition:
if negate:
onecondition = ' <span class="sqlnot">%s</span> %s ' % (attr.get('not_caption', ''), onecondition)
result.append('<div class="sqlcondition"> <span class="sqljc">%s</span> %s </div>' % (jc, onecondition ))
return ''.join(result)
def innerFromBag(self, tblobj, wherebag, sqlArgs, level):
"""<condition column="fattura_num" op="ISNULL" rem='senza fattura' />
<condition column="@anagrafica.provincia" op="IN" jc='AND'>MI,FI,TO</condition>
<group not="true::B" jc='AND'>
<condition column="" op=""/>
<condition column="" op="" jc='OR'/>
</group>"""
result = []
for node in wherebag:
attr = node.getAttr()
value = node.getValue()
if isinstance(value, basestring) and value.startswith('?'):
value = sqlArgs.get(value[1:])
jc = attr.get('jc', '').upper()
negate = attr.get('not') == 'not'
if isinstance(value, Bag):
onecondition = ('\n' + ' ' * level).join(self.innerFromBag(tblobj, value, sqlArgs, level + 1))
onecondition = '(\n' + ' ' * level + onecondition + '\n' + ' ' * level + ')'
else:
op = attr.get('op')
column = attr.get('column')
if not op or not column:
#ingnoring empty query lines
continue
colobj=tblobj.column(column)
if colobj is None:
raise tblobj.exception('not_existing_column', column=column)
dtype = colobj.dtype
if value is None and attr.get('value_caption'):
value = sqlArgs.pop(attr['value_caption'])
onecondition = self.prepareCondition(column, op, value, dtype, sqlArgs,tblobj=tblobj)
if onecondition:
if negate:
onecondition = ' NOT %s ' % onecondition
result.append(' %s ( %s )' % (jc, onecondition ))
return result
def prepareCondition(self, column, op, value, dtype, sqlArgs,tblobj=None):
if not column[0] in '@$':
column = '$%s' % column
if dtype in('D', 'DH'):
value, op = self.decodeDates(value, op, 'D')
if dtype=='DH':
column = 'CAST (%s AS date)' % column
if not dtype in ('A', 'T') and op in (
'contains', 'notcontains', 'startswith', 'endswith', 'regex', 'wordstart'):
value = str(value)
column = 'CAST (%s as text)' % column
dtype = 'A'
ophandler = getattr(self, 'op_%s' % op, None)
if ophandler:
result = ophandler(column=column, value=value, dtype=dtype, sqlArgs=sqlArgs,tblobj=tblobj)
else:
ophandler = self.customOpCbDict.get(op)
assert ophandler, 'undefined ophandler'
result = ophandler(column=column, value=value, dtype=dtype, sqlArgs=sqlArgs, whereTranslator=self,tblobj=tblobj)
return result
def decodeDates(self, value, op, dtype):
if op == 'isnull':
return value, op
if op == 'in' and ',' in value: # is a in search with multiple (single date) arguments: don't use periods!!!
value = ','.join(
[decodeDatePeriod(v, workdate=self.db.workdate, locale=self.db.locale, dtype=dtype) for v in
value.split(',')])
return value, op
value = decodeDatePeriod(value, workdate=self.db.workdate, locale=self.db.locale, dtype=dtype)
mode = None
if value.startswith(';'):
mode = 'to'
elif value.endswith(';'):
mode = 'from'
elif ';' in value:
mode = 'period'
if op in ('greater', 'greatereq'): # keep the higher date
if mode in ('period', 'to'):
value = value.split(';')[1]
else:
value = value.strip(';')
elif op in ('less', 'lesseq'): # keep the lower date
value = value.split(';')[0]
else:
# equal, between and textual operators are ignored
# the right operator is choosen according to the value to find
if mode == 'to': # find data lower then value
op = 'lesseq'
value = value.strip(';')
elif mode == 'from': # find data higher then value
op = 'greatereq'
value = value.strip(';')
elif mode == 'period': # find data between period (value)
op = 'between'
else: # value is a single date
op = 'equal'
return value, op
def storeArgs(self, value, dtype, sqlArgs):
if not dtype in ('A', 'T'):
if isinstance(value, list):
value = [self.catalog.fromText(v, dtype) for v in value]
elif isinstance(value, basestring):
value = self.catalog.fromText(value, dtype)
argLbl = 'v_%i' % len(sqlArgs)
sqlArgs[argLbl] = value
return argLbl
def op_startswithchars(self, column, value, dtype, sqlArgs,tblobj):
"Starts with Chars"
return '%s LIKE :%s' % (column, self.storeArgs('%s%%' % value, dtype, sqlArgs))
def op_equal(self, column, value, dtype, sqlArgs,tblobj):
"Equal to"
return '%s = :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
def op_startswith(self, column, value, dtype, sqlArgs,tblobj):
"Starts with"
return '%s ILIKE :%s' % (column, self.storeArgs('%s%%' % value, dtype, sqlArgs))
def op_wordstart(self, column, value, dtype, sqlArgs,tblobj):
"Word start"
value = value.replace('(', '\(').replace(')', '\)').replace('[', '\[').replace(']', '\]')
return '%s ~* :%s' % (column, self.storeArgs('(^|\\W)%s' % value, dtype, sqlArgs))
def op_contains(self, column, value, dtype, sqlArgs,tblobj):
"Contains"
return '%s ILIKE :%s' % (column, self.storeArgs('%%%s%%' % value, dtype, sqlArgs))
def op_similar(self, column, value, dtype, sqlArgs,tblobj):
"Similar"
phonetic_column = tblobj.column(column).attributes['phonetic']
phonetic_mode = tblobj.column(column).table.column(phonetic_column).attributes['phonetic_mode']
return '%s = %s(:%s)' % (phonetic_column, phonetic_mode, self.storeArgs(value, dtype, sqlArgs))
def op_greater(self, column, value, dtype, sqlArgs,tblobj):
"Greater than"
return '%s > :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
def op_greatereq(self, column, value, dtype, sqlArgs,tblobj):
"Greater or equal to"
return '%s >= :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
def op_less(self, column, value, dtype, sqlArgs,tblobj):
"Less than"
return '%s < :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
def op_lesseq(self, column, value, dtype, sqlArgs,tblobj):
"Less or equal to"
return '%s <= :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
def op_between(self, column, value, dtype, sqlArgs,tblobj):
"Between"
v1, v2 = value.split(';')
return '%s BETWEEN :%s AND :%s' % (
column, self.storeArgs(v1, dtype, sqlArgs), self.storeArgs(v2, dtype, sqlArgs))
def op_isnull(self, column, value, dtype, sqlArgs,tblobj):
"Is null"
return '%s IS NULL' % column
def op_istrue(self, column, value, dtype, sqlArgs,tblobj):
"Is true"
return '%s IS TRUE' % column
def op_isfalse(self, column, value, dtype, sqlArgs,tblobj):
"Is false"
return '%s IS FALSE' % column
def op_nullorempty(self, column, value, dtype, sqlArgs,tblobj):
"Is null or empty"
if dtype in ('L', 'N', 'M', 'R'):
return self.op_isnull(column, value, dtype, sqlArgs)
return " (%s IS NULL OR %s ='')" % (column, column)
def op_in(self, column, value, dtype, sqlArgs,tblobj):
"In"
values_string = self.storeArgs(value.split(','), dtype, sqlArgs)
return '%s IN :%s' % (column, values_string)
def op_regex(self, column, value, dtype, sqlArgs,tblobj):
"Regular expression"
return '%s ~* :%s' % (column, self.storeArgs(value, dtype, sqlArgs))
#def whereFromText(self, table, whereTxt, customColumns=None):
# result = []
# sqlArgs = {}
# tblobj = self.db.table(table)
# pattern = '(AND|OR)'
# whereList = re.compile(pattern).split(whereTxt)
# condList = [cond for cond in whereList if cond not in ('AND', 'OR')]
def whereFromDict(self, table, whereDict, customColumns=None):
result = []
sqlArgs = {}
tblobj = self.db.table(table)
for k, v in whereDict.items():
negate = ''
op = 'equal'
ksplit = k.split('_')
if ksplit[-1].lower() in self.opDict:
op = ksplit.pop().lower()
if ksplit[-1].lower() == 'not':
negate = ' NOT '
ksplit.pop()
column = '_'.join(ksplit)
if customColumns and column in customColumns:
custom = customColumns[column]
if callable(custom):
condition = custom(column, sqlArgs)
if isinstance(custom, basestring):
dtype = tblobj.column(custom).dtype
column = custom
elif isinstance(custom, tuple):
column, dtype = custom
else:
raise
else:
colobj = tblobj.column('$%s' % column)
if colobj is None:
raise
dtype = colobj.dtype
condition = self.prepareCondition(column, op, v, dtype, sqlArgs,tblobj=tblobj)
result.append('%s%s' % (negate, condition))
return result, sqlArgs
class SiteRegister(BaseRemoteObject):
def __init__(self,server,sitename=None,storage_path=None):
self.server = server
self.page_register = PageRegister(self)
self.connection_register = ConnectionRegister(self)
self.user_register = UserRegister(self)
self.remotebag_handler = RemoteStoreBagHandler(self)
self.server.daemon.register(self.remotebag_handler,'RemoteData')
self.last_cleanup = time.time()
self.sitename = sitename
self.storage_path = storage_path
self.catalog = GnrClassCatalog()
self.maintenance = False
self.allowed_users = None
def setConfiguration(self,cleanup=None):
cleanup = cleanup or dict()
self.cleanup_interval = int(cleanup.get('interval') or 120)
self.page_max_age = int(cleanup.get('page_max_age') or 120)
self.connection_max_age = int(cleanup.get('connection_max_age')or 600)
def new_connection(self,connection_id,connection_name=None,user=None,user_id=None,
user_name=None,user_tags=None,user_ip=None,user_agent=None,browser_name=None,avatar_extra=None):
assert not self.connection_register.exists(connection_id), 'SITEREGISTER ERROR: connection_id %s already registered' % connection_id
if not self.user_register.exists(user):
self.new_user( user, user_id=user_id,user_name=user_name,user_tags=user_tags,avatar_extra=avatar_extra)
connection_item = self.connection_register.create(connection_id, connection_name=connection_name,user=user,user_id=user_id,
user_name=user_name,user_tags=user_tags,user_ip=user_ip,user_agent=user_agent,browser_name=browser_name)
return connection_item
def drop_pages(self,connection_id):
for page_id in self.connection_page_keys(connection_id):
self.drop_page(page_id)
def drop_page(self,page_id, cascade=None):
return self.page_register.drop(page_id,cascade=cascade)
def drop_connections(self,user):
for connection_id in self.user_connection_keys(user):
self.drop_connection(connection_id)
def drop_connection(self,connection_id,cascade=None):
self.connection_register.drop(connection_id,cascade=cascade)
def drop_user(self,user):
self.user_register.drop(user)
def user_connection_keys(self,user):
return self.connection_register.user_connection_keys(user)
def user_connection_items(self,user):
return self.connection_register.user_connection_items(user)
def user_connections(self,user):
return self.connection_register.user_connections(user)
def connection_page_keys(self,connection_id):
return self.page_register.connection_page_keys(connection_id=connection_id)
def connection_page_items(self,connection_id):
return self.page_register.connection_page_items(connection_id=connection_id)
def connection_pages(self,connection_id):
return self.page_register.connection_pages(connection_id=connection_id)
def new_page(self,page_id,pagename=None,connection_id=None,subscribed_tables=None,user=None,user_ip=None,user_agent=None ,data=None):
page_item = self.page_register.create(page_id, pagename = pagename,connection_id=connection_id,user=user,
user_ip=user_ip,user_agent=user_agent, data=data)
return page_item
def new_user(self,user=None, user_tags=None, user_id=None, user_name=None,
avatar_extra=None):
user_item = self.user_register.create( user=user, user_tags=user_tags, user_id=user_id, user_name=user_name,
avatar_extra=avatar_extra)
return user_item
def subscribed_table_pages(self,table=None):
return self.page_register.subscribed_table_pages(table)
def pages(self, connection_id=None,user=None,index_name=None, filters=None,include_data=None):
if index_name:
print 'call subscribed_table_pages instead of pages'
return self.subscribed_table_pages(index_name)
return self.page_register.pages(connection_id=connection_id,user=user,filters=filters,include_data=include_data)
def page(self,page_id):
return self.page_register.get_item(page_id)
def connection(self,connection_id):
return self.connection_register.get_item(connection_id)
def user(self,user):
return self.user_register.get_item(user)
def users(self,include_data=None):
return self.user_register.values(include_data)
def connections(self,user=None,include_data=None):
return self.connection_register.connections(user=user,include_data=include_data)
def change_connection_user(self, connection_id, user=None, user_tags=None, user_id=None, user_name=None,
avatar_extra=None):
connection_item = self.connection(connection_id)
olduser = connection_item['user']
newuser_item = self.user(user)
if not newuser_item:
newuser_item = self.new_user( user=user, user_tags=user_tags, user_id=user_id, user_name=user_name,
avatar_extra=avatar_extra)
connection_item['user'] = user
connection_item['user_tags'] = user_tags
connection_item['user_name'] = user_name
connection_item['user_id'] = user_id
connection_item['avatar_extra'] = avatar_extra
for p in self.pages(connection_id=connection_id):
p['user'] = user
if not self.connection_register.connections(olduser):
self.drop_user(olduser)
def refresh(self, page_id, last_user_ts=None,last_rpc_ts=None,pageProfilers=None):
refresh_ts = datetime.now()
page = self.page_register.refresh(page_id,last_user_ts=last_user_ts,last_rpc_ts=last_rpc_ts,refresh_ts=refresh_ts)
if not page:
return
self.page_register.updatePageProfilers(page_id,pageProfilers)
connection = self.connection_register.refresh(page['connection_id'],last_user_ts=last_user_ts,last_rpc_ts=last_rpc_ts,refresh_ts=refresh_ts)
if not connection:
return
return self.user_register.refresh(connection['user'],last_user_ts=last_user_ts,last_rpc_ts=last_rpc_ts,refresh_ts=refresh_ts)
def cleanup(self):
if time.time()-self.last_cleanup < self.cleanup_interval:
return
now = datetime.now()
for page in self.pages():
page_max_age = self.page_max_age if not page['user'].startswith('guest_') else 40
last_refresh_ts = page.get('last_refresh_ts') or page.get('start_ts')
if ((now - last_refresh_ts).seconds > page_max_age):
self.drop_page(page['register_item_id'])
for connection in self.connections():
last_refresh_ts = connection.get('last_refresh_ts') or connection.get('start_ts')
connection_max_age = self.connection_max_age if not connection['user'].startswith('guest_') else 40
if (now - last_refresh_ts).seconds > connection_max_age:
self.drop_connection(connection['register_item_id'],cascade=True)
self.last_cleanup = time.time()
def get_register(self,register_name):
return getattr(self,'%s_register' %register_name)
def setStoreSubscription(self,page_id,storename=None, client_path=None, active=None):
self.page_register.setStoreSubscription(page_id,storename=storename,client_path=client_path,active=active)
def subscribeTable(self,page_id,table,subscribe,subscribeMode=None):
self.page_register.subscribeTable(page_id,table=table,subscribe=subscribe,subscribeMode=subscribeMode)
def subscription_storechanges(self, user, page_id):
external_datachanges = self.page_register.get_datachanges(register_item_id=page_id,reset=True)
page_item_data = self.page_register.get_item_data(page_id)
if not page_item_data:
return external_datachanges
user_subscriptions = page_item_data.getItem('_subscriptions.user')
if not user_subscriptions:
return external_datachanges
store_datachanges = []
datachanges = self.user_register.get_datachanges(user)
user_item_data = self.user_register.get_item_data(user)
storesubscriptions_items = user_subscriptions.items()
global_offsets = user_item_data.getItem('_subscriptions.offsets')
if global_offsets is None:
global_offsets = {}
user_item_data.setItem('_subscriptions.offsets', global_offsets)
for j, change in enumerate(datachanges):
changepath = change.path
change_idx = change.change_idx
for subpath, subdict in storesubscriptions_items:
if subdict['on'] and changepath.startswith(subpath):
if change_idx > subdict.get('offset', 0):
subdict['offset'] = change_idx
change.attributes = change.attributes or {}
if change_idx > global_offsets.get(subpath, 0):
global_offsets[subpath] = change_idx
change.attributes['_new_datachange'] = True
else:
change.attributes.pop('_new_datachange', None)
store_datachanges.append(change)
return external_datachanges+store_datachanges
def handle_ping(self, page_id=None, reason=None, _serverstore_changes=None,**kwargs):
_children_pages_info= kwargs.get('_children_pages_info')
_lastUserEventTs = kwargs.get('_lastUserEventTs')
_lastRpc = kwargs.get('_lastRpc')
_pageProfilers = kwargs.get('_pageProfilers')
page_item = self.refresh(page_id, _lastUserEventTs,last_rpc_ts=_lastRpc,pageProfilers=_pageProfilers)
if not page_item:
return False
catalog = self.catalog
if _serverstore_changes:
self.set_serverstore_changes(page_id, _serverstore_changes)
if _children_pages_info:
for k,v in _children_pages_info.items():
child_lastUserEventTs = v.pop('_lastUserEventTs', None)
child_lastRpc = v.pop('_lastRpc', None)
child_pageProfilers = v.pop('_pageProfilers', None)
if v:
self.set_serverstore_changes(k, v)
if child_lastUserEventTs:
child_lastUserEventTs = catalog.fromTypedText(child_lastUserEventTs)
if child_lastRpc:
child_lastRpc = catalog.fromTypedText(child_lastRpc)
self.refresh(k, child_lastUserEventTs,last_rpc_ts=child_lastRpc,pageProfilers=child_pageProfilers)
envelope = Bag(dict(result=None))
user=page_item['user']
datachanges = self.handle_ping_get_datachanges(page_id, user=user)
if datachanges:
envelope.setItem('dataChanges', datachanges)
if _children_pages_info:
for k in _children_pages_info.keys():
datachanges = self.handle_ping_get_datachanges(k, user=user)
if datachanges:
envelope.setItem('childDataChanges.%s' %k, datachanges)
user_register_data = self.user_register.get_item_data(user)
lastBatchUpdate = user_register_data.getItem('lastBatchUpdate')
if lastBatchUpdate:
if (datetime.now()-lastBatchUpdate).seconds<5:
envelope.setItem('runningBatch',True)
else:
user_register_data.setItem('lastBatchUpdate',None)
return envelope
def handle_ping_get_datachanges(self, page_id, user=None):
result = Bag()
store_datachanges = self.subscription_storechanges(user,page_id)
if store_datachanges:
for j, change in enumerate(store_datachanges):
result.setItem('sc_%i' % j, change.value, change_path=change.path, change_reason=change.reason,
change_fired=change.fired, change_attr=change.attributes,
change_ts=change.change_ts, change_delete=change.delete)
return result
def set_serverstore_changes(self, page_id=None, datachanges=None):
page_item_data = self.page_register.get_item_data(page_id)
for k, v in datachanges.items():
page_item_data.setItem(k, self._parse_change_value(v))
def _parse_change_value(self, change_value):
if isinstance(change_value, basestring):
try:
v = self.catalog.fromTypedText(change_value)
if isinstance(v, basestring):
v = v.decode('utf-8')
return v
except Exception, e:
raise e
return change_value