- Relational databases work with rows of data. These rows can be linked to other rows via foreign keys. All linked rows form a graph.
- Linked db rows works on graphs of such database rows: It allows exporting such graphs to JSON and re-importing them again into databases.
- Export a row and all the rows that are linked to it as JSON, starting from the row of the
book
table with the primary key of 1.
JsonNode json = new DbExporter().contentAsTree(dbConnection, "book", "1").asJsonNode();
- The representation is a tree (starting from the chosen row), but all the relationships are preserved in the export.
(Command line: jbang db-export-json@poser55 -t book -p 1 -db postgres -u "jdbc:postgresql:https://localhost/demo" -l postgres -pw admin > blogpost3.json
)
Example export:
{
"id": 1,
"author_id": 2,
"author_id*author*": [
{
"id": 2,
"last_name": "Huxley"
}
],
"title": "Brave new world"
}
- Import an exported JSON string again into a database schema (maybe another one):
DbImporter dbImporter = new DbImporter();
dbImporter.insertRecords(dbConnection, dbImporter.jsonToRecord(dbConnection, "book", json));
(Command line: jbang db-import-json@poser55 -j blogpost3.json -t book -db postgres -u "jdbc:postgresql:https://localhost/demo" -l postgres -pw admin
)
Maven dependency:
<dependency>
<groupId>org.oser.tools.jdbc</groupId>
<artifactId>linked-db-rows</artifactId>
<version>0.14</version>
</dependency>
- Initialize the database
- Testing (with canonicalization of primary keys)
- For general data import/ export
- To use a prod database setup in development
- To compare 2 database situations
- Maybe as a simpler high-level database access abstraction?
- By default, when inserting it can remap the primary keys of inserted rows in order to not clash with existing primary keys. (So if in the JSON there is a book with primary key 7 (book/7) and in the db also, it looks for another PK to insert the entry, and then it remaps all other links to the book/7 in the JSON.)
- Determine the order in which tables can be inserted (taking care of their dependencies).
- Various other helpers for JDBC, refer to JdbHelpers for more details.
- Optional canonicalization of primary keys in exported data (to more easily compare data).
- Some options on how to export/ re-import linked db rows (see below).
- Most tested on postgres for now, starts to work with h2, sqlserver and oracle (mysql with limitations)
- Test coverage can be improved
- It solves a problem I have - quite hacky in many ways
- Treatment of cycles in FKs of the database schema (DDL) is a new feature (refer to Sakila and
ignoreFkCycles
) - Arrays (as e.g. Postgresql supports them) and other advanced constructs are currently not supported
- Apache version 2.0
There are accessors on DbImporter
and DbExporter
that allow setting various options:
- DbExporter
- stopTablesExcluded: tables that we do NOT want in the exported tree - the export stops before those.
- stopTablesIncluded: tables that we want in the exported tree, but from which no more FK following shall occur.
- fieldExporter and typeFieldExporters: add custom handling to load certain fields from the db (e.g. to ignore them).
You can match by field name and (optionally) table name (refer to
DbExporter#registerFieldExporter()
) or by JDBC type (refer toDbExporter#getTypeFieldExporters()
).
- DbImporter
- defaultPkGenerator: how to generate primary keys for new rows (default: NextValuePkGenerator)
- overriddenPkGenerators: pk generator overrides for special tables
- fieldImporter and typeFieldImporters: if you want to insert certain fields or types in a special way. You can
match by field and (optional) table name (refer to
DbImporter#registerFieldImporter()
) or by JDBC type name (refer toDbImporter#getTypeFieldImporters()
). This allows also e.g. to NOT treat certain fields or types (FieldImporter.NOP_FIELDIMPORTER). Take care to return true to stop the default treatment after the plugin is called! - forceInsert: in case an update would be possible: create a new row and remap other entries. Default: true If forceInsert is false we update the existing entries (if entries exist for the given primary key).
- ignoreFkCycles: by default if in your DDL there are cycles between your table relationships, it refuses to re-import them. Setting this flag to true, ignores cycles (and imports non-cycles anyways).
One can configure foreign keys that do not exist in the db, just for the exporting or importing (=virtual foreign keys).
You can add a FK via a string notation such as user_table(id)-preferences(user_id)
. The foreign key is then added from
the table user_table
and the column id
to the table preferences
with the column user_id
. Multiple virtual foreign keys can be separated by ;
.
Alternatively you can also configure it via the 4 parameters (first and second table, first and second list of columns),
refer to Fk#addVirtualForeignKeyAsString()
or Fk#addVirtualForeignKey()
Refer to the examples in the org.oser.tools.jdbc.DbExporterBasicTests#blog_artificialFk test. We added a new table preferences
that holds the
user preferences. There is no FK between the user_table
and the preferences
table in the db DDL.
Another example illustrates virtual foreign keys between tables in different database schemas: MultiSchemaExperimentTests.
Two graphs may be equivalent given their contained data but just have different primary keys (if we assume that the primary keys
do not hold business meaning, beyond mapping rows). This feature allows to convert a dbRecord to a canonical form (that is assumed
to be the same even if the primary keys vary).
Id orders are determined based on the original order in the database (so assuming integer primary keys this
should be stable for equality). We do not use any data in the dbRecords to determine the order.
Refer to RecordCanonicalizer.canonicalizeIds()
for more details.
-
Exports a db row and all linked rows as JSON (you can choose a supported db via a short name, it downloads the needed jdbc driver if needed) It requires installing https://www.jbang.dev/
-
Examples:
jbang JsonExport.java -t blogpost -p 3 --stopTablesExcluded="user_table" -db postgres \ -u "jdbc:postgresql:https://localhost/demo" -l postgres -pw admin -fks 'user_table(id)-preferences(user_id)' > blogpost3.json
- This exports the data of the table blogpost with primary key 3 to the file blogpost3.json
- It defines also a virtual foreign key
- You can replace
jbang JsonExport.java
withjbang db-export-json@poser55
(the latter does not need code locally)
jbang JsonImport.java -j blogpost3.json -t blogpost -db postgres -u "jdbc:postgresql:https://localhost/demo" -l oracle -pw admin --log=CHANGE
- This imports the JSON file blogpost3.json into the local postgres "demo" db
- You can replace
jbang JsonImport.java
withjbang db-import-json@poser55
-
Specialty: it downloads the needed JDBC driver. It currently supports postgres, h2, hsqldb, mysql, sqlserver, oracle. For now it only supports one (hardcoded) version of each.
-
Help about options:
jbang db-import-json@poser55 -h
orjbang db-export-json@poser55 -h
We use SLF4j/ Logback. There is a convenience method to enable some loggers, example use:
Loggers.enableLoggers(EnumSet.of(Loggers.CHANGE, Loggers.SELECT));
Alternatively use the loggers:
org.oser.tools.jdbc.Loggers.SELECT
org.oser.tools.jdbc.Loggers.CHANGE
org.oser.tools.jdbc.Loggers.DELETE
org.oser.tools.jdbc.Loggers.WARNING
org.oser.tools.jdbc.Loggers.INFO
Refer to DbExporter.getDeleteStatements()
. It does a db export first (using all the parameters of DbExporter).
You should check that the export to JSON is correct before proceeding!
CAVEAT: DbExporter.deleteRecursively()
really DELETES data!
One can add a tree of linked db rows in another part of the graph of rows. E.g. one can take a blog entry (with its comments) and duplicate it on another user. Refer to the org.oser.tools.jdbc.DbExporterBasicTests#blog test: it takes a blog entry (with the blogpost, its comments and with the link to its user) and adds it to another user.
- Numbers, Booleans, Strings are native in JSON.
- Date-Types are mapped to Strings. We use ISO-8601 dates by default for timestamps,
example:
2019-01-01T12:19:11
, theT
character can be replaced by a blank, as the normal toString() of java.sql.Timestamp. Dates (without a time) are in the form of 2019-12-31 (the 2nd entry is the month).
(Some DBs need a config to default to this format, refer to tests.) - Blobs are serialized as BASE64 encoded Strings.
- Subtables are added after the field that links to them (via the foreign key). Subtables are always in sub-arrays (even if .
there is only one entry in the DDL). They are behind a JSON entry of the name
NAME_OF_FK_COLUMN*NAME_OF_SUBTABLE*
, example:author_id*author*
.
- Example output looks like this:
- Prerequisite: Requires the optional (maven) dependency to https://github.com/nidi3/graphviz-java
- Sample code:
DbExporter exporter = new DbExporter();
DbRecord dbRecords = exporter.contentAsTree(connection, "Nodes", 1);
RecordAsGraph asGraph = new RecordAsGraph();
MutableGraph graph = asGraph.recordAsGraph(connection, dbRecords);
asGraph.renderGraph(graph, Format.PNG, new File( "graph.png"));
- You can optionally choose what attributes to display for each table (use the optional 3rd argument of
RecordAsGraph#recordAsGraph()
)
The library participates in the current transaction setting: it supports both auto-commit or manual transaction handling. When running e.g. in an existing Spring transaction context, you could do the following:
@Autowired
EntityManager em;
// flushing before
em.flush();
Session session = (Session) em.getDelegate();
Object result = session.doReturningWork(new ReturningWork<Object>() {
public Object execute(Connection connection) throws SQLException {
// use linked-db-rows code here, using the same connection as Spring
return result;
}
};
// end transaction as normal
The basic tests run (without configuration) for h2 (they run directly via mvn clean install
).
For the complete test set, it expects a local postgresql database with the name "demo" that is initialized with the *.sql files.
It also expects a "sakila" database that contains the Sakila database tables and content: https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/Sakila
Test support for alternative databases is available via the ACTIVE_DB
environment variable (default: postgres). These other dbs are run with testcontainer (so they need a local docker installation).
The script ./launchTests.sh
launches tests for all the db systems where the tests run (db systems other than Postgresql and h2
are launched automatically via testcontainer).
The Sakila demo database https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/Sakila is used in tests (the arrays fields are disabled for inserts)
- Description: https://andresalmiray.com/publishing-to-maven-central-using-apache-maven/ and https://proandroiddev.com/publishing-a-maven-artifact-3-3-step-by-step-instructions-to-mavencentral-publishing-bd661081645d
- Test run:
mvn -Ppublication,local-deploy -Dlocal.repository.path=c:/tmp/repository deploy
- To release, add [release] as first part of the git commit message
- Getting Foreign-Key constraints from the database
- List fks = Fk.getFksOfTable(connection, "tableName"[, fkCache]); Where fkCache is of type Cache<String, List> cache
- Fk holds the details of one foreign key in a structured form
- List orderedTables = JdbcHelper.determineOrder(connection, "rootTable", boolean exceptionWithCycles[, fkCache])
- Determine the order in which one could import the table into the db, taking the Fks into account (invert for deletion)
- Get Fk dependencies of a table and its linked tables Map<String, Set> deps = JdbcHelper.initDependencyGraph("rootTable", treatedTableSet, connection);
- Get datastructure with column metadate for all columns of a table
- SortedMap<String, ColumnMetadata> metadata = JdbcHelper.getColumnMetadata(metadata, tablename);
- Topological sort JdbcHelper.topologicalSort
- Clean ups
- Reduce the limitations
- Fix hints marked as todo
- Test support for different schemas more
- Handle uppercase letters of table names in mysql queries correctly for importing
- Fix bugs:
- Escaping of table and field names
- Extension ideas
- Do more unification of Datatype handling. E.g. oracle treats DATE different from Postgres (so at the moment we need to adapt it in the JSON/ DbRecord). Refer e.g. to DbExporterBasicTests#datatypesTest().
- Oracle testcontainer does not run on Arm osx. To try things out: https://dbfiddle.uk/Wh4zpMKJ