Open afaisman opened 3 months ago
-- identifiers definition
CREATE TABLE identifiers
(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
identifierName VARCHAR(50) NOT NULL,
identifierTypeId INT not NULL,
envId INT NOT NULL,
regionId INT NOT NULL,
clientId INT NOT NULL,
channelConfigId INT NOT NULL,
description VARCHAR(256),
createDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)),
updateDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)),
FOREIGN KEY(identifierTypeId) REFERENCES identifierTypes(id),
FOREIGN KEY(regionId) REFERENCES regions(id),
FOREIGN KEY(clientId) REFERENCES clients(id),
FOREIGN KEY(envId) REFERENCES envs(id),
UNIQUE (identifierName)
);
SELECT i.*, c.name AS channelName, cl.clientName, it.identifierTypeName AS identifierTypeName, -- Assuming the column is named 'typeName' in 'identifierTypes' e.envName AS envName, -- Assuming the column is named 'name' in 'envs' r.regionName AS regionName -- Assuming the column is named 'name' in 'regions' FROM identifiers i JOIN channelConfig c ON i.channelConfigId = c.id JOIN clients cl ON i.clientId = cl.id JOIN identifierTypes it ON i.identifierTypeId = it.id JOIN envs e ON i.envId = e.id JOIN regions r ON i.regionId = r.id WHERE c.name = 'AWM-AdvisorConnect-90176';
Transform the JSON below into the sequence of python calls like:
upsert_channel(channelName="AWM-AdvisorCallTranscript-Zoom1-109725", client="AWMAdvisorCallTranscript"m seal="109725", lob="AWM", region="NA") add_to_channel(channelName="AWM-AdvisorCallTranscript-Zoom1-109725", operation_id="add1", date='2024-12-02', type="email", note="na", data= '[ "aaaa@jpmorgan.com" ]') add_to_channel(channelName="AWM-AdvisorCallTranscript-Zoom1-109725", operation_id="add2", date='2024-01-02', type="email", note="na", data= ' "1111@jpmorgan.com", "22222jpmorgan.com"')
....
channelConfigs: AWM-AdvisorCallTranscript-Zoom1-109725: client: AWMAdvisorCallTranscript seal: 109725 lob: AWM region: NA operations: add2: date: 2024-01-02 type: email note: na data: [ "1111@jpmorgan.com", "22222jpmorgan.com" ] add1: date: 2023-12-02 type: email note: na data: [ "aaaa@jpmorgan.com" ]
AWM-AdvisorConnect-90176: client: AWMAdvisorConnect seal: 90176 lob: AWM region: NA operations: add1: date: 2023-12-02 type: number note: core list of users data: ["+12036444444", "+120322222"]
AWM-SalesAssist-89163: client: AWMSalesAssist seal: 89163 lob: AWM region: NA operations: add2: date: 2023-12-02 type: number note: some extra users? data: ["22222222","1111"] add1: date: 2023-12-02 type: number note: core list of users data: ["1233556"]
AWM-SalesAssist-Zoom1-89163: client: AWMSalesAssist seal: 89163 lob: AWM region: NA operations: add2: date: 2024-12-02 type: email note: initial set of zoom users; mix of eng and sales data: [ "anotheremail@jpmorgan.com", "anotheremail@jpmorgan.com"]
add1:
date: 2023-12-02
type: email
note: initial set of zoom users; mix of eng and sales
data: [
"asdf@jpmorgan.com",
"qwerty@jpmorgan.com"]
client:
seal: 109725
lob: AWM
region: NA
upsert_channel(channelName="AWM-AdvisorCallTranscript-Zoom1-109725", client="AWMAdvisorCallTranscript"m seal="109725", lob="AWM", region="NA") add_to_channel(channelName="AWM-AdvisorCallTranscript-Zoom1-109725", date='2024-12-02', type="email", note="na", data= '[ "aaaa@jpmorgan.com" ]')
CREATE TABLE channelConfig ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT(256) NOT NULL, regionId INTEGER, envId INTEGER, clientId INTEGER, createDateTime TEXT, updateDateTime INTEGER, description TEXT(256), overrides TEXT(4096), CONSTRAINT channelConfig_UN UNIQUE (id), CONSTRAINT unique_name UNIQUE (name), CONSTRAINT channelConfig_FK_1 FOREIGN KEY (clientId) REFERENCES clients(id), CONSTRAINT channelConfig_FK FOREIGN KEY (envId) REFERENCES envs(id), CONSTRAINT channelConfig_FK_2 FOREIGN KEY (regionId) REFERENCES regions(id) );
-- clients definition
CREATE TABLE clients (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, clientName VARCHAR(20) NOT NULL, seal INT NOT NULL, lobId INT NOT NULL, description VARCHAR(256), createDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)), updateDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)),
FOREIGN KEY(lobId) REFERENCES lobs(id), UNIQUE (clientName,seal,lobId) );
-- identifiers definition
CREATE TABLE identifiers (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, identifierName VARCHAR(50) NOT NULL, identifierTypeId INT not NULL, envId INT NOT NULL, regionId INT NOT NULL, clientId INT NOT NULL, channelConfigId INT NOT NULL, description VARCHAR(256), createDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)), updateDateTime DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP)),
FOREIGN KEY(identifierTypeId) REFERENCES identifierTypes(id), FOREIGN KEY(regionId) REFERENCES regions(id), FOREIGN KEY(clientId) REFERENCES clients(id), FOREIGN KEY(envId) REFERENCES envs(id),
UNIQUE (identifierName) );