summaryrefslogtreecommitdiff
path: root/animism-align
diff options
context:
space:
mode:
Diffstat (limited to 'animism-align')
-rw-r--r--animism-align/cli/app/settings/app_cfg.py8
-rw-r--r--animism-align/cli/app/sql/common.py21
-rw-r--r--animism-align/cli/app/sql/models/annotation.py2
-rw-r--r--animism-align/cli/app/sql/versions/202007041642_create_database.py2
-rw-r--r--animism-align/cli/commands/site/migrate_to_mysql.py148
5 files changed, 166 insertions, 15 deletions
diff --git a/animism-align/cli/app/settings/app_cfg.py b/animism-align/cli/app/settings/app_cfg.py
index de5cb6e..dfd0154 100644
--- a/animism-align/cli/app/settings/app_cfg.py
+++ b/animism-align/cli/app/settings/app_cfg.py
@@ -38,7 +38,6 @@ CLICK_GROUPS = {
'viewer': '',
}
-
# -----------------------------------------------------------------------------
# File I/O
# -----------------------------------------------------------------------------
@@ -69,6 +68,11 @@ DIR_STATIC_SITE_VIEWER = join(DIR_DATA_STORE, 'exports/animism')
HASH_TREE_DEPTH = 3 # for sha256 subdirs
HASH_BRANCH_SIZE = 3 # for sha256 subdirs
+# -----------------------------------------------------------------------------
+# Database
+# -----------------------------------------------------------------------------
+
+USE_SQLITE = os.getenv("USE_SQLITE") == "True"
# -----------------------------------------------------------------------------
# S3 storage
@@ -91,7 +95,7 @@ HTTP_EXTERNAL_HOST = os.getenv('HTTP_EXTERNAL_HOST') or f"http://{SERVER_NAME}"
# -----------------------------------------------------------------------------
UCODE_OK = u"\u2714" # check ok
-UCODE_NOK = u'\u2718' # x no ok
+UCODE_NOK = u"\u2718" # x no ok
# -----------------------------------------------------------------------------
# Vimeo API
diff --git a/animism-align/cli/app/sql/common.py b/animism-align/cli/app/sql/common.py
index 10deda3..0a9a158 100644
--- a/animism-align/cli/app/sql/common.py
+++ b/animism-align/cli/app/sql/common.py
@@ -2,7 +2,6 @@ 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
@@ -11,16 +10,16 @@ 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, 'animism.sqlite3'))
+if app_cfg.USE_SQLITE:
+ os.makedirs(app_cfg.DIR_DATABASE, exist_ok=True)
+ connection_url = "sqlite:///{}".format(os.path.join(app_cfg.DIR_DATABASE, 'animism.sqlite3'))
+else:
+ connection_url = "mysql+pymysql://{}:{}@{}/{}?charset=utf8mb4".format(
+ os.getenv("DB_USER"),
+ os.getenv("DB_PASS"),
+ os.getenv("DB_HOST"),
+ os.getenv("DB_NAME")
+ )
engine = create_engine(connection_url, encoding="utf-8", pool_recycle=3600)
diff --git a/animism-align/cli/app/sql/models/annotation.py b/animism-align/cli/app/sql/models/annotation.py
index a22c941..7363d16 100644
--- a/animism-align/cli/app/sql/models/annotation.py
+++ b/animism-align/cli/app/sql/models/annotation.py
@@ -14,7 +14,7 @@ class Annotation(Base):
__tablename__ = 'annotation'
id = Column(Integer, primary_key=True)
type = Column(String(16, convert_unicode=True), nullable=False)
- paragraph_id = Column(Integer, ForeignKey('paragraph.id'), nullable=True)
+ paragraph_id = Column(Integer, nullable=True)
start_ts = Column(Float, nullable=False)
end_ts = Column(Float, nullable=True)
text = Column(Text(convert_unicode=True), nullable=True)
diff --git a/animism-align/cli/app/sql/versions/202007041642_create_database.py b/animism-align/cli/app/sql/versions/202007041642_create_database.py
index bec0cc3..10805e2 100644
--- a/animism-align/cli/app/sql/versions/202007041642_create_database.py
+++ b/animism-align/cli/app/sql/versions/202007041642_create_database.py
@@ -44,7 +44,7 @@ def upgrade():
sa.Column('end_ts', sa.Float(), nullable=True),
sa.Column('text', sa.Text(_expect_unicode=True), nullable=True),
sa.Column('settings', sa.JSON(), nullable=True),
- sa.ForeignKeyConstraint(['paragraph_id'], ['paragraph.id'], ),
+ # sa.ForeignKeyConstraint(['paragraph_id'], ['paragraph.id'], ),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
diff --git a/animism-align/cli/commands/site/migrate_to_mysql.py b/animism-align/cli/commands/site/migrate_to_mysql.py
new file mode 100644
index 0000000..51fa1f5
--- /dev/null
+++ b/animism-align/cli/commands/site/migrate_to_mysql.py
@@ -0,0 +1,148 @@
+import click
+import os
+import glob
+import time
+
+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
+
+@click.command('migrate')
+@click.pass_context
+def cli(ctx):
+ """
+ - Create connections to both databases
+ - For each table, for each row, insert from one to the other
+ """
+ mysql_session, mysql_base = make_mysql_base()
+ sqlite_session, sqlite_base = make_sqlite3_base()
+ mysql_classes = make_classes(mysql_base)
+ sqlite_classes = make_classes(sqlite_base)
+
+ for mysql_class, sqlite_class in zip(mysql_classes, sqlite_classes):
+ sqlite_objs = sqlite_session.query(sqlite_class).order_by(sqlite_class.id).all()
+ for sqlite_obj in sqlite_objs:
+ mysql_obj = mysql_class()
+ for column in sqlite_class.__table__.columns:
+ table_name, column_name = str(column).split(".")
+ # print(f"{table_name} => {column_name}")
+ if column_name != 'id':
+ setattr(mysql_obj, column_name, getattr(sqlite_obj, column_name))
+ mysql_session.add(mysql_obj)
+ mysql_session.commit()
+
+def make_mysql_base():
+ """Make a Mysql connection"""
+ connection_url = "mysql+pymysql://{}:{}@{}/{}?charset=utf8mb4".format(
+ os.getenv("DB_USER"),
+ os.getenv("DB_PASS"),
+ os.getenv("DB_HOST"),
+ os.getenv("DB_NAME")
+ )
+ return make_base(connection_url)
+
+def make_sqlite3_base():
+ """Make a SQLite3 connection"""
+ connection_url = "sqlite:///{}".format(os.path.join(app_cfg.DIR_DATABASE, 'animism.sqlite3'))
+ return make_base(connection_url)
+
+def make_base(connection_url):
+ """Make a connection base from a connection URL"""
+ engine = create_engine(connection_url, encoding="utf-8", pool_recycle=3600)
+ Session = sessionmaker(bind=engine)
+ Base = declarative_base()
+ Base.metadata.bind = engine
+ db = SQLAlchemy()
+ return Session(), Base
+
+def make_classes(Base):
+ """Make classes from a base"""
+
+ from sqlalchemy import create_engine, Table, Column, Text, String, Integer, \
+ Boolean, Float, DateTime, JSON, ForeignKey
+ from sqlalchemy_utc import UtcDateTime, utcnow
+
+ # from app.sql.common import db, Base, Session
+
+ class Episode(Base):
+ """Table for storing episodes and their metadata"""
+ __tablename__ = 'episode'
+ id = Column(Integer, primary_key=True)
+ episode_number = Column(Integer)
+ title = Column(String(256, convert_unicode=True), nullable=False)
+ release_date = Column(String(256, convert_unicode=True))
+ is_live = Column(Boolean, default=False)
+ settings = Column(JSON, default={}, nullable=True)
+
+ class Annotation(Base):
+ """Table for storing references to annotations"""
+ __tablename__ = 'annotation'
+ id = Column(Integer, primary_key=True)
+ type = Column(String(16, convert_unicode=True), nullable=False)
+ paragraph_id = Column(Integer, nullable=True)
+ start_ts = Column(Float, nullable=False)
+ end_ts = Column(Float, nullable=True)
+ text = Column(Text(convert_unicode=True), nullable=True)
+ settings = Column(JSON, default={}, nullable=True)
+
+ class Media(Base):
+ """Table for storing references to media"""
+ __tablename__ = 'media'
+ id = Column(Integer, primary_key=True)
+ type = Column(String(16, convert_unicode=True), nullable=False)
+ tag = Column(String(64, convert_unicode=True), nullable=True)
+ url = Column(String(256, convert_unicode=True), nullable=True)
+ title = Column(String(256, convert_unicode=True), nullable=True)
+ author = Column(String(256, convert_unicode=True), nullable=True)
+ pre_title = Column(String(256, convert_unicode=True), nullable=True)
+ post_title = Column(String(256, convert_unicode=True), nullable=True)
+ translated_title = Column(String(256, convert_unicode=True), nullable=True)
+ date = Column(String(256, convert_unicode=True), nullable=True)
+ source = Column(String(256, convert_unicode=True), nullable=True)
+ medium = Column(String(64, convert_unicode=True), nullable=True)
+ description = Column(Text(convert_unicode=True), nullable=True)
+ start_ts = Column(Float, nullable=True)
+ settings = Column(JSON, default={}, nullable=True)
+
+ class Paragraph(Base):
+ """Table for storing paragraphs, which contain annotations"""
+ __tablename__ = 'paragraph'
+ id = Column(Integer, primary_key=True)
+ type = Column(String(16, convert_unicode=True), nullable=False)
+ start_ts = Column(Float, nullable=False)
+ end_ts = Column(Float, nullable=True)
+ settings = Column(JSON, default={}, nullable=True)
+
+ class Upload(Base):
+ """Table for storing references to various media"""
+ __tablename__ = 'upload'
+ id = Column(Integer, primary_key=True)
+ sha256 = Column(String(256), nullable=False)
+ fn = Column(String(256), nullable=False)
+ ext = Column(String(4, convert_unicode=True), nullable=False)
+ tag = Column(String(64, convert_unicode=True), nullable=True)
+ username = Column(String(16, convert_unicode=True), nullable=False)
+ created_at = Column(UtcDateTime(), default=utcnow())
+
+ class User(Base):
+ """Table for storing the user list"""
+ __tablename__ = 'user'
+ id = Column(Integer, primary_key=True)
+ username = Column(String(256, convert_unicode=True), nullable=False)
+ password = Column(String(256, convert_unicode=True), nullable=False)
+ is_admin = Column(Boolean, default=False)
+ settings = Column(JSON, default={}, nullable=True)
+
+ class Venue(Base):
+ """Table for storing the venue list"""
+ __tablename__ = 'venue'
+ id = Column(Integer, primary_key=True)
+ title = Column(String(256, convert_unicode=True), nullable=False)
+ date = Column(String(256, convert_unicode=True), nullable=False)
+ settings = Column(JSON, default={}, nullable=True)
+
+ return [ Episode, Paragraph, Annotation, Media, Upload, User, Venue ]
generated by cgit v1.2.3-70-g09d2 (git 2.51.0) at 2025-12-10 06:40:07 +0000