def _build_query_and_rank_statements(self, lang, query, plain, field=None):
query_alias = self._ts_query_alias(field)
rank_alias = self._ts_rank_alias(field)
lang_literal = literal_string(lang)
query_literal = literal_string(query)
if plain:
statement = (u'plainto_tsquery({lang_literal}, '
u'{query_literal}) {query_alias}')
else:
statement = (
u'to_tsquery({lang_literal}, {query_literal}) {query_alias}')
statement = statement.format(lang_literal=lang_literal,
query_literal=query_literal,
query_alias=query_alias)
if field is None:
rank_field = '_full_text'
else:
rank_field = (
u'to_tsvector({lang_literal}, cast("{field}" as text))'
).format(lang_literal=lang_literal, field=field)
rank_statement = (
u'ts_rank({rank_field}, {query_alias}, 32) AS {alias}').format(
rank_field=rank_field,
query_alias=query_alias,
alias=rank_alias)
return statement, rank_statement
def _build_query_and_rank_statements(self, lang, query, plain, field=None):
query_alias = self._ts_query_alias(field)
rank_alias = self._ts_rank_alias(field)
lang_literal = literal_string(lang)
query_literal = literal_string(query)
if plain:
statement = u"plainto_tsquery({lang_literal}, {query_literal}) {query_alias}"
else:
statement = u"to_tsquery({lang_literal}, {query_literal}) {query_alias}"
statement = statement.format(lang_literal=lang_literal,
query_literal=query_literal, query_alias=query_alias)
if field is None:
rank_field = '_full_text'
else:
rank_field = u'to_tsvector({lang_literal}, cast("{field}" as text))'
rank_field = rank_field.format(lang_literal=lang_literal, field=field)
rank_statement = u'ts_rank({rank_field}, {query_alias}, 32) AS {alias}'
rank_statement = rank_statement.format(rank_field=rank_field,
query_alias=query_alias,
alias=rank_alias)
return statement, rank_statement
def datastore_create_temp_user_table(context):
'''
Create a table to pass the current username and sysadmin
state to our triggers for marking modified rows
'''
from ckanext.datastore.helpers import literal_string
connection = context['connection']
username = context['user']
connection.execute(u'''
CREATE TEMP TABLE datastore_user (
username text NOT NULL,
sysadmin boolean NOT NULL
) ON COMMIT DROP;
INSERT INTO datastore_user VALUES (
{username}, {sysadmin}
);
'''.format(username=literal_string(username),
sysadmin='TRUE' if is_sysadmin(username) else 'FALSE'))
def datastore_create_temp_user_table(context):
'''
Create a table to pass the current username and sysadmin
state to our triggers for marking modified rows
'''
from ckanext.datastore.helpers import literal_string
connection = context['connection']
username = context['user']
connection.execute(u'''
CREATE TEMP TABLE datastore_user (
username text NOT NULL,
sysadmin boolean NOT NULL
) ON COMMIT DROP;
INSERT INTO datastore_user VALUES (
{username}, {sysadmin}
);
'''.format(
username=literal_string(username),
sysadmin='TRUE' if is_sysadmin(username) else 'FALSE'))
def _where(self, data_dict, fields_types):
filters = data_dict.get('filters', {})
clauses = []
for field, value in filters.iteritems():
if field not in fields_types:
continue
field_array_type = self._is_array_type(fields_types[field])
if isinstance(value, list) and not field_array_type:
clause_str = (u'"{0}" in ({1})'.format(field,
','.join(['%s'] * len(value))))
clause = (clause_str,) + tuple(value)
else:
clause = (u'"{0}" = %s'.format(field), value)
clauses.append(clause)
# add full-text search where clause
q = data_dict.get('q')
if q:
if isinstance(q, basestring):
ts_query_alias = self._ts_query_alias()
clause_str = u'_full_text @@ {0}'.format(ts_query_alias)
clauses.append((clause_str,))
elif isinstance(q, dict):
lang = self._fts_lang(data_dict.get('lang'))
for field, value in q.iteritems():
if field not in fields_types:
continue
query_field = self._ts_query_alias(field)
ftyp = fields_types[field]
if not datastore_helpers.should_fts_index_field_type(ftyp):
clause_str = u'_full_text @@ {0}'.format(query_field)
clauses.append((clause_str,))
clause_str = (u'to_tsvector({0}, cast("{1}" as text)) '
u'@@ {2}').format(literal_string(lang),
field, query_field)
clauses.append((clause_str,))
return clauses
def _where(self, data_dict, fields_types):
filters = data_dict.get('filters', {})
clauses = []
for field, value in filters.iteritems():
if field not in fields_types:
continue
field_array_type = self._is_array_type(fields_types[field])
if isinstance(value, list) and not field_array_type:
clause_str = (u'"{0}" in ({1})'.format(
field, ','.join(['%s'] * len(value))))
clause = (clause_str, ) + tuple(value)
else:
clause = (u'"{0}" = %s'.format(field), value)
clauses.append(clause)
# add full-text search where clause
q = data_dict.get('q')
if q:
if isinstance(q, basestring):
ts_query_alias = self._ts_query_alias()
clause_str = u'_full_text @@ {0}'.format(ts_query_alias)
clauses.append((clause_str, ))
elif isinstance(q, dict):
lang = self._fts_lang(data_dict.get('lang'))
for field, value in q.iteritems():
if field not in fields_types:
continue
query_field = self._ts_query_alias(field)
ftyp = fields_types[field]
if not datastore_helpers.should_fts_index_field_type(ftyp):
clause_str = u'_full_text @@ {0}'.format(query_field)
clauses.append((clause_str, ))
clause_str = (u'to_tsvector({0}, cast("{1}" as text)) '
u'@@ {2}').format(literal_string(lang),
field, query_field)
clauses.append((clause_str, ))
return clauses
def _pg_array(choices):
from ckanext.datastore.helpers import literal_string
return u'ARRAY[' + u','.join(
literal_string(unicode(c)) for c in choices) + u']'
def alter_table(context, data_dict):
'''alter table from combination of fields and first row of data
return: all fields of the resource table'''
supplied_fields = data_dict.get('fields', [])
current_fields = _get_fields(context, data_dict)
if not supplied_fields:
supplied_fields = current_fields
check_fields(context, supplied_fields)
field_ids = _pluck('id', supplied_fields)
records = data_dict.get('records')
new_fields = []
for num, field in enumerate(supplied_fields):
# check to see if field definition is the same or and
# extension of current fields
if num < len(current_fields):
if field['id'] != current_fields[num]['id']:
raise ValidationError({
'fields':
[(u'Supplied field "{0}" not '
u'present or in wrong order').format(field['id'])]
})
# no need to check type as field already defined.
continue
if 'type' not in field:
if not records or field['id'] not in records[0]:
raise ValidationError({
'fields':
[u'"{0}" type not guessable'.format(field['id'])]
})
field['type'] = _guess_type(records[0][field['id']])
new_fields.append(field)
if records:
# check record for sanity as they have not been
# checked during validation
if not isinstance(records, list):
raise ValidationError(
{'records': ['Records has to be a list of dicts']})
if not isinstance(records[0], dict):
raise ValidationError(
{'records': ['The first row is not a json object']})
supplied_field_ids = records[0].keys()
for field_id in supplied_field_ids:
if field_id not in field_ids:
new_fields.append({
'id': field_id,
'type': _guess_type(records[0][field_id])
})
alter_sql = []
for f in new_fields:
alter_sql.append(u'ALTER TABLE {0} ADD {1} {2};'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']), f['type']))
for f in supplied_fields:
if u'info' in f:
info = f.get(u'info')
if isinstance(info, dict):
info_sql = datastore_helpers.literal_string(
json.dumps(info, ensure_ascii=False))
else:
info_sql = 'NULL'
alter_sql.append(u'COMMENT ON COLUMN {0}.{1} is {2}'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']), info_sql))
if alter_sql:
context['connection'].execute(u';'.join(alter_sql).replace(
u'%', u'%%'))
def create_table(context, data_dict):
'''Create table from combination of fields and first row of data.'''
datastore_fields = [
{
'id': '_id',
'type': 'serial primary key'
},
{
'id': '_full_text',
'type': 'tsvector'
},
]
# check first row of data for additional fields
extra_fields = []
supplied_fields = data_dict.get('fields', [])
check_fields(context, supplied_fields)
field_ids = _pluck('id', supplied_fields)
records = data_dict.get('records')
# if type is field is not given try and guess or throw an error
for field in supplied_fields:
if 'type' not in field:
if not records or field['id'] not in records[0]:
raise ValidationError({
'fields':
[u'"{0}" type not guessable'.format(field['id'])]
})
field['type'] = _guess_type(records[0][field['id']])
# Check for duplicate fields
unique_fields = set([f['id'] for f in supplied_fields])
if not len(unique_fields) == len(supplied_fields):
raise ValidationError(
{'field': ['Duplicate column names are not supported']})
if records:
# check record for sanity
if not isinstance(records[0], dict):
raise ValidationError(
{'records': ['The first row is not a json object']})
supplied_field_ids = records[0].keys()
for field_id in supplied_field_ids:
if field_id not in field_ids:
extra_fields.append({
'id': field_id,
'type': _guess_type(records[0][field_id])
})
fields = datastore_fields + supplied_fields + extra_fields
sql_fields = u", ".join([
u'{0} {1}'.format(datastore_helpers.identifier(f['id']), f['type'])
for f in fields
])
sql_string = u'CREATE TABLE {0} ({1});'.format(
datastore_helpers.identifier(data_dict['resource_id']), sql_fields)
info_sql = []
for f in supplied_fields:
info = f.get(u'info')
if isinstance(info, dict):
info_sql.append(u'COMMENT ON COLUMN {0}.{1} is {2}'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']),
datastore_helpers.literal_string(
json.dumps(info, ensure_ascii=False))))
context['connection'].execute(
(sql_string + u';'.join(info_sql)).replace(u'%', u'%%'))
def pg_array(choices):
from ckanext.datastore.helpers import literal_string
return u'ARRAY[' + u','.join(
literal_string(unicode(c[0])) for c in choices) + u']'
def alter_table(context, data_dict):
'''alter table from combination of fields and first row of data
return: all fields of the resource table'''
supplied_fields = data_dict.get('fields', [])
current_fields = _get_fields(context, data_dict)
if not supplied_fields:
supplied_fields = current_fields
check_fields(context, supplied_fields)
field_ids = _pluck('id', supplied_fields)
records = data_dict.get('records')
new_fields = []
for num, field in enumerate(supplied_fields):
# check to see if field definition is the same or and
# extension of current fields
if num < len(current_fields):
if field['id'] != current_fields[num]['id']:
raise ValidationError({
'fields': [(u'Supplied field "{0}" not '
u'present or in wrong order').format(
field['id'])]
})
# no need to check type as field already defined.
continue
if 'type' not in field:
if not records or field['id'] not in records[0]:
raise ValidationError({
'fields': [u'"{0}" type not guessable'.format(field['id'])]
})
field['type'] = _guess_type(records[0][field['id']])
new_fields.append(field)
if records:
# check record for sanity as they have not been
# checked during validation
if not isinstance(records, list):
raise ValidationError({
'records': ['Records has to be a list of dicts']
})
if not isinstance(records[0], dict):
raise ValidationError({
'records': ['The first row is not a json object']
})
supplied_field_ids = records[0].keys()
for field_id in supplied_field_ids:
if field_id not in field_ids:
new_fields.append({
'id': field_id,
'type': _guess_type(records[0][field_id])
})
alter_sql = []
for f in new_fields:
alter_sql.append(u'ALTER TABLE {0} ADD {1} {2};'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']),
f['type']))
for f in supplied_fields:
if u'info' in f:
info = f.get(u'info')
if isinstance(info, dict):
info_sql = datastore_helpers.literal_string(
json.dumps(info, ensure_ascii=False))
else:
info_sql = 'NULL'
alter_sql.append(u'COMMENT ON COLUMN {0}.{1} is {2}'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']),
info_sql))
if alter_sql:
context['connection'].execute(
u';'.join(alter_sql).replace(u'%', u'%%'))
def create_table(context, data_dict):
'''Create table from combination of fields and first row of data.'''
datastore_fields = [
{'id': '_id', 'type': 'serial primary key'},
{'id': '_full_text', 'type': 'tsvector'},
]
# check first row of data for additional fields
extra_fields = []
supplied_fields = data_dict.get('fields', [])
check_fields(context, supplied_fields)
field_ids = _pluck('id', supplied_fields)
records = data_dict.get('records')
# if type is field is not given try and guess or throw an error
for field in supplied_fields:
if 'type' not in field:
if not records or field['id'] not in records[0]:
raise ValidationError({
'fields': [u'"{0}" type not guessable'.format(field['id'])]
})
field['type'] = _guess_type(records[0][field['id']])
# Check for duplicate fields
unique_fields = set([f['id'] for f in supplied_fields])
if not len(unique_fields) == len(supplied_fields):
raise ValidationError({
'field': ['Duplicate column names are not supported']
})
if records:
# check record for sanity
if not isinstance(records[0], dict):
raise ValidationError({
'records': ['The first row is not a json object']
})
supplied_field_ids = records[0].keys()
for field_id in supplied_field_ids:
if field_id not in field_ids:
extra_fields.append({
'id': field_id,
'type': _guess_type(records[0][field_id])
})
fields = datastore_fields + supplied_fields + extra_fields
sql_fields = u", ".join([u'{0} {1}'.format(
datastore_helpers.identifier(f['id']), f['type']) for f in fields])
sql_string = u'CREATE TABLE {0} ({1});'.format(
datastore_helpers.identifier(data_dict['resource_id']),
sql_fields
)
info_sql = []
for f in supplied_fields:
info = f.get(u'info')
if isinstance(info, dict):
info_sql.append(u'COMMENT ON COLUMN {0}.{1} is {2}'.format(
datastore_helpers.identifier(data_dict['resource_id']),
datastore_helpers.identifier(f['id']),
datastore_helpers.literal_string(
json.dumps(info, ensure_ascii=False))))
context['connection'].execute(
(sql_string + u';'.join(info_sql)).replace(u'%', u'%%'))