1import os
2
3import infra.basetest
4
5
6class TestSQLite(infra.basetest.BRTest):
7    config = infra.basetest.BASIC_TOOLCHAIN_CONFIG + \
8        """
9        BR2_PACKAGE_SQLITE=y
10        BR2_TARGET_ROOTFS_CPIO=y
11        # BR2_TARGET_ROOTFS_TAR is not set
12        """
13    db_file = "buildroot.db"
14    db_backup = "db_dump.sql"
15
16    def run_sql_query(self, query):
17        cmd = f'sqlite3 "{self.db_file}" "{query}"'
18        self.assertRunOk(cmd)
19
20    def create_tables(self):
21        sql_query = "CREATE TABLE fruits ("
22        sql_query += "id integer PRIMARY KEY, name varchar(16) NOT NULL);"
23        self.run_sql_query(sql_query)
24
25        sql_query = "CREATE TABLE colors ("
26        sql_query += "id integer PRIMARY KEY, name varchar(16) NOT NULL);"
27        self.run_sql_query(sql_query)
28
29        sql_query = "CREATE TABLE fruit_colors ("
30        sql_query += "fruit_id integer REFERENCES fruits(id), "
31        sql_query += "color_id integer REFERENCES colors(id), "
32        sql_query += "UNIQUE (fruit_id, color_id));"
33        self.run_sql_query(sql_query)
34
35    def insert_data(self):
36        fruits = ["Banana", "Blueberry", "Orange", "Raspberry"]
37        fruit_id = 1
38        for fruit in fruits:
39            sql_query = "INSERT INTO fruits (id, name) "
40            sql_query += f"VALUES ({fruit_id}, '{fruit}');"
41            self.run_sql_query(sql_query)
42            fruit_id += 1
43
44        colors = ["Blue", "Orange", "Red", "Yellow"]
45        color_id = 1
46        for color in colors:
47            sql_query = "INSERT INTO colors (id, name) "
48            sql_query += f"VALUES ({color_id}, '{color}');"
49            self.run_sql_query(sql_query)
50            color_id += 1
51
52        fruit_colors = [(1, 4), (2, 1), (3, 2), (4, 3)]
53        for fruit_color in fruit_colors:
54            fruit_id, color_id = fruit_color
55            sql_query = "INSERT INTO fruit_colors (fruit_id, color_id) "
56            sql_query += f"VALUES ({fruit_id}, {color_id});"
57            self.run_sql_query(sql_query)
58
59    def query_database(self):
60        sql_query = "SELECT "
61        sql_query += "fruits.name AS fruit, colors.name AS color "
62        sql_query += "FROM fruits, colors, fruit_colors "
63        sql_query += "WHERE fruits.id = fruit_colors.fruit_id "
64        sql_query += "AND colors.id = fruit_colors.color_id "
65        sql_query += "ORDER BY fruit;"
66        self.run_sql_query(sql_query)
67
68    def test_run(self):
69        cpio_file = os.path.join(self.builddir, "images", "rootfs.cpio")
70        self.emulator.boot(arch="armv5",
71                           kernel="builtin",
72                           options=["-initrd", cpio_file])
73        self.emulator.login()
74
75        # The sqlite3 binary can execute.
76        self.assertRunOk("sqlite3 --version")
77
78        self.create_tables()
79        self.insert_data()
80        self.query_database()
81
82        sql_query = "UPDATE fruits SET name = 'Lemon' WHERE id = 1;"
83        self.run_sql_query(sql_query)
84
85        # Dump the test database for a backup.
86        cmd = f'echo .dump | sqlite3 "{self.db_file}" > "{self.db_backup}"'
87        self.assertRunOk(cmd)
88
89        # Drop all the tables.
90        tables = ["fruit_colors", "fruits", "colors"]
91        for table in tables:
92            sql_query = f"DROP TABLE {table};"
93            self.run_sql_query(sql_query)
94
95        # Restore the backup.
96        cmd = f'sqlite3 "{self.db_file}" < "{self.db_backup}"'
97        self.assertRunOk(cmd)
98
99        # Query on last time our data, to check the backup restoration
100        # succeeded.
101        self.query_database()
102