def test_valid_database(self):
        """Tests a good database to make sure there are no false positives."""
        good_db = Database(database_name="good_db")
        table1 = Table(
            table_name="table1",
            primary_key=["col1", "col2"],
            shard_key=ShardKey(shard_keys="col1", number_shards=128),
        )
        table1.add_column(Column(column_name="col1", column_type="INT"))
        table1.add_column(Column(column_name="col2", column_type="INT"))
        good_db.add_table(table1)

        table2 = Table(
            table_name="table2",
            primary_key=["col3", "col4"],
            shard_key=ShardKey(shard_keys="col3", number_shards=128),
        )
        table2.add_foreign_key(
            from_keys=["col3", "col4"],
            to_table="table1",
            to_keys=["col1", "col2"],
        )
        table2.add_column(Column(column_name="col3", column_type="INT"))
        table2.add_column(Column(column_name="col4", column_type="INT"))
        good_db.add_table(table2)

        dv = DatabaseValidator(good_db)
        results = dv.validate()
        self.assertTrue(results.is_valid)
        self.assertEqual([], results.issues)
Example #2
0
    def get_complex_db():
        """
        Returns a more complex database with two tables and keys for testing.
        :return: Database with two tables and keys.
        :rtype: Database
        """
        database = Database(database_name="database2")
        table1 = Table(table_name="table1",
                       primary_key="col1",
                       shard_key=ShardKey("col1", 128))
        table1.add_column(Column(column_name="col1", column_type="INT"))
        table1.add_column(Column(column_name="Col2", column_type="DOUBLE"))
        table1.add_column(Column(column_name="COL3", column_type="FLOAT"))
        database.add_table(table1)

        table2 = Table(table_name="table2",
                       primary_key=["col4", "Col5"],
                       shard_key=ShardKey(["col4", "Col5"], 96))
        table2.add_column(Column(column_name="col4", column_type="VARCHAR(0)"))
        table2.add_column(Column(column_name="Col5", column_type="DATE"))
        table2.add_column(Column(column_name="COL6", column_type="BOOL"))
        database.add_table(table2)

        table2.add_foreign_key(from_keys="Col5",
                               to_table="table1",
                               to_keys="COL3")
        table1.add_relationship(
            to_table="table2",
            conditions='("table1"."col1" == "table2."COL6")')

        return database
Example #3
0
    def test_create_excel(self):
        """Test writing to Excel.  Only test is existance.  Checks shoudl be made for validity."""
        database = Database(database_name="xdb")

        table = Table(table_name="table1",
                      schema_name="s1",
                      primary_key="column_1",
                      shard_key=ShardKey("column_1", 128))
        table.add_column(Column(column_name="column_1", column_type="INT"))
        table.add_column(Column(column_name="column_2", column_type="DOUBLE"))
        table.add_column(Column(column_name="column_3", column_type="FLOAT"))
        database.add_table(table)

        table = Table(table_name="table2",
                      schema_name="s1",
                      primary_key="column_1")
        table.add_column(Column(column_name="column_1", column_type="INT"))
        table.add_column(Column(column_name="column_2",
                                column_type="DATETIME"))
        table.add_column(Column(column_name="column_3", column_type="BOOL"))
        table.add_column(Column(column_name="column_4", column_type="DOUBLE"))
        table.add_foreign_key(from_keys="column_1",
                              to_table="table_1",
                              to_keys="column_1")
        table.add_relationship(to_table="table1",
                               conditions="table2.column_4 = table1.column_2")
        database.add_table(table)

        writer = XLSWriter()
        writer.write_database(database, "test_excel")
    def test_with_csvfile(self):
        """test the tsload writer when the csv exists"""
        # todo Create the csv file.
        database = Database(database_name="xdb")

        table = Table(
            table_name="table1",
            schema_name="s1",
            primary_key="column_1",
            shard_key=ShardKey("column_1", 128),
        )
        table.add_column(Column(column_name="column_1", column_type="INT"))
        table.add_column(Column(column_name="column_2", column_type="DOUBLE"))
        table.add_column(Column(column_name="column_3", column_type="FLOAT"))
        table.add_column(Column(column_name="column_3", column_type="DATE"))
        database.add_table(table)

        table = Table(
            table_name="table2",
            schema_name="s1",
            primary_key="column_1",
            shard_key=ShardKey("column_1", 128),
        )
        table.add_column(Column(column_name="column_1", column_type="INT"))
        table.add_column(Column(column_name="column_2", column_type="FLOAT"))
        table.add_column(Column(column_name="column_3", column_type="DOUBLE"))
        database.add_table(table)

        table = Table(
            table_name="table3",
            schema_name="s1",
            primary_key="column_1",
            shard_key=ShardKey("column_1", 128),
        )
        table.add_column(Column(column_name="column_1", column_type="INT"))
        table.add_column(Column(column_name="column_2", column_type="FLOAT"))
        table.add_column(Column(column_name="column_3", column_type="VARCHAR"))
        database.add_table(table)

        tsload_writer = TsloadWriter()
        tsload_writer.write_tsloadcommand(database, "tsloadwriter_test")
        with open("tsloadwriter_test", "r") as infile:
            line = infile.readline()
            self.assertTrue(line.startswith("tsload "))
            self.assertTrue(line.index('--target_database "xdb"') > 0)
            self.assertTrue(line.index('--target_schema "s1"'))
Example #5
0
    def _read_tables_from_workbook(self):
        """
        Reads the databases and tables from Excel.  These are used to populate from the remaining sheets.
        """

        # "Tables":        ["Database", "Schema", "Table", "Updated", "Update Type", "# Rows", "# Columns",
        #                   "Primary Key", "Shard Key", "# Shards", "RLS Column"],
        table_sheet = self.workbook.sheet_by_name("Tables")
        indices = self.indices["Tables"]

        for row_count in range(1, table_sheet.nrows):
            row = table_sheet.row_values(rowx=row_count, start_colx=0)

            database_name = row[indices["Database"]]
            database = self.databases.get(database_name, None)
            if database is None:
                database = Database(database_name=database_name)
                self.databases[database_name] = database

            pk = row[indices["Primary Key"]].strip()
            if pk == "":
                pk = None
            else:
                pk = [x.strip() for x in pk.split(",")]

            sk_name = row[indices["Shard Key"]].strip()
            sk_nbr_shards = row[indices["# Shards"]]

            if (sk_name == "" and sk_nbr_shards != "") or (
                sk_name != "" and sk_nbr_shards == ""
            ):
                eprint(
                    "ERROR:  %s need to provide both a shard key name and number of shards."
                    % row[indices["Table"]]
                )

            if sk_name == "":
                sk = None
            else:
                sk = [x.strip() for x in sk_name.split(",")]

            shard_key = None
            if sk_name != "" and sk_nbr_shards != "":
                shard_key = ShardKey(
                    shard_keys=sk, number_shards=sk_nbr_shards
                )

            table = Table(
                table_name=row[indices["Table"]],
                schema_name=row[indices["Schema"]],
                primary_key=pk,
                shard_key=None,
            )
            database.add_table(table)
    def test_add_and_drop_sk(self):
        """Tests adding / dropping a column from a table."""
        dc = DDLCompare()

        db1 = Database(database_name="database1")
        db2 = Database(database_name="database2")

        t1 = Table(table_name="table1", shard_key=ShardKey(shard_keys="column1", number_shards=16))
        db1.add_table(t1)

        t2 = Table(table_name="table1")
        db2.add_table(t2)

        diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)

        self.assertTrue(type(diff1[0] is ShardKeyDroppedDifference))
        self.assertEqual(diff1[0].table_name, "table1")

        self.assertTrue(type(diff2[0] is ShardKeyAddedDifference))
        self.assertEqual(diff2[0].table_name, "table1")