winbrew_database/
migration.rs

1use anyhow::{Context, Result};
2use rusqlite::Connection;
3
4pub(crate) fn migrate(conn: &Connection) -> Result<()> {
5    conn.execute_batch(
6        "
7        CREATE TABLE IF NOT EXISTS installed_packages (
8            name         TEXT PRIMARY KEY,
9            version      TEXT NOT NULL,
10            kind         TEXT NOT NULL,
11            deployment_kind TEXT NOT NULL DEFAULT 'installed',
12            engine_kind  TEXT NOT NULL,
13            engine_metadata TEXT,
14            install_dir  TEXT NOT NULL,
15            dependencies TEXT NOT NULL DEFAULT '[]',
16            status       TEXT NOT NULL DEFAULT 'installing',
17            installed_at TEXT NOT NULL
18        );
19
20        DROP TABLE IF EXISTS package_bin_lists;
21
22        CREATE TABLE IF NOT EXISTS package_command_lists (
23            package_name TEXT PRIMARY KEY REFERENCES installed_packages(name) ON DELETE CASCADE,
24            commands_json TEXT NOT NULL DEFAULT '[]'
25        );
26
27        CREATE TABLE IF NOT EXISTS command_registry (
28            command_name TEXT NOT NULL COLLATE NOCASE UNIQUE,
29            package_name TEXT NOT NULL REFERENCES installed_packages(name) ON DELETE CASCADE
30        );
31
32        CREATE INDEX IF NOT EXISTS idx_command_registry_package_name
33            ON command_registry(package_name);
34
35        CREATE TABLE IF NOT EXISTS msi_receipts (
36            package_name TEXT PRIMARY KEY REFERENCES installed_packages(name) ON DELETE CASCADE,
37            product_code TEXT NOT NULL UNIQUE,
38            upgrade_code TEXT,
39            scope        TEXT NOT NULL
40        );
41
42        CREATE TABLE IF NOT EXISTS msi_files (
43            package_name    TEXT NOT NULL REFERENCES installed_packages(name) ON DELETE CASCADE,
44            path            TEXT NOT NULL,
45            normalized_path TEXT NOT NULL,
46            hash_algorithm  TEXT,
47            hash_hex        TEXT,
48            is_config_file  INTEGER NOT NULL DEFAULT 0,
49            PRIMARY KEY (package_name, normalized_path)
50        );
51
52        CREATE INDEX IF NOT EXISTS idx_msi_files_normalized_path
53            ON msi_files(normalized_path);
54
55        CREATE TABLE IF NOT EXISTS msi_registry_entries (
56            package_name        TEXT NOT NULL REFERENCES installed_packages(name) ON DELETE CASCADE,
57            hive                TEXT NOT NULL,
58            key_path            TEXT NOT NULL,
59            normalized_key_path TEXT NOT NULL,
60            value_name          TEXT NOT NULL,
61            value_data          TEXT,
62            previous_value      TEXT,
63            PRIMARY KEY (package_name, hive, normalized_key_path, value_name)
64        );
65
66        CREATE INDEX IF NOT EXISTS idx_msi_registry_entries_normalized_key_path
67            ON msi_registry_entries(normalized_key_path);
68
69        CREATE TABLE IF NOT EXISTS msi_shortcuts (
70            package_name           TEXT NOT NULL REFERENCES installed_packages(name) ON DELETE CASCADE,
71            path                   TEXT NOT NULL,
72            normalized_path        TEXT NOT NULL,
73            target_path            TEXT,
74            normalized_target_path TEXT,
75            PRIMARY KEY (package_name, normalized_path)
76        );
77
78        CREATE INDEX IF NOT EXISTS idx_msi_shortcuts_normalized_path
79            ON msi_shortcuts(normalized_path);
80
81        CREATE INDEX IF NOT EXISTS idx_msi_shortcuts_normalized_target_path
82            ON msi_shortcuts(normalized_target_path);
83
84        CREATE TABLE IF NOT EXISTS msi_components (
85            package_name    TEXT NOT NULL REFERENCES installed_packages(name) ON DELETE CASCADE,
86            component_id    TEXT NOT NULL,
87            path            TEXT,
88            normalized_path TEXT,
89            PRIMARY KEY (package_name, component_id)
90        );
91
92        CREATE INDEX IF NOT EXISTS idx_msi_components_component_id
93            ON msi_components(component_id);
94
95        CREATE INDEX IF NOT EXISTS idx_msi_components_normalized_path
96            ON msi_components(normalized_path);
97    ",
98    )
99    .context("migration failed")?;
100
101    Ok(())
102}
103
104#[cfg(test)]
105mod tests {
106    use super::migrate;
107    use rusqlite::{Connection, OptionalExtension};
108
109    #[test]
110    fn migrate_creates_msi_inventory_tables() {
111        let conn = Connection::open_in_memory().expect("open in-memory database");
112
113        migrate(&conn).expect("run migration");
114
115        for table in [
116            "installed_packages",
117            "package_command_lists",
118            "command_registry",
119            "msi_receipts",
120            "msi_files",
121            "msi_registry_entries",
122            "msi_shortcuts",
123            "msi_components",
124        ] {
125            let exists = conn
126                .query_row(
127                    "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?1",
128                    [table],
129                    |row| row.get::<_, i64>(0),
130                )
131                .expect("table lookup");
132
133            assert_eq!(exists, 1, "expected table {table} to exist");
134        }
135
136        let index = "idx_command_registry_package_name";
137        let exists = conn
138            .query_row(
139                "SELECT 1 FROM sqlite_master WHERE type = 'index' AND name = ?1",
140                [index],
141                |row| row.get::<_, i64>(0),
142            )
143            .expect("index lookup");
144
145        assert_eq!(exists, 1, "expected index {index} to exist");
146    }
147
148    #[test]
149    fn migrate_drops_obsolete_package_bin_lists_table() {
150        let conn = Connection::open_in_memory().expect("open in-memory database");
151        conn.execute_batch(
152            "
153            CREATE TABLE installed_packages (
154                name TEXT PRIMARY KEY,
155                version TEXT NOT NULL,
156                kind TEXT NOT NULL,
157                deployment_kind TEXT NOT NULL DEFAULT 'installed',
158                engine_kind TEXT NOT NULL,
159                engine_metadata TEXT,
160                install_dir TEXT NOT NULL,
161                dependencies TEXT NOT NULL DEFAULT '[]',
162                status TEXT NOT NULL DEFAULT 'installing',
163                installed_at TEXT NOT NULL
164            );
165
166            CREATE TABLE package_bin_lists (
167                package_name TEXT PRIMARY KEY REFERENCES installed_packages(name) ON DELETE CASCADE,
168                bin_json TEXT NOT NULL DEFAULT '[]'
169            );
170            ",
171        )
172        .expect("seed obsolete table");
173
174        migrate(&conn).expect("run migration");
175
176        let exists = conn
177            .query_row(
178                "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'package_bin_lists'",
179                [],
180                |row| row.get::<_, i64>(0),
181            )
182            .optional()
183            .expect("table lookup");
184
185        assert!(
186            exists.is_none(),
187            "expected obsolete bin table to be dropped"
188        );
189    }
190}
winbrew_database/migration.rs

winbrew_database/
migration.rs
