diff options
Diffstat (limited to 'cli/app/sql')
| -rw-r--r-- | cli/app/sql/common.py | 33 | ||||
| -rw-r--r-- | cli/app/sql/env.py | 68 | ||||
| -rw-r--r-- | cli/app/sql/models/collection.py | 58 | ||||
| -rw-r--r-- | cli/app/sql/models/media.py | 70 | ||||
| -rw-r--r-- | cli/app/sql/models/upload.py | 44 | ||||
| -rw-r--r-- | cli/app/sql/script.py.mako | 26 |
6 files changed, 299 insertions, 0 deletions
diff --git a/cli/app/sql/common.py b/cli/app/sql/common.py new file mode 100644 index 0000000..cf819e1 --- /dev/null +++ b/cli/app/sql/common.py @@ -0,0 +1,33 @@ +import os +import glob +import time + +import mysql.connector +from sqlalchemy import create_engine +from sqlalchemy.orm import sessionmaker +from sqlalchemy.ext.declarative import declarative_base + +from flask_sqlalchemy import SQLAlchemy + +from app.settings import app_cfg + +# connection_url = "mysql+mysqlconnector://{}:{}@{}/{}?charset=utf8mb4".format( +# os.getenv("DB_USER"), +# os.getenv("DB_PASS"), +# os.getenv("DB_HOST"), +# os.getenv("DB_NAME") +# ) + +os.makedirs(app_cfg.DIR_DATABASE, exist_ok=True) + +connection_url = "sqlite:///{}".format(os.path.join(app_cfg.DIR_DATABASE, 'swimmer.sqlite3')) + +engine = create_engine(connection_url, encoding="utf-8", pool_recycle=3600) + +Session = sessionmaker(bind=engine) +Base = declarative_base() + +db = SQLAlchemy() + +from app.sql.models.collection import Collection +from app.sql.models.upload import Upload diff --git a/cli/app/sql/env.py b/cli/app/sql/env.py new file mode 100644 index 0000000..a2815db --- /dev/null +++ b/cli/app/sql/env.py @@ -0,0 +1,68 @@ +from sqlalchemy import engine_from_config +from sqlalchemy import pool + +from alembic import context + +from app.sql.common import db, engine, connection_url, Base + +# this is the Alembic Config object, which provides +# access to the values within the .ini file in use. +config = context.config +config.set_main_option("script_location", "./app/sql") +config.set_main_option("sqlalchemy.url", connection_url) + +target_metadata = Base.metadata + +from app.sql.models.collection import Collection +from app.sql.models.upload import Upload + +def run_migrations_offline(): + """Run migrations in 'offline' mode. + + This configures the context with just a URL + and not an Engine, though an Engine is acceptable + here as well. By skipping the Engine creation + we don't even need a DBAPI to be available. + + Calls to context.execute() here emit the given string to the + script output. + + """ + url = config.get_main_option("sqlalchemy.url") + context.configure( + url=url, + target_metadata=target_metadata, + literal_binds=True, + dialect_opts={"paramstyle": "named"}, + ) + + with context.begin_transaction(): + context.run_migrations() + + +def run_migrations_online(): + """Run migrations in 'online' mode. + + In this scenario we need to create an Engine + and associate a connection with the context. + + """ + # connectable = engine_from_config( + # config.get_section(config.config_ini_section), + # prefix="sqlalchemy.", + # poolclass=pool.NullPool, + # ) + + with engine.connect() as connection: + context.configure( + connection=connection, target_metadata=target_metadata + ) + + with context.begin_transaction(): + context.run_migrations() + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/cli/app/sql/models/collection.py b/cli/app/sql/models/collection.py new file mode 100644 index 0000000..0bc808c --- /dev/null +++ b/cli/app/sql/models/collection.py @@ -0,0 +1,58 @@ +from sqlalchemy import create_engine, Table, Column, String, Integer, Boolean, DateTime, Text +from sqlalchemy.orm import relationship +from sqlalchemy_utc import UtcDateTime, utcnow +import sqlalchemy.sql.functions as func +from wtforms_alchemy import ModelForm + +from app.sql.common import Base, Session +from app.sql.models.collection_media import CollectionMedia + +class Collection(Base): + """Table for storing references to various media""" + __tablename__ = 'collection' + id = Column(Integer, primary_key=True) + title = Column(String(64, convert_unicode=True), nullable=False) + username = Column(String(16, convert_unicode=True), nullable=False) + notes = Column(Text) + archived = Column(Boolean, default=False) + created_at = Column(UtcDateTime(), default=utcnow()) + updated_at = Column(UtcDateTime(), onupdate=utcnow()) + + medias = relationship("Media", secondary="collection_media", lazy='dynamic') + + def toJSON(self): + return { + 'id': self.id, + 'title': self.title, + 'username': self.username, + 'notes': self.notes, + 'archived': self.archived, + 'created_at': self.created_at, + 'updated_at': self.updated_at, + 'media': [media.toJSON() for media in self.medias.limit(7)], + } + + def toFullJSON(self): + return { + 'id': self.id, + 'title': self.title, + 'username': self.username, + 'notes': self.notes, + 'archived': self.archived, + 'created_at': self.created_at, + 'updated_at': self.updated_at, + 'media': [media.toJSON() for media in self.medias], + } + +def getMediaCounts(session): + return session.query( + CollectionMedia.collection_id, + func.count(CollectionMedia.media_id) + ).group_by(CollectionMedia.collection_id).all() + +class CollectionForm(ModelForm): + class Meta: + model = Collection + exclude = ['created_at', 'updated_at'] + def get_session(): + return Session()
\ No newline at end of file diff --git a/cli/app/sql/models/media.py b/cli/app/sql/models/media.py new file mode 100644 index 0000000..d732b24 --- /dev/null +++ b/cli/app/sql/models/media.py @@ -0,0 +1,70 @@ +from sqlalchemy import create_engine, Table, Column, String, Integer, DateTime, ForeignKey +from sqlalchemy.orm import relationship +from sqlalchemy.ext.hybrid import hybrid_property +from sqlalchemy_utc import UtcDateTime, utcnow +import sqlalchemy.sql.functions as func +from wtforms_alchemy import ModelForm + +from app.sql.common import Base, Session +from app.sql.columns.hash_column import HashColumn +from app.sql.columns.media_type_column import MediaTypeColumn +from app.models.types import MediaTypeIndex, MediaTypeName + +from app.utils.file_utils import sha256_tree +from app.settings import app_cfg + +from os.path import join + +class Media(Base): + """Table for storing references to various media""" + __tablename__ = 'media' + id = Column(Integer, primary_key=True) + import_id = Column(Integer, ForeignKey('media_import.id'), nullable=True) + mediaType = Column(MediaTypeColumn(), nullable=False) + sha256 = Column(HashColumn(32), nullable=False) + ext = Column(String(4, convert_unicode=True), nullable=False) + frame = Column(Integer, nullable=True) + created_at = Column(UtcDateTime(), default=utcnow()) + + # children = relationship("Media") + # media_features = relationship("MediaFeature") + # media_metadata = relationship("MediaMetadata") + + def toJSON(self): + return { + 'id': self.id, + 'import_id': self.import_id, + 'mediaType': self.mediaType, + 'sha256': self.sha256, + 'frame': self.frame, + 'ext': self.ext, + 'url': self.url(), + 'created_at': self.created_at, + } + + def toFullJSON(self, session): + siblings = session.query(Media).filter(Media.sha256 == self.sha256).all() + return { + 'el': self.toJSON(), + 'siblings': [ el.toJSON() for el in siblings ], + } + + def filename(self): + if self.mediaType == 'video_frame': + return "{}_{:03d}{}".format(self.sha256, self.frame, self.ext) + return "{}{}".format(self.sha256, self.ext) + + def filetree(self): + return sha256_tree(self.sha256) + + def filepath(self): + return join(app_cfg.DIR_MEDIA, self.filetree()) + + def fullpath(self): + return join(self.filepath(), self.filename()) + + def archivepath(self): + return join('media', self.filename()) + + def url(self): + return join(app_cfg.URL_MEDIA, self.filetree(), self.filename()) diff --git a/cli/app/sql/models/upload.py b/cli/app/sql/models/upload.py new file mode 100644 index 0000000..f1e8108 --- /dev/null +++ b/cli/app/sql/models/upload.py @@ -0,0 +1,44 @@ +from sqlalchemy import create_engine, Table, Column, String, Integer, DateTime +import sqlalchemy.sql.functions as func +from sqlalchemy_utc import UtcDateTime, utcnow +from wtforms_alchemy import ModelForm + +from app.sql.common import db, Base, Session +from app.sql.columns.hash_column import HashColumn +from app.sql.columns.media_type_column import MediaTypeColumn + +from app.utils.file_utils import sha256_tree +from app.settings import app_cfg + +from os.path import join + +class Upload(Base): + """Table for storing references to various media""" + __tablename__ = 'uploads' + id = Column(Integer, primary_key=True) + sha256 = Column(HashColumn(32), nullable=False) + ext = Column(String(4, convert_unicode=True), nullable=False) + username = Column(String(16, convert_unicode=True), nullable=False) + created_at = Column(UtcDateTime(), default=utcnow()) + + def toJSON(self): + return { + 'id': self.id, + 'sha256': self.sha256, + 'ext': self.ext, + 'username': self.username, + 'url': self.url(), + 'created_at': self.created_at, + } + + def filename(self): + return "{}{}".format(self.sha256, self.ext) + + def filepath(self): + return join(app_cfg.DIR_UPLOADS, sha256_tree(self.sha256)) + + def fullpath(self): + return join(self.filepath(), self.filename()) + + def url(self): + return join(app_cfg.URL_UPLOADS, sha256_tree(self.sha256), self.filename()) diff --git a/cli/app/sql/script.py.mako b/cli/app/sql/script.py.mako new file mode 100644 index 0000000..94bb012 --- /dev/null +++ b/cli/app/sql/script.py.mako @@ -0,0 +1,26 @@ +"""${message} + +Revision ID: ${up_revision} +Revises: ${down_revision | comma,n} +Create Date: ${create_date} + +""" +from alembic import op +import sqlalchemy as sa +import sqlalchemy_utc +import app.sql.columns +${imports if imports else ""} + +# revision identifiers, used by Alembic. +revision = ${repr(up_revision)} +down_revision = ${repr(down_revision)} +branch_labels = ${repr(branch_labels)} +depends_on = ${repr(depends_on)} + + +def upgrade(): + ${upgrades if upgrades else "pass"} + + +def downgrade(): + ${downgrades if downgrades else "pass"} |