*I fixed it by modify file
V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql *
*like this :*
INSERT INTO metadatavalue (*metadata_value_id*,resource_id,
resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
*nextval('metadatavalue_seq') AS metadata_value_id,*
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where
metadata_schema_id=(select metadata_schema_id from metadataschemaregistry
where short_id='dc') and element = 'description' and qualifier is null) AS
metadata_field_id,
introductory_text AS text_value,
null AS text_lang,
0 AS place
FROM community where not introductory_text is null;
.....
*my file in attachment, hope someone needs*
On Friday, September 10, 2021 at 9:55:25 AM UTC+7 ku bun wrote:
> *Thank you for reply , i tryed like you solution but it still same error:*
>
> *First run script sql:*
> C:\Program Files\PostgreSQL\11\bin>psql -U dspace -f update-sequences.sql
> csdlkhcn
> Password for user dspace:
> setval
> --------
> 80
> (1 row)
>
>
> setval
> --------
> 100
> (1 row)
>
>
> setval
> ---------
> 2556018
> (1 row)
>
>
> setval
> --------
> 958
> (1 row)
>
>
> setval
> --------
>
> (1 row)
>
>
> setval
> --------
>
> (1 row)
>
>
> setval
> --------
> 4075
> (1 row)
>
>
> setval
> --------
> 88
> (1 row)
>
>
> setval
> --------
> 75
> (1 row)
>
>
> setval
> ---------
> 2624133
> (1 row)
>
>
> setval
> --------
> 1
> (1 row)
>
>
> setval
> --------
>
> (1 row)
>
>
> setval
> --------
>
> (1 row)
>
>
> setval
> --------
> 434
> (1 row)
>
>
> setval
> --------
> 372
> (1 row)
>
>
> psql:update-sequences.sql:63: ERROR: relation "handle_id_seq" does not
> exist
> LINE 1: SELECT setval('handle_id_seq', max(handle_id)) FROM handle;
> ^
> setval
> --------
> 352309
> (1 row)
> *After Run migrate:*
> e:\Dspace_Data\Dspace\bin>dspace database migrate ignored
> Using DSpace installation in: e:\Dspace_Data\Dspace
>
> Database URL: jdbc:postgresql://localhost:5432/csdlkhcn
> Migrating database to latest version AND running previously "Ignored"
> migrations... (Check logs for details)
> Migration exception:
> java.sql.SQLException: Flyway migration error occurred
> at
> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:673)
> at
> org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:187)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:229)
> at
> org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:81)
> Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
> Migration V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed
> ----------------------------------------------------------------------
> SQL State : 23502
> Error Code : 0
> Message : ERROR: null value in column "metadata_value_id" violates
> not-null constraint
> Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài
> li?u t?ng h?p,.., null, 0, null, -1, 4).
> Location :
> org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql
>
> (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-6.3.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)
> Line : 44
> Statement : INSERT INTO metadatavalue (resource_id, resource_type_id,
> metadata_field_id, text_value, text_lang, place)
> SELECT
> community_id AS resource_id,
> 4 AS resource_type_id,
> (select metadata_field_id from metadatafieldregistry where
> metadata_schema_id=(select metadata_schema_id from metadataschemaregistry
> where short_id='dc') and element = 'description' and qualifier =
> 'abstract') AS metadata_field_id,
> short_description AS text_value,
> null AS text_lang,
> 0 AS place
> FROM community where not short_description is null
>
> at
> org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
> at
> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
> at
> org.flywaydb.core.internal.command.DbMigrate.doMigrate(DbMigrate.java:352)
> at
> org.flywaydb.core.internal.command.DbMigrate.access$1100(DbMigrate.java:47)
> at
> org.flywaydb.core.internal.command.DbMigrate$4.doInTransaction(DbMigrate.java:308)
> at
> org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
> at
> org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:305)
> at
> org.flywaydb.core.internal.command.DbMigrate.access$1000(DbMigrate.java:47)
> at
> org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:230)
> at
> org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:173)
> at
> org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
> at
> org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)
> at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)
> at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
> at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
> at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
> at
> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:662)
> ... 7 more
> Caused by: org.postgresql.util.PSQLException: ERROR: null value in column
> "metadata_value_id" violates not-null constraint
> Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài
> li?u t?ng h?p,.., null, 0, null, -1, 4).
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
> at
> org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
> at
> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
> at
> org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
> at
> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at
> org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)
> at
> org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)
> ... 23 more
> *I think the column metadata_value_id is not auto increment when insert to
> table metadatavalue, i try change type this column from integer to serial
> but it impossible, my sequences like this:*
>
> *[image: squenences.PNG]*
> *How to set metadata_value_id is auto increment, I think this can solve
> it. I use postgresql version 11 on window server 2016.*
> On Tuesday, September 7, 2021 at 9:27:46 PM UTC+7 Tim Donohue wrote:
>
>> Hello,
>>
>> The INSERT that seems to be failing is this one:
>>
>> https://github.com/DSpace/DSpace/blob/main/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql#L44-L52
>>
>> The purpose of that statement is to migrate Community metadata from the
>> "community" table into the "metadatavalue" table. According to the error
>> message you shared, it's saying that it's trying to insert a "null" value
>> for "metadata_value_id"...which is odd, since that field should be
>> automatically filled out by a database sequence.
>>
>> Could you try running the update-sequences script on your database and
>> then try the migration again? It's possible that'll fix it. Run:
>>
>> './dspace database update-sequences'
>> './dspace database migrate ignored'
>>
>> In the migrate script you may need the "ignored" flag to ensure
>> out-of-order migrations also run. See the Upgrading docs, step 9(c).
>> https://wiki.lyrasis.org/display/DSDOC7x/Upgrading+DSpace
>>
>> Let us know on this list if that helps.
>>
>> Tim
>>
>> On Saturday, September 4, 2021 at 10:01:01 AM UTC-5 [email protected]
>> wrote:
>>
>>> *1.Before run migrate database, I check info version db: *
>>> NOTE: This database is NOT yet initialized for auto-migrations (via
>>> Flyway).
>>>
>>> Your database looks to be compatible with DSpace version 4.0
>>> All upgrades *after* version 4.0 will be run during the next migration.
>>>
>>> If you'd like to upgrade now, simply run 'dspace database migrate'.
>>>
>>> *2.After run migrate database, I receive this errors :*
>>> Migrating database to latest version... (Check dspace logs for details)
>>> Migration exception:
>>> java.sql.SQLException: Flyway migration error occurred
>>> at
>>> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:738)
>>> at
>>> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:632)
>>> at
>>> org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:228)
>>> at
>>> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at
>>> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>>> at
>>> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
>>> at
>>> org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:277)
>>> at
>>> org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:133)
>>> at
>>> org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)
>>> Caused by:
>>> org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
>>> Migration V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed
>>> ----------------------------------------------------------------------
>>> SQL State : 23502
>>> Error Code : 0
>>> Message : ERROR: null value in column "metadata_value_id" violates
>>> not-null constraint
>>> Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài
>>> li?u t?ng h?p,.., null, 0, null, -1, 4).
>>> Location :
>>> org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql
>>>
>>> (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-7.0.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)
>>> Line : 44
>>> Statement : INSERT INTO metadatavalue (resource_id, resource_type_id,
>>> metadata_field_id, text_value, text_lang, place)
>>> SELECT
>>> community_id AS resource_id,
>>> 4 AS resource_type_id,
>>> (select metadata_field_id from metadatafieldregistry where
>>> metadata_schema_id=(select metadata_schema_id from metadataschemaregistry
>>> where short_id='dc') and element = 'description' and qualifier =
>>> 'abstract') AS metadata_field_id,
>>> short_description AS text_value,
>>> null AS text_lang,
>>> 0 AS place
>>> FROM community where not short_description is null
>>>
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:369)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:54)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:282)
>>> at
>>> org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:279)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:54)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:162)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:159)
>>> at
>>> org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:71)
>>> at
>>> org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
>>> at
>>> org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:140)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:159)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:137)
>>> at org.flywaydb.core.Flyway$1.execute(Flyway.java:206)
>>> at org.flywaydb.core.Flyway$1.execute(Flyway.java:158)
>>> at org.flywaydb.core.Flyway.execute(Flyway.java:527)
>>> at org.flywaydb.core.Flyway.migrate(Flyway.java:158)
>>> at
>>> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:729)
>>> ... 9 more
>>> Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
>>> Migration V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed
>>> ----------------------------------------------------------------------
>>> SQL State : 23502
>>> Error Code : 0
>>> Message : ERROR: null value in column "metadata_value_id" violates
>>> not-null constraint
>>> Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài
>>> li?u t?ng h?p,.., null, 0, null, -1, 4).
>>> Location :
>>> org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql
>>>
>>> (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-7.0.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)
>>> Line : 44
>>> Statement : INSERT INTO metadatavalue (resource_id, resource_type_id,
>>> metadata_field_id, text_value, text_lang, place)
>>> SELECT
>>> community_id AS resource_id,
>>> 4 AS resource_type_id,
>>> (select metadata_field_id from metadatafieldregistry where
>>> metadata_schema_id=(select metadata_schema_id from metadataschemaregistry
>>> where short_id='dc') and element = 'description' and qualifier =
>>> 'abstract') AS metadata_field_id,
>>> short_description AS text_value,
>>> null AS text_lang,
>>> 0 AS place
>>> FROM community where not short_description is null
>>>
>>> at
>>> org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275)
>>> at
>>> org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:220)
>>> at
>>> org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:127)
>>> at
>>> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:88)
>>> at
>>> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.access$000(SqlMigrationExecutor.java:33)
>>> at
>>> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:77)
>>> at
>>> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:74)
>>> at
>>> org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:28)
>>> at
>>> org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:74)
>>> at
>>> org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:366)
>>> ... 27 more
>>> Caused by: org.postgresql.util.PSQLException: ERROR: null value in
>>> column "metadata_value_id" violates not-null constraint
>>> Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài
>>> li?u t?ng h?p,.., null, 0, null, -1, 4).
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505)
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241)
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
>>> at
>>> org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
>>> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
>>> at
>>> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:309)
>>> at
>>> org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:295)
>>> at
>>> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:272)
>>> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:267)
>>> at
>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
>>> at
>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
>>> at
>>> org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
>>> at
>>> org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:111)
>>> at
>>> org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:208)
>>> ... 35 more
>>> *3. And check db info again:*
>>> Database Type: postgres
>>> Database URL: jdbc:postgresql://localhost:5432/SOKHCNLD
>>> Database Schema: public
>>> Database Username: dspace
>>> Database Software: PostgreSQL version 11.13
>>> Database Driver: PostgreSQL JDBC Driver version 42.2.9
>>> PostgreSQL 'pgcrypto' extension installed/up-to-date? true (version=1.3)
>>> FlywayDB Version: 6.5.5
>>>
>>>
>>> +-----------+----------------+-----------------------------------------------------+----------+---------------------+----------------+
>>> | Category | Version | Description
>>> | Type | Installed On | State |
>>>
>>> +-----------+----------------+-----------------------------------------------------+----------+---------------------+----------------+
>>> | Versioned | 1.1 | Initial DSpace 1.1 database schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.2 | Upgrade to DSpace 1.2 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.3 | Upgrade to DSpace 1.3 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.3.9 | Drop constraint for DSpace 1 4 schema
>>> | JDBC | | Below Baseline |
>>> | Versioned | 1.4 | Upgrade to DSpace 1.4 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.5 | Upgrade to DSpace 1.5 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.5.9 | Drop constraint for DSpace 1 6 schema
>>> | JDBC | | Below Baseline |
>>> | Versioned | 1.6 | Upgrade to DSpace 1.6 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.7 | Upgrade to DSpace 1.7 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 1.8 | Upgrade to DSpace 1.8 schema
>>> | SQL | | Below Baseline |
>>> | Versioned | 3.0 | Upgrade to DSpace 3.x schema
>>> | SQL | | Below Baseline |
>>> | | 4.0 | Initializing from DSpace 4.0 database
>>> schema | BASELINE | 2021-09-04 20:53:33 | Baseline |
>>> | Versioned | 4.9.2015.10.26 | DS-2818 registry update
>>> | SQL | 2021-09-04 20:53:33 | Success |
>>> | Versioned | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy
>>> | SQL | 2021-09-04 20:53:33 | Success |
>>> | Versioned | 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop
>>> constraint | JDBC | 2021-09-04 20:53:33 | Success |
>>> | Versioned | 5.0.2014.09.26 | DS-1582 Metadata For All Objects
>>> | SQL | | Pending |
>>> | Versioned | 5.0.2014.11.04 | Enable XMLWorkflow Migration
>>> | JDBC | | Pending |
>>> | Versioned | 5.6.2016.08.23 | DS-3097
>>> | SQL | | Pending |
>>> | Versioned | 5.7.2017.04.11 | DS-3563 Index metadatavalue resource type
>>> id column | SQL | | Pending |
>>>
>>> I try find value null in column "metadata_value_id" but don't see any
>>> value null, i fall into a deadlock, hope someone can help me, thank so much
>>> :)
>>>
>>
--
All messages to this mailing list should adhere to the Code of Conduct:
https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups
"DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/dspace-tech/d0ecfe1c-abc0-4f2d-83b6-3b3f0191de07n%40googlegroups.com.
--
-- The contents of this file are subject to the license and copyright
-- detailed in the LICENSE and NOTICE files at the root of the source
-- tree and available online at
--
-- http://www.dspace.org/license/
--
-- ===============================================================
-- WARNING WARNING WARNING WARNING WARNING WARNING WARNING WARNING
--
-- DO NOT MANUALLY RUN THIS DATABASE MIGRATION. IT WILL BE EXECUTED
-- AUTOMATICALLY (IF NEEDED) BY "FLYWAY" WHEN YOU STARTUP DSPACE.
-- http://flywaydb.org/
-- ===============================================================
------------------------------------------------------
-- DS-1582 Metadata on all DSpace Objects
-- NOTE: This script also has a complimentary Flyway Java Migration
-- which drops the "item_id" constraint on metadatavalue
-- org.dspace.storage.rdbms.migration.V5_0_2014_09_25__DS_1582_Metadata_For_All_Objects_drop_constraint
------------------------------------------------------
alter table metadatavalue rename item_id to resource_id;
alter table metadatavalue alter column resource_id set not null;
alter table metadatavalue add column resource_type_id integer;
UPDATE metadatavalue SET resource_type_id = 2;
alter table metadatavalue alter column resource_type_id set not null;
-- ---------
-- community
-- ---------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier is null) AS metadata_field_id,
introductory_text AS text_value,
null AS text_lang,
0 AS place
FROM community where not introductory_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier = 'abstract') AS metadata_field_id,
short_description AS text_value,
null AS text_lang,
0 AS place
FROM community where not short_description is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier = 'tableofcontents') AS metadata_field_id,
side_bar_text AS text_value,
null AS text_lang,
0 AS place
FROM community where not side_bar_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'rights' and qualifier is null) AS metadata_field_id,
copyright_text AS text_value,
null AS text_lang,
0 AS place
FROM community where not copyright_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
community_id AS resource_id,
4 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,
name AS text_value,
null AS text_lang,
0 AS place
FROM community where not name is null;
alter table community drop column introductory_text, drop column short_description, drop column side_bar_text, drop column copyright_text, drop column name;
-- ----------
-- collection
-- ----------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier is null) AS metadata_field_id,
introductory_text AS text_value,
null AS text_lang,
0 AS place
FROM collection where not introductory_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier = 'abstract') AS metadata_field_id,
short_description AS text_value,
null AS text_lang,
0 AS place
FROM collection where not short_description is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier = 'tableofcontents') AS metadata_field_id,
side_bar_text AS text_value,
null AS text_lang,
0 AS place
FROM collection where not side_bar_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'rights' and qualifier is null) AS metadata_field_id,
copyright_text AS text_value,
null AS text_lang,
0 AS place
FROM collection where not copyright_text is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,
name AS text_value,
null AS text_lang,
0 AS place
FROM collection where not name is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'provenance' and qualifier is null) AS metadata_field_id,
provenance_description AS text_value,
null AS text_lang,
0 AS place
FROM collection where not provenance_description is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
collection_id AS resource_id,
3 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'rights' and qualifier = 'license') AS metadata_field_id,
license AS text_value,
null AS text_lang,
0 AS place
FROM collection where not license is null;
alter table collection drop column introductory_text, drop column short_description, drop column copyright_text, drop column side_bar_text, drop column name, drop column license, drop column provenance_description;
-- ---------
-- bundle
-- ---------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
bundle_id AS resource_id,
1 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,
name AS text_value,
null AS text_lang,
0 AS place
FROM bundle where not name is null;
alter table bundle drop column name;
-- ---------
-- bitstream
-- ---------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
bitstream_id AS resource_id,
0 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,
name AS text_value,
null AS text_lang,
0 AS place
FROM bitstream where not name is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
bitstream_id AS resource_id,
0 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'description' and qualifier is null) AS metadata_field_id,
description AS text_value,
null AS text_lang,
0 AS place
FROM bitstream where not description is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
bitstream_id AS resource_id,
0 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'format' and qualifier is null) AS metadata_field_id,
user_format_description AS text_value,
null AS text_lang,
0 AS place
FROM bitstream where not user_format_description is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
bitstream_id AS resource_id,
0 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'source' and qualifier is null) AS metadata_field_id,
source AS text_value,
null AS text_lang,
0 AS place
FROM bitstream where not source is null;
alter table bitstream drop column name, drop column description, drop column user_format_description, drop column source;
-- ---------
-- epersongroup
-- ---------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
eperson_group_id AS resource_id,
6 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,
name AS text_value,
null AS text_lang,
0 AS place
FROM epersongroup where not name is null;
alter table epersongroup drop column name;
-- ---------
-- eperson
-- ---------
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
eperson_id AS resource_id,
7 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='eperson') and element = 'firstname' and qualifier is null) AS metadata_field_id,
firstname AS text_value,
null AS text_lang,
0 AS place
FROM eperson where not firstname is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
eperson_id AS resource_id,
7 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='eperson') and element = 'lastname' and qualifier is null) AS metadata_field_id,
lastname AS text_value,
null AS text_lang,
0 AS place
FROM eperson where not lastname is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
eperson_id AS resource_id,
7 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='eperson') and element = 'phone' and qualifier is null) AS metadata_field_id,
phone AS text_value,
null AS text_lang,
0 AS place
FROM eperson where not phone is null;
INSERT INTO metadatavalue (metadata_value_id,resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)
SELECT
nextval('metadatavalue_seq') AS metadata_value_id,
eperson_id AS resource_id,
7 AS resource_type_id,
(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='eperson') and element = 'language' and qualifier is null) AS metadata_field_id,
language AS text_value,
null AS text_lang,
0 AS place
FROM eperson where not language is null;
alter table eperson drop column firstname, drop column lastname, drop column phone, drop column language;
-- ---------
-- dcvalue view
-- ---------
drop view dcvalue;
CREATE VIEW dcvalue AS
SELECT MetadataValue.metadata_value_id AS "dc_value_id", MetadataValue.resource_id,
MetadataValue.metadata_field_id AS "dc_type_id", MetadataValue.text_value,
MetadataValue.text_lang, MetadataValue.place
FROM MetadataValue, MetadataFieldRegistry
WHERE MetadataValue.metadata_field_id = MetadataFieldRegistry.metadata_field_id
AND MetadataFieldRegistry.metadata_schema_id = 1 AND MetadataValue.resource_type_id = 2;
