migrate from sqlite to mysql. leave off foreignkey constraint

4 files changed, 18 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 ###