def test_create_index(db_path): db = Database(db_path) assert [] == db["Gosh"].indexes result = CliRunner().invoke(cli.cli, ["create-index", db_path, "Gosh", "c1"]) assert 0 == result.exit_code assert [ Index(seq=0, name="idx_Gosh_c1", unique=0, origin="c", partial=0, columns=["c1"]) ] == db["Gosh"].indexes # Try with a custom name result = CliRunner().invoke( cli.cli, ["create-index", db_path, "Gosh", "c2", "--name", "blah"]) assert 0 == result.exit_code assert [ Index(seq=0, name="blah", unique=0, origin="c", partial=0, columns=["c2"]), Index(seq=1, name="idx_Gosh_c1", unique=0, origin="c", partial=0, columns=["c1"]), ] == db["Gosh"].indexes # Try a two-column unique index create_index_unique_args = [ "create-index", db_path, "Gosh2", "c1", "c2", "--unique", ] result = CliRunner().invoke(cli.cli, create_index_unique_args) assert 0 == result.exit_code assert [ Index( seq=0, name="idx_Gosh2_c1_c2", unique=1, origin="c", partial=0, columns=["c1", "c2"], ) ] == db["Gosh2"].indexes # Trying to create the same index should fail assert 0 != CliRunner().invoke(cli.cli, create_index_unique_args).exit_code # ... unless we use --if-not-exists assert (0 == CliRunner().invoke( cli.cli, create_index_unique_args + ["--if-not-exists"]).exit_code)
def test_indexes(db): assert [ Index( seq=0, name="idx_issue_comments_user", unique=0, origin="c", partial=0, columns=["user"], ), Index( seq=1, name="idx_issue_comments_issue", unique=0, origin="c", partial=0, columns=["issue"], ), ] == db["issue_comments"].indexes
def test_indexes(fresh_db): fresh_db.conn.executescript( """ create table Gosh (c1 text, c2 text, c3 text); create index Gosh_c1 on Gosh(c1); create index Gosh_c2c3 on Gosh(c2, c3); """ ) assert [ Index( seq=0, name="Gosh_c2c3", unique=0, origin="c", partial=0, columns=["c2", "c3"], ), Index(seq=1, name="Gosh_c1", unique=0, origin="c", partial=0, columns=["c1"]), ] == fresh_db["Gosh"].indexes
def test_extracts(fresh_db, kwargs, expected_table, use_table_factory): table_kwargs = {} insert_kwargs = {} if use_table_factory: table_kwargs = kwargs else: insert_kwargs = kwargs trees = fresh_db.table("Trees", **table_kwargs) trees.insert_all( [ {"id": 1, "species_id": "Oak"}, {"id": 2, "species_id": "Oak"}, {"id": 3, "species_id": "Palm"}, ], **insert_kwargs ) # Should now have two tables: Trees and Species assert {expected_table, "Trees"} == set(fresh_db.table_names()) assert ( "CREATE TABLE [{}] (\n [id] INTEGER PRIMARY KEY,\n [value] TEXT\n)".format( expected_table ) == fresh_db[expected_table].schema ) assert ( "CREATE TABLE [Trees] (\n [id] INTEGER,\n [species_id] INTEGER REFERENCES [{}]([id])\n)".format( expected_table ) == fresh_db["Trees"].schema ) # Should have unique index on Species assert [ Index( seq=0, name="idx_{}_value".format(expected_table), unique=1, origin="c", partial=0, columns=["value"], ) ] == fresh_db[expected_table].indexes # Finally, check the rows assert [{"id": 1, "value": "Oak"}, {"id": 2, "value": "Palm"}] == list( fresh_db[expected_table].rows ) assert [ {"id": 1, "species_id": 1}, {"id": 2, "species_id": 1}, {"id": 3, "species_id": 2}, ] == list(fresh_db["Trees"].rows)
def test_lookup_adds_unique_constraint_to_existing_table(fresh_db): species = fresh_db.table("species", pk="id") palm_id = species.insert({"name": "Palm"}).last_pk species.insert({"name": "Oak"}) assert [] == species.indexes assert palm_id == species.lookup({"name": "Palm"}) assert [ Index( seq=0, name="idx_species_name", unique=1, origin="c", partial=0, columns=["name"], ) ] == species.indexes
def test_lookup_new_table_compound_key(fresh_db): species = fresh_db["species"] palm_id = species.lookup({"name": "Palm", "type": "Tree"}) oak_id = species.lookup({"name": "Oak", "type": "Tree"}) assert palm_id == species.lookup({"name": "Palm", "type": "Tree"}) assert oak_id == species.lookup({"name": "Oak", "type": "Tree"}) assert [ Index( seq=0, name="idx_species_name_type", unique=1, origin="c", partial=0, columns=["name", "type"], ) ] == species.indexes
def test_create_index_unique(fresh_db): dogs = fresh_db["dogs"] dogs.insert({ "name": "Cleo", "twitter": "cleopaws", "age": 3, "is_good_dog": True }) assert [] == dogs.indexes dogs.create_index(["name"], unique=True) assert (Index( seq=0, name="idx_dogs_name", unique=1, origin="c", partial=0, columns=["name"], ) == dogs.indexes[0])
def test_lookup_new_table(fresh_db): species = fresh_db["species"] palm_id = species.lookup({"name": "Palm"}) oak_id = species.lookup({"name": "Oak"}) cherry_id = species.lookup({"name": "Cherry"}) assert palm_id == species.lookup({"name": "Palm"}) assert oak_id == species.lookup({"name": "Oak"}) assert cherry_id == species.lookup({"name": "Cherry"}) assert palm_id != oak_id != cherry_id # Ensure the correct indexes were created assert [ Index( seq=0, name="idx_species_name", unique=1, origin="c", partial=0, columns=["name"], ) ] == species.indexes
alter=True, batch_size=batch_size) except sqlite3.OperationalError: raise @pytest.mark.parametrize( "columns,index_name,expected_index", ( ( ["is good dog"], None, Index( seq=0, name="idx_dogs_is good dog", unique=0, origin="c", partial=0, columns=["is good dog"], ), ), ( ["is good dog", "age"], None, Index( seq=0, name="idx_dogs_is good dog_age", unique=0, origin="c", partial=0, columns=["is good dog", "age"], ),
def test_lookup_with_extra_insert_parameters(fresh_db): other_table = fresh_db["other_table"] other_table.insert({"id": 1, "name": "Name"}, pk="id") species = fresh_db["species"] id = species.lookup( { "name": "Palm", "type": "Tree" }, { "first_seen": "2020-01-01", "make_not_null": 1, "fk_to_other": 1, "default_is_dog": "cat", "extract_this": "This is extracted", "convert_to_upper": "upper", "make_this_integer": "2", "this_at_front": 1, }, pk="renamed_id", foreign_keys=(("fk_to_other", "other_table", "id"), ), column_order=("this_at_front", ), not_null={"make_not_null"}, defaults={"default_is_dog": "dog"}, extracts=["extract_this"], conversions={"convert_to_upper": "upper(?)"}, columns={"make_this_integer": int}, ) assert species.schema == ( "CREATE TABLE [species] (\n" " [renamed_id] INTEGER PRIMARY KEY,\n" " [this_at_front] INTEGER,\n" " [name] TEXT,\n" " [type] TEXT,\n" " [first_seen] TEXT,\n" " [make_not_null] INTEGER NOT NULL,\n" " [fk_to_other] INTEGER REFERENCES [other_table]([id]),\n" " [default_is_dog] TEXT DEFAULT 'dog',\n" " [extract_this] INTEGER REFERENCES [extract_this]([id]),\n" " [convert_to_upper] TEXT,\n" " [make_this_integer] INTEGER\n" ")") assert species.get(id) == { "renamed_id": id, "this_at_front": 1, "name": "Palm", "type": "Tree", "first_seen": "2020-01-01", "make_not_null": 1, "fk_to_other": 1, "default_is_dog": "cat", "extract_this": 1, "convert_to_upper": "UPPER", "make_this_integer": 2, } assert species.indexes == [ Index( seq=0, name="idx_species_name_type", unique=1, origin="c", partial=0, columns=["name", "type"], ) ]