def _check_read_permissions(self):
'''
Check whether the right permissions are set for the read only user.
A table is created by the write user to test the read only user.
'''
write_connection = db._get_engine(None,
{'connection_url': self.write_url}).connect()
write_connection.execute(u"DROP TABLE IF EXISTS public._foo;"
u"CREATE TABLE public._foo (id INTEGER, name VARCHAR)")
read_connection = db._get_engine(None,
{'connection_url': self.read_url}).connect()
statements = [
u"CREATE TABLE public._bar (id INTEGER, name VARCHAR)",
u"INSERT INTO public._foo VALUES (1, 'okfn')"
]
try:
for sql in statements:
read_trans = read_connection.begin()
try:
read_connection.execute(sql)
except ProgrammingError, e:
if 'permission denied' not in str(e):
raise
else:
log.info("Connection url {0}".format(self.read_url))
if 'debug' in self.config and self.config['debug']:
log.critical("We have write permissions on the read-only database.")
else:
raise Exception("We have write permissions on the read-only database.")
finally:
read_trans.rollback()
def _read_connection_has_correct_privileges(self):
''' Returns True if the right permissions are set for the read
only user. A table is created by the write user to test the
read only user.
'''
write_connection = db._get_engine(
{'connection_url': self.write_url}).connect()
read_connection = db._get_engine(
{'connection_url': self.read_url}).connect()
drop_foo_sql = u'DROP TABLE IF EXISTS _foo'
write_connection.execute(drop_foo_sql)
try:
try:
write_connection.execute(u'CREATE TABLE _foo ()')
for privilege in ['INSERT', 'UPDATE', 'DELETE']:
test_privilege_sql = u"SELECT has_table_privilege('_foo', '{privilege}')"
sql = test_privilege_sql.format(privilege=privilege)
have_privilege = read_connection.execute(sql).first()[0]
if have_privilege:
return False
finally:
write_connection.execute(drop_foo_sql)
finally:
write_connection.close()
read_connection.close()
return True
def _check_read_permissions(self):
'''
Check whether the right permissions are set for the read only user.
A table is created by the write user to test the read only user.
'''
write_connection = db._get_engine(None,
{'connection_url': self.write_url}).connect()
write_connection.execute(u"DROP TABLE IF EXISTS public._foo;"
u"CREATE TABLE public._foo (id INTEGER, name VARCHAR)")
read_connection = db._get_engine(None,
{'connection_url': self.read_url}).connect()
statements = [
u"CREATE TABLE public._bar (id INTEGER, name VARCHAR)",
u"INSERT INTO public._foo VALUES (1, 'okfn')"
]
try:
for sql in statements:
read_trans = read_connection.begin()
try:
read_connection.execute(sql)
except ProgrammingError, e:
if 'permission denied' not in str(e):
raise
else:
log.info("Connection url {0}".format(self.read_url))
if 'debug' in self.config and self.config['debug']:
log.critical("We have write permissions on the read-only database.")
else:
raise Exception("We have write permissions on the read-only database.")
finally:
read_trans.rollback()
def _read_connection_has_correct_privileges(self):
''' Returns True if the right permissions are set for the read
only user. A table is created by the write user to test the
read only user.
'''
write_connection = db._get_engine({
'connection_url': self.write_url
}).connect()
read_connection = db._get_engine({
'connection_url': self.read_url
}).connect()
drop_foo_sql = u'DROP TABLE IF EXISTS _foo'
write_connection.execute(drop_foo_sql)
try:
try:
write_connection.execute(u'CREATE TABLE _foo ()')
for privilege in ['INSERT', 'UPDATE', 'DELETE']:
test_privilege_sql = u"SELECT has_table_privilege('_foo', '{privilege}')"
sql = test_privilege_sql.format(privilege=privilege)
have_privilege = read_connection.execute(sql).first()[0]
if have_privilege:
return False
finally:
write_connection.execute(drop_foo_sql)
finally:
write_connection.close()
read_connection.close()
return True
def _create_alias_table(self):
mapping_sql = '''
SELECT DISTINCT
substr(md5(dependee.relname || COALESCE(dependent.relname, '')), 0, 17) AS "_id",
dependee.relname AS name,
dependee.oid AS oid,
dependent.relname AS alias_of
-- dependent.oid AS oid
FROM
pg_class AS dependee
LEFT OUTER JOIN pg_rewrite AS r ON r.ev_class = dependee.oid
LEFT OUTER JOIN pg_depend AS d ON d.objid = r.oid
LEFT OUTER JOIN pg_class AS dependent ON d.refobjid = dependent.oid
WHERE
(dependee.oid != dependent.oid OR dependent.oid IS NULL) AND
(dependee.relname IN (SELECT tablename FROM pg_catalog.pg_tables)
OR dependee.relname IN (SELECT viewname FROM pg_catalog.pg_views)) AND
dependee.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
ORDER BY dependee.oid DESC;
'''
create_alias_table_sql = u'CREATE OR REPLACE VIEW "_table_metadata" AS {0}'.format(mapping_sql)
try:
connection = db._get_engine(
{'connection_url': self.write_url}).connect()
connection.execute(create_alias_table_sql)
finally:
connection.close()
def datastore_delete(context, data_dict):
'''Deletes a table or a set of records from the datastore.
:param resource_id: resource id that the data will be deleted from.
:type resource_id: string
:param filters: filters to apply before deleting (eg {"name": "fred"}).
If missing delete whole table and all dependent views.
:type filters: dictionary
:returns: original filters sent.
:rtype: dictionary
'''
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL'''
)
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(
p.toolkit._('Resource "{0}" was not found.'.format(res_id)))
p.toolkit.check_access('datastore_delete', context, data_dict)
result = db.delete(context, data_dict)
result.pop('id')
result.pop('connection_url')
return result
def _check_read_permissions(self):
'''
Check whether the right permissions are set for the read only user.
The table 'writetest' was created by
create_datastore_db_and_read_only_user.sql for this purpose.
'''
read_connection = db._get_engine(None, {
'connection_url': self.read_url
}).connect()
read_trans = read_connection.begin()
statements = [
u"CREATE TABLE public.bar (id INTEGER NOT NULL, name VARCHAR)",
u"INSERT INTO public.writetest VALUES (1, 'okfn')"
]
try:
for sql in statements:
read_trans = read_connection.begin()
try:
read_connection.execute(sql)
except ProgrammingError, e:
if 'permission denied' not in str(e):
raise
except InternalError, e:
# This occurs when a slave in a Postgres master-slave config
if 'read-only transaction' not in str(e):
raise
def _read_connection_has_correct_privileges(self):
''' Returns True if the right permissions are set for the read
only user. A table is created by the write user to test the
read only user.
'''
write_connection = db._get_engine({
'connection_url': self.write_url
}).connect()
read_connection_user = sa_url.make_url(self.read_url).username
drop_foo_sql = u'DROP TABLE IF EXISTS _foo'
write_connection.execute(drop_foo_sql)
try:
write_connection.execute(u'CREATE TEMP TABLE _foo ()')
for privilege in ['INSERT', 'UPDATE', 'DELETE']:
test_privilege_sql = u"SELECT has_table_privilege(%s, '_foo', %s)"
have_privilege = write_connection.execute(
test_privilege_sql,
(read_connection_user, privilege)).first()[0]
if have_privilege:
return False
finally:
write_connection.execute(drop_foo_sql)
write_connection.close()
return True
def _create_alias_table(self):
connection = db._get_engine(
None, {
'connection_url': pylons.config['ckan.datastore_write_url']
}).connect()
with connection.begin() as transaction:
table_metadata_exists_sql = '''
SELECT 1 FROM pg_views where viewname = '_table_metadata'
'''
table_metadata_exists = connection.execute(
table_metadata_exists_sql).fetchone()
if not table_metadata_exists:
mapping_sql = '''
SELECT distinct
dependee.relname AS name,
-- r.ev_class::regclass AS oid,
dependent.relname AS alias_of
-- d.refobjid::regclass AS oid,
FROM
pg_attribute as a
JOIN pg_depend as d on d.refobjid = a.attrelid AND d.refobjsubid = a.attnum
JOIN pg_rewrite as r ON d.objid = r.oid
JOIN pg_class as dependee ON r.ev_class = dependee.oid
JOIN pg_class as dependent ON d.refobjid = dependent.oid
WHERE dependee.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
'''
create_alias_table_sql = u'create view "_table_metadata" as {0}'.format(
mapping_sql)
connection.execute(create_alias_table_sql)
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
set_url_type(
model.Package.get('annakarenina').resources, cls.sysadmin_user)
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id': resource.id,
'fields': [{'id': u'b\xfck', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'nested', 'type': 'json'},
{'id': 'characters', 'type': 'text[]'},
{'id': 'published'}],
'primary_key': u'b\xfck',
'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}]},
{u'b\xfck': 'warandpeace', 'author': 'tolstoy',
'nested': {'a':'b'}}
]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create', params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def command(self):
if not self.args or self.args[0] in ['--help', '-h', 'help']:
print self.__doc__
return
self._load_config()
# Set up context
user = toolkit.get_action('get_site_user')({'ignore_auth': True}, {})
self.context = {'user': '******'}
# Set up datastore DB engine
self.engine = _get_engine({
'connection_url': pylons.config['ckan.datastore.write_url']
})
cmd = self.args[0]
if cmd == 'replace':
self.replace()
elif cmd == 'purge-all':
self.purge_all()
else:
print 'Command %s not recognized' % cmd
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id': resource.id,
'aliases': u'b\xfck2',
'fields': [{'id': 'book', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'rating with %', 'type': 'text'}],
'records': [{'book': 'annakarenina', 'author': 'tolstoy',
'rating with %': '90%'},
{'book': 'warandpeace', 'author': 'tolstoy',
'rating with %': '42%'}]
}
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
set_url_type(
model.Package.get('annakarenina').resources, cls.sysadmin_user)
def datastore_delete(context, data_dict):
'''Deletes a table or a set of records from the datastore.
:param resource_id: resource id that the data will be deleted from.
:type resource_id: string
:param filters: filters to apply before deleting (eg {"name": "fred"}).
If missing delete whole table and all dependent views.
:type filters: dictionary
:returns: original filters sent.
:rtype: dictionary
'''
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL''')
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(p.toolkit._(
'Resource "{0}" was not found.'.format(res_id)
))
p.toolkit.check_access('datastore_delete', context, data_dict)
result = db.delete(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id': resource.id,
'aliases': u'b\xfck2',
'fields': [{'id': 'book', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'rating with %', 'type': 'text'}],
'records': [{'book': 'annakarenina', 'author': 'tolstoy',
'rating with %': '90%'},
{'book': 'warandpeace', 'author': 'tolstoy',
'rating with %': '42%'}]
}
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
set_url_type(
model.Package.get('annakarenina').resources, cls.sysadmin_user)
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
plugin = p.load("datastore")
if plugin.legacy_mode:
# make sure we undo adding the plugin
p.unload("datastore")
raise nose.SkipTest("SQL tests are not supported in legacy mode")
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get("testsysadmin")
cls.normal_user = model.User.get("annafan")
cls.dataset = model.Package.get("annakarenina")
resource = cls.dataset.resources[0]
cls.data = {
"resource_id": resource.id,
"aliases": "books4",
"fields": [{"id": u"b\xfck", "type": "text"}, {"id": "author", "type": "text"}, {"id": "published"}],
"records": [
{
u"b\xfck": "annakarenina",
"author": "tolstoy",
"published": "2005-03-01",
"nested": ["b", {"moo": "moo"}],
},
{u"b\xfck": "warandpeace", "author": "tolstoy", "nested": {"a": "b"}},
],
}
postparams = "%s=1" % json.dumps(cls.data)
auth = {"Authorization": str(cls.sysadmin_user.apikey)}
res = cls.app.post("/api/action/datastore_create", params=postparams, extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict["success"] is True
# Make an organization, because private datasets must belong to one.
cls.organization = tests.call_action_api(
cls.app, "organization_create", name="test_org", apikey=cls.sysadmin_user.apikey
)
cls.expected_records = [
{
u"_full_text": u"'annakarenina':1 'b':3 'moo':4 'tolstoy':2",
u"_id": 1,
u"author": u"tolstoy",
u"b\xfck": u"annakarenina",
u"nested": [u"b", {u"moo": u"moo"}],
u"published": u"2005-03-01T00:00:00",
},
{
u"_full_text": u"'b':3 'tolstoy':2 'warandpeac':1",
u"_id": 2,
u"author": u"tolstoy",
u"b\xfck": u"warandpeace",
u"nested": {u"a": u"b"},
u"published": None,
},
]
cls.expected_join_results = [{u"first": 1, u"second": 1}, {u"first": 1, u"second": 2}]
engine = db._get_engine({"connection_url": pylons.config["ckan.datastore.write_url"]})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def _create_alias_table(self):
connection = db._get_engine(None,
{'connection_url': pylons.config['ckan.datastore_write_url']}).connect()
with connection.begin() as transaction:
table_metadata_exists_sql = '''
SELECT 1 FROM pg_views where viewname = '_table_metadata'
'''
table_metadata_exists = connection.execute(table_metadata_exists_sql).fetchone()
if not table_metadata_exists:
mapping_sql = '''
SELECT distinct
dependee.relname AS name,
-- r.ev_class::regclass AS oid,
dependent.relname AS alias_of
-- d.refobjid::regclass AS oid,
FROM
pg_attribute as a
JOIN pg_depend as d on d.refobjid = a.attrelid AND d.refobjsubid = a.attnum
JOIN pg_rewrite as r ON d.objid = r.oid
JOIN pg_class as dependee ON r.ev_class = dependee.oid
JOIN pg_class as dependent ON d.refobjid = dependent.oid
WHERE dependee.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
'''
create_alias_table_sql = u'create view "_table_metadata" as {0}'.format(mapping_sql)
connection.execute(create_alias_table_sql)
def setup_class(cls):
wsgiapp = middleware.make_app(config['global_conf'], **config)
cls.app = paste.fixture.TestApp(wsgiapp)
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id': resource.id,
'aliases': 'books',
'fields': [{'id': u'b\xfck', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'published'},
{'id': u'characters', u'type': u'_text'}],
'records': [{u'b\xfck': 'annakarenina',
'author': 'tolstoy',
'published': '2005-03-01',
'nested': ['b', {'moo': 'moo'}],
u'characters': [u'Princess Anna', u'Sergius']},
{u'b\xfck': 'warandpeace', 'author': 'tolstoy',
'nested': {'a': 'b'}}]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create', params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
engine = db._get_engine({
'connection_url': config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def _check_read_permissions(self):
'''
Check whether the right permissions are set for the read only user.
The table 'writetest' was created by
create_datastore_db_and_read_only_user.sql for this purpose.
'''
read_connection = db._get_engine(None,
{'connection_url': self.read_url}).connect()
read_trans = read_connection.begin()
statements = [
u"CREATE TABLE public.bar (id INTEGER NOT NULL, name VARCHAR)",
u"INSERT INTO public.writetest VALUES (1, 'okfn')"
]
try:
for sql in statements:
read_trans = read_connection.begin()
try:
read_connection.execute(sql)
except ProgrammingError, e:
if 'permission denied' not in str(e):
raise
except InternalError, e:
# This occurs when a slave in a Postgres master-slave config
if 'read-only transaction' not in str(e):
raise
def datastore_search_htsql(context, data_dict):
"""Execute HTSQL-Queries on the datastore.
:param htsql: htsql statement
:type htsql: string
:returns: a dictionary containing the search results.
keys: fields: columns for results
records: results from the query
:rtype: dictionary
"""
query = _get_or_bust(data_dict, "htsql")
query = str(query)
print query
uri = pylons.config["ckan.datastore.read_url"]
engine = db._get_engine(None, {"connection_url": uri})
htsql = HTSQL(None, {"tweak.sqlalchemy": {"engine": engine}, "tweak.timeout": {"timeout": 1000}})
with htsql:
cmd = UniversalCmd(query)
plan = analyze(cmd)
sql = plan.statement.sql
data_dict["sql"] = sql
action = p.toolkit.get_action("datastore_search_sql")
result = action(context, data_dict)
result["htsql"] = query
return result
def _create_alias_table(self):
mapping_sql = '''
SELECT DISTINCT
substr(md5(dependee.relname || COALESCE(dependent.relname, '')), 0, 17) AS "_id",
dependee.relname AS name,
dependee.oid AS oid,
dependent.relname AS alias_of
-- dependent.oid AS oid
FROM
pg_class AS dependee
LEFT OUTER JOIN pg_rewrite AS r ON r.ev_class = dependee.oid
LEFT OUTER JOIN pg_depend AS d ON d.objid = r.oid
LEFT OUTER JOIN pg_class AS dependent ON d.refobjid = dependent.oid
WHERE
(dependee.oid != dependent.oid OR dependent.oid IS NULL) AND
(dependee.relname IN (SELECT tablename FROM pg_catalog.pg_tables)
OR dependee.relname IN (SELECT viewname FROM pg_catalog.pg_views)) AND
dependee.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
ORDER BY dependee.oid DESC;
'''
create_alias_table_sql = u'CREATE OR REPLACE VIEW "_table_metadata" AS {0}'.format(
mapping_sql)
try:
connection = db._get_engine({
'connection_url': self.write_url
}).connect()
connection.execute(create_alias_table_sql)
finally:
connection.close()
def _clean_test_database(self, package_name, id):
base_location = self._get_ckan_base_api_url()
api_key = self._get_user_api_key()
testclient = CkanClient(base_location, api_key)
# package_name ='spatialize_test_resource_3'
testclient.package_entity_delete(package_name)
# also remove table from database using id
data_dict = {}
data_dict['connection_url'] = pylons.config.get(
'ckan.datastore.write_url',
'postgresql://*****:*****@localhost/test_datastore')
engine = db._get_engine(None, data_dict)
connection = engine.connect()
resources_sql = 'DROP TABLE IF EXISTS "' + id + '";'
# resources_sql = 'DROP TABLE "b11351a2-5bbc-4f8f-8078-86a4eef1c7b0";'
try:
print '>>>>>>>>>>>>> Executing command: ', resources_sql
trans = connection.begin()
results = connection.execute(resources_sql)
trans.commit()
except Exception, e:
print "exception", e
assert False
def datastore_trigger_each_row(context, data_dict):
''' update each record with trigger
The datastore_trigger_each_row API action allows you to apply triggers to
an existing DataStore resource.
:param resource_id: resource id that the data is going to be stored under.
:type resource_id: string
**Results:**
:returns: The rowcount in the table.
:rtype: int
'''
res_id = data_dict['resource_id']
p.toolkit.check_access('datastore_trigger_each_row', context, data_dict)
data_dict['connection_url'] = config['ckan.datastore.write_url']
sql = sqlalchemy.text(u'''update {0} set _id=_id '''.format(
datastore_helpers.identifier(res_id)))
try:
results = db._get_engine(data_dict).execute(sql)
except sqlalchemy.exc.DatabaseError as err:
message = err.args[0].split('\n')[0].decode('utf8')
raise p.toolkit.ValidationError(
{u'records': [message.split(u') ', 1)[-1]]})
return results.rowcount
def _read_connection_has_correct_privileges(self):
''' Returns True if the right permissions are set for the read
only user. A table is created by the write user to test the
read only user.
'''
write_connection = db._get_engine(
{'connection_url': self.write_url}).connect()
read_connection_user = sa_url.make_url(self.read_url).username
drop_foo_sql = u'DROP TABLE IF EXISTS _foo'
write_connection.execute(drop_foo_sql)
try:
write_connection.execute(u'CREATE TEMP TABLE _foo ()')
for privilege in ['INSERT', 'UPDATE', 'DELETE']:
test_privilege_sql = u"SELECT has_table_privilege(%s, '_foo', %s)"
have_privilege = write_connection.execute(
test_privilege_sql, (read_connection_user, privilege)).first()[0]
if have_privilege:
return False
finally:
write_connection.execute(drop_foo_sql)
write_connection.close()
return True
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id': resource.id,
'fields': [{'id': u'b\xfck', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'nested', 'type': 'json'},
{'id': 'characters', 'type': 'text[]'},
{'id': 'published'}],
'primary_key': u'b\xfck',
'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}]},
{u'b\xfck': 'warandpeace', 'author': 'tolstoy',
'nested': {'a':'b'}}
]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create', params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
import pylons
engine = db._get_engine(
None,
{'connection_url': pylons.config['ckan.datastore.write_url']}
)
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
resource = model.Package.get('annakarenina').resources[0]
cls.data = {
'resource_id':
resource.id,
'aliases':
'books',
'fields': [{
'id': u'b\xfck',
'type': 'text'
}, {
'id': 'author',
'type': 'text'
}, {
'id': 'published'
}, {
'id': u'characters',
u'type': u'_text'
}],
'records': [{
u'b\xfck': 'annakarenina',
'author': 'tolstoy',
'published': '2005-03-01',
'nested': ['b', {
'moo': 'moo'
}],
u'characters': [u'Princess Anna', u'Sergius']
}, {
u'b\xfck': 'warandpeace',
'author': 'tolstoy',
'nested': {
'a': 'b'
}
}]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create',
params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
import pylons
engine = db._get_engine(
None,
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
cls._original_config = config.copy()
config['ckan.plugins'] = 'datastore'
wsgiapp = middleware.make_app(config['global_conf'], **config)
cls.app = paste.fixture.TestApp(wsgiapp)
def test_pg_version_check(self):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
engine = db._get_engine(None,
{'connection_url': pylons.config['sqlalchemy.url']})
connection = engine.connect()
assert db._pg_version_is_at_least(connection, '8.0')
assert not db._pg_version_is_at_least(connection, '10.0')
def _get_local_engine(self):
if (self.engine is None):
data_dict = {}
data_dict['connection_url'] = pylons.config.get(
'sqlalchemy.url',
'postgresql://*****:*****@localhost/testdb')
self.engine = db._get_engine(None, data_dict)
return self.engine
def test_pg_version_check(self):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
engine = db._get_engine(
{'connection_url': pylons.config['sqlalchemy.url']})
connection = engine.connect()
assert db._pg_version_is_at_least(connection, '8.0')
assert not db._pg_version_is_at_least(connection, '10.0')
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
plugin = p.load('datastore')
if plugin.legacy_mode:
# make sure we undo adding the plugin
p.unload('datastore')
raise nose.SkipTest("SQL tests are not supported in legacy mode")
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
cls.dataset = model.Package.get('annakarenina')
resource = cls.dataset.resources[0]
cls.data = {
'resource_id': resource.id,
'force': True,
'aliases': 'books4',
'fields': [{'id': u'b\xfck', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'published'}],
'records': [{u'b\xfck': 'annakarenina',
'author': 'tolstoy',
'published': '2005-03-01',
'nested': ['b', {'moo': 'moo'}]},
{u'b\xfck': 'warandpeace',
'author': 'tolstoy',
'nested': {'a': 'b'}}]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create', params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
# Make an organization, because private datasets must belong to one.
cls.organization = tests.call_action_api(
cls.app, 'organization_create',
name='test_org',
apikey=cls.sysadmin_user.apikey)
cls.expected_records = [{u'_full_text': u"'annakarenina':1 'b':3 'moo':4 'tolstoy':2",
u'_id': 1,
u'author': u'tolstoy',
u'b\xfck': u'annakarenina',
u'nested': [u'b', {u'moo': u'moo'}],
u'published': u'2005-03-01T00:00:00'},
{u'_full_text': u"'b':3 'tolstoy':2 'warandpeac':1",
u'_id': 2,
u'author': u'tolstoy',
u'b\xfck': u'warandpeace',
u'nested': {u'a': u'b'},
u'published': None}]
cls.expected_join_results = [{u'first': 1, u'second': 1}, {u'first': 1, u'second': 2}]
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
cls.dataset = model.Package.get('annakarenina')
cls.resource = cls.dataset.resources[0]
cls.data = {
'resource_id': cls.resource.id,
'force': True,
'aliases': 'books3',
'fields': [{'id': u'b\xfck', 'type': 'text'},
{'id': 'author', 'type': 'text'},
{'id': 'published'},
{'id': u'characters', u'type': u'_text'},
{'id': 'rating with %'}],
'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}],
u'characters': [u'Princess Anna', u'Sergius'],
'rating with %': '60%'},
{u'b\xfck': 'warandpeace', 'author': 'tolstoy',
'nested': {'a': 'b'}, 'rating with %': '99%'}
]
}
postparams = '%s=1' % json.dumps(cls.data)
auth = {'Authorization': str(cls.sysadmin_user.apikey)}
res = cls.app.post('/api/action/datastore_create', params=postparams,
extra_environ=auth)
res_dict = json.loads(res.body)
assert res_dict['success'] is True
# Make an organization, because private datasets must belong to one.
cls.organization = tests.call_action_api(
cls.app, 'organization_create',
name='test_org',
apikey=cls.sysadmin_user.apikey)
cls.expected_records = [{u'published': u'2005-03-01T00:00:00',
u'_id': 1,
u'nested': [u'b', {u'moo': u'moo'}],
u'b\xfck': u'annakarenina',
u'author': u'tolstoy',
u'characters': [u'Princess Anna', u'Sergius'],
u'rating with %': u'60%'},
{u'published': None,
u'_id': 2,
u'nested': {u'a': u'b'},
u'b\xfck': u'warandpeace',
u'author': u'tolstoy',
u'characters': None,
u'rating with %': u'99%'}]
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']}
)
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def datastore_upsert(context, data_dict):
'''Updates or inserts into a table in the datastore
The datastore_upsert API action allows a user to add or edit records to
an existing dataStore resource. In order for the *upsert* and *update*
methods to work, a unique key has to be defined via the datastore_create
action. The available methods are:
*upsert*
Update if record with same key already exists, otherwise insert.
Requires unique key.
*insert*
Insert only. This method is faster that upsert, but will fail if any
inserted record matches an existing one. Does *not* require a unique
key.
*update*
Update only. An exception will occur if the key that should be updated
does not exist. Requires unique key.
:param resource_id: resource id that the data is going to be stored under.
:type resource_id: string
:param records: the data, eg: [{"dob": "2005", "some_stuff": ["a","b"]}]
:type records: list of dictionaries
:param method: the method to use to put the data into the datastore.
Possible options are: upsert (default), insert, update
:type method: string
**Results:**
:returns: The modified data object.
:rtype: dictionary
'''
if 'id' in data_dict:
data_dict['resource_id'] = data_dict['id']
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL'''
)
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(
p.toolkit._('Resource "{0}" was not found.'.format(res_id)))
p.toolkit.check_access('datastore_upsert', context, data_dict)
result = db.upsert(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def datastore_delete(context, data_dict):
'''Deletes a table or a set of records from the DataStore.
:param resource_id: resource id that the data will be deleted from. (optional)
:type resource_id: string
:param force: set to True to edit a read-only resource
:type force: bool (optional, default: False)
:param filters: filters to apply before deleting (eg {"name": "fred"}).
If missing delete whole table and all dependent views. (optional)
:type filters: dictionary
**Results:**
:returns: Original filters sent.
:rtype: dictionary
'''
schema = context.get('schema', dsschema.datastore_upsert_schema())
filters = data_dict.pop('filters', None)
data_dict, errors = _validate(data_dict, schema, context)
if filters:
data_dict['filters'] = filters
if errors:
raise p.toolkit.ValidationError(errors)
p.toolkit.check_access('datastore_delete', context, data_dict)
if not data_dict.pop('force', False):
resource_id = data_dict['resource_id']
_check_read_only(context, resource_id)
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
res_id = data_dict['resource_id']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL'''
)
results = db._get_engine(data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(
p.toolkit._(u'Resource "{0}" was not found.'.format(res_id)))
result = db.delete(context, data_dict)
# Set the datastore_active flag on the resource if necessary
if not data_dict.get('filters'):
p.toolkit.get_action('resource_patch')(context, {
'id': data_dict['resource_id'],
'datastore_active': False
})
result.pop('id', None)
result.pop('connection_url')
return result
def datastore_upsert(context, data_dict):
'''Updates or inserts into a table in the datastore
The datastore_upsert API action allows a user to add or edit records to
an existing dataStore resource. In order for the *upsert* and *update*
methods to work, a unique key has to be defined via the datastore_create
action. The available methods are:
*upsert*
Update if record with same key already exists, otherwise insert.
Requires unique key.
*insert*
Insert only. This method is faster that upsert, but will fail if any
inserted record matches an existing one. Does *not* require a unique
key.
*update*
Update only. An exception will occur if the key that should be updated
does not exist. Requires unique key.
:param resource_id: resource id that the data is going to be stored under.
:type resource_id: string
:param records: the data, eg: [{"dob": "2005", "some_stuff": ["a","b"]}]
:type records: list of dictionaries
:param method: the method to use to put the data into the datastore.
Possible options are: upsert (default), insert, update
:type method: string
**Results:**
:returns: The modified data object.
:rtype: dictionary
'''
if 'id' in data_dict:
data_dict['resource_id'] = data_dict['id']
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL''')
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(p.toolkit._(
'Resource "{0}" was not found.'.format(res_id)
))
p.toolkit.check_access('datastore_upsert', context, data_dict)
result = db.upsert(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def setup_class(cls):
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def setup_class(cls):
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
import pylons
engine = db._get_engine(
None,
{'connection_url': pylons.config['ckan.datastore.write_url']}
)
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def setup_class(cls):
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
import pylons
engine = db._get_engine(
None,
{'connection_url': pylons.config['ckan.datastore.write_url']}
)
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def datastore_delete(context, data_dict):
'''Deletes a table or a set of records from the DataStore.
:param resource_id: resource id that the data will be deleted from. (optional)
:type resource_id: string
:param force: set to True to edit a read-only resource
:type force: bool (optional, default: False)
:param filters: filters to apply before deleting (eg {"name": "fred"}).
If missing delete whole table and all dependent views. (optional)
:type filters: dictionary
**Results:**
:returns: Original filters sent.
:rtype: dictionary
'''
schema = context.get('schema', dsschema.datastore_upsert_schema())
filters = data_dict.pop('filters', None)
data_dict, errors = _validate(data_dict, schema, context)
if filters:
data_dict['filters'] = filters
if errors:
raise p.toolkit.ValidationError(errors)
p.toolkit.check_access('datastore_delete', context, data_dict)
if not data_dict.pop('force', False):
resource_id = data_dict['resource_id']
_check_read_only(context, resource_id)
data_dict['connection_url'] = pylons.config['ckan.datastore.write_url']
res_id = data_dict['resource_id']
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL''')
results = db._get_engine(data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(p.toolkit._(
u'Resource "{0}" was not found.'.format(res_id)
))
result = db.delete(context, data_dict)
# Set the datastore_active flag on the resource if necessary
if not data_dict.get('filters'):
p.toolkit.get_action('resource_patch')(
context, {'id': data_dict['resource_id'], 'datastore_active': False})
result.pop('id', None)
result.pop('connection_url')
return result
def datastore_search(context, data_dict):
'''Search a datastore table.
:param resource_id: id or alias of the data that is going to be selected.
:type resource_id: string
:param filters: matching conditions to select.
:type filters: dictionary
:param q: full text query
:type q: string
:param plain: treat as plain text query (default: true)
:type plain: bool
:param language: language of the full text query (default: english)
:type language: string
:param limit: maximum number of rows to return (default: 100)
:type limit: int
:param offset: offset the number of rows
:type offset: int
:param fields: fields to return
(default: all fields in original order)
:type fields: list or comma separated string
:param sort: comma separated field names with ordering
eg: "fieldname1, fieldname2 desc"
:type sort: string
:returns: a dictionary containing the search parameters and the
search results.
keys: fields: same as datastore_create accepts
offset: query offset value
limit: query limit value
filters: query filters
total: number of total matching records
records: list of matching results
:rtype: dictionary
'''
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
resources_sql = sqlalchemy.text(u'SELECT 1 FROM "_table_metadata" WHERE name = :id')
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(p.toolkit._(
'Resource "{0}" was not found.'.format(res_id)
))
p.toolkit.check_access('datastore_search', context, data_dict)
result = db.search(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def datastore_search(context, data_dict):
'''Search a datastore table.
:param resource_id: id or alias of the data that is going to be selected.
:type resource_id: string
:param filters: matching conditions to select.
:type filters: dictionary
:param q: full text query
:type q: string
:param plain: treat as plain text query (default: true)
:type plain: bool
:param language: language of the full text query (default: english)
:type language: string
:param limit: maximum number of rows to return (default: 100)
:type limit: int
:param offset: offset the number of rows
:type offset: int
:param fields: fields to return
(default: all fields in original order)
:type fields: list or comma separated string
:param sort: comma separated field names with ordering
eg: "fieldname1, fieldname2 desc"
:type sort: string
:returns: a dictionary containing the search parameters and the
search results.
keys: fields: same as datastore_create accepts
offset: query offset value
limit: query limit value
filters: query filters
total: number of total matching records
records: list of matching results
:rtype: dictionary
'''
res_id = _get_or_bust(data_dict, 'resource_id')
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
resources_sql = sqlalchemy.text(
u'SELECT 1 FROM "_table_metadata" WHERE name = :id')
results = db._get_engine(None, data_dict).execute(resources_sql, id=res_id)
res_exists = results.rowcount > 0
if not res_exists:
raise p.toolkit.ObjectNotFound(
p.toolkit._('Resource "{0}" was not found.'.format(res_id)))
p.toolkit.check_access('datastore_search', context, data_dict)
result = db.search(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def _resource_exists(context, data_dict):
''' Returns true if the resource exists in CKAN and in the datastore '''
model = _get_or_bust(context, 'model')
res_id = _get_or_bust(data_dict, 'resource_id')
if not model.Resource.get(res_id):
return False
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL''')
results = db._get_engine(data_dict).execute(resources_sql, id=res_id)
return results.rowcount > 0
def _resource_exists(context, data_dict):
''' Returns true if the resource exists in CKAN and in the datastore '''
model = _get_or_bust(context, 'model')
res_id = _get_or_bust(data_dict, 'resource_id')
if not model.Resource.get(res_id):
return False
resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
WHERE name = :id AND alias_of IS NULL''')
results = db._get_engine(data_dict).execute(resources_sql, id=res_id)
return results.rowcount > 0
def _is_read_only_database(self):
''' Returns True if no connection has CREATE privileges on the public
schema. This is the case if replication is enabled.'''
for url in [self.ckan_url, self.write_url, self.read_url]:
connection = db._get_engine(None,
{'connection_url': url}).connect()
sql = u"SELECT has_schema_privilege('public', 'CREATE')"
is_writable = connection.execute(sql).first()[0]
if is_writable:
return False
return True
def configure(self, config):
self.config = config
# check for ckan.datastore.write_url and ckan.datastore.read_url
if 'ckan.datastore.write_url' not in config:
error_msg = 'ckan.datastore.write_url not found in config'
raise DatastoreException(error_msg)
# Legacy mode means that we have no read url. Consequently sql search
# is not available and permissions do not have to be changed. In legacy
# mode, the datastore runs on PG prior to 9.0 (for example 8.4).
self.legacy_mode = _is_legacy_mode(self.config)
# Check whether users have disabled datastore_search_sql
self.enable_sql_search = p.toolkit.asbool(
self.config.get('ckan.datastore.sqlsearch.enabled', True))
datapusher_formats = config.get('datapusher.formats', '').split()
self.datapusher_formats = datapusher_formats or DEFAULT_FORMATS
# Check whether we are running one of the paster commands which means
# that we should ignore the following tests.
if sys.argv[0].split('/')[-1] == 'paster':
if 'datastore' in sys.argv[1:]:
log.warn(
'Omitting permission checks because you are running'
'paster commands.'
)
return
self.ckan_url = self.config['sqlalchemy.url']
self.write_url = self.config['ckan.datastore.write_url']
if self.legacy_mode:
self.read_url = self.write_url
log.warn('Legacy mode active. '
'The sql search will not be available.')
else:
self.read_url = self.config['ckan.datastore.read_url']
self.read_engine = db._get_engine(
{'connection_url': self.read_url})
if not model.engine_is_pg(self.read_engine):
log.warn('We detected that you do not use a PostgreSQL '
'database. The DataStore will NOT work and DataStore '
'tests will be skipped.')
return
if self._is_read_only_database():
log.warn('We detected that CKAN is running on a read '
'only database. Permission checks and the creation '
'of _table_metadata are skipped.')
else:
self._check_urls_and_permissions()
self._create_alias_table()
def _is_read_only_database(self):
''' Returns True if no connection has CREATE privileges on the public
schema. This is the case if replication is enabled.'''
for url in [self.ckan_url, self.write_url, self.read_url]:
connection = db._get_engine(None, {
'connection_url': url
}).connect()
sql = u"SELECT has_schema_privilege('public', 'CREATE')"
is_writable = connection.execute(sql).first()[0]
if is_writable:
return False
return True
def setup_class(cls):
wsgiapp = middleware.make_app(config['global_conf'], **config)
cls.app = paste.fixture.TestApp(wsgiapp)
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def unpublish(self):
conn_params = {
'connection_url': self.connection_url,
'package_id': self.package_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
sql = "DROP MATERIALIZED VIEW IF EXISTS " + self.getName()
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
def setup_class(cls):
wsgiapp = middleware.make_app(config["global_conf"], **config)
cls.app = paste.fixture.TestApp(wsgiapp)
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load("datastore")
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get("testsysadmin")
cls.normal_user = model.User.get("annafan")
engine = db._get_engine({"connection_url": pylons.config["ckan.datastore.write_url"]})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
set_url_type(model.Package.get("annakarenina").resources, cls.sysadmin_user)
def setup_class(cls):
wsgiapp = middleware.make_app(config['global_conf'], **config)
cls.app = paste.fixture.TestApp(wsgiapp)
if not tests.is_datastore_supported():
raise nose.SkipTest("Datastore not supported")
p.load('datastore')
ctd.CreateTestData.create()
cls.sysadmin_user = model.User.get('testsysadmin')
cls.normal_user = model.User.get('annafan')
engine = db._get_engine(
{'connection_url': pylons.config['ckan.datastore.write_url']})
cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
def _is_read_only_database(self):
""" Returns True if no connection has CREATE privileges on the public
schema. This is the case if replication is enabled."""
for url in [self.ckan_url, self.write_url, self.read_url]:
connection = db._get_engine({"connection_url": url}).connect()
try:
sql = u"SELECT has_schema_privilege('public', 'CREATE')"
is_writable = connection.execute(sql).first()[0]
finally:
connection.close()
if is_writable:
return False
return True
def _is_legacy_mode(config):
"""
Decides if the DataStore should run on legacy mode
Returns True if `ckan.datastore.read_url` is not set in the provided
config object or CKAN is running on Postgres < 9.x
"""
write_url = config.get("ckan.datastore.write_url")
engine = db._get_engine({"connection_url": write_url})
connection = engine.connect()
return not config.get("ckan.datastore.read_url") or not db._pg_version_is_at_least(connection, "9.0")
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
# Get the connection parameters for the datastore
conn_params = {
'connection_url': self.connection_url,
'resource_id': self.resource_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({"connection": connection}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(
toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col['id'] == self.geo_col for col in fields):
# ... append one
fields.append({'id': self.geo_col, 'type': u'geometry'})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (
self.resource_id, self.geo_col)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = "UPDATE \"%s\" SET \"%s\" = geometryfromtext('POINT(' || \"%s\" || ' ' || \"%s\" || ')', 4326)"
sql = sql % (self.resource_id, self.geo_col, self.lng_col,
self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
return True
return True
def _is_legacy_mode(config):
'''
Decides if the DataStore should run on legacy mode
Returns True if `ckan.datastore.read_url` is not set in the provided
config object or CKAN is running on Postgres < 9.x
'''
write_url = config.get('ckan.datastore.write_url')
engine = db._get_engine({'connection_url': write_url})
connection = engine.connect()
return (not config.get('ckan.datastore.read_url')
or not db._pg_version_is_at_least(connection, '9.0'))
def unpublish(self):
conn_params = {
'connection_url': self.connection_url,
'package_id': self.resource_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
sql = "DROP TABLE IF EXISTS _" + re.sub('-', '_', self.resource_id)
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
def _is_legacy_mode(config):
'''
Decides if the DataStore should run on legacy mode
Returns True if `ckan.datastore.read_url` is not set in the provided
config object or CKAN is running on Postgres < 9.x
'''
write_url = config.get('ckan.datastore.write_url')
engine = db._get_engine({'connection_url': write_url})
connection = engine.connect()
return (not config.get('ckan.datastore.read_url') or
not db._pg_version_is_at_least(connection, '9.0'))
def configure(self, config):
self.config = config
# check for ckan.datastore.write_url and ckan.datastore.read_url
if (not 'ckan.datastore.write_url' in config):
error_msg = 'ckan.datastore.write_url not found in config'
raise DatastoreException(error_msg)
# Legacy mode means that we have no read url. Consequently sql search is not
# available and permissions do not have to be changed. In legacy mode, the
# datastore runs on PG prior to 9.0 (for example 8.4).
self.legacy_mode = _is_legacy_mode(self.config)
# Check whether users have disabled datastore_search_sql
self.enable_sql_search = p.toolkit.asbool(
self.config.get('ckan.datastore.sqlsearch.enabled', True))
datapusher_formats = config.get('datapusher.formats', '').split()
self.datapusher_formats = datapusher_formats or DEFAULT_FORMATS
# Check whether we are running one of the paster commands which means
# that we should ignore the following tests.
if sys.argv[0].split(
'/')[-1] == 'paster' and 'datastore' in sys.argv[1:]:
log.warn('Omitting permission checks because you are '
'running paster commands.')
return
self.ckan_url = self.config['sqlalchemy.url']
self.write_url = self.config['ckan.datastore.write_url']
if self.legacy_mode:
self.read_url = self.write_url
log.warn('Legacy mode active. '
'The sql search will not be available.')
else:
self.read_url = self.config['ckan.datastore.read_url']
self.read_engine = db._get_engine({'connection_url': self.read_url})
if not model.engine_is_pg(self.read_engine):
log.warn('We detected that you do not use a PostgreSQL '
'database. The DataStore will NOT work and DataStore '
'tests will be skipped.')
return
if self._is_read_only_database():
log.warn('We detected that CKAN is running on a read '
'only database. Permission checks and the creation '
'of _table_metadata are skipped.')
else:
self._check_urls_and_permissions()
self._create_alias_table()
def unpublish(self):
conn_params = {
'connection_url': self.connection_url,
'package_id': self.package_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
sql = "DROP TABLE IF EXISTS _" + re.sub('-', '_', self.package_id)
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
def new_resource_show(context, data_dict):
engine = db._get_engine(context, {'connection_url': self.read_url})
new_data_dict = resource_show(context, data_dict)
try:
connection = engine.connect()
result = connection.execute(
'SELECT 1 FROM "_table_metadata" WHERE name = %s AND alias_of IS NULL',
new_data_dict['id']).fetchone()
if result:
new_data_dict['datastore_active'] = True
else:
new_data_dict['datastore_active'] = False
finally:
connection.close()
return new_data_dict
