Created
July 6, 2017 18:20
-
-
Save martyglaubitz/892097534f9f4d2bbeb9bd71b9ff5b30 to your computer and use it in GitHub Desktop.
Creates an SQLite database from a Magnolia JSON dump
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment