Database Modeling

This new package provides a set of function in order to extract metadata information from different data sources such as RDBMS, JSON file etc

  • apoc.model.jdbc('key or url', {schema:'<schema>', write: <true/false>, filters: { tables:[], views: [], columns: []}) YIELD nodes, relationships: load schema from relational databases

apoc.model.jdbc

The procedure allows to extract metadata information by any JDBC compatible db.

Configuration

Config Type Description

schema

String. Default empty

The schema name.

write

boolean. Default false

If you want persist the data on Neo4j

filters

map<String, Array<String>>. Default empty

A set of filters for each object type tables, views, columns

Filters

Config Type Description

tables

Array<String>

A set of regex patterns that, if matched, exclude the tables

views

Array<String>

A set of regex patterns that, if matched, exclude the views

columns

Array<String>

A set of regex patterns that, if matched, exclude the columns

Example

Starting from the following schema:

 CREATE TABLE "country" (
   "Code" CHAR(3) NOT NULL DEFAULT '',
   "Name" CHAR(52) NOT NULL DEFAULT '',
   "Continent" enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
   "Region" CHAR(26) NOT NULL DEFAULT '',
   "SurfaceArea" FLOAT(10,2) NOT NULL DEFAULT '0.00',
   "IndepYear" SMALLINT(6) DEFAULT NULL,
   "Population" INT(11) NOT NULL DEFAULT '0',
   "LifeExpectancy" FLOAT(3,1) DEFAULT NULL,
   "GNP" FLOAT(10,2) DEFAULT NULL,
   "GNPOld" FLOAT(10,2) DEFAULT NULL,
   "LocalName" CHAR(45) NOT NULL DEFAULT '',
   "GovernmentForm" CHAR(45) NOT NULL DEFAULT '',
   "HeadOfState" CHAR(60) DEFAULT NULL,
   "Capital" INT(11) DEFAULT NULL,
   "Code2" CHAR(2) NOT NULL DEFAULT '',
   PRIMARY KEY ("Code")
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 CREATE TABLE "city" (
   "ID" INT(11) NOT NULL AUTO_INCREMENT,
   "Name" CHAR(35) NOT NULL DEFAULT '',
   "CountryCode" CHAR(3) NOT NULL DEFAULT '',
   "District" CHAR(20) NOT NULL DEFAULT '',
   "Population" INT(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ("ID"),
   KEY "CountryCode" ("CountryCode"),
   CONSTRAINT "city_ibfk_1" FOREIGN KEY ("CountryCode") REFERENCES "country" ("Code")
 ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

 CREATE TABLE "countrylanguage" (
   "CountryCode" CHAR(3) NOT NULL DEFAULT '',
   "Language" CHAR(30) NOT NULL DEFAULT '',
   "IsOfficial" enum('T','F') NOT NULL DEFAULT 'F',
   "Percentage" FLOAT(4,1) NOT NULL DEFAULT '0.0',
   PRIMARY KEY ("CountryCode","Language"),
   KEY "CountryCode" ("CountryCode"),
   CONSTRAINT "countryLanguage_ibfk_1" FOREIGN KEY ("CountryCode") REFERENCES "country" ("Code")
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

By doing this procedure call:

call apoc.model.jdbc('jdbc:mysql://mysql:3306', {schema: 'test', credentials: {user: 'root', password: 'andrea'}})

You’ll get the following result:

apoc.model.jdbc