diff options
| -rw-r--r-- | cli/app/settings/app_cfg.py | 6 | ||||
| -rw-r--r-- | cli/app/sql/common.py | 23 | ||||
| -rw-r--r-- | cli/app/sql/env.py | 2 | ||||
| -rw-r--r-- | cli/app/sql/models/graph.py | 3 | ||||
| -rw-r--r-- | cli/app/sql/models/upload.py | 4 | ||||
| -rw-r--r-- | cli/app/sql/versions/202103161645_add_foreign_key_constraint.py | 29 | ||||
| -rw-r--r-- | cli/commands/admin/migrate_to_mysql.py | 120 | ||||
| -rw-r--r-- | docs/migrate_to_mysql.md | 1 | ||||
| -rw-r--r-- | env-sample | 7 | ||||
| -rw-r--r-- | frontend/app/views/index/containers/graph.index.js | 1 |
10 files changed, 181 insertions, 15 deletions
diff --git a/cli/app/settings/app_cfg.py b/cli/app/settings/app_cfg.py index 3865abd..af6cf89 100644 --- a/cli/app/settings/app_cfg.py +++ b/cli/app/settings/app_cfg.py @@ -33,6 +33,7 @@ load_dotenv(dotenv_path=fp_env) CLICK_GROUPS = { # 'process': 'commands/process', 'site': 'commands/site', + 'admin': 'commands/admin', 'db': '', 'flask': '', } @@ -65,6 +66,11 @@ HASH_BRANCH_SIZE = 3 # for sha256 subdirs DIR_PUBLIC_EXPORTS = os.getenv('DIR_PUBLIC_EXPORTS') or DIR_EXPORTS +# ----------------------------------------------------------------------------- +# Database +# ----------------------------------------------------------------------------- + +USE_SQLITE = os.getenv("USE_SQLITE") == "True" # ----------------------------------------------------------------------------- # S3 storage diff --git a/cli/app/sql/common.py b/cli/app/sql/common.py index c8bd557..8e1d2b3 100644 --- a/cli/app/sql/common.py +++ b/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, 'swimmer.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, 'swimmer.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) @@ -31,7 +30,7 @@ Base.metadata.bind = engine db = SQLAlchemy() # include the models in reverse dependency order, so relationships work +from app.sql.models.upload import Upload from app.sql.models.tile import Tile from app.sql.models.page import Page from app.sql.models.graph import Graph -from app.sql.models.upload import Upload diff --git a/cli/app/sql/env.py b/cli/app/sql/env.py index 7753565..3e015b5 100644 --- a/cli/app/sql/env.py +++ b/cli/app/sql/env.py @@ -14,10 +14,10 @@ config.set_main_option("sqlalchemy.url", connection_url) target_metadata = Base.metadata # include the models in reverse dependency order, so relationships work +from app.sql.models.upload import Upload from app.sql.models.tile import Tile from app.sql.models.page import Page from app.sql.models.graph import Graph -from app.sql.models.upload import Upload def run_migrations_offline(): """Run migrations in 'offline' mode. diff --git a/cli/app/sql/models/graph.py b/cli/app/sql/models/graph.py index 8e068a0..08f4d3c 100644 --- a/cli/app/sql/models/graph.py +++ b/cli/app/sql/models/graph.py @@ -23,6 +23,7 @@ class Graph(Base): updated_at = Column(UtcDateTime(), onupdate=utcnow()) pages = relationship('Page', lazy='dynamic') + uploads = relationship('Upload', lazy='dynamic') def toJSON(self): return { @@ -40,11 +41,13 @@ class Graph(Base): def toFullJSON(self): data = self.toJSON() data['pages'] = [ page.toLinkJSON() for page in self.pages ] + data['uploads'] = [ upload.toJSON() for upload in self.uploads ] return data def toSiteJSON(self): data = self.toJSON() data['pages'] = [ page.toFullJSON() for page in self.pages ] + data['uploads'] = [ upload.toJSON() for upload in self.uploads ] return data class GraphForm(ModelForm): diff --git a/cli/app/sql/models/upload.py b/cli/app/sql/models/upload.py index 87f758a..30e53dc 100644 --- a/cli/app/sql/models/upload.py +++ b/cli/app/sql/models/upload.py @@ -1,4 +1,4 @@ -from sqlalchemy import create_engine, Table, Column, String, Integer, DateTime +from sqlalchemy import create_engine, Table, Column, ForeignKey, String, Integer, DateTime import sqlalchemy.sql.functions as func from sqlalchemy_utc import UtcDateTime, utcnow from wtforms_alchemy import ModelForm @@ -14,7 +14,7 @@ class Upload(Base): """Table for storing references to various media""" __tablename__ = 'upload' id = Column(Integer, primary_key=True) - graph_id = Column(Integer) + graph_id = Column(Integer, ForeignKey('graph.id'), nullable=True) sha256 = Column(String(256), nullable=False) fn = Column(String(256), nullable=False) ext = Column(String(4, convert_unicode=True), nullable=False) diff --git a/cli/app/sql/versions/202103161645_add_foreign_key_constraint.py b/cli/app/sql/versions/202103161645_add_foreign_key_constraint.py new file mode 100644 index 0000000..673f9e4 --- /dev/null +++ b/cli/app/sql/versions/202103161645_add_foreign_key_constraint.py @@ -0,0 +1,29 @@ +"""add foreign key constraint + +Revision ID: 3f7df6bf63b8 +Revises: 645f315e651d +Create Date: 2021-03-16 16:45:39.455892 + +""" +from alembic import op +import sqlalchemy as sa +import sqlalchemy_utc + + +# revision identifiers, used by Alembic. +revision = '3f7df6bf63b8' +down_revision = '645f315e651d' +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.create_foreign_key(None, 'upload', 'graph', ['graph_id'], ['id']) + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.drop_constraint(None, 'upload', type_='foreignkey') + # ### end Alembic commands ### diff --git a/cli/commands/admin/migrate_to_mysql.py b/cli/commands/admin/migrate_to_mysql.py new file mode 100644 index 0000000..32ff7cf --- /dev/null +++ b/cli/commands/admin/migrate_to_mysql.py @@ -0,0 +1,120 @@ +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_to_mysql') +@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 + + class Upload(Base): + """Table for storing references to various media""" + __tablename__ = 'upload' + id = Column(Integer, primary_key=True) + graph_id = Column(Integer, ForeignKey('graph.id'), nullable=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 Tile(Base): + """Table for storing references to tiles""" + __tablename__ = 'tile' + id = Column(Integer, primary_key=True) + graph_id = Column(Integer, ForeignKey('graph.id'), nullable=True) + page_id = Column(Integer, ForeignKey('page.id'), nullable=True) + target_page_id = Column(Integer, ForeignKey('page.id'), nullable=True) + type = Column(String(16, convert_unicode=True), nullable=False) + sort_order = Column(Integer, default=0) + settings = Column(JSON, default={}, nullable=True) + created_at = Column(UtcDateTime(), default=utcnow()) + updated_at = Column(UtcDateTime(), onupdate=utcnow()) + + class Page(Base): + """Table for storing references to pages""" + __tablename__ = 'page' + id = Column(Integer, primary_key=True) + graph_id = Column(Integer, ForeignKey('graph.id'), nullable=True) + path = Column(String(64, convert_unicode=True), nullable=False) + title = Column(String(64, convert_unicode=True), nullable=False) + username = Column(String(32, convert_unicode=True), nullable=False) + description = Column(Text(convert_unicode=True), nullable=False) + settings = Column(JSON, default={}, nullable=True) + created_at = Column(UtcDateTime(), default=utcnow()) + updated_at = Column(UtcDateTime(), onupdate=utcnow()) + + class Graph(Base): + """Table for storing references to graphs""" + __tablename__ = 'graph' + id = Column(Integer, primary_key=True) + home_page_id = Column(Integer, nullable=True) + path = Column(String(64, convert_unicode=True), nullable=False) + title = Column(String(64, convert_unicode=True), nullable=False) + username = Column(String(32, convert_unicode=True), nullable=False) + description = Column(Text(convert_unicode=True), nullable=False) + settings = Column(JSON, default={}, nullable=True) + created_at = Column(UtcDateTime(), default=utcnow()) + updated_at = Column(UtcDateTime(), onupdate=utcnow()) + + return [ Upload, Graph, Page, Tile ] diff --git a/docs/migrate_to_mysql.md b/docs/migrate_to_mysql.md new file mode 100644 index 0000000..45fe7de --- /dev/null +++ b/docs/migrate_to_mysql.md @@ -0,0 +1 @@ +migrate_to_mysql.md @@ -2,3 +2,10 @@ FLASK_RUN_PORT=7500 SERVER_NAME=swim.neural.garden HTTP_EXTERNAL_HOST=https://swim.neural.garden +USE_SQLITE=False + +DB_USER=swimmer +DB_PASS= +DB_HOST=localhost +DB_NAME=swimmer + diff --git a/frontend/app/views/index/containers/graph.index.js b/frontend/app/views/index/containers/graph.index.js index 91098a7..bf3d75e 100644 --- a/frontend/app/views/index/containers/graph.index.js +++ b/frontend/app/views/index/containers/graph.index.js @@ -11,6 +11,7 @@ class GraphIndex extends Component { componentDidMount() { actions.graph.index() } + render() { const { index } = this.props // console.log(this.props) |