def test1(self):
base = sqlahelper.get_base()
my_base = declarative.declarative_base()
sqlahelper.set_base(my_base)
base2 = sqlahelper.get_base()
try:
self.assertIsNot(base2, base)
self.assertIs(base2, my_base)
except AttributeError: # Python < 2.7
self.assertNotEqual(base2, base)
self.assertEqual(base2, my_base)
def test1(self):
base = sqlahelper.get_base()
my_base = declarative.declarative_base()
sqlahelper.set_base(my_base)
base2 = sqlahelper.get_base()
try:
self.assertIsNot(base2, base)
self.assertIs(base2, my_base)
except AttributeError: # Python < 2.7
self.assertNotEqual(base2, base)
self.assertEqual(base2, my_base)
def test1(self):
import transaction
Base = sqlahelper.get_base()
class Person(Base):
__tablename__ = "people"
id = sa.Column(sa.Integer, primary_key=True)
first_name = sa.Column(sa.Unicode(100), nullable=False)
last_name = sa.Column(sa.Unicode(100), nullable=False)
engine = sa.create_engine(self.db1.url)
sqlahelper.add_engine(engine)
Base.metadata.create_all()
fred = Person(id=1, first_name=u"Fred", last_name=u"Flintstone")
wilma = Person(id=2, first_name=u"Wilma", last_name=u"Flintstone")
barney = Person(id=3, first_name=u"Barney", last_name=u"Rubble")
betty = Person(id=4, first_name=u"Betty", last_name=u"Rubble")
Session = sqlahelper.get_session()
sess = Session()
sess.add_all([fred, wilma, barney, betty])
transaction.commit()
sess.expunge_all()
del fred, wilma, barney, betty
# Can we get back a record?
barney2 = sess.query(Person).get(3)
self.assertEqual(barney2.id, 3)
self.assertEqual(barney2.first_name, u"Barney")
self.assertEqual(barney2.last_name, u"Rubble")
sa.select([Person.first_name])
# Can we iterate the first names in reverse alphabetical order?
q = sess.query(Person.first_name).order_by(Person.first_name.desc())
result = [x.first_name for x in q]
control = [u"Wilma", u"Fred", u"Betty", u"Barney"]
self.assertEqual(result, control)
def test1(self):
import transaction
Base = sqlahelper.get_base()
class Person(Base):
__tablename__ = "people"
id = sa.Column(sa.Integer, primary_key=True)
first_name = sa.Column(sa.Unicode(100), nullable=False)
last_name = sa.Column(sa.Unicode(100), nullable=False)
engine = sa.create_engine(self.db1.url)
sqlahelper.add_engine(engine)
Base.metadata.create_all()
fred = Person(id=1, first_name=u"Fred", last_name=u"Flintstone")
wilma = Person(id=2, first_name=u"Wilma", last_name=u"Flintstone")
barney = Person(id=3, first_name=u"Barney", last_name=u"Rubble")
betty = Person(id=4, first_name=u"Betty", last_name=u"Rubble")
Session = sqlahelper.get_session()
sess = Session()
sess.add_all([fred, wilma, barney, betty])
transaction.commit()
sess.expunge_all()
del fred, wilma, barney, betty
# Can we get back a record?
barney2 = sess.query(Person).get(3)
self.assertEqual(barney2.id, 3)
self.assertEqual(barney2.first_name, u"Barney")
self.assertEqual(barney2.last_name, u"Rubble")
sa.select([Person.first_name])
# Can we iterate the first names in reverse alphabetical order?
q = sess.query(Person.first_name).order_by(Person.first_name.desc())
result = [x.first_name for x in q]
control = [u"Wilma", u"Fred", u"Betty", u"Barney"]
self.assertEqual(result, control)
def _init_ptah(self, handler=None, *args, **kw):
config.initialize(self.p_config, ("ptah", self.__class__.__module__), initsettings=False)
config.initialize_settings(self._settings, self.p_config)
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.drop_all()
Base.metadata.create_all()
transaction.commit()
def _init_ptah(self, settings=None, handler=None, *args, **kw):
if settings is None:
settings = self._settings
config.initialize(('ptah', self.__class__.__module__))
config.initialize_settings(settings, self.p_config)
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.drop_all()
Base.metadata.create_all()
transaction.commit()
def test_multiple_engines(self):
default = sa.create_engine(self.db1.url)
stats = sa.create_engine(self.db2.url)
sqlahelper.add_engine(default)
sqlahelper.add_engine(stats, "stats")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine(), default)
self.assertIs(sqlahelper.get_engine("default"), default)
self.assertIs(sqlahelper.get_engine("stats"), stats)
# Are the session binding and base binding set correctly?
self.assertIs(sqlahelper.get_session().bind, default)
self.assertIs(sqlahelper.get_base().metadata.bind, default)
def test_multiple_engines_without_default(self):
db1 = sa.create_engine(self.db1.url)
db2 = sa.create_engine(self.db2.url)
sqlahelper.add_engine(db1, "db1")
sqlahelper.add_engine(db2, "db2")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine("db1"), db1)
self.assertIs(sqlahelper.get_engine("db2"), db2)
# There should be no default engine
self.assertIsNone(sqlahelper.get_session().bind)
self.assertIsNone(sqlahelper.get_base().metadata.bind)
self.assertIsNone(sqlahelper.get_engine())
def test_multiple_engines(self):
default = sa.create_engine(self.db1.url)
stats = sa.create_engine(self.db2.url)
sqlahelper.add_engine(default)
sqlahelper.add_engine(stats, "stats")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine(), default)
self.assertIs(sqlahelper.get_engine("default"), default)
self.assertIs(sqlahelper.get_engine("stats"), stats)
# Are the session binding and base binding set correctly?
self.assertIs(sqlahelper.get_session().bind, default)
self.assertIs(sqlahelper.get_base().metadata.bind, default)
def test_multiple_engines_without_default(self):
db1 = sa.create_engine(self.db1.url)
db2 = sa.create_engine(self.db2.url)
sqlahelper.add_engine(db1, "db1")
sqlahelper.add_engine(db2, "db2")
# Can we retrieve the engines?
self.assertIs(sqlahelper.get_engine("db1"), db1)
self.assertIs(sqlahelper.get_engine("db2"), db2)
# There should be no default engine
self.assertIsNone(sqlahelper.get_session().bind)
self.assertIsNone(sqlahelper.get_base().metadata.bind)
self.assertIsNone(sqlahelper.get_engine())
def _init_ptah(self, settings=None, handler=None, *args, **kw):
st = dict(self._settings)
if settings is not None:
st.update(settings)
config.initialize(('ptah', self.__class__.__module__),
reg = self.p_config.registry)
config.initialize_settings(st, self.p_config)
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.drop_all()
Base.metadata.create_all()
transaction.commit()
def ptah_initialize(configurator, packages=None, autoinclude=False):
""" Initialize ptah packages.
Load all ptah packages and intialize ptah settings system.
This function automatically called by :py:func:`make_wsgi_app` function.
"""
import ptah
import sqlahelper
import transaction
from pyramid.exceptions import ConfigurationExecutionError
configurator.include('pyramid_tm')
configurator.begin()
try:
settings = configurator.registry.settings
# exclude
excludes = []
if 'ptah.excludes' in settings:
excludes.extend(s.strip()
for s in settings['ptah.excludes'].split())
# load packages
ptah.config.initialize(
configurator, packages, excludes, autoinclude, initsettings=True)
configurator.commit()
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.create_all()
# send AppStarting event
ptah.config.start(configurator)
# commit possible transaction
transaction.commit()
except Exception, e:
if isinstance(e, ConfigurationExecutionError):
e = e.evalue
if not isinstance(e, ptah.config.StopException):
ptah.config.shutdown()
e = ptah.config.StopException(e)
raise e
ptah.config.shutdown()
raise e
def init_ptah(self, *args, **kw):
self.registry.settings.update(self._settings)
self.config.scan('ptah')
self.config.commit()
self.config.autocommit = True
ptah.init_settings(self.config, self.registry.settings)
if self._init_sqla:
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.drop_all()
Base.metadata.create_all()
transaction.commit()
def init_ptah(self, *args, **kw):
self.registry.settings.update(self._settings)
self.config.scan('ptah')
self.config.commit()
self.config.autocommit = True
ptah.init_settings(self.config, self.registry.settings)
if self._init_sqla:
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.drop_all()
Base.metadata.create_all()
transaction.commit()
def load_database(database=settings.DATABASE):
engine = sqlalchemy.engine_from_config(database, prefix='')
sqlahelper.add_engine(engine)
tlogger.info("database set up: %s" % engine)
for package in settings.INSTALLED_APPS:
try:
importlib.import_module(package + ".models")
except ImportError:
result = "failed"
else:
result = "done"
finally:
tlogger.debug("loading models: %s.models.. %s" % (package, result))
Base = sqlahelper.get_base()
tlogger.info('creating database tables')
Base.metadata.create_all(engine)
def ptah_init(configurator):
""" Initialize ptah packages.
Load all ptah packages and intialize ptah settings system.
This function automatically called by :py:func:`make_wsgi_app` function.
"""
import ptah
import transaction
import sqlahelper
configurator.include('pyramid_tm')
try:
settings = configurator.registry.settings
# exclude
excludes = []
if 'ptah.excludes' in settings:
excludes.extend(s.strip()
for s in settings['ptah.excludes'].split())
# load packages
ptah.config.initialize(None, excludes, configurator.registry)
# load settings
ptah.config.initialize_settings(settings, configurator)
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.create_all()
# send AppStarting event
ptah.config.start(configurator)
except Exception, e:
if not isinstance(e, ptah.config.StopException):
ptah.config.shutdown()
raise ptah.config.StopException(e)
ptah.config.shutdown()
raise
def main():
if len(sys.argv) != 2:
sys.exit("Usage: python -m drkpr.scripts.create_db INI_FILE")
ini_file = sys.argv[1]
logging.config.fileConfig(ini_file)
log = logging.getLogger(__name__)
app = get_app(ini_file, "myapp")
settings = app.registry.settings
engine = sqlalchemy.engine_from_config(settings,
prefix="sqlalchemy.",
pool_recycle=3600,
convert_unicode=True)
sqlahelper.add_engine(engine)
Base = sqlahelper.get_base()
Session = sqlahelper.get_session()
log.info("Create testing user accounts ...")
random.seed("whatever")
for i in range(0, 100):
sur_name = sur_names[random.randint(0, len(sur_names) - 1)]
given_name = given_names[random.randint(0, len(given_names) - 1)]
username = "******" % (sur_name, given_name)
try:
q = Session.query(model.DKUser)
r = q.get(username)
if not r:
user = model.DKUser()
user.username = username
user.roles = ":".join(["", model.ROLE_USER, ""])
user.passwd = hashlib.md5("cpksecurity").hexdigest()
user.master_email = "*****@*****.**" % username
user.actived = True
Session.add(user)
log.info(" Account '%s' setup complete." % username)
else:
log.info("Account '%s' already setup." % username)
except Exception, e:
print e
def main():
if len(sys.argv) != 2:
sys.exit("Usage: python -m drkpr.scripts.create_db INI_FILE")
ini_file = sys.argv[1]
logging.config.fileConfig(ini_file)
log = logging.getLogger(__name__)
app = get_app(ini_file, "myapp")
settings = app.registry.settings
engine = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy.",
pool_recycle=3600, convert_unicode=True)
sqlahelper.add_engine(engine)
Base = sqlahelper.get_base()
Session = sqlahelper.get_session()
log.info("Create testing user accounts ...")
random.seed("whatever")
for i in range(0,100):
sur_name = sur_names[random.randint(0, len(sur_names)-1)]
given_name = given_names[random.randint(0, len(given_names)-1)]
username = "******" % (sur_name, given_name)
try:
q = Session.query(model.DKUser)
r = q.get(username)
if not r:
user = model.DKUser()
user.username = username
user.roles = ":".join(["", model.ROLE_USER, ""])
user.passwd = hashlib.md5("cpksecurity").hexdigest()
user.master_email = "*****@*****.**" % username
user.actived = True
Session.add(user)
log.info(" Account '%s' setup complete." % username)
else:
log.info("Account '%s' already setup." % username)
except Exception, e:
print e
# -*- coding: utf-8 -*-
import sqlahelper
from sqlalchemy import (Column, Integer)
from geoalchemy2 import Geometry
Base = sqlahelper.get_base()
DBSession = sqlahelper.get_session()
class LidarTileIndex(Base):
__tablename__ = 'grid50mfull'
__table_args__ = {'schema': 'lidar_tile_index', 'autoload': True}
id = Column('oid', Integer, primary_key=True)
geom = Column(Geometry("POLYGON", srid=21781))
def get_bydata(self, typ, data):
""" Get token for data """
t = self._sql_get_by_data.first(data=data, typ=typ.id)
if t is not None:
return t.token
def remove(self, token):
""" Remove token """
Session.query(Token).filter(
sqla.sql.or_(Token.token == token,
Token.valid > datetime.datetime.now())).delete()
service = TokenService()
Base = sqlh.get_base()
Session = sqlh.get_session()
class Token(Base):
__tablename__ = 'ptah_tokens'
id = sqla.Column(sqla.Integer, primary_key=True)
token = sqla.Column(sqla.Unicode(48))
valid = sqla.Column(sqla.DateTime)
data = sqla.Column(sqla.Unicode)
typ = sqla.Column(sqla.Unicode(48))
def __init__(self, typ, data):
super(Token, self).__init__()
def get_bydata(self, typ, data):
""" Get token for data """
t = self._sql_get_by_data.first(data=data, typ=typ.id)
if t is not None:
return t.token
def remove(self, token):
""" Remove token """
Session.query(Token).filter(
sqla.sql.or_(Token.token == token,
Token.valid > datetime.datetime.now())).delete()
service = TokenService()
Base = sqlh.get_base()
Session = sqlh.get_session()
class Token(Base):
__tablename__ = 'ptah_tokens'
id = sqla.Column(sqla.Integer, primary_key=True)
token = sqla.Column(sqla.Unicode(48))
valid = sqla.Column(sqla.DateTime)
data = sqla.Column(sqla.Unicode)
typ = sqla.Column(sqla.Unicode(48))
def __init__(self, typ, data):
super(Token, self).__init__()
def main():
if len(sys.argv) != 2:
sys.exit("Usage: python -m drkpr.scripts.create_db INI_FILE")
ini_file = sys.argv[1]
logging.config.fileConfig(ini_file)
log = logging.getLogger(__name__)
app = get_app(ini_file, "myapp")
settings = app.registry.settings
engine = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy.",
pool_recycle=3600, convert_unicode=True)
sqlahelper.add_engine(engine)
Base = sqlahelper.get_base()
Session = sqlahelper.get_session()
# Create the tables if they don't already exist
log.info("Initialize database ...")
Base.metadata.create_all(bind=Session.bind, checkfirst=True)
#create default privileges
log.info("Populate default privileges ...")
log.info("nothing here...")
log.info("Populate default privileges done.")
#create default roles
log.info("Populate default roles ...")
q = Session.query(model.DKRole)
if not q.all():
records = [ model.DKRole(model.ROLE_SYSADMIN),
model.DKRole(model.ROLE_USER),
]
Session.add_all(records)
log.info("Populate default roles done.")
else:
log.info("Roles already exist.")
log.info("Populate default roles done.")
#create default system parameters
log.info("Populate default system parameters ...")
q = Session.query(model.DKSystem)
if not q.all():
records = [ model.DKSystem("master_key_status", model.SS_SERVICE_NO_KEY),
model.DKSystem("service_key_gen_status", model.SS_SERVICE_NOT_AVAIL),
model.DKSystem("service_key_revoke_status", model.SS_SERVICE_NOT_AVAIL),
]
Session.add_all(records)
log.info("Populate default system parameters done.")
else:
log.info("System parameters exists.")
#create default admin account
log.info("Create default admin account ...")
q = Session.query(model.DKUser)
r = q.get("sysadmin")
if not r:
user = model.DKUser()
user.username = "******"
user.roles = ":".join(["", model.ROLE_SYSADMIN, ""])
user.passwd = hashlib.md5("sysadmin").hexdigest()
user.master_email = "*****@*****.**"
user.actived = True
Session.add(user)
log.info("Admin account setup complete.")
else:
log.info("Admin account already setup.")
transaction.commit()
def main(global_config, **settings):
""" This is your application startup.
"""
config = Configurator(root_factory=APP_FACTORY, settings=settings,
session_factory = session_factory,
authentication_policy = auth_policy)
config.include('ptah')
config.include('pyramid_tm')
import sqlahelper, sqlalchemy
engine = sqlalchemy.engine_from_config(settings, 'sqlalchemy.')
sqlahelper.add_engine(engine)
# you could use ptah.ptah_init() and be home by now
# --- start ptah.ptah_init() ---
config.ptah_initialize(('nerdfilestest', 'ptah'))
config.commit()
# --- end ptah.ptah_init() ---
# create sql tables
Base = sqlahelper.get_base()
Base.metadata.create_all()
# your application configuration
root = APP_FACTORY()
# admin user
user = Session.query(User).first()
if user is None:
user = User('Admin', 'admin', '*****@*****.**', '12345')
Session.add(user)
# give manager role to admin
if user.uri not in root.__local_roles__:
root.__local_roles__[user.uri] = [Manager.id]
# set authcontext as admin user
ptah.authService.set_userid(user.uri)
# create default page
if 'front-page' not in root.keys():
page = Page(title=u'Welcome to Ptah')
page.text = open(
abspath_from_asset_spec('nerdfilestest:welcome.pt'), 'rb').read()
root['front-page'] = page
# We are not in a web request; we need to manually commit.
import transaction
transaction.commit()
# enable rest api
ptah.enable_rest_api(config)
# enable ptah-manage, see ptah.managers in .ini for users whom can access
config.add_route(
'ptah-manage','/ptah-manage/*traverse',
factory=ptah.manage.PtahManageRoute, use_global_views=True)
config.begin()
ptah.manage.CONFIG['managers'] = ['admin']
config.end()
config.scan()
return config.make_wsgi_app()
""" sqla module """
import urllib
import sqlahelper as psa
from sqlalchemy.orm.mapper import _mapper_registry
from pyramid.view import view_config
from pyramid.compat import url_quote_plus
from pyramid.decorator import reify
from pyramid.httpexceptions import HTTPFound
import ptah
from ptah import form
Session = psa.get_session()
metadata = psa.get_base().metadata
@ptah.manage.module('sqla')
class SQLAModule(ptah.manage.PtahModule):
__doc__ = 'A listing of all tables with ability to view and edit records'
title = 'SQLAlchemy'
metadata = {}
def __getitem__(self, key):
try:
id, table = key.split('-', 1)
except:
raise KeyError(key)
md = self.metadata[id][0]
def create_base(basedotted):
"""dotted = foo.bar:Base"""
if basedotted:
return import_symbol(basedotted)
else:
return sqlahelper.get_base()
class SettingRecord(sqlh.get_base()):
__tablename__ = 'ptah_settings'
name = sqla.Column(sqla.String, primary_key=True)
value = sqla.Column(sqla.String)
# -*- coding: utf-8 -*-
"""
oflogviewer.models.meta
~~~~~~~~~~~~~~~~~~~~~~~
SQLAlchemyの初期化
:copyright: Copyright (c) 2015, National Institute of Information and Communications Technology.All rights reserved.
:license: GPL3, see LICENSE for more details.
"""
from sqlalchemy import create_engine
from sqlahelper import get_base, get_session, add_engine
__all__ = ['Base', 'Session', 'init_engine']
Base = get_base()
Session = get_session()
def init_engine(config):
engine = create_engine(
config.get('SQLALCHEMY_DATABASE_URI'),
echo=config.get('SQLALCHEMY_ECHO'),
pool_recycle=config.get('SQLALCHEMY_POOL_RECYCLE'),
convert_unicode=True
)
add_engine(engine)
""" introspect module """
import urllib
import sqlahelper as psa
from sqlalchemy.orm.mapper import _mapper_registry
from pyramid.decorator import reify
from pyramid.httpexceptions import HTTPFound
import ptah
from ptah import config, view, form, manage
Session = psa.get_session()
metadata = psa.get_base().metadata
class SQLAModule(manage.PtahModule):
__doc__ = u'A listing of all tables with ability to view and edit records'
title = 'SQLAlchemy'
manage.module('sqla')
metadata = {}
def __getitem__(self, key):
try:
id, table = key.split('-', 1)
except:
raise KeyError(key)
md = self.metadata[id][0]
return Table(md.tables[table], self, self.request)
return None
ticket_no = self.ticket_no
return project.get_ticket(ticket_no)
@reify
def member(self):
project = self.project
if project is None:
return None
authenticated_user = self.request.authenticated_user
if authenticated_user is None:
return None
return Member.query.filter(Member.user==authenticated_user).filter(Member.project==project).one()
Base = sqlahelper.get_base()
DBSession = sqlahelper.get_session()
class User(Base):
__tablename__ = 'users'
query = DBSession.query_property()
id = sa.Column(sa.Integer, primary_key=True)
user_name = sa.Column(sa.Unicode(255), unique=True)
_password = sa.Column("password", sa.String(255))
def set_password(self, password):
self._password = hashlib.sha1(password).hexdigest()
password = property(fset=set_password)
def validate_password(self, password):
import ptah
import sqlalchemy as sqla
import sqlahelper as psa
Base = psa.get_base()
Session = psa.get_session()
class CrowdProvider(object):
#ptah.auth_provider('user+crowd')
def authenticate(self, creds):
login, password = creds['login'], creds['password']
user = CrowdUser.get_bylogin(login)
if user is not None:
if ptah.passwordTool.check(user.password, password):
return user
def get_principal_bylogin(self, login):
return CrowdUser.get_bylogin(login)
class CrowdUser(Base):
__tablename__ = 'ptah_crowd'
pid = sqla.Column(sqla.Integer, primary_key=True)
uri = sqla.Column(sqla.Unicode(45), unique=True)
name = sqla.Column(sqla.Unicode(255))
def main():
if len(sys.argv) != 2:
sys.exit("Usage: python -m drkpr.scripts.create_db INI_FILE")
ini_file = sys.argv[1]
logging.config.fileConfig(ini_file)
log = logging.getLogger(__name__)
app = get_app(ini_file, "myapp")
settings = app.registry.settings
engine = sqlalchemy.engine_from_config(settings,
prefix="sqlalchemy.",
pool_recycle=3600,
convert_unicode=True)
sqlahelper.add_engine(engine)
Base = sqlahelper.get_base()
Session = sqlahelper.get_session()
# Create the tables if they don't already exist
log.info("Initialize database ...")
Base.metadata.create_all(bind=Session.bind, checkfirst=True)
#create default privileges
log.info("Populate default privileges ...")
log.info("nothing here...")
log.info("Populate default privileges done.")
#create default roles
log.info("Populate default roles ...")
q = Session.query(model.DKRole)
if not q.all():
records = [
model.DKRole(model.ROLE_SYSADMIN),
model.DKRole(model.ROLE_USER),
]
Session.add_all(records)
log.info("Populate default roles done.")
else:
log.info("Roles already exist.")
log.info("Populate default roles done.")
#create default system parameters
log.info("Populate default system parameters ...")
q = Session.query(model.DKSystem)
if not q.all():
records = [
model.DKSystem("master_key_status", model.SS_SERVICE_NO_KEY),
model.DKSystem("service_key_gen_status",
model.SS_SERVICE_NOT_AVAIL),
model.DKSystem("service_key_revoke_status",
model.SS_SERVICE_NOT_AVAIL),
]
Session.add_all(records)
log.info("Populate default system parameters done.")
else:
log.info("System parameters exists.")
#create default admin account
log.info("Create default admin account ...")
q = Session.query(model.DKUser)
r = q.get("sysadmin")
if not r:
user = model.DKUser()
user.username = "******"
user.roles = ":".join(["", model.ROLE_SYSADMIN, ""])
user.passwd = hashlib.md5("sysadmin").hexdigest()
user.master_email = "*****@*****.**"
user.actived = True
Session.add(user)
log.info("Admin account setup complete.")
else:
log.info("Admin account already setup.")
transaction.commit()
