def __init__(self, dbms: DBMS, db: str, schema: str, table: str):
self.dbms = dbms
self.connection = dbms.connect(db)
self.cursor: pyodbc.Cursor = self.connection.cursor()
dbms.tables.setdefault(db, {}).setdefault(schema, {})[table] = self
self.db = db
self.schema = schema
self.table = table
self._db = pk.Database(db)
self._schema = pk.Schema(schema)
self._table = pk.Table(table, self._schema, query_cls=self.dbms.sql)
self.sqlite_table = pk.Table('{}_{}'.format(table, Table.count))
self.indexes = self.dbms.dialect.get_indexes(self.cursor, schema,
table)
self.columns, self.name_to_column = self.__get_columns()
try:
self.test_table(self.cursor)
except Exception as ex:
msg = 'Table {}.{}.{} not found:\nException:{}'.format(
db, schema, table, ex)
self.logger.error(msg)
raise SemanticException(msg)
self.filters = []
Table.count += 1
def get_stream_snapshot(columns, stream_id):
x = pypika.Table('event')
y = pypika.Table('event')
query = pypika.Query.from_(x).select(*map(functools.partial(lambda x, y: getattr(x, y), x), columns))
query = query.left_join(y).on((y.name.like("%.Snapshot")) & (x.stream == y.stream) & (x.version<y.version))
#return query.where((x.stream == pypika.terms.PseudoColumn('%s')) & (y.id.isnull())).orderby(x.version)
return query.where((x.stream == str(stream_id)) & (y.id.isnull())).orderby(x.version)
def leveling(char, exp, db):
t = pypika.Table('lvl_exp')
q = pypika.Query.from_(t).select('*').get_sql()
res = db.select(q)
char.exp += exp
for r in res:
if r[0] == char.lvl + 1 and r[1] <= char.exp:
char.lvl += 1
char.recalculate_basics()
t = pypika.Table('characters')
q = pypika.Query.update(t).set(t.lvl, char.lvl).set(
t.curr_exp, char.exp).where(t.id == char.id).get_sql()
db.update(q)
def change_location(char, mmap, dx, dy, db):
cur_x = char.curr_location.x
cur_y = char.curr_location.y
for l in LOCATIONS:
if l.x == (cur_x + dx) and l.y == (cur_y + dy):
global CUR_LOCATION
CUR_LOCATION = l
char.curr_location = l
constants.BACKGROUND_COLOR = char.curr_location.rgb
if dx > 0:
char.x = 0
elif dx < 0:
char.x = mmap.w - 1
if dy > 0:
char.y = 0
elif dy < 0:
char.y = mmap.h - 1
t = pypika.Table('characters')
q = pypika.Query.update(t).set(
t.curr_location, l.id).where(t.id == char.id).get_sql()
db.update(q)
mmap.random_fill(create_monsters_list(db))
return True
return False
def __post_init__(self):
import peewee
from playhouse.postgres_ext import ArrayField
from playhouse.postgres_ext import BinaryJSONField
from playhouse.mysql_ext import JSONField as MySQL_JSONField
from playhouse.sqlite_ext import JSONField as SQLite_JSONField
super().__post_init__()
self._primary_keys = {}
for k, v in self.mapping2model.items():
if inspect.isclass(v) and issubclass(v, peewee.Model):
self._primary_keys[k] = v._meta.primary_key.name
for name, f in v._meta.fields.items():
if isinstance(f, ArrayField):
self._table_cache[k]['array_fields'].add(f.name)
elif isinstance(
f,
(BinaryJSONField, MySQL_JSONField, SQLite_JSONField)):
self._table_cache[k]['json_fields'].add(f.name)
for k, v in self.mapping2model.items():
if inspect.isclass(v) and issubclass(v, peewee.Model):
self.mapping2model[k] = pypika.Table(v._meta.table_name)
self._phg_cache = None
def init_locations(db_handler):
t = pypika.Table('locations')
q = pypika.Query.from_(t).select('*').get_sql()
response = db_handler.select(q)
for r in response:
LOCATIONS.append(Location(r[0], r[1], r[2], r[3], r[4]))
def __init__(self, *args):
self.game = pypika.Table('Games')
self.query_builder = pypika.SQLLiteQuery.from_(
self.game).select(*args if args else '*')
self.params = {
'where_params': [],
'limit_params': [],
# might add more
}
def set_skills_to_hero(hero, db):
t = pypika.Table('skills_on_chars')
q = pypika.Query.from_(t).select('*').where(t.char_ == hero.id).get_sql()
q = '''SELECT s.id, s.name, s.cost, s.dmg, s.on_self, s.lvl_impr, s.type, s.duration, s.asset FROM skills as s JOIN skills_on_chars as skc on s.id = skc.skill WHERE skc.char_=''' + str(
hero.id)
res = db.select(q)
for r in res:
hero.skills.append(
Skill(r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7],
make_pygame_image(r[8])))
def create_monsters_list(db):
t = pypika.Table('monsters')
q = pypika.Query.from_(t).select('*').get_sql()
monster_res = db.select(q)
monsters = []
# id name lvl base_health base_damage curr_location item asset
for mr in monster_res:
t = pypika.Table('items')
q = pypika.Query.from_(t).select('*').where(t.id == mr[6]).get_sql()
rrr = db.select(q)[0]
rarity = mr[8]
monst_count = (1 / rarity) * (MAP_W / TILE_SIZE) * (MAP_H /
TILE_SIZE) / 5
monst_count = round(monst_count)
for _ in range(monst_count):
item_to_monst = Item(rrr[0], rrr[1], make_pygame_image(rrr[5]),
rrr[2], rrr[4], rrr[3])
monsters.append(
Monster(mr[0], mr[1], mr[3], mr[4], item_to_monst, 0, 0,
make_pygame_image(mr[7]), mr[8], mr[9]))
return monsters
def get_fc_folders(fc_number='19H086-1',
minesite='FortHills',
units=None,
complete=False):
"""Example query to get all event folders for specific FC
- Useful to check for existence of docs
Parameters
----------
fc_number : str, optional
default '19H086-1'
minesite : str, optional
default 'FortHills'
Returns
-------
[type]
[description]
"""
from smseventlog import queries as qr
query = qr.FCDetails()
args = [
dict(vals=dict(MineSite=minesite), table=query.d),
dict(vals=dict(FCNumber=fc_number))
]
if complete:
args.append(dict(vals=dict(complete=1)))
if not units is None:
args.append(dict(ct=query.a.unit.isin(units)))
# add extra table + wo column for query
t = pk.Table('EventLog')
query.q = query.q.left_join(t).on_field('UID')
query.add_fltr_args(args)
query.add_extra_cols([t.WorkOrder, t.Title, t.DateAdded])
df = query.get_df()
# return df
# list of event folders
efs = [EventFolder.from_model(e=row) for row in df.itertuples()]
return efs
def __post_init__(self):
self.json_dumps_func = json_dumps_ex
self._table_cache = {
# 'mapping': {
# 'array_fields': [],
# 'json_fields': [],
# }
}
for k, v in self.mapping2model.items():
if isinstance(v, str):
self.mapping2model[k] = pypika.Table(v)
self._table_cache[k] = {
'array_fields': set(),
'json_fields': set(),
}
def instantiate(cls, **kwargs):
self = super(Postgres, cls).instantiate(**kwargs)
keys = self._Crud__field_values.keys()
table = pypika.Table(cls._table)
query = pypika.PostgreSQLQuery.into(table).columns( *keys ).insert( *[pypika.Parameter("%({})s".format(k)) for k in keys ] ).returning('*')
with conn_pool.getconn() as conn:
print(conn.autocommit)
with conn.cursor() as curr:
curr.execute(query.get_sql(), self._Crud__field_values)
data = curr.fetchone()
for field in self._schema.keys():
self._Crud__field_values[field] = data.get(field, None)
setattr(self, field, data.get(field, None))
return self
def update_dt_exclusions_ma(units, rng_dates=None, dates=None):
# set MA=0 (False) for units in given date range range
t = pk.Table('DowntimeExclusions')
cond = [t.Unit.isin(units)]
if not rng_dates is None:
cond.append(t.Date.between(*rng_dates))
if not dates is None:
cond.append(t.Date.isin(dates))
q = pk.Query().update(t).set(t.MA, 0).where(pk.Criterion.all(cond))
sql = q.get_sql()
cursor = db.cursor
rows = cursor.execute(sql).rowcount
cursor.commit()
print(f'Rows updated: {rows}')
def __post_init__(self):
import tortoise
from tortoise.fields import JSONField
super().__post_init__()
for k, v in self.mapping2model.items():
if inspect.isclass(v) and issubclass(v, tortoise.models.Model):
for name, f in v._meta.fields_map.items():
if f.SQL_TYPE.endswith('[]'):
self._table_cache[k]['array_fields'].add(name)
elif isinstance(f, JSONField):
self._table_cache[k]['json_fields'].add(name)
for k, v in self.mapping2model.items():
if inspect.isclass(v) and issubclass(v, tortoise.models.Model):
self.mapping2model[k] = pypika.Table(v._meta.db_table)
self._phg_cache = None
self.is_pg = False
def create_update_query(self, columns, row, updates_location, mail_types):
table_to_update = pypika.Table(self.name)
update_query = pypika.Query() \
.update(table_to_update) \
.where(table_to_update.id == row["id"])
for key in columns:
if key in row:
update_query = update_query.set(
key, Table.format_exit_string(row[key]))
result_query = Table.get_sql(update_query)
file_name = mail_types[row[
"id"]] if self.name == "mail_content" else self.name + '_' + str(
row["id"])
update_file = open(updates_location + file_name + ".sql", 'w')
update_file.write(result_query)
update_file.close()
print("File " + file_name + ".sql" + " successfully generated here " +
updates_location)
return result_query
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
return Table.get_sql(insert_query)
def create_update_query(self, columns, row, updates_location, mail_types):
table_to_update = pypika.Table(self.name)
update_query = pypika.Query() \
.update(table_to_update) \
.where(table_to_update.id == row["id"])
for key in columns:
if key in row:
update_query = update_query.set(
key, Table.format_exit_string(row[key]))
# WORKAROUND: H2 database does not support quoting for table names or column names.
# Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now).
result_query = update_query.get_sql(quote_char="") + ";"
file_name = mail_types[row[
"id"]] if self.name == "mail_content" else self.name + '_' + str(
row["id"])
update_file = open(updates_location + file_name + ".sql", 'w')
update_file.write(result_query)
update_file.close()
print("File " + file_name + ".sql" + " successfully generated here " +
updates_location)
return result_query
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
# WORKAROUND: H2 database does not support quoting for table names or column names.
# Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now).
return insert_query.get_sql(quote_char="") + ";"
def main():
# INIT
db = DBHandler()
# id name lvl curr_exp base_health base_damage curr_location inventory
hero_res = start_menu(db)
while hero_res == -1:
hero_res = start_menu(db)
# INVENTORY
t = pypika.Table('inventories')
q = pypika.Query.from_(t).select('*').where(t.id == hero_res[7]).get_sql()
# id capacity size
inv_res = db.select(q)[0]
heros_inv = Inventory(inv_res[0], inv_res[1], inv_res[2], db)
# LOCATIONS
init_locations(db)
for l in LOCATIONS:
if l.id == hero_res[6]:
global CUR_LOCATION
CUR_LOCATION = l
# PYGAME
pygame.init()
pygame.key.set_repeat(100, 300)
clock = pygame.time.Clock()
screen = pygame.display.set_mode([DISPLAY_W, DISPLAY_H], pygame.DOUBLEBUF)
mapp = Map(MAP_W, MAP_H)
mapp.random_fill(create_monsters_list(db))
hero = Character(hero_res[0], hero_res[1], hero_res[2], hero_res[3],
hero_res[4], hero_res[5], heros_inv, round(mapp.w / 2),
round(mapp.h / 2), CUR_LOCATION,
make_pygame_image("assets/new_hero.png"), hero_res[-1],
hero_res[-2])
set_skills_to_hero(hero, db)
constants.BACKGROUND_COLOR = hero.curr_location.rgb
inventory_surf = pygame.Surface((DISPLAY_W - MAP_W, DISPLAY_H / 2))
inventory_surf.fill(INVENTORY_COLOR)
skills_surf = pygame.Surface((DISPLAY_W - MAP_W, DISPLAY_H / 2))
skills_surf.fill(SKILLS_COLOR)
in_inventory_mode = False
in_skills_mode = False
in_skill_direction_choosing_mode = False
target_is_found = False
target = None
screen.fill(constants.BACKGROUND_COLOR)
for i, it in mapp:
if it != 0:
screen.blit(it.img, (it.x * TILE_SIZE, it.y * TILE_SIZE))
if isinstance(it, Monster):
health_bar = pygame.Surface(
(TILE_SIZE * (it.health / it.base_health),
int(TILE_SIZE / 10)))
health_bar.fill(hex_to_rgb('#00FF00'))
screen.blit(health_bar, (it.x * TILE_SIZE, (it.y * TILE_SIZE)))
health_bar = pygame.Surface(
(TILE_SIZE * (hero.curr_health / hero.get_total_health()),
int(TILE_SIZE / 10)))
health_bar.fill(hex_to_rgb('#00FF00'))
screen.blit(health_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 15))
mana_bar = pygame.Surface(
(TILE_SIZE * (hero.curr_mana / hero.total_mana), int(TILE_SIZE / 10)))
mana_bar.fill(hex_to_rgb('#0000FF'))
screen.blit(mana_bar, (hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 10))
screen.blit(hero.img, (hero.x * TILE_SIZE, hero.y * TILE_SIZE))
screen.blit(inventory_surf, (MAP_W, 0))
screen.blit(skills_surf, (MAP_W, DISPLAY_H / 2))
draw_UI(screen, hero, in_inventory_mode, in_skills_mode,
in_skill_direction_choosing_mode)
pygame.display.update()
running = True
while running and hero.curr_health > 0:
clock.tick(FPS)
for e in pygame.event.get():
if e.type == pygame.QUIT:
running = False
if e.type == pygame.KEYDOWN:
global CUR_ITEM
global CUR_SKILL
if e.key == pygame.K_ESCAPE:
running = False
break
if e.key == pygame.K_i:
if not in_inventory_mode:
in_skills_mode = False
in_inventory_mode = not in_inventory_mode
CUR_ITEM = 0
if e.key == pygame.K_s:
if not in_skills_mode:
in_inventory_mode = False
in_skills_mode = not in_skills_mode
CUR_SKILL = 0
if in_inventory_mode:
if e.key == pygame.K_UP:
if CUR_ITEM != 0:
CUR_ITEM -= 1
if e.key == pygame.K_DOWN:
if CUR_ITEM < hero.inventory.size - 1:
CUR_ITEM += 1
if e.key == pygame.K_g:
index = hero.x + hero.y * mapp.w
mmmap = mapp.cells[index]
if isinstance(mmmap, Item):
if hero.take_item(mmmap):
mapp.cells[index] = 0
if e.key == pygame.K_d:
hero.drop_item(hero.inventory.items[CUR_ITEM])
elif in_skills_mode:
if e.key == pygame.K_UP:
if CUR_SKILL != 0:
CUR_SKILL -= 1
if e.key == pygame.K_DOWN:
if CUR_SKILL < len(hero.skills) - 1:
CUR_SKILL += 1
if e.key == pygame.K_RETURN:
skill = hero.skills[CUR_SKILL]
if skill.cost > hero.curr_mana:
break
if skill.type == 'heal':
skill.use_on_target(hero)
skill_animation(hero, hero.x, hero.y, screen)
hero.curr_mana -= skill.cost
in_skills_mode = False
if skill.type == 'dmg':
in_skill_direction_choosing_mode = True
in_skills_mode = False
elif in_skill_direction_choosing_mode:
if e.key == pygame.K_UP:
ind = hero.x + hero.y * mapp.w
while ind >= 0:
if isinstance(mapp.cells[ind], Monster):
target_is_found = True
target = mapp.cells[ind]
break
ind -= mapp.w
elif e.key == pygame.K_DOWN:
ind = hero.x + hero.y * mapp.w
while ind <= mapp.h * mapp.w:
if isinstance(mapp.cells[ind], Monster):
target_is_found = True
target = mapp.cells[ind]
break
ind += mapp.w
elif e.key == pygame.K_LEFT:
ind = hero.x + hero.y * mapp.w
while ind >= hero.y * mapp.w:
if isinstance(mapp.cells[ind], Monster):
target_is_found = True
target = mapp.cells[ind]
break
ind -= 1
elif e.key == pygame.K_RIGHT:
ind = hero.x + hero.y * mapp.w
while ind <= hero.y * mapp.w + mapp.w:
if isinstance(mapp.cells[ind], Monster):
target_is_found = True
target = mapp.cells[ind]
break
ind += 1
else:
target_is_found = False
target = None
in_skill_direction_choosing_mode = False
if target_is_found:
hero.curr_mana -= skill.cost
skill_animation(hero, target.x, target.y, screen)
hero.skills[CUR_SKILL].use_on_target(target)
if target.health <= 0:
mapp.cells[target.x +
target.y * mapp.w] = target.item
target.item.x = target.x
target.item.y = target.y
leveling(hero, target.exp, db)
target_is_found = False
target = None
in_skill_direction_choosing_mode = False
else:
if e.key == pygame.K_UP:
move(hero, mapp, 0, -1, db)
if e.key == pygame.K_DOWN:
move(hero, mapp, 0, 1, db)
if e.key == pygame.K_LEFT:
move(hero, mapp, -1, 0, db)
if e.key == pygame.K_RIGHT:
move(hero, mapp, 1, 0, db)
if hero.curr_health <= 0:
break
hero.regenerate()
screen.fill(constants.BACKGROUND_COLOR)
for i, it in mapp:
if it != 0:
screen.blit(it.img,
(it.x * TILE_SIZE, it.y * TILE_SIZE))
if isinstance(it, Monster):
health_bar = pygame.Surface(
(TILE_SIZE * (it.health / it.base_health),
int(TILE_SIZE / 10)))
health_bar.fill(hex_to_rgb('#00FF00'))
screen.blit(health_bar,
(it.x * TILE_SIZE, (it.y * TILE_SIZE)))
health_bar = pygame.Surface(
(TILE_SIZE * (hero.curr_health / hero.get_total_health()),
int(TILE_SIZE / 10)))
health_bar.fill(hex_to_rgb('#00FF00'))
screen.blit(health_bar,
(hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 15))
mana_bar = pygame.Surface(
(TILE_SIZE * (hero.curr_mana / hero.total_mana),
int(TILE_SIZE / 10)))
mana_bar.fill(hex_to_rgb('#0000FF'))
screen.blit(mana_bar,
(hero.x * TILE_SIZE, (hero.y * TILE_SIZE) - 10))
screen.blit(hero.img, (hero.x * TILE_SIZE, hero.y * TILE_SIZE))
screen.blit(inventory_surf, (MAP_W, 0))
screen.blit(skills_surf, (MAP_W, DISPLAY_H / 2))
draw_UI(screen, hero, in_inventory_mode, in_skills_mode,
in_skill_direction_choosing_mode)
pygame.display.update()
if hero.curr_health <= 0:
print('Your character is totally dead :(')
print('Make another one and good luck!')
def get_stream(columns, stream_id):
e = pypika.Table('event')
return pypika.Table(e).select(*columns).where(e.stream == str(stream_id)).orderby(e.version)
def start_menu(db):
print('1. Choose existing character')
print('2. Create new character')
print('3. List existing characters')
print('You always can input 0 to return to main menu')
available_anses = [1, 2, 3]
ans = input('> ')
try:
ans = int(ans)
if ans not in available_anses:
return -1
except ValueError:
return -1
if ans == 0:
return -1
if ans == 1:
t = pypika.Table('characters')
q = pypika.Query.from_(t).select('*').get_sql()
res = db.select(q)
print()
for r in res:
print(r[1] + ', lvl ' + str(r[2]))
print()
hero_name = input('hero name: ')
if hero_name == '0':
return -1
t = pypika.Table('characters')
q = pypika.Query.from_(t).select('*').where(
t.name == hero_name).get_sql()
hero_res = db.select(q)
try:
hero_res = hero_res[0]
except IndexError:
print('no such character')
return -1
t = pypika.Table('classes')
q = pypika.Query.from_(t).select('name').where(
t.id == hero_res[8]).get_sql()
class_res = db.select(q)
hero_res = list(hero_res)
hero_res.append(class_res[0][0])
print('starting your adventure')
return hero_res
if ans == 2:
print()
name = input('Name: ')
if name == '0':
return -1
print('Available classes: ')
t = pypika.Table('classes')
q = pypika.Query.from_(t).select('*').get_sql()
class_res = db.select(q)
i = 0
for r in class_res:
print(
str(i + 1) + '.',
r[1] + " (hp: " + str(r[2]) + ", dmg: " + str(r[3]) + ')')
i += 1
class_ = input('No. of class: ')
if class_ == '0':
return -1
class_ = int(class_)
class_ -= 1 # for right indexation
t = pypika.Table('inventories')
q = pypika.Query.into('inventories').columns(
t.capacity).insert(3).get_sql()
db.insert(q)
q = pypika.Query.from_(t).select('id').orderby(
'id', order=Order.desc).get_sql()
new_inv = db.select(q)[0]
t = pypika.Table('characters')
q = pypika.Query.into(t).columns(
t.name, t.base_health, t.base_damage, t.curr_location, t.inventory,
t.class_).insert(name, class_res[class_][2], class_res[class_][3],
1, new_inv[0], class_res[class_][0]).get_sql()
db.insert(q)
q = pypika.Query.from_(t).select("*").where(t.name == name).get_sql()
res = db.select(q)[0]
if class_res[class_][1] == 'Mage':
t = pypika.Table('skills_on_chars')
q = pypika.Query.into(t).columns(t.char_, t.skill).insert(
res[0], 1).insert(res[0], 2).get_sql()
db.insert(q)
res = list(res)
res.append(class_res[class_][1])
print('new character have been created')
return res
if ans == 3:
t = pypika.Table('characters')
q = pypika.Query.from_(t).select('*').get_sql()
res = db.select(q)
print()
for r in res:
print(r[1] + ', lvl ' + str(r[2]))
print()
return -1
