def query(cls):
profile = sql.t.EPytisFormProfileBase.alias('profile')
params = sql.t.EPytisFormProfileParams.alias('params')
return sqlalchemy.select(
cls._exclude(profile, 'id', 'username', 'spec_name', 'profile_id', 'pickle', 'dump',
'errors') +
cls._exclude(params, 'id', 'pickle', 'dump', 'errors') +
[sql.gL("profile.id||'.'||params.id").label('id'),
sql.gL("'form/'|| params.form_name ||'/'|| profile.spec_name ||'//'")
.label('fullname'),
sql.gL("case when profile.errors is not null and params.errors is not null "
"then profile.errors ||'\n'||params.errors "
"else coalesce(profile.errors, params.errors) end").label('errors'),
sql.gL("case when profile.dump is not null and params.dump is not null "
"then profile.dump ||'\n'||params.dump "
"else coalesce(profile.dump, params.dump) end").label('dump'),
profile.c.pickle.label('pickled_filter'),
params.c.pickle.label('pickled_params')],
from_obj=[
profile.join(
params, and_(
profile.c.username == params.c.username,
profile.c.spec_name == params.c.spec_name,
profile.c.profile_id == params.c.profile_id
)
)
]
)
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 't_start', 't_show') + [
sql.gL("count(t_start)").label('n_open'),
sql.gL("max(t_start)").label('last_used')
],
from_obj=[log]).group_by('form', 'class', 'info', 'login')
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 't_start', 't_show') +
[sql.gL("count(t_start)").label('n_open'),
sql.gL("max(t_start)").label('last_used')],
from_obj=[log]
).group_by('form', 'class', 'info', 'login')
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 'login', 't_start', 't_show') +
[sql.gL("count(distinct login)").label('n_users'),
sql.gL("count(t_start)").label('n_open'),
sql.gL("extract('epoch' from avg(t_show-t_start))").label('avg_start'),
sql.gL("max(t_start)").label('last_used')],
from_obj=[log]
).group_by('form', 'class', 'info')
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 'info', 't_start', 't_show') +
[sql.gL("count(t_start)").label('n_open'),
sql.gL("max(t_start)").label('last_used'),
sql.gL("'form/'||form").label('shortname')],
from_obj=[log],
whereclause='log.t_start between f_user_cfg_datum_od() and f_user_cfg_datum_do()'
).group_by('form', 'class', 'login')
def select_1():
m = sql.t.EPytisMenu.alias('m')
a_ = sql.t.CPytisMenuActions.alias('a')
mh = sql.t.EPytisHelpMenu.alias('mh')
sh = sql.t.EPytisHelpSpec.alias('sh')
return sqlalchemy.select(
sql.reorder_columns(
[sql.gL("'menu/'||m.menuid").label('help_id'),
m.c.menuid.label('menuid'),
m.c.fullname.label('fullname'),
m.c.title.label('title'),
m.c.help.label('description'),
mh.c.content.label('menu_help'),
a_.c.spec_name.label('spec_name'),
sh.c.description.label('spec_description'),
sh.c.help.label('spec_help'),
sql.gL("null::int").label('page_id'),
sql.gL("null::int").label('parent'),
sql.gL("null").label('ord'),
sql.gL("null").label('content'),
sql.gL("text2ltree('999999')||subpath(m.position, 1)").label('position'),
sql.gL("(select count(*)-1 from e_pytis_menu where position <@ m.position)")
.label('position_nsub'),
sql.gL("coalesce(mh.changed, false) or coalesce(sh.changed, false)")
.label('changed'),
sql.gL("coalesce(mh.removed, false) or coalesce(sh.removed, false)")
.label('removed')],
['help_id', 'menuid', 'fullname', 'title', 'description', 'menu_help',
'spec_name', 'spec_description', 'spec_help', 'page_id', 'parent', 'ord',
'content', 'position', 'position_nsub', 'changed', 'removed']),
from_obj=[m.join(a_, a_.c.fullname == m.c.fullname).
outerjoin(mh, mh.c.fullname == a_.c.fullname).
outerjoin(sh, sh.c.spec_name == a_.c.spec_name)],
whereclause='nlevel(position) >= 2'
)
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 'login', 't_start', 't_show', 'info') + [
sql.gL("count(distinct login)").label('n_users'),
sql.gL("count(t_start)").label('n_open'),
sql.gL("extract('epoch' from avg(t_show-t_start))").label(
'avg_start'),
sql.gL("max(t_start)").label('last_used')
],
from_obj=[log]).group_by('form', 'class')
def query(cls):
_inserts = sql.t.XInserts
return sqlalchemy.select([
sql.gL("vytvoreno::date").label('datum'),
sql.gL("vytvoreno::time").label('cas'),
_inserts.c.id.label('id'),
_inserts.c.vytvoril.label('vytvoril'),
_inserts.c.vytvoreno.label('vytvoreno'),
_inserts.c.tabulka.label('tabulka'),
_inserts.c.klic.label('klic')
],
from_obj=[_inserts])
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(
cls._exclude(log, 'id', 'info', 't_start', 't_show') + [
sql.gL("count(t_start)").label('n_open'),
sql.gL("max(t_start)").label('last_used'),
sql.gL("'form/'||form").label('shortname')
],
from_obj=[log],
whereclause=
'log.t_start between f_user_cfg_datum_od() and f_user_cfg_datum_do()'
).group_by('form', 'class', 'login')
def query(cls):
_deletes = sql.t.XDeletes
return sqlalchemy.select(
[sql.gL("smazano::date").label('datum'),
sql.gL("smazano::time").label('cas'),
sql.gL("_deletes.oid").label('oid'),
_deletes.c.id.label('id'),
_deletes.c.smazal.label('smazal'),
_deletes.c.smazano.label('smazano'),
_deletes.c.tabulka.label('tabulka'),
_deletes.c.klic.label('klic')],
from_obj=[_deletes],
whereclause='smazal = current_user')
def query(cls):
_inserts = sql.t.XInserts
return sqlalchemy.select(
[sql.gL("vytvoreno::date").label('datum'),
sql.gL("vytvoreno::time").label('cas'),
sql.gL("_inserts.oid").label('oid'),
_inserts.c.id.label('id'),
_inserts.c.vytvoril.label('vytvoril'),
_inserts.c.vytvoreno.label('vytvoreno'),
_inserts.c.tabulka.label('tabulka'),
_inserts.c.klic.label('klic')],
from_obj=[_inserts],
whereclause='vytvoril = current_user')
def query(cls):
_updates = sql.t.XUpdates
return sqlalchemy.select(
[sql.gL("zmeneno::date").label('datum'),
sql.gL("zmeneno::time").label('cas'),
sql.gL("_updates.oid").label('oid'),
_updates.c.id.label('id'),
_updates.c.zmenil.label('zmenil'),
_updates.c.zmeneno.label('zmeneno'),
_updates.c.tabulka.label('tabulka'),
_updates.c.klic.label('klic'),
_updates.c.zmeny.label('zmeny')],
from_obj=[_updates],
whereclause='zmenil = current_user')
def select_1():
m = sql.t.EPytisMenu.alias("m")
a_ = sql.t.CPytisMenuActions.alias("a")
mh = sql.t.EPytisHelpMenu.alias("mh")
sh = sql.t.EPytisHelpSpec.alias("sh")
return sqlalchemy.select(
sql.reorder_columns(
[
sql.gL("'menu/'||m.menuid").label("help_id"),
m.c.menuid.label("menuid"),
m.c.fullname.label("fullname"),
m.c.title.label("title"),
m.c.help.label("description"),
mh.c.content.label("menu_help"),
a_.c.spec_name.label("spec_name"),
sh.c.description.label("spec_description"),
sh.c.help.label("spec_help"),
sql.gL("null::int").label("page_id"),
sql.gL("null::int").label("parent"),
sql.gL("null").label("ord"),
sql.gL("null").label("content"),
sql.gL("text2ltree('999999')||subpath(m.position, 1)").label("position"),
sql.gL("(select count(*)-1 from e_pytis_menu where position <@ m.position)").label(
"position_nsub"
),
sql.gL("coalesce(mh.changed, false) or coalesce(sh.changed, false)").label("changed"),
sql.gL("coalesce(mh.removed, false) or coalesce(sh.removed, false)").label("removed"),
],
[
"help_id",
"menuid",
"fullname",
"title",
"description",
"menu_help",
"spec_name",
"spec_description",
"spec_help",
"page_id",
"parent",
"ord",
"content",
"position",
"position_nsub",
"changed",
"removed",
],
),
from_obj=[
m.join(a_, a_.c.fullname == m.c.fullname)
.outerjoin(mh, mh.c.fullname == a_.c.fullname)
.outerjoin(sh, sh.c.spec_name == a_.c.spec_name)
],
whereclause="nlevel(position) >= 2",
)
def query(cls):
log = sql.t.CmsSessionLogData.alias('l')
session = sql.t.CmsSession.alias('s')
users = sql.t.CmsUsers.alias('u')
return sqlalchemy.select(cls._exclude(log, 'end_time') + [
users.c.fullname.label('fullname'),
sql.gL("coalesce(l.end_time, s.last_access) - l.start_time").label(
'duration'),
sql.gL("s.session_id IS NOT NULL AND age(s.last_access)<'1 hour'").
label('active')
],
from_obj=[
log.outerjoin(
session, log.c.session_id ==
session.c.session_id).join(
users, log.c.uid == users.c.uid)
])
def query(cls):
l = sql.t.CmsSessionLogData.alias('l')
s = sql.t.CmsSession.alias('s')
u = sql.t.CmsUsers.alias('u')
return sqlalchemy.select(cls._exclude(l, 'end_time') + [
u.c.fullname.label('fullname'),
sql.gL("coalesce(l.end_time, s.last_access) - l.start_time").label(
'duration'),
sql.gL("s.session_id IS NOT NULL AND age(s.last_access)<'1 hour'").
label('active')
],
from_obj=[
l.outerjoin(
s,
sql.gR('l.session_id = s.session_id')
).join(u, sql.gR('l.uid = u.uid'))
])
def select_3():
p = sql.t.EPytisHelpPages.alias("p")
return sqlalchemy.select(
sql.reorder_columns(
cls._exclude(p)
+ [
sql.gL("'page/'||page_id").label("help_id"),
sql.gL("null").label("menuid"),
sql.gL("null").label("fullname"),
sql.gL("null").label("menu_help"),
sql.gL("null").label("spec_name"),
sql.gL("null").label("spec_description"),
sql.gL("null").label("spec_help"),
sql.gL("(select count(*)-1 from e_pytis_help_pages " "where position <@ p.position)").label(
"position_nsub"
),
sql.gL("false").label("changed"),
sql.gL("false").label("removed"),
],
[
"help_id",
"menuid",
"fullname",
"title",
"description",
"menu_help",
"spec_name",
"spec_description",
"spec_help",
"page_id",
"parent",
"ord",
"content",
"position",
"position_nsub",
"changed",
"removed",
],
),
from_obj=[p],
)
def select_3():
p = sql.t.EPytisHelpPages.alias('p')
return sqlalchemy.select(
sql.reorder_columns(
cls._exclude(p) +
[sql.gL("'page/'||page_id").label('help_id'),
sql.gL("null").label('menuid'),
sql.gL("null").label('fullname'),
sql.gL("null").label('menu_help'),
sql.gL("null").label('spec_name'),
sql.gL("null").label('spec_description'),
sql.gL("null").label('spec_help'),
sql.gL("(select count(*)-1 from e_pytis_help_pages "
"where position <@ p.position)").label('position_nsub'),
sql.gL("false").label('changed'),
sql.gL("false").label('removed')],
['help_id', 'menuid', 'fullname', 'title', 'description', 'menu_help',
'spec_name', 'spec_description', 'spec_help', 'page_id', 'parent', 'ord',
'content', 'position', 'position_nsub', 'changed', 'removed']),
from_obj=[p]
)
def query(cls):
profile = sql.t.EPytisFormProfileBase.alias('profile')
params = sql.t.EPytisFormProfileParams.alias('params')
return sqlalchemy.select(
cls._exclude(profile, 'id', 'username', 'spec_name', 'profile_id', 'pickle', 'dump',
'errors') +
cls._exclude(params, 'id', 'pickle', 'dump', 'errors') +
[sql.gL("profile.id||'.'||params.id").label('id'),
sql.gL("'form/'|| params.form_name ||'/'|| profile.spec_name ||'//'")
.label('fullname'),
sql.gL("case when profile.errors is not null and params.errors is not null "
"then profile.errors ||'\n'||params.errors "
"else coalesce(profile.errors, params.errors) end").label('errors'),
sql.gL("case when profile.dump is not null and params.dump is not null "
"then profile.dump ||'\n'||params.dump "
"else coalesce(profile.dump, params.dump) end").label('dump'),
profile.c.pickle.label('pickled_filter'),
params.c.pickle.label('pickled_params')],
from_obj=[profile.join(params, sql.gR('profile.username = params.username and '
'profile.spec_name = params.spec_name and '
'profile.profile_id = params.profile_id'))]
)
def query(cls):
struc = sql.t.CmsMenuStructure.alias('s')
lang = sql.t.CmsLanguages.alias('l')
texts = sql.t.CmsMenuTexts.alias('t')
mod = sql.t.CmsModules.alias('m')
return sqlalchemy.select(
cls._exclude(struc) + cls._exclude(lang, 'lang_id') +
cls._exclude(texts, 'menu_item_id', 'lang', 'published') +
cls._exclude(mod, 'mod_id') + [
sql.gL("s.menu_item_id ||'.'|| l.lang").label('menu_id'),
sql.gL("coalesce(t.published, 'FALSE')").label('published'),
sql.gL("coalesce(t.title, s.identifier)").label(
'title_or_identifier'),
sql.gL("(select count(*)-1 from cms_menu_structure "
"where tree_order <@ s.tree_order)").label(
'tree_order_nsub')
],
from_obj=[
struc.join(lang, sqlalchemy.sql.true()).outerjoin(
texts,
and_(texts.c.menu_item_id == struc.c.menu_item_id,
texts.c.lang == lang.c.lang)).outerjoin(
mod, mod.c.mod_id == struc.c.mod_id)
])
def query(cls):
s = sql.t.CmsMenuStructure.alias('s')
l = sql.t.CmsLanguages.alias('l')
t_ = sql.t.CmsMenuTexts.alias('t')
m = sql.t.CmsModules.alias('m')
return sqlalchemy.select(
cls._exclude(s) + cls._exclude(l, 'lang_id') +
cls._exclude(t_, 'menu_item_id', 'lang', 'published') +
cls._exclude(m, 'mod_id') + [
sql.gL("s.menu_item_id ||'.'|| l.lang").label('menu_id'),
sql.gL("coalesce(t.published, 'FALSE')").label('published'),
sql.gL("coalesce(t.title, s.identifier)").label(
'title_or_identifier'),
sql.gL("(select count(*)-1 from cms_menu_structure "
"where tree_order <@ s.tree_order)").label(
'tree_order_nsub')
],
from_obj=[
s.join(l, sqlalchemy.sql.true()).outerjoin(
t_,
sql.gR(
't.menu_item_id = s.menu_item_id AND t.lang = l.lang')
).outerjoin(m, sql.gR('m.mod_id = s.mod_id'))
])
def select_2():
series = sqlalchemy.select(
["*"], from_obj=[sqlalchemy.text("generate_series(0, 0)")
]).alias('series')
return sqlalchemy.select(sql.reorder_columns([
sql.gL("'menu/'").label('help_id'),
sql.gL("null").label('menuid'),
sql.gL("null").label('fullname'),
sql.gL("'Aplikační menu '").label('title'),
sql.gL("null").label('description'),
sql.gL("null").label('menu_help'),
sql.gL("null").label('spec_name'),
sql.gL("null").label('spec_description'),
sql.gL("null").label('spec_help'),
sql.gL("null::int").label('page_id'),
sql.gL("null::int").label('parent'),
sql.gL("null::int").label('ord'),
sql.gL("null").label('content'),
sql.gL("text2ltree('999999')").label('position'),
sql.gL("(select count(*) from e_pytis_menu)").label(
'position_nsub'),
sql.gL("false").label('changed'),
sql.gL("false").label('removed')
], [
'help_id', 'menuid', 'fullname', 'title', 'description',
'menu_help', 'spec_name', 'spec_description', 'spec_help',
'page_id', 'parent', 'ord', 'content', 'position',
'position_nsub', 'changed', 'removed'
]),
from_obj=[series])
def select_2():
series = sqlalchemy.select(["*"], from_obj=["generate_series(0, 0)"]).alias("series")
return sqlalchemy.select(
sql.reorder_columns(
[
sql.gL("'menu/'").label("help_id"),
sql.gL("null").label("menuid"),
sql.gL("null").label("fullname"),
sql.gL("'Aplikační menu '").label("title"),
sql.gL("null").label("description"),
sql.gL("null").label("menu_help"),
sql.gL("null").label("spec_name"),
sql.gL("null").label("spec_description"),
sql.gL("null").label("spec_help"),
sql.gL("null::int").label("page_id"),
sql.gL("null::int").label("parent"),
sql.gL("null::int").label("ord"),
sql.gL("null").label("content"),
sql.gL("text2ltree('999999')").label("position"),
sql.gL("(select count(*) from e_pytis_menu)").label("position_nsub"),
sql.gL("false").label("changed"),
sql.gL("false").label("removed"),
],
[
"help_id",
"menuid",
"fullname",
"title",
"description",
"menu_help",
"spec_name",
"spec_description",
"spec_help",
"page_id",
"parent",
"ord",
"content",
"position",
"position_nsub",
"changed",
"removed",
],
),
from_obj=[series],
)
