class Attempt(Base):
__tablename__ = 'Attempt'
__table_args__ = (schema.Index('name_group_test_date', 'first_name',
'middle_name', 'last_name', 'group',
'testsuite_id', 'date'), {
'mysql_engine': 'InnoDB'
})
id = schema.Column(types.Integer, primary_key=True)
first_name = schema.Column(types.Unicode(255), nullable=False)
middle_name = schema.Column(types.Unicode(255), nullable=False)
last_name = schema.Column(types.Unicode(255), nullable=False)
group = schema.Column(types.Unicode(255), nullable=False)
testsuite_id = schema.Column(types.Integer,
schema.ForeignKey('TestSuite.id',
ondelete='SET NULL'),
nullable=True)
testsuite = orm.relationship(TestSuite)
test = schema.Column(types.Binary, nullable=False)
date = schema.Column(types.DateTime,
default=datetime.datetime.now,
nullable=False)
is_attempted = schema.Column(types.Boolean, default=False, nullable=False)
is_attempted_correct = schema.Column(types.Boolean,
default=False,
nullable=False)
result = schema.Column(types.Binary, nullable=True)
class Movie(Base, ElasticMixin):
__tablename__ = 'movies'
id = Column(types.String(40), primary_key=True)
title = Column(types.Unicode(40))
director = Column(types.Unicode(40))
year = Column(types.Integer)
rating = Column(types.Numeric)
genre_id = Column(None, ForeignKey('genres.id'))
genre = orm.relationship('Genre')
__elastic_parent__ = ('Genre', 'genre_id')
def __init__(self, *args, **kwargs):
Base.__init__(self, *args, **kwargs)
self.id = sha1(self.title.encode('utf-8')).hexdigest()
@property
def genre_title(self):
return self.genre and self.genre.title or ''
@classmethod
def elastic_mapping(cls):
return ESMapping(analyzer='content',
properties=ESMapping(
ESString('title', boost=5.0),
ESString('director'), ESField('year'),
ESField('rating'),
ESString('genre_title', analyzer='lowercase')))
class OhayouTime(Base):
__tablename__ = 'ohayouTime'
id = Column(types.Integer, primary_key=True)
user = Column(types.Unicode(32))
type = Column(types.Unicode(32))
datetime = Column(types.DateTime, default=datetime.datetime.now())
def __init__(self, metadata):
self._metadata = metadata
self.drupal_comments_table = Table(
'opendata_package_v',
self._metadata,
Column(
'changed',
types.UnicodeText,
primary_key=True,
nullable=False
),
Column('name', types.Unicode(60)),
Column('thread', types.Unicode(255)),
Column('comment_body_value', types.UnicodeText),
Column('language', types.Unicode(12)),
Column('pkg_id', types.UnicodeText)
)
self.drupal_comments_count_table = Table(
'opendata_package_count_v',
self._metadata,
Column('count', types.Integer),
Column('pkg_id', types.UnicodeText)
)
self.drupal_ratings_table = Table(
'opendata_package_rating_v',
self._metadata,
Column('rating', types.Float),
Column('pkg_id', types.UnicodeText)
)
def init_users_table(metadata):
return Table('users', metadata,
Column('id', types.Integer, primary_key=True),
Column('email', types.Unicode(255)),
Column('password', types.Unicode(255)),
Column('active', types.Boolean)
)
def create_sa_proxies(self):
# create the table and mapper
metadata = schema.MetaData()
user_table = schema.Table(
'user', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('first_name', types.Unicode(25)),
schema.Column('last_name', types.Unicode(25)))
class User(object):
pass
orm.mapper(User, user_table)
# create the session
engine = create_engine('sqlite:///:memory:')
metadata.bind = engine
metadata.create_all()
session = orm.sessionmaker(bind=engine)()
# add some dummy data
user_table.insert().execute([{
'first_name': 'Jonathan',
'last_name': 'LaCour'
}, {
'first_name': 'Yoann',
'last_name': 'Roman'
}])
# get the SA objects
self.sa_object = session.query(User).first()
select = user_table.select()
self.result_proxy = select.execute()
self.row_proxy = select.execute().fetchone()
def _fixed_lookup_fixture(self):
return [
(sqltypes.String(), sqltypes.VARCHAR()),
(sqltypes.String(1), sqltypes.VARCHAR(1)),
(sqltypes.String(3), sqltypes.VARCHAR(3)),
(sqltypes.Text(), sqltypes.TEXT()),
(sqltypes.Unicode(), sqltypes.VARCHAR()),
(sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
(sqltypes.UnicodeText(), sqltypes.TEXT()),
(sqltypes.CHAR(3), sqltypes.CHAR(3)),
(sqltypes.NUMERIC, sqltypes.NUMERIC()),
(sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.Numeric, sqltypes.NUMERIC()),
(sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.DECIMAL, sqltypes.DECIMAL()),
(sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
(sqltypes.INTEGER, sqltypes.INTEGER()),
(sqltypes.BIGINT, sqltypes.BIGINT()),
(sqltypes.Float, sqltypes.FLOAT()),
(sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
(sqltypes.DATETIME, sqltypes.DATETIME()),
(sqltypes.DateTime, sqltypes.DATETIME()),
(sqltypes.DateTime(), sqltypes.DATETIME()),
(sqltypes.DATE, sqltypes.DATE()),
(sqltypes.Date, sqltypes.DATE()),
(sqltypes.TIME, sqltypes.TIME()),
(sqltypes.Time, sqltypes.TIME()),
(sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
(sqltypes.Boolean, sqltypes.BOOLEAN()),
]
class User(Base):
"""
Application's user model. Use this if you want to store / manage your own user auth
"""
__tablename__ = 'users'
user_id = Column(types.Integer, autoincrement=True, primary_key=True)
name = Column(types.Unicode(60))
email = Column(types.Unicode(60), unique=True)
_password = Column('password', types.Unicode(60))
@property
def password(self):
return self._password
@password.setter
def password(self, password):
self._password = hash_password(password)
def __init__(self, email, password, name):
self.name = name
self.email = email
self.password = password
@classmethod
def get_by_username(cls, email):
return _DBSESSION.query(cls).filter(cls.email == email).first()
@classmethod
def check_password(cls, email, password):
user = cls.get_by_username(email)
if not user:
return False
return crypt.check(user.password, password)
def init_user_data_table(metadata):
return Table(
'user_data', metadata, Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(255)), Column('nick', types.Unicode(255)),
Column('vcs_user', types.Unicode(255)),
Column('vcs_pass', types.Unicode(255)),
Column('user_id', types.Integer, schema.ForeignKey('users.id')))
def init_rev_table(metadata):
return Table(
'revisions', metadata, Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(255), default=''),
Column('log', types.Text(), default=u'No log message'),
Column('author', types.Unicode(255), default=u'Unknown Author'),
Column('date', types.DateTime()))
def init_dirs_table(metadata):
return Table('dirs', metadata,
Column('path', types.Unicode(255), primary_key=True),
Column('name', types.Unicode(255)),
Column('root', types.Unicode(255)),
Column('rev_id', types.Integer, schema.ForeignKey('revisions.id')),
Column('in_dir', types.Unicode(255), schema.ForeignKey('dirs.path'))
)
class GrantToken(Base):
__tablename__ = 'oauth2_grant_token'
EXPIRATION_TIME = timedelta(minutes=5)
code = Column(types.Unicode(100), primary_key=True)
client_pk = Column(GUID,
ForeignKey(Client.pk,
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
user_id = 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=False)
redirect_uri = Column(types.Unicode(255), nullable=False)
client = orm.relationship(Client)
user = orm.relationship(User)
@property
def client_id(self):
return self.client_pk.hex
@property
def expires(self):
return self.expires_at.astimezone(tzutc()).replace(tzinfo=None)
def delete(self):
session = orm.session.object_session(self)
session.delete(self)
session.commit()
return self
def __init__(self, client, user, code, scopes, redirect_uri):
"""
:param client:
:type client: Client
:param user:
:type user: User
:param code:
:type code: basestring
:param scopes:
:type scopes: collections.Iterable
:keyword expires_in:
:type expires_in: datetime.timedelta
"""
self.client = client
self.user = user
self.code = code
self.scopes = scopes
self.expires_at = datetime.now(tzutc()) + self.EXPIRATION_TIME
self.redirect_uri = redirect_uri
def init_email_confirm_table(metadata):
return Table(
'email_confirm',
metadata,
Column('id', types.Integer, primary_key=True),
Column('token', types.Unicode(255)),
Column('email', types.Unicode(255)),
Column('date', types.DateTime),
)
class User(Base):
__tablename__ = "users"
id = sqlalchemy.Column(types.Integer, primary_key=True)
username = sqlalchemy.Column(types.Unicode(), unique=True, nullable=False)
password = sqlalchemy.Column(types.Unicode())
email = sqlalchemy.Column(types.Unicode())
date_joined = sqlalchemy.Column(types.DateTime())
last_login = sqlalchemy.Column(types.DateTime())
class Tweet(Base):
__tablename__ = 'tweet'
id = Column(types.Integer, primary_key=True)
user = Column(types.Unicode(32))
text = Column(types.Unicode(140))
datetime = Column(types.DateTime, default=datetime.datetime.now())
replyID = Column(types.String(64), default=-1)
isAnalyze = Column(types.SmallInteger, default=False)
class AchievementsTable(Base):
# Define the achivements description table interface
__tablename__ = "Achievements"
AchievementID = sa.Column(types.Integer, primary_key=True)
AchievementIconFileLocation = sa.Column(types.Unicode(1024))
AchievementName = sa.Column(types.Unicode(128))
AchievementDescription = sa.Column(types.Unicode(1024))
AchievementScore = sa.Column(types.Integer)
class AdminUser(Base):
"""
AdminUser - the db user entry
- we use here the same class defintion as for the user import
which will allow to place a managed resolver on top of this
"""
__tablename__ = "admin_users"
groupid = schema.Column(types.Unicode(100),
primary_key=True,
index=True)
userid = schema.Column(types.Unicode(100),
primary_key=True,
index=True)
username = schema.Column(types.Unicode(255),
default=u'',
unique=True,
index=True)
phone = schema.Column(types.Unicode(100), default=u'')
mobile = schema.Column(types.Unicode(100), default=u'')
email = schema.Column(types.Unicode(100), default=u'')
surname = schema.Column(types.Unicode(100), default=u'')
givenname = schema.Column(types.Unicode(100), default=u'')
password = schema.Column(types.Unicode(255), default=u'')
class ImportedUserSchema(db.Model):
__tablename__ = "imported_user"
__table_args__ = {
"mysql_collate": "utf8_unicode_ci",
"mysql_charset": "utf8",
}
groupid = schema.Column(types.Unicode(100), primary_key=True, index=True)
userid = schema.Column(types.Unicode(100), primary_key=True, index=True)
username = schema.Column(types.Unicode(255), default="", index=True)
phone = schema.Column(types.Unicode(100), default="")
mobile = schema.Column(types.Unicode(100), default="")
email = schema.Column(types.Unicode(100), default="")
surname = schema.Column(types.Unicode(100), default="")
givenname = schema.Column(types.Unicode(100), default="")
password = schema.Column(types.Unicode(255), default="", index=True)
class TagType(Base):
"""User added tags"""
__tablename__ = "tagtypes"
id = Column(types.Integer, primary_key=True)
text = Column(types.Unicode(500))
project_id = Column(types.Integer)
#label = Column(types.SmallInteger)
creator_id = Column(types.Integer)
color = Column(types.Unicode(50))
class Shorturl(Base):
__tablename__ = 'shorturl'
__table_args__ = {'schema': _schema + "_static"}
__acl__ = [DENY_ALL]
id = Column(types.Integer, primary_key=True)
url = Column(types.Unicode(1000))
ref = Column(types.String(20), index=True, unique=True, nullable=False)
creator_email = Column(types.Unicode(200))
creation = Column(types.DateTime)
last_hit = Column(types.DateTime)
nb_hits = Column(types.Integer)
class Product(Base):
implements(IProduct)
__tablename__ = 'products'
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode())
description = Column(types.Unicode())
price = Column(types.Float())
def __repr__(self):
return "<%s %s(%s)>" % (self.__class__.__name__, self.name, self.id)
def upgrade_1(session, metadata):
"""
Version 1 upgrade.
This upgrade adds two new fields to the songusage database
:param session: SQLAlchemy Session object
:param metadata: SQLAlchemy MetaData object
"""
op = get_upgrade_op(session)
op.add_column('songusage_data', Column('plugin_name', types.Unicode(20), server_default=''))
op.add_column('songusage_data', Column('source', types.Unicode(10), server_default=''))
class Note(Base):
"""Stores notes; both structured and unstructured"""
__tablename__ = "notes"
id = Column(types.Integer, primary_key=True)
creator_id = Column(types.Integer)
citation_id = Column(types.Integer)
general = Column(types.Unicode(1000))
population = Column(types.Unicode(1000))
ic = Column(types.Unicode(1000)) # intervention/comparator
outcome = Column(types.Unicode(1000))
def test_should_unknown_sqlalchemy_composite_raise_exception():
class CompositeClass:
def __init__(self, col1, col2):
self.col1 = col1
self.col2 = col2
re_err = "Don't know how to convert the composite field"
with pytest.raises(Exception, match=re_err):
convert_sqlalchemy_composite(
composite(CompositeFullName, (Column(types.Unicode(50)), Column(types.Unicode(50)))),
Registry(),
)
class Comment(Base):
__tablename__ = 'comments'
id = schema.Column(types.Integer,
schema.Sequence('comment_seq_id', optional=True),
primary_key=True)
pageid = schema.Column(types.Integer,
schema.ForeignKey('pages.id'),
nullable=False)
content = schema.Column(types.Text(), default=u'')
name = schema.Column(types.Unicode(255))
email = schema.Column(types.Unicode(255), nullable=False)
created = schema.Column(types.TIMESTAMP(), default=now)
class ResetPassword(Base):
"""
This table facilitates password recovery. If someone requests to reset
their password, we generate a random token and insert it into this db.
We then email them this token, verifying their id.
"""
__tablename__ = "ResetPassword"
id = Column(types.Integer, primary_key=True)
user_email = Column(types.Unicode(80))
token = Column(types.Unicode(10))
def init_schema(url):
"""
Setup a bible database connection and initialise the database schema.
:param url: The database to setup.
"""
session, metadata = init_db(url)
meta_table = Table(
'metadata',
metadata,
Column('key', types.Unicode(255), primary_key=True, index=True),
Column('value', types.Unicode(255)),
)
book_table = Table(
'book',
metadata,
Column('id', types.Integer, primary_key=True),
Column('book_reference_id', types.Integer, index=True),
Column('testament_reference_id', types.Integer),
Column('name', types.Unicode(50), index=True),
)
verse_table = Table(
'verse',
metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('book_id', types.Integer, ForeignKey('book.id'), index=True),
Column('chapter', types.Integer, index=True),
Column('verse', types.Integer, index=True),
Column('text', types.UnicodeText, index=True),
)
try:
class_mapper(BibleMeta)
except UnmappedClassError:
mapper(BibleMeta, meta_table)
try:
class_mapper(Book)
except UnmappedClassError:
mapper(Book,
book_table,
properties={'verses': relation(Verse, backref='book')})
try:
class_mapper(Verse)
except UnmappedClassError:
mapper(Verse, verse_table)
metadata.create_all(checkfirst=True)
return session
class Page(Base):
__tablename__ = 'pages'
id = schema.Column(types.Integer,
schema.Sequence('page_seq_id', optional=True),
primary_key=True)
content = schema.Column(types.Text(), nullable=False)
posted = schema.Column(types.DateTime(), default=now)
title = schema.Column(types.Unicode(255), default=u'Untitled Page')
heading = schema.Column(types.Unicode(255))
gender = schema.Column(types.Enum('male', 'female'))
comments = orm.relationship("Comment", backref='page')
tags = orm.relationship('Tag', secondary=pagetag_table, backref='pages')
class UsersTable(Base):
# Define the users table interface
__tablename__ = "Users"
UserID = sa.Column(types.Integer, primary_key=True)
UserName = sa.Column(types.Unicode(64))
UserPoints = sa.Column(types.Integer)
UserEMail = sa.Column(types.Unicode(64))
UserPassword = sa.Column(types.Unicode(256))
LogInCount = sa.Column(types.Integer)
LastLogin = sa.Column(types.DateTime())
IsAdmin = sa.Column(types.Boolean)
IconID = sa.Column(types.Integer)
class User(Base):
__tablename__ = 'User'
__table_args__ = ({'mysql_engine': 'InnoDB'}, )
id = schema.Column(types.Integer, primary_key=True)
username = schema.Column(types.Unicode(255), unique=True, nullable=False)
password = schema.Column(types.Unicode(255), unique=True, nullable=False)
groups = orm.relationship('Group', secondary=user_group_table)
def _set_password(self, password):
"""Hash password on the fly."""
if isinstance(password, unicode):
password_8bit = password.encode('UTF-8')
else:
password_8bit = password
salt = sha1()
salt.update(os.urandom(60))
hash = sha1()
hash.update(password_8bit + salt.hexdigest())
hashed_password = salt.hexdigest() + hash.hexdigest()
# Make sure the hased password is an UTF-8 object at the end of the
# process because SQLAlchemy _wants_ a unicode object for Unicode
# fields
if not isinstance(hashed_password, unicode):
hashed_password = hashed_password.decode('UTF-8')
self.password = hashed_password
def _get_password(self):
"""Return the password hashed"""
return self.password
def validate_password(self, password):
"""
Check the password against existing credentials.
:param password: the password that was provided by the user to
try and authenticate. This is the clear text version that we will
need to match against the hashed one in the database.
:type password: unicode object.
:return: Whether the password is valid.
:rtype: bool
"""
hashed_pass = sha1()
hashed_pass.update(password + self.password[:40])
return self.password[40:] == hashed_pass.hexdigest()
