def create_table(cls):
schema = Schema(db)
schema.drop_if_exists("tokens")
with schema.create("tokens") as table:
table.string("id", 40)
table.primary("id")
from dotenv import load_dotenv
from orator import DatabaseManager, Schema, Model
import os
load_dotenv()
DATABASES = {
"mysql": {
'driver': 'mysql',
'host': 'localhost',
'database': 'blog',
'user': os.getenv("DB_USERNAME"),
'password': os.getenv("DB_PASSWORD"),
'prefix': '',
'port': 8889
}
}
db = DatabaseManager(DATABASES)
schema = Schema(db)
Model.set_connection_resolver(db)
__dates__ = ['deleted_at']
@belongs_to('draftLeague')
def League(self):
return League
#open config
config = open("config.json", "r")
credentials = json.load(config)
config.close()
#connect to database, put connection into models/schema for creation. Settings in config.json
db = DatabaseManager(credentials['database'])
Model.set_connection_resolver(db)
schema = Schema(db)
#Create Database is not exists
if not schema.has_table('leagues'):
print("Creating Leagues Table")
with schema.create('leagues') as table:
table.timestamps()
table.soft_deletes()
table.increments('id')
table.string('leagueName')
table.string('channelId')
table.json('roles').default('[]')
settings = {}
settings['rounds'] = 3
settings['roundTiming'] = {}
settings['roundTiming']['1'] = 5
def rollback():
from dc.migration import rollback
db = get_mysql_client(config.get('app.db.mysql'))
schema = Schema(db)
rollback(schema)
def rollback(schema: Schema):
schema.drop('points')
from orator import Schema
from config import db
schema = Schema(db)
# with schema.create('test') as table:
# table.increments('id')
with schema.connection('mysql1').create('user') as table:
table.increments('id')
table.string('uid', 32).unique()
table.string('name', 20)
table.string('email', 100)
with schema.connection('mysql1').create('terminal') as table:
table.increments('id')
table.string('tid', 32)
table.string('uid', 32)
table.foreign('uid').references('uid').on('user')
def migrate():
from dc.migration import migrate
db = get_mysql_client(config.get('app.db.mysql'))
schema = Schema(db)
migrate(schema)
def migrate(schema: Schema):
if not schema.has_table('trim_strategies'):
with schema.create('trim_strategies') as table:
table.big_increments('id')
table.big_integer('account_id')
table.string('name')
table.string('worktime')
table.small_integer('is_repeat') # worktime内是否重复生效
table.timestamps()
'''
对当前Point进行过滤
'''
if not schema.has_table('trim_filters'):
with schema.create('trim_filters') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型 string/int/double/
table.string('key')
table.string('op')
table.string('value')
table.text('extra')
table.timestamps()
'''
需要历史数据介入进行过滤
'''
if not schema.has_table('trim_history_filters'):
with schema.create('trim_history_filters') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型 string/int/double/
table.small_integer('day') # 参与过滤的天数数据
table.string(
'aggregate').nullable() # 聚合函数 支持sum／avg／latest／earliest
table.string('key')
table.string('op')
table.string('value')
table.text('extra')
table.timestamps()
'''
触发相应条件时需要执行的对应修改操作
'''
if not schema.has_table('trims'):
with schema.create('trims') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型
table.string('level').default(
'campaign') # 操作级别 campaign/adgroup/ad
table.string('key') # key
table.string('action')
table.string('value')
table.text('extra1').nullable()
table.text('extra2').nullable()
table.text('extra3').nullable()
table.timestamps()
'''
策略对应的修改动作，以及执行结果的反馈
'''
if not schema.has_table('trim_actions'):
with schema.create('trim_actions') as table:
table.big_increments('id')
table.big_integer('account_id')
table.big_integer('campaign_id')
table.text('action') # 调整动作
table.text('value').nullable() # 调整值
table.text('extra').nullable() # 额外参数
table.text('triggered_point').nullable() # 触发该action的记录
table.text('resp_cnt').nullable() # 执行结果内容
table.string('resp_status_code').nullable() # 执行结果状态
table.timestamps()
def rollback(schema: Schema):
schema.drop('trim_strategies')
schema.drop('trim_filters')
schema.drop('trim_history_filters')
schema.drop('trims')
schema.drop('trim_actions')
}
db_config = {
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'database': 'apitest',
'user': '******',
'password': '******',
'prefix': ''
}
}
# Create DB Manager and Schema for interacting with the MySQL
db = DatabaseManager(db_config)
schema = Schema(db)
# Retrieve data from the API endpoint
print(f"Retrieving data from {api_endpoint}")
with urllib.request.urlopen(api_endpoint) as url:
json_data = json.loads(url.read().decode())
# Set field sizes - we know some are longer than 255
print(f"Setting field sizes")
for field in data_fields.keys():
# Max of length of values of entries in the array
seq = [len(x[field]) for x in json_data if x[field]]
if max(seq) > 255:
data_fields[field]["size"] = max(seq)
'mysql': {
'driver': 'mysql',
'host': settings.YAHOO_ANSWERS_DB_HOST,
'port': settings.YAHOO_ANSWERS_DB_PORT,
'database': settings.YAHOO_ANSWERS_DB_NAME,
'user': settings.YAHOO_ANSWERS_DB_USER,
'password': settings.YAHOO_ANSWERS_DB_PASS,
'log_queries': settings.APP_DB_LOG_QUERIES
}
}
_DB = orator.DatabaseManager(_CONFIG)
orator.Model.set_connection_resolver(_DB)
Model = orator.Model
raw = _DB.raw
schema = Schema(_DB)
@click.command()
def test_db_conn():
for row in _DB.select('SHOW STATUS'):
print(row['Variable_name'], ': ', row['Value'], sep='')
@click.group()
def cli():
pass
cli.add_command(test_db_conn)
def rollback(schema: Schema):
schema.drop('users')
class DatabaseUtilities:
def __init__(self):
self.sego_home = Path.home() / ".sego"
self.database = self.sego_home / "sego_database.db"
self.config = {
'sqlite': {
'driver': 'sqlite',
'database': str(self.database),
'prefix': '',
}
}
self.db = DatabaseManager(self.config)
self.schema = Schema(self.db)
Model.set_connection_resolver(self.db)
def create_connection(self, db_file):
""" create a database connection to a SQLite database """
conn = None
try:
conn = sqlite3.connect(db_file)
print(sqlite3.version)
except Error as e:
print(e)
finally:
if conn:
conn.close()
def get_database_path(self):
return self.database
def create_applications_table(self):
with self.schema.connection('sqlite').create('applications') as table:
table.increments('id')
with self.schema.table('applications') as table:
table.string('app_name').nullable()
table.string('description').nullable()
table.string('developer').nullable()
table.string('version').nullable()
table.string('app_directory').nullable()
table.string("application_identifier").nullable()
table.big_integer('application_type').nullable()
table.timestamp("created_at").nullable()
table.timestamp("updated_at").nullable()
table.big_integer("active").default(0)
def create_plugins_table(self):
with self.schema.connection('sqlite').create('plugins') as table:
table.increments('id')
with self.schema.table('plugins') as table:
table.string("name").nullable()
table.string("description").nullable()
table.string("version").nullable()
def create_targets_table(self):
with self.schema.connection('sqlite').create('targets') as table:
table.increments('id')
with self.schema.table('targets') as table:
table.string('target').nullable()
table.string('description').nullable()
table.string('list_action').nullable()
table.string('generate_action').nullable()
table.timestamp("created_at").nullable()
table.timestamp("updated_at").nullable()
def register_targets(self):
target = Targets()
target.target = "Routes"
target.description = "This generator target manages application routes"
target.save()
target = Targets()
target.target = "Controllers"
target.description = "This generator target manages application controllers"
target.save()
target = Targets()
target.target = "Applications"
target.description = "This generator target manages applications"
target.save()
def register_application(self, app_data):
new_app = Applications()
new_app.app_name = app_data["app_name"]
new_app.description = app_data["description"]
new_app.developer = app_data["developer"]
new_app.version = app_data["version"]
new_app.app_directory = app_data["app_directory"]
new_app.application_identifier = app_data["application_identifier"]
new_app.application_type = 1
new_app.save()
def list_applications(self):
return Applications.all()
def setup(self):
self.create_connection(str(self.database))
self.create_applications_table()
self.create_plugins_table()
self.create_targets_table()
self.register_targets()
def __init__(self, dbcon):
self._db = dbcon
self._schema = Schema(self._db)
self._nodes_table = 'Nodes'
self._edges_table = 'Edges'
class SchemaNode(object):
"""
Database dictionary resembles (TBox) a vocabulary of "terminological components", i.e. abstract terms
Dictionary properties e.g. dimensions, names, counters, etc describe the concepts in dictionary
These terms are Entity types, Attribute types, Data Resource types, Link(edge) types, etc....
TBox is about types and relationships between types
e.g. Entity-Attribute, Table-Column, JSON Object-Fields, etc....
The Node class builds the schema of the `Nodes` table in MariaDB DBMS
Each record in this table, is a node in the hypergraph of metadata
"""
def __init__(self, dbcon):
self._db = dbcon
self._schema = Schema(self._db)
self._nodes_table = 'Nodes'
self._edges_table = 'Edges'
def build(self):
with self._schema.create(self._nodes_table) as table:
# Primary key (not null) with auto increment
table.increments('nID')
# Use ManyToOne non-symmetric, self-reference relationship on objects of Node class
# for the mapping of Columns/Fields onto Attributes
# In that case we will create two models Field, Attribute and we define a relationship between them
# ONE attribute is referenced by MANY fields
# MANY different fields point to ONE attribute Object
# from any FLD object set a reference that points to an attribute
table.integer(
'aID').unsigned().nullable() # ONE side (non-symmetric)
# Add foreign key constraint
table.foreign('aID').references('nID').on(
self._nodes_table).on_delete('cascade')
# Use ManyToOne (Parent/Child) non-symmetric, self-reference relationship on objects of Node class
# To link DMS to Data Models and Data Model to Entities
# or DRS to Data Sets and Data Sets to Columns
table.integer(
'pID').unsigned().nullable() # ONE side (non-symmetric)
# Add foreign key constraint
table.foreign('pID').references('nID').on(
self._nodes_table).on_delete('cascade')
# Add node dimensions
table.integer('dim4').unsigned()
table.integer('dim3').unsigned()
table.integer('dim2').unsigned()
# Add unique composite key constraint
table.unique(['dim4', 'dim3', 'dim2'])
# prevent duplication of aliases
# i) for two ENTities in the same model or
# ii) for two TBLs in the same data resource
# Add unique composite key constraint
table.unique(['dim4', 'dim3', 'alias'])
# unique name identifier is composed from cname and alias
# Add unique key constraint
table.string('uname', 200).default('<NA>').unique()
# canonical name (cname) is the name that is used in the data resource, e.g. field names in flat files
table.string('cname', 100).default('<NA>')
# alias is an alternate name that we assign to a Model, Entity, Attribute (see composite_key above)
# see test_modules examples
table.string('alias', 100).index()
# node type in dictionary terms hypergraph:
# it is used to specify what kind of object, i.e. term concept, we store in metadata dictionary
# DMS(Data Model System), DM(Data Model), ENT(Entity)
# DRS(Data Resources System), DS(Data Set), TBL (Table), SDM (serialization of data model),
# HLS(HyperLink System), HLT(HyperLinkType)...
# ATTR(Attribute), FLD(Field)
node_types = [
'<NA>', 'SYS', 'DMS', 'DM', 'ENT', 'ATTR', 'DRS', 'DS', 'TBL',
'SDM', 'FLD', 'HLS', 'HLT'
]
table.enum('ntype', node_types).default('<NA>').index()
# container type:
# it is used to specify the type of a data resource container,
# i.e. hierarchical file (JSON), flat file (CSV, TSV), database table (MYSQL),
node_contypes = [
'<NA>', 'HB', 'HA', 'SYS', 'HL', 'DM', 'DS', 'CSV', 'TSV',
'MYSQL', 'TBL', 'SDM', 'JSON'
]
table.enum('ctype', node_contypes).default('<NA>').index()
# counter for the number of
# Data Models, Entities, Attributes,
# Data Resources, Tables, Columns
# HyperLink Types, HyperLinks
table.integer('counter').unsigned().default(0)
# Description of object
table.text('descr').default('<NA>')
# location of the data resources, data model on the disk, internet
# this is the full path
table.string('path', 100).default('<NA>')
# MySQL database name
table.string('db', 30).default('<NA>')
# Table names for Clickhouse SET engines
table.string('old_set', 30).default('<NA>')
table.string('new_set', 30).default('<NA>')
# ToDo: Labels on hyperlink types to describe direct and reverse direction (traversal path)
# label_direct
# label_reverse
# For objects of ntype ATTR `junction` field indicates that
# the attribute node (head) is linked to more than on entities (ENT - tail nodes)
# we call this hypernode a junction node
# In relational databases this is the common field where two tables are usually joined
table.boolean('junction').default(False)
# ToDo: implement another flag to indicate whether junction is open or closed
# if bridge=True (junction open) then you can traverse to the next entity
# ToDo: implement another flag to indicate whether junction plays the role of primary or foreign key
# ATTR value type
table.string('vtype', 50).default('<NA>')
def erase(self):
"""
Truncates all data in Nodes table
:return:
"""
# You must disable the FK constraints first
self._db.table(self._nodes_table).truncate()
class SchemaEdge(object):
"""
Each instance is a hyperlink that links:
a tail node (Entity or Table) with
a head node (Attribute or Column)
Each hyperlink has two connectors (edges):
An outgoing edge from the tail
An incoming edge to the head
Many Outgoing Edges that start From One HEdge (HE)
Many Incoming Edges that end To One HNode (HN)
The Edge hyperlink type represents a DIRECTED MANY TO MANY RELATIONSHIP
with an OUTGOING BIDIRECTIONAL HYPERLINK that connects
a tail node (from) with a head node (to) with bidirectional edges
Important Notice:
Do not confuse the DIRECTION OF RELATIONSHIP with the
DIRECTION OF TRAVERSING THE BIDIRECTIONAL EDGES of the HYPERLINK
Many-to-Many Relationship
MANY side (tail node) <------------------- ONE side (edge) ------------------> MANY side (head node)
(fromID - hyperlink object)
An outgoing edge
FROM Entity ========================== Edge ==========================> TO Attribute
(HyperEdge) (toID - hyperlink object) (HyperNode)
An incoming edge
"""
def __init__(self, dbcon):
self._db = dbcon
self._schema = Schema(self._db)
self._nodes_table = 'Nodes'
self._edges_table = 'Edges'
def build(self):
with self._schema.create(self._edges_table) as table:
# Primary key (not null) with auto increment
table.increments('eID')
# Use ManyToMany relationships between HyperNodes (HEAD nodes, toID) and HyperEdges (TAIL nodes, fromID)
# HyperEdges and HyperNodes are object of model classes HEdge, HNode that are based on the Node class
table.integer('toID').unsigned().nullable()
table.integer('fromID').unsigned().nullable()
# Add foreign key constraint
table.foreign('fromID').references('nID').on(
self._nodes_table).on_delete('cascade')
# Add foreign key constraint
table.foreign('toID').references('nID').on(
self._nodes_table).on_delete('cascade')
def erase(self):
"""
Truncates all data in Edges table
:return:
"""
# You must disable the FK constraints first
self._db.table(self._edges_table).truncate()