Produits Entr'ouvert » w.c.s.

    cur.execute('DROP TABLE wcs_all_forms CASCADE')

    cur.execute('DROP TABLE wcs_all_forms CASCADE')

    cur.execute('DROP TABLE wcs_all_forms CASCADE')

    cur.execute('TRUNCATE wcs_all_forms;')

    cur.execute('DROP TABLE wcs_all_forms CASCADE')

    cur.execute('DROP TABLE wcs_all_forms CASCADE')

def get_formdef_trigger_function_name(formdef):

    assert formdef.id is not None

    return '%s_%s_trigger_fn' % (formdef.data_sql_prefix, formdef.id)

def get_formdef_trigger_name(formdef):

    assert formdef.id is not None

    return '%s_%s_trigger' % (formdef.data_sql_prefix, formdef.id)

        cur.execute('DELETE FROM %s' % table_name)  # Force trigger execution

    cur.execute("SELECT relkind FROM pg_class WHERE relname = 'wcs_all_forms'")

    row = cur.fetchone()

    # only do the delete if wcs_all_forms is a table and not still a view

    if row is not None and row[0] == 'r':

        cur.execute("DELETE FROM wcs_all_forms WHERE object_type = 'formdata'")

        cur.execute('DELETE FROM %s' % table_name)  # Force trigger execution

    # recreate the trigger function, just so it's uptodate

    category_value = formdef.category_id

    geoloc_base_x_query = "NULL"

    geoloc_base_y_query = "NULL"

    if formdef.geolocations and 'base' in formdef.geolocations:

        # default geolocation is in the 'base' key; we have to unstructure the

        # field is the POINT type of postgresql cannot be used directly as it

        # doesn't have an equality operator.

        geoloc_base_x_query = "NEW.geoloc_base[0]"

        geoloc_base_y_query = "NEW.geoloc_base[1]"

    if formdef.category_id is None:

        category_value = "NULL"

    criticality_levels = len(formdef.workflow.criticality_levels or [0])

    endpoint_status = formdef.workflow.get_endpoint_status()

    endpoint_status_filter = ", ".join(["'wf-%s'" % x.id for x in endpoint_status])

    if endpoint_status_filter == "":

        # not the prettiest in town, but will do fine for now.

        endpoint_status_filter = "'xxxx'"

    object_type = formdef.data_sql_prefix   # shortcut.

    formed_name_quotedstring = psycopg2.extensions.QuotedString(formdef.name)

    formed_name_quotedstring.encoding = 'utf8'

    formdef_name = formed_name_quotedstring.getquoted().decode()

    cur.execute('''

CREATE OR REPLACE FUNCTION {trg_fn_name}()

RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

    -- TODO : sync back from users change !

    IF TG_OP = 'DELETE' THEN

        DELETE FROM wcs_all_forms WHERE formdef_id = {formdef_id} AND id = OLD.id AND object_type = '{object_type}';

        RETURN OLD;

    ELSEIF TG_OP = 'INSERT' THEN

        INSERT INTO wcs_all_forms VALUES (

            {category_id},

            '{object_type}',

            {formdef_id},

            NEW.id,

            NEW.user_id,

            NEW.receipt_time,

            NEW.status,

            NEW.id_display,

            NEW.submission_agent_id,

            NEW.submission_channel,

            NEW.backoffice_submission,

            NEW.last_update_time,

            NEW.digests,

            NEW.user_label,

            NEW.concerned_roles_array,

            NEW.actions_roles_array,

            NEW.fts,

            NEW.status IN ({endpoint_status}),

            {formdef_name},

            (SELECT name FROM users WHERE users.id = CAST(NEW.user_id AS INTEGER)),

            NEW.criticality_level - {criticality_levels},

            {geoloc_base_x},

            {geoloc_base_y},

            NEW.anonymised);

        RETURN NEW;

    ELSE

        UPDATE wcs_all_forms SET

                user_id = NEW.user_id,

                receipt_time = NEW.receipt_time,

                status = NEW.status,

                id_display = NEW.id_display,

                submission_agent_id = NEW.submission_agent_id,

                submission_channel = NEW.submission_channel,

                backoffice_submission = NEW.backoffice_submission,

                last_update_time = NEW.last_update_time,

                digests = NEW.digests,

                user_label = NEW.user_label,

                concerned_roles_array = NEW.concerned_roles_array,

                actions_roles_array = NEW.actions_roles_array,

                fts = NEW.fts,

                is_at_endpoint = NEW.status IN ({endpoint_status}),

                formdef_name = {formdef_name},

                user_name = (SELECT name FROM users WHERE users.id = CAST(NEW.user_id AS INTEGER)),

                criticality_level = NEW.criticality_level - {criticality_levels},

                geoloc_base_x = {geoloc_base_x},

                geoloc_base_y = {geoloc_base_y},

                anonymised = NEW.anonymised

            WHERE formdef_id = {formdef_id}  AND id = OLD.id AND object_type = '{object_type}';

        RETURN NEW;

    END IF;

END;

$$;

    '''.format(trg_fn_name = get_formdef_trigger_function_name(formdef),

               category_id = category_value,                   # always valued ? need to handle null otherwise.

               formdef_id = formdef.id,

               geoloc_base_x = geoloc_base_x_query,

               geoloc_base_y = geoloc_base_y_query,

               formdef_name = formdef_name,

               criticality_levels = criticality_levels,

               endpoint_status = endpoint_status_filter,

               object_type = object_type

        ))

    trg_name = get_formdef_trigger_name(formdef)

    cur.execute(

        '''SELECT 1 FROM pg_trigger

            WHERE tgrelid = '%s'::regclass

              AND tgname = '%s'

        ''' % (table_name, trg_name))

    if len(cur.fetchall()) == 0:

        cur.execute(

            '''CREATE TRIGGER {trg_name} AFTER INSERT OR UPDATE OR DELETE

                ON {table_name}

                FOR EACH ROW EXECUTE FUNCTION {trg_fn_name}();

                '''.format(trg_fn_name = get_formdef_trigger_function_name(formdef),

                           table_name = table_name,

                           trg_name = trg_name))

    from .formdef import FormDef

    from .carddef import CardDef

    # XXX TODO: make me dynamic, please ?

    cur.execute("""CREATE TABLE IF NOT EXISTS wcs_all_forms (

        category_id integer,

        object_type character varying NOT NULL, -- formdef or carddef

        formdef_id integer NOT NULL,

        id integer NOT NULL,

        user_id character varying,

        receipt_time timestamp without time zone,

        status character varying,

        id_display character varying,

        submission_agent_id character varying,

        submission_channel character varying,

        backoffice_submission boolean,

        last_update_time timestamp without time zone,

        digests jsonb,

        user_label character varying,

        concerned_roles_array text[],

        actions_roles_array text[],

        fts tsvector,

        is_at_endpoint boolean,

        formdef_name text,

        user_name character varying,

        criticality_level integer,

        geoloc_base_x double precision,

        geoloc_base_y double precision,

        anonymised timestamp with time zone

        , PRIMARY KEY(object_type, formdef_id, id)

    )""")

    cur.execute('''CREATE INDEX IF NOT EXISTS %s_fts ON %s USING gin(fts)''' % ("wcs_all_forms", "wcs_all_forms"))

    for attr in ('receipt_time', 'anonymised', 'user_id', 'status'):

        cur.execute('''CREATE INDEX IF NOT EXISTS %s_%s ON %s (%s)''' % ("wcs_all_forms", attr, "wcs_all_forms", attr))

    for attr in ('concerned_roles_array', 'actions_roles_array'):

        cur.execute('''CREATE INDEX IF NOT EXISTS %s_%s ON %s USING gin (%s)''' % ("wcs_all_forms", attr, "wcs_all_forms", attr))

    # Purge of any dead data

    valid_data = {FormDef.data_sql_prefix: set(), CardDef.data_sql_prefix: set()}

    for formdef in FormDef.select(ignore_migration=True):

        valid_data[formdef.data_sql_prefix].add(formdef.id)

    for object_type, valid_ids in valid_data.items():

        if valid_ids:

            cur.execute("DELETE FROM wcs_all_forms WHERE object_type = '%s' AND formdef_id NOT IN (%s)" % (object_type, ", ".join(valid_ids)))

        else:

            cur.execute("DELETE FROM wcs_all_forms WHERE object_type = '%s'" % object_type)

    ###fake_formdef = FormDef()

    ###common_fields = get_view_fields(fake_formdef)

    ###common_fields.append(('concerned_roles_array', 'concerned_roles_array'))

    ###common_fields.append(('actions_roles_array', 'actions_roles_array'))

    ###common_fields.append(('fts', 'fts'))

    ###common_fields.append(('is_at_endpoint', 'is_at_endpoint'))

    ###common_fields.append(('formdef_name', 'formdef_name'))

    ###common_fields.append(('user_name', 'user_name'))

    ###common_fields.append(('criticality_level', 'criticality_level'))

    ###common_fields.append(('geoloc_base_x', 'geoloc_base_x'))

    ###common_fields.append(('geoloc_base_y', 'geoloc_base_y'))

    ###common_fields.append(('anonymised', 'anonymised'))

            cur.execute('''DELETE FROM %s''' % cls._table_name) # force trigger execution first.

SQL_LEVEL = (57, 'switch wcs_all_forms to a trigger-maintained table')

        # fake code to help in testing the error code path.

    if sql_level < 57:

        cur.execute("SELECT relkind FROM pg_class WHERE relname = 'wcs_all_forms';")

        rows = cur.fetchall()

        if len(rows) != 0:

            if rows[0][0] == 'v':

                # force wcs_all_forms table creation

                cur.execute('DROP VIEW IF EXISTS wcs_all_forms CASCADE;')

                do_global_views()

            else:

                assert(rows[0][0] == 'r')

        else:

            do_global_views()

        # assert there is no row, ie we are doing a clean migration (special case with unit tests likely) ?

        #cur.execute("SELECT COUNT(*) FROM wcs_all_forms;")

        #assert(cur.fetchone()[0] == 0)

        # now copy all data into the table

        from wcs.carddef import CardDef

        from wcs.formdef import FormDef

        for formdef in FormDef.select() + CardDef.select():

            category_value = formdef.category_id

            if formdef.category_id is None:

                category_value = "NULL"

            geoloc_base_x_query = "NULL"

            geoloc_base_y_query = "NULL"

            if formdef.geolocations and 'base' in formdef.geolocations:

                # default geolocation is in the 'base' key; we have to unstructure the

                # field is the POINT type of postgresql cannot be used directly as it

                # doesn't have an equality operator.

                geoloc_base_x_query = "geoloc_base[0]"

                geoloc_base_y_query = "geoloc_base[1]"

            criticality_levels = len(formdef.workflow.criticality_levels or [0])

            endpoint_status = formdef.workflow.get_endpoint_status()

            endpoint_status_filter = ", ".join(["'wf-%s'" % x.id for x in endpoint_status])

            if endpoint_status_filter == "":

                # not the prettiest in town, but will do fine for now.

                endpoint_status_filter = "'xxxx'"

            object_type = formdef.data_sql_prefix   # shortcut.

            formed_name_quotedstring = psycopg2.extensions.QuotedString(formdef.name)

            formed_name_quotedstring.encoding = 'utf8'

            formdef_name = formed_name_quotedstring.getquoted().decode()

            cur.execute("""

                INSERT INTO wcs_all_forms

                SELECT

                    {category_id},

                    '{object_type}',

                    {formdef_id},

                    id,

                    user_id,

                    receipt_time,

                    status,

                    id_display,

                    submission_agent_id,

                    submission_channel,

                    backoffice_submission,

                    last_update_time,

                    digests,

                    user_label,

                    concerned_roles_array,

                    actions_roles_array,

                    fts,

                    status IN ({endpoint_status}),

                    {formdef_name},

                    (SELECT name FROM users WHERE users.id = CAST(user_id AS INTEGER)),

                    criticality_level - {criticality_levels},

                    {geoloc_base_x},

                    {geoloc_base_y},

                    anonymised

                FROM {table_name}

                ON CONFLICT DO NOTHING;

                    """.format(

                        table_name = get_formdef_table_name(formdef),

                        category_id = category_value,                   # always valued ? need to handle null otherwise.

                        formdef_id = formdef.id,

                        geoloc_base_x = geoloc_base_x_query,

                        geoloc_base_y = geoloc_base_y_query,

                        formdef_name = formdef_name,

                        criticality_levels = criticality_levels,

                        endpoint_status = endpoint_status_filter,

                        object_type = object_type

                    ))