Produits Entr'ouvert » w.c.s.

    cur.execute('TRUNCATE wcs_all_forms;')

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('TRUNCATE wcs_all_forms;')

    # 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'"

    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;

        RETURN OLD;

    ELSEIF TG_OP = 'INSERT' THEN

        INSERT INTO wcs_all_forms VALUES (

            {category_id},

            {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;

        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 = psycopg2.extensions.QuotedString(formdef.name).getquoted().decode(),

               criticality_levels = criticality_levels,

               endpoint_status = endpoint_status_filter

        ))

    cur.execute('''CREATE OR REPLACE 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 = get_formdef_trigger_name(formdef)))

    # XXX TODO: make me dynamic, please ?

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

        category_id integer,

        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(formdef_id, id)

    )""")

    ###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.

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