1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
from sqlite_utils.db import Index
import pytest
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
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_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_fails_if_constraint_cannot_be_added(fresh_db):
species = fresh_db.table("species", pk="id")
species.insert_all([{"id": 1, "name": "Palm"}, {"id": 2, "name": "Palm"}])
# This will fail because the name column is not unique
with pytest.raises(Exception, match="UNIQUE constraint failed"):
species.lookup({"name": "Palm"})
def test_lookup_with_extra_values(fresh_db):
species = fresh_db["species"]
id = species.lookup({"name": "Palm", "type": "Tree"}, {"first_seen": "2020-01-01"})
assert species.get(id) == {
"id": 1,
"name": "Palm",
"type": "Tree",
"first_seen": "2020-01-01",
}
# A subsequent lookup() should ignore the second dictionary
id2 = species.lookup({"name": "Palm", "type": "Tree"}, {"first_seen": "2021-02-02"})
assert id2 == id
assert species.get(id2) == {
"id": 1,
"name": "Palm",
"type": "Tree",
"first_seen": "2020-01-01",
}
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"],
)
]
@pytest.mark.parametrize("strict", (False, True))
def test_lookup_new_table_strict(fresh_db, strict):
fresh_db["species"].lookup({"name": "Palm"}, strict=strict)
assert fresh_db["species"].strict == strict or not fresh_db.supports_strict
|
