from sqlalchemy.dialects import postgresql, mysql, sqlite
from sqlalchemy import func
Base = declarative_base()
from sqlalchemy.dialects import registry
registry.register('spatialite', 'ambry.orm.dialects.spatialite',
'SpatialiteDialect')
registry.register('postgis', 'ambry.orm.dialects.postgis', 'PostgisDialect')
# http://stackoverflow.com/a/23175518/1144479
# SQLAlchemy does not map BigInt to Int by default on the sqlite dialect.
# It should, but it doesnt.
BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql')
BigIntegerType = BigIntegerType.with_variant(mysql.BIGINT(), 'mysql')
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')
class Geometry(UserDefinedType):
"""Geometry type, to ensure that WKT text is properly inserted into the
database with the GeomFromText() function.
NOTE! This is paired with code in
database.relational.RelationalDatabase.table() to convert NUMERIC
fields that have the name 'geometry' to GEOMETRY types. Sqlalchemy
sees spatialte GEOMETRY types as NUMERIC
"""
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import UUIDType, JSONType
from sqlalchemy import BigInteger, Column, DateTime
from sqlalchemy.sql import func
import sqlalchemy
from sqlalchemy.dialects import sqlite, postgresql
from sqlalchemy.orm.attributes import flag_modified
BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), "postgresql")
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), "sqlite")
DeclarativeBase = declarative_base()
PkType = BigIntegerType
ExtPkType = UUIDType(binary=False)
import uuid
from typing import Optional
class Base(DeclarativeBase): # type: ignore
__abstract__ = True
id = Column(PkType, primary_key=True)
ext_id = Column(ExtPkType,
default=lambda: uuid.uuid4(),
nullable=False,
unique=True)
created_at = Column(DateTime, server_default=func.now())
from sqlalchemy.dialects import sqlite
from sqlalchemy import Column, Integer, BigInteger, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import DateTime
from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlalchemy.orm.exc import NoResultFound
# SO much boilerplate
BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), "sqlite")
# create a custom utcnow function
class utcnow(expression.FunctionElement):
type = DateTime()
@compiles(utcnow, "sqlite")
def sqlite_utcnow(element, compiler, **kw):
return "CURRENT_TIMESTAMP"
class AppMixin(object):
"""
Provide common attributes to our models
from sqlalchemy import BigInteger
from sqlalchemy.dialects import postgresql, mysql, sqlite
KeyInteger = BigInteger()
KeyInteger = KeyInteger.with_variant(postgresql.BIGINT(), 'postgresql')
KeyInteger = KeyInteger.with_variant(mysql.BIGINT(), 'mysql')
KeyInteger = KeyInteger.with_variant(sqlite.INTEGER(), 'sqlite')
class SABase(object):
"""
Base class for all the DB mapper objects.
"""
def _commit(self, session, batch, merge=False):
if merge:
session.merge(self)
else:
session.add(self)
if batch:
return
session.flush()
session.commit()
def commit_to_db(self, session, batch=False):
"""
Commit the DB object/row to the database.
@type session: sqlalchemy.orm.scoping.ScopedSession object
@param session: SQLAlch session to commit row to.
"""
self._commit(session, batch)
from sqlalchemy import BigInteger from sqlalchemy.dialects import sqlite # sqlite does not allow BigInteger as a primary key with autoincrement. Use an integer for sqlite (local testing) # but BigInteger elsewhere BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')
""" SQLAlchemy Database Models used to generate and update db tables """
from . import db
from sqlalchemy import BigInteger
from sqlalchemy.dialects import postgresql, sqlite
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime
from application import UNDERGRAD_MAJORS as majors
# Fix BigInteger not working in sqlite but still works with postgresql
BigInt = BigInteger()
BigInt = BigInt.with_variant(postgresql.BIGINT(), 'postgresql')
BigInt = BigInt.with_variant(sqlite.INTEGER(), 'sqlite')
class User(UserMixin, db.Model):
"""Model for user accounts."""
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64),
index=False,
unique=False,
nullable=False)
email = db.Column(db.String(80), index=True, unique=True, nullable=False)
password = db.Column(db.String(128),
index=False,
unique=False,
nullable=False)
admin = db.Column(db.Boolean(), default=False)
from sqlalchemy import BigInteger, Integer, Column, DateTime, func
from sqlalchemy.dialects import postgresql, sqlite
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql')
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')
_sessionmaker = sessionmaker()
session = scoped_session(_sessionmaker)
class CommonColumnsMixin():
""" Some common attributes for tables. """
id = Column(Integer, primary_key=True)
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
# post-contractual effects is governed by the laws of Austria. Any disputes
# concerning this License-Agreement including the issue of its valid conclusion
# and its pre- and post-contractual effects are exclusively decided by the
# competent court, in whose district STRG.AT GmbH has its registered seat, at
# the discretion of STRG.AT GmbH also the competent court, in whose district the
# Licensee has his registered seat, an establishment or assets.
import re
from sqlalchemy import Column, ForeignKey, BigInteger, Integer, UniqueConstraint
from sqlalchemy.orm import backref, relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.orderinglist import ordering_list
IdType = BigInteger()
IdType = IdType.with_variant(Integer, 'sqlite')
# taken from stackoverflow:
# http://stackoverflow.com/a/1176023/44562
_first_cap_re = re.compile('(.)([A-Z][a-z]+)')
_all_cap_re = re.compile('([a-z0-9])([A-Z])')
def cls2tbl(cls):
"""
Converts a class (or a class name) to a table name. The class name is
expected to be in *CamelCase*. The return value will be
*seperated_by_underscores* and prefixed with an underscore. Omitting
the underscore will yield the name of the class's :ref:`view <db_view>`.
"""
if isinstance(cls, type):
cls = cls.__name__
from sqlalchemy import Integer
from sqlalchemy import BigInteger
from sqlalchemy import Numeric
from sqlalchemy import Boolean
from sqlalchemy import ForeignKey
from sqlalchemy import DateTime
from sqlalchemy import UnicodeText
from sqlalchemy.orm import relationship
from datetime import datetime
from contextlib import contextmanager
UnsignedBigInteger = BigInteger()
UnsignedBigInteger = UnsignedBigInteger.with_variant(
mysql.BIGINT(unsigned=True), 'mysql')
UnsignedBigInteger = UnsignedBigInteger.with_variant(sqlite.INTEGER(),
'sqlite')
def fk_guid(constraint, table):
str_tokens = [
table.name,
] + [element.parent.name for element in constraint.elements
] + [element.target_fullname for element in constraint.elements]
print(f'str_tokens: {str_tokens}')
guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens))
return str(guid)
