keeps/dbptk-developer

DBPTK Developer - library and command-line tool for execution of database preservation actions

dbptk-developer's Issues

Column type change in MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, the following changes occurred on the column data types:

• [bigint] became [decimal](19, 0)
• [nvarchar](max) became [text]
• [nvarchar](50) became [varchar](50)
• [datetime] became [datetime2](7)
• [tinyint] became [smallint]

Missing primary key constraint names in MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, the name of the primary key constraints is lost. Example: CONSTRAINT [PK_Bibliography_1] PRIMARY KEY CLUSTERED > PRIMARY KEY CLUSTERED.

Missing complexType "clobType” or "blobType" in table.xsd (SIARD Output)

If "clobType” or "blobType" are used in the complexType “rowType” then the table.xsd must contain the complexType "clobType” or "blobType".

Wrong / v2.0-rc2:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.admin.ch/xmlns/siard/1.0/northwind/suppliers.xsd" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.admin.ch/xmlns/siard/1.0/northwind/suppliers.xsd">
    <xs:element name="table">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" minOccurs="0" name="row" type="rowType">
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:complexType name="rowType">
        <xs:sequence>
            <xs:element name="c1" type="xs:decimal"/>
            <xs:element minOccurs="0" name="c2" type="xs:string"/> 
            <xs:element minOccurs="0" name="c3" type="xs:string"/>
            <xs:element minOccurs="0" name="c4" type="xs:string"/>
            <xs:element minOccurs="0" name="c5" type="xs:string"/>
            <xs:element minOccurs="0" name="c6" type="xs:string"/>
            <xs:element minOccurs="0" name="c7" type="xs:string"/>
            <xs:element minOccurs="0" name="c8" type="xs:string"/>
            <xs:element minOccurs="0" name="c9" type="xs:string"/>
            <xs:element minOccurs="0" name="c10" type="xs:string"/>
            <xs:element minOccurs="0" name="c11" type="xs:string"/>
            <xs:element minOccurs="0" name="c12" type="clobType"/>
        </xs:sequence>
    </xs:complexType>
</xs:schema>

Correct:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.admin.ch/xmlns/siard/1.0/northwind/suppliers.xsd" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.admin.ch/xmlns/siard/1.0/northwind/suppliers.xsd">
    <xs:element name="table">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" minOccurs="0" name="row" type="rowType">
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:complexType name="rowType">
        <xs:sequence>
            <xs:element name="c1" type="xs:decimal"/>
            <xs:element minOccurs="0" name="c2" type="xs:string"/> 
            <xs:element minOccurs="0" name="c3" type="xs:string"/>
            <xs:element minOccurs="0" name="c4" type="xs:string"/>
            <xs:element minOccurs="0" name="c5" type="xs:string"/>
            <xs:element minOccurs="0" name="c6" type="xs:string"/>
            <xs:element minOccurs="0" name="c7" type="xs:string"/>
            <xs:element minOccurs="0" name="c8" type="xs:string"/>
            <xs:element minOccurs="0" name="c9" type="xs:string"/>
            <xs:element minOccurs="0" name="c10" type="xs:string"/>
            <xs:element minOccurs="0" name="c11" type="xs:string"/>
            <xs:element minOccurs="0" name="c12" type="clobType"/>
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="clobType">
        <xs:simpleContent>
            <xs:extension base="xs:string">
                <xs:attribute name="file" type="xs:string"/>
                <xs:attribute name="length" type="xs:integer"/>
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>
    <xs:complexType name="blobType">
        <xs:simpleContent>
            <xs:extension base="xs:hexBinary">
                <xs:attribute name="file" type="xs:string"/>
                <xs:attribute name="length" type="xs:integer"/>
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>
</xs:schema>

Missing charsets on SIARD to MySQL conversion

When round-trip testing from MySQL to SIARD and back again, column charset definition are missing. Example: missing COLLATE utf8_bin.

Also, default charset changed from utf8 to latin1.

Unsupported varbinary(max) on MSSQLServer to SIARD conversion

When converting from MSSQLServer to SIARD, columns of the type varbinary(max) are mapped into SIARD type BIT VARYING(-8), which does not make sense.

On string types, NULL values appear as empty strings

When round-trip testing from a DBMS to SIARD and back, on data inserts NULL values are inserted not as NULL but as empty strings ''. This occurs in all DBMS tested: PostgreSQL, MySQL and MSSQLServer.

Error converting data from SIARD to MSSQLServer

When converting from SIARD to MSSQLServer the following error occurred:

ERROR 2014-12-04 18:19:50,977 (SIARDImportModule) An error occurred while handling data row
pt.gov.dgarq.roda.common.convert.db.model.exception.ModuleException: Error executing insert batch
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataRow(JDBCExportModule.java:540)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule$SIARDContentSAXHandler.endElement(SIARDImportModule.java:953)
    at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
    at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule.getDatabase(SIARDImportModule.java:224)
    at pt.gov.dgarq.roda.common.convert.db.Main.main(Main.java:89)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The stream value is not the specified length. The specified length was 4,241, the actual length is 4,198.
    at com.microsoft.sqlserver.jdbc.TDSWriter.error(IOBuffer.java:3426)
    at com.microsoft.sqlserver.jdbc.TDSWriter.writeReader(IOBuffer.java:3418)
    at com.microsoft.sqlserver.jdbc.TDSWriter.writeRPCReaderUnicode(IOBuffer.java:4641)
    at com.microsoft.sqlserver.jdbc.DTV$SendByRPCOp.execute(dtv.java:887)
    at com.microsoft.sqlserver.jdbc.DTV.executeOp(dtv.java:1078)
    at com.microsoft.sqlserver.jdbc.DTV.sendByRPC(dtv.java:1116)
    at com.microsoft.sqlserver.jdbc.Parameter.sendByRPC(Parameter.java:660)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.sendParamsByRPC(SQLServerPreparedStatement.java:473)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(SQLServerPreparedStatement.java:628)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:1282)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:1209)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1173)
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataRow(JDBCExportModule.java:535)
    ... 14 more

Missing ANSI_PADDING definition on MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, missing definition of ANSI_PADDING. Example: SET ANSI_PADDING ON.

Check if this is a problem or if ANSI_PADDING is not a significant property.

Triggers and stored procedures were not exported on SIARD to MSSQLServer convertion

When round-trip testing from MSSQLServer to SIARD and back, triggers and stored procedures were converted into SIARD, but they were not converted back to MSSQLServer.

Change SIARDArchiveContainer.OutputContainerType names

from INSIDE_ARCHIVE, OUTSIDE_ARCHIVE
to MAIN, ATTACHMENT (or something better)

java.lang.NullPointerException when exporting database

Hi.

As part of the E-ARK project (http://eark-project.com/) I am trying out the keeps/db-preservation-toolkit.

When trying to export an Alfresco MySQL database I get the following error:

INFO 2014-11-17 13:19:06,747 (Main) Translating database: MySQLJDBCImportModule to DBMLExportModule
ERROR 2014-11-17 14:30:25,105 (Main) Unexpected exception
java.lang.NullPointerException
at org.apache.commons.transaction.util.FileHelper.copy(FileHelper.java:331)
at org.apache.commons.transaction.util.FileHelper.copy(FileHelper.java:296)
at org.apache.commons.transaction.util.FileHelper.copy(FileHelper.java:271)
at pt.gov.dgarq.roda.common.convert.db.model.data.FileItem.(FileItem.java:52)
at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.convertRawToCell(JDBCImportModule.java:480)
at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.convertRawToRow(JDBCImportModule.java:443)
at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:529)
at pt.gov.dgarq.roda.common.convert.db.Main.main(Main.java:93)

I have made a mysqldump from the file, but it is too big to attach here (105MB). If you have a place where I can upload the file, please let me know, so you can recreate the problem.

• Torben

SIARD: Simple ZIP data compression (8 Deflate) is now allowed

@luis100
Hi Luis
With the SIARD-Addendum (Addendum_2015-01-23_eCH-0165_V1.0_SIARD-Formatspezifikation.pdf) a simple ZIP data compression (8 Deflate) is now allowed.
Linke to German Addendum: http://www.ech.ch/alfresco/guestDownload/attach/workspace/SpacesStore/7822a908-cf37-4146-9dab-90a5763380f2/Addendum_2015-01-23_eCH-0165_V1.0_SIARD-Formatspezifikation.pdf
Chlara

Setting different schema names

When round-trip testing from DBMS to SIARD and back again, schema names are being suffixed by "_dbpresexport" in at least PostgreSQL and MSSQLServer. In PostgreSQL this creates difficulties when accessing the database.

Better XMLBufferedWriter

Put attributes as arguments of the openTag(String, Attribute...) and automatically calculate indentation level.

Remove all JAXB code and make the code generation in a maven task.

Testing environments

Add different testing groups/suites/configurations so that the tests that depend on databases (and maybe other future tests with external dependencies) don't run unless we specifically ask maven to run them.

No user or privileges defined

When exporting from SIARD to PostgreSQL, the created database has no privileges defined.

NullPointerException when converting SIARD to MySQL

When round-trip testing from MySQL to SIARD and back again, the following error occurred on the SIARD to MySQL conversion:

ERROR 2014-12-04 17:03:32,931 (Main) Unexpected exception
java.lang.NullPointerException
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataCell(JDBCExportModule.java:637)
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataRow(JDBCExportModule.java:526)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule$SIARDContentSAXHandler.endElement(SIARDImportModule.java:953)
    at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
    at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule.getDatabase(SIARDImportModule.java:224)
    at pt.gov.dgarq.roda.common.convert.db.Main.main(Main.java:89)

Change all Vector to ArrayList

Erroneous default message when dbtoolkit is called with no parameters

Concerning SIARD import and export modules the displayed message is:
SIARD dir

which can be misleading since the argument should be a complete pathname and not a directory.

Suggestion:
SIARD pathname

MySQL database name is suffixed

When round-trip testing from MySQL to SIARD and back again, database name gets suffixed by the database original name. Example: alfresco_siard becomes alfresco_siard_alfresco, original database name was alfresco.

Move String literals to constants

Missing autoincrement on SIARD to MySQL conversion

When round-trip testing from MySQL to SIARD and back again, AUTO_INCREMENTS are exported.

Togglable PrettyPrint in SIARD1ContentExportStrategy

JDBC driver class could not be found

do i need to download myself an Access jdbc driver?

$ java -jar db-preservation-toolkit-1.0.0-jar-with-dependencies.jar -i MSAccess books.mdb -o DBML ./books/   

INFO 2013-09-16 12:37:59,699 (Main) Translating database: MsAccessImportModule to DBMLExportModule
ERROR 2013-09-16 12:37:59,707 (Main) Error while importing/exporting
pt.gov.dgarq.roda.common.convert.db.model.exception.ModuleException: JDBC driver class could not be found
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:538)
    at pt.gov.dgarq.roda.common.convert.db.Main.main(Main.java:93)
Caused by: java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:171)
    at pt.gov.dgarq.roda.common.convert.db.modules.msAccess.in.MsAccessImportModule.getConnection(MsAccessImportModule.java:55)
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.getDatabaseStructure(JDBCImportModule.java:174)
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.in.JDBCImportModule.getDatabase(JDBCImportModule.java:523)
    ... 1 more

VIEWS are missing in MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, missing declarations of VIEWS. This may also happen in other DBMSs.

Missing UNIQUE constraint

When roud-trip testing from PostgreSQL to SIARD and back again, UNIQUE constraints on columns are missing. This may occur with other DBMS but it was only verified in PostgreSQL.

Scalable ReadStrategy#listFiles

Due to scalabily concerns, change to a closeable iterable (new interface) or a directorystream http://docs.oracle.com/javase/7/docs/api/java/nio/file/DirectoryStream.html

Add write strategy as an argument to both metadata strategies methods

instead of passing it in the constructor.

Missing some DEFAULT definition when converting from SIARD to MySQL

When round-trip testing from MySQL to SIARD and back again, missing some DEFAULT declaration on column type definitions. Example, missing DEFAULT ''.

Change Set to List

Fix "magic numbers"

Lost precision on dates

On round-trip testing from PostgreSQL to SIARD and back again, dates loose precision (milliseconds are lost). Example 2014-11-27 11:12:38.373 become 2014-11-27 11:12:38.37. Other DBMS may also have this problem.

The DBML generated by the db-preservation-toolkit (xml file) is not semantically valid

xmllint --noout DBML.xml

DBML.xml:1144: namespace error : Namespace prefix xsi for nil on s is not defined
                    <s xsi:nil="true"/>
                                     ^
DBML.xml:1174: namespace error : Namespace prefix xsi for nil on s is not defined
                    <s xsi:nil="true"/>
                                     ^
...

Arrays are not supported

Column type change in MySQL

When round-trip testing from MySQL to SIARD and back again, the following column data types changed:

• tinyint(4) became smallint(6)
• bigint(20) became decimal(19,0)
• timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP became datetime NOT NULL
• tinyint(1) became bit(1)
• blob became longblob

Missing IDENTITY (autoincrement) on MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, IDENTITY definitions are lost.

Check if this is a problem or if IDENTITY (autoincrement) is not a significant property.

Missing SEQUENCE in SIARD to PostgreSQL

When round-trip testing from PostgreSQL to SIARD and back, SEQUENCES are missing.

Check if this is a problem or if SEQUENCE is not considered to be a significant property.

Better ZipAndFolderReadStrategy

Delegate to a FolderReadStrategy instead of implementing it in this class.

Possibly lost one hour on date values in SIARD to MySQL conversion

When round-trip testing from MySQL to SIARD and back again, and column type changed from timestamp to datetime, the value of the insert changes one hour, but it is possibly due to the difference on the data types. Example: 2014-08-27 11:04:23 becomes 2014-08-27 12:04:23.

Column type change in PostgreSQL

When round-trip testing PostgreSQL to SIARD and back again, the following column data types change:

• character varying becomes text
• bigint becomes `numeric(19,0)

throw exception when a unsupported data type is found

Foreign keys not exported on SIARD to MySQL conversion

When round-trip testing from MySQL to SIARD and back again, foreign keys were not found on table definition.

Missing DEFAULT column type definition in MSSQLServer

When round-trip testing from MSSQLServer to SIARD and back, missing definition of DEFAULT values on column data type declarations.

Capitalize sql99toXSDType class

Don't initialize variables

Do this for variables that are eventually defined. The purpose of this is to have a compile time error in some future code if a code path is added that does not set the variable.

use Files.copy(), reading directly from xsdSchema path in Export module

then if IOUtils is unused, remove it from the dependencies

SimpleTypeBinary not supported by SIARD Import module

When testing export from SIARD to MySQL on a database with BLOBs, the following error occurred:

ERROR 2014-12-04 17:03:32,917 (SIARDImportModule) An error occurred while handling data row
pt.gov.dgarq.roda.common.convert.db.model.exception.InvalidDataException: SimpleTypeBinary not applicable to simple cell or not yet supported
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataCell(JDBCExportModule.java:591)
    at pt.gov.dgarq.roda.common.convert.db.modules.jdbc.out.JDBCExportModule.handleDataRow(JDBCExportModule.java:526)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule$SIARDContentSAXHandler.endElement(SIARDImportModule.java:953)
    at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
    at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
    at pt.gov.dgarq.roda.common.convert.db.modules.siard.in.SIARDImportModule.getDatabase(SIARDImportModule.java:224)
    at pt.gov.dgarq.roda.common.convert.db.Main.main(Main.java:89)

add open and close schema, also add it on database handler

Need SIARD support

Hi.

I need to have SIARD support integrated into the db-preservation-toolkit. I can see, that there is already a pull request for such a feature: #3

Would you please consider merging this int the toolkit? I am using an Alfresco database for testing, and I might as well test the SIARD support as well :-)

• Torben/e-ark