Creates an SQLite database from a Magnolia JSON dump

jcr2sqlite.py

import json
import sqlite3

def load_jcr_json(filePath: str):
    with open(filePath) as file:
        return json.load(file)


nodes_scheme = '''
    CREATE TABLE IF NOT EXISTS nodes (
        identifier text PRIMARY KEY,
        name text NOT NULL,
        path text NOT NULL,
        type text NOT NULL
    )
'''

child_nodes_scheme = '''
    CREATE TABLE IF NOT EXISTS child_nodes (
        parent_node_identifier text NOT NULL,
        child_node_identifier text NOT NULL,
        PRIMARY KEY (parent_node_identifier, child_node_identifier),
        FOREIGN KEY(parent_node_identifier) REFERENCES nodes(identifier),
        FOREIGN KEY(child_node_identifier) REFERENCES nodes(identifier)
    )
'''

node_properties_scheme = '''
    CREATE TABLE IF NOT EXISTS node_properties (
        node_identifier text NOT NULL,
        name text NOT NULL,
        type text NOT NULL,
        value text NOT NULL,
        PRIMARY KEY (node_identifier, name),
        FOREIGN KEY(node_identifier) REFERENCES nodes(identifier)
    )
'''

conn = None
def connect():
    global conn
    conn = sqlite3.connect('jcr.sqlite')

def disconnect():
    global conn
    conn.close()

def with_cursor(function):
    global conn

    cursor = None

    try:
        cursor = conn.cursor()
        function(cursor)
        conn.commit()

    except Exception as e:
        print(e)
    finally:
        if cursor:
            cursor.close()


def create_database():
    def _create_database(cursor):
        cursor.execute(nodes_scheme)
        cursor.execute(child_nodes_scheme)
        cursor.execute(node_properties_scheme)

    with_cursor(_create_database)

def import_node(node: dict):
    def _import_node(cursor):
        node_identifier = node["identifier"]
        node_name = node["name"]
        node_path = node["path"]
        node_type = node["type"]

        cursor.execute('''INSERT INTO nodes VALUES (
            '{identifier}', '{name}', '{path}', '{type}'
        )'''.format(identifier=node_identifier, name=node_name, path=node_path, type=node_type))

    with_cursor(_import_node)

    import_node_properties(node)

    childNodes = node.get("nodes")
    if not childNodes:
        return

    for childNode in childNodes:
        import_child_node(node, childNode)

def import_child_node(parentNode: dict, childNode: dict):
    import_node(childNode)

    parent_node_identifier = parentNode["identifier"]
    child_node_identifier = childNode["identifier"]

    def _import_child_node(cursor):
        cursor.execute('''INSERT INTO child_nodes VALUES (
            '{parent_node_identifier}', '{child_node_identifier}'
        )'''.format(parent_node_identifier=parent_node_identifier, child_node_identifier=child_node_identifier))

    with_cursor(_import_child_node)

def import_node_properties(node: dict):
    def _import_node_properties(cursor):
        properties = node.get("properties")
        if not properties:
            print("no properties in node: " + node["path"])
            return None

        node_identifier = node["identifier"]

        rows = []
        for property in properties:
            property_name = property["name"]
            property_type = property["type"]
            property_value = property["values"][0]

            rows.append((node_identifier, property_name, property_type, property_value))

        cursor.executemany("INSERT INTO node_properties VALUES (?, ?, ?, ?)", rows)

    with_cursor(_import_node_properties)

if __name__ == "__main__":
    create_database()