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"],
)
]