Produits Entr'ouvert » OLAP / Business Intelligence pour Publik

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

        ('category', 'ref'),

        ('formdef', 'ref'),

def test_requests_exception(wcs, tmpdir, olap_cmd, caplog):

def test_requests_not_ok(wcs, tmpdir, olap_cmd, caplog):

def test_requests_not_json(wcs, tmpdir, olap_cmd, caplog):

def test_create_reference_column(postgres_db, olap_cmd):

    olap_cmd()

    conn = postgres_db.conn()

    with postgres_db.conn() as conn:

        with conn.cursor() as c:

            conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

            c.execute('ALTER TABLE olap.category DROP COLUMN ref')

            c.execute('ALTER TABLE olap.formdef DROP COLUMN ref')

    olap_cmd()

    with postgres_db.conn() as conn:

        with conn.cursor() as c:

            c.execute('SELECT * FROM olap.category')

            for line in c.fetchall():

                assert len(line) == 3

    return s.replace('-', '_').replace(' ', '_').lower()

    def update_table_sequence_number(self, name):

        self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),

        (SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': quote(name)})

            self.update_table_sequence_number(name)

    def create_referenced_table(self, name, fields, comment):

        # add primary key and reference fields

        new_fields = [['id', 'serial primary key'], ['ref', 'varchar UNIQUE']] + fields

        self.create_table(name, new_fields, comment=comment)

        if self.prev_table_exists(name):

            # verify if ref exists in table schema

            self.ex("""SELECT column_name FROM information_schema.columns

            WHERE table_name=%s AND table_schema='{schema}'""", vars=(name,))

            columns = [c[0] for c in self.cur.fetchall()]

            if 'ref' in columns:

                self.ex('INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}', ctx={'name': quote(name)})

            else:

                fields_labels =  ', '.join([f[0] for f in fields])

                self.ex('SELECT {fields} FROM {schema}.{name}', ctx={'name': quote(name),

                                                                     'fields': fields_labels})

                new_values = []

                for line in self.cur.fetchall():

                    result = (slugify(line[-1]),) + line

                    new_values.append(result)

                if new_values:

                    columns_number = len(line) + 1

                    columns_values = ', '.join(['%s' for x in xrange(columns_number)])

                    tmpl = ', '.join(['(DEFAULT, %s)' % columns_values] * len(new_values))

                    query = 'INSERT INTO {schema_temp}.{name} VALUES %s' % tmpl

                    query = self.ex(query, ctx={'name': quote(name)},

                                    vars=list(itertools.chain(*new_values)))

    def do_referenced_data(self, name, data, result_column, update_column='label'):

        to_insert = []

        for item in data:

            ref = item[0]

            self.ex(

                'SELECT ref, {column} FROM {name} WHERE ref = %s',

                ctx={'name': quote(name), 'column': quote(update_column)},

                vars=(ref,))

            if self.cur.fetchall():

                for item in self.cur.fetchall():

                    self.ex('UPDATE {name} SET {column}=%s WHERE ref=%s',

                            ctx={'name': quote(name), 'column': quote(update_column)},

                            vars=[item[1], ref])

            else:

                to_insert.append(item)

        if to_insert:

            columns_values = ', '.join(['%s' for x in xrange(len(item))])

            tmpl = ', '.join(['(DEFAULT, %s)' % columns_values] * len(data))

            query = 'INSERT INTO {name} VALUES %s' % tmpl

            self.ex(query, ctx={'name': quote(name)}, # 'column': quote(update_column)},

                    vars=list(itertools.chain(*to_insert)))

        result = {}

        self.ex('SELECT id, {column} FROM {name}', ctx={'name': quote(name),

                                                        'column': result_column})

        for _id, column  in self.cur.fetchall():

            result[column] = _id

        return result

    def do_category_table(self):

        fields = [['label', 'varchar']]

        table_name = self.ctx['category_table']

        self.create_referenced_table(table_name, fields, 'catégorie')

        categories_data = [(c.id, c.name) for c in self.categories]

        tmp_cat_map = self.do_referenced_data(table_name, categories_data, 'label')

        self.update_table_sequence_number(table_name)

        # remap categories ids to ids in the table

        return dict((c.id, tmp_cat_map[c.name]) for c in self.categories)

    def do_formdef_table(self):

        categories_mapping = self.do_category_table()

        formdef_fields = [['category_id', 'integer REFERENCES {category_table} (id)'],

                          ['label', 'varchar']

        ]

        table_name = self.ctx['form_table']

        self.create_referenced_table(table_name, formdef_fields, 'types de formulaire')

        formdefs_data = [(form.slug, categories_mapping.get(form.schema.category_id),

                          form.schema.name) for form in self.formdefs if form.count]

        self.formdefs_mapping = self.do_referenced_data(table_name, formdefs_data, 'ref')

        self.update_table_sequence_number(table_name)

        # forms

        self.do_formdef_table()

                'formdef_id': self.formdefs_mapping[self.formdef.slug],