async def test_migration_missing_node_descriptor(test_db, caplog):
test_db_v3 = test_db("simple_v3.sql")
ieee = "ec:1b:bd:ff:fe:54:4f:40"
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
cur.execute("DELETE FROM node_descriptors WHERE ieee=?", [ieee])
with caplog.at_level(logging.WARNING):
# The invalid device will still be loaded, for now
app = await make_app(test_db_v3)
assert "partially initialized" in caplog.text
assert len(app.devices) == 2
bad_dev = app.devices[t.EUI64.convert(ieee)]
assert bad_dev.node_desc is None
caplog.clear()
# Saving the device should cause the node descriptor to not be saved
await app._dblistener._save_device(bad_dev)
await app.pre_shutdown()
# The node descriptor is not in the database
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
cur.execute(
f"SELECT * FROM node_descriptors{zigpy.appdb.DB_V} WHERE ieee=?",
[ieee])
assert not cur.fetchall()
async def test_migration_bad_attributes(test_db, force_version,
corrupt_device):
test_db_bad_attrs = test_db("bad_attrs_v3.db")
with sqlite3.connect(test_db_bad_attrs) as conn:
cur = conn.cursor()
cur.execute("SELECT count(*) FROM devices")
(num_devices_before_migration, ) = cur.fetchone()
cur.execute("SELECT count(*) FROM endpoints")
(num_ep_before_migration, ) = cur.fetchone()
if corrupt_device:
with sqlite3.connect(test_db_bad_attrs) as conn:
cur = conn.cursor()
cur.execute(
"DELETE FROM endpoints WHERE ieee='60:a4:23:ff:fe:02:39:7b'")
cur.execute("SELECT changes()")
(deleted_eps, ) = cur.fetchone()
else:
deleted_eps = 0
# Migration will handle invalid attributes entries
app = await make_app(test_db_bad_attrs)
await app.pre_shutdown()
assert len(app.devices) == num_devices_before_migration
assert (sum(len(d.non_zdo_endpoints)
for d in app.devices.values()) == num_ep_before_migration -
deleted_eps)
# Version was upgraded (and then downgraded)
with sqlite3.connect(test_db_bad_attrs) as conn:
cur = conn.cursor()
cur.execute("PRAGMA user_version")
assert cur.fetchone() == (zigpy.appdb.DB_VERSION, )
if force_version is not None:
cur.execute(f"PRAGMA user_version={force_version}")
app2 = await make_app(test_db_bad_attrs)
await app2.pre_shutdown()
# All devices still exist
assert len(app2.devices) == num_devices_before_migration
assert (sum(len(d.non_zdo_endpoints)
for d in app2.devices.values()) == num_ep_before_migration -
deleted_eps)
with sqlite3.connect(test_db_bad_attrs) as conn:
cur = conn.cursor()
cur.execute("PRAGMA user_version")
# Ensure the final database schema version number does not decrease
assert cur.fetchone()[0] == max(zigpy.appdb.DB_VERSION, force_version
or 0)
def dump_db(path):
with sqlite3.connect(path) as conn:
cur = conn.cursor()
cur.execute("PRAGMA user_version")
(user_version, ) = cur.fetchone()
sql = "\n".join(iterdump(conn))
return user_version, sql
async def test_remigrate_forcibly_downgraded_v4(test_db):
"""Test V4 re-migration which was forcibly downgraded to v3."""
test_db_v4_downgraded_to_v3 = test_db("simple_v3.sql")
# Migrate it to the latest version
app = await make_app(test_db_v4_downgraded_to_v3)
await app.pre_shutdown()
# Downgrade it back to v3
with sqlite3.connect(test_db_v4_downgraded_to_v3) as conn:
# new neighbor
conn.execute("""INSERT INTO neighbors VALUES(
'ec:1b:bd:ff:fe:54:4f:40',
'81:b1:12:dc:9f:bd:f4:b6',
'00:0d:6f:ff:fe:a6:11:7b',
48462,
37,2,15,132)
""")
conn.execute("PRAGMA user_version=3")
with sqlite3.connect(test_db_v4_downgraded_to_v3) as conn:
cur = conn.cursor()
neighbors_v3 = list(cur.execute("SELECT * FROM neighbors"))
assert len(neighbors_v3) == 3
neighbors_v4 = list(cur.execute("SELECT * FROM neighbors_v4"))
assert len(neighbors_v4) == 2
(ver, ) = cur.execute("PRAGMA user_version").fetchone()
assert ver == 3
app = await make_app(test_db_v4_downgraded_to_v3)
await app.pre_shutdown()
with sqlite3.connect(test_db_v4_downgraded_to_v3) as conn:
cur = conn.cursor()
neighbors_v4 = list(cur.execute("SELECT * FROM neighbors_v4"))
assert len(neighbors_v4) == 3
(ver, ) = cur.execute("PRAGMA user_version").fetchone()
assert ver == zigpy.appdb.DB_VERSION
async def test_migration_missing_neighbors_v3(test_db):
test_db_v3 = test_db("simple_v3.sql")
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
cur.execute("DROP TABLE neighbors")
# Ensure the table doesn't exist
with pytest.raises(sqlite3.OperationalError):
cur.execute("SELECT * FROM neighbors")
# Migration won't fail even though the database version number is 3
app = await make_app(test_db_v3)
await app.pre_shutdown()
# Version was upgraded
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
cur.execute("PRAGMA user_version")
assert cur.fetchone() == (zigpy.appdb.DB_VERSION, )
async def test_v4_to_v5_migration_bad_neighbors(test_db, with_bad_neighbor):
"""V4 migration has no `neighbors_v4` foreign key and no `ON DELETE CASCADE`"""
test_db_v4 = test_db("simple_v3_to_v4.sql")
with sqlite3.connect(test_db_v4) as conn:
cur = conn.cursor()
if with_bad_neighbor:
# Row refers to an invalid device, left behind by a bad `DELETE`
cur.execute("""
INSERT INTO neighbors_v4
VALUES (
'11:aa:bb:cc:dd:ee:ff:00',
'22:aa:bb:cc:dd:ee:ff:00',
'33:aa:bb:cc:dd:ee:ff:00',
12345,
1,1,2,0,2,0,15,132
)
""")
(num_v4_neighbors,
) = cur.execute("SELECT count(*) FROM neighbors_v4").fetchone()
app = await make_app(test_db_v4)
await app.pre_shutdown()
with sqlite3.connect(test_db_v4) as conn:
(num_new_neighbors, ) = cur.execute(
f"SELECT count(*) FROM neighbors{zigpy.appdb.DB_V}").fetchone()
# Only the invalid row was not migrated
if with_bad_neighbor:
assert num_new_neighbors == num_v4_neighbors - 1
else:
assert num_new_neighbors == num_v4_neighbors
def inner(filename):
databases = pathlib.Path(__file__).parent / "databases"
db_path = pathlib.Path(tmpdir / filename)
if filename.endswith(".db"):
db_path.write_bytes((databases / filename).read_bytes())
return str(db_path)
conn = sqlite3.connect(str(db_path))
sql = (databases / filename).read_text()
conn.executescript(sql)
conn.commit()
conn.close()
return str(db_path)
async def test_migration_0_to_5(test_db):
test_db_v0 = test_db("zigbee_20190417_v0.db")
with sqlite3.connect(test_db_v0) as conn:
cur = conn.cursor()
cur.execute("SELECT count(*) FROM devices")
(num_devices_before_migration, ) = cur.fetchone()
assert num_devices_before_migration == 27
app1 = await make_app(test_db_v0)
await app1.pre_shutdown()
assert len(app1.devices) == 27
app2 = await make_app(test_db_v0)
await app2.pre_shutdown()
# All 27 devices migrated
assert len(app2.devices) == 27
async def test_v4_to_v6_migration_missing_endpoints(test_db,
with_quirk_attribute):
"""V5's schema was too rigid and failed to migrate endpoints created by quirks"""
test_db_v3 = test_db("simple_v3.sql")
if with_quirk_attribute:
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
cur.execute("""
INSERT INTO attributes
VALUES (
'00:0d:6f:ff:fe:a6:11:7a',
123,
456,
789,
'test'
)
""")
def get_device(dev):
if dev.ieee == t.EUI64.convert("00:0d:6f:ff:fe:a6:11:7a"):
ep = dev.add_endpoint(123)
ep.add_input_cluster(456)
return dev
# Migrate to v5 and then v6
with patch("zigpy.quirks.get_device", get_device):
app = await make_app(test_db_v3)
if with_quirk_attribute:
dev = app.get_device(ieee=t.EUI64.convert("00:0d:6f:ff:fe:a6:11:7a"))
assert dev.endpoints[123].in_clusters[456]._attr_cache[789] == "test"
await app.pre_shutdown()
async def test_migration_from_3_to_4(open_twice, test_db):
test_db_v3 = test_db("simple_v3.sql")
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
neighbors_before = list(cur.execute("SELECT * FROM neighbors"))
assert len(neighbors_before) == 2
assert all([len(row) == 8 for row in neighbors_before])
node_descs_before = list(cur.execute("SELECT * FROM node_descriptors"))
assert len(node_descs_before) == 2
assert all([len(row) == 2 for row in node_descs_before])
# Ensure migration works on first run, and after shutdown
if open_twice:
app = await make_app(test_db_v3)
await app.pre_shutdown()
app = await make_app(test_db_v3)
dev1 = app.get_device(nwk=0xBD4D)
assert dev1.node_desc == zdo_t.NodeDescriptor(
logical_type=zdo_t.LogicalType.Router,
complex_descriptor_available=0,
user_descriptor_available=0,
reserved=0,
aps_flags=0,
frequency_band=zdo_t.NodeDescriptor.FrequencyBand.Freq2400MHz,
mac_capability_flags=142,
manufacturer_code=4476,
maximum_buffer_size=82,
maximum_incoming_transfer_size=82,
server_mask=11264,
maximum_outgoing_transfer_size=82,
descriptor_capability_field=0,
)
assert len(dev1.neighbors) == 1
assert dev1.neighbors[0].neighbor == zdo_t.Neighbor(
extended_pan_id=t.ExtendedPanId.convert("81:b1:12:dc:9f:bd:f4:b6"),
ieee=t.EUI64.convert("ec:1b:bd:ff:fe:54:4f:40"),
nwk=0x6D1C,
reserved1=0,
device_type=zdo_t.Neighbor.DeviceType.Router,
rx_on_when_idle=1,
relationship=zdo_t.Neighbor.RelationShip.Sibling,
reserved2=0,
permit_joining=2,
depth=15,
lqi=130,
)
dev2 = app.get_device(nwk=0x6D1C)
assert dev2.node_desc == dev1.node_desc.replace(manufacturer_code=4456)
assert len(dev2.neighbors) == 1
assert dev2.neighbors[0].neighbor == zdo_t.Neighbor(
extended_pan_id=t.ExtendedPanId.convert("81:b1:12:dc:9f:bd:f4:b6"),
ieee=t.EUI64.convert("00:0d:6f:ff:fe:a6:11:7a"),
nwk=0xBD4D,
reserved1=0,
device_type=zdo_t.Neighbor.DeviceType.Router,
rx_on_when_idle=1,
relationship=zdo_t.Neighbor.RelationShip.Sibling,
reserved2=0,
permit_joining=2,
depth=15,
lqi=132,
)
await app.pre_shutdown()
with sqlite3.connect(test_db_v3) as conn:
cur = conn.cursor()
# Old tables are untouched
assert neighbors_before == list(cur.execute("SELECT * FROM neighbors"))
assert node_descs_before == list(
cur.execute("SELECT * FROM node_descriptors"))
# New tables exist
neighbors_after = list(cur.execute("SELECT * FROM neighbors_v4"))
assert len(neighbors_after) == 2
assert all([len(row) == 12 for row in neighbors_after])
node_descs_after = list(
cur.execute("SELECT * FROM node_descriptors_v4"))
assert len(node_descs_after) == 2
assert all([len(row) == 14 for row in node_descs_after])
