diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/versions/2f3c3597c78d_.py | 36 | ||||
| -rw-r--r-- | migrations/versions/7ff57806ffd5_.py | 249 | ||||
| -rw-r--r-- | migrations/versions/83622276d439_.py | 4 |
3 files changed, 287 insertions, 2 deletions
diff --git a/migrations/versions/2f3c3597c78d_.py b/migrations/versions/2f3c3597c78d_.py new file mode 100644 index 0000000..b80945e --- /dev/null +++ b/migrations/versions/2f3c3597c78d_.py @@ -0,0 +1,36 @@ +"""empty message + +Revision ID: 2f3c3597c78d +Revises: 9ec17b558413 +Create Date: 2019-01-29 02:43:08.865695 + +""" +from alembic import op +import sqlalchemy as sa +from sqlalchemy.dialects import postgresql +from sqlalchemy_utils.types import TSVectorType +from sqlalchemy_searchable import sync_trigger + +# revision identifiers, used by Alembic. +revision = '2f3c3597c78d' +down_revision = '9ec17b558413' +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.alter_column('package', 'short_desc', nullable=False, new_column_name='short_desc') + op.add_column('package', sa.Column('search_vector', TSVectorType("title", "short_desc", "desc"), nullable=True)) + op.create_index('ix_package_search_vector', 'package', ['search_vector'], unique=False, postgresql_using='gin') + + conn = op.get_bind() + sync_trigger(conn, 'package', 'search_vector', ["title", "short_desc", "desc"]) + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.drop_index('ix_package_search_vector', table_name='package') + op.drop_column('package', 'search_vector') + # ### end Alembic commands ### diff --git a/migrations/versions/7ff57806ffd5_.py b/migrations/versions/7ff57806ffd5_.py new file mode 100644 index 0000000..f1e4869 --- /dev/null +++ b/migrations/versions/7ff57806ffd5_.py @@ -0,0 +1,249 @@ +"""empty message + +Revision ID: 7ff57806ffd5 +Revises: 2f3c3597c78d +Create Date: 2019-01-29 02:57:50.279918 + +""" +from alembic import op +import sqlalchemy as sa +from sqlalchemy.dialects import postgresql + +# revision identifiers, used by Alembic. +revision = '7ff57806ffd5' +down_revision = '2f3c3597c78d' +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.execute(""" + DROP TYPE IF EXISTS tsq_state CASCADE; + +CREATE TYPE tsq_state AS ( + search_query text, + parentheses_stack int, + skip_for int, + current_token text, + current_index int, + current_char text, + previous_char text, + tokens text[] +); + +CREATE OR REPLACE FUNCTION tsq_append_current_token(state tsq_state) +RETURNS tsq_state AS $$ +BEGIN + IF state.current_token != '' THEN + state.tokens := array_append(state.tokens, state.current_token); + state.current_token := ''; + END IF; + RETURN state; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_tokenize_character(state tsq_state) +RETURNS tsq_state AS $$ +BEGIN + IF state.current_char = '(' THEN + state.tokens := array_append(state.tokens, '('); + state.parentheses_stack := state.parentheses_stack + 1; + state := tsq_append_current_token(state); + ELSIF state.current_char = ')' THEN + IF (state.parentheses_stack > 0 AND state.current_token != '') THEN + state := tsq_append_current_token(state); + state.tokens := array_append(state.tokens, ')'); + state.parentheses_stack := state.parentheses_stack - 1; + END IF; + ELSIF state.current_char = '"' THEN + state.skip_for := position('"' IN substring( + state.search_query FROM state.current_index + 1 + )); + + IF state.skip_for > 1 THEN + state.tokens = array_append( + state.tokens, + substring( + state.search_query + FROM state.current_index FOR state.skip_for + 1 + ) + ); + ELSIF state.skip_for = 0 THEN + state.current_token := state.current_token || state.current_char; + END IF; + ELSIF ( + state.current_char = '-' AND + (state.current_index = 1 OR state.previous_char = ' ') + ) THEN + state.tokens := array_append(state.tokens, '-'); + ELSIF state.current_char = ' ' THEN + state := tsq_append_current_token(state); + IF substring( + state.search_query FROM state.current_index FOR 4 + ) = ' or ' THEN + state.skip_for := 2; + + -- remove duplicate OR tokens + IF state.tokens[array_length(state.tokens, 1)] != ' | ' THEN + state.tokens := array_append(state.tokens, ' | '); + END IF; + END IF; + ELSE + state.current_token = state.current_token || state.current_char; + END IF; + RETURN state; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_tokenize(search_query text) RETURNS text[] AS $$ +DECLARE + state tsq_state; +BEGIN + SELECT + search_query::text AS search_query, + 0::int AS parentheses_stack, + 0 AS skip_for, + ''::text AS current_token, + 0 AS current_index, + ''::text AS current_char, + ''::text AS previous_char, + '{}'::text[] AS tokens + INTO state; + + state.search_query := lower(trim( + regexp_replace(search_query, '""+', '""', 'g') + )); + + FOR state.current_index IN ( + SELECT generate_series(1, length(state.search_query)) + ) LOOP + state.current_char := substring( + search_query FROM state.current_index FOR 1 + ); + + IF state.skip_for > 0 THEN + state.skip_for := state.skip_for - 1; + CONTINUE; + END IF; + + state := tsq_tokenize_character(state); + state.previous_char := state.current_char; + END LOOP; + state := tsq_append_current_token(state); + + state.tokens := array_nremove(state.tokens, '(', -state.parentheses_stack); + + RETURN state.tokens; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + +-- Processes an array of text search tokens and returns a tsquery +CREATE OR REPLACE FUNCTION tsq_process_tokens(config regconfig, tokens text[]) +RETURNS tsquery AS $$ +DECLARE + result_query text; + previous_value text; + value text; +BEGIN + result_query := ''; + FOREACH value IN ARRAY tokens LOOP + IF value = '"' THEN + CONTINUE; + END IF; + + IF left(value, 1) = '"' AND right(value, 1) = '"' THEN + value := phraseto_tsquery(config, value); + ELSIF value NOT IN ('(', ' | ', ')', '-') THEN + value := quote_literal(value) || ':*'; + END IF; + + IF previous_value = '-' THEN + IF value = '(' THEN + value := '!' || value; + ELSE + value := '!(' || value || ')'; + END IF; + END IF; + + SELECT + CASE + WHEN result_query = '' THEN value + WHEN ( + previous_value IN ('!(', '(', ' | ') OR + value IN (')', ' | ') + ) THEN result_query || value + ELSE result_query || ' & ' || value + END + INTO result_query; + previous_value := value; + END LOOP; + + RETURN to_tsquery(config, result_query); +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_process_tokens(tokens text[]) +RETURNS tsquery AS $$ + SELECT tsq_process_tokens(get_current_ts_config(), tokens); +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_parse(config regconfig, search_query text) +RETURNS tsquery AS $$ + SELECT tsq_process_tokens(config, tsq_tokenize(search_query)); +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_parse(config text, search_query text) +RETURNS tsquery AS $$ + SELECT tsq_parse(config::regconfig, search_query); +$$ LANGUAGE SQL IMMUTABLE; + + +CREATE OR REPLACE FUNCTION tsq_parse(search_query text) RETURNS tsquery AS $$ + SELECT tsq_parse(get_current_ts_config(), search_query); +$$ LANGUAGE SQL IMMUTABLE; + + +-- remove first N elements equal to the given value from the array (array +-- must be one-dimensional) +-- +-- If negative value is given as the third argument the removal of elements +-- starts from the last array element. +CREATE OR REPLACE FUNCTION array_nremove(anyarray, anyelement, int) +RETURNS ANYARRAY AS $$ + WITH replaced_positions AS ( + SELECT UNNEST( + CASE + WHEN $2 IS NULL THEN + '{}'::int[] + WHEN $3 > 0 THEN + (array_positions($1, $2))[1:$3] + WHEN $3 < 0 THEN + (array_positions($1, $2))[ + (cardinality(array_positions($1, $2)) + $3 + 1): + ] + ELSE + '{}'::int[] + END + ) AS position + ) + SELECT COALESCE(( + SELECT array_agg(value) + FROM unnest($1) WITH ORDINALITY AS t(value, index) + WHERE index NOT IN (SELECT position FROM replaced_positions) + ), $1[1:0]); +$$ LANGUAGE SQL IMMUTABLE; +""") + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + pass + # ### end Alembic commands ### diff --git a/migrations/versions/83622276d439_.py b/migrations/versions/83622276d439_.py index 49f5ebb..b275752 100644 --- a/migrations/versions/83622276d439_.py +++ b/migrations/versions/83622276d439_.py @@ -66,7 +66,7 @@ def upgrade(): sa.Column('author_id', sa.Integer(), nullable=True), sa.Column('name', sa.String(length=100), nullable=False), sa.Column('title', sa.String(length=100), nullable=False), - sa.Column('shortDesc', sa.String(length=200), nullable=False), + sa.Column('short_desc', sa.String(length=200), nullable=False), sa.Column('desc', sa.Text(), nullable=True), sa.Column('type', sa.Enum('MOD', 'GAME', 'TXP', name='packagetype'), nullable=True), sa.Column('license_id', sa.Integer(), nullable=True), @@ -141,7 +141,7 @@ def upgrade(): op.create_table('edit_request_change', sa.Column('id', sa.Integer(), nullable=False), sa.Column('request_id', sa.Integer(), nullable=True), - sa.Column('key', sa.Enum('name', 'title', 'shortDesc', 'desc', 'type', 'license', 'tags', 'repo', 'website', 'issueTracker', 'forums', name='packagepropertykey'), nullable=False), + sa.Column('key', sa.Enum('name', 'title', 'short_desc', 'desc', 'type', 'license', 'tags', 'repo', 'website', 'issueTracker', 'forums', name='packagepropertykey'), nullable=False), sa.Column('oldValue', sa.Text(), nullable=True), sa.Column('newValue', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['request_id'], ['edit_request.id'], ), |