示例#1
0
def create_relation_vacancies():
    table = u'vacancies'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(
        conn.execute,
        u"UPDATE vacancies SET vacancy_name_tsvector = (to_tsvector('international',"
        u"vacancy_name));")

    yield momoko.Op(
        conn.execute, u"UPDATE vacancies SET vacancy_description_tsvector"
        u" = (to_tsvector('international', description));")

    yield momoko.Op(
        conn.execute, u"CREATE INDEX vacancy_name_idx ON vacancies "
        u"USING GIN(vacancy_name_tsvector);")

    yield momoko.Op(
        conn.execute, u"CREATE INDEX vacancy_description_idx ON vacancies "
        u"USING GIN(vacancy_description_tsvector);")

    yield momoko.Op(
        conn.execute,
        u"""DROP FUNCTION IF EXISTS vacancy_vector_update() CASCADE;""")

    yield momoko.Op(
        conn.execute,
        u"""DROP TRIGGER IF EXISTS tsvectorupdate on vacancies CASCADE;""")

    yield momoko.Op(
        conn.execute,
        u"""CREATE FUNCTION vacancy_vector_update() RETURNS TRIGGER AS $$
    BEGIN
        IF TG_OP = 'INSERT' THEN
            new.vacancy_name_tsvector = to_tsvector('international', COALESCE(NEW.vacancy_name, ''));
            new.vacancy_description_tsvector = to_tsvector('international', COALESCE(NEW.description, ''));
        END IF;

        IF TG_OP = 'UPDATE' THEN

            IF NEW.vacancy_name <> OLD.vacancy_name THEN
                new.vacancy_name_tsvector = to_tsvector('international', COALESCE(NEW.vacancy_name, ''));
            END IF;

            IF NEW.description <> OLD.description THEN
                new.vacancy_description_tsvector = to_tsvector('international', COALESCE(NEW.description, ''));
            END IF;

        END IF;
        RETURN NEW;
    END
    $$ LANGUAGE 'plpgsql';""")

    yield momoko.Op(
        conn.execute, u"CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE "
        u"ON vacancies FOR EACH ROW EXECUTE PROCEDURE vacancy_vector_update();"
    )
示例#2
0
def create_config():
    conn = PSQLClient.get_client()
    try:
        yield momoko.Op(
            conn.execute, u"""
        CREATE TEXT SEARCH DICTIONARY russian_ispell (
        TEMPLATE = ispell,
        DictFile = russian,
        AffFile = russian,
        StopWords = russian
        );

        CREATE TEXT SEARCH DICTIONARY english_ispell (
        TEMPLATE = ispell,
        DictFile = english,
        AffFile = english,
        StopWords = english
        );

        CREATE TEXT SEARCH CONFIGURATION international ( COPY = russian );

        ALTER TEXT SEARCH CONFIGURATION international ALTER MAPPING FOR hword, hword_part, word WITH russian_ispell, russian_stem;
        ALTER TEXT SEARCH CONFIGURATION international ALTER MAPPING FOR asciihword, asciiword, hword_asciipart WITH english_ispell, english_stem;
        """)

        print 'configuration for text search has been created'
    except (psycopg2.Warning, psycopg2.Error) as error:
        raise Exception(str(error))
示例#3
0
def create_relation_projects():
    table = u'projects'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    # INDEXES:
    yield momoko.Op(
        conn.execute,
        u"UPDATE projects SET title_tsvector = (to_tsvector('international', title));"
    )

    yield momoko.Op(
        conn.execute, u"UPDATE projects SET description_short_tsvector"
        u" = (to_tsvector('international', description_short));")

    yield momoko.Op(
        conn.execute, u"CREATE INDEX title_idx ON projects "
        u"USING GIN (title_tsvector);")

    yield momoko.Op(
        conn.execute, u"CREATE INDEX description_short_idx ON projects "
        u"USING GIN (description_short_tsvector);")

    yield momoko.Op(conn.execute, u"CREATE INDEX tags_idx ON projects(tags);")

    yield momoko.Op(
        conn.execute,
        u"""DROP FUNCTION IF EXISTS project_vector_update() CASCADE;""")

    yield momoko.Op(
        conn.execute,
        u"""DROP TRIGGER IF EXISTS tsvectorupdate on projects CASCADE;""")

    yield momoko.Op(
        conn.execute,
        u"""CREATE FUNCTION project_vector_update() RETURNS TRIGGER AS $$
        BEGIN
            IF TG_OP = 'INSERT' THEN
                new.title_tsvector = to_tsvector('international', COALESCE(NEW.title, ''));
                new.description_short_tsvector = to_tsvector('international', COALESCE(NEW.description_short, ''));

            END IF;
            IF TG_OP = 'UPDATE' THEN
                IF NEW.title <> OLD.title THEN
                    new.title_tsvector = to_tsvector('international', COALESCE(NEW.title, ''));
                END IF;
                IF NEW.description_short <> OLD.description_short THEN
                    new.description_short_tsvector = to_tsvector('international', COALESCE(NEW.description_short, ''));
                END IF;

            END IF;
            RETURN NEW;
        END
        $$ LANGUAGE 'plpgsql';""")

    yield momoko.Op(
        conn.execute, u"CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE "
        u"ON projects FOR EACH ROW EXECUTE PROCEDURE project_vector_update();")
示例#4
0
def create_config():
    conn = PSQLClient.get_client()
    try:
        yield momoko.Op(conn.execute, u"""
        CREATE TEXT SEARCH DICTIONARY russian_ispell (
        TEMPLATE = ispell,
        DictFile = russian,
        AffFile = russian,
        StopWords = russian
        );

        CREATE TEXT SEARCH DICTIONARY english_ispell (
        TEMPLATE = ispell,
        DictFile = english,
        AffFile = english,
        StopWords = english
        );

        CREATE TEXT SEARCH CONFIGURATION international ( COPY = russian );

        ALTER TEXT SEARCH CONFIGURATION international ALTER MAPPING FOR hword, hword_part, word WITH russian_ispell, russian_stem;
        ALTER TEXT SEARCH CONFIGURATION international ALTER MAPPING FOR asciihword, asciiword, hword_asciipart WITH english_ispell, english_stem;
        """)

        print 'configuration for text search has been created'
    except (psycopg2.Warning, psycopg2.Error) as error:
        raise Exception(str(error))
示例#5
0
def create_relation_schools():
    table = u'schools'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"CREATE INDEX schools_title_idx ON schools (title);")
示例#6
0
def create_relation_countries():
    table = u'countries'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"CREATE INDEX countries_title_ru_idx ON countries (title_ru);")
    yield momoko.Op(conn.execute, u"CREATE INDEX countries_title_en_idx ON countries (title_en);")
示例#7
0
def create_relation_schools():
    table = u'schools'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute,
                    u"CREATE INDEX schools_title_idx ON schools (title);")
示例#8
0
def delete_tables():
    conn = PSQLClient.get_client()
    tables = ALL_TABLES.keys()
    for table in tables:
        try:
            print u'deleting {}'.format(table)
            yield momoko.Op(conn.execute, u'DROP TABLE %s CASCADE' % table)
        except Exception, ex:
            print ex
            continue
示例#9
0
def delete_tables():
    conn = PSQLClient.get_client()
    tables = ALL_TABLES.keys()
    for table in tables:
        try:
            print u'deleting {}'.format(table)
            yield momoko.Op(conn.execute, u'DROP TABLE %s CASCADE' % table)
        except Exception, ex:
            print ex
            continue
示例#10
0
def create_relation_cities():
    table = u'cities'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"CREATE INDEX cities_region_idx ON cities (region);")
    yield momoko.Op(conn.execute, u"CREATE INDEX cities_area_idx ON cities (area);")
    yield momoko.Op(conn.execute, u"CREATE INDEX cities_title_idx ON cities (title);")
示例#11
0
def create_relation_participants():
    table = u'participants'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"CREATE INDEX participants_first_name_idx ON participants (first_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX participants_last_name_idx ON participants (last_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX participants_middle_name_idx ON participants (middle_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX participants_role_name_idx ON participants (role_name);")
示例#12
0
def create_relation_scientists():
    table = u'scientists'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"CREATE INDEX scientists_first_name_idx ON scientists (first_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX scientists_last_name_idx ON scientists (last_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX scientists_middle_name_idx ON scientists (middle_name);")
    yield momoko.Op(conn.execute, u"CREATE INDEX scientists_interests_idx ON scientists USING GIN(interests);")
示例#13
0
def create_relation_countries():
    table = u'countries'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX countries_title_ru_idx ON countries (title_ru);")
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX countries_title_en_idx ON countries (title_en);")
示例#14
0
def create_relation_cities():
    table = u'cities'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute,
                    u"CREATE INDEX cities_region_idx ON cities (region);")
    yield momoko.Op(conn.execute,
                    u"CREATE INDEX cities_area_idx ON cities (area);")
    yield momoko.Op(conn.execute,
                    u"CREATE INDEX cities_title_idx ON cities (title);")
示例#15
0
def create_relation_vacancies():
    table = u'vacancies'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(conn.execute, u"UPDATE vacancies SET vacancy_name_tsvector = (to_tsvector('international',"
                                  u"vacancy_name));")

    yield momoko.Op(conn.execute, u"UPDATE vacancies SET vacancy_description_tsvector"
                                  u" = (to_tsvector('international', description));")

    yield momoko.Op(conn.execute, u"CREATE INDEX vacancy_name_idx ON vacancies "
                                  u"USING GIN(vacancy_name_tsvector);")

    yield momoko.Op(conn.execute, u"CREATE INDEX vacancy_description_idx ON vacancies "
                                  u"USING GIN(vacancy_description_tsvector);")

    yield momoko.Op(conn.execute, u"""DROP FUNCTION IF EXISTS vacancy_vector_update() CASCADE;""")

    yield momoko.Op(conn.execute, u"""DROP TRIGGER IF EXISTS tsvectorupdate on vacancies CASCADE;""")

    yield momoko.Op(conn.execute,

    u"""CREATE FUNCTION vacancy_vector_update() RETURNS TRIGGER AS $$
    BEGIN
        IF TG_OP = 'INSERT' THEN
            new.vacancy_name_tsvector = to_tsvector('international', COALESCE(NEW.vacancy_name, ''));
            new.vacancy_description_tsvector = to_tsvector('international', COALESCE(NEW.description, ''));
        END IF;

        IF TG_OP = 'UPDATE' THEN

            IF NEW.vacancy_name <> OLD.vacancy_name THEN
                new.vacancy_name_tsvector = to_tsvector('international', COALESCE(NEW.vacancy_name, ''));
            END IF;

            IF NEW.description <> OLD.description THEN
                new.vacancy_description_tsvector = to_tsvector('international', COALESCE(NEW.description, ''));
            END IF;

        END IF;
        RETURN NEW;
    END
    $$ LANGUAGE 'plpgsql';""")

    yield momoko.Op(conn.execute, u"CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE "
                                  u"ON vacancies FOR EACH ROW EXECUTE PROCEDURE vacancy_vector_update();")
示例#16
0
def create_relation_projects():
    table = u'projects'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    # INDEXES:
    yield momoko.Op(conn.execute, u"UPDATE projects SET title_tsvector = (to_tsvector('international', title));")

    yield momoko.Op(conn.execute, u"UPDATE projects SET description_short_tsvector"
                                  u" = (to_tsvector('international', description_short));")

    yield momoko.Op(conn.execute, u"CREATE INDEX title_idx ON projects "
                                  u"USING GIN (title_tsvector);")

    yield momoko.Op(conn.execute, u"CREATE INDEX description_short_idx ON projects "
                                  u"USING GIN (description_short_tsvector);")

    yield momoko.Op(conn.execute, u"CREATE INDEX tags_idx ON projects(tags);")

    yield momoko.Op(conn.execute, u"""DROP FUNCTION IF EXISTS project_vector_update() CASCADE;""")

    yield momoko.Op(conn.execute, u"""DROP TRIGGER IF EXISTS tsvectorupdate on projects CASCADE;""")

    yield momoko.Op(conn.execute,
    u"""CREATE FUNCTION project_vector_update() RETURNS TRIGGER AS $$
        BEGIN
            IF TG_OP = 'INSERT' THEN
                new.title_tsvector = to_tsvector('international', COALESCE(NEW.title, ''));
                new.description_short_tsvector = to_tsvector('international', COALESCE(NEW.description_short, ''));

            END IF;
            IF TG_OP = 'UPDATE' THEN
                IF NEW.title <> OLD.title THEN
                    new.title_tsvector = to_tsvector('international', COALESCE(NEW.title, ''));
                END IF;
                IF NEW.description_short <> OLD.description_short THEN
                    new.description_short_tsvector = to_tsvector('international', COALESCE(NEW.description_short, ''));
                END IF;

            END IF;
            RETURN NEW;
        END
        $$ LANGUAGE 'plpgsql';""")

    yield momoko.Op(conn.execute, u"CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE "
                                  u"ON projects FOR EACH ROW EXECUTE PROCEDURE project_vector_update();")
示例#17
0
def create_relation_scientists():
    table = u'scientists'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX scientists_first_name_idx ON scientists (first_name);")
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX scientists_last_name_idx ON scientists (last_name);")
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX scientists_middle_name_idx ON scientists (middle_name);"
    )
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX scientists_interests_idx ON scientists USING GIN(interests);"
    )
示例#18
0
def create_relation_participants():
    table = u'participants'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)

    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX participants_first_name_idx ON participants (first_name);"
    )
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX participants_last_name_idx ON participants (last_name);"
    )
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX participants_middle_name_idx ON participants (middle_name);"
    )
    yield momoko.Op(
        conn.execute,
        u"CREATE INDEX participants_role_name_idx ON participants (role_name);"
    )
示例#19
0
 def call(self, *args, **kwargs):
     from db.connections import PSQLClient
     conn = PSQLClient.get_client()
     return function(self, conn, *args, **kwargs)
示例#20
0
def create_relation_main_cities():
    table = u'main_cities'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)
示例#21
0
 def call(self, *args, **kwargs):
     from db.connections import PSQLClient
     conn = PSQLClient.get_client()
     return function(self, conn, *args, **kwargs)
示例#22
0
def create_relation_main_cities():
    table = u'main_cities'
    print u'creating {} relation'.format(table)
    conn = PSQLClient.get_client()
    query = prepare_creation(table)
    yield momoko.Op(conn.execute, query)