Esempio n. 1
0
    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
Esempio n. 2
0
 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
Esempio n. 3
0
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'))
Esempio n. 4
0
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'))
Esempio n. 5
0
    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
Esempio n. 6
0
    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
Esempio n. 7
0
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']'
Esempio n. 8
0
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'%%'))
Esempio n. 9
0
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'%%'))
Esempio n. 10
0
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']'
Esempio n. 11
0
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'%%'))
Esempio n. 12
0
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'%%'))