class FUserCfg(sql.SQLRaw):
name = 'f_user_cfg'
schemas = pytis_schemas.value(globals())
@classmethod
def sql(class_):
return """
create or replace function f_user_cfg_datum_od() returns date as $$
begin
if (select count(*) from pg_class where relname='bv_users_cfg') > 0 then
return (select datum_od from bv_users_cfg);
elsif(select count(*) from pg_class where relname='cv_users_cfg') > 0 then
return (select datum_od from cv_users_cfg);
else
return '2000-01-01'::date;
end if;
end;
$$ language plpgsql stable;
create or replace function f_user_cfg_datum_do() returns date as $$
begin
if (select count(*) from pg_class where relname='bv_users_cfg') > 0 then
return (select datum_do from bv_users_cfg);
elsif(select count(*) from pg_class where relname='cv_users_cfg') > 0 then
return (select datum_do from cv_users_cfg);
else
return '2099-12-31'::date;
end if;
end;
$$ language plpgsql stable;
"""
depends_on = ()
class EPytisFormLog(sql.SQLTable):
"""
Statistics about using forms by users and form opening performance.
form is fully qualified specification name.
class is pytis class name of the form instance.
login is login name of the user who has opened the form.
info is optional extra information provided by the form (e.g. sorting used).
t_start is the time when user invoked the form opening command.
t_show is the time when the form got actually ready for operation after its start.
"""
name = 'e_pytis_form_log'
schemas = pytis_schemas.value(globals())
fields = (
sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('form', pytis.data.String(not_null=True), index=True),
sql.Column('class', pytis.data.String(not_null=True), index=True),
sql.Column('info', pytis.data.String(not_null=False), index=True),
sql.Column('login', pytis.data.Name(not_null=True), index=True),
sql.Column('t_start',
pytis.data.DateTime(not_null=True, without_timezone=True),
index=True),
sql.Column('t_show',
pytis.data.DateTime(not_null=True, without_timezone=True)),
)
inherits = (XChanges, )
with_oids = True
depends_on = ()
access_rights = default_access_rights.value(globals())
class EPytisCryptoKeys(Base_LogSQLTable):
"""Table of encryption keys of users for defined encryption areas."""
name = 'e_pytis_crypto_keys'
schemas = pytis_schemas.value(globals())
fields = (
sql.PrimaryColumn('key_id', pytis.data.Serial()),
sql.Column('name',
pytis.data.String(not_null=True),
label=_("Šifrovací oblast"),
references=sql.a(sql.r.CPytisCryptoNames.name,
onupdate='CASCADE')),
sql.Column('username',
pytis.data.String(not_null=True),
label=_("Uživatel"),
doc="Arbitrary user identifier."),
sql.Column('key', pytis.data.Binary(not_null=True)),
sql.Column(
'fresh',
pytis.data.Boolean(not_null=True),
label=_("Nový"),
doc=
"Flag indicating the key is encrypted by a non-login password. ",
default=False),
)
inherits = (XChanges, )
unique = ((
'name',
'username',
), )
depends_on = (CPytisCryptoNames, )
access_rights = default_access_rights.value(globals())
class PytisEncryptNumeric(sql.SQLRaw):
name = 'pytis_encrypt_float_overloaded'
schemas = pytis_schemas.value(globals())
@classmethod
def sql(class_):
return """
class PytisCryptoTUserContact(sql.SQLType):
name = 'pytis_crypto_t_user_contact'
schemas = pytis_schemas.value(globals())
fields = (
sql.Column('email', pytis.data.String(not_null=False)),
sql.Column('gpg_key', pytis.data.String(not_null=False)),
)
depends_on = ()
access_rights = ()
class PytisCryptoTKeyPair(sql.SQLType):
name = 'pytis_crypto_t_key_pair'
schemas = pytis_schemas.value(globals())
fields = (
sql.Column('public', pytis.data.String(not_null=False)),
sql.Column('private', pytis.data.String(not_null=False)),
)
depends_on = ()
access_rights = ()
class CopyUserProfile(sql.SQLFunction):
"""Zkopíruje profil z ev_pytis_form_profiles jinému uživateli."""
schemas = pytis_schemas.value(globals())
name = 'copy_user_profile'
arguments = (sql.Column('profile_id', pytis.data.String()),
sql.Column('username', pytis.data.String()),)
result_type = pytis.data.String()
multirow = False
stability = 'VOLATILE'
depends_on = (EvPytisFormProfiles,)
access_rights = ()
class PytisCryptPassword(sql.SQLPlFunction):
name = 'pytis_crypt_password'
schemas = pytis_schemas.value(globals())
arguments = (sql.Column('name_', pytis.data.String()), )
result_type = pytis.data.String()
multirow = False
stability = 'STABLE'
access_rights = ()
def body(self):
return """
class EvPytisFormUserList(sql.SQLView):
name = 'ev_pytis_form_user_list'
schemas = pytis_schemas.value(globals())
@classmethod
def query(cls):
log = sql.t.EPytisFormLog.alias('log')
return sqlalchemy.select(cls._exclude(log, 'id', 'form', 'class',
'info', 't_start', 't_show'),
from_obj=[log]).group_by('login')
depends_on = (EPytisFormLog, )
access_rights = default_access_rights.value(globals())
class EPytisConfig(sql.SQLTable):
"""Pytis application configuration storage."""
name = 'e_pytis_config'
schemas = pytis_schemas.value(globals())
fields = (sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('option', pytis.data.String(not_null=True)),
sql.Column('value', pytis.data.String(not_null=True)),
)
inherits = (XChanges,)
unique = (('username', 'option',),)
depends_on = ()
access_rights = default_access_rights.value(globals())
class FDateMonth(sql.SQLFunction):
"""Pomocná funkce pro agregační matici pytisu."""
schemas = pytis_schemas.value(globals())
name = 'f_date_month'
arguments = (sql.Column('', pytis.data.Date()),)
result_type = pytis.data.Integer()
multirow = False
stability = 'VOLATILE'
depends_on = ()
access_rights = ()
def body(self):
return "select date_part('month', $1)::int"
class PytisEncryptInt(sql.SQLPlFunction):
name = 'pytis_encrypt_int'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('data', pytis.data.Integer()),
sql.Column('name', pytis.data.String()),
)
result_type = pytis.data.Binary()
multirow = False
access_rights = ()
depends = (PytisCryptPassword, )
def body(self):
return """
class EvPytisFormProfiles(sql.SQLView):
"""Pytis profiles."""
name = 'ev_pytis_form_profiles'
schemas = pytis_schemas.value(globals())
@classmethod
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 on_delete(self):
return (
"(delete from e_pytis_form_profile_base where id = split_part(old.id, '.', 1)::int;"
"delete from e_pytis_form_profile_params where id = split_part(old.id, '.', 2)::int"
";)", )
depends_on = (
EPytisFormProfileBase,
EPytisFormProfileParams,
)
access_rights = default_access_rights.value(globals())
class EPytisFormSettings(sql.SQLTable):
"""Storage of pytis profile independent form settings."""
name = 'e_pytis_form_settings'
schemas = pytis_schemas.value(globals())
fields = (sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('spec_name', pytis.data.String(not_null=True)),
sql.Column('form_name', pytis.data.String(not_null=True)),
sql.Column('pickle', pytis.data.String(not_null=True)),
sql.Column('dump', pytis.data.String(not_null=False)),
)
inherits = (XChanges,)
unique = (('username', 'spec_name', 'form_name',),)
depends_on = ()
access_rights = default_access_rights.value(globals())
class EPytisAggregatedViews(sql.SQLTable):
"""Pytis aggregated views storage."""
name = 'e_pytis_aggregated_views'
schemas = pytis_schemas.value(globals())
fields = (sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('spec_name', pytis.data.String(not_null=True)),
sql.Column('aggregated_view_id', pytis.data.String(not_null=True)),
sql.Column('title', pytis.data.String(not_null=True)),
sql.Column('pickle', pytis.data.String(not_null=True)),
)
inherits = (XChanges,)
unique = (('username', 'spec_name', 'aggregated_view_id',),)
depends_on = ()
access_rights = default_access_rights.value(globals())
class PytisCryptoExtractKey(sql.SQLPlFunction):
name = 'pytis_crypto_extract_key'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('encrypted', pytis.data.Binary()),
sql.Column('psw', pytis.data.String()),
)
result_type = pytis.data.String()
multirow = False
stability = 'IMMUTABLE'
access_rights = ()
depends_on = (EPytisCryptoKeys, )
def body(self):
return """
class PytisCryptoUnlockCurrentUserPasswords(sql.SQLPlFunction):
name = 'pytis_crypto_unlock_current_user_passwords'
schemas = pytis_schemas.value(globals())
arguments = (sql.Column('password_', pytis.data.String()), )
result_type = pytis.data.String()
multirow = True
access_rights = ()
depends_on = (
EPytisCryptoKeys,
PytisCryptoDbKeys,
PytisCryptoUnlockPasswords,
)
def body(self):
return """
class EvPytisUserCryptoKeys(sql.SQLView):
name = 'ev_pytis_user_crypto_keys'
schemas = pytis_schemas.value(globals())
@classmethod
def query(cls):
keys = sql.t.EPytisCryptoKeys.alias('keys')
return sqlalchemy.select(
cls._exclude(keys, 'username', 'key'),
from_obj=[keys],
whereclause=keys.c.username == sqlalchemy.text('current_user'),
)
depends_on = (EPytisCryptoKeys, )
access_rights = default_access_rights.value(globals())
class PytisCryptoDbKeys(sql.SQLTable):
"""
Table of asymetric encryption keys.
It is currently used to encrypt user passwords passed to some database functions.
Use select pytis_crypto_create_db_key('pytis', 1024) to create a key for that purpose.
"""
name = 'pytis_crypto_db_keys'
schemas = pytis_schemas.value(globals())
fields = (
sql.PrimaryColumn('key_name', pytis.data.String(not_null=False)),
sql.Column('public', pytis.data.String(not_null=False)),
sql.Column('private', pytis.data.String(not_null=False)),
)
depends_on = ()
access_rights = crypto_select_rights.value(globals())
class CPytisCryptoNames(Base_LogSQLTable):
"""Codebook of encryption areas defined in the application."""
name = 'c_pytis_crypto_names'
schemas = pytis_schemas.value(globals())
fields = (
sql.PrimaryColumn('name',
pytis.data.String(not_null=False),
label=_("Šifrovací oblast")),
sql.Column('description',
pytis.data.String(not_null=False),
label=_("Popis")),
)
inherits = (XChanges, )
depends_on = ()
access_rights = default_access_rights.value(globals())
class EPytisActionLog(sql.SQLTable):
"""Pytis user actions log."""
name = 'e_pytis_action_log'
schemas = pytis_schemas.value(globals())
fields = (
sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('timestamp', pytis.data.DateTime(not_null=True)),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('spec_name', pytis.data.String(not_null=True)),
sql.Column('form_name', pytis.data.String(not_null=True)),
sql.Column('action', pytis.data.String(not_null=True)),
sql.Column('info', pytis.data.String(not_null=False)),
)
inherits = (XChanges, )
depends_on = ()
access_rights = default_access_rights.value(globals())
class PytisCryptoDecryptDbPassword(sql.SQLPlFunction):
name = 'pytis_crypto_decrypt_db_password'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('password_', pytis.data.String()),
sql.Column('key_name_', pytis.data.String()),
)
result_type = pytis.data.String()
multirow = False
stability = 'STABLE'
access_rights = ()
depends_on = (EPytisCryptoKeys, PytisCryptoDbKeys,
PytisCryptoDecryptUsingKey)
def body(self):
return """
class PytisCryptoDbKey(sql.SQLPlFunction):
name = 'pytis_crypto_db_key'
schemas = pytis_schemas.value(globals())
arguments = (sql.Column('key_name_', pytis.data.String()), )
result_type = pytis.data.String()
multirow = False
stability = 'STABLE'
security_definer = True
access_rights = ()
depends_on = (
EPytisCryptoKeys,
PytisCryptoDbKeys,
)
def body(self):
return """
class EvPytisFormUsers(sql.SQLView):
name = 'ev_pytis_form_users'
schemas = pytis_schemas.value(globals())
@classmethod
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')
depends_on = (EPytisFormLog, )
access_rights = default_access_rights.value(globals())
class PytisCryptoDeleteKey(sql.SQLPlFunction):
name = 'pytis_crypto_delete_key'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('name_', pytis.data.String()),
sql.Column('user_', pytis.data.String()),
sql.Column('force', pytis.data.Boolean()),
)
result_type = pytis.data.Boolean()
multirow = False
stability = 'VOLATILE'
access_rights = ()
depends_on = (EPytisCryptoKeys, )
def body(self):
return """
class EPytisFormProfileBase(sql.SQLTable):
"""Pytis form configuration storage."""
name = 'e_pytis_form_profile_base'
schemas = pytis_schemas.value(globals())
fields = (sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('spec_name', pytis.data.String(not_null=True)),
sql.Column('profile_id', pytis.data.String(not_null=True)),
sql.Column('title', pytis.data.String(not_null=True)),
sql.Column('pickle', pytis.data.String(not_null=True)),
sql.Column('dump', pytis.data.String(not_null=False)),
sql.Column('errors', pytis.data.String(not_null=False)),
)
inherits = (XChanges,)
unique = (('username', 'spec_name', 'profile_id',),)
depends_on = ()
access_rights = default_access_rights.value(globals())
class EPytisFormProfileParams(sql.SQLTable):
"""Pytis form profile form type specific parameters."""
name = 'e_pytis_form_profile_params'
schemas = pytis_schemas.value(globals())
fields = (sql.PrimaryColumn('id', pytis.data.Serial()),
sql.Column('username', pytis.data.Name(not_null=True)),
sql.Column('spec_name', pytis.data.String(not_null=True)),
sql.Column('form_name', pytis.data.String(not_null=True)),
sql.Column('profile_id', pytis.data.String(not_null=True)),
sql.Column('pickle', pytis.data.String(not_null=True)),
sql.Column('dump', pytis.data.String(not_null=False)),
sql.Column('errors', pytis.data.String(not_null=False)),
)
inherits = (XChanges,)
unique = (('username', 'spec_name', 'form_name', 'profile_id',),)
depends_on = ()
access_rights = default_access_rights.value(globals())
class PytisCryptoChangePassword(sql.SQLPlFunction):
name = 'pytis_crypto_change_password'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('id_', pytis.data.Integer()),
sql.Column('old_psw', pytis.data.String()),
sql.Column('new_psw', pytis.data.String()),
)
result_type = pytis.data.Boolean()
multirow = False
stability = 'VOLATILE'
access_rights = ()
security_definer = True
depends_on = (EPytisCryptoKeys, PytisCryptoExtractKey)
def body(self):
return """
class PytisCryptoCreateDbKey(sql.SQLFunction):
name = 'pytis_crypto_create_db_key'
schemas = pytis_schemas.value(globals())
arguments = (
sql.Column('key_name_', pytis.data.String()),
sql.Column('bits', pytis.data.Integer()),
)
result_type = None
multirow = False
access_rights = ()
depends_on = (
EPytisCryptoKeys,
PytisCryptoDbKeys,
PytisCryptoGenerateKey,
)
def body(self):
return """
class PytisCryptoGenerateKey(Base_PyFunction):
name = 'pytis_crypto_generate_key'
schemas = pytis_schemas.value(globals())
arguments = (sql.Column('', pytis.data.Integer()), )
result_type = PytisCryptoTKeyPair
multirow = False
stability = 'VOLATILE'
depends_on = (PytisCryptoTKeyPair, )
access_rights = ()
@staticmethod
def pytis_crypto_generate_key(bits):
bits = args[0]
import Crypto.PublicKey.RSA
rsa = Crypto.PublicKey.RSA.generate(bits)
public = rsa.publickey().exportKey()
private = rsa.exportKey()
return [public, private]
