class BigInteger(types.TypeDecorator): impl = types.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') @property def python_type(self): return int def __repr__(self): return 'BigInteger()'
class Historial(db.Model): __tablename__ = 'historial' id = db.Column(db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite'), primary_key=True, autoincrement=True) ts = db.Column(db.DateTime(timezone=True), server_default=func.now()) trigger = db.Column(db.String(50)) mensaje = db.Column(db.String(50))
class Estado(db.Model): __tablename__ = 'pines' id = db.Column(db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite'), primary_key=True, autoincrement=True) pin = db.Column(db.Integer, nullable=False, unique=True) laston = db.Column(db.DateTime(timezone=True), server_default=func.now()) lastoff = db.Column(db.DateTime(timezone=True), server_default=func.now()) curval = db.Column(db.Integer, nullable=False, default=0)
class Timers(db.Model): __tablename__ = 'timers' id = db.Column(db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite'), primary_key=True, autoincrement=True) pin = db.Column(db.Integer, nullable=False) timeon = db.Column(db.Integer, nullable=False, default=60) timeoff = db.Column(db.Integer, nullable=False, default=0) repeat = db.Column(db.Integer, nullable=False, default=0)
class MarketOrder(SqlBase, CrestSqlInterface): __tablename__ = "market_order" # sqlite doesn't support BigInteger auto-increment primary keys. Map it to Integer, just for testing BigInt = BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') db_id = Column(BigInt, primary_key=True) buy = Column(Boolean, nullable=False) issued = Column(DateTime, nullable=False) price = Column(Float, nullable=False) volume = Column(Integer, nullable=False) duration = Column(Integer, nullable=False) minVolume = Column(Integer, nullable=False) volumeEntered = Column(Integer, nullable=False) range = Column(String(30, convert_unicode='force', unicode_error='backslashreplace'), nullable=False) id = Column(BigInteger, nullable=False) stationID = Column(BigInteger, ForeignKey('station.id'), nullable=False) r_station = relationship("Station") # item_id is "type" in this object type = Column(Integer, ForeignKey('item.id'), nullable=False) r_item = relationship("Item") capture_time = Column(DateTime, nullable=False) @classmethod def get_objects_from_crest(cls, crest_connection, **kwargs): if 'region' not in kwargs: raise AttributeError() region_crest = kwargs['region'].get_crest_item_by_attr( crest_connection, 'id', kwargs['region'].id) return region_crest().marketOrdersAll().items @classmethod def new_object_from_crest(cls, crest, **kwargs): date = cls.string_to_datetime(getattr(crest, 'issued')) setattr(crest, 'issued', date) new_obj = super().new_object_from_crest(crest, **kwargs) new_obj.capture_time = datetime.now() return new_obj
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 datetime import datetime from sqlalchemy import UniqueConstraint from sqlalchemy.dialects.postgresql import MACADDR from sqlalchemy.ext.mutable import MutableList, MutableDict from sqlalchemy.orm import relationship, composite, deferred, backref from sqlalchemy.dialects import sqlite from dal import db from dal.shared import ModelIter, Point from config import configs # sqlite is used for testing. this adds compatibility from dal.user import Commentable, UserGroup BigInteger = db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') SmallInteger = db.SmallInteger().with_variant(sqlite.INTEGER(), 'sqlite') MacAddress = MACADDR().with_variant(db.String, 'sqlite') # many to many associations class InstallationPanelModel(db.Model, ModelIter): __tablename__ = 'installations_panel_models' id = db.Column(db.Integer, primary_key=True) installation_id = deferred( db.Column(db.Integer, db.ForeignKey('installations.id'), index=True)) model_id = deferred( db.Column(db.Integer, db.ForeignKey('panel_models.id'), index=True)) quantity = db.Column(db.Integer, nullable=False) serials = db.Column(
log = logging.getLogger(__name__) metadata = MetaData() # for SQLite warnings.filterwarnings('ignore', '.*does \*not\* support Decimal*.') # These are keywords that all tables should have table_keywords = {} table_keywords['mysql_charset'] = 'latin1' table_keywords['mysql_engine'] = 'InnoDB' KeyInteger = BigInteger() KeyInteger = KeyInteger.with_variant(postgresql.BIGINT(), 'postgresql') KeyInteger = KeyInteger.with_variant(mysql.BIGINT(), 'mysql') KeyInteger = KeyInteger.with_variant(sqlite.INTEGER(), 'sqlite') # -------------------------------------------------------------------- # Method to verify if tables exist or are according to the schema # -------------------------------------------------------------------- def get_missing_tables(db): tables = [ db_version, # WORKFLOW st_workflow, st_workflowstate, st_workflow_meta, st_workflow_files, st_host, st_job,
from sqlalchemy import BigInteger from sqlalchemy import Boolean, Column, DateTime, Float from sqlalchemy import ForeignKey, Integer, String, Enum from sqlalchemy.dialects import sqlite from sqlalchemy.orm import relation, backref from base import Base, Json FileActiveStates = ['STAGING', 'STARTED', 'SUBMITTED', 'READY', 'ACTIVE'] FileTerminalStates = ['FINISHED', 'FAILED', 'CANCELED'] # NOT_USED is not terminal, nor not-terminal FileOnHoldStates = ['NOT_USED', 'ON_HOLD', 'ON_HOLD_STAGING'] # sqlite doesn't like auto increment with BIGINT, so we need to use a variant # on that case FileId = BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') class File(Base): __tablename__ = 't_file' file_id = Column(FileId, primary_key=True) hashed_id = Column(Integer) file_index = Column(Integer) job_id = Column(String(36), ForeignKey('t_job.job_id')) vo_name = Column(String(50)) source_se = Column(String(255)) dest_se = Column(String(255)) file_state = Column( Enum(*(FileActiveStates + FileTerminalStates + FileOnHoldStates))) transfer_host = Column(String(255))
""" Database object shortcuts """ import pytz import sqlalchemy from sqlalchemy import types from sqlalchemy.dialects import sqlite from app import db from dateutil import parser # alias common names BigInteger = db.BigInteger().with_variant( sqlite.INTEGER(), 'postgresql') # type: sqlalchemy.types.BigInteger Boolean = db.Boolean # type: sqlalchemy.types.Boolean Date = db.Date # type: sqlalchemy.types.Date Enum = db.Enum # type: sqlalchemy.types.Enum Float = db.Float # type: sqlalchemy.types.Float ForeignKey = db.ForeignKey # type: sqlalchemy.schema.ForeignKey Integer = db.Integer # type: sqlalchemy.types.Integer Interval = db.Interval # type: sqlalchemy.types.Interval Numeric = db.Numeric # type: sqlalchemy.types.Numeric SmallInteger = db.SmallInteger # type: sqlalchemy.types.SmallInteger String = db.String # type: sqlalchemy.types.String Text = db.Text # type: sqlalchemy.types.Text Time = db.Time # type: sqlalchemy.types.Time class DateTime(types.TypeDecorator): impl = types.DateTime def __init__(self, *args, **kwargs):
import uuid import pytz import sqlalchemy from sqlalchemy import types from sqlalchemy.dialects import postgresql, sqlite from backend.extensions import db # alias common names BigInteger = db.BigInteger().with_variant( sqlite.INTEGER(), 'sqlite') # type: sqlalchemy.types.BigInteger Boolean = db.Boolean # type: sqlalchemy.types.Boolean Date = db.Date # type: sqlalchemy.types.Date Enum = db.Enum # type: sqlalchemy.types.Enum Float = db.Float # type: sqlalchemy.types.Float ForeignKey = db.ForeignKey # type: sqlalchemy.schema.ForeignKey Integer = db.Integer # type: sqlalchemy.types.Integer Interval = db.Interval # type: sqlalchemy.types.Interval Numeric = db.Numeric # type: sqlalchemy.types.Numeric SmallInteger = db.SmallInteger # type: sqlalchemy.types.SmallInteger String = db.String # type: sqlalchemy.types.String Text = db.Text # type: sqlalchemy.types.Text Time = db.Time # type: sqlalchemy.types.Time class DateTime(types.TypeDecorator): impl = types.DateTime def __init__(self, *args, **kwargs): kwargs['timezone'] = True
#!/usr/bin/env python # -*- coding: utf-8 -*- # Common Python library imports # Pip package imports import pytz import sqlalchemy from sqlalchemy import types from sqlalchemy.dialects import sqlite # Internal package imports from backend.extensions import db # alias common names BigInteger = db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') # type: sqlalchemy.types.BigInteger Boolean = db.Boolean # type: sqlalchemy.types.Boolean Date = db.Date # type: sqlalchemy.types.Date Enum = db.Enum # type: sqlalchemy.types.Enum Float = db.Float # type: sqlalchemy.types.Float ForeignKey = db.ForeignKey # type: sqlalchemy.schema.ForeignKey Integer = db.Integer # type: sqlalchemy.types.Integer Interval = db.Interval # type: sqlalchemy.types.Interval Numeric = db.Numeric # type: sqlalchemy.types.Numeric SmallInteger = db.SmallInteger # type: sqlalchemy.types.SmallInteger String = db.String # type: sqlalchemy.types.String Text = db.Text # type: sqlalchemy.types.Text Time = db.Time # type: sqlalchemy.types.Time class DateTime(types.TypeDecorator):
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)
created 28-jul-2018 by [email protected] license: lgpl-2.1 """ # coding: utf-8 from sqlalchemy import BIGINT, BOOLEAN, Column, Enum, Float, ForeignKey, \ INTEGER, Index, String, TIMESTAMP, text, VARBINARY from sqlalchemy import func from sqlalchemy.dialects import sqlite from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata BigIntId = BIGINT().with_variant(sqlite.INTEGER(), 'sqlite') class ConfigTable(Base): __tablename__ = 'config' __table_args__ = (Index('index1', 'keyword', 'host_id', unique=True), ) id = Column(INTEGER, primary_key=True, unique=True, autoincrement=True) keyword = Column(String(32), nullable=False) value = Column(String(1023), nullable=True) created = Column(TIMESTAMP, nullable=False, server_default=func.now()) host_id = Column(ForeignKey(u'hosts.id'), nullable=False, index=True) host = relationship('Host')
import xml.etree.ElementTree from sqlalchemy import BigInteger from sqlalchemy.dialects import sqlite # without setting this explicitly, we get a warning that this option # will default to disabled in future versions (due to incurring a lot # of overhed). We aren't using the relevant functionality, so let's # just opt-in to the change now: app.config.update(SQLALCHEMY_TRACK_MODIFICATIONS=False) db = SQLAlchemy(app) # Sets up variant type so that postgresql can use BIGINT primary keys # while sqlite uses Integer primary keys. BigIntegerType = BigInteger().with_variant( sqlite.INTEGER(), 'sqlite') def init_db(uri=None): """Start up the DB connection. `uri` is the uri to use for the database. If it is None, the uri from the config file will be used. """ if uri is None: uri = cfg.get('database', 'uri') app.config.update(SQLALCHEMY_DATABASE_URI=uri) # A joining table for project's access to networks, which have a many to many # relationship:
import uuid import xml.etree.ElementTree from sqlalchemy import BigInteger from sqlalchemy.dialects import sqlite # without setting this explicitly, we get a warning that this option # will default to disabled in future versions (due to incurring a lot # of overhed). We aren't using the relevant functionality, so let's # just opt-in to the change now: app.config.update(SQLALCHEMY_TRACK_MODIFICATIONS=False) db = SQLAlchemy(app) # Sets up variant type so that postgresql can use BIGINT primary keys # while sqlite uses Integer primary keys. BigIntegerType = BigInteger().with_variant(sqlite.INTEGER(), 'sqlite') def init_db(uri=None): """Start up the DB connection. `uri` is the uri to use for the database. If it is None, the uri from the config file will be used. """ if uri is None: uri = cfg.get('database', 'uri') app.config.update(SQLALCHEMY_DATABASE_URI=uri) # A joining table for project's access to networks, which have a many to many # relationship:
""" 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)