def refresh_all_mat_views(concurrently=True):
'''Refreshes all materialized views. Currently, views are refreshed in
non-deterministic order, so view definitions can't depend on each other.'''
# TODO use `include='materialized'` once https://bitbucket.org/zzzeek/sqlalchemy/issues/3588/ lands
mat_views = db.inspect(db.engine).get_view_names()
for v in mat_views:
refresh_mat_view(v, concurrently)
def by_id(cls, id):
from app import db
primary_key = db.inspect(cls).primary_key[0]
data = db.session.query(cls).filter(primary_key == id).first()
return data
def refresh_all_mat_views(concurrently=True):
"""Refreshes all materialized views. Currently, views are refreshed in
non-deterministic order, so view definitions can't depend on each other."""
mat_views = db.inspect(db.engine).get_view_names()
for v in mat_views:
refresh_mat_view(v, concurrently)
def refresh_all_mat_views(concurrently=True): '''Refreshes all materialized views. Currently, views are refreshed in non-deterministic order, so view definitions can't depend on each other.''' # TODO use `include='materialized'` once https://bitbucket.org/zzzeek/sqlalchemy/issues/3588/ lands mat_views = db.inspect(db.engine).get_view_names() for v in mat_views: refresh_mat_view(v, concurrently)
def test_db_existence():
"""
Check whether the __init__ created a db and user class table.
"""
db = SQLAlchemy()
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
inspect = db.inspect(engine)
assert (inspect.has_table("user"))
def after_delete_item(mapper, connection, target):
state = db.inspect(target)
u = Log(item_code=target.item_code, action='D')
d = DeletedItems(item_code=target.item_code, item_name=target.item_name)
u.user_code = session['user_code']
for attr in state.attrs:
hist = state.get_history(attr.key, True)
state.session.add(u)
state.session.add(d)
def db_rebuild():
"""
Destroy and rebuild database with fake data.
"""
# destroy and rebuild tables
db.reflect()
db.drop_all()
db.create_all()
# insert locations as defined in model
Location.insert_locations()
# insert roles as defined in model
Role.insert_roles()
# insert geos and usertypes as defined in model
Geo.insert_geos()
UserType.insert_user_types()
# insert education data
Education.create_education()
# insert fake admin/test users
from random import seed
import forgery_py
seed()
test_user_1 = User(email='*****@*****.**',
username='******',
password='******',
app_name='password'.encode('base64'),
confirmed=True,
name='David Kimaru',
location='KE',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
admin_user = User(email='*****@*****.**',
username='******',
password='******',
app_name='webmaster'.encode('base64'),
confirmed=True,
name='Web Master',
location='UG',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
db.session.add_all([test_user_1, admin_user])
db.session.commit()
# insert fake user data
# User.generate_fake(60)
# print results
inspector = db.inspect(db.engine)
print('The following tables were created.')
print('-' * 17)
for table in inspector.get_table_names():
print(table)
def db_rebuild():
"""
Destroy and rebuild database with fake data.
"""
# destroy and rebuild tables
db.reflect()
db.drop_all()
db.create_all()
# insert fake test users
test_user_1 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
)
test_user_2 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
)
db.session.add_all([test_user_1, test_user_2])
db.session.commit()
# insert fake user data
User.generate_fake(60)
# insert fake conferences
FootballConference.generate_fake(20)
# insert fake fantasy leagues
FantasyLeague.generate_fake(20)
# insert fake fantasy teams
FantasyTeam.generate_fake()
# insert fake football teams
FootballTeam.generate_fake(80)
# insert fake football players
FootballPlayer.generate_fake(3000)
# insert fake draft orders
DraftOrder.generate_fake(rounds=5)
# complete draft
# do not complete the draft when creating fake data
# DraftPick.generate_fake()
# print results
inspector = db.inspect(db.engine)
print 'The following tables were created.'
print '-' * 35
for table in inspector.get_table_names():
print table
def after_update_item(mapper, connection, target):
state = db.inspect(target)
u = Log(item_code=target.item_code, action='U')
u.user_code = session['user_code']
for attr in state.attrs:
hist = state.get_history(attr.key, True)
if not hist.has_changes():
continue
p = LoggedParameters(parameter=str(attr.key))
u.parameters.append(p)
state.session.add(u)
def compute_priority(self):
""" This method is called before persisting a feature request to
maintain the list of priorities for a client without holes and without
duplicates. """
requests_count = FeatureRequest.count_active_by_client(self.client)
self.priority = max(self.priority, 1) # Priorities go from 1
# Max value = feature requests count for the client
if self.id is None:
# Creation
self.priority = min(self.priority, requests_count+1)
# create hole: increment priority of every FeatureRequest of this
# client with priority >= self.priority
if not self.is_archived:
FeatureRequest.increment_priority(self.client, self.priority)
else:
self.priority = min(self.priority, requests_count)
state = db.inspect(self)
hist = state.get_history(state.attrs.priority.key, True)
old_priority = self.priority
if hist.deleted != ():
old_priority = hist.deleted[0]
# Is archived changed ?
hist = state.get_history(state.attrs.is_archived.key, True)
if hist.added != () or hist.deleted != ():
# is_archived changed
if self.is_archived:
FeatureRequest.decrement_priority(self.client, old_priority+1)
else:
FeatureRequest.increment_priority(self.client, old_priority)
hist = state.get_history(state.attrs.priority.key, True)
if hist.added == () or hist.added is None or hist.deleted == () or hist.deleted is None:
return # Priority not changed
# Priority was changed
old_priority = hist.deleted[0]
new_priority = hist.added[0]
if self.priority < old_priority:
# Elements in [new_priority, old_priority-1]
# are shifted upwards to make place
FeatureRequest.increment_priority(self.client, new_priority,
to_priority=old_priority-1)
elif new_priority > old_priority:
# Elements in [old_priority+1, new_priority]
# are shifted downwards to make place
FeatureRequest.decrement_priority(self.client, old_priority+1,
to_priority=new_priority)
self.priority = new_priority
def db_rebuild():
"""
Destroy and rebuild database with fake data.
"""
# destroy and rebuild tables
db.reflect()
db.drop_all()
db.create_all()
# insert fake test users
test_user_1 = User(email="*****@*****.**", username="******", password="******", confirmed=True)
test_user_2 = User(email="*****@*****.**", username="******", password="******", confirmed=True)
db.session.add_all([test_user_1, test_user_2])
db.session.commit()
# insert fake user data
User.generate_fake(60)
# insert fake conferences
FootballConference.generate_fake(20)
# insert fake fantasy leagues
FantasyLeague.generate_fake(20)
# insert fake fantasy teams
FantasyTeam.generate_fake()
# insert fake football teams
FootballTeam.generate_fake(80)
# insert fake football players
FootballPlayer.generate_fake(3000)
# insert fake draft orders
DraftOrder.generate_fake(rounds=5)
# complete draft
# do not complete the draft when creating fake data
# DraftPick.generate_fake()
# print results
inspector = db.inspect(db.engine)
print "The following tables were created."
print "-" * 35
for table in inspector.get_table_names():
print table
def on_model_change(self, form, model, is_created):
"""
更新子类型数据
"""
from app.utils.db import ClosureTable
from app import db
super().on_model_change(form, model, is_created)
cl_table = ClosureTable(model)
state = db.inspect(model)
# 软删除
if state.attrs.deleted.history.has_changes() and model.deleted:
cl_table.delete_subtree()
return
# 创建
if is_created:
cl_table.insert_node()
else:
cl_table.move_tree()
def init_app(self, app: Flask):
with app.app_context():
# rename file with its sha256
file_list: List[str] = os.listdir(app.static_folder)
_original: str = os.getcwd()
os.chdir(app.static_folder)
for file in file_list:
with open(file, "rb")as f:
# noinspection InsecureHash
sha256sum = hashlib.sha256(f.read()).hexdigest()
if file != sha256sum:
os.rename(file, sha256sum)
from app.models import Docker
from app import db
# https://stackoverflow.com/a/49178408
if Docker.__tablename__ in db.inspect(db.engine).get_table_names():
file_list: List[str] = os.listdir(app.static_folder)
hash_list: List[str] = Docker.get_binary_list()
if set(hash_list) != set(file_list):
raise FileNotFoundError
os.chdir(_original)
import dash_core_components as dcc
import dash_html_components as html
import dash_bootstrap_components as dbc
from dash.dependencies import Input, Output
from dash.exceptions import PreventUpdate
import dash_table
import pandas as pd
from app import app, db
### ----------------------------- LAYOUT --------------------------------- ###
_option_list = db.inspect(db.engine).get_table_names()
options = [{'label': option, 'value': option} for option in _option_list]
layout = html.Div([
dbc.Container([
dcc.Dropdown(id='select_table',
options=options,
placeholder="Select Table",
multi=False),
html.Br(),
html.Br(),
html.Div(id='postgres_datatable')
], )
])
### ---------------------------- CALLBACKS ------------------------------- ###
@app.callback(Output('postgres_datatable', 'children'),
def db_rebuild():
"""
Destroy and rebuild database with fake data.
"""
# destroy and rebuild tables
db.reflect()
db.drop_all()
db.create_all()
# insert roles as defined in model
Role.insert_roles()
# insert geos and usertypes as defined in model
Geo.insert_geos()
UserType.insert_user_types()
# insert firm types/tiers as defined in model
FirmType.insert_firm_types()
FirmTier.insert_firm_tiers()
# insert fake admin/test users
from random import seed
import forgery_py
seed()
test_user_1 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Salim Hamed',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True)
)
test_user_2 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bryan Davis',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True)
)
test_user_3 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Joe Smith',
location='San Francisco, CA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True)
)
test_user_4 = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bill Gates',
location='Bellevue, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True)
)
admin_user = User(
email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bill Gates',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True)
)
db.session.add_all([test_user_1, test_user_2, test_user_3, test_user_4,
admin_user])
db.session.commit()
# insert fake user data
User.generate_fake(60)
# insert fake post data
Post.generate_fake(400)
# insert fake followers
Follow.generate_fake(2000)
# insert fake firms
Firm.generate_fake(5000)
# insert fake companies
Company.generate_fake(10000)
# insert fake relationships
Relationship.generate_fake(60000)
# print results
inspector = db.inspect(db.engine)
print 'The following tables were created.'
print '-'*17
for table in inspector.get_table_names():
print table
def is_empty():
table_names = db.inspect(db.engine).get_table_names()
is_empty = table_names == []
return is_empty
def after_insert_variant(mapper, connection, target):
state = db.inspect(target)
changes = {}
u = Log(item_code=target.item_code, action='IV')
u.user_code = session['user_code']
state.session.add(u)
def db_rebuild():
"""
Destroy and rebuild database with fake data.
"""
# destroy and rebuild tables
db.reflect()
db.drop_all()
db.create_all()
# insert roles as defined in model
Role.insert_roles()
# insert geos and usertypes as defined in model
Geo.insert_geos()
UserType.insert_user_types()
# insert firm types/tiers as defined in model
FirmType.insert_firm_types()
FirmTier.insert_firm_tiers()
# insert fake admin/test users
from random import seed
import forgery_py
seed()
test_user_1 = User(email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Salim Hamed',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
test_user_2 = User(email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bryan Davis',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
test_user_3 = User(email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Joe Smith',
location='San Francisco, CA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
test_user_4 = User(email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bill Gates',
location='Bellevue, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
admin_user = User(email='*****@*****.**',
username='******',
password='******',
confirmed=True,
name='Bill Gates',
location='Seattle, WA',
about_me=forgery_py.lorem_ipsum.sentence(),
member_since=forgery_py.date.date(True))
db.session.add_all(
[test_user_1, test_user_2, test_user_3, test_user_4, admin_user])
db.session.commit()
# insert fake user data
User.generate_fake(60)
# insert fake post data
Post.generate_fake(400)
# insert fake followers
Follow.generate_fake(2000)
# insert fake firms
Firm.generate_fake(5000)
# insert fake companies
Company.generate_fake(10000)
# insert fake relationships
Relationship.generate_fake(60000)
# print results
inspector = db.inspect(db.engine)
print('The following tables were created.')
print('-' * 17)
for table in inspector.get_table_names():
print(table)
def after_insert_item(mapper, connection, target):
state = db.inspect(target)
u = Log(item_code=target.item_code, action='I')
u.user_code = session['user_code']
state.session.add(u)
