class Subscription(Base):
"""Model class to represent ATT subscriptions"""
__tablename__ = "subscriptions"
id = Column(Integer, primary_key=True)
phone_number = Column(String(50))
status = Column(ENUM(SubscriptionStatus), default=SubscriptionStatus.new)
activation_date = Column(TIMESTAMP(timezone=True), nullable=True)
expiry_date = Column(TIMESTAMP(timezone=True), nullable=True)
plan_id = Column(Integer, ForeignKey('plans.id'))
plan = relationship("Plan", back_populates="plans")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id} ({self.status}), "
f"phone_number: {self.phone_number or '[no phone number]'}, ",
f"plan: {self.plan_id}>")
@classmethod
def get_subscriptions(cls, **kwargs):
"""Gets a list of Subscription objects using given kwargs
Generates query filters from kwargs param using base class method
Args:
kwargs: key value pairs to apply as filters
Returns:
list: objects returned from query result
"""
return cls.query.filter(**kwargs).all()
class Payday(db.Model):
__tablename__ = 'paydays'
__table_args__ = (UniqueConstraint('ts_end', name='paydays_ts_end_key'), )
# TODO: Move this to a different module?
EPOCH = datetime.datetime(1970, 1, 1, tzinfo=pytz.utc)
id = Column(Integer, nullable=False, primary_key=True)
ts_start = Column(TIMESTAMP(timezone=True),
nullable=False,
default="now()")
ts_end = Column(TIMESTAMP(timezone=True), nullable=False, default=EPOCH)
nparticipants = Column(Integer, default=0)
ntippers = Column(Integer, default=0)
ntips = Column(Integer, default=0)
ntransfers = Column(Integer, default=0)
transfer_volume = Column(Numeric(precision=35, scale=2), default=0.0)
ncc_failing = Column(Integer, default=0)
ncc_missing = Column(Integer, default=0)
ncharges = Column(Integer, default=0)
charge_volume = Column(Numeric(precision=35, scale=2), default=0.0)
charge_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0)
nachs = Column(Integer, default=0)
ach_volume = Column(Numeric(precision=35, scale=2), default=0.0)
ach_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0)
nach_failing = Column(Integer, default=0)
class Tip(db.Model):
__tablename__ = 'tips'
id = Column(Integer, nullable=False, primary_key=True)
ctime = Column(TIMESTAMP(timezone=True), nullable=False)
mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()")
tipper = Column(Text, ForeignKey("participants.id", onupdate="CASCADE",
ondelete="RESTRICT"), nullable=False)
tippee = Column(Text, ForeignKey("participants.id", onupdate="CASCADE",
ondelete="RESTRICT"), nullable=False)
amount = Column(Numeric(precision=35, scale=2), nullable=False)
class APIKey(db.Model):
__tablename__ = 'api_keys'
id = Column(Integer, nullable=False, primary_key=True)
ctime = Column(TIMESTAMP(timezone=True), nullable=False)
mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()")
participant = Column(Text,
ForeignKey("participants.username",
onupdate="CASCADE",
ondelete="RESTRICT"),
nullable=False)
api_key = Column(Text, nullable=True)
class Archive(Base):
"Archive"
__tablename__ = 'archive'
id = Column(BigInteger, primary_key=True)
messageid = Column(Unicode(255), index=True)
actions = Column(Unicode(255))
clientip = Column(Unicode(128))
date = Column(Date, index=True)
from_address = Column(Unicode(255), index=True)
from_domain = Column(Unicode(255), index=True)
headers = Column(UnicodeText)
hostname = Column(UnicodeText)
highspam = Column(SmallInteger, default=0, index=True)
rblspam = Column(SmallInteger, default=0)
saspam = Column(SmallInteger, default=0)
spam = Column(SmallInteger, default=0, index=True)
nameinfected = Column(SmallInteger, default=0)
otherinfected = Column(SmallInteger, default=0)
isquarantined = Column(SmallInteger, default=0, index=True)
isarchived = Column(SmallInteger, default=0, index=True)
sascore = Column(Float)
scaned = Column(SmallInteger, default=0, index=True)
size = Column(Integer)
blacklisted = Column(SmallInteger, default=0, index=True)
spamreport = Column(UnicodeText)
whitelisted = Column(SmallInteger, default=0, index=True)
subject = Column(UnicodeText)
time = Column(Time(timezone=True))
timestamp = Column(TIMESTAMP(timezone=True),
server_default=utcnow(),
index=True)
to_address = Column(Unicode(255), index=True)
to_domain = Column(Unicode(255), index=True)
virusinfected = Column(SmallInteger, default=0)
ts = Column(TIMESTAMP(timezone=True), server_default=utcnow())
msgfiles = Column(UnicodeText)
def __init__(self, messageid):
"init"
self.messageid = messageid
__mapper_args__ = {'order_by': timestamp}
@property
def isdangerous(self):
"Check if the message is dangerous"
if self.virusinfected or self.otherinfected or self.nameinfected:
return True
return False
class FileLedgerMixin(TableMixin):
"""Mixin for parsed file ledger (tracking) table, which
serves as accounting system for parsers. Designed to work with
FileMetadataMixin in the libadfir-parsers library.
"""
file_name = Column(DialectSpecificText(), nullable=False)
file_path = Column(DialectSpecificText(), nullable=False)
file_size = Column(Integer, nullable=False)
md5hash = Column(DialectSpecificText())
sha1hash = Column(DialectSpecificText())
sha2hash = Column(DialectSpecificText())
modify_time = Column(TIMESTAMP(timezone=True))
access_time = Column(TIMESTAMP(timezone=True))
create_time = Column(TIMESTAMP(timezone=True))
completed = Column(Boolean, index=True)
class FileLedger(BaseTable, ConcreteTableMixin):
'''
Parsed $MFT file ledger (tracking) table
'''
file_name = Column(String().with_variant(Text, 'postgresql'), nullable=False)
file_path = Column(String().with_variant(Text, 'postgresql'), nullable=False)
file_size = Column(BigInteger, nullable=False)
md5hash = Column(String().with_variant(Text, 'postgresql'))
sha1hash = Column(String().with_variant(Text, 'postgresql'))
sha2hash = Column(String().with_variant(Text, 'postgresql'), nullable=False)
modify_time = Column(TIMESTAMP(timezone=True))
access_time = Column(TIMESTAMP(timezone=True))
create_time = Column(TIMESTAMP(timezone=True))
completed = Column(Boolean, index=True)
entry_headers = relationship('EntryHeader', backref='file_ledger')
class Users(Base):
__tablename__ = 'forumusers'
uid = Column(String(50), primary_key=True) # user id
firstName = Column(String(200))
lastName = Column(String(100))
source = Column(String(100)) # URL of the user profile
colleagues = Column(Integer)
followers = Column(Integer)
following = Column(Integer)
numPosts = Column(Integer)
numVotes = Column(Integer)
numAwards = Column(Integer)
points = Column(Integer)
account = Column(String(50)) # account type: base, plus, pro
city = Column(String(100))
state = Column(String(50))
dateJoined = Column(DateTime) # creation date of the user account
seeking = Column(Text) # currently seeking
experience = Column(Text) # real estate experience
occupation = Column(String(767))
goals = Column(Text) # real estate goals
crawl_time = Column(TIMESTAMP(timezone=True),
server_default=func.now(),
onupdate=func.now())
class Metric(Base):
"""
This is an ORM constructor for the metrics table.
Using the ORM makes it easier to create and destroy the table, as well as
change to different backends. For example, to use sqlite for local development.
The table has the following columns:
uid = a unique auto increment row id
ts = timestamp supplied by the user via POST request
name = metric name
value = metric value
raw = copy of the original json POST request
"""
__tablename__ = 'metrics'
uid = Column(Integer, primary_key=True)
ts = Column(TIMESTAMP(timezone=True), index=True)
name = Column(String(length=512), index=True)
value = Column(String(length=512))
raw = Column(JSONB)
def asdict(self):
return dict(id=self.uid,
ts=str(self.ts),
name=self.name,
value=self.value,
raw=self.raw)
class TableMixin:
"""Mixin class for (non-view) tables."""
id = Column(Integer, primary_key=True)
created_at = Column(TIMESTAMP(timezone=True),
server_default=TimestampDefaultExpression(),
index=True)
__table_args__ = dict(mysql_engine='InnoDB', mysql_charset='utf8mb4')
class IndexerKillList(Base):
"Keyword kill list"
__tablename__ = 'indexer_killlist'
id = Column(BigInteger, primary_key=True)
ts = Column(TIMESTAMP(timezone=True), nullable=False, primary_key=True)
tablename = Column(Unicode(255), nullable=False, primary_key=True)
class User(db.Model):
__tablename__ = "users"
user_id = Column(Integer, primary_key=True)
username = Column(String(32))
registration_datetime = Column(TIMESTAMP(),
default=datetime.utcnow,
nullable=False)
class OrderDetailDAO(BaseModel):
__tablename__ = 'order_detail'
id = Column(Integer, primary_key=True)
order_id = Column(Integer)
food_id = Column(Integer)
amount = Column(Integer)
price = Column(DECIMAL(5, 2))
created_at = Column(TIMESTAMP(timezone=False), nullable=False, server_default=func.current_timestamp())
@hybrid_method
def add(self, session):
try:
self._commit(session, self)
return self
except Exception as e:
session.rollback()
raise e
@hybrid_method
def food(self, session):
return FoodDAO.byID(session, self.food_id)
@hybrid_method
def by_order_id(cls, session, order_id):
query = session.query(cls).filter(cls.order_id== order_id)
return query.all()
def test_support_sql_tz_naive_datetime_types(self):
column_name = "tz_naive_datetime"
tz_naive_types = (DateTime(timezone=False), TIMESTAMP(timezone=False))
for sql_type in tz_naive_types:
self.assertEqual(
GraphQLDateTime,
try_get_graphql_scalar_type(column_name, sql_type))
class AuditLog(Base):
"Audit Log items"
__tablename__ = 'auditlog'
id = Column(BigInteger, primary_key=True)
username = Column(Unicode(255))
category = Column(SmallInteger, default=1, index=True)
info = Column(UnicodeText)
hostname = Column(UnicodeText)
remoteip = Column(UnicodeText)
timestamp = Column(TIMESTAMP(timezone=True),
server_default=utcnow())
__mapper_args__ = {'order_by': timestamp}
def __init__(self, username, category, info, hostname,
remoteip, timestamp=None):
"init"
self.username = username
self.category = category
self.info = info
self.hostname = hostname
self.remoteip = remoteip
if timestamp:
self.timestamp = timestamp
# @property
def tojson(self):
"JSON friendly format"
return dict(username=self.username,
category=unicode(CATEGORY_MAP[self.category]),
info=self.info,
hostname=self.hostname,
remoteip=self.remoteip,
timestamp=str(self.timestamp.strftime('%Y-%m-%d %H:%M')))
def ingest(self, df, table_name):
if 'alertregions' in table_name:
comp_cols = ['geohash', 'time', 'expires', 'region']
update_cols = ['geohash', 'time', 'expires']
str_cols = ", ".join(update_cols)
update_or_string = self.query_string_interpolation(df, update_cols)
update_query = f"SELECT id, {str_cols} FROM api_data_alerts WHERE {update_or_string}"
alerts = pd.read_sql(con=self.engine, sql=update_query)
for index, item in alerts.iterrows():
idx_filter = df[(df['time'] == item['time'])
& (df['geohash'] == item['geohash']) &
(df['expires'] == item['expires'])].index
df.loc[idx_filter, 'alert_id'] = item['id']
elif 'alerts' in table_name:
comp_cols = ['geohash', 'time', 'expires']
else:
comp_cols = ['geohash', 'time']
df.drop_duplicates(subset=comp_cols, inplace=True)
delete_or_string = self.query_string_interpolation(df, comp_cols)
delete_query = f"DELETE FROM {table_name} WHERE id IN (SELECT id FROM {table_name} WHERE {delete_or_string})"
self.cursor.execute(delete_query)
self.conn.commit()
time_cols = [
col for col in df.columns
if 'time' in col.lower() or 'expires' in col.lower()
]
conv_dict = {col: TIMESTAMP(timezone=True) for col in time_cols}
df.to_sql(name=table_name,
con=self.engine,
if_exists='append',
index=False,
dtype=conv_dict,
chunksize=1000)
class Event(Base):
__tablename__ = 'events'
id = Column(
UUID(as_uuid=True),
primary_key=True,
# This requires the uuid-ossp extension
server_default=text('uuid_generate_v4()'))
service_id = Column(UUID(as_uuid=True),
ForeignKey('services.id'),
nullable=False)
# Index on the timestamp, BETWEEN
when = Column(TIMESTAMP(timezone=True), nullable=False)
status = Column(ENUM('up',
'down',
'limited',
name='status_enum',
create_type=False),
nullable=False)
description = Column(TEXT, nullable=False)
informational = Column(Boolean, nullable=False)
extra = Column(JSONB, nullable=False)
ix_events_when = Index(when.desc())
# service = relationship('Service', backref='events', cascade='delete, delete-orphan')
def __str__(self):
return f"{self.service} -> {self.status.upper()}"
class TrashTvArchillectHistory(Base):
"""DB Model for 'TRASH_TV_ARCHILLECT_HISTORY' db view."""
__tablename__ = "TRASH_TV_ARCHILLECT_HISTORY"
id = Column(AGNOSTIC_UUID(), primary_key=True, default=uuid4)
gif_id = Column(String, ForeignKey(TrashTvArchillectData.archillect_id))
timestamp = Column(TIMESTAMP(timezone=True), server_default=func.now())
def test_do_not_support_sql_tz_aware_datetime_types(self):
column_name = "tz_aware_datetime"
tz_aware_types = (DateTime(timezone=True), TIMESTAMP(timezone=True))
for sql_type in tz_aware_types:
with self.assertWarns(Warning):
graphql_type = try_get_graphql_scalar_type(
column_name, sql_type)
self.assertIsNone(graphql_type)
class IndexerCounter(Base):
"Indexing counters"
__tablename__ = 'indexer_counters'
tablename = Column(Unicode(255), primary_key=True)
maxts = Column(TIMESTAMP(timezone=True),
server_default=utcnow(),
index=True,
nullable=False)
def test_render_col_with_server_default(self):
c = Column('updated_at',
TIMESTAMP(),
server_default='TIMEZONE("utc", CURRENT_TIMESTAMP)',
nullable=False)
result = autogenerate._render_column(c, self.autogen_context)
eq_(
result, 'sa.Column(\'updated_at\', sa.TIMESTAMP(), '
'server_default=\'TIMEZONE("utc", CURRENT_TIMESTAMP)\', '
'nullable=False)')
def test_render_server_default_text(self):
c = Column('updated_at',
TIMESTAMP(),
server_default=text('now()'),
nullable=False)
result = autogenerate.render._render_column(c, self.autogen_context)
eq_ignore_whitespace(
result, 'sa.Column(\'updated_at\', sa.TIMESTAMP(), '
'server_default=sa.text(!U\'now()\'), '
'nullable=False)')
class ATTPlanVersion(Base):
"""Model class to represent ATT plan version
Custom versioning class to keep track of plans enabled ATT side
"""
__tablename__ = "att_plan_versions"
id = Column(Integer, primary_key=True)
subscription_id = Column(
Integer, ForeignKey("subscriptions.id"), nullable=False
)
subscription = relationship("Subscription", back_populates="subscriptions")
plan_id = Column(String(30), ForeignKey("plans.id"), nullable=False)
plan = relationship("Plan", back_populates="plans")
start_effective_date = Column(TIMESTAMP(timezone=True), nullable=False)
end_effective_date = Column(TIMESTAMP(timezone=True), nullable=False)
mb_available = Column(BigInteger)
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.subscription_id}, "
f"{str(self.plan_id)} ({self.start_effective_date} - "
f"{self.end_effective_date}) "
)
def getmb(id):
# r = s.query(Plan).filter(Plan.id== self.plan_id).first().get('mb_available')
# return (r.mb_available)
connection = psycopg2.connect(user="******",
password="******",
host="127.0.0.1",
port="5432",
database="wingattbilling")
cursor = connection.cursor()
postgreSQL_select_Query = "select mb_available from plans where id=" + id
cursor.execute(postgreSQL_select_Query)
mobile_records_one = cursor.fetchone()
print(mobile_records_one)
print("r")
return(1048)
class Identification(db.Model):
__tablename__ = 'identifications'
id = Column(BigInteger, nullable=False, primary_key=True)
ctime = Column(TIMESTAMP(timezone=True), nullable=False)
mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()")
individual = Column(Text, ForeignKey( "participants.username"
, onupdate="CASCADE"
, ondelete="RESTRICT"
), nullable=False)
group = Column(Text, ForeignKey( "participants.username"
, onupdate="CASCADE"
, ondelete="RESTRICT"
), nullable=False)
weight = Column(Numeric(precision=17, scale=16), nullable=False)
identified_by = Column(Text, ForeignKey( "participants.username"
, onupdate="CASCADE"
, ondelete="RESTRICT"
), nullable=False)
class Exchange(db.Model):
__tablename__ = 'exchanges'
id = Column(Integer, nullable=False, primary_key=True)
timestamp = Column(TIMESTAMP(timezone=True), nullable=False,
default="now()")
amount = Column(Numeric(precision=35, scale=2), nullable=False)
fee = Column(Numeric(precision=35, scale=2), nullable=False)
participant = Column(Text, ForeignKey("participants.username",
onupdate="CASCADE", ondelete="RESTRICT"),
nullable=False)
class Post(Base):
__tablename__ = 'post'
id = Column(Integer, primary_key=True, nullable=False)
created = Column(TIMESTAMP(timezone=False), default=datetime.utcnow,
nullable=False)
link = Column(TEXT, nullable=False)
title = Column(TEXT, nullable=False)
type = Column(Enum('article', 'ask', 'job', 'show', name='post_type'),
nullable=False)
username = Column(TEXT)
website = Column(TEXT)
__table_args__ = (Index('post_index', 'id', 'username'), )
class Activities(Base):
__tablename__ = 'activities'
id = Column(Integer, primary_key=True, nullable=False)
external_id = Column(TEXT, nullable=False)
activities = Column(JSON, nullable=False)
chart_types = Column(JSON, nullable=False)
time_unit = Column(TEXT, nullable=False)
month = Column(TEXT, nullable=True)
year = Column(Integer, nullable=True)
created = Column(TIMESTAMP(timezone=False),
default=datetime.utcnow,
nullable=False)
class TrashTvArchillectData(Base):
"""DB Model for 'TRASH_TV_ARCHILLECT_DATA' db view."""
__tablename__ = "TRASH_TV_ARCHILLECT_DATA"
id = Column(AGNOSTIC_UUID(), primary_key=True, default=uuid4)
archillect_id = Column(String(), unique=True)
source_link = Column(VARCHAR())
gif_raw_data = Column(LargeBinary, nullable=True)
timestamp = Column(TIMESTAMP(timezone=True), server_default=func.now())
played_on_archillect = relationship("TrashTvArchillectHistory")
class SAAccessKey(Base):
""" The SQLAlchemy declarative model class for a camera object. """
__tablename__ = 'access_key'
id = Column(BigInteger,
nullable=False,
primary_key=True,
autoincrement=True)
key_id = Column(String(length=32, convert_unicode=True),
nullable=False,
index=True,
unique=True)
secret = Column(String(length=64, convert_unicode=True),
nullable=False,
server_default="")
desc = Column(String(length=255, convert_unicode=True),
nullable=False,
server_default="")
username = Column(String(length=64, convert_unicode=True),
ForeignKey('user.username',
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
server_default="")
enabled = Column(Boolean(), nullable=False, server_default=text("0"))
key_type = Column(SmallInteger, nullable=False, server_default=text("0"))
ctime = Column(TIMESTAMP(), nullable=False, server_default=text("0"))
user = relationship("SAUser", back_populates="access_keys")
def __repr__(self):
return "SA AccessKey Object(key_id:%s)" % (self.key_id)
def from_access_key(self, access_key):
self.key_id = access_key.key_id
self.secret = access_key.secret
self.key_type = access_key.key_type
self.username = access_key.username
self.desc = access_key.desc
self.enabled = access_key.enabled
self.ctime = access_key.ctime
def to_access_key(self, access_key_cls):
return access_key_cls(key_id=self.key_id,
secret=self.secret,
username=self.username,
key_type=self.key_type,
enabled=self.enabled,
desc=self.desc,
ctime=self.ctime)
def generate_table(schema_name=None, bind=None):
metadata = MetaData(schema=schema_name, bind=bind)
Table('HISTORY_TABLE', metadata,
Column('history_id', BigInteger, primary_key=True, nullable=False),
Column('cpu_info', VARCHAR(50), nullable=False),
Column('memory_info', VARCHAR(50), nullable=False),
Column('last_UDL_run_date', TIMESTAMP(True), nullable=False),
Column('UDL_completion_duration', VARCHAR(50), nullable=False),
Column('file_size', VARCHAR(50), nullable=False)
)
return metadata