[ejabberd] Migration from 2.0.x to 2.1.x

Jonathan Siegle jsiegle at psu.edu
Wed Aug 18 18:02:55 MSD 2010


On Aug 17, 2010, at 3:28 PM, Jonathan Siegle wrote:
> 
> 
> 1) Backup production db
> 2) Restore production to a test db
> 3) Try to identify what tables have changed and what the alter table commands should look like. 
> 
> Is there a resource for #3? 


Ok sdiff is your friend.

For alterations:
alter table users add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
alter table rosterusers add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

alter table spool add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP


alter table vcard add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

alter table privacy_list add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

alter table private_storage add column created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

For creates:
CREATE TABLE pubsub_node (
  host text,
  node text,
  parent text,
  type text,
  nodeid bigint auto_increment primary key
) CHARACTER SET utf8;
CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120));
CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120));

CREATE TABLE pubsub_node_option (
  nodeid bigint,
  name text,
  val text
) CHARACTER SET utf8;
CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid);
ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;

CREATE TABLE pubsub_node_owner (
  nodeid bigint,
  owner text
) CHARACTER SET utf8;
CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid);
ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;

CREATE TABLE pubsub_state (
  nodeid bigint,
  jid text,
  affiliation character(1),
  subscriptions text,
  stateid bigint auto_increment primary key
) CHARACTER SET utf8;
CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60));
CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state(nodeid, jid(60));
ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;

CREATE TABLE pubsub_item (
  nodeid bigint,
  itemid text,
  publisher text,
  creation text,
  modification text,
  payload text
) CHARACTER SET utf8;
CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36));
CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36));
ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;

CREATE TABLE pubsub_subscription_opt (
  subid text,
  opt_name varchar(32),
  opt_value text
);
CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32));


-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5937 bytes
Desc: not available
URL: <http://lists.jabber.ru/pipermail/ejabberd/attachments/20100818/d69fdf41/attachment.bin>


More information about the ejabberd mailing list