def ref_order_save(self, attributes='', came_from=''):
# save reference order
attributes = sj.loads(attributes)
cls_title = None
for attr in attributes:
attribute = dbs.query(SapnsAttribute).get(attr['id'])
if not cls_title:
cls_title = attribute.class_.title
attribute.reference_order = attr['order']
dbs.add(attribute)
dbs.flush()
if came_from:
redirect(url(came_from))
else:
redirect(
url('/message',
params=dict(message=_(
'Reference order for "%s" has been successfully updated'
) % cls_title,
came_from='')))
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = u'%s#%s' % (klass.name,
name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = '%s#%s' % (klass.name, name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
def add_update(code, description):
# save "update"
new_u = SapnsUpdates()
new_u.code = code
new_u.description = description
new_u.exec_date = dt.datetime.now()
dbs.add(new_u)
dbs.flush()
def add_update(code, description):
# save "update"
new_u = SapnsUpdates()
new_u.code = code
new_u.description = description
new_u.exec_date = dt.datetime.now()
dbs.add(new_u)
dbs.flush()
def setUp(self):
"""Prepare model test fixture."""
try:
new_attrs = {}
new_attrs.update(self.attrs)
new_attrs.update(self.do_get_dependencies())
self.obj = self.klass(**new_attrs)
DBSession.add(self.obj)
DBSession.flush()
return self.obj
except:
DBSession.rollback()
raise
def __call__(self):
csfp = SapnsPermission()
csfp.class_id = SapnsClass.by_name(u'sp_permission').class_id
csfp.permission_name = u'sp_permission#create_shortcuts'
csfp.display_name = u'Create shortcuts'
csfp.type = SapnsPermission.TYPE_PROCESS
csfp.url = u'/dashboard/permissions/create_shortcuts/'
csfp.requires_id = True
dbs.add(csfp)
dbs.flush()
managers = SapnsRole.by_name(u'managers')
managers.permissions_.append(csfp)
dbs.flush()
def save(self, **kw):
logger = logging.getLogger('DocsController.save')
try:
# collect params
id_author = request.identity['user'].user_id
id_object = get_paramw(kw, 'id_object', int)
id_class = get_paramw(kw, 'id_class', int)
title = get_paramw(kw, 'title', unicode)
id_type = get_paramw(kw, 'id_type', int, opcional=True)
id_format = get_paramw(kw, 'id_format', int)
id_repo = get_paramw(kw, 'id_repo', int)
file_name = get_paramw(kw, 'file_name', unicode)
id_doc = get_paramw(kw, 'id_doc', int, opcional=True)
# create doc
if id_doc:
# edit
new_doc = dbs.query(SapnsDoc).get(id_doc)
else:
# create
new_doc = SapnsDoc()
new_doc.repo_id = id_repo
new_doc.doctype_id = id_type
new_doc.docformat_id = id_format
new_doc.author_id = id_author
new_doc.filename = file_name
new_doc.title = title
dbs.add(new_doc)
dbs.flush()
if not id_doc:
# (only at creation)
new_doc.register(id_class, id_object)
return dict(status=True)
except Exception, e:
logger.error(e)
return dict(status=False, message=str(e).decode('utf-8'))
def update_role(self, **kw):
logger = logging.getLogger('UsersController.roles')
try:
id_user = get_paramw(kw, 'id_user', int)
id_role = get_paramw(kw, 'id_role', int)
selected = get_paramw(kw, 'selected', strtobool)
if id_user == 1 and id_role == 1:
raise Exception(
'It is not possible to remove "managers" privilege from "superuser"'
)
user_role = dbs.query(SapnsUserRole).\
filter(and_(SapnsUserRole.role_id == id_role,
SapnsUserRole.user_id == id_user,
)).\
first()
if selected:
if not user_role:
user_role = SapnsUserRole()
user_role.role_id = id_role
user_role.user_id = id_user
dbs.add(user_role)
dbs.flush()
else:
if user_role:
user_role = dbs.query(SapnsUserRole).\
filter(and_(SapnsUserRole.role_id == id_role,
SapnsUserRole.user_id == id_user,
)).\
delete()
dbs.flush()
return sj.dumps(dict(status=True))
except Exception, e:
logger.error(e)
return sj.dumps(dict(status=False))
def create_dashboards(us):
logger = logging.getLogger('lib.sapns.util.create_dashboards')
#us = dbs.query(SapnsUser).get(id_user)
logger.info('Creating dashboard for "%s"' % us.display_name)
# user's dashboard
dboard = us.get_dashboard()
if not dboard:
dboard = SapnsShortcut()
dboard.user_id = us.user_id
dboard.parent_id = None
dboard.title = unicode(l_('Dashboard'))
dboard.order = 0
dbs.add(dboard)
dbs.flush()
# data exploration
data_ex = SapnsShortcut()
data_ex.title = unicode(l_('Data exploration'))
data_ex.parent_id = dboard.shortcut_id
data_ex.user_id = us.user_id
data_ex.order = 0
dbs.add(data_ex)
dbs.flush()
# Data exploration/Sapns
sc_sapns = SapnsShortcut()
sc_sapns.title = 'Sapns'
sc_sapns.parent_id = data_ex.shortcut_id
sc_sapns.user_id = us.user_id
sc_sapns.order = 0
dbs.add(sc_sapns)
# Data exploration/Project
sc_project = SapnsShortcut()
sc_project.title = config.get('app.name', unicode(l_('Project')))
sc_project.parent_id = data_ex.shortcut_id
sc_project.user_id = us.user_id
sc_project.order = 1
dbs.add(sc_project)
dbs.flush()
else:
logger.info('Dashboard already exists')
def create_dashboards(us):
logger = logging.getLogger('lib.sapns.util.create_dashboards')
#us = dbs.query(SapnsUser).get(id_user)
logger.info('Creating dashboard for "%s"' % us.display_name)
# user's dashboard
dboard = us.get_dashboard()
if not dboard:
dboard = SapnsShortcut()
dboard.user_id = us.user_id
dboard.parent_id = None
dboard.title = unicode(l_('Dashboard'))
dboard.order = 0
dbs.add(dboard)
dbs.flush()
# data exploration
data_ex = SapnsShortcut()
data_ex.title = unicode(l_('Data exploration'))
data_ex.parent_id = dboard.shortcut_id
data_ex.user_id = us.user_id
data_ex.order = 0
dbs.add(data_ex)
dbs.flush()
# Data exploration/Sapns
sc_sapns = SapnsShortcut()
sc_sapns.title = 'Sapns'
sc_sapns.parent_id = data_ex.shortcut_id
sc_sapns.user_id = us.user_id
sc_sapns.order = 0
dbs.add(sc_sapns)
# Data exploration/Project
sc_project = SapnsShortcut()
sc_project.title = config.get('app.name', unicode(l_('Project')))
sc_project.parent_id = data_ex.shortcut_id
sc_project.user_id = us.user_id
sc_project.order = 1
dbs.add(sc_project)
dbs.flush()
else:
logger.info('Dashboard already exists')
def __init__(self, username_or_email):
#logger = logging.getLogger('ForgotPassword.__init__')
self.u = dbs.query(SapnsUser).\
filter(or_(func.upper(SapnsUser.user_name) == func.upper(username_or_email),
func.upper(SapnsUser.email_address) == func.upper(username_or_email),
)).\
first()
if not self.u:
raise EUserDoesNotExist
# generate a random password
random.seed()
s1 = hl.sha1('%6.6d' % random.randint(0, 999999))
self.new_password = ''
for c in s1.hexdigest()[:random.randint(10, 15)]:
if random.randint(0, 1):
self.new_password += c.upper()
else:
self.new_password += c
self.u.password = self.new_password
dbs.add(self.u)
dbs.flush()
self.dst = [(
self.u.email_address.encode('utf-8'),
self.u.user_name.encode('utf-8'),
)]
# e-mail settings
self.remitente = (
config.get('mail.e_mail'),
config.get('mail.name'),
)
# get e-mail templates
self.env = Environment(loader=PackageLoader('sapns', 'templates'))
def update_role(self, **kw):
logger = logging.getLogger('UsersController.roles')
try:
id_user = get_paramw(kw, 'id_user', int)
id_role = get_paramw(kw, 'id_role', int)
selected = get_paramw(kw, 'selected', strtobool)
if id_user == 1 and id_role == 1:
raise Exception('It is not possible to remove "managers" privilege from "superuser"')
user_role = dbs.query(SapnsUserRole).\
filter(and_(SapnsUserRole.role_id == id_role,
SapnsUserRole.user_id == id_user,
)).\
first()
if selected:
if not user_role:
user_role = SapnsUserRole()
user_role.role_id = id_role
user_role.user_id = id_user
dbs.add(user_role)
dbs.flush()
else:
if user_role:
user_role = dbs.query(SapnsUserRole).\
filter(and_(SapnsUserRole.role_id == id_role,
SapnsUserRole.user_id == id_user,
)).\
delete()
dbs.flush()
return sj.dumps(dict(status=True))
except Exception, e:
logger.error(e)
return sj.dumps(dict(status=False))
def create_shortcut_(self, **kw):
logger = logging.getLogger('PermissionsController.create_shortcut_')
try:
user_id = get_paramw(kw, 'user_id', int)
permission_id = get_paramw(kw, 'permission_id', int)
groups = get_paramw(kw, 'groups', sj.loads)
p = dbs.query(SapnsPermission).get(permission_id)
if p.type not in [SapnsPermission.TYPE_LIST, SapnsPermission.TYPE_PROCESS] or \
p.type == SapnsPermission.TYPE_PROCESS and p.requires_id:
raise EPermissions(_(u'Shortcuts can only be created from LIST and PROCESS (no required id) type permissions'))
title = p.display_name
if p.type == SapnsPermission.TYPE_LIST:
title = p.class_.title
for id_group in groups:
group = dbs.query(SapnsShortcut).get(id_group)
sc = SapnsShortcut()
sc.user_id = user_id
sc.parent_id = id_group
sc.permission_id = permission_id
sc.title = title
sc.order = group.next_order()
dbs.add(sc)
dbs.flush()
_key = '%d_%d' % (user_id, id_group)
cache.get_cache('user_get_shortcuts').remove_value(key=_key)
return dict(status=True)
except EPermissions, e:
logger.error(e)
return dict(status=False, msg=unicode(e))
def ins_order_save(self, attributes='', title='', came_from=''):
# save insertion order
attributes = sj.loads(attributes)
title_saved = False
cls_title = None
for attr in attributes:
attribute = dbs.query(SapnsAttribute).get(attr['id'])
if not cls_title:
cls_title = attribute.class_.title
attribute.title = attr['title']
attribute.insertion_order = attr['order']
attribute.required = attr['required']
attribute.visible = attr['visible']
dbs.add(attribute)
if not title_saved:
title_saved = True
attribute.class_.title = title
dbs.add(attribute.class_)
dbs.flush()
if came_from:
redirect(url(came_from))
else:
redirect(
url('/message',
params=dict(message=_(
'Insertion order for "%s" has been successfully updated'
) % cls_title,
came_from='')))
def ref_order_save(self, attributes='', came_from=''):
# save reference order
attributes = sj.loads(attributes)
cls_title = None
for attr in attributes:
attribute = dbs.query(SapnsAttribute).get(attr['id'])
if not cls_title:
cls_title = attribute.class_.title
attribute.reference_order = attr['order']
dbs.add(attribute)
dbs.flush()
if came_from:
redirect(url(came_from))
else:
redirect(url('/message',
params=dict(message=_('Reference order for "%s" has been successfully updated') % cls_title,
came_from='')))
def __call__(self):
logger = logging.getLogger('TM_Test')
managers = SapnsRole.by_name(u'managers')
for usuario in managers.users_:
logger.info(usuario.display_name)
db = usuario.get_dashboard()
for sc in db.children:
logger.info(sc.title)
sc.title = '_%s' % sc.title
dbs.add(sc)
dbs.flush()
def attributes(cls):
for attr in cls.attributes:
logger.info(attr.name)
attr.title = '*%s' % attr.title
dbs.add(attr)
dbs.flush()
attributes(SapnsClass.by_name(u'alumnos'))
attributes(SapnsClass.by_name(u'clientes'))
def __init__(self, username_or_email):
#logger = logging.getLogger('ForgotPassword.__init__')
self.u = dbs.query(SapnsUser).\
filter(or_(func.upper(SapnsUser.user_name) == func.upper(username_or_email),
func.upper(SapnsUser.email_address) == func.upper(username_or_email),
)).\
first()
if not self.u:
raise EUserDoesNotExist
# generate a random password
random.seed()
s1 = hl.sha1('%6.6d' % random.randint(0, 999999))
self.new_password = ''
for c in s1.hexdigest()[:random.randint(10, 15)]:
if random.randint(0, 1):
self.new_password += c.upper()
else:
self.new_password += c
self.u.password = self.new_password
dbs.add(self.u)
dbs.flush()
self.dst = [(self.u.email_address.encode('utf-8'), self.u.user_name.encode('utf-8'),)]
# e-mail settings
self.remitente = (config.get('mail.e_mail'), config.get('mail.name'),)
# get e-mail templates
self.env = Environment(loader=PackageLoader('sapns', 'templates'))
def ins_order_save(self, attributes='', title='', came_from=''):
# save insertion order
attributes = sj.loads(attributes)
title_saved = False
cls_title = None
for attr in attributes:
attribute = dbs.query(SapnsAttribute).get(attr['id'])
if not cls_title:
cls_title = attribute.class_.title
attribute.title = attr['title']
attribute.insertion_order = attr['order']
attribute.required = attr['required']
attribute.visible = attr['visible']
dbs.add(attribute)
if not title_saved:
title_saved = True
attribute.class_.title = title
dbs.add(attribute.class_)
dbs.flush()
if came_from:
redirect(url(came_from))
else:
redirect(url('/message',
params=dict(message=_('Insertion order for "%s" has been successfully updated') % cls_title,
came_from='')))
#logger.info(_trigger)
try:
dbs.execute(_trigger)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# update related classes
for attr_id, fk_table in pending_attr.iteritems():
attr = dbs.query(SapnsAttribute).get(attr_id)
attr.related_class_id = tables_id[fk_table].class_id
dbs.add(attr)
dbs.flush()
def create_dashboards(self, us):
logger = logging.getLogger('lib.sapns.util.create_dashboards')
logger.info('Creating dashboard for "%s"' % us.display_name)
# user's dashboard
dboard = us.get_dashboard()
if not dboard:
dboard = SapnsShortcut()
dboard.user_id = us.user_id
dboard.parent_id = None
def attributes(cls):
for attr in cls.attributes:
logger.info(attr.name)
attr.title = '*%s' % attr.title
dbs.add(attr)
dbs.flush()
def create_data_exploration():
managers = SapnsRole.by_name(ROLE_MANAGERS)
#managers = SapnsRole()
logger = logging.getLogger('lib.sapns.util.create_data_exploration')
tables = extract_model(all=True) #['tables']
for us in dbs.query(SapnsUser).\
join((SapnsUserRole,
and_(SapnsUserRole.user_id == SapnsUser.user_id,
SapnsUserRole.role_id == managers.group_id,
))):
create_dashboards(us)
data_ex = us.get_dataexploration()
sc_sapns = data_ex.by_order(0)
sc_project = data_ex.by_order(1)
# data exploration/project
for i, tbl in enumerate(tables):
cls = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
# look for this table "list" action
act_table = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.type == SapnsPermission.TYPE_LIST,
SapnsPermission.class_id == cls.class_id)).\
first()
if not act_table:
act_table = SapnsPermission()
act_table.permission_name = '%s#%s' % (cls.name, SapnsPermission.TYPE_LIST)
act_table.display_name = unicode(l_('List'))
act_table.type = SapnsPermission.TYPE_LIST
act_table.class_id = cls.class_id
dbs.add(act_table)
dbs.flush()
# add to "managers" role
managers.permissions_.append(act_table)
dbs.flush()
# project
sc_parent = sc_project.shortcut_id
if cls.name.startswith('sp_'):
# sapns
sc_parent = sc_sapns.shortcut_id
sc_table = dbs.query(SapnsShortcut).\
filter(and_(SapnsShortcut.parent_id == sc_parent,
SapnsShortcut.permission_id == act_table.permission_id,
SapnsShortcut.user_id == us.user_id,
)).\
first()
# does this user have this class shortcut?
if not sc_table:
sc_table = SapnsShortcut()
sc_table.title = tbl['name']
sc_table.parent_id = sc_parent
sc_table.user_id = us.user_id
sc_table.permission_id = act_table.permission_id
sc_table.order = i
dbs.add(sc_table)
dbs.flush()
else:
logger.info('Shortcut for "%s" already exists' % cls.title)
# TODO: sort (alphabetically) shortcuts inside "data exploration"
logger.info('Sorting shortcuts inside "data exploration"')
i = 0
for sc in dbs.query(SapnsShortcut).\
filter(SapnsShortcut.parent_id == sc_project.shortcut_id).\
order_by(SapnsShortcut.title):
sc.order = i
dbs.add(sc)
dbs.flush()
i += 1
meta = MetaData(bind=dbs.bind)
tbl = Table(cls.name, meta, autoload=True)
is_insert = False
if update.get('id'):
logger.info('Updating object [%d] of "%s"' % (update['id'], cls.name))
dbs.execute(tbl.update(whereclause=tbl.c.id == update['id'], values=update))
else:
logger.info('Inserting new object in "%s"' % cls.name)
ins = tbl.insert(values=update).returning(tbl.c.id)
r = dbs.execute(ins)
is_insert = True
ch_cls.name = ch_cls.name
dbs.add(ch_cls)
dbs.flush()
if not update.get('id'):
update['id'] = r.fetchone().id
_exec_post_conditions('after', 'sapns', update)
_exec_post_conditions('after', config.get('app.root_folder'), update)
# TODO: log
_desc = _('updating an existing record')
_what = _('update')
if is_insert:
_desc = _('creating a new record')
_what = _('create')
def update_metadata():
logger = logging.getLogger('lib.sapns.update_metadata')
managers = SapnsRole.by_name(ROLE_MANAGERS)
#managers = SapnsRole()
tables = extract_model(all=True)
tables_id = {}
pending_attr = {}
for tbl in tables:
logger.info('Table: %s' % tbl['name'])
klass = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
if not klass:
logger.warning('.....creating')
klass = SapnsClass()
klass.name = tbl['name']
klass.title = tbl['name'].title()
desc = unicode(l_('Class: %s'))
klass.description = desc % tbl['name']
dbs.add(klass)
dbs.flush()
# # grant access (r/w) to managers
managers.add_privilege(klass.class_id)
else:
logger.warning('.....already exists')
tables_id[tbl['name']] = klass.class_id
# create an action
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = '%s#%s' % (klass.name, name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
# create standard actions
create_action(unicode(l_('New')), SapnsPermission.TYPE_NEW)
create_action(unicode(l_('Edit')), SapnsPermission.TYPE_EDIT)
create_action(unicode(l_('Delete')), SapnsPermission.TYPE_DELETE)
create_action(unicode(l_('List')), SapnsPermission.TYPE_LIST)
create_action(unicode(l_('Docs')), SapnsPermission.TYPE_DOCS)
first_ref = False
for i, col in enumerate(tbl['columns']):
logger.info('Column: %s' % col['name'])
attr = dbs.query(SapnsAttribute).\
filter(and_(SapnsAttribute.name == col['name'],
SapnsAttribute.class_id == klass.class_id,
)).\
first()
if not attr and col['name'] != 'id':
logger.warning('.....creating')
attr = SapnsAttribute()
attr.name = col['name']
attr.title = col['name'].replace('_', ' ').title()
attr.class_id = klass.class_id
attr.type = col['type_name']
if attr.type == SapnsAttribute.TYPE_STRING and not first_ref:
attr.reference_order = 0
first_ref = True
attr.visible = True
attr.insertion_order = i
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
# col['prec']
# col['scale']
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec']-col['scale'],
col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
dbs.add(attr)
dbs.flush()
# grant access (r/w) to managers
rw_access = SapnsAttrPrivilege.ACCESS_READWRITE
managers.add_attr_privilege(attr.attribute_id, rw_access)
else:
logger.warning('.....already exists')
# fill the "field_regex"
if attr and not attr.field_regex:
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec'] - col['scale'], col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
# foreign key
if col['fk_table'] != None:
pending_attr[attr.attribute_id] = col['fk_table'].name
# update related classes
for attr_id, fk_table in pending_attr.iteritems():
attr = dbs.query(SapnsAttribute).get(attr_id)
attr.related_class_id = tables_id[fk_table]
dbs.add(attr)
dbs.flush()
def update_metadata():
logger = logging.getLogger('lib.sapns.update_metadata')
managers = SapnsRole.by_name(ROLE_MANAGERS)
#managers = SapnsRole()
tables = extract_model(all=True)
tables_id = {}
pending_attr = {}
for tbl in tables:
logger.info('Table: %s' % tbl['name'])
klass = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
if not klass:
logger.warning('.....creating')
klass = SapnsClass()
klass.name = tbl['name']
klass.title = tbl['name'].title()
desc = unicode(l_('Class: %s'))
klass.description = desc % tbl['name']
dbs.add(klass)
dbs.flush()
# # grant access (r/w) to managers
managers.add_privilege(klass.class_id)
else:
logger.warning('.....already exists')
tables_id[tbl['name']] = klass.class_id
# create an action
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = '%s#%s' % (klass.name, name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
# create standard actions
create_action(unicode(l_('New')), SapnsPermission.TYPE_NEW)
create_action(unicode(l_('Edit')), SapnsPermission.TYPE_EDIT)
create_action(unicode(l_('Delete')), SapnsPermission.TYPE_DELETE)
create_action(unicode(l_('List')), SapnsPermission.TYPE_LIST)
create_action(unicode(l_('Docs')), SapnsPermission.TYPE_DOCS)
first_ref = False
for i, col in enumerate(tbl['columns']):
logger.info('Column: %s' % col['name'])
attr = dbs.query(SapnsAttribute).\
filter(and_(SapnsAttribute.name == col['name'],
SapnsAttribute.class_id == klass.class_id,
)).\
first()
if not attr and col['name'] != 'id':
logger.warning('.....creating')
attr = SapnsAttribute()
attr.name = col['name']
attr.title = col['name'].replace('_', ' ').title()
attr.class_id = klass.class_id
attr.type = col['type_name']
if attr.type == SapnsAttribute.TYPE_STRING and not first_ref:
attr.reference_order = 0
first_ref = True
attr.visible = True
attr.insertion_order = i
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
# col['prec']
# col['scale']
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec']-col['scale'],
col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
dbs.add(attr)
dbs.flush()
# grant access (r/w) to managers
rw_access = SapnsAttrPrivilege.ACCESS_READWRITE
managers.add_attr_privilege(attr.attribute_id, rw_access)
else:
logger.warning('.....already exists')
# fill the "field_regex"
if attr and not attr.field_regex:
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec'] - col['scale'], col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
# foreign key
if col['fk_table'] != None:
pending_attr[attr.attribute_id] = col['fk_table'].name
# update related classes
for attr_id, fk_table in pending_attr.iteritems():
attr = dbs.query(SapnsAttribute).get(attr_id)
attr.related_class_id = tables_id[fk_table]
dbs.add(attr)
dbs.flush()
def create_data_exploration(self):
managers = SapnsRole.by_name(ROLE_MANAGERS)
#managers = SapnsRole()
logger = logging.getLogger('lib.sapns.util.create_data_exploration')
tables = self.extract_model(all_=True)
for us in dbs.query(SapnsUser).\
join((SapnsUserRole,
and_(SapnsUserRole.user_id == SapnsUser.user_id,
SapnsUserRole.role_id == managers.group_id,
))):
self.create_dashboards(us)
data_ex = us.get_dataexploration()
sc_sapns = None
sc_project = None
if data_ex:
sc_sapns = data_ex.by_order(0)
sc_project = data_ex.by_order(1)
# data exploration/project
for i, tbl in enumerate(tables):
cls = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
# look for this table "list" action
act_table = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.type == SapnsPermission.TYPE_LIST,
SapnsPermission.class_id == cls.class_id)).\
first()
if not act_table:
act_table = SapnsPermission()
act_table.permission_name = u'%s#%s' % (
cls.name, SapnsPermission.TYPE_LIST)
act_table.display_name = u'List'
act_table.type = SapnsPermission.TYPE_LIST
act_table.class_id = cls.class_id
dbs.add(act_table)
dbs.flush()
# add to "managers" role
managers.permissions_.append(act_table)
dbs.flush()
# project
sc_parent = sc_project
if cls.name.startswith('sp_'):
# sapns
sc_parent = sc_sapns
if sc_parent:
sc_table = dbs.query(SapnsShortcut).\
filter(and_(SapnsShortcut.parent_id == sc_parent.shortcut_id,
SapnsShortcut.permission_id == act_table.permission_id,
SapnsShortcut.user_id == us.user_id,
)).\
first()
# does this user have this class shortcut?
if not sc_table:
sc_table = SapnsShortcut()
sc_table.title = tbl['name']
sc_table.parent_id = sc_parent.shortcut_id
sc_table.user_id = us.user_id
sc_table.permission_id = act_table.permission_id
sc_table.order = i
dbs.add(sc_table)
dbs.flush()
else:
logger.info(u'Shortcut for "%s" already exists' %
cls.title)
# sort (alphabetically) shortcuts inside "data exploration"
if sc_project:
logger.info('Sorting shortcuts inside "data exploration"')
i = 0
for sc in dbs.query(SapnsShortcut).\
filter(SapnsShortcut.parent_id == sc_project.shortcut_id).\
order_by(SapnsShortcut.title):
sc.order = i
dbs.add(sc)
dbs.flush()
i += 1
def save(self, **params):
logger = logging.getLogger('UsersController.save')
try:
#logger.info(params)
id_ = get_paramw(params, 'id', int, opcional=True)
display_name = get_paramw(params, 'display_name', unicode)
user_name = get_paramw(params, 'user_name', unicode)
email_address = get_paramw(params, 'email_address', unicode)
password = get_paramw(params, 'password', unicode, opcional=True)
copy_from = get_paramw(params, 'copy_from', int, opcional=True)
new_user = False
if id_:
user = dbs.query(SapnsUser).get(id_)
else:
new_user = True
user = SapnsUser()
user.display_name = display_name
# user_name
another = dbs.query(SapnsUser).\
filter(and_(SapnsUser.user_id != user.user_id,
SapnsUser.user_name == user_name,
)).\
first()
if another:
raise EUser(_('"User name" is already in use'))
# email_address
another = dbs.query(SapnsUser).\
filter(and_(SapnsUser.user_id != user.user_id,
SapnsUser.email_address == email_address,
)).\
first()
if another:
raise EUser(_('"E-mail address" is already in use'))
# user_name
user.user_name = user_name
# email_address
user.email_address = email_address
if password:
user.password = password
dbs.add(user)
dbs.flush()
# copy shortcuts and privileges form another user
if new_user:
user.copy_from(copy_from)
return dict(status=True)
except EUser, e:
logger.error(e)
return dict(status=False, message=unicode(e))
def update_metadata(self):
logger = logging.getLogger('lib.sapns.update_metadata')
env = Environment(loader=FileSystemLoader(current_path))
managers = dbs.query(SapnsRole).\
filter(SapnsRole.group_name == u'managers').\
first()
tables = self.extract_model(all_=True)
tables_id = {}
for tbl in tables:
logger.info('Table: %s' % tbl['name'])
klass = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
if not klass:
logger.warning('.....creating')
klass = SapnsClass()
klass.name = tbl['name']
klass.title = tbl['name'].title()
klass.description = u'Clases: %s' % tbl['name']
dbs.add(klass)
dbs.flush()
# grant access (r/w) to "managers"
priv = SapnsPrivilege()
priv.role_id = managers.group_id
priv.class_id = klass.class_id
priv.granted = True
dbs.add(priv)
dbs.flush()
else:
logger.warning('.....already exists')
tables_id[tbl['name']] = klass #.class_id
tmpl = env.get_template('trigger_function_log.txt')
pending_attr = {}
for tbl in tables:
#tables_id[tbl['name']] = klass.class_id
klass = tables_id[tbl['name']]
# create an action
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = u'%s#%s' % (klass.name, name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
# create standard actions
create_action(u'New', SapnsPermission.TYPE_NEW)
create_action(u'Edit', SapnsPermission.TYPE_EDIT)
create_action(u'Delete', SapnsPermission.TYPE_DELETE)
create_action(u'List', SapnsPermission.TYPE_LIST)
create_action(u'Docs', SapnsPermission.TYPE_DOCS)
log_attributes = Dict(created=False, updated=False)
log_cols = []
first_ref = False
for i, col in enumerate(tbl['columns']):
logger.info('Column: %s' % col['name'])
attr = dbs.query(SapnsAttribute).\
filter(and_(SapnsAttribute.name == col['name'],
SapnsAttribute.class_id == klass.class_id,
)).\
first()
# log attributes
if col['name'] in ['_created', '_updated']:
if col['name'] == '_created':
log_attributes.created = True
if col['name'] == '_updated':
log_attributes.updated = True
continue
elif col['name'] != 'id':
log_cols.append(col['name'])
if col['name'] not in ['id', '_created', '_updated']:
if not attr:
logger.warning('.....creating')
attr = SapnsAttribute()
attr.name = col['name']
attr.title = col['name'].replace('_', ' ').title()
attr.class_id = klass.class_id
attr.type = col['type_name']
if attr.type == SapnsAttribute.TYPE_STRING and not first_ref:
attr.reference_order = 0
first_ref = True
attr.visible = True
attr.insertion_order = i
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
# col['prec']
# col['scale']
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec']-col['scale'],
col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
dbs.add(attr)
dbs.flush()
# grant access (r/w) to managers
priv = SapnsAttrPrivilege()
priv.role_id = managers.group_id
priv.attribute_id = attr.attribute_id
priv.access = SapnsAttrPrivilege.ACCESS_READWRITE
dbs.add(priv)
dbs.flush()
else:
logger.warning('.....already exists')
# fill the "field_regex"
if attr and not attr.field_regex:
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec'] - col['scale'], col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
# foreign key
if col['fk_table'] != None:
pending_attr[attr.attribute_id] = col['fk_table'].name
if tbl['name'] not in [u'sp_logs',
u'sp_role_permission',
u'sp_user_role']:
_log_attributes = []
# _created
if not log_attributes.created:
_log_attributes.append('ADD _created TIMESTAMP')
# _updated
if not log_attributes.updated:
_log_attributes.append('ADD _updated TIMESTAMP')
if _log_attributes:
_alter = 'ALTER TABLE %s %s;' % (tbl['name'], ', '.join(_log_attributes))
logger.info(_alter)
try:
dbs.execute(_alter)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# log trigger function
try:
logf = tmpl.render(tbl_name=tbl['name'], cols=log_cols,
class_id=klass.class_id,
)
#logger.info(logf)
dbs.execute(logf)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# log triggers
log_trigger = 'SELECT COUNT(*) FROM pg_trigger WHERE tgname = \'zzzflog_%s\'' % tbl['name']
lt = dbs.execute(log_trigger).fetchone()
if lt[0] == 0:
_trigger = '''create trigger zzzflog_%s
after insert or update or delete
on %s
for each row
execute procedure flog_%s();''' % ((tbl['name'],)*3)
#logger.info(_trigger)
try:
dbs.execute(_trigger)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
#logger.info(_trigger)
try:
dbs.execute(_trigger)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# update related classes
for attr_id, fk_table in pending_attr.iteritems():
attr = dbs.query(SapnsAttribute).get(attr_id)
attr.related_class_id = tables_id[fk_table].class_id
dbs.add(attr)
dbs.flush()
def create_dashboards(self, us):
logger = logging.getLogger('lib.sapns.util.create_dashboards')
logger.info('Creating dashboard for "%s"' % us.display_name)
# user's dashboard
dboard = us.get_dashboard()
if not dboard:
dboard = SapnsShortcut()
dboard.user_id = us.user_id
dboard.parent_id = None
def save(self, **params):
logger = logging.getLogger('UsersController.save')
try:
#logger.info(params)
id_ = get_paramw(params, 'id', int, opcional=True)
display_name = get_paramw(params, 'display_name', unicode)
user_name = get_paramw(params, 'user_name', unicode)
email_address = get_paramw(params, 'email_address', unicode)
password = get_paramw(params, 'password', unicode, opcional=True)
copy_from = get_paramw(params, 'copy_from', int, opcional=True)
new_user = False
if id_:
user = dbs.query(SapnsUser).get(id_)
else:
new_user = True
user = SapnsUser()
user.display_name = display_name
# user_name
another = dbs.query(SapnsUser).\
filter(and_(SapnsUser.user_id != user.user_id,
SapnsUser.user_name == user_name,
)).\
first()
if another:
raise EUser(_('"User name" is already in use'))
# email_address
another = dbs.query(SapnsUser).\
filter(and_(SapnsUser.user_id != user.user_id,
SapnsUser.email_address == email_address,
)).\
first()
if another:
raise EUser(_('"E-mail address" is already in use'))
# user_name
user.user_name = user_name
# email_address
user.email_address = email_address
if password:
user.password = password
dbs.add(user)
dbs.flush()
# copy shortcuts and privileges form another user
if new_user:
user.copy_from(copy_from)
return dict(status=True)
except EUser, e:
logger.error(e)
return dict(status=False, message=unicode(e))
def update_metadata(self):
logger = logging.getLogger('lib.sapns.update_metadata')
env = Environment(loader=FileSystemLoader(current_path))
managers = dbs.query(SapnsRole).\
filter(SapnsRole.group_name == u'managers').\
first()
tables = self.extract_model(all_=True)
tables_id = {}
for tbl in tables:
logger.info('Table: %s' % tbl['name'])
klass = dbs.query(SapnsClass).\
filter(SapnsClass.name == tbl['name']).\
first()
if not klass:
logger.warning('.....creating')
klass = SapnsClass()
klass.name = tbl['name']
klass.title = tbl['name'].title()
klass.description = u'Clases: %s' % tbl['name']
dbs.add(klass)
dbs.flush()
# grant access (r/w) to "managers"
priv = SapnsPrivilege()
priv.role_id = managers.group_id
priv.class_id = klass.class_id
priv.granted = True
dbs.add(priv)
dbs.flush()
else:
logger.warning('.....already exists')
tables_id[tbl['name']] = klass #.class_id
tmpl = env.get_template('trigger_function_log.txt')
pending_attr = {}
for tbl in tables:
#tables_id[tbl['name']] = klass.class_id
klass = tables_id[tbl['name']]
# create an action
def create_action(name, type_):
action = dbs.query(SapnsPermission).\
filter(and_(SapnsPermission.class_id == klass.class_id,
SapnsPermission.type == type_)).\
first()
if not action:
action = SapnsPermission()
action.permission_name = u'%s#%s' % (klass.name,
name.lower())
action.display_name = name
action.type = type_
action.class_id = klass.class_id
dbs.add(action)
dbs.flush()
# add this action to "managers" role
managers.permissions_.append(action)
dbs.flush()
elif action.type == SapnsPermission.TYPE_LIST:
for s in action.shortcuts:
s.title = action.class_.title
dbs.add(s)
dbs.flush()
# create standard actions
create_action(u'New', SapnsPermission.TYPE_NEW)
create_action(u'Edit', SapnsPermission.TYPE_EDIT)
create_action(u'Delete', SapnsPermission.TYPE_DELETE)
create_action(u'List', SapnsPermission.TYPE_LIST)
create_action(u'Docs', SapnsPermission.TYPE_DOCS)
log_attributes = Dict(created=False, updated=False)
log_cols = []
first_ref = False
for i, col in enumerate(tbl['columns']):
logger.info('Column: %s' % col['name'])
attr = dbs.query(SapnsAttribute).\
filter(and_(SapnsAttribute.name == col['name'],
SapnsAttribute.class_id == klass.class_id,
)).\
first()
# log attributes
if col['name'] in ['_created', '_updated']:
if col['name'] == '_created':
log_attributes.created = True
if col['name'] == '_updated':
log_attributes.updated = True
continue
elif col['name'] != 'id':
log_cols.append(col['name'])
if col['name'] not in ['id', '_created', '_updated']:
if not attr:
logger.warning('.....creating')
attr = SapnsAttribute()
attr.name = col['name']
attr.title = col['name'].replace('_', ' ').title()
attr.class_id = klass.class_id
attr.type = col['type_name']
if attr.type == SapnsAttribute.TYPE_STRING and not first_ref:
attr.reference_order = 0
first_ref = True
attr.visible = True
attr.insertion_order = i
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
# col['prec']
# col['scale']
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec']-col['scale'],
col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
dbs.add(attr)
dbs.flush()
# grant access (r/w) to managers
priv = SapnsAttrPrivilege()
priv.role_id = managers.group_id
priv.attribute_id = attr.attribute_id
priv.access = SapnsAttrPrivilege.ACCESS_READWRITE
dbs.add(priv)
dbs.flush()
else:
logger.warning('.....already exists')
# fill the "field_regex"
if attr and not attr.field_regex:
if attr.type == SapnsAttribute.TYPE_INTEGER and \
not attr.name.startswith('id_'):
# signed
attr.field_regex = r'^\s*(\+|\-)?\d+\s*$'
elif attr.type == SapnsAttribute.TYPE_FLOAT:
# signed
attr.field_regex = r'^\s*(\+|\-)?\d{1,%d}(\.\d{1,%d})?\s*$' % \
(col['prec'] - col['scale'], col['scale'])
elif attr.type == SapnsAttribute.TYPE_TIME:
attr.field_regex = r'^\s*([01][0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?\s*$'
# foreign key
if col['fk_table'] != None:
pending_attr[attr.attribute_id] = col['fk_table'].name
if tbl['name'] not in [
u'sp_logs', u'sp_role_permission', u'sp_user_role'
]:
_log_attributes = []
# _created
if not log_attributes.created:
_log_attributes.append('ADD _created TIMESTAMP')
# _updated
if not log_attributes.updated:
_log_attributes.append('ADD _updated TIMESTAMP')
if _log_attributes:
_alter = 'ALTER TABLE %s %s;' % (
tbl['name'], ', '.join(_log_attributes))
logger.info(_alter)
try:
dbs.execute(_alter)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# log trigger function
try:
logf = tmpl.render(
tbl_name=tbl['name'],
cols=log_cols,
class_id=klass.class_id,
)
#logger.info(logf)
dbs.execute(logf)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
# log triggers
log_trigger = 'SELECT COUNT(*) FROM pg_trigger WHERE tgname = \'zzzflog_%s\'' % tbl[
'name']
lt = dbs.execute(log_trigger).fetchone()
if lt[0] == 0:
_trigger = '''create trigger zzzflog_%s
after insert or update or delete
on %s
for each row
execute procedure flog_%s();''' % (
(tbl['name'], ) * 3)
#logger.info(_trigger)
try:
dbs.execute(_trigger)
dbs.flush()
except Exception, e:
#dbs.rollback()
logger.error(e)
is_insert = False
if update.get('id'):
logger.info('Updating object [%d] of "%s"' %
(update['id'], cls.name))
dbs.execute(
tbl.update(whereclause=tbl.c.id == update['id'],
values=update))
else:
logger.info('Inserting new object in "%s"' % cls.name)
ins = tbl.insert(values=update).returning(tbl.c.id)
r = dbs.execute(ins)
is_insert = True
ch_cls.name = ch_cls.name
dbs.add(ch_cls)
dbs.flush()
if not update.get('id'):
update['id'] = r.fetchone().id
_exec_post_conditions('after', 'sapns', update)
_exec_post_conditions('after', config.get('app.root_folder'),
update)
# TODO: log
_desc = _('updating an existing record')
_what = _('update')
if is_insert:
_desc = _('creating a new record')
_what = _('create')
