class CmsActions(sql.SQLTable): """Enumeration of valid actions. (Including both module independent actions and per module actions.) Module independent actions have NULL in the mod_id column. """ name = '_cms_actions' external = True schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('action_id', pytis.data.Serial()), sql.Column('mod_id', pytis.data.Integer(not_null=False), references=sql.gA('cms_modules', ondelete='CASCADE')), sql.Column('name', pytis.data.String(maxlen=16, not_null=True)), sql.Column('description', pytis.data.String(not_null=True)), ) unique = (( 'mod_id', 'name', ), ) depends_on = (CmsModules, ) access_rights = cms_rights.value(globals()) init_columns = ('name', 'description') init_values = ( ('visit', 'Display the item content'), ('show', 'See the item in the menu'), )
class CmsMenuStructure(sql.SQLTable): """Language independent menu structure.""" name = 'cms_menu_structure' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('menu_item_id', pytis.data.Serial()), sql.Column('identifier', pytis.data.String(maxlen=32, not_null=True), unique=True), sql.Column('parent', pytis.data.Integer(not_null=False), references=sql.gA('cms_menu_structure')), sql.Column('mod_id', pytis.data.Integer(not_null=False), references=sql.gA('cms_modules')), sql.Column('ord', pytis.data.Integer(not_null=True)), sql.Column('tree_order', pytis.data.LTree(not_null=False)), ) index_columns = ( #('ord', sqlalchemy.literal_column('coalesce(parent, 0)'),), ( 'parent', 'ord', ), ) with_oids = True depends_on = (CmsModules, ) access_rights = cms_rights.value(globals())
class CmsUsersTable(sql.SQLTable): name = 'cms_users_table' schemas = cms_schemas.value(globals()) fields = (sql.PrimaryColumn('uid', pytis.data.Serial()), ) with_oids = True depends_on = () access_rights = ()
class CmsRightsAssignment(sql.SQLTable): """Underlying binding table between menu items, roles and module actions.""" name = 'cms_rights_assignment' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('rights_assignment_id', pytis.data.Serial()), sql.Column('menu_item_id', pytis.data.Integer(not_null=True), references=sql.gA('cms_menu_structure', ondelete='CASCADE')), sql.Column('role_id', pytis.data.Integer(not_null=True), references=sql.gA('cms_roles', ondelete='CASCADE')), sql.Column('action_id', pytis.data.Integer(not_null=True), references=sql.gA('cms_actions', ondelete='CASCADE')), ) with_oids = True unique = (( 'menu_item_id', 'role_id', 'action_id', ), ) depends_on = ( CmsMenuStructure, CmsRoles, CmsActions, ) access_rights = cms_rights.value(globals())
class CmsSessionLogData(sql.SQLTable): """Log of web user logins (underlying data).""" name = 'cms_session_log_data' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('log_id', pytis.data.Serial()), sql.Column('session_id', pytis.data.Integer(not_null=False), references=sql.gA('cms_session', ondelete='SET NULL')), sql.Column('uid', pytis.data.Integer(not_null=False), references=sql.gA(cms_users_table.value(globals()), ondelete='CASCADE')), sql.Column('login', pytis.data.String(not_null=True)), sql.Column('success', pytis.data.Boolean(not_null=True), default=False), sql.Column('start_time', pytis.data.DateTime(not_null=True)), sql.Column('end_time', pytis.data.DateTime(not_null=False)), sql.Column('ip_address', pytis.data.String(not_null=True)), sql.Column('user_agent', pytis.data.String(not_null=False)), sql.Column('referer', pytis.data.String(not_null=False)), ) with_oids = True depends_on = () access_rights = cms_rights_rw.value(globals())
class CmsMenuTexts(sql.SQLTable): """Language dependent texts and properties for menu items.""" name = 'cms_menu_texts' schemas = cms_schemas.value(globals()) fields = ( sql.Column('menu_item_id', pytis.data.Integer(not_null=True), references=sql.gA('cms_menu_structure', ondelete='CASCADE')), sql.Column('lang', pytis.data.String(minlen=2, maxlen=2, not_null=True), references=sql.gA('cms_languages(lang)', ondelete='CASCADE')), sql.Column('published', pytis.data.Boolean(not_null=True), default='TRUE'), sql.Column('title', pytis.data.String(not_null=True)), sql.Column('heading', pytis.data.String(not_null=False)), sql.Column('description', pytis.data.String(not_null=False)), sql.Column('content', pytis.data.String(not_null=False)), ) with_oids = True depends_on = ( CmsMenuStructure, CmsLanguages, ) access_rights = cms_rights.value(globals())
class CmsMenuStructureUniqueTreeOrder(sql.SQLRaw): name = 'cms_menu_structure_unique_tree_order' schemas = cms_schemas.value(globals()) @classmethod def sql(class_): return ("CREATE UNIQUE INDEX cms_menu_structure_unique_tree_order " "ON cms_menu_structure (ord, coalesce(parent, 0));") depends_on = (CmsMenuStructure, )
class CmsMenuStructureTreeOrder(sql.SQLFunction): """Generate a sortable string representing the hierarchical position of given menu item.""" schemas = cms_schemas.value(globals()) name = 'cms_menu_structure_tree_order' arguments = (sql.Column('', pytis.data.Integer()), ) result_type = pytis.data.LTree() multirow = False stability = 'VOLATILE' depends_on = (CmsMenuStructure, ) access_rights = ()
class CmsUsers(sql.SQLTable): name = 'pytis_cms_users' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('uid', pytis.data.Serial()), sql.Column('login', pytis.data.String(not_null=True), unique=True), sql.Column('fullname', pytis.data.String(not_null=True)), sql.Column('passwd', pytis.data.String(not_null=True)), ) depends_on = () access_rights = ()
class CmsModules(sql.SQLTable): """Codebook of extension modules available in the CMS.""" name = 'cms_modules' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('mod_id', pytis.data.Serial()), sql.Column('modname', pytis.data.String(maxlen=64, not_null=True), unique=True), ) with_oids = True depends_on = () access_rights = cms_rights.value(globals())
class CmsLanguages(sql.SQLTable): """Codebook of languages available in the CMS.""" name = 'cms_languages' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('lang_id', pytis.data.Serial()), sql.Column('lang', pytis.data.String(minlen=2, maxlen=2, not_null=True), unique=True), ) with_oids = True depends_on = () access_rights = cms_rights.value(globals())
class X186(sql.SQLRaw): name = '@186' schemas = cms_schemas.value(globals()) @classmethod def sql(class_): return ( "create or replace rule session_delete as on delete to cms_session do " "( update cms_session_log_data set end_time=old.last_access " "WHERE session_id=old.session_id;)") depends_on = ( CmsSession, CmsSessionLogData, )
class CmsRoles(sql.SQLTable): """CMS roles.""" name = 'cms_roles' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('role_id', pytis.data.Serial()), sql.Column('name', pytis.data.String(not_null=True)), sql.Column('system_role', pytis.data.String(not_null=False), unique=True), sql.Column('description', pytis.data.String(not_null=False)), ) with_oids = True depends_on = () access_rights = cms_rights.value(globals())
class CmsSessionLog(sql.SQLView): """Log of web user logins (user visible information).""" name = 'cms_session_log' schemas = cms_schemas.value(globals()) @classmethod 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 on_insert(self): return ( """INSERT INTO cms_session_log_data (session_id, uid, login, success, start_time, ip_address, user_agent, referer) VALUES (new.session_id, new.uid, new.login, new.success, new.start_time, new.ip_address, new.user_agent, new.referer) RETURNING log_id, session_id, uid, login, success, start_time, ip_address, user_agent, referer, NULL::text, NULL::interval, NULL::boolean""", ) update_order = (CmsSessionLogData, ) no_update_columns = ( 'duration', 'active', ) delete_order = (CmsSessionLogData, ) depends_on = ( CmsSession, CmsSessionLogData, CmsUsers, ) access_rights = cms_rights.value(globals())
class CmsSession(sql.SQLTable): """Web user session information for authentication and login history.""" name = 'cms_session' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('session_id', pytis.data.Serial()), sql.Column('uid', pytis.data.Integer(not_null=True), references=sql.gA(cms_users_table.value(globals()), ondelete='CASCADE')), sql.Column('session_key', pytis.data.String(not_null=True)), sql.Column('last_access', pytis.data.DateTime(not_null=True)), ) unique = (( 'uid', 'session_key', ), ) depends_on = () access_rights = cms_rights_rw.value(globals())
class CmsRoles(sql.SQLTable): """CMS roles.""" name = '_cms_roles' external = True schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('role_id', pytis.data.Serial()), sql.Column('name', pytis.data.String(not_null=True)), sql.Column('system_role', pytis.data.String(not_null=False), unique=True), sql.Column('description', pytis.data.String(not_null=False)), ) depends_on = () access_rights = cms_rights.value(globals()) init_columns = ('system_role', 'name') init_values = ( ('ANYONE', 'Anyone'), ('USER', 'Logged user'), )
class CmsUserRoles(sql.SQLView): name = 'cms_user_roles' schemas = cms_schemas.value(globals()) @classmethod def query(cls): a_ = sql.t.CmsUserRoleAssignment.alias('a') u = sql.t.CmsUsers.alias('u') r_ = sql.t.CmsRoles.alias('r') return sqlalchemy.select( cls._exclude(a_) + cls._exclude(r_, 'role_id') + [u.c.login.label('login'), u.c.fullname.label('fullname')], from_obj=[ a_.join(u, sql.gR('a.uid = u.uid')).join( r_, sql.gR('a.role_id = r.role_id')) ]) def on_insert(self): return ( "INSERT INTO cms_user_role_assignment (user_role_id, uid, role_id) " "VALUES (new.user_role_id, new.uid, new.role_id) " "RETURNING user_role_id, uid, role_id, NULL::text, NULL::text, NULL::text, " "NULL::text, NULL::text", ) def on_update(self): return ( "UPDATE cms_user_role_assignment SET uid = new.uid, role_id = new.role_id " "WHERE user_role_id=old.user_role_id", ) def on_delete(self): return ( "DELETE FROM cms_user_role_assignment WHERE user_role_id = old.user_role_id", ) depends_on = ( CmsUserRoleAssignment, CmsUsers, CmsRoles, ) access_rights = cms_rights.value(globals())
class CmsUserRoleAssignment(sql.SQLTable): """Binding table assigning CMS roles to CMS users.""" name = 'cms_user_role_assignment' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('user_role_id', pytis.data.Serial()), sql.Column('uid', pytis.data.Integer(not_null=True), references=sql.gA(cms_users_table.value(globals()), ondelete='CASCADE')), sql.Column('role_id', pytis.data.Integer(not_null=True), references=sql.gA('cms_roles', ondelete='CASCADE')), ) with_oids = True unique = (( 'uid', 'role_id', ), ) depends_on = (CmsRoles, ) access_rights = cms_rights.value(globals())
class CmsAccessLogData(sql.SQLTable): """Log of cms page access.""" name = 'cms_access_log_data' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('log_id', pytis.data.Serial()), sql.Column('timestamp', pytis.data.DateTime(not_null=True)), sql.Column('uri', pytis.data.String(not_null=True)), sql.Column('uid', pytis.data.Integer(not_null=False), references=sql.gA(cms_users_table.value(globals()), ondelete='CASCADE')), sql.Column('modname', pytis.data.String(not_null=False)), sql.Column('action', pytis.data.String(not_null=False)), sql.Column('ip_address', pytis.data.String(not_null=True)), sql.Column('user_agent', pytis.data.String(not_null=False)), sql.Column('referer', pytis.data.String(not_null=False)), ) with_oids = True depends_on = () access_rights = cms_rights_rw.value(globals())
class CmsThemes(sql.SQLTable): """Definition of available color themes.""" name = 'cms_themes' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('theme_id', pytis.data.Serial()), sql.Column('name', pytis.data.String(not_null=True), unique=True), sql.Column('foreground', pytis.data.Color(not_null=False)), sql.Column('background', pytis.data.Color(not_null=False)), sql.Column('border', pytis.data.Color(not_null=False)), sql.Column('heading_fg', pytis.data.Color(not_null=False)), sql.Column('heading_bg', pytis.data.Color(not_null=False)), sql.Column('heading_line', pytis.data.Color(not_null=False)), sql.Column('frame_fg', pytis.data.Color(not_null=False)), sql.Column('frame_bg', pytis.data.Color(not_null=False)), sql.Column('frame_border', pytis.data.Color(not_null=False)), sql.Column('link', pytis.data.Color(not_null=False)), sql.Column('link_visited', pytis.data.Color(not_null=False)), sql.Column('link_hover', pytis.data.Color(not_null=False)), sql.Column('meta_fg', pytis.data.Color(not_null=False)), sql.Column('meta_bg', pytis.data.Color(not_null=False)), sql.Column('help', pytis.data.Color(not_null=False)), sql.Column('error_fg', pytis.data.Color(not_null=False)), sql.Column('error_bg', pytis.data.Color(not_null=False)), sql.Column('error_border', pytis.data.Color(not_null=False)), sql.Column('message_fg', pytis.data.Color(not_null=False)), sql.Column('message_bg', pytis.data.Color(not_null=False)), sql.Column('message_border', pytis.data.Color(not_null=False)), sql.Column('table_cell', pytis.data.Color(not_null=False)), sql.Column('table_cell2', pytis.data.Color(not_null=False)), sql.Column('top_fg', pytis.data.Color(not_null=False)), sql.Column('top_bg', pytis.data.Color(not_null=False)), sql.Column('top_border', pytis.data.Color(not_null=False)), sql.Column('highlight_bg', pytis.data.Color(not_null=False)), sql.Column('inactive_folder', pytis.data.Color(not_null=False)), ) with_oids = True depends_on = () access_rights = cms_rights.value(globals())
class CmsActions(sql.SQLTable): """Enumeration of valid actions. (Including both module independent actions and per module actions.) Module independent actions have NULL in the mod_id column. """ name = 'cms_actions' schemas = cms_schemas.value(globals()) fields = ( sql.PrimaryColumn('action_id', pytis.data.Serial()), sql.Column('mod_id', pytis.data.Integer(not_null=False), references=sql.gA('cms_modules', ondelete='CASCADE')), sql.Column('name', pytis.data.String(maxlen=16, not_null=True)), sql.Column('description', pytis.data.String(not_null=True)), ) with_oids = True unique = (( 'mod_id', 'name', ), ) depends_on = (CmsModules, ) access_rights = cms_rights.value(globals())
class CmsRights(sql.SQLView): """User editable access rights assignment.""" name = '_cms_rights' external = True schemas = cms_schemas.value(globals()) @classmethod def query(cls): x = sql.t.CmsRightsAssignment.alias('x') s = sql.t.CmsMenuStructure.alias('s') r_ = sql.t.CmsRoles.alias('r') a_ = sql.t.CmsActions.alias('a') return sqlalchemy.select( cls._exclude(x) + [ r_.c.name.label('role_name'), s.c.mod_id.label('mod_id'), r_.c.description.label('role_description'), r_.c.system_role.label('system_role'), a_.c.name.label('action_name'), a_.c.description.label('action_description') ], from_obj=[ x.join(s, s.c.menu_item_id == x.c.menu_item_id).join( r_, r_.c.role_id == x.c.role_id).join( a_, a_.c.action_id == x.c.action_id) ]) insert_order = (CmsRightsAssignment, ) update_order = (CmsRightsAssignment, ) delete_order = (CmsRightsAssignment, ) depends_on = ( CmsRightsAssignment, CmsMenuStructure, CmsRoles, CmsActions, ) access_rights = cms_rights.value(globals())
class CmsMenu(sql.SQLView): """Complete menu structure with texts for each language defined in cms_languages.""" name = 'cms_menu' schemas = cms_schemas.value(globals()) @classmethod 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 on_insert(self): return ("""( INSERT INTO cms_menu_structure (identifier, parent, mod_id, ord) VALUES (new.identifier, new.parent, new.mod_id, coalesce(new.ord, (SELECT max(ord)+100 FROM cms_menu_structure WHERE coalesce(parent, 0)=coalesce(new.parent, 0)), 100)); UPDATE cms_menu_structure SET tree_order = cms_menu_structure_tree_order(menu_item_id); INSERT INTO cms_menu_texts (menu_item_id, lang, published, title, heading, description, content) SELECT (SELECT menu_item_id FROM cms_menu_structure WHERE identifier=new.identifier), new.lang, new.published, new.title, new.heading, new.description, new.content RETURNING menu_item_id, NULL::varchar(32), NULL::int, NULL::int, NULL::int, NULL::ltree, lang, title, heading, description, content, NULL::varchar(64), menu_item_id ||'.'|| lang, published, title, 0::bigint )""", ) def on_update(self): return ("""( UPDATE cms_menu_structure SET identifier = new.identifier, parent = new.parent, mod_id = new.mod_id, ord = new.ord WHERE cms_menu_structure.menu_item_id = old.menu_item_id; UPDATE cms_menu_structure SET tree_order = cms_menu_structure_tree_order(menu_item_id); UPDATE cms_menu_texts SET published = new.published, title = new.title, heading = new.heading, description = new.description, content = new.content WHERE menu_item_id = old.menu_item_id AND lang = new.lang; INSERT INTO cms_menu_texts (menu_item_id, lang, published, title, heading, description, content) SELECT old.menu_item_id, new.lang, new.published, new.title, new.heading, new.description, new.content WHERE new.lang NOT IN (SELECT lang FROM cms_menu_texts WHERE menu_item_id=old.menu_item_id) AND coalesce(new.title, new.heading, new.description, new.content) IS NOT NULL; )""", ) def on_delete(self): return ( "(DELETE FROM cms_menu_structure WHERE menu_item_id = old.menu_item_id;)", ) depends_on = ( CmsMenuStructure, CmsLanguages, CmsMenuTexts, CmsModules, ) access_rights = cms_rights.value(globals())