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)
