def test_mappings_vp_0(self):
sat1 = create_example_satellite("1")
hub1 = create_example_hub("1")
sat2 = create_example_satellite("1", "1")
vp_1_1 = create_example_version_pointer("1", "1")
_ = Schema('dv', [sat1, hub1, sat2, vp_1_1])
# print(schema.referred_tables)
table_mappings = TableMappings([
t._asdict() for t in [
TableMapping("dv", "example1_s", "", "dv", "example_1_1_vp"),
TableMapping("dv", "example11_s", "", "dv", "example_1_1_vp")
]
])
column_mappings = ColumnMappings([
c._asdict() for c in [
ColumnMapping("dv", "example1_s", "example1_key", "", "dv",
"example_1_1_vp", "example1_m_key"),
ColumnMapping("dv", "example11_s", "example1_key", "", "dv",
"example_1_1_vp", "example1_c_key"),
ColumnMapping("dv", "example11_s", "load_dts", "", "dv",
"example_1_1_vp", "example1_c_load_dts"),
ColumnMapping("dv", "example1_s", "load_dts", "", "dv",
"example_1_1_vp", "load_dts"),
]
])
mappings = Mappings(table_mappings, column_mappings,
[vp_1_1] + column_mappings.source_tables())
mappings.check(vp_1_1)
path = mappings.path(vp_1_1)
self.assertEqual([c.full_name for c in path], [
'dv.example1_s.example1_key', 'dv.example1_h.example1_key',
'dv.example11_s.example1_key'
])
def test_satellite_references(self):
hub = self.create_example_hub()
satellite = self.create_example_satellite()
_ = Schema('dv', [hub, satellite])
self.assertEqual(satellite.referred_tables(), [hub])
self.assertEqual(satellite.referring_tables(), [])
self.assertEqual(satellite.related_hub, [hub])
def test_link_satellite_fk_lookup(self):
link = self.create_example_link()
link_satellite = self.create_example_link_satellite()
_ = Schema('dv', [link, link_satellite])
[fk] = link_satellite.fks
self.assertEqual(fk.foreign_table, link)
self.assertEqual(fk.foreign_columns, [link.root_key])
def test_satellite_fk_lookup(self):
hub = self.create_example_hub()
satellite = self.create_example_satellite()
_ = Schema('dv', [hub, satellite])
[fk] = satellite.fks
self.assertEqual(fk.foreign_table, hub)
self.assertEqual(fk.foreign_columns, [hub.key])
def test_link_satellite_references(self):
hub1 = self.create_example_hub("1")
hub2 = self.create_example_hub("2")
link = self.create_example_link()
link_satellite = self.create_example_link_satellite()
_ = Schema('dv', [hub1, hub2, link, link_satellite])
self.assertEqual(link_satellite.referred_tables(), [link])
self.assertEqual(link_satellite.referring_tables(), [])
self.assertEqual(link_satellite.related_link, [link])
def test_link_fk_lookup(self):
hub1 = self.create_example_hub("1")
hub2 = self.create_example_hub("2")
link = self.create_example_link()
_ = Schema('dv', [hub1, hub2, link])
[fk1, fk2] = link.fks
self.assertEqual(fk1.foreign_table, hub1)
self.assertEqual(fk1.foreign_columns, [hub1.key])
self.assertEqual(fk2.foreign_table, hub2)
self.assertEqual(fk2.foreign_columns, [hub2.key])
def test_vp_1_es(self):
sat1 = create_example_satellite("1")
hub1 = create_example_hub("1")
link_1_2 = create_example_link("1", "2")
lsat_1_2 = create_example_link_satellite("1", "2")
hub2 = create_example_hub("2")
sat2 = create_example_satellite("2")
vp_1_2 = create_example_version_pointer("1", "2")
schema = Schema('dv', [sat1, hub1, link_1_2, lsat_1_2, hub2, sat2, vp_1_2])
# print(schema.referred_tables)
table_mappings = TableMappings([t._asdict() for t in [
TableMapping("dv", "example1_s", "", "dv", "example_1_2_vp"),
TableMapping("dv", "example1_h", "", "dv", "example_1_2_vp"),
TableMapping("dv", "example_1_2_l", "", "dv", "example_1_2_vp"),
TableMapping("dv", "example2_h", "", "dv", "example_1_2_vp"),
TableMapping("dv", "example2_s", "", "dv", "example_1_2_vp")
]])
column_mappings = ColumnMappings([c._asdict() for c in [
ColumnMapping("dv", "example1_s", "example1_key", "", "dv", "example_1_2_vp", "example1_m_key"),
ColumnMapping("dv", "example2_s", "example2_key", "", "dv", "example_1_2_vp", "example2_c_key"),
ColumnMapping("dv", "example2_s", "load_dts", "", "dv", "example_1_2_vp", "example2_c_load_dts"),
ColumnMapping("dv", "example1_s", "load_dts", "", "dv", "example_1_2_vp", "load_dts"),
]])
mappings = Mappings(table_mappings, column_mappings, list(schema.tables.values()))
mappings.check(vp_1_2)
# path = mappings.path(vp_1_2)
# print([c.full_name for c in path])
self.create_tables(sat1, hub1, link_1_2, lsat_1_2, hub2, sat2)
ts = self.start_ts
self.cur.executemany('INSERT INTO dv.example1_s VALUES(?, ?, ?, ?, ?, ?)', [
('1', ts+10, 'a', 'b', ts+3, ''),
])
self.cur.executemany('INSERT INTO dv.example_1_2_l VALUES(?, ?, ?, ?, ?)', [
('18', '1', '8', ts+8, ''),
('19', '1', '9', ts+10, ''),
])
self.cur.executemany('INSERT INTO dv.example_1_2_l_s VALUES(?, ?, ?, ?)', [
('18', ts+8, ts, ''),
('19', ts+10, ts, ''),
('19', ts+15, ts+5, ''),
])
self.cur.executemany('INSERT INTO dv.example2_s VALUES(?, ?, ?, ?, ?, ?)', [
('9', ts+11, 'c', 'd', ts, ''),
('9', ts+12, 'e', 'f', ts+5, ''),
])
sql = self.render_view(vp_1_2, mappings)
self.cur.executescript(sql)
result = self.cur.execute('SELECT * FROM dv.example_1_2_vp ORDER BY load_dts').fetchall()
expected = [
('1', '9', ts+11, ts+10)
]
self.assertEqual(result, expected)
def test_hub_references(self):
hub1 = self.create_example_hub("1")
hub2 = self.create_example_hub("2")
satellite1 = self.create_example_satellite("1")
link = self.create_example_link()
_ = Schema('dv', [hub1, hub2, satellite1, link])
self.assertEqual(hub1.referred_tables(), [])
result = hub1.referring_tables()
result.sort(key=lambda t: t.name)
self.assertEqual(result, [satellite1, link])
self.assertEqual(hub1.related_links, [link])
self.assertEqual(hub1.related_satellites, [satellite1])
def test_mappings_vp_2(self):
sat1 = create_example_satellite("1")
hub1 = create_example_hub("1")
link_1_2 = create_example_link("1", "2")
hub2 = create_example_hub("2")
link_2_3 = create_example_link("2", "3")
lsat_2_3 = create_example_link_satellite("2", "3")
hub3 = create_example_hub("3")
sat3 = create_example_satellite("3")
vp_1_3 = create_example_version_pointer("1", "3")
schema = Schema('dv', [
sat1, hub1, link_1_2, hub2, link_2_3, lsat_2_3, hub3, sat3, vp_1_3
])
# print(schema.referred_tables)
table_mappings = TableMappings([
t._asdict() for t in [
TableMapping("dv", "example1_s", "", "dv", "example_1_3_vp"),
TableMapping("dv", "example1_h", "", "dv", "example_1_3_vp"),
TableMapping("dv", "example_1_2_l", "", "dv",
"example_1_3_vp"),
TableMapping("dv", "example2_h", "", "dv", "example_1_3_vp"),
TableMapping("dv", "example_2_3_l", "", "dv",
"example_1_3_vp"),
TableMapping("dv", "example3_h", "", "dv", "example_1_3_vp"),
TableMapping("dv", "example3_s", "", "dv", "example_1_3_vp")
]
])
column_mappings = ColumnMappings([
c._asdict() for c in [
ColumnMapping("dv", "example1_s", "example1_key", "", "dv",
"example_1_3_vp", "example1_m_key"),
ColumnMapping("dv", "example3_s", "example3_key", "", "dv",
"example_1_3_vp", "example3_c_key"),
ColumnMapping("dv", "example3_s", "load_dts", "", "dv",
"example_1_3_vp", "example3_c_load_dts"),
ColumnMapping("dv", "example1_s", "load_dts", "", "dv",
"example_1_3_vp", "load_dts"),
]
])
mappings = Mappings(
table_mappings, column_mappings,
list(schema.tables.values()) + column_mappings.source_tables())
mappings.check(vp_1_3)
path = mappings.path(vp_1_3)
self.assertEqual([c.full_name for c in path], [
'dv.example1_s.example1_key', 'dv.example1_h.example1_key',
'dv.example_1_2_l.example1_key', 'dv.example_1_2_l.example2_key',
'dv.example2_h.example2_key', 'dv.example_2_3_l.example2_key',
'dv.example_2_3_l.example3_key', 'dv.example3_h.example3_key',
'dv.example3_s.example3_key'
])
def test_vp_0(self):
sat1 = create_example_satellite("1", effective_ts=True, generate_type='table')
hub1 = create_example_hub("1", generate_type='table')
sat2 = create_example_satellite("1", "1", effective_ts=True, generate_type='table')
vp_1_1 = create_example_version_pointer("1", "1")
schema = Schema('dv', [sat1, hub1, sat2, vp_1_1])
# print(schema.referred_tables)
table_mappings = TableMappings([t._asdict() for t in [
TableMapping("dv", "example1_s", "", "dv", "example_1_1_vp"),
TableMapping("dv", "example11_s", "", "dv", "example_1_1_vp")
]])
column_mappings = ColumnMappings([c._asdict() for c in [
ColumnMapping("dv", "example1_s", "example1_key", "", "dv", "example_1_1_vp", "example1_m_key"),
ColumnMapping("dv", "example11_s", "example1_key", "", "dv", "example_1_1_vp", "example1_c_key"),
ColumnMapping("dv", "example11_s", "load_dts", "", "dv", "example_1_1_vp", "example1_c_load_dts"),
ColumnMapping("dv", "example1_s", "load_dts", "", "dv", "example_1_1_vp", "load_dts"),
]])
mappings = Mappings(table_mappings, column_mappings, list(schema.tables.values()))
mappings.check(vp_1_1)
self.create_tables(sat1, hub1, sat2)
ts = self.start_ts
self.cur.executemany('INSERT INTO dv.example1_s VALUES(?, ?, ?, ?, ?, ?)', [
('1', ts+10, 'a', 'b', ts+6, ''),
])
self.cur.executemany('INSERT INTO dv.example11_s VALUES(?, ?, ?, ?, ?, ?)', [
('1', ts+11, 'c', 'd', ts, ''),
('1', ts+12, 'e', 'f', ts+5, ''),
])
sql = self.render_view(vp_1_1, mappings)
self.cur.executescript(sql)
result = self.cur.execute('SELECT * FROM dv.example_1_1_vp ORDER BY load_dts').fetchall()
expected = [
('1', '1', ts+12, ts+10)
]
self.assertEqual(result, expected)