class User(Base):
__tablename__ = "user"
user_name = Column(VARCHAR(150), primary_key=True)
password = Column(BYTEA(32))
salt = Column(BYTEA(32))
def _convert_type(colname, ora_type, schema_name='', table_name=''):
"""
Converts a data type in the source (Oracle) database to a Postgres type.
Args:
colname (str): Name of the column.
ora_type (obj): Data type in the source (Oracle) database.
schema_name (str): Name of the schema.
table_name (str): Name of the table.
"""
pg_type = ora_type
# "NullType is used as a default type for those cases
# where a type cannot be determined"
# NB: this needs to be first in the list
# Otherwise str(ora_type) clauses will error
if isinstance(ora_type, sqlalchemy.types.NullType):
pg_type = sqlalchemy.types.String()
logging.info('\t{}.{}.{}: NULL DETECTED'.format(
schema_name, table_name, colname))
return pg_type
elif isinstance(ora_type, sqlalchemy.types.Numeric):
pg_type = sqlalchemy.types.Numeric()
elif isinstance(ora_type, sqlalchemy.types.DateTime):
pg_type = TIMESTAMP()
elif isinstance(ora_type, sqlalchemy.types.Text):
pg_type = sqlalchemy.types.Text()
elif isinstance(ora_type, sqlalchemy.types.NVARCHAR):
pg_type = sqlalchemy.types.VARCHAR()
elif isinstance(ora_type, sqlalchemy.types.BLOB):
pg_type = BYTEA()
elif str(ora_type) == 'RAW':
pg_type = BYTEA()
# this isn't currently catching the binary_float
elif str(ora_type) == 'BINARY_FLOAT':
pg_type = REAL()
elif str(ora_type) == 'INTERVAL DAY TO SECOND':
pg_type = sqlalchemy.types.Interval(second_precision=True)
else:
pass
if pg_type != ora_type:
msg = "\t{}.{}.{}: {} converted to {}".format(schema_name, table_name,
colname, ora_type,
pg_type)
logging.info(msg)
return pg_type
class CeleryTaskSetMetaModel(Base):
__tablename__ = "celery_tasksetmeta"
id = Column(Integer, autoincrement=True, primary_key=True)
taskset_id = Column(String(155), nullable=True, unique=True)
result = Column(BYTEA(), nullable=True)
date_done = Column(DateTime(), nullable=True)
class Association(Base):
__tablename__ = 'associations'
__table_args__ = {'schema': 'openid'}
server_url = Column(UnicodeText, primary_key=True)
handle = Column(UnicodeText, primary_key=True)
secret = Column(BYTEA(length=128))
issued = Column(Integer)
lifetime = Column(Integer)
assoc_type = Column(UnicodeText)
@property
def expires(self):
"""Sets the timestamp for when the current association expires"""
return self.issued + self.lifetime
def __init__(self, server_url, handle, secret, issued, lifetime,
assoc_type):
self.server_url = server_url
self.handle = handle
self.secret = secret
self.issued = issued
self.lifetime = lifetime
self.assoc_type = assoc_type
def __repr__(self):
return u"<Association(%s, %s)>" % (self.server_url, self.handle)
def is_expired(self):
"""Checks to see if an association has expired."""
now = datetime.datetime.now()
now_stamp = int(time.mktime(now.timetuple()))
return now_stamp > self.expires
class User(Base):
__tablename__ = "users"
user_id = sa.Column(sa.Integer(), primary_key=True, unique=True)
cal_id = sa.Column(sa.String())
secrets = sa.Column(sa.JSON())
cal_service = sa.Column(BYTEA())
class CeleryTaskMetaModel(Base):
__tablename__ = "celery_taskmeta"
id = Column(Integer, autoincrement=True, primary_key=True)
task_id = Column(String(155), nullable=True, unique=True)
status = Column(String(50), nullable=True)
result = Column(BYTEA(), nullable=True)
date_done = Column(DateTime(), nullable=True)
traceback = Column(Text(), nullable=True)
class User(db.Model):
__tablename__ = "user"
openid = db.Column(db.String(32), primary_key=True)
nickname = db.Column(db.String(32))
avatar = db.Column(BYTEA())
def update(self):
db.session.merge(self)
db.session.commit()
class DBUser(BaseModel):
__tablename__ = "user"
login = Column(VARCHAR(20), unique=True, nullable=False)
password = Column(BYTEA(), nullable=False)
first_name = Column(VARCHAR(50))
last_name = Column(VARCHAR(50))
is_delete = Column(BOOLEAN(), nullable=False, default=False)
class Icon(Base):
"""An icon we have retrived, scaled and stored in our S3-compatible
store."""
__tablename__ = "icons"
# This is a key into our S3 bucket
icon_uuid = Column(PGUUID, primary_key=True)
# BLAKE2b hash to help deduplicate the same icon file served from different
# locations
source_blake2b_hash = Column(BYTEA(length=64), nullable=False, unique=True)
class File(Base):
'''
Data model for a file stored in the file system.
Files are stored in a content-addressable file system: a SHA-2 hash of the
content determines the path where it is stored. For example, a file with
hash e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 is
stored in
data/e/3/b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855.
'''
__tablename__ = 'file'
id = Column(Integer, primary_key=True)
name = Column(String(255))
mime = Column(String(255))
hash = Column(BYTEA(32)) # sha256
def __init__(self, name, mime, content):
''' Constructor. '''
self.name = name
self.mime = mime
hash_ = hashlib.sha256()
hash_.update(content)
self.hash = hash_.digest()
# Write content to file.
data_dir = app.config.get_path('data')
hash_hex = binascii.hexlify(self.hash).decode('ascii')
dir1 = os.path.join(data_dir, hash_hex[0])
dir2 = os.path.join(dir1, hash_hex[1])
path = os.path.join(dir2, hash_hex[2:])
if not os.path.isdir(dir1):
os.mkdir(dir1)
if not os.path.isdir(dir2):
os.mkdir(dir2)
if not os.path.isfile(path):
file_ = open(path, 'wb')
file_.write(content)
file_.close()
def relpath(self):
''' Return path to the file relative to the data directory. '''
data_dir = app.config.get_path('data')
hash_ = binascii.hexlify(self.hash).decode('ascii')
return os.path.join(hash_[0], hash_[1], hash_[2:])
class SQLShareGrant(Base):
__tablename__ = "share_grants"
access_object_id = Column(
satypes.BigInteger,
ForeignKey("access_objects.access_object_id"),
nullable=False,
)
access_verb_id = Column(satypes.SmallInteger,
ForeignKey("access_verbs.access_verb_id"),
nullable=False)
revoked = Column(satypes.Boolean, nullable=False, index=True)
share_token = Column(BYTEA(), nullable=False, primary_key=True)
class Credentials(Base):
__tablename__ = "credentials"
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
username = Column(TEXT(), unique=True, nullable=False)
password = Column(BYTEA(), nullable=False)
last_seen = Column(TIMESTAMP())
user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False)
reset_id = Column(UUID(as_uuid=True), nullable=True)
created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow)
updated_at = Column(TIMESTAMP(),
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow)
user = relationship(Users, backref="credentials", uselist=False)
def test_postgresql_hstore_subtypes(self):
eq_ignore_whitespace(
autogenerate.render._repr_type(HSTORE(), self.autogen_context),
"postgresql.HSTORE(text_type=sa.Text())")
eq_ignore_whitespace(
autogenerate.render._repr_type(HSTORE(text_type=String()),
self.autogen_context),
"postgresql.HSTORE(text_type=sa.String())")
eq_ignore_whitespace(
autogenerate.render._repr_type(HSTORE(text_type=BYTEA()),
self.autogen_context),
"postgresql.HSTORE(text_type=postgresql.BYTEA())")
assert 'from sqlalchemy.dialects import postgresql' in \
self.autogen_context.imports
class FlatpakRef(Base):
'''
Flatpak object/app/runtime reference.
'''
__tablename__ = 'flatpak_refs'
uuid = Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
repo_id = Column(Integer, ForeignKey('flatpak_repositories.id'))
repo = relationship('FlatpakRepository')
kind = Column(Enum(FlatpakRefKind))
name = Column(Text())
version = Column(DebVersion()) # Version of this Ref
branch = Column(String(128), default='stable')
commit = Column(BYTEA()) # OSTree commit ID of this ref
architecture_id = Column(Integer, ForeignKey('archive_architectures.id'))
architecture = relationship('ArchiveArchitecture') # Architecture this reference was made for
class Game(Base):
__tablename__ = 'games'
g_id = Column(Integer, primary_key=True)
title = Column(String(250))
platform_id = Column(Integer, ForeignKey('platforms.p_id'))
platform = relationship("Platform", backref=backref('games'))
picture = Column(String(250), default="http://i.imgur.com/g4zDXeF.png")
image = Column(BYTEA()) #Here?
image_name = Column(String(250))
image_size = Column(Integer)
info = Column(String(1500), default="")
release_date = Column(DateTime, default=datetime.now)
developer = Column(String(250), default="")
publisher = Column(String(250), default="")
relations = Column(Integer, default=0)
edited_by = Column(String(250), default="")
def __init__(self, title=None, platform=None):
self.title = title
self.platform_id = platform.p_id
self.platform = platform
def __repr__(self):
return '<Game %r>' % (self.title)
class File(Base):
'''
Data model for a file stored in the file system.
Files are stored in a content-addressable file system: a SHA-2 hash of the
content determines the path where it is stored. For example, a file with
hash e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 is
stored in
data/e/3/b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855.
Zip archives can be created by setting zip_archive=True and passing
a list of file tuples and/or str_files tuples:
zip_files = [
('filename.jpg','path/to/file')
]
zip_str_files = [
('filename.csv','some,juicy,content')
]
zip_str_files are created in-memory using StringIO
and written as zipfile.ZipInfo.
'''
__tablename__ = 'file'
ACCESS_TYPES = [
('private', 'Private'),
('shared', 'Shared'),
]
id = Column(Integer, primary_key=True)
name = Column(String(255))
mime = Column(String(255))
hash = Column(BYTEA(32)) # sha256
access_type = Column(ChoiceType(ACCESS_TYPES),
nullable=False,
default='private')
user_id = Column(Integer,
ForeignKey('user.id', name='fk_file_user'),
nullable=False)
def __init__(self,
name,
mime,
user_id,
content=None,
zip_archive=False,
zip_files=[],
zip_str_files=[],
access_type='private'):
'''
Constructor.
'''
self.name = name
self.mime = mime
self.user_id = user_id
self.access_type = access_type
# Create dummy content to use in hash if there is
# no content (zip archives)
if content is None:
content = ('DUMMY DATA - {}' + random_string(1000)).encode('utf8')
hash_ = hashlib.sha256()
hash_.update(content)
self.hash = hash_.digest()
# Write content to file.
data_dir = get_path('data')
hash_hex = binascii.hexlify(self.hash).decode('ascii')
dir1 = os.path.join(data_dir, hash_hex[0])
dir2 = os.path.join(dir1, hash_hex[1])
path = os.path.join(dir2, hash_hex[2:])
if not os.path.isdir(dir1):
os.mkdir(dir1)
if not os.path.isdir(dir2):
os.mkdir(dir2)
if not os.path.isfile(path):
if zip_archive:
self.zip_files(path, zip_files, zip_str_files)
else:
file_ = open(path, 'wb')
file_.write(content)
file_.close()
def chown(self, uid, gid):
''' Change ownership of this file and its two immediate ancestors. '''
file_path = os.path.join(get_path('data'), self.relpath())
os.chown(file_path, uid, gid)
ancestor1_path = os.path.dirname(file_path)
os.chown(ancestor1_path, uid, gid)
ancestor2_path = os.path.dirname(ancestor1_path)
os.chown(ancestor2_path, uid, gid)
def zip_files(self, path, files, str_files):
''' Create a zip archive of files and string files.'''
zip_file = zipfile.ZipFile(path, 'w')
data_dir = get_path('data')
# Add files
for f in files:
f_path = os.path.join(data_dir, f[1])
zip_file.write(f_path,
arcname=f[0],
compress_type=zipfile.ZIP_DEFLATED)
# Write string files
for str_file in str_files:
info = zipfile.ZipInfo(str_file[0])
info.date_time = time.localtime(time.time())[:6]
info.compress_type = zipfile.ZIP_DEFLATED
# http://stackoverflow.com/questions/434641/how-do-i-set-permissions-attributes-on-a-file-in-a-zip-file-using-pythons-zip/6297838#6297838
info.external_attr = 0o644 << 16 # rw-r-r
zip_file.writestr(info, str_file[1])
zip_file.close()
def relpath(self):
''' Return path to the file relative to the data directory. '''
hash_ = binascii.hexlify(self.hash).decode('ascii')
return os.path.join(hash_[0], hash_[1], hash_[2:])
def url(self):
'''
Return API relative URL for file.
'''
return '/api/files/{}'.format(self.id)
def as_dict(self):
''' Return dictionary representation of this file. '''
return {
'id': self.id,
'name': self.name,
'mime': self.mime,
'path': self.relpath(),
'access_type': self.access_type,
'url': '/api/files/{}'.format(self.id)
}
def to_base64_sql(text_to_encode):
encoding = "base64"
return func.encode(cast(text_to_encode, BYTEA()),
cast(encoding, sqlalchemy.Text()))
def upgrade():
op.create_table(
"timestamps",
sa.Column("name", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("value", sa.TIMESTAMP()),
)
op.create_table(
"accounts_user",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column("password", sa.VARCHAR(length=128), nullable=False),
sa.Column("last_login", sa.TIMESTAMP(timezone=True), nullable=False),
sa.Column("is_superuser", sa.BOOLEAN(), nullable=False),
sa.Column("username", CIText(), nullable=False, unique=True),
sa.Column("name", sa.VARCHAR(length=100), nullable=False),
sa.Column("is_staff", sa.BOOLEAN(), nullable=False),
sa.Column("is_active", sa.BOOLEAN(), nullable=False),
sa.Column("date_joined", sa.TIMESTAMP(timezone=True), nullable=False),
)
op.create_check_constraint(
"accounts_user_username_length",
"accounts_user",
sa.text("length(username) <= 50"),
)
op.create_check_constraint(
"accounts_user_valid_username",
"accounts_user",
sa.text("username ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'"),
)
op.create_table(
"cookies",
sa.Column("cookie", sa.TEXT(), primary_key=True, nullable=False),
sa.Column(
"name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
),
sa.Column("last_seen", sa.TIMESTAMP()),
)
op.create_index("cookies_last_seen", "cookies", ["last_seen"])
op.create_table(
"oauth_consumers",
sa.Column(
"consumer",
sa.VARCHAR(length=32),
primary_key=True,
nullable=False,
),
sa.Column("secret", sa.VARCHAR(length=64), nullable=False),
sa.Column("date_created", sa.DATE(), nullable=False),
sa.Column(
"created_by",
CIText(),
sa.ForeignKey("accounts_user.username", onupdate="CASCADE"),
),
sa.Column("last_modified", sa.DATE(), nullable=False),
sa.Column("description", sa.VARCHAR(length=255), nullable=False),
)
op.create_table(
"cheesecake_main_indices",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column("absolute", sa.INTEGER(), nullable=False),
sa.Column("relative", sa.INTEGER(), nullable=False),
)
op.create_table(
"packages",
sa.Column("name", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("stable_version", sa.TEXT()),
sa.Column("normalized_name", sa.TEXT()),
sa.Column("autohide", sa.BOOLEAN(), server_default=sa.text("TRUE")),
sa.Column("comments", sa.BOOLEAN(), server_default=sa.text("TRUE")),
sa.Column("bugtrack_url", sa.TEXT()),
sa.Column(
"hosting_mode",
sa.TEXT(),
nullable=False,
server_default="pypi-explicit",
),
)
op.create_check_constraint(
"packages_valid_name",
"packages",
sa.text("name ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'"),
)
op.create_table(
"releases",
sa.Column(
"name",
sa.TEXT(),
sa.ForeignKey("packages.name", onupdate="CASCADE"),
primary_key=True,
nullable=False,
),
sa.Column("version", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("author", sa.TEXT()),
sa.Column("author_email", sa.TEXT()),
sa.Column("maintainer", sa.TEXT()),
sa.Column("maintainer_email", sa.TEXT()),
sa.Column("home_page", sa.TEXT()),
sa.Column("license", sa.TEXT()),
sa.Column("summary", sa.TEXT()),
sa.Column("description", sa.TEXT()),
sa.Column("keywords", sa.TEXT()),
sa.Column("platform", sa.TEXT()),
sa.Column("download_url", sa.TEXT()),
sa.Column("_pypi_ordering", sa.INTEGER()),
sa.Column("_pypi_hidden", sa.BOOLEAN()),
sa.Column("description_html", sa.TEXT()),
sa.Column(
"cheesecake_installability_id",
sa.INTEGER(),
sa.ForeignKey("cheesecake_main_indices.id"),
),
sa.Column(
"cheesecake_documentation_id",
sa.INTEGER(),
sa.ForeignKey("cheesecake_main_indices.id"),
),
sa.Column(
"cheesecake_code_kwalitee_id",
sa.INTEGER(),
sa.ForeignKey("cheesecake_main_indices.id"),
),
sa.Column("requires_python", sa.TEXT()),
sa.Column("description_from_readme", sa.BOOLEAN()),
)
op.create_index("release_name_idx", "releases", ["name"])
op.create_index("release_version_idx", "releases", ["version"])
op.create_index("release_pypi_hidden_idx", "releases", ["_pypi_hidden"])
op.create_table(
"release_dependencies",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("kind", sa.INTEGER()),
sa.Column("specifier", sa.TEXT()),
)
op.create_index("rel_dep_name_idx", "release_dependencies", ["name"])
op.create_index(
"rel_dep_name_version_idx",
"release_dependencies",
["name", "version"],
)
op.create_index(
"rel_dep_name_version_kind_idx",
"release_dependencies",
["name", "version", "kind"],
)
op.create_foreign_key(
None,
"release_dependencies",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_table(
"ratings",
sa.Column("name", sa.TEXT(), nullable=False),
sa.Column("version", sa.TEXT(), nullable=False),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey("accounts_user.username", ondelete="CASCADE"),
nullable=False,
),
sa.Column("date", sa.TIMESTAMP()),
sa.Column("rating", sa.INTEGER()),
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
)
op.create_unique_constraint("ratings_id_key", "ratings", ["id"])
op.create_unique_constraint(
"ratings_name_key",
"ratings",
["name", "version", "user_name"],
)
op.create_foreign_key(
None,
"ratings",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
ondelete="CASCADE",
)
op.create_index("rating_name_version", "ratings", ["name", "version"])
op.create_table(
"comments",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column(
"rating",
sa.INTEGER(),
sa.ForeignKey(
"ratings.id",
ondelete="CASCADE",
),
),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey("accounts_user.username", ondelete="CASCADE"),
),
sa.Column("date", sa.TIMESTAMP()),
sa.Column("message", sa.TEXT()),
sa.Column(
"in_reply_to",
sa.INTEGER(),
sa.ForeignKey(
"comments.id",
ondelete="CASCADE",
),
),
)
op.create_table(
"oauth_access_tokens",
sa.Column(
"token",
sa.VARCHAR(length=32),
primary_key=True,
nullable=False,
),
sa.Column("secret", sa.VARCHAR(length=64), nullable=False),
sa.Column("consumer", sa.VARCHAR(length=32), nullable=False),
sa.Column("date_created", sa.DATE(), nullable=False),
sa.Column("last_modified", sa.DATE(), nullable=False),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
),
)
op.create_table(
"openid_nonces",
sa.Column("created", sa.TIMESTAMP()),
sa.Column("nonce", sa.TEXT()),
)
op.create_index("openid_nonces_created", "openid_nonces", ["created"])
op.create_index("openid_nonces_nonce", "openid_nonces", ["nonce"])
op.create_table(
"openid_sessions",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column("url", sa.TEXT()),
sa.Column("assoc_handle", sa.TEXT()),
sa.Column("expires", sa.TIMESTAMP()),
sa.Column("mac_key", sa.TEXT()),
)
op.create_table(
"oauth_request_tokens",
sa.Column(
"token",
sa.VARCHAR(length=32),
primary_key=True,
nullable=False,
),
sa.Column("secret", sa.VARCHAR(length=64), nullable=False),
sa.Column("consumer", sa.VARCHAR(length=32), nullable=False),
sa.Column("callback", sa.TEXT()),
sa.Column("date_created", sa.DATE(), nullable=False),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
),
)
op.create_table(
"oid_nonces",
sa.Column(
"server_url",
sa.VARCHAR(length=2047),
primary_key=True,
nullable=False,
),
sa.Column(
"timestamp",
sa.INTEGER(),
autoincrement=False,
primary_key=True,
nullable=False,
),
sa.Column(
"salt",
sa.CHAR(length=40),
primary_key=True,
nullable=False,
),
)
op.create_table(
"mirrors",
sa.Column("ip", sa.TEXT(), primary_key=True, nullable=False),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey("accounts_user.username"),
),
sa.Column("index_url", sa.TEXT()),
sa.Column("last_modified_url", sa.TEXT()),
sa.Column("local_stats_url", sa.TEXT()),
sa.Column("stats_url", sa.TEXT()),
sa.Column("mirrors_url", sa.TEXT()),
)
op.create_table(
"trove_classifiers",
sa.Column(
"id",
sa.INTEGER(),
autoincrement=False,
primary_key=True,
nullable=False,
),
sa.Column("classifier", sa.TEXT()),
sa.Column("l2", sa.INTEGER()),
sa.Column("l3", sa.INTEGER()),
sa.Column("l4", sa.INTEGER()),
sa.Column("l5", sa.INTEGER()),
)
op.create_index(
"trove_class_class_idx",
"trove_classifiers",
["classifier"],
)
op.create_index("trove_class_id_idx", "trove_classifiers", ["id"])
op.create_unique_constraint(
"trove_classifiers_classifier_key",
"trove_classifiers",
["classifier"],
)
op.create_table(
"roles",
sa.Column("role_name", sa.TEXT()),
sa.Column(
"user_name",
CIText(),
sa.ForeignKey("accounts_user.username", onupdate="CASCADE"),
),
sa.Column(
"package_name",
sa.TEXT(),
sa.ForeignKey("packages.name", onupdate="CASCADE"),
),
)
op.create_index("roles_pack_name_idx", "roles", ["package_name"])
op.create_index("roles_user_name_idx", "roles", ["user_name"])
op.create_table(
"release_requires_python",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("specifier", sa.TEXT()),
)
op.create_foreign_key(
None,
"release_requires_python",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_index(
"rel_req_python_name_idx",
"release_requires_python",
["name"],
)
op.create_index(
"rel_req_python_name_version_idx",
"release_requires_python",
["name", "version"],
)
op.create_index(
"rel_req_python_version_id_idx",
"release_requires_python",
["version"],
)
op.create_table(
"browse_tally",
sa.Column(
"trove_id",
sa.INTEGER(),
autoincrement=False,
primary_key=True,
nullable=False,
),
sa.Column("tally", sa.INTEGER()),
)
op.create_table(
"dual",
sa.Column("dummy", sa.INTEGER()),
)
op.create_table(
"release_urls",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("url", sa.TEXT()),
sa.Column("packagetype", sa.TEXT()),
)
op.create_foreign_key(
None,
"release_urls",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_index("release_urls_name_idx", "release_urls", ["name"])
op.create_index(
"release_urls_packagetype_idx",
"release_urls",
["packagetype"],
)
op.create_index("release_urls_version_idx", "release_urls", ["version"])
op.create_table(
"description_urls",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("url", sa.TEXT()),
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
)
op.create_index("description_urls_name_idx", "description_urls", ["name"])
op.create_index(
"description_urls_name_version_idx",
"description_urls",
["name", "version"],
)
op.create_foreign_key(
None,
"description_urls",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_table(
"oauth_nonce",
sa.Column("timestamp", sa.INTEGER(), nullable=False),
sa.Column("consumer", sa.VARCHAR(length=32), nullable=False),
sa.Column("nonce", sa.VARCHAR(length=32), nullable=False),
sa.Column("token", sa.VARCHAR(length=32)),
)
op.create_table(
"journals",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("action", sa.TEXT()),
sa.Column("submitted_date", sa.TIMESTAMP()),
sa.Column(
"submitted_by",
CIText(),
sa.ForeignKey("accounts_user.username", onupdate="CASCADE"),
),
sa.Column("submitted_from", sa.TEXT()),
)
op.execute("ALTER TABLE journals ADD COLUMN id SERIAL")
op.create_index("journals_name_idx", "journals", ["name"])
op.create_index(
"journals_changelog",
"journals",
["submitted_date", "name", "version", "action"],
)
op.create_index(
"journals_latest_releases",
"journals",
["submitted_date", "name", "version"],
)
op.create_index("journals_version_idx", "journals", ["version"])
op.create_table(
"rego_otk",
sa.Column(
"name",
CIText(),
sa.ForeignKey("accounts_user.username", ondelete="CASCADE"),
),
sa.Column("otk", sa.TEXT()),
sa.Column("date", sa.TIMESTAMP()),
)
op.create_index("rego_otk_otk_idx", "rego_otk", ["otk"])
op.create_index("rego_otk_name_idx", "rego_otk", ["name"])
op.create_unique_constraint("rego_otk_unique", "rego_otk", ["otk"])
op.create_table(
"release_files",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("python_version", sa.TEXT()),
sa.Column("packagetype", sa.TEXT()),
sa.Column("comment_text", sa.TEXT()),
sa.Column("filename", sa.TEXT()),
sa.Column("md5_digest", sa.TEXT()),
sa.Column("downloads", sa.INTEGER(), server_default=sa.text("0")),
sa.Column("upload_time", sa.TIMESTAMP()),
)
op.create_index("release_files_name_idx", "release_files", ["name"])
op.create_index(
"release_files_name_version_idx",
"release_files",
["name", "version"],
)
op.create_index("release_files_version_idx", "release_files", ["version"])
op.create_index(
"release_files_packagetype_idx",
"release_files",
["packagetype"],
)
op.create_unique_constraint(
"release_files_filename_key",
"release_files",
["filename"],
)
op.create_unique_constraint(
"release_files_md5_digest_key",
"release_files",
["md5_digest"],
)
op.create_foreign_key(
None,
"release_files",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_table(
"openid_whitelist",
sa.Column("name", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("trust_root", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("created", sa.TIMESTAMP()),
)
op.create_table(
"comments_journal",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column("id", sa.INTEGER()),
sa.Column(
"submitted_by",
CIText(),
sa.ForeignKey("accounts_user.username", ondelete="CASCADE"),
),
sa.Column("date", sa.TIMESTAMP()),
sa.Column("action", sa.TEXT()),
)
op.create_foreign_key(
None,
"comments_journal",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
ondelete="CASCADE",
)
op.create_table(
"csrf_tokens",
sa.Column(
"name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
primary_key=True,
nullable=False,
),
sa.Column("token", sa.TEXT()),
sa.Column("end_date", sa.TIMESTAMP()),
)
op.create_table(
"oid_associations",
sa.Column(
"server_url",
sa.VARCHAR(length=2047),
primary_key=True,
nullable=False,
),
sa.Column(
"handle",
sa.VARCHAR(length=255),
primary_key=True,
nullable=False,
),
sa.Column("secret", BYTEA(), nullable=False),
sa.Column("issued", sa.INTEGER(), nullable=False),
sa.Column("lifetime", sa.INTEGER(), nullable=False),
sa.Column("assoc_type", sa.VARCHAR(length=64), nullable=False),
)
op.create_check_constraint(
"secret_length_constraint",
"oid_associations",
sa.text("length(secret) <= 128"),
)
op.create_table(
"cheesecake_subindices",
sa.Column(
"main_index_id",
sa.INTEGER(),
sa.ForeignKey("cheesecake_main_indices.id"),
primary_key=True,
nullable=False,
),
sa.Column("name", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("value", sa.INTEGER(), nullable=False),
sa.Column("details", sa.TEXT(), nullable=False),
)
op.create_table(
"accounts_email",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column(
"user_id",
sa.INTEGER(),
sa.ForeignKey(
"accounts_user.id",
deferrable=True,
initially="DEFERRED",
),
nullable=False,
),
sa.Column("email", sa.VARCHAR(length=254), nullable=False),
sa.Column("primary", sa.BOOLEAN(), nullable=False),
sa.Column("verified", sa.BOOLEAN(), nullable=False),
)
op.create_index("accounts_email_email_like", "accounts_email", ["email"])
op.create_index("accounts_email_user_id", "accounts_email", ["user_id"])
op.create_unique_constraint(
"accounts_email_email_key",
"accounts_email",
["email"],
)
op.create_table(
"sshkeys",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column(
"name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
),
sa.Column("key", sa.TEXT()),
)
op.create_index("sshkeys_name", "sshkeys", ["name"])
op.create_table(
"accounts_gpgkey",
sa.Column("id", sa.INTEGER(), primary_key=True, nullable=False),
sa.Column(
"user_id",
sa.INTEGER(),
sa.ForeignKey(
"accounts_user.id",
deferrable=True,
initially="DEFERRED",
),
nullable=False,
),
sa.Column("key_id", CIText(), nullable=False),
sa.Column("verified", sa.BOOLEAN(), nullable=False),
)
op.create_unique_constraint(
"accounts_gpgkey_key_id_key",
"accounts_gpgkey",
["key_id"],
)
op.create_check_constraint(
"accounts_gpgkey_valid_key_id",
"accounts_gpgkey",
sa.text("key_id ~* '^[A-F0-9]{8}$'"),
)
op.create_index("accounts_gpgkey_user_id", "accounts_gpgkey", ["user_id"])
op.create_table(
"openid_discovered",
sa.Column("created", sa.TIMESTAMP()),
sa.Column("url", sa.TEXT(), primary_key=True, nullable=False),
sa.Column("services", BYTEA()),
sa.Column("op_endpoint", sa.TEXT()),
sa.Column("op_local", sa.TEXT()),
)
op.create_table(
"release_classifiers",
sa.Column("name", sa.TEXT()),
sa.Column("version", sa.TEXT()),
sa.Column(
"trove_id",
sa.INTEGER(),
sa.ForeignKey("trove_classifiers.id"),
),
)
op.create_index("rel_class_name_idx", "release_classifiers", ["name"])
op.create_index(
"rel_class_name_version_idx",
"release_classifiers",
["name", "version"],
)
op.create_index(
"rel_class_version_id_idx",
"release_classifiers",
["version"],
)
op.create_index(
"rel_class_trove_id_idx",
"release_classifiers",
["trove_id"],
)
op.create_foreign_key(
None,
"release_classifiers",
"releases",
["name", "version"],
["name", "version"],
onupdate="CASCADE",
)
op.create_table(
"openids",
sa.Column("id", sa.TEXT(), primary_key=True, nullable=False),
sa.Column(
"name",
CIText(),
sa.ForeignKey(
"accounts_user.username",
onupdate="CASCADE",
ondelete="CASCADE",
),
),
)
class Commit(Named, GHDBase):
__tablename__ = 'commits'
__table_args__ = (
Index('committed_at_index', 'committed_at'),
Index('authored_at_index', 'authored_at'),
)
committer_id = Column(UUID(as_uuid=True), ForeignKey('users.id'))
committer = relationship("User",
back_populates="committed",
foreign_keys=[committer_id])
committer_email_id = Column(UUID(as_uuid=True), ForeignKey('emails.id'))
committer_email = relationship("Email",
back_populates="committed",
foreign_keys=[committer_email_id])
committed_at = Column(DateTime(timezone=False))
author_id = Column(UUID(as_uuid=True), ForeignKey('users.id'))
author = relationship("User",
back_populates="authored",
foreign_keys=[author_id])
author_email_id = Column(UUID(as_uuid=True), ForeignKey('emails.id'))
author_email = relationship("Email",
back_populates="authored",
foreign_keys=[author_email_id])
authored_at = Column(DateTime(timezone=False))
repo_id = Column(UUID(as_uuid=True), ForeignKey('repos.id'))
repo = relationship("Repo", back_populates="commits")
sha = Column(BYTEA(length=40), nullable=False, unique=True)
additions = Column(Integer)
deletions = Column(Integer)
files = relationship('File', back_populates='commit')
parents = relationship(
"Commit",
secondary="commit_parent",
primaryjoin="Commit.id == commit_parent.c.child_id",
secondaryjoin="Commit.id == commit_parent.c.parent_id",
backref="children")
refs = relationship('Ref', back_populates='head')
def __init__(self,
sha,
name,
repo,
additions,
deletions,
committer=None,
committer_email=None,
committed_at=None,
author=None,
author_email=None,
authored_at=None):
super().__init__(name)
self.repo = repo
self.sha = sha
self.additions = additions
self.deletions = deletions
if committer is not None:
self.committer = committer
if committer_email is not None:
self.committer_email = committer_email
if committed_at is not None:
self.committed_at = committed_at
if author is not None:
self.author = author
if author_email is not None:
self.author_email = author_email
if authored_at is not None:
self.authored_at = authored_at
def get_test_group_case():
"""
Returns a postgres CASE statement to return the test_group based on the
value of test_name.
"""
c = "CASE\n"
for tg_name, tests in TEST_GROUPS.items():
c += "WHEN test_name = ANY('{{{}}}') THEN '{}'\n".format(
','.join(tests), tg_name)
c += "ELSE 'unknown'\n"
c += "END\n"
return c
# create domain size4 as int4 check (value >= 0);
SHA1 = BYTEA()
# create domain sha1 as bytea check (octet_length(value) = 20);
SIZE4 = Integer()
OOTEST = ENUM(*TEST_NAMES, name='ootest')
class Report(Base):
__tablename__ = 'report'
report_no = Column(Integer, primary_key=True)
autoclaved_no = Column(Integer, ForeignKey('autoclaved.autoclaved_no'))
autoclaved = relationship('Autoclaved', back_populates='reports')
class APIKey(Base):
__tablename__ = "api_keys"
user_uuid = Column(PGUUID, ForeignKey("users.user_uuid"), primary_key=True)
api_key = Column(BYTEA(length=16), nullable=False, unique=True, index=True)
'a': 'admin-locked',
'e': 'encored',
'u': 'thumbnail-required',
}, {
'embed-type': {
'D': 'google-drive',
'v': 'other',
},
}),
nullable=False,
server_default=''),
Column('page_views', Integer(), nullable=False, server_default='0'),
Column('favorites', Integer(), nullable=False),
Column('submitter_ip_address', String(length=45), nullable=True),
Column('submitter_user_agent_id', Integer(), nullable=True),
Column('image_representations', BYTEA(), nullable=True),
Column('is_spam', Boolean(), nullable=False, server_default='f'),
default_fkey(['userid'], ['login.userid'], name='submission_userid_fkey'),
default_fkey(['folderid'], ['folder.folderid'],
name='submission_folderid_fkey'),
ForeignKeyConstraint(
['submitter_user_agent_id'],
['user_agents.user_agent_id'],
name="submission_agent_id_fkey",
),
Index(
'ind_submission_score',
text("""(
log(favorites + 1)
+ log(page_views + 1) / 2
+ unixtime / 180000.0
Column('settings', CharSettingsColumn({
'h': 'hidden',
'n': 'no-notifications',
'u': 'profile-filter',
'm': 'index-filter',
'f': 'featured-filter',
}, length=20), nullable=False, server_default=''),
default_fkey(['userid'], ['login.userid'], name='folder_userid_fkey'),
)
Index('ind_folder_userid', folder.c.userid)
forgotpassword = Table(
'forgotpassword', metadata,
Column('token_sha256', BYTEA(), primary_key=True, nullable=False),
Column('email', String(length=254), nullable=False),
Column('created_at', TIMESTAMP(timezone=True), nullable=False, server_default=func.now()),
)
Index('ind_forgotpassword_created_at', forgotpassword.c.created_at)
frienduser = Table(
'frienduser', metadata,
Column('userid', Integer(), primary_key=True, nullable=False),
Column('otherid', Integer(), primary_key=True, nullable=False),
Column('settings', CharSettingsColumn({
'p': 'pending',
}, length=20), nullable=False, server_default='p'),
Column('created_at', TIMESTAMP(timezone=True), nullable=False, server_default=func.now()),
'u': 'profile-filter',
'm': 'index-filter',
'f': 'featured-filter',
},
length=20),
nullable=False,
server_default=''),
default_fkey(['userid'], ['login.userid'], name='folder_userid_fkey'),
)
Index('ind_folder_userid', folder.c.userid)
forgotpassword = Table(
'forgotpassword',
metadata,
Column('token_sha256', BYTEA(), primary_key=True, nullable=False),
Column('email', String(length=254), nullable=False),
Column('created_at',
TIMESTAMP(timezone=True),
nullable=False,
server_default=func.now()),
)
Index('ind_forgotpassword_created_at', forgotpassword.c.created_at)
frienduser = Table(
'frienduser',
metadata,
Column('userid', Integer(), primary_key=True, nullable=False),
Column('otherid', Integer(), primary_key=True, nullable=False),
Column('settings',
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
company_id = db.Column(db.Integer, db.ForeignKey('companies.id'), nullable=False, index=True)
# Email and username are case-insensitive to avoid double
# subscribes and other confusion.
username = db.Column(CIText(), nullable=False)
email = db.Column(CIText(), unique=True, nullable=False)
password = db.Column(BYTEA(255), nullable=False)
is_admin = db.Column(db.Boolean, default=False, nullable=False)
activation_token = db.Column(BYTEA, nullable=False)
reset_password_token = db.Column(BYTEA, default=b'', nullable=False)
disabled = db.Column(db.Boolean, default=False, nullable=False)
onboarded = db.Column(db.Boolean, default=False, nullable=False)
updated_at = db.Column(
db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False
)
created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
# Dump the raw utm data on signup. We can make sense of it in a
# separate step.
utm_source = db.Column(db.String(256), nullable=False, default="not-set")
welcome_email_sent = db.Column(db.Boolean, default=False, nullable=False)
trial_end_email_sent = db.Column(db.Boolean, default=False, nullable=False)
def __repr__(self):
return '<User %r>' % self.email
@staticmethod
def normal_signup(username, email, password, utm_source):
user = User(username=username, email=email, utm_source=utm_source)
user.set_password(password)
user.set_activation_token()
user.is_admin = True
return user
@staticmethod
def get_by_activation_token(token):
return User.query.filter_by(activation_token=bytes(token, "utf-8")).first()
@staticmethod
def get_by_reset_password_token(token):
return User.query.filter_by(reset_password_token=bytes(token, "utf-8")).first()
@property
def is_active(self) -> bool:
"""Returns true if the email has been validated."""
return self.activation_token == b''
def set_activation_token(self):
self.activation_token = self.generate_jwt(
TokenType.Activation, current_app.config["ACTIVATION_PERIOD_EXPIRY"]
)
def set_password(self, password):
self.password = bcrypt.generate_password_hash(password)
def verify_password(self, password) -> bool:
return bcrypt.check_password_hash(self.password, password)
def set_reset_password_token(self):
self.reset_password_token = self.generate_jwt(
TokenType.Reset,
current_app.config["RESET_PASSWORD_PERIOD_EXPIRY"]
)
def generate_jwt(self, token_type, expires_in) -> bytes:
claims = {
"user": self.id,
"company": self.company_id,
"type": token_type.value
}
# We need the company name for invited users
# Company will be None the first time around as it isn't saved yet
if token_type == TokenType.Activation and self.company is not None:
claims.update({"companyName": self.company.name})
return create_expiring_jwt(
claims,
expires_in
)
def publish_state(self) -> PublishState:
"""Returns whether the user is allowed to publish a proposal or not"""
if not self.is_active:
return PublishState.USER_INACTIVE_CANNOT_PUBLISH
return self.company.publish_state()
def to_json(self, get_token=True):
"""Data sent to the frontend for that user"""
return {
"id": self.id,
"username": self.username,
"email": self.email,
"isAdmin": self.is_admin,
"isActive": self.is_active,
"publishState": self.publish_state().value,
"companyId": self.company_id,
"disabled": self.disabled,
"onboarded": self.onboarded,
"createdAt": int(self.created_at.replace(tzinfo=timezone.utc).timestamp()),
"loginToken": self.generate_jwt(
TokenType.Login, current_app.config["LOGIN_PERIOD_EXPIRY"]
).decode("utf-8") if get_token else "",
}
def to_public_json(self):
"""Used on the published proposal to avoid leaking data"""
return {
"id": self.id,
"username": self.username,
}
