QLDB POC (Python)

migration-user.sql

CREATE USER IF NOT EXISTS migration@'127.0.0.1' IDENTIFIED BY 'qldb';
GRANT ALL ON demo.* TO migration@'127.0.0.1'

qldb-example.json

[
{"id":1,"first_name":"John","last_name":"Sm***","email":"jo********@ex*****.com","username":null,"website":null,"address_line1":"123 A Street","address_line2":null,"city":"New York","state":"NY","country":"US","created":"2021-05-06 21:53:48","modified":"2021-05-06 21:53:48"},
{"id":2,"first_name":"Jane","last_name":"Do***","email":"doj******@ex*****.com","username":"doj1969","website":null,"address_line1":"457 B Street","address_line2":null,"city":"Newark","state":"NJ","country":"US","created":"2021-05-06 21:56:30","modified":"2021-05-06 21:56:30"}
]

qldb-members-example.sql

INSERT INTO members VALUES(NULL,'John','Sm***','jo********@ex*****.com',NULL,NULL,'123 A Street',NULL,'New York','NY','US',NOW(),NOW());
INSERT INTO members VALUES(NULL,'Jane','Do***','doj******@ex*****.com','doj1969',NULL,'457 B Street',NULL,'Newark','NJ','US',NOW(),NOW());

qldb-poc.py

import json

from pyqldb.config.retry_config import RetryConfig
from pyqldb.driver.qldb_driver import QldbDriver

# Configure retry limit to 3
retry_config = RetryConfig(retry_limit=3)

# Initialize the driver
print("Initializing the driver")
qldb_driver = QldbDriver("demo", retry_config=retry_config)


def create_table(transaction_executor, table):

    print("Creating table {}".format(table))
    transaction_executor.execute_statement("Create TABLE {}".format(table))

def create_index(transaction_executor, table, column):
    print("Creating index {}.{}".format(table, column))
    transaction_executor.execute_statement("CREATE INDEX ON {}({})".format(table,column))


def insert_record(transaction_executor, table, values):
    print("Inserting into {}".format(table))
    transaction_executor.execute_statement("INSERT INTO {} ?".format(table),  values)



table="example3"
column="id"
#qldb_driver.execute_lambda(lambda executor: create_table(executor, table))
#qldb_driver.execute_lambda(lambda executor: create_index(executor, table, column))

with open('qldb-example.json') as f:
  data=json.load(f)

qldb_driver.execute_lambda(lambda x: insert_record(x, table, data))

schema.sql

CREATE SCHEMA IF NOT EXISTS demo;
USE demo;

CREATE TABLE members (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name     VARCHAR(50) NOT NULL,
  last_name      VARCHAR(50) NOT NULL,
  email          VARCHAR(100) NOT NULL,
  username       VARCHAR(100) NULL,
  website        VARCHAR(100) NULL,
  address_line1  VARCHAR(100) NULL,
  address_line2  VARCHAR(100) NULL,
  city           VARCHAR(100) NULL,
  state          VARCHAR(2)   NULL,
  country        CHAR(2)      NOT NULL,
  created        DATETIME DEFAULT CURRENT_TIMESTAMP,
  modified       DATETIME,
  PRIMARY KEY(id),
  UNIQUE KEY (email),
  INDEX (username),
  INDEX (last_name, first_name),
  INDEX (created)
);

CREATE TABLE audit (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_id       INT UNSIGNED NOT NULL,
    table_name     VARCHAR(50) NOT NULL,
    created        TIMESTAMP NOT NULL,
    who            VARCHAR(100) NOT NULL,
    what           ENUM ('Insert', 'Update', 'Delete') NOT NULL,
    description    VARCHAR(500) NOT NULL,
  PRIMARY KEY(id),
  INDEX (table_name, table_id),
  INDEX (id, created),
  INDEX (created, who),
  INDEX (who, created)
);

CREATE TABLE members_audit_insert(
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  audit_id       INT UNSIGNED NOT NULL,
  details        TEXT,
  PRIMARY KEY(id),
  INDEX (audit_id)
);

CREATE TABLE members_audit_update(
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  audit_id       INT UNSIGNED NOT NULL,
  details        TEXT,
  PRIMARY KEY(id),
  INDEX (audit_id)
);

CREATE TABLE members_audit_delete(
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  audit_id       INT UNSIGNED NOT NULL,
  details        TEXT,
  PRIMARY KEY(id),
  INDEX (audit_id)
);

SETUP.TXT

MYSQL_ROOT_PASSWD=$(date | md5sum | cut -c1-20)"#"; echo ${MYSQL_ROOT_PASSWD}
docker run --name mysql-qldb-migration -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWD} -d mysql/mysql-server:latest
docker logs mysql-qldb-migration
docker exec -i mysql-qldb-migration mysql -uroot -p${MYSQL_ROOT_PASSWD} < schema.sql
docker exec -i mysql-qldb-migration mysql -uroot -p${MYSQL_ROOT_PASSWD} demo < qldb-members-example.sql
docker exec -it mysql-qldb-migration mysql -uroot -p${MYSQL_ROOT_PASSWD} demo

docker exec -i mysql-qldb-migration mysql -uroot -p${MYSQL_ROOT_PASSWD} < migration-user.sql
docker exec -it mysql-qldb-migration /bin/bash

echo "SELECT * FROM members ORDER BY id" | mysqlsh --sql --result-format=json/array --uri=migration:qldb@localhost/demo > members.json
cat members.json