misc/migration/3.18.0_Any.py
author Julien Cristau <julien.cristau@logilab.fr>
Thu, 14 Nov 2013 12:26:29 +0100
changeset 9364 73bd5012336f
parent 9361 0542a85fe667
child 9369 176c1edf51b0
permissions -rw-r--r--
Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975) Work on sets instead of arrays, so if the same value appears twice it's not repeated in the concatenated output. This patch handles the postgresql and sqlite backends, mysql is left alone at this point (seems doable, but I don't have time or motivation to fix and test it).
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
9299
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     1
sync_schema_props_perms('defaultval')
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     2
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     3
def convert_defaultval(cwattr, default):
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     4
    from decimal import Decimal
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     5
    import yams
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     6
    from cubicweb import Binary
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     7
    if default is None:
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     8
        return
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
     9
    atype = cwattr.to_entity[0].name
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    10
    if atype == 'Boolean':
9360
eda5071e30a1 [migration] fix handling of default value for boolean attributes
Julien Cristau <julien.cristau@logilab.fr>
parents: 9300
diff changeset
    11
        # boolean attributes with default=False were stored as ''
eda5071e30a1 [migration] fix handling of default value for boolean attributes
Julien Cristau <julien.cristau@logilab.fr>
parents: 9300
diff changeset
    12
        assert default in ('True', 'False', ''), repr(default)
9299
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    13
        default = default == 'True'
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    14
    elif atype in ('Int', 'BigInt'):
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    15
        default = int(default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    16
    elif atype == 'Float':
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    17
        default = float(default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    18
    elif atype == 'Decimal':
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    19
        default = Decimal(default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    20
    elif atype in ('Date', 'Datetime', 'TZDatetime', 'Time'):
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    21
        try:
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    22
            # handle NOW and TODAY, keep them stored as strings
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    23
            yams.KEYWORD_MAP[atype][default.upper()]
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    24
            default = default.upper()
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    25
        except KeyError:
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    26
            # otherwise get an actual date or datetime
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    27
            default = yams.DATE_FACTORY_MAP[atype](default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    28
    else:
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    29
        assert atype == 'String', atype
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    30
        default = unicode(default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    31
    return Binary.zpickle(default)
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    32
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    33
dbh = repo.system_source.dbhelper
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    34
driver = config.sources()['system']['db-driver']
9300
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    35
9299
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    36
if driver == 'postgres' or driver.startswith('sqlserver'):
9300
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    37
9299
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    38
    sql('ALTER TABLE cw_cwattribute ADD new_defaultval %s' % dbh.TYPE_MAPPING['Bytes'])
9300
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    39
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    40
    for cwattr in rql('CWAttribute X').entities():
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    41
        olddefault = cwattr.defaultval
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    42
        if olddefault is not None:
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    43
            req = "UPDATE cw_cwattribute SET new_defaultval = %(val)s WHERE cw_eid = %(eid)s"
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    44
            args = {'val': dbh.binary_value(convert_defaultval(cwattr, olddefault).getvalue()), 'eid': cwattr.eid}
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    45
            sql(req, args, ask_confirm=False)
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    46
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    47
    sql('ALTER TABLE cw_cwattribute DROP COLUMN cw_defaultval')
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    48
    if config.sources()['system']['db-driver'] == 'postgres':
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    49
        sql('ALTER TABLE cw_cwattribute RENAME COLUMN new_defaultval TO cw_defaultval')
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    50
    else:
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    51
        sql("sp_rename 'cw_cwattribute.new_defaultval', 'cw_defaultval', 'COLUMN'")
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    52
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    53
elif driver == 'sqlite':
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    54
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    55
    import re
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    56
    create = sql("SELECT sql FROM sqlite_master WHERE name = 'cw_CWAttribute'")[0][0]
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    57
    create = re.sub('cw_defaultval varchar[^,]*,', 'cw_defaultval bytea,', create, re.I)
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    58
    create = re.sub('cw_CWAttribute', 'tmp_cw_CWAttribute', create, re.I)
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    59
    sql(create)
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    60
    sql("INSERT INTO tmp_cw_CWAttribute SELECT * FROM cw_CWAttribute")
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    61
    for cwattr in rql('CWAttribute X').entities():
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    62
        olddefault = cwattr.defaultval
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    63
        if olddefault is None:
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    64
            continue
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    65
        req = "UPDATE tmp_cw_CWAttribute SET cw_defaultval = %(val)s WHERE cw_eid = %(eid)s"
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    66
        args = {'val': dbh.binary_value(convert_defaultval(cwattr, olddefault).getvalue()),
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    67
                'eid': cwattr.eid}
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    68
        sql(req, args, ask_confirm=False)
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    69
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    70
    sql('DROP TABLE cw_CWAttribute')
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    71
    sql('ALTER TABLE tmp_cw_CWAttribute RENAME TO cw_CWAttribute')
5f10cd13224d defaultval migration for sqlite
Julien Cristau <julien.cristau@logilab.fr>
parents: 9299
diff changeset
    72
9299
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    73
else:
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    74
    assert False, 'upgrade not supported on this database backend'
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    75
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    76
# Set object type to "Bytes" for CWAttribute's "defaultval" attribute
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    77
rql('SET X to_entity B WHERE X is CWAttribute, X from_entity Y, Y name "CWAttribute", '
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    78
    'X relation_type Z, Z name "defaultval", B name "Bytes"')
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    79
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    80
from yams import buildobjs as ybo
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    81
schema.add_relation_def(ybo.RelationDefinition('CWAttribute', 'defaultval', 'Bytes'))
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    82
schema.del_relation_def('CWAttribute', 'defaultval', 'String')
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    83
c5eed908117d [schema] store default attribute values in a Bytes field, allowing python objects as default values
Aurélien Campeas <aurelien.campeas@logilab.fr>
parents:
diff changeset
    84
commit()
9361
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    85
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    86
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    87
for rschema in schema.relations():
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    88
    if rschema.symmetric:
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    89
        with session.allow_all_hooks_but('activeintegrity'):
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    90
            rql('SET X %(r)s Y WHERE Y %(r)s X, NOT X %(r)s Y' % {'r': rschema.type})
0542a85fe667 symmetric relations: replace bogus rql2sql translation by a hook
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9360
diff changeset
    91
    commit()