topic Re: Upgrade 1.2.1 to 1.3 with PostGreSQL in Alfresco Archive

Upgrade 1.2.1 to 1.3 with PostGreSQL

matafagafo — Fri, 05 Oct 2007 17:55:25 GMT

Hello I have a very old Alfresco installation, and now finally I'm in the process of version upgrade, but I can't find the "Alfresco Schema conversion V1.2.1 to V1.3" for PostGreSQL database. Where can i find it ? Thanks for any help.

Re: Upgrade 1.2.1 to 1.3 with PostGreSQL

kevinr — Thu, 11 Oct 2007 09:48:23 GMT

There is not a schema conversion file. You should be able to update the alfresco.war and when the server starts it will upgrade the schema automatically. I suggest you back your DB and alf_data folder first!

Thanks,

Kevin

Re: Upgrade 1.2.1 to 1.3 with PostGreSQL

matafagafo — Fri, 12 Oct 2007 16:09:40 GMT

Thanks for your answer
I found this http://wiki.alfresco.com/wiki/Upgrading_to_1.3.0 telling about the update schema ste, but they only have this script for MySQL and Oracle.

I tried your suggestion but I receive the following error …… Any help will be very welcome.

12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table child_assoc add constraint FKC6EFFF328E50E582 foreign key (parent_node_id) references node
12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table child_assoc add constraint FKC6EFFF3274173FF4 foreign key (child_node_id) references node
12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table node_aspects add constraint FK2B91A9DE7F2C8017 foreign key (node_id) references node
12:56:39,845 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table node_assoc add constraint FK5BAEF398B69C43F3 foreign key (source_node_id) references node
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table node_assoc add constraint FK5BAEF398A8FC7769 foreign key (target_node_id) references node
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table node_properties add constraint FKC962BF907F2C8017 foreign key (node_id) references node
12:56:39,860 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,876 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table node_status add constraint FK38ECB8CF7F2C8017 foreign key (node_id) references node
12:56:39,876 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:39,876 ERROR [tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table store add constraint FK68AF8E122DBA5BA foreign key (root_node_id) references node
12:56:39,876 ERROR [tool.hbm2ddl.SchemaUpdate] ERROR: number of referencing and referenced columns for foreign key disagree
12:56:47,235 ERROR [hibernate.util.JDBCExceptionReporter] Entrada em lote 0 insert into node (protocol, identifier, uuid, type_qname, acl_id, id) values (archive, SpacesStore, bc15aba8-78db-11dc-8480-f9f5b733027c, {http://www.alfresco.org/model/system/1.0}store_root, NULL, 4) foi abortada. Chame getNextException para ver a causa.
12:56:47,235 ERROR [hibernate.util.JDBCExceptionReporter] ERROR: null value in column "guid" violates not-null constraint
12:56:47,251 ERROR [event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
   at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2048)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
   at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.flush(HibernateNodeDaoServiceImpl.java:124)
   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.flush(AlfrescoTransactionSupport.java:528)
   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:589)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:657)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:482)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:469)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:266)
   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:397)
   at org.alfresco.repo.importer.ImporterBootstrap.bootstrap(ImporterBootstrap.java:420)
   at org.alfresco.repo.importer.ImporterBootstrap.onApplicationEvent(ImporterBootstrap.java:654)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3764)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4216)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:825)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:515)
   at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1220)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.apache.commons.modeler.BaseModelMBean.invoke(BaseModelMBean.java:457)
   at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:836)
   at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:761)
   at org.apache.catalina.manager.ManagerServlet.check(ManagerServlet.java:1397)
   at org.apache.catalina.manager.HTMLManagerServlet.doPost(HTMLManagerServlet.java:214)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
   at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
   at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.BatchUpdateException: Entrada em lote 0 insert into node (protocol, identifier, uuid, type_qname, acl_id, id) values (archive, SpacesStore, bc15aba8-78db-11dc-8480-f9f5b733027c, {http://www.alfresco.org/model/system/1.0}store_root, NULL, 4) foi abortada. Chame getNextException para ver a causa.
   at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2478)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1287)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:336)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2540)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
   … 63 more

Re: Upgrade 1.2.1 to 1.3 with PostGreSQL

kevinr — Fri, 12 Oct 2007 16:43:26 GMT

Ah I see. No we don't have that script. You will need to reverse engineer the script using the existing ones as an example as appropriate for your database yourself. There may also be a kind user out there who has already done this themselves and doesn't mind adding the script to the wiki… Otherwise you last option is to contact our sales department if you purchase a license that includes support - as i imagine we could then create one for you then.

Thanks,

Kevin

Re: Upgrade 1.2.1 to 1.3 with PostGreSQL

matafagafo — Tue, 23 Oct 2007 11:00:08 GMT

Thanks for your answer, I decided to create the script myself, bellow is my script final version, use it with your own risk.
It works only in PostGreSQL 8.2 and earlier.
Enjoy.

– This work only in PostGreSQL 8.2 and more
–
– Create temporary 1.3 schema
–

CREATE TABLE t_access_control_entry
(
id bigint NOT NULL,
protocol character varying(50) NULL,
identifier character varying(100) NULL,
uuid character varying(36) NULL,
typeUri character varying(100) NULL,
typeName character varying(100) NULL,
name character varying(100) NULL,
recipient character varying(100) NULL,
acl_id bigint NULL,
permission_id bigint NULL,
authority_id character varying(100) NULL,
allowed boolean NOT NULL,
CONSTRAINT t_access_control_entry_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE t_access_control_list
(
id bigint NOT NULL,
protocol character varying(50) NOT NULL,
identifier character varying(100) NULL,
uuid character varying(36) NULL,
"inherits" boolean NOT NULL,
CONSTRAINT t_access_control_list_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE t_auth_ext_keys
(
id character varying(100) NOT NULL,
externalkey character varying(100) NOT NULL,
CONSTRAINT t_auth_ext_keys_pkey PRIMARY KEY (id, externalkey)
)
WITHOUT OIDS;

CREATE TABLE t_authority
(
recipient character varying(100) NOT NULL,
CONSTRAINT t_authority_pkey PRIMARY KEY (recipient)
)
WITHOUT OIDS;

CREATE TABLE t_child_assoc
(
id bigint NOT NULL,
parent_node_id bigint,
parent_protocol character varying(50) NULL,
parent_identifier character varying(100) NULL,
parent_uuid character varying(36) NULL,
child_node_id bigint,
child_protocol character varying(50) NULL,
child_identifier character varying(100) NULL,
child_uuid character varying(36) NULL,
type_qname character varying(255) NOT NULL,
qname character varying(255) NOT NULL,
is_primary boolean,
assoc_index integer,
CONSTRAINT t_child_assoc_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE t_node
(
id bigint NOT NULL,
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
uuid character varying(36) NOT NULL,
acl_id bigint,
type_qname character varying(255) NOT NULL,
CONSTRAINT t_node_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE t_node_aspects
(
protocol character varying(50) NULL,
identifier character varying(100) NULL,
uuid character varying(36) NULL,
node_id bigint,
qname character varying(200)
)
WITHOUT OIDS;

CREATE TABLE t_node_assoc
(
id bigint NOT NULL,
source_node_id bigint,
source_protocol character varying(50) NULL,
source_identifier character varying(100) NULL,
source_uuid character varying(36) NULL,
target_node_id bigint,
target_protocol character varying(50) NULL,
target_identifier character varying(100) NULL,
target_uuid character varying(36) NULL,
type_qname character varying(255) NOT NULL,
CONSTRAINT t_node_assoc_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE t_node_status
(
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
guid character varying(36) NOT NULL,
node_id bigint,
change_txn_id character varying(56) NOT NULL,
"deleted" boolean NOT NULL,
CONSTRAINT t_node_status_pkey PRIMARY KEY (protocol, identifier, guid)
)
WITHOUT OIDS;

CREATE TABLE t_permission
(
id bigint NOT NULL,
type_qname character varying(200) NOT NULL,
name character varying(100) NOT NULL,
CONSTRAINT t_permission_pkey PRIMARY KEY (id),
CONSTRAINT t_permission_type_qname_key UNIQUE (type_qname, name)
)
WITHOUT OIDS;

CREATE TABLE t_store
(
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
root_node_id bigint,
CONSTRAINT t_store_pkey PRIMARY KEY (protocol, identifier)
)
WITHOUT OIDS;

CREATE TABLE t_version_count
(
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
version_count bigint,
CONSTRAINT t_version_count_pkey PRIMARY KEY (protocol, identifier)
)
WITHOUT OIDS;

CREATE SEQUENCE hibernate_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 790
CACHE 1;
ALTER TABLE hibernate_sequence OWNER TO alfresco;

–
– Copy data from old tables to intermediate tables
–

insert into T_store (protocol, identifier)
select protocol, identifier from store;

insert into T_node (id, protocol, identifier, uuid, type_qname)
select nextval('hibernate_sequence') , protocol, identifier, guid, type_qname from node;

update T_store tstore set root_node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tstore.protocol and
    tnode.identifier = tstore.identifier and
    tnode.uuid =
    (select ostore.root_guid from store ostore where
      ostore.protocol = tstore.protocol and
      ostore.identifier = tstore.identifier
    )
);

insert into t_version_count (protocol, identifier, version_count)
select protocol, identifier, version_count from version_count;

insert into t_node_status (protocol, identifier, guid, change_txn_id, deleted)
select protocol, identifier, guid, change_txn_id, deleted from node_status;

update T_node_status tstatus set node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tstatus.protocol and
    tnode.identifier = tstatus.identifier and
    tnode.uuid = tstatus.guid
);

insert into T_node_aspects
(
    protocol, identifier, uuid, qname
)
select
      protocol, identifier, guid, qname
    from node_aspects;

update T_node_aspects taspects set node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = taspects.protocol and
    tnode.identifier = taspects.identifier and
    tnode.uuid = taspects.uuid
);

insert into T_child_assoc
(
    id, parent_protocol, parent_identifier, parent_uuid,
    child_protocol, child_identifier, child_uuid,
    type_qname, qname, is_primary, assoc_index
)
select
    nextval('hibernate_sequence'), parent_protocol, parent_identifier, parent_guid,
    child_protocol, child_identifier, child_guid,
    type_qname, qname, isPrimary, assoc_index
from
    child_assoc;

update T_child_assoc tassoc set parent_node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tassoc.parent_protocol and
    tnode.identifier = tassoc.parent_identifier and
    tnode.uuid = tassoc.parent_uuid
);

update T_child_assoc tassoc set child_node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tassoc.child_protocol and
    tnode.identifier = tassoc.child_identifier and
    tnode.uuid = tassoc.child_uuid
);

insert into T_node_assoc
(
    id, source_protocol, source_identifier, source_uuid,
    target_protocol, target_identifier, target_uuid,
    type_qname
)
select
    nextval('hibernate_sequence'), source_protocol, source_identifier, source_guid,
    target_protocol, target_identifier, target_guid,
    type_qname
from
    node_assoc;

update T_node_assoc tassoc set source_node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tassoc.source_protocol and
    tnode.identifier = tassoc.source_identifier and
    tnode.uuid = tassoc.source_uuid
);

update T_node_assoc tassoc set target_node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tassoc.target_protocol and
    tnode.identifier = tassoc.target_identifier and
    tnode.uuid = tassoc.target_uuid
);

insert into T_permission
   (
     id, type_qname, name
   )
   select
     nextval('hibernate_sequence'), '{' || type_uri || '}' || type_name, name
   from
     permission_ref;

insert into T_access_control_list
(
    id, protocol, identifier, uuid, inherits
)
select
      nextval('hibernate_sequence'), protocol, identifier, guid, inherits
    from node_permission;

update T_node tnode set acl_id =
(select tacl.id from T_access_control_list tacl where
    tacl.protocol = tnode.protocol and
    tacl.identifier = tnode.identifier and
    tacl.uuid = tnode.uuid
);

insert into T_auth_ext_keys
(
    id, externalKey
)
select
    id, externalKey
from
    externalkeys;

insert into T_authority
(
    recipient
)
select
    recipient
from
    recipient;

insert into T_access_control_entry
(
    id, protocol, identifier, uuid,
    typeUri, typeName, name,
    recipient,
    allowed
)
select
    nextval('hibernate_sequence'), e.protocol, e.identifier, e.guid,
    e.typeUri, e.typeName, e.name,
    e.recipient,
    e.allowed
from node_perm_entry e join t_node n on e.protocol = n.protocol and e.identifier = n.identifier and e.guid = n.uuid
;

update T_access_control_entry tentry
set
    acl_id =
    (
      select
        tacl.id
      from T_access_control_list tacl
      join T_node tnode on tacl.id = tnode.acl_id
      where
        tnode.protocol = tentry.protocol and
        tnode.identifier = tentry.identifier and
        tnode.uuid = tentry.uuid
    );

update T_access_control_entry tentry
set
    permission_id =
    (
      select
        tpermission.id
      from T_permission tpermission
      where
        tpermission.type_qname = '{' || tentry.typeUri || '}' || tentry.typeName and
        tpermission.name = tentry.name
    );

update T_access_control_entry tentry
set
    authority_id =
    (
      select
        tauthority.recipient
      from T_authority tauthority
      where
        tauthority.recipient = tentry.recipient
    );

–
– Create New schema (PostGreSQL)
–

DROP TABLE child_assoc cascade ;
DROP TABLE node_assoc cascade ;
DROP TABLE node_aspects cascade ;
DROP TABLE node cascade ;
DROP TABLE node_status cascade ;
DROP TABLE version_count cascade ;
DROP TABLE store cascade ;
DROP TABLE node_perm_entry cascade ;
DROP TABLE node_permission cascade ;
DROP TABLE permission_ref cascade ;
DROP TABLE recipient cascade ;
DROP TABLE externalKeys cascade ;

CREATE TABLE access_control_entry
(
id bigint NOT NULL,
acl_id bigint NOT NULL,
permission_id bigint NOT NULL,
authority_id character varying(100) NOT NULL,
allowed boolean NOT NULL,
CONSTRAINT access_control_entry_pkey PRIMARY KEY (id),
CONSTRAINT access_control_entry_acl_id_key UNIQUE (acl_id, permission_id, authority_id)
)
WITHOUT OIDS;
ALTER TABLE access_control_entry OWNER TO alfresco;

CREATE TABLE access_control_list
(
id bigint NOT NULL,
"inherits" boolean NOT NULL,
CONSTRAINT access_control_list_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE access_control_list OWNER TO alfresco;

CREATE TABLE auth_ext_keys
(
id character varying(100) NOT NULL,
externalkey character varying(100) NOT NULL,
CONSTRAINT auth_ext_keys_pkey PRIMARY KEY (id, externalkey)
)
WITHOUT OIDS;
ALTER TABLE auth_ext_keys OWNER TO alfresco;

CREATE TABLE authority
(
recipient character varying(100) NOT NULL,
CONSTRAINT authority_pkey PRIMARY KEY (recipient)
)
WITHOUT OIDS;
ALTER TABLE authority OWNER TO alfresco;

CREATE TABLE child_assoc
(
id bigint NOT NULL,
parent_node_id bigint,
child_node_id bigint,
type_qname character varying(255) NOT NULL,
qname character varying(255) NOT NULL,
is_primary boolean,
assoc_index integer,
CONSTRAINT child_assoc_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE child_assoc OWNER TO alfresco;

CREATE TABLE node
(
id bigint NOT NULL,
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
uuid character varying(36) NOT NULL,
type_qname character varying(255) NOT NULL,
acl_id bigint,
CONSTRAINT node_pkey PRIMARY KEY (id),
CONSTRAINT node_protocol_key UNIQUE (protocol, identifier, uuid)
)
WITHOUT OIDS;
ALTER TABLE node OWNER TO alfresco;

CREATE TABLE node_aspects
(
node_id bigint NOT NULL,
qname character varying(200)
)
WITHOUT OIDS;
ALTER TABLE node_aspects OWNER TO alfresco;

CREATE TABLE node_assoc
(
id bigint NOT NULL,
source_node_id bigint,
target_node_id bigint,
type_qname character varying(255) NOT NULL,
CONSTRAINT node_assoc_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE node_assoc OWNER TO alfresco;

CREATE TABLE node_status
(
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
guid character varying(36) NOT NULL,
node_id bigint,
change_txn_id character varying(56) NOT NULL,
CONSTRAINT node_status_pkey PRIMARY KEY (protocol, identifier, guid)
)
WITHOUT OIDS;
ALTER TABLE node_status OWNER TO alfresco;

CREATE TABLE permission
(
id bigint NOT NULL,
type_qname character varying(200) NOT NULL,
name character varying(100) NOT NULL,
CONSTRAINT permission_pkey PRIMARY KEY (id),
CONSTRAINT permission_type_qname_key UNIQUE (type_qname, name)
)
WITHOUT OIDS;
ALTER TABLE permission OWNER TO alfresco;

CREATE TABLE store
(
protocol character varying(50) NOT NULL,
identifier character varying(100) NOT NULL,
root_node_id bigint,
CONSTRAINT store_pkey PRIMARY KEY (protocol, identifier)
)
WITHOUT OIDS;
ALTER TABLE store OWNER TO alfresco;

CREATE TABLE version_count
(
protocol character varying(100) NOT NULL,
identifier character varying(100) NOT NULL,
version_count integer NOT NULL,
CONSTRAINT version_count_pkey PRIMARY KEY (protocol, identifier)
)
WITHOUT OIDS;
ALTER TABLE version_count OWNER TO alfresco;

–
– Copy data into new schema
–

insert into store
(
    protocol, identifier, root_node_id
)
select
    protocol, identifier, root_node_id
from
    T_store;

insert into node
(
    id, protocol, identifier, uuid, type_qname, acl_id
)
select
    id, protocol, identifier, uuid, type_qname, acl_id
from
    T_node;

insert into version_count
(
    protocol, identifier, version_count
)
select
    protocol, identifier, version_count
from
    T_version_count;

insert into node_status
(
    protocol, identifier, guid, node_id, change_txn_id
)
select
    protocol, identifier, guid, node_id, change_txn_id
from
    T_node_status;

ALTER TABLE node_properties ADD COLUMN node_id bigint;

update node_properties tproperties set node_id =
(select tnode.id from T_node tnode where
    tnode.protocol = tproperties.protocol and
    tnode.identifier = tproperties.identifier and
    tnode.uuid = tproperties.guid
);

ALTER TABLE node_properties ALTER COLUMN node_id SET NOT NULL;
ALTER TABLE node_properties DROP CONSTRAINT node_properties_pkey;
alter table node_properties add CONSTRAINT node_properties_pkey PRIMARY KEY (node_id, qname);
ALTER TABLE node_properties DROP COLUMN protocol;
ALTER TABLE node_properties DROP COLUMN identifier;
ALTER TABLE node_properties DROP COLUMN guid;

insert into node_aspects
(
    node_id, qname
)
select
      node_id, qname
from
    T_node_aspects;

insert into child_assoc
(
    id, parent_node_id, child_node_id, type_qname, qname, is_primary, assoc_index
)
select
    id, parent_node_id, child_node_id, type_qname, qname, is_primary, assoc_index
from
    T_child_assoc;

insert into node_assoc
(
    id, source_node_id, target_node_id, type_qname
)
select
    id, source_node_id, target_node_id, type_qname
from
    T_node_assoc;

insert into permission
   (
     id, type_qname, name
   )
   select
     id, type_qname, name
   from
     T_permission;

insert into access_control_list
(
    id, inherits
)
select
    id, inherits
from
    T_access_control_list;

insert into auth_ext_keys
(
    id, externalKey
)
select
    id, externalKey
from
    T_auth_ext_keys;

insert into authority
(
    recipient
)
select
    recipient
from
    T_authority;

insert into access_control_entry
(
    id, acl_id, permission_id, authority_id, allowed
)
select
    id, acl_id, permission_id, authority_id, allowed
from
    T_access_control_entry;

– Enable constraints

alter table access_control_entry add CONSTRAINT fkf064df7560601995 FOREIGN KEY (permission_id) REFERENCES permission (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table access_control_entry add CONSTRAINT fkf064df75b25a50bf FOREIGN KEY (authority_id) REFERENCES authority (recipient) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table access_control_entry add CONSTRAINT fkf064df75b9553f6c FOREIGN KEY (acl_id) REFERENCES access_control_list (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table auth_ext_keys add CONSTRAINT fk31d3ba097b7fde43 FOREIGN KEY (id) REFERENCES authority (recipient) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table child_assoc add CONSTRAINT fkc6efff3274173ff4 FOREIGN KEY (child_node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table child_assoc add CONSTRAINT fkc6efff328e50e582 FOREIGN KEY (parent_node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node add CONSTRAINT fk33ae02b9553f6c FOREIGN KEY (acl_id) REFERENCES access_control_list (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node add CONSTRAINT fk33ae02d24add25 FOREIGN KEY (protocol, identifier) REFERENCES store (protocol, identifier) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node_properties add CONSTRAINT fkc962bf907f2c8017 FOREIGN KEY (node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node_aspects add CONSTRAINT fk2b91a9de7f2c8017 FOREIGN KEY (node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node_assoc add CONSTRAINT fk5baef398a8fc7769 FOREIGN KEY (target_node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node_assoc add CONSTRAINT fk5baef398b69c43f3 FOREIGN KEY (source_node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table node_status add CONSTRAINT fk38ecb8cf7f2c8017 FOREIGN KEY (node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
alter table store add CONSTRAINT fk68af8e122dba5ba FOREIGN KEY (root_node_id) REFERENCES node (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE applied_patch ALTER id TYPE character varying(64);