schema.Column('phoneticTranscription', types.Unicode(255)),
# narrow phonetic transcription -- optional
schema.Column('narrowPhoneticTranscription', types.Unicode(255)),
schema.Column('morphemeBreak', types.Unicode(255)),
schema.Column('morphemeGloss', types.Unicode(255)),
schema.Column('comments', types.UnicodeText()),
schema.Column('speakerComments', types.UnicodeText()),
schema.Column('context',
types.UnicodeText()), # describing context of utterance
# Forced choice textual values
schema.Column('grammaticality', types.Unicode(255)),
# Temporal values: only dateElicited is consciously enterable by the user
schema.Column('dateElicited', types.Date()),
schema.Column('datetimeEntered', types.DateTime()),
schema.Column('datetimeModified', types.DateTime(), default=now),
# syntacticCategoryString: OLD-generated value
schema.Column('syntacticCategoryString', types.Unicode(255)),
# morphemeBreakIDs and morphemeGlossIDs: OLD-generated values
schema.Column('morphemeBreakIDs', types.Unicode(1023)),
schema.Column('morphemeGlossIDs', types.Unicode(1023)),
# breakGlossCategory: OLD-generated value, e.g., 'chien|dog|N-s|PL|NUM'
schema.Column('breakGlossCategory', types.Unicode(1023)),
# A Form can have only one each of elicitor, enterer and verifier, but each
# of these can have more than one form
# Form-User = Many-to-One
def reflecttable(self, connection, table):
#TODO: map these better
column_func = {
14 : lambda r: sqltypes.String(r['FLEN']), # TEXT
7 : lambda r: sqltypes.Integer(), # SHORT
8 : lambda r: sqltypes.Integer(), # LONG
9 : lambda r: sqltypes.Float(), # QUAD
10 : lambda r: sqltypes.Float(), # FLOAT
27 : lambda r: sqltypes.Float(), # DOUBLE
35 : lambda r: sqltypes.DateTime(), # TIMESTAMP
37 : lambda r: sqltypes.String(r['FLEN']), # VARYING
261: lambda r: sqltypes.TEXT(), # BLOB
40 : lambda r: sqltypes.Char(r['FLEN']), # CSTRING
12 : lambda r: sqltypes.Date(), # DATE
13 : lambda r: sqltypes.Time(), # TIME
16 : lambda r: sqltypes.Numeric(precision=r['FPREC'], length=r['FSCALE'] * -1) #INT64
}
tblqry = """
SELECT DISTINCT R.RDB$FIELD_NAME AS FNAME,
R.RDB$NULL_FLAG AS NULL_FLAG,
R.RDB$FIELD_POSITION,
F.RDB$FIELD_TYPE AS FTYPE,
F.RDB$FIELD_SUB_TYPE AS STYPE,
F.RDB$FIELD_LENGTH AS FLEN,
F.RDB$FIELD_PRECISION AS FPREC,
F.RDB$FIELD_SCALE AS FSCALE
FROM RDB$RELATION_FIELDS R
JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
WHERE F.RDB$SYSTEM_FLAG=0 and R.RDB$RELATION_NAME=?
ORDER BY R.RDB$FIELD_POSITION"""
keyqry = """
SELECT SE.RDB$FIELD_NAME SENAME
FROM RDB$RELATION_CONSTRAINTS RC
JOIN RDB$INDEX_SEGMENTS SE
ON RC.RDB$INDEX_NAME=SE.RDB$INDEX_NAME
WHERE RC.RDB$CONSTRAINT_TYPE=? AND RC.RDB$RELATION_NAME=?"""
fkqry = """
SELECT RC.RDB$CONSTRAINT_NAME CNAME,
CSE.RDB$FIELD_NAME FNAME,
IX2.RDB$RELATION_NAME RNAME,
SE.RDB$FIELD_NAME SENAME
FROM RDB$RELATION_CONSTRAINTS RC
JOIN RDB$INDICES IX1
ON IX1.RDB$INDEX_NAME=RC.RDB$INDEX_NAME
JOIN RDB$INDICES IX2
ON IX2.RDB$INDEX_NAME=IX1.RDB$FOREIGN_KEY
JOIN RDB$INDEX_SEGMENTS CSE
ON CSE.RDB$INDEX_NAME=IX1.RDB$INDEX_NAME
JOIN RDB$INDEX_SEGMENTS SE
ON SE.RDB$INDEX_NAME=IX2.RDB$INDEX_NAME AND SE.RDB$FIELD_POSITION=CSE.RDB$FIELD_POSITION
WHERE RC.RDB$CONSTRAINT_TYPE=? AND RC.RDB$RELATION_NAME=?
ORDER BY SE.RDB$INDEX_NAME, SE.RDB$FIELD_POSITION"""
# get primary key fields
c = connection.execute(keyqry, ["PRIMARY KEY", table.name.upper()])
pkfields =[r['SENAME'] for r in c.fetchall()]
# get all of the fields for this table
def lower_if_possible(name):
# Remove trailing spaces: FB uses a CHAR() type,
# that is padded with spaces
name = name.rstrip()
# If its composed only by upper case chars, use
# the lowered version, otherwise keep the original
# (even if stripped...)
lname = name.lower()
if lname.upper() == name and not ' ' in name:
return lname
return name
c = connection.execute(tblqry, [table.name.upper()])
row = c.fetchone()
if not row:
raise exceptions.NoSuchTableError(table.name)
while row:
name = row['FNAME']
args = [lower_if_possible(name)]
kw = {}
# get the data types and lengths
args.append(column_func[row['FTYPE']](row))
# is it a primary key?
kw['primary_key'] = name in pkfields
table.append_column(schema.Column(*args, **kw))
row = c.fetchone()
# get the foreign keys
c = connection.execute(fkqry, ["FOREIGN KEY", table.name.upper()])
fks = {}
while True:
row = c.fetchone()
if not row: break
cname = lower_if_possible(row['CNAME'])
try:
fk = fks[cname]
except KeyError:
fks[cname] = fk = ([], [])
rname = lower_if_possible(row['RNAME'])
schema.Table(rname, table.metadata, autoload=True, autoload_with=connection)
fname = lower_if_possible(row['FNAME'])
refspec = rname + '.' + lower_if_possible(row['SENAME'])
fk[0].append(fname)
fk[1].append(refspec)
for name,value in fks.iteritems():
table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name))
def test_encode_datetime():
now = datetime.datetime.now()
e = encode(types.DateTime())
d = decode(types.DateTime())
# microseconds are lost, but that's ok
assert now.timetuple()[:6] == d(e(now)).timetuple()[:6]
class SurveySession(BaseObject):
"""Information about a user's session."""
__tablename__ = "session"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
brand = schema.Column(types.String(64))
account_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
last_modifier_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=True,
index=False,
)
last_publisher_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=True,
index=False,
)
group_id = schema.Column(
types.Unicode(32),
schema.ForeignKey("group.group_id"),
)
title = schema.Column(types.Unicode(512))
created = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
modified = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
refreshed = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
published = schema.Column(
types.DateTime,
nullable=True,
default=None,
)
archived = schema.Column(
types.DateTime(timezone=True),
nullable=True,
default=None,
)
completion_percentage = schema.Column(
types.Integer,
nullable=True,
default=0,
)
zodb_path = schema.Column(types.String(512), nullable=False)
report_comment = schema.Column(types.UnicodeText())
account = orm.relation(
Account,
backref=orm.backref(
"sessions",
order_by=modified,
cascade="all, delete, delete-orphan",
),
foreign_keys=[account_id],
)
last_modifier = orm.relation(
Account,
foreign_keys=[last_modifier_id],
)
last_publisher = orm.relation(
Account,
foreign_keys=[last_publisher_id],
)
group = orm.relation(
Group,
backref=orm.backref("sessions",
order_by=modified,
cascade="all, delete, delete-orphan"),
)
migrated = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
# Allow this class to be subclassed in other projects
__mapper_args__ = {
"polymorphic_identity": "euphorie",
"polymorphic_on": brand,
"with_polymorphic": "*",
}
@property
def is_archived(self):
archived = self.archived
if not archived:
return False
return archived <= localized_now()
@property
def review_state(self):
"""Check if it the published column.
If it has return 'published' otherwise return 'private'
"""
return "published" if self.published else "private"
def hasTree(self):
return bool(
Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).count())
def reset(self):
Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).delete()
self.created = self.modified = datetime.datetime.now()
def touch(self):
self.last_modifier = get_current_account()
self.modified = datetime.datetime.now()
def refresh_survey(self, survey=None):
"""Mark the session with the current date to indicate that is has been
refreshed with the latest version of the Survey (from Zope).
If survey is passed, update all titles in the tree, based on the CMS
version of the survey, i.e. update all titles of modules and risks.
Those are used in the navigation. If a title change is the only change
in the CMS, the survey session is not re-created. Therefore this
method ensures that the titles are updated where necessary.
"""
if survey:
query = Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session_id == self.id)
tree = query.all()
for item in tree:
if item.zodb_path.find("custom-risks") >= 0:
continue
zodb_item = survey.restrictedTraverse(
item.zodb_path.split("/"), None)
if zodb_item and zodb_item.title != item.title:
item.title = zodb_item.title
self.refreshed = datetime.datetime.now()
def addChild(self, item):
sqlsession = Session()
query = (sqlsession.query(SurveyTreeItem.path).filter(
SurveyTreeItem.session_id == self.id).filter(
SurveyTreeItem.depth == 1).order_by(
SurveyTreeItem.path.desc()))
last = query.first()
if not last:
index = 1
else:
index = int(last[0][-3:]) + 1
item.session = self
item.depth = 1
item.path = "%03d" % index
item.parent_id = None
sqlsession.add(item)
self.touch()
return item
def children(self, filter=None):
query = (Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session_id == self.id).filter(
SurveyTreeItem.depth == 1))
if filter is not None:
query = query.filter(filter)
return query.order_by(SurveyTreeItem.path)
def copySessionData(self, other):
"""Copy all user data from another session to this one."""
session = Session()
# Copy all tree data to the new session (skip_children and postponed)
old_tree = orm.aliased(SurveyTreeItem, name="old_tree")
in_old_tree = sql.and_(
old_tree.session_id == other.id,
SurveyTreeItem.zodb_path == old_tree.zodb_path,
SurveyTreeItem.profile_index == old_tree.profile_index,
)
skip_children = sql.select([old_tree.skip_children],
in_old_tree).limit(1)
postponed = sql.select([old_tree.postponed], in_old_tree).limit(1)
new_items = (session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).filter(
sql.exists(sql.select([old_tree.id]).where(in_old_tree))))
new_items.update(
{
"skip_children": skip_children,
"postponed": postponed
},
synchronize_session=False,
)
# Mandatory modules must have skip_children=False. It's possible that
# the module was optional with skip_children=True and now after the
# update it's mandatory. So we must check and correct.
# In case a risk was marked as "always present", be sure its
# identification gets set to 'no'
preset_to_no = []
survey = getSite()["client"].restrictedTraverse(self.zodb_path)
for item in new_items.all():
if item.type == "risk":
if item.identification == "no":
preset_to_no.append(item.risk_id)
elif item.type == "module":
module = survey.restrictedTraverse(item.zodb_path.split("/"))
if not module.optional:
item.skip_children = False
# Copy all risk data to the new session
# This triggers a "Only update via a single table query is currently
# supported" error with SQLAlchemy 0.6.6
# old_risk = orm.aliased(Risk.__table__, name='old_risk')
# is_old_risk = sql.and_(in_old_tree, old_tree.id == old_risk.id)
# identification = sql.select([old_risk.identification], is_old_risk)
# new_risks = session.query(Risk)\
# .filter(Risk.session == self)\
# .filter(sql.exists(
# sql.select([SurveyTreeItem.id]).where(sql.and_(
# SurveyTreeItem.id == Risk.id,
# sql.exists([old_tree.id]).where(sql.and_(
# in_old_tree, old_tree.type == 'risk'))))))
# new_risks.update({'identification': identification},
# synchronize_session=False)
skip_preset_to_no_clause = ""
if len(preset_to_no):
skip_preset_to_no_clause = "old_risk.risk_id not in %s AND" % (str(
[str(x)
for x in preset_to_no]).replace("[", "(").replace("]", ")"))
statement = """\
UPDATE RISK
SET identification = old_risk.identification,
frequency = old_risk.frequency,
effect = old_risk.effect,
probability = old_risk.probability,
priority = old_risk.priority,
existing_measures = old_risk.existing_measures,
comment = old_risk.comment
FROM risk AS old_risk JOIN tree AS old_tree ON old_tree.id=old_risk.id, tree
WHERE tree.id=risk.id AND
%(skip_preset_to_no_clause)s
tree.session_id=%(new_sessionid)s AND
old_tree.session_id=%(old_sessionid)s AND
old_tree.zodb_path=tree.zodb_path AND
old_tree.profile_index=tree.profile_index;
""" % dict( # noqa: E501
old_sessionid=other.id,
new_sessionid=self.id,
skip_preset_to_no_clause=skip_preset_to_no_clause,
)
session.execute(statement)
statement = """\
INSERT INTO action_plan (risk_id, action_plan, prevention_plan, action,
requirements, responsible, budget, plan_type,
planning_start, planning_end,
solution_id, used_in_training)
SELECT new_tree.id,
action_plan.action_plan,
action_plan.prevention_plan,
action_plan.action,
action_plan.requirements,
action_plan.responsible,
action_plan.budget,
action_plan.plan_type,
action_plan.planning_start,
action_plan.planning_end,
action_plan.solution_id,
action_plan.used_in_training
FROM action_plan JOIN risk ON action_plan.risk_id=risk.id
JOIN tree ON tree.id=risk.id,
tree AS new_tree
WHERE tree.session_id=%(old_sessionid)d AND
new_tree.session_id=%(new_sessionid)d AND
tree.zodb_path=new_tree.zodb_path AND
tree.profile_index=new_tree.profile_index;
""" % {
"old_sessionid": other.id,
"new_sessionid": self.id,
}
session.execute(statement)
# Copy over previous session metadata. Specifically, we don't want to
# create a new modification timestamp, just because the underlying
# survey was updated.
statement = """\
UPDATE session
SET
modified = old_session.modified,
created = old_session.created,
last_modifier_id = old_session.last_modifier_id
FROM session as old_session
WHERE
old_session.id=%(old_sessionid)d AND
session.id=%(new_sessionid)d
""" % {
"old_sessionid": other.id,
"new_sessionid": self.id,
}
session.execute(statement)
session.query(Company).filter(Company.session == other).update(
{"session_id": self.id}, synchronize_session=False)
@classmethod
def get_account_filter(cls, account=None):
"""Filter only the sessions for the given account
:param acount: True means current account.
A falsish value means do not filter.
Otherwise try to interpret the user input:
a string or an int means the account_id should be that value,
an object account will be used to extract the account id,
from an iterable we will try to extract the account ids
"""
if account is True:
account = get_current_account()
if isinstance(account, Account):
account = account.id
if not account:
return False
if isinstance(account, (int, six.string_types)):
return cls.account_id == account
try:
account_ids = {getattr(item, "id", item) for item in account}
except TypeError:
log.error("Cannot understand the account parameter: %r", account)
raise
account_ids = {
item
for item in account_ids
if item and isinstance(item, (int, six.string_types))
}
if not account_ids:
return False
if len(account_ids) == 1:
for account_id in account_ids:
return cls.get_account_filter(account_id)
return cls.account_id.in_(account_ids)
@classmethod
def get_group_filter(cls, group=None):
"""Filter only the sessions for the given group
:param group: True means the current account's group.
A falsish value means do not filter.
Otherwise try to interpret the user input:
a string or an int means the group_id should be that value,
an object group will be used to extract the group id,
and from an iterable we will try to extract the group ids
"""
if group is True:
group = getattr(get_current_account(), "group_id", None)
if isinstance(group, Group):
group = group.group_id
if not group:
return False
if isinstance(group, (int, six.string_types)):
return cls.group_id == group
try:
group_ids = {getattr(item, "group_id", item) for item in group}
except TypeError:
log.error("Cannot understand the group parameter: %r", group)
raise
group_ids = {
item
for item in group_ids
if item and isinstance(item, (int, six.string_types))
}
if not group_ids:
return False
if len(group_ids) == 1:
for group_id in group_ids:
return cls.get_group_filter(group_id)
return cls.group_id.in_(group_ids)
@classmethod
def get_archived_filter(cls):
"""Filter sessions that are archived"""
return sql.or_(
cls.archived >= localized_now(),
cls.archived == None # noqa: E711
)
@classmethod
def _get_context_tools(cls, context):
"""Return the set of tools we can find under this context"""
if not context:
return set()
# Check the path relative to the client folder
if context.portal_type == "Plone Site":
context = context.client
if context.portal_type == "euphorie.survey":
return {context}
portal_type_filter = {
"portal_type": [
"euphorie.clientcountry",
"euphorie.clientsector",
"euphorie.survey",
]
}
surveys = set()
def _add_survey(container):
for obj in container.listFolderContents(portal_type_filter):
if obj.portal_type == "euphorie.survey":
surveys.add(obj)
else:
_add_survey(obj)
_add_survey(context)
return surveys
@classmethod
def get_context_filter(cls, context):
"""Filter sessions under this context using the zodb_path column"""
surveys = cls._get_context_tools(context)
if not surveys:
return False
return cls.zodb_path.in_({
safe_unicode("/".join(survey.getPhysicalPath()[-3:]))
for survey in surveys
})
@property
def tool(self):
client = api.portal.get().client
return client.restrictedTraverse(str(self.zodb_path), None)
@property
def traversed_session(self):
return self.tool.restrictedTraverse("++session++%s" % self.id)
@property
def country(self):
return str(self.zodb_path).split("/")[0]
def test_setup_epoch(self):
column_info = {"type": types.DateTime()}
bulkdata.setup_epoch(mock.Mock(), mock.Mock(), column_info)
self.assertIsInstance(column_info["type"], bulkdata.EpochType)
class Project(Base):
__tablename__ = "projects"
#__mapper_args__ = dict(order_by="date desc")
id = Column(types.Integer, primary_key=True)
#leader_id = Column(types.Integer, ForeignKey('user.id'))
# If initial_round_size > 0, this will point to
# the entry in the citations_tasks table that
# maps to the initial assignment associated with
# this project.
initial_assignment_id = Column(types.Integer)
name = Column(types.Unicode(255))
description = Column(types.Unicode(10000))
# This is used to identify the project when
# the leader invites others to the project
code = Column(types.Unicode(10), index=True, unique=True)
# `single', `double', or `advanced'
screening_mode = Column(types.Unicode(50))
# True := tags are private
# False := tags are public
tag_privacy = Column(types.Boolean)
# the number of labels to be procured for each abstract
num_labels_thus_far = Column(types.Integer)
# basically, the AI criteria (or random)
sort_by = Column(types.Unicode(255))
# If >0, this represents a fixed set of
# citations that will (potentially) be
# screened by everyone on the project
initial_round_size = Column(types.Integer)
# Minimum and maximum number of citations a user should screen for this project
min_citations = Column(types.Integer)
max_citations = Column(types.Integer)
# Bookkeeping
date_created = Column(types.DateTime())
date_modified = Column(types.DateTime())
priorities = relationship('Priority',
order_by='Priority.id',
backref='project')
citations = relationship('Citation',
order_by='Citation.id',
backref='project')
assignments = relationship('Assignment',
order_by='Assignment.id',
backref='project')
labels = relationship('Label', order_by='Label.id', backref='project')
members = relationship('User',
secondary=users_projects_table,
backref='member_of_projects')
leaders = relationship('User',
secondary=projects_leaders_table,
backref='leader_of_projects')
class BQDialect(default.DefaultDialect):
colspecs = {
types.Unicode: BQString,
types.Integer: BQInteger,
types.SmallInteger: BQInteger,
types.Numeric: BQFloat,
types.Float: BQFloat,
types.DateTime: BQTimestamp,
types.Date: BQTimestamp,
types.String: BQString,
types.LargeBinary: BQBytes,
types.Boolean: BQBoolean,
types.Text: BQString,
types.CHAR: BQString,
types.TIMESTAMP: BQTimestamp,
types.VARCHAR: BQString
}
__TYPE_MAPPINGS = {
'TIMESTAMP': types.DateTime(),
'STRING': types.String(),
'FLOAT': types.Float(),
'INTEGER': types.Integer(),
'BOOLEAN': types.Boolean()
}
name = 'bigquery'
driver = 'bq1'
poolclass = pool.SingletonThreadPool
statement_compiler = BQSQLCompiler
ddl_compiler = BQDDLCompiler
preparer = BQIdentifierPreparer
execution_ctx_cls = BQExecutionContext
supports_alter = False
supports_unicode_statements = True
supports_sane_multi_rowcount = False
supports_sane_rowcount = False
supports_sequences = False
supports_native_enum = False
positional = False
paramstyle = 'named'
default_sequence_base = 0
default_schema_name = None
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def __init__(self, **kw):
#
# Create a dialect object
#
super(BQDialect, self).__init__(**kw)
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def create_connect_args(self, url):
#
# This function recovers connection parameters from the connection string
#
return [], {}
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def initialize(self, connection):
"""disable all dialect initialization"""
# - - - - - - - - - - - - - - - - - - - - - - - - - -
@classmethod
def dbapi(cls):
return dbapi
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def do_execute(self, cursor, statement, parameters, context=None):
cursor.execute(statement, parameters)
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def do_executemany(self, cursor, statement, parameters, context=None):
cursor.executemany(statement, parameters)
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_schema_names(self, engine, **kw):
return engine.connect().connection.get_schema_names()
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_view_names(self, connection, schema=None, **kw):
raise NotImplementedError()
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_view_definition(self, connection, viewname, schema=None, **kw):
raise NotImplementedError()
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def has_table(self, connection, table_name, schema=None):
return table_name in connection.connection.get_table_names()
# - - - - - - - - - - - - - - - - - - - - - - - - - -
@reflection.cache
def get_table_names(self, engine, schema=None, **kw):
return engine.connect().connection.get_table_names()
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_columns(self, engine, table_name, schema=None, **kw):
cols = engine.connect().connection.get_columns(table_name)
get_coldef = lambda x, y: {
"name": x,
"type": BQDialect.__TYPE_MAPPINGS.get(y, types.Binary()),
"nullable": True,
"default": None
}
return [get_coldef(*col) for col in cols]
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_primary_keys(self, engine, table_name, schema=None, **kw):
return []
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_foreign_keys(self, engine, table_name, schema=None, **kw):
return []
# - - - - - - - - - - - - - - - - - - - - - - - - - -
def get_indexes(self, connection, table_name, schema=None, **kw):
return []
def reflecttable(self, connection, table, include_columns):
#TODO: map these better
column_func = {
14 : lambda r: sqltypes.String(r['FLEN']), # TEXT
7 : lambda r: sqltypes.Integer(), # SHORT
8 : lambda r: r['FPREC']==0 and sqltypes.Integer() or sqltypes.Numeric(precision=r['FPREC'], length=r['FSCALE'] * -1), #INT or NUMERIC
9 : lambda r: sqltypes.Float(), # QUAD
10 : lambda r: sqltypes.Float(), # FLOAT
27 : lambda r: sqltypes.Float(), # DOUBLE
35 : lambda r: sqltypes.DateTime(), # TIMESTAMP
37 : lambda r: sqltypes.String(r['FLEN']), # VARYING
261: lambda r: sqltypes.TEXT(), # BLOB
40 : lambda r: sqltypes.Char(r['FLEN']), # CSTRING
12 : lambda r: sqltypes.Date(), # DATE
13 : lambda r: sqltypes.Time(), # TIME
16 : lambda r: sqltypes.Numeric(precision=r['FPREC'], length=r['FSCALE'] * -1) #INT64
}
tblqry = """
SELECT DISTINCT R.RDB$FIELD_NAME AS FNAME,
R.RDB$NULL_FLAG AS NULL_FLAG,
R.RDB$FIELD_POSITION,
F.RDB$FIELD_TYPE AS FTYPE,
F.RDB$FIELD_SUB_TYPE AS STYPE,
F.RDB$FIELD_LENGTH AS FLEN,
F.RDB$FIELD_PRECISION AS FPREC,
F.RDB$FIELD_SCALE AS FSCALE
FROM RDB$RELATION_FIELDS R
JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
WHERE F.RDB$SYSTEM_FLAG=0 and R.RDB$RELATION_NAME=?
ORDER BY R.RDB$FIELD_POSITION"""
keyqry = """
SELECT SE.RDB$FIELD_NAME SENAME
FROM RDB$RELATION_CONSTRAINTS RC
JOIN RDB$INDEX_SEGMENTS SE
ON RC.RDB$INDEX_NAME=SE.RDB$INDEX_NAME
WHERE RC.RDB$CONSTRAINT_TYPE=? AND RC.RDB$RELATION_NAME=?"""
fkqry = """
SELECT RC.RDB$CONSTRAINT_NAME CNAME,
CSE.RDB$FIELD_NAME FNAME,
IX2.RDB$RELATION_NAME RNAME,
SE.RDB$FIELD_NAME SENAME
FROM RDB$RELATION_CONSTRAINTS RC
JOIN RDB$INDICES IX1
ON IX1.RDB$INDEX_NAME=RC.RDB$INDEX_NAME
JOIN RDB$INDICES IX2
ON IX2.RDB$INDEX_NAME=IX1.RDB$FOREIGN_KEY
JOIN RDB$INDEX_SEGMENTS CSE
ON CSE.RDB$INDEX_NAME=IX1.RDB$INDEX_NAME
JOIN RDB$INDEX_SEGMENTS SE
ON SE.RDB$INDEX_NAME=IX2.RDB$INDEX_NAME AND SE.RDB$FIELD_POSITION=CSE.RDB$FIELD_POSITION
WHERE RC.RDB$CONSTRAINT_TYPE=? AND RC.RDB$RELATION_NAME=?
ORDER BY SE.RDB$INDEX_NAME, SE.RDB$FIELD_POSITION"""
# get primary key fields
c = connection.execute(keyqry, ["PRIMARY KEY", self._denormalize_name(table.name)])
pkfields =[self._normalize_name(r['SENAME']) for r in c.fetchall()]
# get all of the fields for this table
c = connection.execute(tblqry, [self._denormalize_name(table.name)])
found_table = False
while True:
row = c.fetchone()
if row is None:
break
found_table = True
name = self._normalize_name(row['FNAME'])
if include_columns and name not in include_columns:
continue
args = [name]
kw = {}
# get the data types and lengths
coltype = column_func.get(row['FTYPE'], None)
if coltype is None:
warnings.warn(RuntimeWarning("Did not recognize type '%s' of column '%s'" % (str(row['FTYPE']), name)))
coltype = sqltypes.NULLTYPE
else:
coltype = coltype(row)
args.append(coltype)
# is it a primary key?
kw['primary_key'] = name in pkfields
# is it nullable ?
kw['nullable'] = not bool(row['NULL_FLAG'])
table.append_column(schema.Column(*args, **kw))
if not found_table:
raise exceptions.NoSuchTableError(table.name)
# get the foreign keys
c = connection.execute(fkqry, ["FOREIGN KEY", self._denormalize_name(table.name)])
fks = {}
while True:
row = c.fetchone()
if not row: break
cname = self._normalize_name(row['CNAME'])
try:
fk = fks[cname]
except KeyError:
fks[cname] = fk = ([], [])
rname = self._normalize_name(row['RNAME'])
schema.Table(rname, table.metadata, autoload=True, autoload_with=connection)
fname = self._normalize_name(row['FNAME'])
refspec = rname + '.' + self._normalize_name(row['SENAME'])
fk[0].append(fname)
fk[1].append(refspec)
for name,value in fks.iteritems():
table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name))
class BaseEngineSpec: # pylint: disable=too-many-public-methods
"""Abstract class for database engine specific configurations"""
engine = "base" # str as defined in sqlalchemy.engine.engine
engine_aliases: Optional[Tuple[str]] = None
engine_name: Optional[
str
] = None # used for user messages, overridden in child classes
_date_trunc_functions: Dict[str, str] = {}
_time_grain_expressions: Dict[Optional[str], str] = {}
column_type_mappings: Tuple[
Tuple[
Pattern[str],
Union[TypeEngine, Callable[[Match[str]], TypeEngine]],
GenericDataType,
],
...,
] = (
(
re.compile(r"^smallint", re.IGNORECASE),
types.SmallInteger(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^int.*", re.IGNORECASE),
types.Integer(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^bigint", re.IGNORECASE),
types.BigInteger(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^decimal", re.IGNORECASE),
types.Numeric(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^numeric", re.IGNORECASE),
types.Numeric(),
GenericDataType.NUMERIC,
),
(re.compile(r"^real", re.IGNORECASE), types.REAL, GenericDataType.NUMERIC,),
(
re.compile(r"^smallserial", re.IGNORECASE),
types.SmallInteger(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^serial", re.IGNORECASE),
types.Integer(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^bigserial", re.IGNORECASE),
types.BigInteger(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^string", re.IGNORECASE),
types.String(),
utils.GenericDataType.STRING,
),
(
re.compile(r"^N((VAR)?CHAR|TEXT)", re.IGNORECASE),
UnicodeText(),
utils.GenericDataType.STRING,
),
(
re.compile(r"^((VAR)?CHAR|TEXT|STRING)", re.IGNORECASE),
String(),
utils.GenericDataType.STRING,
),
(
re.compile(r"^datetime", re.IGNORECASE),
types.DateTime(),
GenericDataType.TEMPORAL,
),
(re.compile(r"^date", re.IGNORECASE), types.Date(), GenericDataType.TEMPORAL,),
(
re.compile(r"^timestamp", re.IGNORECASE),
types.TIMESTAMP(),
GenericDataType.TEMPORAL,
),
(
re.compile(r"^interval", re.IGNORECASE),
types.Interval(),
GenericDataType.TEMPORAL,
),
(re.compile(r"^time", re.IGNORECASE), types.Time(), GenericDataType.TEMPORAL,),
(
re.compile(r"^bool.*", re.IGNORECASE),
types.Boolean(),
GenericDataType.BOOLEAN,
),
)
time_groupby_inline = False
limit_method = LimitMethod.FORCE_LIMIT
time_secondary_columns = False
allows_joins = True
allows_subqueries = True
allows_alias_in_select = True
allows_alias_in_orderby = True
allows_sql_comments = True
force_column_alias_quotes = False
arraysize = 0
max_column_name_length = 0
try_remove_schema_from_table_name = True # pylint: disable=invalid-name
run_multiple_statements_as_one = False
@classmethod
def get_dbapi_exception_mapping(cls) -> Dict[Type[Exception], Type[Exception]]:
"""
Each engine can implement and converge its own specific exceptions into
Superset DBAPI exceptions
Note: On python 3.9 this method can be changed to a classmethod property
without the need of implementing a metaclass type
:return: A map of driver specific exception to superset custom exceptions
"""
return {}
@classmethod
def get_dbapi_mapped_exception(cls, exception: Exception) -> Exception:
"""
Get a superset custom DBAPI exception from the driver specific exception.
Override if the engine needs to perform extra changes to the exception, for
example change the exception message or implement custom more complex logic
:param exception: The driver specific exception
:return: Superset custom DBAPI exception
"""
new_exception = cls.get_dbapi_exception_mapping().get(type(exception))
if not new_exception:
return exception
return new_exception(str(exception))
@classmethod
def get_allow_cost_estimate(cls, extra: Dict[str, Any]) -> bool:
return False
@classmethod
def get_engine(
cls,
database: "Database",
schema: Optional[str] = None,
source: Optional[str] = None,
) -> Engine:
user_name = utils.get_username()
return database.get_sqla_engine(
schema=schema, nullpool=True, user_name=user_name, source=source
)
@classmethod
def get_timestamp_expr(
cls,
col: ColumnClause,
pdf: Optional[str],
time_grain: Optional[str],
type_: Optional[str] = None,
) -> TimestampExpression:
"""
Construct a TimestampExpression to be used in a SQLAlchemy query.
:param col: Target column for the TimestampExpression
:param pdf: date format (seconds or milliseconds)
:param time_grain: time grain, e.g. P1Y for 1 year
:param type_: the source column type
:return: TimestampExpression object
"""
if time_grain:
time_expr = cls.get_time_grain_expressions().get(time_grain)
if not time_expr:
raise NotImplementedError(
f"No grain spec for {time_grain} for database {cls.engine}"
)
if type_ and "{func}" in time_expr:
date_trunc_function = cls._date_trunc_functions.get(type_)
if date_trunc_function:
time_expr = time_expr.replace("{func}", date_trunc_function)
if type_ and "{type}" in time_expr:
date_trunc_function = cls._date_trunc_functions.get(type_)
if date_trunc_function:
time_expr = time_expr.replace("{type}", type_)
else:
time_expr = "{col}"
# if epoch, translate to DATE using db specific conf
if pdf == "epoch_s":
time_expr = time_expr.replace("{col}", cls.epoch_to_dttm())
elif pdf == "epoch_ms":
time_expr = time_expr.replace("{col}", cls.epoch_ms_to_dttm())
return TimestampExpression(time_expr, col, type_=DateTime)
@classmethod
def get_time_grains(cls) -> Tuple[TimeGrain, ...]:
"""
Generate a tuple of supported time grains.
:return: All time grains supported by the engine
"""
ret_list = []
time_grains = builtin_time_grains.copy()
time_grains.update(config["TIME_GRAIN_ADDONS"])
for duration, func in cls.get_time_grain_expressions().items():
if duration in time_grains:
name = time_grains[duration]
ret_list.append(TimeGrain(name, _(name), func, duration))
return tuple(ret_list)
@classmethod
def _sort_time_grains(
cls, val: Tuple[Optional[str], str], index: int
) -> Union[float, int, str]:
"""
Return an ordered time-based value of a portion of a time grain
for sorting
Values are expected to be either None or start with P or PT
Have a numerical value in the middle and end with
a value for the time interval
It can also start or end with epoch start time denoting a range
i.e, week beginning or ending with a day
"""
pos = {
"FIRST": 0,
"SECOND": 1,
"THIRD": 2,
"LAST": 3,
}
if val[0] is None:
return pos["FIRST"]
prog = re.compile(r"(.*\/)?(P|PT)([0-9\.]+)(S|M|H|D|W|M|Y)(\/.*)?")
result = prog.match(val[0])
# for any time grains that don't match the format, put them at the end
if result is None:
return pos["LAST"]
second_minute_hour = ["S", "M", "H"]
day_week_month_year = ["D", "W", "M", "Y"]
is_less_than_day = result.group(2) == "PT"
interval = result.group(4)
epoch_time_start_string = result.group(1) or result.group(5)
has_starting_or_ending = bool(len(epoch_time_start_string or ""))
def sort_day_week() -> int:
if has_starting_or_ending:
return pos["LAST"]
if is_less_than_day:
return pos["SECOND"]
return pos["THIRD"]
def sort_interval() -> float:
if is_less_than_day:
return second_minute_hour.index(interval)
return day_week_month_year.index(interval)
# 0: all "PT" values should come before "P" values (i.e, PT10M)
# 1: order values within the above arrays ("D" before "W")
# 2: sort by numeric value (PT10M before PT15M)
# 3: sort by any week starting/ending values
plist = {
0: sort_day_week(),
1: pos["SECOND"] if is_less_than_day else pos["THIRD"],
2: sort_interval(),
3: float(result.group(3)),
}
return plist.get(index, 0)
@classmethod
def get_time_grain_expressions(cls) -> Dict[Optional[str], str]:
"""
Return a dict of all supported time grains including any potential added grains
but excluding any potentially disabled grains in the config file.
:return: All time grain expressions supported by the engine
"""
# TODO: use @memoize decorator or similar to avoid recomputation on every call
time_grain_expressions = cls._time_grain_expressions.copy()
grain_addon_expressions = config["TIME_GRAIN_ADDON_EXPRESSIONS"]
time_grain_expressions.update(grain_addon_expressions.get(cls.engine, {}))
denylist: List[str] = config["TIME_GRAIN_DENYLIST"]
for key in denylist:
time_grain_expressions.pop(key)
return dict(
sorted(
time_grain_expressions.items(),
key=lambda x: (
cls._sort_time_grains(x, 0),
cls._sort_time_grains(x, 1),
cls._sort_time_grains(x, 2),
cls._sort_time_grains(x, 3),
),
)
)
@classmethod
def make_select_compatible(
cls, groupby_exprs: Dict[str, ColumnElement], select_exprs: List[ColumnElement]
) -> List[ColumnElement]:
"""
Some databases will just return the group-by field into the select, but don't
allow the group-by field to be put into the select list.
:param groupby_exprs: mapping between column name and column object
:param select_exprs: all columns in the select clause
:return: columns to be included in the final select clause
"""
return select_exprs
@classmethod
def fetch_data(
cls, cursor: Any, limit: Optional[int] = None
) -> List[Tuple[Any, ...]]:
"""
:param cursor: Cursor instance
:param limit: Maximum number of rows to be returned by the cursor
:return: Result of query
"""
if cls.arraysize:
cursor.arraysize = cls.arraysize
try:
if cls.limit_method == LimitMethod.FETCH_MANY and limit:
return cursor.fetchmany(limit)
return cursor.fetchall()
except Exception as ex:
raise cls.get_dbapi_mapped_exception(ex)
@classmethod
def expand_data(
cls, columns: List[Dict[Any, Any]], data: List[Dict[Any, Any]]
) -> Tuple[List[Dict[Any, Any]], List[Dict[Any, Any]], List[Dict[Any, Any]]]:
"""
Some engines support expanding nested fields. See implementation in Presto
spec for details.
:param columns: columns selected in the query
:param data: original data set
:return: list of all columns(selected columns and their nested fields),
expanded data set, listed of nested fields
"""
return columns, data, []
@classmethod
def alter_new_orm_column(cls, orm_col: "TableColumn") -> None:
"""Allow altering default column attributes when first detected/added
For instance special column like `__time` for Druid can be
set to is_dttm=True. Note that this only gets called when new
columns are detected/created"""
# TODO: Fix circular import caused by importing TableColumn
@classmethod
def epoch_to_dttm(cls) -> str:
"""
SQL expression that converts epoch (seconds) to datetime that can be used in a
query. The reference column should be denoted as `{col}` in the return
expression, e.g. "FROM_UNIXTIME({col})"
:return: SQL Expression
"""
raise NotImplementedError()
@classmethod
def epoch_ms_to_dttm(cls) -> str:
"""
SQL expression that converts epoch (milliseconds) to datetime that can be used
in a query.
:return: SQL Expression
"""
return cls.epoch_to_dttm().replace("{col}", "({col}/1000)")
@classmethod
def get_datatype(cls, type_code: Any) -> Optional[str]:
"""
Change column type code from cursor description to string representation.
:param type_code: Type code from cursor description
:return: String representation of type code
"""
if isinstance(type_code, str) and type_code != "":
return type_code.upper()
return None
@classmethod
def normalize_indexes(cls, indexes: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
"""
Normalizes indexes for more consistency across db engines
noop by default
:param indexes: Raw indexes as returned by SQLAlchemy
:return: cleaner, more aligned index definition
"""
return indexes
@classmethod
def extra_table_metadata(
cls, database: "Database", table_name: str, schema_name: str
) -> Dict[str, Any]:
"""
Returns engine-specific table metadata
:param database: Database instance
:param table_name: Table name
:param schema_name: Schema name
:return: Engine-specific table metadata
"""
# TODO: Fix circular import caused by importing Database
return {}
@classmethod
def apply_limit_to_sql(cls, sql: str, limit: int, database: "Database") -> str:
"""
Alters the SQL statement to apply a LIMIT clause
:param sql: SQL query
:param limit: Maximum number of rows to be returned by the query
:param database: Database instance
:return: SQL query with limit clause
"""
# TODO: Fix circular import caused by importing Database
if cls.limit_method == LimitMethod.WRAP_SQL:
sql = sql.strip("\t\n ;")
qry = (
select("*")
.select_from(TextAsFrom(text(sql), ["*"]).alias("inner_qry"))
.limit(limit)
)
return database.compile_sqla_query(qry)
if cls.limit_method == LimitMethod.FORCE_LIMIT:
parsed_query = sql_parse.ParsedQuery(sql)
sql = parsed_query.set_or_update_query_limit(limit)
return sql
@classmethod
def get_limit_from_sql(cls, sql: str) -> Optional[int]:
"""
Extract limit from SQL query
:param sql: SQL query
:return: Value of limit clause in query
"""
parsed_query = sql_parse.ParsedQuery(sql)
return parsed_query.limit
@classmethod
def set_or_update_query_limit(cls, sql: str, limit: int) -> str:
"""
Create a query based on original query but with new limit clause
:param sql: SQL query
:param limit: New limit to insert/replace into query
:return: Query with new limit
"""
parsed_query = sql_parse.ParsedQuery(sql)
return parsed_query.set_or_update_query_limit(limit)
@staticmethod
def csv_to_df(**kwargs: Any) -> pd.DataFrame:
"""Read csv into Pandas DataFrame
:param kwargs: params to be passed to DataFrame.read_csv
:return: Pandas DataFrame containing data from csv
"""
kwargs["encoding"] = "utf-8"
kwargs["iterator"] = True
chunks = pd.read_csv(**kwargs)
df = pd.concat(chunk for chunk in chunks)
return df
@classmethod
def df_to_sql(cls, df: pd.DataFrame, **kwargs: Any) -> None:
"""Upload data from a Pandas DataFrame to a database. For
regular engines this calls the DataFrame.to_sql() method. Can be
overridden for engines that don't work well with to_sql(), e.g.
BigQuery.
:param df: Dataframe with data to be uploaded
:param kwargs: kwargs to be passed to to_sql() method
"""
df.to_sql(**kwargs)
@classmethod
def create_table_from_csv( # pylint: disable=too-many-arguments
cls,
filename: str,
table: Table,
database: "Database",
csv_to_df_kwargs: Dict[str, Any],
df_to_sql_kwargs: Dict[str, Any],
) -> None:
"""
Create table from contents of a csv. Note: this method does not create
metadata for the table.
"""
df = cls.csv_to_df(filepath_or_buffer=filename, **csv_to_df_kwargs)
engine = cls.get_engine(database)
if table.schema:
# only add schema when it is preset and non empty
df_to_sql_kwargs["schema"] = table.schema
if engine.dialect.supports_multivalues_insert:
df_to_sql_kwargs["method"] = "multi"
cls.df_to_sql(df=df, con=engine, **df_to_sql_kwargs)
@classmethod
def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
"""
Convert Python datetime object to a SQL expression
:param target_type: The target type of expression
:param dttm: The datetime object
:return: The SQL expression
"""
return None
@classmethod
def create_table_from_excel( # pylint: disable=too-many-arguments
cls,
filename: str,
table: Table,
database: "Database",
excel_to_df_kwargs: Dict[str, Any],
df_to_sql_kwargs: Dict[str, Any],
) -> None:
"""
Create table from contents of a excel. Note: this method does not create
metadata for the table.
"""
df = pd.read_excel(io=filename, **excel_to_df_kwargs)
engine = cls.get_engine(database)
if table.schema:
# only add schema when it is preset and non empty
df_to_sql_kwargs["schema"] = table.schema
if engine.dialect.supports_multivalues_insert:
df_to_sql_kwargs["method"] = "multi"
cls.df_to_sql(df=df, con=engine, **df_to_sql_kwargs)
@classmethod
def get_all_datasource_names(
cls, database: "Database", datasource_type: str
) -> List[utils.DatasourceName]:
"""Returns a list of all tables or views in database.
:param database: Database instance
:param datasource_type: Datasource_type can be 'table' or 'view'
:return: List of all datasources in database or schema
"""
# TODO: Fix circular import caused by importing Database
schemas = database.get_all_schema_names(
cache=database.schema_cache_enabled,
cache_timeout=database.schema_cache_timeout,
force=True,
)
all_datasources: List[utils.DatasourceName] = []
for schema in schemas:
if datasource_type == "table":
all_datasources += database.get_all_table_names_in_schema(
schema=schema,
force=True,
cache=database.table_cache_enabled,
cache_timeout=database.table_cache_timeout,
)
elif datasource_type == "view":
all_datasources += database.get_all_view_names_in_schema(
schema=schema,
force=True,
cache=database.table_cache_enabled,
cache_timeout=database.table_cache_timeout,
)
else:
raise Exception(f"Unsupported datasource_type: {datasource_type}")
return all_datasources
@classmethod
def handle_cursor(cls, cursor: Any, query: Query, session: Session) -> None:
"""Handle a live cursor between the execute and fetchall calls
The flow works without this method doing anything, but it allows
for handling the cursor and updating progress information in the
query object"""
# TODO: Fix circular import error caused by importing sql_lab.Query
@classmethod
def extract_error_message(cls, ex: Exception) -> str:
return f"{cls.engine} error: {cls._extract_error_message(ex)}"
@classmethod
def _extract_error_message(cls, ex: Exception) -> str:
"""Extract error message for queries"""
return utils.error_msg_from_exception(ex)
@classmethod
def extract_errors(cls, ex: Exception) -> List[Dict[str, Any]]:
return [
dataclasses.asdict(
SupersetError(
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
message=cls._extract_error_message(ex),
level=ErrorLevel.ERROR,
extra={"engine_name": cls.engine_name},
)
)
]
@classmethod
def adjust_database_uri(cls, uri: URL, selected_schema: Optional[str]) -> None:
"""
Mutate the database component of the SQLAlchemy URI.
The URI here represents the URI as entered when saving the database,
``selected_schema`` is the schema currently active presumably in
the SQL Lab dropdown. Based on that, for some database engine,
we can return a new altered URI that connects straight to the
active schema, meaning the users won't have to prefix the object
names by the schema name.
Some databases engines have 2 level of namespacing: database and
schema (postgres, oracle, mssql, ...)
For those it's probably better to not alter the database
component of the URI with the schema name, it won't work.
Some database drivers like presto accept '{catalog}/{schema}' in
the database component of the URL, that can be handled here.
"""
@classmethod
def patch(cls) -> None:
"""
TODO: Improve docstring and refactor implementation in Hive
"""
@classmethod
def get_schema_names(cls, inspector: Inspector) -> List[str]:
"""
Get all schemas from database
:param inspector: SqlAlchemy inspector
:return: All schemas in the database
"""
return sorted(inspector.get_schema_names())
@classmethod
def get_table_names(
cls, database: "Database", inspector: Inspector, schema: Optional[str]
) -> List[str]:
"""
Get all tables from schema
:param inspector: SqlAlchemy inspector
:param schema: Schema to inspect. If omitted, uses default schema for database
:return: All tables in schema
"""
tables = inspector.get_table_names(schema)
if schema and cls.try_remove_schema_from_table_name:
tables = [re.sub(f"^{schema}\\.", "", table) for table in tables]
return sorted(tables)
@classmethod
def get_view_names(
cls, database: "Database", inspector: Inspector, schema: Optional[str]
) -> List[str]:
"""
Get all views from schema
:param inspector: SqlAlchemy inspector
:param schema: Schema name. If omitted, uses default schema for database
:return: All views in schema
"""
views = inspector.get_view_names(schema)
if schema and cls.try_remove_schema_from_table_name:
views = [re.sub(f"^{schema}\\.", "", view) for view in views]
return sorted(views)
@classmethod
def get_table_comment(
cls, inspector: Inspector, table_name: str, schema: Optional[str]
) -> Optional[str]:
"""
Get comment of table from a given schema and table
:param inspector: SqlAlchemy Inspector instance
:param table_name: Table name
:param schema: Schema name. If omitted, uses default schema for database
:return: comment of table
"""
comment = None
try:
comment = inspector.get_table_comment(table_name, schema)
comment = comment.get("text") if isinstance(comment, dict) else None
except NotImplementedError:
# It's expected that some dialects don't implement the comment method
pass
except Exception as ex: # pylint: disable=broad-except
logger.error("Unexpected error while fetching table comment")
logger.exception(ex)
return comment
@classmethod
def get_columns(
cls, inspector: Inspector, table_name: str, schema: Optional[str]
) -> List[Dict[str, Any]]:
"""
Get all columns from a given schema and table
:param inspector: SqlAlchemy Inspector instance
:param table_name: Table name
:param schema: Schema name. If omitted, uses default schema for database
:return: All columns in table
"""
return inspector.get_columns(table_name, schema)
@classmethod
def where_latest_partition( # pylint: disable=too-many-arguments
cls,
table_name: str,
schema: Optional[str],
database: "Database",
query: Select,
columns: Optional[List[Dict[str, str]]] = None,
) -> Optional[Select]:
"""
Add a where clause to a query to reference only the most recent partition
:param table_name: Table name
:param schema: Schema name
:param database: Database instance
:param query: SqlAlchemy query
:param columns: List of TableColumns
:return: SqlAlchemy query with additional where clause referencing latest
partition
"""
# TODO: Fix circular import caused by importing Database, TableColumn
return None
@classmethod
def _get_fields(cls, cols: List[Dict[str, Any]]) -> List[Any]:
return [column(c["name"]) for c in cols]
@classmethod
def select_star( # pylint: disable=too-many-arguments,too-many-locals
cls,
database: "Database",
table_name: str,
engine: Engine,
schema: Optional[str] = None,
limit: int = 100,
show_cols: bool = False,
indent: bool = True,
latest_partition: bool = True,
cols: Optional[List[Dict[str, Any]]] = None,
) -> str:
"""
Generate a "SELECT * from [schema.]table_name" query with appropriate limit.
WARNING: expects only unquoted table and schema names.
:param database: Database instance
:param table_name: Table name, unquoted
:param engine: SqlALchemy Engine instance
:param schema: Schema, unquoted
:param limit: limit to impose on query
:param show_cols: Show columns in query; otherwise use "*"
:param indent: Add indentation to query
:param latest_partition: Only query latest partition
:param cols: Columns to include in query
:return: SQL query
"""
fields: Union[str, List[Any]] = "*"
cols = cols or []
if (show_cols or latest_partition) and not cols:
cols = database.get_columns(table_name, schema)
if show_cols:
fields = cls._get_fields(cols)
quote = engine.dialect.identifier_preparer.quote
if schema:
full_table_name = quote(schema) + "." + quote(table_name)
else:
full_table_name = quote(table_name)
qry = select(fields).select_from(text(full_table_name))
if limit:
qry = qry.limit(limit)
if latest_partition:
partition_query = cls.where_latest_partition(
table_name, schema, database, qry, columns=cols
)
if partition_query is not None:
qry = partition_query
sql = database.compile_sqla_query(qry)
if indent:
sql = sqlparse.format(sql, reindent=True)
return sql
@classmethod
def estimate_statement_cost(cls, statement: str, cursor: Any,) -> Dict[str, Any]:
"""
Generate a SQL query that estimates the cost of a given statement.
:param statement: A single SQL statement
:param cursor: Cursor instance
:return: Dictionary with different costs
"""
raise Exception("Database does not support cost estimation")
@classmethod
def query_cost_formatter(
cls, raw_cost: List[Dict[str, Any]]
) -> List[Dict[str, str]]:
"""
Format cost estimate.
:param raw_cost: Raw estimate from `estimate_query_cost`
:return: Human readable cost estimate
"""
raise Exception("Database does not support cost estimation")
@classmethod
def process_statement(
cls, statement: str, database: "Database", user_name: str
) -> str:
"""
Process a SQL statement by stripping and mutating it.
:param statement: A single SQL statement
:param database: Database instance
:param username: Effective username
:return: Dictionary with different costs
"""
parsed_query = ParsedQuery(statement)
sql = parsed_query.stripped()
sql_query_mutator = config["SQL_QUERY_MUTATOR"]
if sql_query_mutator:
sql = sql_query_mutator(sql, user_name, security_manager, database)
return sql
@classmethod
def estimate_query_cost(
cls, database: "Database", schema: str, sql: str, source: Optional[str] = None
) -> List[Dict[str, Any]]:
"""
Estimate the cost of a multiple statement SQL query.
:param database: Database instance
:param schema: Database schema
:param sql: SQL query with possibly multiple statements
:param source: Source of the query (eg, "sql_lab")
"""
extra = database.get_extra() or {}
if not cls.get_allow_cost_estimate(extra):
raise Exception("Database does not support cost estimation")
user_name = g.user.username if g.user else None
parsed_query = sql_parse.ParsedQuery(sql)
statements = parsed_query.get_statements()
engine = cls.get_engine(database, schema=schema, source=source)
costs = []
with closing(engine.raw_connection()) as conn:
cursor = conn.cursor()
for statement in statements:
processed_statement = cls.process_statement(
statement, database, user_name
)
costs.append(cls.estimate_statement_cost(processed_statement, cursor))
return costs
@classmethod
def modify_url_for_impersonation(
cls, url: URL, impersonate_user: bool, username: Optional[str]
) -> None:
"""
Modify the SQL Alchemy URL object with the user to impersonate if applicable.
:param url: SQLAlchemy URL object
:param impersonate_user: Flag indicating if impersonation is enabled
:param username: Effective username
"""
if impersonate_user and username is not None:
url.username = username
@classmethod
def update_impersonation_config(
cls, connect_args: Dict[str, Any], uri: str, username: Optional[str],
) -> None:
"""
Update a configuration dictionary
that can set the correct properties for impersonating users
:param connect_args: config to be updated
:param uri: URI
:param impersonate_user: Flag indicating if impersonation is enabled
:param username: Effective username
:return: None
"""
@classmethod
def execute(cls, cursor: Any, query: str, **kwargs: Any) -> None:
"""
Execute a SQL query
:param cursor: Cursor instance
:param query: Query to execute
:param kwargs: kwargs to be passed to cursor.execute()
:return:
"""
if not cls.allows_sql_comments:
query = sql_parse.strip_comments_from_sql(query)
if cls.arraysize:
cursor.arraysize = cls.arraysize
try:
cursor.execute(query)
except Exception as ex:
raise cls.get_dbapi_mapped_exception(ex)
@classmethod
def make_label_compatible(cls, label: str) -> Union[str, quoted_name]:
"""
Conditionally mutate and/or quote a sqlalchemy expression label. If
force_column_alias_quotes is set to True, return the label as a
sqlalchemy.sql.elements.quoted_name object to ensure that the select query
and query results have same case. Otherwise return the mutated label as a
regular string. If maxmimum supported column name length is exceeded,
generate a truncated label by calling truncate_label().
:param label: expected expression label/alias
:return: conditionally mutated label supported by the db engine
"""
label_mutated = cls._mutate_label(label)
if (
cls.max_column_name_length
and len(label_mutated) > cls.max_column_name_length
):
label_mutated = cls._truncate_label(label)
if cls.force_column_alias_quotes:
label_mutated = quoted_name(label_mutated, True)
return label_mutated
@classmethod
def get_sqla_column_type(
cls,
column_type: Optional[str],
column_type_mappings: Tuple[
Tuple[
Pattern[str],
Union[TypeEngine, Callable[[Match[str]], TypeEngine]],
GenericDataType,
],
...,
] = column_type_mappings,
) -> Union[Tuple[TypeEngine, GenericDataType], None]:
"""
Return a sqlalchemy native column type that corresponds to the column type
defined in the data source (return None to use default type inferred by
SQLAlchemy). Override `column_type_mappings` for specific needs
(see MSSQL for example of NCHAR/NVARCHAR handling).
:param column_type: Column type returned by inspector
:return: SqlAlchemy column type
"""
if not column_type:
return None
for regex, sqla_type, generic_type in column_type_mappings:
match = regex.match(column_type)
if match:
if callable(sqla_type):
return sqla_type(match), generic_type
return sqla_type, generic_type
return None
@staticmethod
def _mutate_label(label: str) -> str:
"""
Most engines support mixed case aliases that can include numbers
and special characters, like commas, parentheses etc. For engines that
have restrictions on what types of aliases are supported, this method
can be overridden to ensure that labels conform to the engine's
limitations. Mutated labels should be deterministic (input label A always
yields output label X) and unique (input labels A and B don't yield the same
output label X).
:param label: Preferred expression label
:return: Conditionally mutated label
"""
return label
@classmethod
def _truncate_label(cls, label: str) -> str:
"""
In the case that a label exceeds the max length supported by the engine,
this method is used to construct a deterministic and unique label based on
the original label. By default this returns an md5 hash of the original label,
conditionally truncated if the length of the hash exceeds the max column length
of the engine.
:param label: Expected expression label
:return: Truncated label
"""
label = hashlib.md5(label.encode("utf-8")).hexdigest()
# truncate hash if it exceeds max length
if cls.max_column_name_length and len(label) > cls.max_column_name_length:
label = label[: cls.max_column_name_length]
return label
@classmethod
def column_datatype_to_string(
cls, sqla_column_type: TypeEngine, dialect: Dialect
) -> str:
"""
Convert sqlalchemy column type to string representation.
By default removes collation and character encoding info to avoid unnecessarily
long datatypes.
:param sqla_column_type: SqlAlchemy column type
:param dialect: Sqlalchemy dialect
:return: Compiled column type
"""
sqla_column_type = sqla_column_type.copy()
if hasattr(sqla_column_type, "collation"):
sqla_column_type.collation = None
if hasattr(sqla_column_type, "charset"):
sqla_column_type.charset = None
return sqla_column_type.compile(dialect=dialect).upper()
@classmethod
def get_function_names(cls, database: "Database") -> List[str]:
"""
Get a list of function names that are able to be called on the database.
Used for SQL Lab autocomplete.
:param database: The database to get functions for
:return: A list of function names useable in the database
"""
return []
@staticmethod
def pyodbc_rows_to_tuples(data: List[Any]) -> List[Tuple[Any, ...]]:
"""
Convert pyodbc.Row objects from `fetch_data` to tuples.
:param data: List of tuples or pyodbc.Row objects
:return: List of tuples
"""
if data and type(data[0]).__name__ == "Row":
data = [tuple(row) for row in data]
return data
@staticmethod
def mutate_db_for_connection_test(database: "Database") -> None:
"""
Some databases require passing additional parameters for validating database
connections. This method makes it possible to mutate the database instance prior
to testing if a connection is ok.
:param database: instance to be mutated
"""
return None
@staticmethod
def get_extra_params(database: "Database") -> Dict[str, Any]:
"""
Some databases require adding elements to connection parameters,
like passing certificates to `extra`. This can be done here.
:param database: database instance from which to extract extras
:raises CertificateException: If certificate is not valid/unparseable
"""
extra: Dict[str, Any] = {}
if database.extra:
try:
extra = json.loads(database.extra)
except json.JSONDecodeError as ex:
logger.error(ex)
raise ex
return extra
@classmethod
def is_readonly_query(cls, parsed_query: ParsedQuery) -> bool:
"""Pessimistic readonly, 100% sure statement won't mutate anything"""
return (
parsed_query.is_select()
or parsed_query.is_explain()
or parsed_query.is_show()
)
@classmethod
def get_column_spec(
cls,
native_type: Optional[str],
source: utils.ColumnTypeSource = utils.ColumnTypeSource.GET_TABLE,
column_type_mappings: Tuple[
Tuple[
Pattern[str],
Union[TypeEngine, Callable[[Match[str]], TypeEngine]],
GenericDataType,
],
...,
] = column_type_mappings,
) -> Union[ColumnSpec, None]:
"""
Converts native database type to sqlalchemy column type.
:param native_type: Native database typee
:param source: Type coming from the database table or cursor description
:return: ColumnSpec object
"""
col_types = cls.get_sqla_column_type(
native_type, column_type_mappings=column_type_mappings
)
if col_types:
column_type, generic_type = col_types
# wrap temporal types in custom type that supports literal binding
# using datetimes
if generic_type == GenericDataType.TEMPORAL:
column_type = literal_dttm_type_factory(
type(column_type), cls, native_type or ""
)
is_dttm = generic_type == GenericDataType.TEMPORAL
return ColumnSpec(
sqla_type=column_type, generic_type=generic_type, is_dttm=is_dttm
)
return None
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
:param url: The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
* author_type
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* song_book_id
* title
* alternate_title
* song_key
* transpose_by
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* song_number
* theme_name
* search_title
* search_lyrics
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(
'authors', metadata,
Column('id', types.Integer(), primary_key=True),
Column('first_name', types.Unicode(128)),
Column('last_name', types.Unicode(128)),
Column('display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(
'media_files', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), default=None),
Column('file_name', types.Unicode(255), nullable=False),
Column('type', types.Unicode(64), nullable=False, default='audio'),
Column('weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(
'song_books', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), nullable=False),
Column('publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(
'songs', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_book_id', types.Integer(), ForeignKey('song_books.id'), default=None),
Column('title', types.Unicode(255), nullable=False),
Column('alternate_title', types.Unicode(255)),
Column('song_key', types.Unicode(3)),
Column('transpose_by', types.Integer(), default=0),
Column('chords', types.UnicodeText),
Column('lyrics', types.UnicodeText, nullable=False),
Column('verse_order', types.Unicode(128)),
Column('copyright', types.Unicode(255)),
Column('comments', types.UnicodeText),
Column('ccli_number', types.Unicode(64)),
Column('song_number', types.Unicode(64)),
Column('theme_name', types.Unicode(128)),
Column('search_title', types.Unicode(255), index=True, nullable=False),
Column('search_lyrics', types.UnicodeText, nullable=False),
Column('create_date', types.DateTime(), default=func.now()),
Column('last_modified', types.DateTime(), default=func.now(), onupdate=func.now()),
Column('temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(
'topics', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(
'authors_songs', metadata,
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('author_type', types.Unicode(255), primary_key=True, nullable=False, server_default=text('""'))
)
# Definition of the "songs_topics" table
songs_topics_table = Table(
'songs_topics', metadata,
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('topic_id', types.Integer(), ForeignKey('topics.id'), primary_key=True)
)
mapper(Author, authors_table, properties={
'songs': relation(Song, secondary=authors_songs_table, viewonly=True)
})
mapper(AuthorSong, authors_songs_table, properties={
'author': relation(Author)
})
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table, properties={
# Use the authors_songs relation when you need access to the 'author_type' attribute
# or when creating new relations
'authors_songs': relation(AuthorSong, cascade="all, delete-orphan"),
# Use lazy='joined' to always load authors when the song is fetched from the database (bug 1366198)
'authors': relation(Author, secondary=authors_songs_table, viewonly=True, lazy='joined'),
'book': relation(Book, backref='songs'),
'media_files': relation(MediaFile, backref='songs', order_by=media_files_table.c.weight),
'topics': relation(Topic, backref='songs', secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
Column('serial', types.Integer, Sequence("serial_seq")),
Column('name', types.String(60), nullable=False, default=name_default),
Column('t_list',
types.ARRAY(types.String(60)),
nullable=False,
default=t_list_default),
Column('t_enum',
types.Enum(MyEnum),
nullable=False,
default=t_enum_default),
Column('t_int_enum',
types.Enum(MyIntEnum),
nullable=False,
default=t_int_enum_default),
Column('t_datetime',
types.DateTime(),
nullable=False,
default=t_datetime_default),
Column('t_date', types.DateTime(), nullable=False, default=t_date_default),
Column('t_date_2',
types.DateTime(),
nullable=False,
default=t_date_2_default),
Column('t_interval',
types.Interval(),
nullable=False,
default=t_interval_default),
Column('t_boolean', types.Boolean(), nullable=False, default=True),
Column('version', PG_UUID, default=uuid4, onupdate=uuid4))
class BearerToken(Base):
__tablename__ = 'oauth2_bearer_token'
access_token = Column(types.Unicode(100), primary_key=True)
client_pk = Column(GUID,
ForeignKey(Client.pk,
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
user_pk = Column(GUID,
ForeignKey(User.pk,
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
scopes = Column(postgresql.ARRAY(types.Unicode(80)), nullable=False)
expires_at = Column(types.DateTime(timezone=True), nullable=True)
refresh_token = Column(types.Unicode(100), nullable=True, unique=True)
client = orm.relationship(Client)
user = orm.relationship(User)
@property
def client_id(self):
return self.client_pk.hex
@hybrid_property
def expires(self):
"""
.. seealso:: https://flask-oauthlib.readthedocs.org/en/latest/api.html#flask_oauthlib.provider.OAuth2Provider.tokengetter
"""
return self.expires_at.astimezone(tzutc()).replace(tzinfo=None)
def __init__(self,
client,
user,
access_token,
scopes,
expires_in,
refresh_token=None):
"""
:param client:
:type client: Client
:param user:
:type user: User
:param access_token:
:type access_token: basestring
:keyword scopes:
:type scopes: collections.Iterable
:keyword expires_in:
:type expires_in: datetime.timedelta
"""
if not isinstance(expires_in, timedelta):
expires_in = timedelta(seconds=expires_in)
self.client = client
self.user = user
self.access_token = access_token
self.scopes = scopes
self.expires_at = datetime.now(tzutc()) + expires_in
self.refresh_token = refresh_token
class User(db.Model, UserMixin, DeploymentMixin): #pylint: disable=no-init,too-few-public-methods
'''
User
'''
__tablename__ = 'users'
id = Column(types.Integer, autoincrement=True, primary_key=True) #pylint: disable=invalid-name
picture_id = Column(
types.String,
default=lambda: base64.urlsafe_b64encode(os.urandom(20))[0:-2])
has_picture = Column(types.Boolean, default=False)
first_name = Column(types.String,
info={
'label': lazy_gettext('First Name'),
})
last_name = Column(types.String,
info={
'label': lazy_gettext('Last Name'),
})
email = Column(EmailType,
nullable=False,
info={
'label': lazy_gettext('Email'),
})
password = Column(types.String,
nullable=False,
info={
'label': lazy_gettext('Password'),
})
active = Column(types.Boolean, nullable=False)
last_login_at = Column(types.DateTime())
current_login_at = Column(types.DateTime())
confirmed_at = Column(types.DateTime())
last_login_ip = Column(types.Text)
current_login_ip = Column(types.Text)
login_count = Column(types.Integer)
position = Column(types.String, info={
'label': lazy_gettext('Position'),
})
organization = Column(types.String,
info={
'label': lazy_gettext('Organization'),
})
organization_type = Column(
types.String,
info={
'label': lazy_gettext('Type of Organization'),
'description':
lazy_gettext('The type of organization you work for'),
'choices': [(k, v) for k, v in ORG_TYPES.iteritems()]
})
country = Column(CountryType, info={
'label': lazy_gettext('Country'),
})
city = Column(types.String, info={'label': lazy_gettext('City')})
latlng = Column(types.String,
info={
'label': lazy_gettext('Location'),
'description': lazy_gettext('Enter your location')
})
projects = Column(
types.Text,
info={
'label':
lazy_gettext('Projects'),
'description':
lazy_gettext(
'Add name and url or short description of any current work projects.'
)
})
tutorial_step = Column(types.Integer())
created_at = Column(types.DateTime(), default=datetime.datetime.now)
updated_at = Column(types.DateTime(),
default=datetime.datetime.now,
onupdate=datetime.datetime.now)
def is_admin(self):
return self.email in current_app.config.get('ADMIN_UI_USERS', [])
@property
def full_name(self):
return u"%s %s" % (self.first_name, self.last_name)
@property
def full_location(self):
loc = []
if self.city:
loc.append(self.city)
if self.country and self.country.code != 'ZZ':
loc.append(self.country.name)
return ", ".join(loc)
@property
def display_in_search(self):
'''
Determine whether user has filled out bare minimum to display in search
results.
Specifically, we want to make sure that the first and last name
are both non-NULL and non-blank.
'''
return bool(self.first_name and self.last_name)
@property
def picture_path(self):
'''
Path where picture would be found (hosted on S3).
'''
return "{}/static/pictures/{}/{}".format(
current_app.config['NOI_DEPLOY'], self.id, self.picture_id)
@property
def picture_url(self):
'''
Full path to picture.
'''
return 'https://s3.amazonaws.com/{bucket}/{path}'.format(
bucket=current_app.config['S3_BUCKET_NAME'],
path=self.picture_path)
def remove_picture(self):
conn = S3Connection(current_app.config['S3_ACCESS_KEY_ID'],
current_app.config['S3_SECRET_ACCESS_KEY'])
bucket = conn.get_bucket(current_app.config['S3_BUCKET_NAME'])
if bucket.get_key(self.picture_path):
bucket.delete_key(self.picture_path)
self.has_picture = False
def upload_picture(self, fileobj, mimetype):
'''
Upload the given file object with the given mime type to S3 and
mark the user as having a picture.
'''
conn = S3Connection(current_app.config['S3_ACCESS_KEY_ID'],
current_app.config['S3_SECRET_ACCESS_KEY'])
bucket = conn.get_bucket(current_app.config['S3_BUCKET_NAME'])
bucket.make_public(recursive=False)
k = bucket.new_key(self.picture_path)
k.set_metadata('Content-Type', mimetype)
k.set_contents_from_file(fileobj)
k.make_public()
self.has_picture = True
@property
def helpful_users(self, limit=10):
'''
Returns a list of (user, score) tuples with matching positive skills,
ordered by the most helpful (highest score) descending.
'''
my_skills = aliased(UserSkill, name='my_skills', adapt_on_names=True)
their_skills = aliased(UserSkill,
name='their_skills',
adapt_on_names=True)
return User.query_in_deployment().\
add_column(func.sum(their_skills.level - my_skills.level)).\
filter(their_skills.user_id != my_skills.user_id).\
filter(User.id == their_skills.user_id).\
filter(their_skills.name == my_skills.name).\
filter(my_skills.user_id == self.id).\
filter(my_skills.level == LEVELS['LEVEL_I_WANT_TO_LEARN']['score']).\
group_by(User).\
order_by(desc(func.sum(their_skills.level - my_skills.level))).\
limit(limit)
@property
def nearest_neighbors(self, limit=10):
'''
Returns a list of (user, score) tuples with the closest matching
skills. If they haven't answered the equivalent skill question, we
consider that a very big difference (12).
Order is closest to least close, which is an ascending score.
'''
my_skills = aliased(UserSkill, name='my_skills', adapt_on_names=True)
their_skills = aliased(UserSkill,
name='their_skills',
adapt_on_names=True)
# difference we assume for user that has not answered question
unanswered_difference = (LEVELS['LEVEL_I_CAN_DO_IT']['score'] -
LEVELS['LEVEL_I_WANT_TO_LEARN']['score']) * 2
return User.query_in_deployment().\
add_column(((len(self.skills) - func.count(func.distinct(their_skills.id))) *
unanswered_difference) + \
func.sum(func.abs(their_skills.level - my_skills.level))).\
filter(their_skills.user_id != my_skills.user_id).\
filter(User.id == their_skills.user_id).\
filter(their_skills.name == my_skills.name).\
filter(my_skills.user_id == self.id).\
group_by(User).\
order_by(((len(self.skills) - func.count(func.distinct(their_skills.id)))
* unanswered_difference) + \
func.sum(func.abs(their_skills.level - my_skills.level))).\
limit(limit)
@property
def has_fully_registered(self):
'''
Returns whether the user has fully completed the registration/signup
flow.
'''
return db.session.query(UserJoinedEvent).\
filter_by(user_id=self.id).\
first() is not None
def set_fully_registered(self):
'''
Marks the user as having fully completed the registration/signup
flow, if they haven't already.
'''
if self.has_fully_registered:
return
db.session.add(UserJoinedEvent.from_user(self))
def match(self, level, limit=10):
'''
Returns a list of UserSkillMatch objects, in descending order of number
of skills matched for each user.
'''
skills_to_learn = [
s.name for s in self.skills
if s.level == LEVELS['LEVEL_I_WANT_TO_LEARN']['score']
]
if skills_to_learn:
matched_users = User.query_in_deployment().\
add_column(func.string_agg(UserSkill.name, ',')).\
add_column(func.count(UserSkill.id)).\
filter(UserSkill.name.in_(skills_to_learn)).\
filter(User.id == UserSkill.user_id).\
filter(UserSkill.level == level).\
filter(UserSkill.user_id != self.id).\
group_by(User).\
order_by(func.count().desc()).\
limit(limit)
else:
matched_users = []
for user, question_ids_by_comma, count in matched_users:
yield UserSkillMatch(user, question_ids_by_comma.split(','))
def match_against(self, user):
'''
Returns a list of three-tuples in the format:
(<questionnaire id>, <count of matching questions>, <skill dict>, )
In descending order of <count of matching questions>.
<skill dict> is keyed by the skill level of the other user, with each
value being a set of questions they can answer at that level.
'''
skills = UserSkill.query.\
filter(UserSkill.user_id == user.id).\
filter(UserSkill.name.in_(
[s.name for s in
self.skills if s.level == LEVELS['LEVEL_I_WANT_TO_LEARN']['score']
])).all()
resp = {}
for skill in skills:
question = QUESTIONS_BY_ID[skill.name]
questionnaire_id = question['questionnaire']['id']
if questionnaire_id not in resp:
resp[questionnaire_id] = dict()
if skill.level not in resp[questionnaire_id]:
resp[questionnaire_id][skill.level] = set()
resp[questionnaire_id][skill.level].add(skill.name)
resp = [(qname,
sum([len(questions)
for questions in skill_levels.values()]), skill_levels)
for qname, skill_levels in resp.items()]
return sorted(resp, lambda a, b: a[1] - b[1], reverse=True)
def match_against_with_progress(self, user):
'''
Like match_against(), but also includes information about
areas of expertise the target user has that we don't match
on.
'''
progress = user.questionnaire_progress
matches = self.match_against(user)
match_areas = {}
for questionnaire_id, _, _ in matches:
match_areas[questionnaire_id] = True
for questionnaire_id, progress in progress.items():
if (questionnaire_id not in match_areas
and progress['answered'] > 0):
matches.append((questionnaire_id, 0, {}))
return matches
def match_against_with_progress_in_area(self, user, areaid):
'''
Return a tuple of (matched_skill_dict, unmatched_skill_dict)
for the given area.
matched_skill_dict contains information about skills that
the target user has which we want to learn, while
unmatched_skill_dict contains all other skills the target
user has.
Each dict is keyed by the skill level of the other user,
with each value being a set of questions they can answer at that
level.
'''
questionnaire = QUESTIONNAIRES_BY_ID[areaid]
matches = self.match_against(user)
matched_skill_dict = {}
matched_skills = {}
for questionnaire_id, _, skill_dict in matches:
if questionnaire_id == areaid:
matched_skill_dict = skill_dict
break
for question_ids in matched_skill_dict.values():
for question_id in question_ids:
matched_skills[question_id] = True
unmatched_skill_dict = {}
skill_levels = user.skill_levels
for topic in questionnaire.get('topics', []):
for question in topic['questions']:
qid = question['id']
if (qid in skill_levels and qid not in matched_skills):
level = skill_levels[qid]
if level not in unmatched_skill_dict:
unmatched_skill_dict[level] = []
unmatched_skill_dict[level].append(qid)
return (matched_skill_dict, unmatched_skill_dict)
@property
def questionnaire_progress(self):
'''
Return a dictionary mapping top-level skill area IDs (e.g.,
'opendata', 'prizes') to information about how many questions
the user has answered in that skill area.
'''
skill_levels = self.skill_levels
progress = {}
for questionnaire in QUESTIONNAIRES:
topic_progress = {'answered': 0, 'total': 0}
progress[questionnaire['id']] = topic_progress
for topic in questionnaire.get('topics', []):
for question in topic['questions']:
topic_progress['total'] += 1
if question['id'] in skill_levels:
topic_progress['answered'] += 1
return progress
@property
def skill_levels(self):
'''
Dictionary of this user's entered skills, keyed by the id of the skill.
'''
return dict([(skill.name, skill.level) for skill in self.skills])
@property
def connections(self):
'''
Count the number of distinct email addresses this person has sent or
received messages from in the deployment.
'''
sent = db.session.query(func.count(func.distinct(Email.to_user_id))).\
filter(Email.to_user_id != self.id).\
filter(Email.from_user_id == self.id).first()[0]
received = db.session.query(func.count(func.distinct(Email.from_user_id))).\
filter(Email.from_user_id != self.id).\
filter(Email.to_user_id == self.id).first()[0]
return sent + received
def set_skill(self, skill_name, skill_level):
'''
Set the level of a single skill by name.
'''
if skill_name not in QUESTIONS_BY_ID:
return
try:
if int(skill_level) not in VALID_SKILL_LEVELS:
return
except ValueError:
return
for skill in self.skills:
if skill_name == skill.name:
skill.level = skill_level
db.session.add(skill)
return
db.session.add(
UserSkill(user_id=self.id, name=skill_name, level=skill_level))
def email_connect(self, users):
'''
Indicate that this user has opened an email window with this list of
users as recipients.
'''
event = ConnectionEvent()
for user in users:
event.emails.append(Email(from_user_id=self.id,
to_user_id=user.id))
db.session.add(event)
return event
roles = orm.relationship('Role',
secondary='role_users',
backref=orm.backref('users', lazy='dynamic'))
expertise_domains = orm.relationship('UserExpertiseDomain',
cascade='all,delete-orphan',
backref='user')
languages = orm.relationship('UserLanguage',
cascade='all,delete-orphan',
backref='user')
skills = orm.relationship('UserSkill',
cascade='all,delete-orphan',
backref='user')
@classmethod
def get_most_complete_profiles(cls, limit=10):
'''
Obtain a list of most complete profiles, as (User, score) tuples.
'''
return User.query_in_deployment().\
add_column(func.count(UserSkill.id)).\
filter(User.id == UserSkill.user_id).\
group_by(User).\
order_by(func.count(UserSkill.id).desc()).\
limit(limit)
@classmethod
def get_most_connected_profiles(cls, limit=10):
'''
Obtain a list of most connected profiles, as descending (User, score)
tuples.
'''
count_of_unique_emails = func.count(
func.distinct(
cast(Email.to_user_id, String) + '-' +
cast(Email.from_user_id, String)))
return User.query_in_deployment().\
add_column(count_of_unique_emails).\
filter((User.id == Email.from_user_id) | (User.id == Email.to_user_id)).\
group_by(User).\
order_by(count_of_unique_emails.desc()).\
limit(limit)
@hybrid_property
def expertise_domain_names(self):
'''
Convenient list of expertise domains by name.
'''
return [ed.name for ed in self.expertise_domains]
@expertise_domain_names.setter
def _expertise_domains_setter(self, values):
'''
Update expertise domains in bulk. Values are array of names.
'''
# Only add new expertise
for val in values:
if val not in self.expertise_domain_names:
db.session.add(UserExpertiseDomain(name=val, user_id=self.id))
# delete expertise no longer found
expertise_to_remove = []
for exp in self.expertise_domains:
if exp.name not in values:
expertise_to_remove.append(exp)
for exp in expertise_to_remove:
self.expertise_domains.remove(exp)
def get_area_scores(self):
skill_levels = self.skill_levels
result = {}
NO_ANSWER = -999
for questionnaire in QUESTIONNAIRES:
if not questionnaire['questions']:
continue
max_score = NO_ANSWER
answers_with_score = {}
for question in questionnaire['questions']:
if question['id'] in skill_levels:
score = skill_levels[question['id']]
if score > max_score:
max_score = score
if score not in answers_with_score:
answers_with_score[score] = 0
answers_with_score[score] += 1
reported_max_score = None
if max_score != NO_ANSWER:
reported_max_score = max_score
result[questionnaire['id']] = {
'skills': scores_to_skills(answers_with_score),
'max_score': reported_max_score
}
return result
@hybrid_property
def locales(self):
'''
Convenient list of locales for this user.
'''
return [l.locale for l in self.languages]
@locales.setter
def _languages_setter(self, values):
'''
Update locales for this user in bulk. Values are an array of language
codes.
'''
locale_codes = [l.language for l in self.locales]
# only add new languages
for val in values:
if val not in locale_codes:
db.session.add(UserLanguage(locale=val, user_id=self.id))
# delete languages no longer found
languages_to_remove = []
for lan in self.languages:
if lan.locale.language not in values:
languages_to_remove.append(lan)
for lan in languages_to_remove:
self.languages.remove(lan)
__table_args__ = (UniqueConstraint('deployment', 'email'), )
df['HOSPITAL'] = df['HP_ID'] + " " + df['HP_NAME']
df['STORE'] = df['STORE_ID'] + " " + df['STORE_NAME']
df['RM_POS_NAME'] = df['RM'] + " " + df['RM_NAME']
df['DSM_POS_NAME'] = df['DSM'] + " " + df['DSM_NAME']
df['RSP_POS_NAME'] = df['RSP'] + " " + df['RSP_NAME']
print(df)
print("start importing...")
df.to_sql(
"data",
con=engine,
if_exists="replace",
index=False,
dtype={
"YEAR": t.INTEGER(),
"DATE": t.DateTime(),
"MONTH": t.INTEGER(),
"QUARTER": t.INTEGER(),
"HP_ID": t.NVARCHAR(length=10),
"HP_NAME": t.NVARCHAR(length=100),
"HOSPITAL": t.NVARCHAR(length=110),
"STORE_ID": t.NVARCHAR(length=10),
"STORE_NAME": t.NVARCHAR(length=100),
"STORE": t.NVARCHAR(length=110),
"PROVINCE": t.NVARCHAR(length=3),
"CITY": t.NVARCHAR(length=30),
"COUNTY": t.NVARCHAR(length=30),
"LEVEL": t.NVARCHAR(length=4),
"IF_COMMUNITY": t.Boolean(),
"IF_DUALCALL": t.Boolean(),
"PRODUCT": t.NVARCHAR(length=10),
def create(
cls,
user_version,
user_version_nullable=False,
table_name='alembic_version_history',
metadata=None,
extra_columns=(),
user_version_column_name='user_version',
user_version_type=types.String(255),
direction_column_name='operation_direction',
operation_column_name='operation_type',
alembic_version_separator='##',
alembic_version_column_name='alembic_version',
prev_alembic_version_column_name='prev_alembic_version',
change_time_column_name='changed_at',
):
"""Autocreate a history table.
This table contains columns for:
* user version
* alembic version(s) prior to upgrade
* alembic version(s) after upgrade
* :paramref:`operation type <.Auditor.create.operation_column_name>`
* :paramref:`operation direction <.Auditor.create.direction_column_name>`
* operation direction
* upgrade time
The user may add their own columns and, to some extent, customize
those provided. See the parameter list for details.
:param user_version: a constant value or callable giving the user
version to be stored with each migration step. If callable, it
accepts the kwargs provided by alembic's on_version_apply
callback. A good value for this might be your application's git
version or current version tag.
.. note::
:paramref:`.Auditor.create.user_version` does not have to be
provided. It can be null. It is intended to tie an alembic version
to a specific point in your version control, so that you may
consult that history and know exactly the content of the patch
that was executed. Failing to include this information may
seriously dilute the value of keeping these records at all. For
that reason, we highly recommend providing *something* here.
If you pass ``None``, a warning will be raised.
:param user_version_nullable: Suppresses the above-mentioned warning.
:param table_name: The name of the version history table.
:param metadata: The SQLAlchemy MetaData object with which the table
is to be created. If not provided, a new one will be created.
:param extra_columns: A sequence of extra columns to add to the
default table. Each element of the column is a 2-tuple
``(col, val)`` where ``col`` is a SQLAlchemy ``Column`` and
``val`` is a value for it, expressed the same way as
:paramref:`~.Auditor.create.user_version`: as a constant,
type-appropriate value, or a function of kwargs returning such a
value.
:param user_version_column_name: the name used for the column
storing the value of :paramref:`~.Auditor.create.user_version`.
:param user_version_type: the SQL type of
:paramref:`~.Auditor.create.user_version`. If not specified, this
is assumed to be VARCHAR(32).
:param operation_column_name: the name of the column storing operation
type. Currently supported values are ``migrate`` and ``stamp``,
which indicate respectively that database changes are made, or
that the version is changed without effecting any true changes.
The field is nonnullable but unconstrained in case future Alembic
versions support other migration types.
:param direction_column_name: the name of the column storing the
operation direction. This column is an enum (native on backends that
support it, or given as a varchar with constraints) of the string
values ``up`` and ``down``. It is left nullable in case future
Alembic versions support migration types without an up/down
direction.
:param alembic_version_column_name: The name of the column storing
the "new" (i.e. after the operation is complete) alembic
version(s) of the database. Note that this is distinct in theory
from the "up" version of the migration operation, and distinct
in practice when the operation is a downgrade.
:param prev_alembic_version_column_name: The name of the column
storing the "old" (i.e before the operation is complete) alembic
version(s) of the database. Note that this is distinct in theory
from the "down" version of the migration operation, and distinct
in practice when the operation is a downgrade.
:param alembic_version_separator: if multiple alembic versions are
given for one of the alembic version columns, they are joined
together with ``alembic_version_separator`` as the delimiter.
:param change_time_column_name: the name of the column storing the
time of this migration
"""
if not user_version_nullable:
if user_version is None:
cls.version_warn()
elif callable(user_version):
orig_user_version = user_version
def user_version(**kw):
val = orig_user_version(**kw)
if val is None:
cls.version_warn(stacklevel=1)
return val
if metadata is None:
metadata = MetaData()
alembic_version_type = types.String(255)
columns = [
Column('id',
types.BIGINT().with_variant(types.Integer, 'sqlite'),
primary_key=True),
Column(alembic_version_column_name, alembic_version_type),
Column(prev_alembic_version_column_name, alembic_version_type),
CheckConstraint('coalesce(%s, %s) IS NOT NULL' %
(alembic_version_column_name,
prev_alembic_version_column_name),
name='alembic_versions_nonnull'),
Column(operation_column_name, types.String(32), nullable=False),
Column(direction_column_name, types.String(32), nullable=False),
Column(user_version_column_name, user_version_type),
Column(change_time_column_name, types.DateTime())
]
def alembic_vers(f):
return functools.partial(f, separator=alembic_version_separator)
col_vals = {
alembic_version_column_name: alembic_vers(ccv.new_alembic_version),
prev_alembic_version_column_name:
alembic_vers(ccv.old_alembic_version),
operation_column_name: ccv.operation_type,
direction_column_name: ccv.operation_direction,
user_version_column_name: user_version,
change_time_column_name: ccv.change_time,
}
for col, val in extra_columns:
columns.append(col)
if col.name in col_vals:
raise exc.AuditCreateError('value %s used twice' % col.name)
col_vals[col.name] = val
auditor = cls(Table(table_name, metadata, *columns), col_vals)
return auditor
class AuthUser(Base):
""" Table name: auth_users
::
id = Column(types.Integer(), primary_key=True)
login = Column(Unicode(80), default=u'', index=True)
_password = Column('password', Unicode(80), default=u'')
email = Column(Unicode(80), default=u'', index=True)
active = Column(types.Enum(u'Y',u'N',u'D'), default=u'Y')
"""
__tablename__ = 'auth_users'
__table_args__ = {"sqlite_autoincrement": True}
id = Column(types.Integer(), primary_key=True)
auth_id = Column(types.Integer, ForeignKey(AuthID.id), index=True)
provider = Column(Unicode(80), default=u'local', index=True)
login = Column(Unicode(80), default=u'', index=True)
salt = Column(Unicode(24))
_password = Column('password', Unicode(80), default=u'')
email = Column(Unicode(80), default=u'', index=True)
created = Column(types.DateTime(), default=func.now())
active = Column(types.Enum(u'Y',u'N',u'D', name=u"active"), default=u'Y')
def _set_password(self, password):
self.salt = self.get_salt(24)
password = password + self.salt
self._password = BCRYPTPasswordManager().encode(password, rounds=12)
def _get_password(self):
return self._password
password = synonym('_password', descriptor=property(_get_password, \
_set_password))
def get_salt(self, length):
m = hashlib.sha256()
word = ''
for i in xrange(length):
word += random.choice(string.ascii_letters)
m.update(word)
return unicode(m.hexdigest()[:length])
@classmethod
def get_by_id(cls, id):
"""
Returns AuthUser object or None by id
.. code-block:: python
from apex.models import AuthID
user = AuthID.get_by_id(1)
"""
return DBSession.query(cls).filter(cls.id==id).first()
@classmethod
def get_by_login(cls, login):
"""
Returns AuthUser object or None by login
.. code-block:: python
from apex.models import AuthUser
user = AuthUser.get_by_login('login')
"""
return DBSession.query(cls).filter(cls.login==login).first()
@classmethod
def get_by_email(cls, email):
"""
Returns AuthUser object or None by email
.. code-block:: python
from apex.models import AuthUser
user = AuthUser.get_by_email('*****@*****.**')
"""
return DBSession.query(cls).filter(cls.email==email).first()
@classmethod
def check_password(cls, **kwargs):
if kwargs.has_key('id'):
user = cls.get_by_id(kwargs['id'])
if kwargs.has_key('login'):
user = cls.get_by_login(kwargs['login'])
if not user:
return False
try:
if BCRYPTPasswordManager().check(user.password,
'%s%s' % (kwargs['password'], user.salt)):
return True
except TypeError:
pass
request = get_current_request()
fallback_auth = request.registry.settings.get('apex.fallback_auth')
if fallback_auth:
resolver = DottedNameResolver(fallback_auth.split('.', 1)[0])
fallback = resolver.resolve(fallback_auth)
return fallback().check(DBSession, request, user, \
kwargs['password'])
return False
landmines_df.longitude,
landmines_df.latitude))
# Drop the lat/lng column
landmines_gdf = landmines_gdf.drop(['latitude', 'longitude'], axis=1)
# Reset the data frame's index
landmines_gdf = landmines_gdf.reset_index(drop=True)
# add to database
landmines_gdf.to_postgis(con=engine,
name='incidents',
if_exists='append',
dtype={
'info': types.Text(),
'datetime': types.DateTime(),
'geometry': geoTypes(geometry_type='POINT',
srid=4326)
})
################################################################################
# #
# upload the imagery #
# #
################################################################################
# list of imagey
imagery_paths = [os.path.join(figures_dir_path, 'kandahar-compressed.tif')]
# init imagery dict
imagery_dict = []
class AuthID(Base):
""" Table name: auth_id
::
id = Column(types.Integer(), primary_key=True)
display_name = Column(Unicode(80), default=u'')
active = Column(types.Enum(u'Y',u'N',u'D', name=u"active"), default=u'Y')
created = Column(types.DateTime(), default=func.now())
"""
__tablename__ = 'auth_id'
__table_args__ = {"sqlite_autoincrement": True}
id = Column(types.Integer(), primary_key=True)
display_name = Column(Unicode(80), default=u'')
active = Column(types.Enum(u'Y',u'N',u'D', name=u"active"), default=u'Y')
created = Column(types.DateTime(), default=func.now())
groups = relationship('AuthGroup', secondary=auth_group_table, \
backref='auth_users')
users = relationship('AuthUser')
"""
Fix this to use association_proxy
groups = association_proxy('auth_group_table', 'authgroup')
"""
last_login = relationship('AuthUserLog', \
order_by='AuthUserLog.id.desc()')
login_log = relationship('AuthUserLog', \
order_by='AuthUserLog.id')
def in_group(self, group):
"""
Returns True or False if the user is or isn't in the group.
"""
return group in [g.name for g in self.groups]
@classmethod
def get_by_id(cls, id):
"""
Returns AuthID object or None by id
.. code-block:: python
from apex.models import AuthID
user = AuthID.get_by_id(1)
"""
return DBSession.query(cls).filter(cls.id==id).first()
def get_profile(self, request=None):
"""
Returns AuthUser.profile object, creates record if it doesn't exist.
.. code-block:: python
from apex.models import AuthUser
user = AuthUser.get_by_id(1)
profile = user.get_profile(request)
in **development.ini**
.. code-block:: python
apex.auth_profile =
"""
if not request:
request = get_current_request()
auth_profile = request.registry.settings.get('apex.auth_profile')
if auth_profile:
resolver = DottedNameResolver(auth_profile.split('.')[0])
profile_cls = resolver.resolve(auth_profile)
return get_or_create(DBSession, profile_cls, user_id=self.id)
@property
def group_list(self):
group_list = []
if self.groups:
for group in self.groups:
group_list.append(group.name)
return ','.join( map( str, group_list ) )
class AbstractModel(Base):
__abstract__ = True
date_created = schema.Column(types.DateTime(timezone=True),
default=get_utc_now)
class User(db.Model, TimestampMixin, UserMixin):
"""User model class"""
__tablename__ = 'users'
id = db.Column(types.Integer, primary_key=True)
username = db.Column(types.String(50), unique=True, nullable=False)
email = db.Column(types.String(50), unique=True, nullable=False)
_password = db.Column('password', types.String(64), nullable=False)
is_active = db.Column(types.Boolean, nullable=False, default=True)
last_login = db.Column(types.DateTime(timezone=True),
onupdate=datetime.utcnow())
def __init__(self, username, email, password=None, **kwargs):
"""Create instance."""
db.Model.__init__(self, username=username, email=email, **kwargs)
if password:
self.set_password(password)
else:
self.password = None
def __repr__(self):
return '<User {0}>'.format(self.username)
def is_authenticated(self):
return True
def is_active(self):
return self.is_active
def is_anonymous(self):
return False
def get_id(self):
return str(self.id)
def _get_password(self):
return self._password
def _set_password(self, password):
self._password = generate_password_hash(password)
# Hide password encryption by exposing password field only.
password = orm.synonym('_password',
descriptor=property(_get_password, _set_password))
def check_password(self, password):
if self.password is None:
return False
return check_password_hash(self.password, password)
@classmethod
def search(cls, keywords):
criteria = []
for keyword in keywords.split():
keyword = '%' + keyword + '%'
criteria.append(
types.or_(
User.name.ilike(keyword),
User.email.ilike(keyword),
))
q = reduce(types.and_, criteria)
return cls.query.filter(q)
def test_should_datetime_convert_string():
assert_column_conversion(types.DateTime(), graphene.String)
class Flight(Base):
__tablename__ = 'flights'
id = Column(Integer, primary_key=True)
hexident = Column(String(6), nullable=False)
callsign = Column(String(10))
# gen_date_time timestamp of the first ADSb message of this hexiden processed
first_seen = Column(types.DateTime(timezone=True), nullable=False)
# gen_date_time timestamp of (any) last ADSb message of this hexident
last_seen = Column(types.DateTime(timezone=True))
# https://gis.stackexchange.com/questions/4467/how-to-handle-time-in-gis
# flightpath = Column(Geometry('LINESTRINGZ', srid=SRID, dimension=3))
intention = Column(Enum(Intention), default=Intention.unknown)
#https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete
positions: Position = relationship('Position',
backref=backref('flight', lazy=True),
passive_deletes=True,
order_by="asc(Position.time)")
def __init__(self, hexident: string):
self.hexident = hexident
self.squawk = None
self.__flightpath = []
self.__times = []
self._transmission_type_count = dict.fromkeys(range(1, 9, 1), 0)
self._on_landing_subscribers = []
self._on_takeoff_subscribers = []
self._onground = None
self._last_event = datetime.datetime.now(datetime.timezone.utc)
def __str__(self):
return "Flight {hexident}: last seen: {last_seen}".format(
**self.__dict__)
@property
def age(self) -> datetime.timedelta:
"""
Computes the age in seconds since last seen.
:return: Age in seconds since last seen
"""
return datetime.datetime.now(datetime.timezone.utc) - self.last_seen
@property
def interpolated_track(self):
"""Compute flight heading from last known 2 positions."""
if len(self.positions) >= 2:
return interpolate_track(self.positions[-2:])
else:
return None
def update(self, adsb: adsb_parser.AdsbMessage):
"""
Updates the instance attributes with values from an ADSb message object and returns.
MSG types and contained info:
- 1: callsign & onground
- 2: speed & latitude & longitude & onground
- 3: altitude & latitude & longitude
- 4: speed & track & verticalrate & onground
- 5: altitude OR altitude & vertical_rate OR altitude & speed & track
- 6: (speed & track) (verticalrate) squawk & alert & emergency & spi
- 7: altitude
- 8: onground
:param adsb: Instance of AdsbMessage
:returns Updated version of self
"""
# Upon landing MSG type changes from 3 to 2 (no altitude is transmitted after landing)
MSG_FIELDS = {
1: ('callsign', 'onground'),
2: ('speed', 'latitude', 'longitude', 'onground'),
3: ('altitude', 'latitude', 'longitude', 'onground'),
4: ('speed', 'track', 'verticalrate', 'onground'),
5: ('altitude', 'verticalrate'),
8: ('onground', )
}
if adsb.hexident != self.hexident:
log.error(
"Trying to update flight '{}' with ADSb message of flight '{}'"
.format(self.hexident, adsb.hexident))
return self
self._transmission_type_count[adsb.transmission_type] += 1
if not self.first_seen:
self.first_seen = adsb.gen_date_time
# Note: last_seen timestamp gets updated from any MSG type, regardless whether the message content will be used
# to update the object attributes or not.
self.last_seen = adsb.gen_date_time
# Process only message types defined as keys in MSG_FIELDS
try:
log.debug("Updating flight {} with MSG type: {}".format(
self.hexident, adsb.transmission_type))
for field in MSG_FIELDS[adsb.transmission_type]:
setattr(self, field, getattr(adsb, field))
log.debug("Updating field: {}={}".format(
field, getattr(adsb, field)))
except KeyError:
log.debug(
"Skipping updating flight with transmission type {:d}: {}".
format(adsb.transmission_type, adsb))
# Update flight path geometry only if msg includes coordinates
# ATTENTION: x: longitude (easting), y: latitude (northing)
if adsb.transmission_type == 3:
if adsb.longitude is not None and adsb.latitude is not None and adsb.altitude is not None:
position = Position(time=adsb.gen_date_time,
coordinates=from_shape(Point(
adsb.longitude, adsb.latitude,
feet2m(adsb.altitude)),
srid=SRID),
onground=adsb.onground)
self.positions.append(position)
self.update_onground(adsb.onground)
else:
log.debug(
"Cannot update position as MSG3 did not include lon/lat: {}"
.format(str(adsb)))
# First MSG2 of aircraft at terminal does not contain coordinates, only 'onground'
# Also, the altitude is not included in MSG2, and is being set here to GND_ALTITUDE (0m AGL)
elif adsb.transmission_type == 2 and adsb.longitude is not None and adsb.latitude is not None:
self.positions.append(
Position(time=adsb.gen_date_time,
coordinates=from_shape(Point(adsb.longitude,
adsb.latitude,
GND_ALTITUDE),
srid=SRID),
onground=adsb.onground))
self.update_onground(adsb.onground)
return self
def update_onground(self, onground):
"""Flip the onground attribute of this flight and broadcast event."""
if self._onground is None: # First position of new flight
self._onground = onground
elif self._onground and self._onground != onground: # takeoff
self._onground = onground
self._broadcast_takeoff(self.positions[-1])
elif not self._onground and self._onground != onground: # landing
self._onground = onground
self._broadcast_landing(self.positions[-1])
def register_on_landing(self, subscriber):
"""Register an on-landing subscriber."""
self._on_landing_subscribers.append(subscriber)
def register_on_takeoff(self, subscriber):
"""Register an on-takeoff subscriber."""
self._on_takeoff_subscribers.append(subscriber)
def _broadcast_landing(self, position):
"""Call the callback of landing subscribers."""
if self._valid_event(position):
for subscriber in self._on_landing_subscribers:
subscriber(position, self)
else:
log.warning(
"Suppressing landing event broadcast for flight {} at {}".
format(position.flight_id, position.time))
def _broadcast_takeoff(self, position):
"""Call the callback of takeoff subscribers."""
if self._valid_event(position):
for subscriber in self._on_takeoff_subscribers:
subscriber(position, self)
else:
log.warning(
"Suppressing landing event broadcast for flight {} at {}".
format(position.flight_id, position.time))
def _valid_event(self, position):
"""Return true if the identified event (landing or takeoff) lies sufficiently apart from previous event.
Sometimes a landing airplane 'bounces' off the runway triggering several flips between onground statuses.
This leads to identification of several landing and takeoff events within 1-2sec. To prevent this, the position
time stamp of the current event is compared to any previous event, and if the time difference is below a short
threshold the new event should be discarded In this case False is returned by this method.
The new event timestamp overwrites the last event timestamp, as the time difference between the bounces matters,
and not between first and any following bounce.
"""
valid = position.time - self._last_event > datetime.timedelta(
seconds=2.0)
self._last_event = position.time
return valid
"""샘플쿼리 모듈
:filename: - query.py
:modified: - 2017.08.24
:note: - 이 모듈에서는 자주사용하는 샘플쿼리를 미리 정의함
"""
'''모듈 불러오기'''
from sqlalchemy import types #ALCHEMY for engine
'''쿼리 인스턴스 임포트'''
#기본상품정보조회
#컬럼 데이터타입
basic_col = \
{'ISIN_NO' :types.NVARCHAR(length=50),
'STD_DATE' :types.DateTime(),
'FIRST_AMT' :types.BigInteger(),
'REMAIN_AMT' :types.BigInteger(),
'EFF_DATE' :types.DateTime(),
'MAT_DATE' :types.DateTime(),
'PRSV_RATE' :types.Float()}
#쿼리문
basic_sql = \
(
"select ISIN_NO,"
" to_date(STD_DATE,'yyyymmdd') STD_DATE, "
" FIRST_AMT, REMAIN_AMT, "
" EFF_DATE, MAT_DATE, PRSV_RATE "
"from "
"( "
" select tblLATEST.ISIN_NO, " #ISIN번호
" greatest(tblLATEST.STND_DATE, nvl(tblREFUND.STND_DATE,0)) STD_DATE, " #처리일자
" tblLATEST.FIRST_AMT/1000000 FIRST_AMT, " #최초발행금액 (백만원)
class Content(AutoTimestampMixin, BaseContentModel):
"""
Content
"""
URI_TYPE = UriType.PRIMARY
_title = Column(types.String(512))
_author_names = Column(types.String(128)) # Model TODO: make 256
# TODO: split out ContentPublication as 1:1?
_publication = Column(types.String(64)) # e.g. Nature
_published_timestamp = Column(types.DateTime(), index=True) # UTC
_granularity_published = Column(types.SmallInteger())
_tzinfo_published = Column(types.String(64))
publisher = Column(types.String(64)) # e.g. Macmillan Science & Education
summary = Column(types.Text())
full_text = Column(types.Text())
# problems
# orgs
# geos
# communities
# ratings
# comments
__table_args__ = (
Index(
'ux_content',
# ux for unique index
'_title',
'_author_names',
'_publication',
'_published_timestamp',
unique=True), )
Key = namedtuple(
'ContentKey', # add publisher?
'title, author_names, publication, published_timestamp')
@classmethod
def create_key(cls, title, author_names, publication, published_timestamp,
**kwds):
"""Create Trackable key"""
lowered_title = title.lower()
normalized_authors = cls.normalize_author_names(author_names)
flex_dt = FlexTime.cast(published_timestamp)
dt_utc = flex_dt.astimezone(UTC).deflex(native=True)
return cls.Key(lowered_title, normalized_authors, publication, dt_utc)
def derive_key(self, **kwds):
"""Derive Trackable key from instance"""
return self.model_class.Key(
self.title.lower(), self.author_names, self.publication,
self.published_timestamp.astimezone(UTC).deflex(native=True))
@property
def title(self):
return self._title.capitalize() if self._title else self._title
@title.setter
def title(self, val):
if val is None:
raise ValueError('Cannot be set to None')
# During __init__()
if self._title is None:
self._title = val.lower()
return
# Not during __init__()
key = self.model_class.create_key(
val, self.author_names, self.publication,
self.published_timestamp.astimezone(UTC))
self.register_update(key)
title = orm.synonym('_title', descriptor=title)
@property
def author_names(self):
return self._author_names
@author_names.setter
def author_names(self, val):
if val is None:
raise ValueError('Cannot be set to None')
normalized_val = self.normalize_author_names(val)
# During __init__()
if self._author_names is None:
self._author_names = normalized_val
return
# Not during __init__()
key = self.model_class.create_key(
self.title, normalized_val, self.publication,
self.published_timestamp.astimezone(UTC))
self.register_update(key)
author_names = orm.synonym('_author_names', descriptor=author_names)
@classmethod
def normalize_author_names(cls, author_names):
author_name_list = [
cls.normalize_author_name(an) for an in author_names.split(';')
]
return '; '.join(author_name_list)
@classmethod
def normalize_author_name(cls, author_name):
author_name = author_name.strip()
author_name_components = [c.strip() for c in author_name.split(',')]
credential = None
if (len(author_name_components) > 1
and cls.is_credential(author_name_components[-1])):
credential = author_name_components[-1]
author_name_components = author_name_components[:-1]
if len(author_name_components) == 1:
author_name_components = [
c.strip() for c in author_name_components[0].split()
]
if (len(author_name_components) > 1
and cls.is_credential(author_name_components[-1])):
credential = author_name_components[-1]
author_name_components = author_name_components[:-1]
author_name = ' '.join(author_name_components)
elif len(author_name_components) == 2:
last_name, first_name = author_name_components
author_name = ' '.join((first_name.strip(), last_name.strip()))
else:
raise ValueError('Invalid author name')
author_name = author_name.title()
if credential:
author_name = ', '.join((author_name, credential))
return author_name
@classmethod
def is_credential(cls, component):
return (component.upper() == component or component == 'PhD')
@property
def publication(self):
return self._publication
@publication.setter
def publication(self, val):
if val is None:
val = ''
# During __init__()
if self._publication is None:
self._publication = val
return
# Not during __init__()
key = self.model_class.create_key(
self.title, self.author_names, val,
self.published_timestamp.astimezone(UTC))
self.register_update(key)
publication = orm.synonym('_publication', descriptor=publication)
@property
def published_timestamp(self):
flex_dt = FlexTime.instance(self._published_timestamp)
localized = flex_dt.astimezone(self.tzinfo_published)
return FlexTime.instance(localized,
granularity=self.granularity_published,
truncate=True)
@published_timestamp.setter
def published_timestamp(self, val):
"""Set published timestamp given FlexTime datetime or TimestampInfo"""
self.set_published_timestamp_info(val)
published_timestamp = orm.synonym('_published_timestamp',
descriptor=published_timestamp)
@property
def granularity_published(self):
return FlexTime.Granularity(self._granularity_published)
granularity_published = orm.synonym('_granularity_published',
descriptor=granularity_published)
@property
def tzinfo_published(self):
return self._tzinfo_published
tzinfo_published = orm.synonym('_tzinfo_published',
descriptor=tzinfo_published)
@property
def published_timestamp_info(self):
"""Get publication datetime info namedtuple"""
return self.published_timestamp.info
jsonified_published_timestamp_info = JsonProperty(
name='published_timestamp_info', after='tzinfo_published')
def set_published_timestamp_info(self, dt, granularity=None, geo=None):
"""
Set publication timestamp info
Set all fields related to published timestamp. This is the only
method that actually sets the private member variables.
Content may be published with varying levels of granularity,
ranging from year to microseconds. This is supported by the
FlexTime factory class which endows datetime instances with
granularity and info members.
I/O:
dt: FlexTime or regular datetime instance or DatetimeInfo tuple
granularity=None: FlexTime granularity or associated int value
geo=None: geo where the content was originally published
"""
# TODO: if geo and dt is naive, set timezone based on geo
flex_dt = FlexTime.cast(dt, granularity)
granularity, info = flex_dt.granularity, flex_dt.info
now = pendulum.now(UTC)
if flex_dt > now:
raise ValueError('Publication date may not be in the future')
# During __init__()
if self._published_timestamp is None:
self._published_timestamp = (flex_dt.astimezone(UTC).deflex(
native=True))
else: # Not during __init__()
key = self.model_class.create_key(self.title, self.author_names,
self.publication, flex_dt)
self.register_update(key)
self._granularity_published = granularity.value
self._tzinfo_published = info.tzinfo
def __init__(self,
title,
author_names,
publication,
published_timestamp,
granularity_published=None,
geo=None,
publisher=None,
summary=None,
full_text=None):
self.set_published_timestamp_info(published_timestamp,
granularity_published, geo)
self.title = title
self.author_names = author_names
self.publication = publication
self.publisher = publisher
self.summary = summary
self.full_text = full_text
def load_dialect_impl(self, dialect):
if dialect.name == "sqlite":
return dialect.type_descriptor(CHAR(25))
else:
return dialect.type_descriptor(types.DateTime())
def random_string():
import string
import random
return ''.join(random.choice(string.ascii_uppercase + string.digits) for x in range(12))
metadata = schema.MetaData()
tags_table = schema.Table('tags', metadata,
schema.Column('id', types.Integer, primary_key=True),
#schema.Column('image_id', types.Integer, nullable=False),
schema.Column('tag', types.Unicode(255), nullable=False, unique=True),
#schema.Column('relevancy', types.Integer, default=0),
schema.Column('created_at', types.DateTime(), default=now()),
)
image_table = schema.Table('images', metadata,
schema.Column('id', types.Integer, primary_key=True), #this would be crc32 of absolute url of the image
schema.Column('image_holder', types.Unicode(255)), #web page where image is. also written as 'identifier' at some places
schema.Column('image', types.Unicode(255), nullable=False), #absolute url of image
schema.Column('width', types.Integer),
schema.Column('height', types.Integer),
schema.Column('rights', types.Unicode(255)),
schema.Column('creator', types.Unicode(255)),
schema.Column('source', types.Unicode(255)),
schema.Column('created_at', types.DateTime(), default=now()),
)
relation_table = schema.Table('relation', metadata,
def test_should_datetime_convert_datetime():
assert get_field(types.DateTime()).type == DateTime
class utcnow(expression.FunctionElement):
type = types.DateTime()
class Allocation(TimestampMixin, ORMBase, OtherModels):
"""Describes a timespan within which one or many timeslots can be
reserved.
There's an important concept to understand before working with allocations.
The resource uuid of an alloction is not always pointing to the actual
resource.
A resource may in fact be a real resource, or an imaginary resource with
a uuid derived from the real resource. This is a somewhat historical
artifact.
If you need to know which allocations belong to a real resource, the
mirror_of field is what's relevant. The originally created allocation
with the real_resource is also called the master-allocation and it is
the one allocation with mirror_of and resource being equal.
When in doubt look at the managed_* functions of seantis.reservation.db's
Scheduler class.
"""
__tablename__ = 'allocations'
id = Column(types.Integer(), primary_key=True, autoincrement=True)
resource = Column(customtypes.GUID(), nullable=False)
mirror_of = Column(customtypes.GUID(), nullable=False)
group = Column(customtypes.GUID(), nullable=False)
quota = Column(types.Integer(), default=1)
partly_available = Column(types.Boolean(), default=False)
approve_manually = Column(types.Boolean(), default=False)
reservation_quota_limit = Column(
types.Integer(), default=0, nullable=False
)
# The dates are stored without any timzone information (unaware).
# Therefore the times are implicitly stored in the timezone the resource
# resides in.
# This is fine and dandy as long as all resources are in the same timezone.
# If they are not problems arise. So in the future the resource should
# carry a timezone property which is applied to the dates which will then
# be stored in UTC
# => TODO
_start = Column(types.DateTime(), nullable=False)
_end = Column(types.DateTime(), nullable=False)
_raster = Column(types.Integer(), nullable=False)
recurrence_id = Column(types.Integer(),
ForeignKey('recurrences.id',
onupdate='cascade',
ondelete='cascade'))
recurrence = relation('Recurrence', lazy='joined')
__table_args__ = (
Index('mirror_resource_ix', 'mirror_of', 'resource'),
UniqueConstraint('resource', '_start', name='resource_start_ix')
)
def copy(self):
allocation = Allocation()
allocation.resource = self.resource
allocation.mirror_of = self.mirror_of
allocation.group = self.group
allocation.quota = self.quota
allocation.partly_available = self.partly_available
allocation.approve_manually = self.approve_manually
allocation._start = self._start
allocation._end = self._end
allocation._raster = self._raster
allocation.recurrence_id = self.recurrence_id
return allocation
def get_start(self):
return self._start
def set_start(self, start):
self._start = rasterize_start(start, self.raster)
start = property(get_start, set_start)
def get_end(self):
return self._end
def set_end(self, end):
self._end = rasterize_end(end, self.raster)
end = property(get_end, set_end)
def get_raster(self):
return self._raster
def set_raster(self, raster):
# the raster can only be set once!
assert(not self._raster)
self._raster = raster
raster = property(get_raster, set_raster)
@property
def display_start(self):
"""Does nothing but to form a nice pair to display_end."""
return self.start
@property
def display_end(self):
"""Returns the end plus one microsecond (nicer display)."""
return self.end + timedelta(microseconds=1)
@property
def whole_day(self):
"""True if the allocation is a whole-day allocation.
A whole-day allocation is not really special. It's just an allocation
which starts at 0:00 and ends at 24:00 (or 23:59:59'999).
As such it can actually also span multiple days, only hours and minutes
count.
The use of this is to display allocations spanning days differently.
"""
s, e = self.display_start, self.display_end
assert s != e # this can never be, except when caused by cosmic rays
return utils.whole_day(s, e)
def overlaps(self, start, end):
""" Returns true if the current timespan overlaps with the given
start and end date.
"""
start, end = rasterize_span(start, end, self.raster)
return utils.overlaps(start, end, self.start, self.end)
def contains(self, start, end):
""" Returns true if the current timespan contains the given start
and end date.
"""
start, end = rasterize_span(start, end, self.raster)
return self.start <= start and end <= self.end
def free_slots(self, start=None, end=None):
""" Returns the slots which are not yet reserved."""
reserved = [slot.start for slot in self.reserved_slots]
slots = []
for start, end in self.all_slots(start, end):
if not start in reserved:
slots.append((start, end))
return slots
def align_dates(self, start=None, end=None):
""" Aligns the given dates to the start and end date of the
allocation.
"""
start = start or self.start
start = start < self.start and self.start or start
end = end or self.end
end = end > self.end and self.end or end
return start, end
def all_slots(self, start=None, end=None):
""" Returns the slots which exist with this timespan. Reserved or free.
"""
start, end = self.align_dates(start, end)
if self.partly_available:
for start, end in iterate_span(start, end, self.raster):
yield start, end
else:
yield self.start, self.end
def is_available(self, start=None, end=None):
""" Returns true if the given daterange is completely available. """
if not (start and end):
start, end = self.start, self.end
assert(self.overlaps(start, end))
if self.is_blocked(start, end):
return False
reserved = [slot.start for slot in self.reserved_slots]
for start, end in self.all_slots(start, end):
if start in reserved:
return False
return True
def is_blocked(self, start=None, end=None):
if not (start and end):
start, end = self.start, self.end
else:
start, end = utils.as_machine_date(start, end)
BlockedPeriod = self.models.BlockedPeriod
query = self._query_blocked_periods()
query = query.filter(BlockedPeriod.start <= end)
query = query.filter(BlockedPeriod.end >= start)
return query.first() is not None
def _query_blocked_periods(self):
query = Session.query(self.models.BlockedPeriod)
query = query.filter_by(resource=self.resource)
return query
@property
def pending_reservations(self):
""" Returns the pending reservations query for this allocation.
As the pending reservations target the group and not a specific
allocation this function returns the same value for masters and
mirrors.
"""
Reservation = self.models.Reservation
query = Session.query(Reservation.id)
query = query.filter(Reservation.target == self.group)
query = query.filter(Reservation.status == u'pending')
return query
@property
def waitinglist_length(self):
return self.pending_reservations.count()
@property
def availability(self):
"""Returns the availability in percent."""
if self.partly_available:
total = sum(1 for s in self.all_slots())
else:
total = 1
count = len(self.reserved_slots)
for blocked_period in self._query_blocked_periods():
count += len(list(iterate_span(blocked_period.start,
blocked_period.end,
self.raster)))
if total == count:
return 0.0
if count == 0:
return 100.0
return 100.0 - (float(count) / float(total) * 100.0)
@property
def in_group(self):
"""True if the event is in any group."""
query = Session.query(Allocation.id)
query = query.filter(Allocation.resource == self.resource)
query = query.filter(Allocation.group == self.group)
query = query.limit(2)
return len(query.all()) > 1
@property
def in_recurrence(self):
"""True if the event is attached to a recurrence."""
return self.recurrence_id is not None
@property
def is_separate(self):
"""True if available separately (as opposed to available only as
part of a group)."""
if self.partly_available:
return True
if self.in_group:
return False
return True
def availability_partitions(self, scheduler):
"""Partitions the space between start and end into blocks of either
free, blocked or reserved time. Each block has a percentage
representing the space the block occupies compared to the size of the
whole allocation.
The blocks are ordered from start to end. Each block is an item with
two values. The first being the percentage, the second being the type.
The type can be one of None, 'reserved' or 'blocked'.
So given an allocation that goes from 8 to 9 and a reservation that
goes from 8:15 until 8:30 and a block that goes from 8:30 to 9:00
we get the following blocks:
[
(25%, None),
(25%, 'reserved'),
(50%, 'blocked')
]
This is useful to divide an allocation block into different divs on the
frontend, indicating to the user which parts of an allocation are
available for reservation.
Makes sure to only display slots that are within it's resources
first_hour/last_hour timespan.
"""
resource = get_resource_by_uuid(scheduler.uuid).getObject()
min_start_resource = datetime.combine(self.start,
time(resource.first_hour))
max_end_resource = datetime.combine(self.end,
time(resource.last_hour))
display_start = max(min_start_resource, self.start)
display_end = min(max_end_resource, self.end)
reserved = dict((r.start, r) for r in self.reserved_slots if
r.start >= display_start and r.end <= display_end)
blocked = set()
for blocked_period in self._query_blocked_periods():
blocked.update(start for start, end in
iterate_span(max(blocked_period.start,
display_start),
min(blocked_period.end,
display_end),
self.raster))
if not (reserved or blocked):
return [(100.0, None)]
# Get the percentage one slot represents
slots = list(self.all_slots(display_start, display_end))
step = 100.0 / float(len(slots))
# Create an entry for each slot with either True or False
pieces = []
for slot in slots:
piece = None
if slot[0] in reserved:
reserved_slot = reserved[slot[0]]
token = reserved_slot.reservation_token
reservation = scheduler.reservation_by_token(token).one()
piece = ('reserved', reservation.description, reservation.id)
elif slot[0] in blocked:
piece = ('blocked', None)
pieces.append(piece)
# Group by the None/'reserved'/'blocked' values in the pieces and sum
# up the percentage
partitions = []
for flag, group in groupby(pieces, key=lambda p: p):
percentage = len(list(group)) * step
partitions.append([percentage, flag])
# Make sure to get rid of floating point rounding errors
total = sum([p[0] for p in partitions])
diff = 100.0 - total
partitions[-1:][0][0] -= diff
return partitions
@property
def is_transient(self):
"""True if the allocation does not exist in the database, and is not
about to be written to the database. If an allocation is transient it
means that the given instance only exists in memory.
See:
http://www.sqlalchemy.org/docs/orm/session.html
#quickie-intro-to-object-states
http://stackoverflow.com/questions/3885601/
sqlalchemy-get-object-instance-state
"""
return object_session(self) is None and not has_identity(self)
@property
def is_master(self):
"""True if the allocation is a master allocation."""
return self.resource == self.mirror_of
def siblings(self, imaginary=True):
"""Returns the master/mirrors group this allocation is part of.
If 'imaginary' is true, inexistant mirrors are created on the fly.
those mirrors are transient (see self.is_transient)
"""
# this function should always have itself in the result
if not imaginary and self.is_transient:
assert False, \
'the resulting list would not contain this allocation'
if self.quota == 1:
assert(self.is_master)
return [self]
query = Session.query(Allocation)
query = query.filter(Allocation.mirror_of == self.mirror_of)
query = query.filter(Allocation._start == self._start)
existing = dict(((e.resource, e) for e in query))
master = self.is_master and self or existing[self.mirror_of]
existing[master.resource] = master
uuids = utils.generate_uuids(master.resource, master.quota)
imaginary = imaginary and (master.quota - len(existing)) or 0
siblings = [master]
for uuid in uuids:
if uuid in existing:
siblings.append(existing[uuid])
elif imaginary > 0:
allocation = master.copy()
allocation.resource = uuid
siblings.append(allocation)
imaginary -= 1
return siblings
df1 = df_volume[df_volume['TEMP'].str.isnumeric()]
df2 = df_volume[df_volume['TEMP'].str.isnumeric() == False]
df1['TEMP'] = df1['TEMP'].apply(np.int64)
df1['AMOUNT'] = df1['AMOUNT'] * df1['TEMP']
df_volume = pd.concat([df1, df2])
df_volume.drop('TEMP', axis=1, inplace=True)
df_combined = pd.concat([df, df_volume])
print(df_combined)
print('start importing...')
df_combined.to_sql('data',
con=engine,
if_exists='replace',
index=False,
dtype={
'DATE': t.DateTime(),
'AMOUNT': t.FLOAT(),
'TC I': t.NVARCHAR(length=200),
'TC II': t.NVARCHAR(length=200),
'TC III': t.NVARCHAR(length=200),
'TC IV': t.NVARCHAR(length=200),
'MOLECULE': t.NVARCHAR(length=200),
'PRODUCT': t.NVARCHAR(length=200),
'PACKAGE': t.NVARCHAR(length=200),
'CORPORATION': t.NVARCHAR(length=200),
'MANUF_TYPE': t.NVARCHAR(length=20),
'FORMULATION': t.NVARCHAR(length=50),
'STRENGTH': t.NVARCHAR(length=20),
'UNIT': t.NVARCHAR(length=25),
'PERIOD': t.NVARCHAR(length=3),
'MOLECULE_TC': t.NVARCHAR(length=255),
