Database Management Systems
There are some areas where the various database management systems (DBMS) are treated differently in SIARD Suite:
- JDBC URL for connecting to a database,
- Handling of proprietary data types,
- Preparation of a database for download,
- Preparation of a database for upload.
JDBC URL for connecting to a database
SIARD Suite documents the standard JDBC URL for connecting to a supported database system. However, there are many variations how database management systems embed platform (e.g. Windows login) or network security (e.g. Kerberos) into their access control. It is not possible here to document every specialty of every DBMS. However, as long as an acceptable JDBC URL is used, a connection to a database can be established using SIARD Suite. For details about an acceptable JDBC URL for a DBMS its native documentation must be consulted.
- MS Access
- The JDBC implementation for MS Access only permits a single type of JDBC URL: jdbc:access:<path to mdb/accdb>
- DB/2
- https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052342.html
- MySQL
- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html
- Oracle
- http://docs.oracle.com/cd/B28359_01/java.111/b31224/jdbcthin.htm
- PostgreSQL
- https://jdbc.postgresql.org/documentation/head/connect.html
- SQL Server
- https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url
When connecting to a database using SIARD Suite fails, try a native connection not involving SIARD Suite first. When that is successful but the JDBC URL still fails to connect to the database, try the same JDBC URL using SQuirreL.
Handling of proprietary data types
The proprietary data types are mapped to SQL:2008 data types by the JDBC wrapper for each DBMS. The mapping is documented in the LibreOffice tables Jdbc<DBMS>-TypeInfo.ods in the folder doc/datatypes.
Preparation of a database for download
SIARD Suite will download everything that is readable by the database user used for the connection. Choosing a suitable database user for the download determines the extent of the database being archived. Often a suitable "technical user" of a database application associated with the database has exactly the access rights needed for the archival of the database.
However, if no such user is available, one should not use the master database user (database administrator, DBA, dbo, root, SYSTEM, sa, ...) for the download because this master database user can read many system tables that should not be archived with the database. Instead, it is recommended, to create a new database user for the purpose of archiving a database. This archival user then should be granted read access to all schemas, tables, views, and types needed for archival. The documentation of the DBMS in question must be consulted to learn how to create such a user and grant the necessary rights.
After a suitable archival user has been determined or created, the download of the database can proceed using the credentials of the archival user.
Preparation of a database for upload
For uploading a database to a DBMS using SIARD Suite, suitable database schemas must be available on the target DBMS. Those schemas can then be used in the schema mapping part of the upload dialog (or in the schema mapping part of the arguments of the command-line application SiardToDb).
Ideally the target schemas are empty. However, due to security constraints one does not always have the choice to choose or create schemas freely. If the target schemas are not empty, SIARD Suite will only upload the database if either no name collisions of tables and types prevail, or else the "overwrite" option has been checked.
The database user chosen for the upload of a database must have the privilege to create types and tables and insert data into them. The DBMS documentation must be consulted for information as to how the database schemas can be created and how the database user can be granted the privilege to create types and tables in them and insert data into them. If one has access to the master database user (database administrator, DBA, dbo, root, ...) it may be convenient to use it for upload of a database. However, in that case the "overwrite" option should not be selected. Otherwise there is too great a risk that vital tables or types are overwritten.