Seitenvergleich

...

To implement a schema level update, two steps must be taken:

The new numeric schema level must be specified in the plugin’s descriptor file.
The actual schema update must be implemented.

Define new schema level in plugin descriptor

...

Codeblock

language	json

{
    "$schema": "http://git.intra.pinuts.de/cmsbs/cmsbs/raw/master/doc/json-schemas/plugin.desc.json",
    "__encoding": "Pinuts File Encoding: UTF-8",
    "name": "de.acme.tutorial",
    ...,
    "dbSchema": 1
}

Implement schema update

Create a new ES6 file in your plugin’s CSE directory. If your plugin’s name is de.acme.projectxtutorial then cmsbs-conf/cse/plugins/de.acme.projectxtutorial/callback/DbSchemaUpdate.es6mjs would be a good choice:

Codeblock

language	js

/// <reference path="../../../.vscode.js"/>

const SQL = UM.config.sql;


export default class DbSchemaMigration extends PluginSchemaMigration {
    schemaUpdate_0_1(scr) {
        // Create column for primary attribute "new_attribute":
        this.primaryAddColumn(scr, UM.config.attributes.getAttribute('new_attribute'));

   }      schemaUpdate_1_2() {
        // Create column for new table attribute's column// Alter primary attribute's column according to the current settings from *.attributes file:
        this.tableAddColumnprimaryModifyColumn(scr, UM.config.getAttribute("table'altered_attribute->new_column"'));

   }      schemaUpdate_2_3(scr) {
    // Create column for new table attribute's column:
   let sql = "CREATE VIEW " + SQL.qualifyTable('v_groupmembers') + " AS SELECT "
            + " grp.oid user_oid, 1 pos, user.sid c_userUid, membership.c_joinedAt, membership.c_isManager, membership.c_fromSegment, "
            + " user.firstname c_firstname, user.lastname c_lastname "
            + " FROM  " + SQL.users + " grp, " + SQL.qualifyTable(this.tableAddColumn(scr, UM.config.getAttribute('groupMembershipstable_attribute->new_column').sqlName);
+
" membership, " + SQL.users + " user" // Alter table attribute's column according to the current settings   + " WHERE membership.c_groupUid=grp.sid AND user.oid=membership.user_oid";from *.attributes file:
         scr.add(new SqlQuery(sql));
    }
    
    schemaUpdate_3_4(scr) {
        this.tableModifyColumn(scr, UM.config.getAttribute("groupNews->link"'table_attribute->altered_column'));
    }    
  schemaUpdate_23_24(scr) {     // Issue SQL statement varto SQLcreate = UM.config.sql;
an INDEX:
        this.primaryModifyColumnprimaryAddIndex(scr, UM.config.getAttribute("nid"'new_attribute'));

        // Index on users.p_nid
        let createIndex1 = new SqlQuery("CREATE INDEX idx_users_nid ON " + SQL.qualifyTable('users') + " (P_NID)");
        scr.add(createIndex1);

        // HBSEDS-871 Fehlende MySQL-Indixe anlegenIssue SQL statement to mass-update some user entries:
        letconst createIndex2update1 = new SqlQuery("CREATE INDEX idx_users_address ON " + `UPDATE ${SQL.qualifyTable('users')} + " (address)");
        createIndex2.expectException = true;
        scr.add(createIndex2);

        let createIndex3 = new SqlQuery("CREATE INDEX idx_users_city ON " + SQL.qualifyTable('users') + " (city)");
        createIndex3.expectException = true;
        scr.add(createIndex3SET p_entrytype='user' WHERE p_entrytype IS NULL`);
        scr.add(update1);
        
let createIndex4 = new SqlQuery("CREATE INDEX idx_users_company ON "// +Issue SQL.qualifyTable('users') + " (company)");
   statement to mass-update some user entries:
     createIndex4.expectException = true;         scr.add(createIndex4);

        let createIndex5 const update2 = new SqlQuery("CREATE INDEX idx_users_country ON " + `UPDATE ${SQL.qualifyTable('users')} + " (country)");
        createIndex5.expectException = true;
        scr.add(createIndex5);

        let createIndex6 = new SqlQuery("CREATE INDEX idx_users_sid ON " + SQL.qualifyTable('users') + " (sid)");
        createIndex6.expectException = true;
        scr.add(createIndex6);

        let createIndex7 = new SqlQuery("CREATE INDEX idx_users_zipcode ON " + SQL.qualifyTable('users') + " (zipcode)");
        createIndex7.expectException = trueSET p_entrytype='customer' WHERE p_entrytype='user'`);
        scr.add(createIndex7update2);
    }

    schemaUpdate_24_25(scr) {
        var SQL = UM.config.sql;

        // HBSEDS-871 Fehlende MySQL-Indixe anlegen
        let columSpecs = " (keycol, valcol)";
        if (SQL.dbTypeName == 'mysql') {
            columSpecs = " (keycol, valcol(191))";
        }
        let createIndex2 = new SqlQuery("CREATE INDEX idx_users_attr_keycol_valcol ON " + SQL.qualifyTable('users_attr') + columSpecs);
        createIndex2.expectException = true;
        scr.add(createIndex2);
    }

    schemaUpdate_25_26(scr) {
        this.primaryAddColumn(scr, UM.config.getAttribute("active"));

        let update1 = new SqlQuery("UPDATE " + UM.config.sql.qualifyTable('users') + " SET p_active='1' WHERE p_entrytype='user'");
        scr.add(update1);
    }
}

// Export
de.pinuts.hbs.eds.DbSchemaMigration = DbSchemaMigration;
}

// Don't forget to export the class to the respective namespace:
de.acme.tutorial.DbSchemaMigration = DbSchemaMigration;

The method schemaUpdate_0_1 will be executed upon UM startup whenever the recorded schema level for the plugin de.acme.projectx is below “1”.

To implement a second schema update, just increase the dbSchema counter in your plugin descriptor file and implement the schemaUpdate_1_2 method.

There are several helper methods available to accomplish common tasks like adding or altering columns or executing custom SQL statements:

Hinweis

Please keep in mind that a database schema update cannot be undone automatically!

Have a fresh backup at hand to be able to rollback the database if needed.

Versionen im Vergleich

Alte Version 4

Neue Version Aktuell

Schlüssel

Define new schema level in plugin descriptor

Implement schema update