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()