def test_tables(db):
assert {"pull_requests", "users", "repos",
"milestones"} == set(db.table_names())
assert set(db["pull_requests"].foreign_keys) == {
ForeignKey(
table="pull_requests",
column="merged_by",
other_table="users",
other_column="id",
),
ForeignKey(
table="pull_requests",
column="assignee",
other_table="users",
other_column="id",
),
ForeignKey(
table="pull_requests",
column="milestone",
other_table="milestones",
other_column="id",
),
ForeignKey(table="pull_requests",
column="repo",
other_table="repos",
other_column="id"),
ForeignKey(table="pull_requests",
column="user",
other_table="users",
other_column="id"),
}
def test_add_foreign_keys(fresh_db):
fresh_db["authors"].insert_all([{
"id": 1,
"name": "Sally"
}, {
"id": 2,
"name": "Asheesh"
}],
pk="id")
fresh_db["categories"].insert_all([{"id": 1, "name": "Wildlife"}], pk="id")
fresh_db["books"].insert_all([{
"title": "Hedgehogs of the world",
"author_id": 1,
"category_id": 1
}])
assert [] == fresh_db["books"].foreign_keys
fresh_db.add_foreign_keys([
("books", "author_id", "authors", "id"),
("books", "category_id", "categories", "id"),
])
assert [
ForeignKey(table="books",
column="author_id",
other_table="authors",
other_column="id"),
ForeignKey(
table="books",
column="category_id",
other_table="categories",
other_column="id",
),
] == sorted(fresh_db["books"].foreign_keys)
def test_db_to_sqlite(connection, tmpdir, cli_runner):
db_path = str(tmpdir / "test.db")
cli_runner([connection, db_path, "--all"])
db = sqlite_utils.Database(db_path)
assert {"categories", "products", "vendors"} == set(db.table_names())
assert [
{
"id": 1,
"name": "Bobcat Statue",
"cat_id": 1,
"vendor_id": 1
},
{
"id": 2,
"name": "Yoga Scarf",
"cat_id": 1,
"vendor_id": None
},
] == list(db["products"].rows)
assert [{"id": 1, "name": "Junk"}] == list(db["categories"].rows)
assert [
ForeignKey(
table="products",
column="cat_id",
other_table="categories",
other_column="id",
),
ForeignKey(
table="products",
column="vendor_id",
other_table="vendors",
other_column="id",
),
] == sorted(db["products"].foreign_keys)
def test_foreign_keys(db):
assert db["pull_requests"].foreign_keys == [
ForeignKey(table="pull_requests",
column="repo",
other_table="repos",
other_column="id"),
ForeignKey(
table="pull_requests",
column="merged_by",
other_table="users",
other_column="id",
),
ForeignKey(
table="pull_requests",
column="milestone",
other_table="milestones",
other_column="id",
),
ForeignKey(
table="pull_requests",
column="assignee",
other_table="users",
other_column="id",
),
ForeignKey(table="pull_requests",
column="user",
other_table="users",
other_column="id"),
]
def test_insert_m2m_list(fresh_db):
dogs = fresh_db["dogs"]
dogs.insert({"id": 1, "name": "Cleo"}, pk="id").m2m(
"humans",
[{"id": 1, "name": "Natalie D"}, {"id": 2, "name": "Simon W"}],
pk="id",
)
assert {"dogs", "humans", "dogs_humans"} == set(fresh_db.table_names())
humans = fresh_db["humans"]
dogs_humans = fresh_db["dogs_humans"]
assert [{"humans_id": 1, "dogs_id": 1}, {"humans_id": 2, "dogs_id": 1}] == list(
dogs_humans.rows
)
assert [{"id": 1, "name": "Natalie D"}, {"id": 2, "name": "Simon W"}] == list(
humans.rows
)
assert [
ForeignKey(
table="dogs_humans", column="dogs_id", other_table="dogs", other_column="id"
),
ForeignKey(
table="dogs_humans",
column="humans_id",
other_table="humans",
other_column="id",
),
] == dogs_humans.foreign_keys
def test_posts(converted):
assert [
ForeignKey(table="posts",
column="checkin",
other_table="checkins",
other_column="id"),
ForeignKey(
table="posts",
column="post_source",
other_table="post_sources",
other_column="id",
),
] == converted["posts"].foreign_keys
posts = list(converted["posts"].rows)
assert [{
"id": "58994045e386e304939156e0",
"createdAt": 1486438469,
"text":
"The samosa chaat appetizer (easily enough for two or even four people) was a revelation - I've never tasted anything quite like it before, absolutely delicious. Chicken tika masala was amazing too.",
"url": "https://foursquare.com/item/58994045668af77dae50b376",
"contentId": "58994045668af77dae50b376",
"created": "2017-02-07T03:34:29",
"post_source": "UJXJTUHR42CKGO54KXQWGUZJL3OJKMKMVHGJ1SWIOC5TRKAC",
"checkin": "592b2cfe09e28339ac543fde",
}] == posts
def ensure_foreign_keys(db):
existing = []
for table in db.tables:
existing.extend(table.foreign_keys)
desired = [
ForeignKey(table="checkins",
column="createdBy",
other_table="users",
other_column="id"),
ForeignKey(table="checkins",
column="event",
other_table="events",
other_column="id"),
ForeignKey(
table="checkins",
column="sticker",
other_table="stickers",
other_column="id",
),
]
for fk in desired:
if fk not in existing:
try:
db[fk.table].add_foreign_key(fk.column, fk.other_table,
fk.other_column)
except AlterError:
pass
def test_db_to_sqlite(connection, tmpdir, cli_runner):
db_path = str(tmpdir / "test.db")
cli_runner([connection, db_path, "--all"])
db = sqlite_utils.Database(db_path)
assert {"categories", "products", "vendors"} == set(db.table_names())
assert [
# Slight oddity: vendor_id comes out as a string even though MySQL
# defined it as an integer because sqlite-utils treats mixed
# integer + null as a string type, not an integer type
{"id": 1, "name": "Bobcat Statue", "cat_id": 1, "vendor_id": "1"},
{"id": 2, "name": "Yoga Scarf", "cat_id": 1, "vendor_id": None},
] == list(db["products"].rows)
assert [{"id": 1, "name": "Junk"}] == list(db["categories"].rows)
assert [
ForeignKey(
table="products",
column="cat_id",
other_table="categories",
other_column="id",
),
ForeignKey(
table="products",
column="vendor_id",
other_table="vendors",
other_column="id",
),
] == sorted(db["products"].foreign_keys)
def test_insert_m2m_iterable(fresh_db):
iterable_records = ({
"id": 1,
"name": "Phineas"
}, {
"id": 2,
"name": "Ferb"
})
def iterable():
for record in iterable_records:
yield record
platypuses = fresh_db["platypuses"]
platypuses.insert({
"id": 1,
"name": "Perry"
}, pk="id").m2m(
"humans",
iterable(),
pk="id",
)
assert {"platypuses", "humans",
"humans_platypuses"} == set(fresh_db.table_names())
humans = fresh_db["humans"]
humans_platypuses = fresh_db["humans_platypuses"]
assert [
{
"humans_id": 1,
"platypuses_id": 1
},
{
"humans_id": 2,
"platypuses_id": 1
},
] == list(humans_platypuses.rows)
assert [{
"id": 1,
"name": "Phineas"
}, {
"id": 2,
"name": "Ferb"
}] == list(humans.rows)
assert [
ForeignKey(
table="humans_platypuses",
column="platypuses_id",
other_table="platypuses",
other_column="id",
),
ForeignKey(
table="humans_platypuses",
column="humans_id",
other_table="humans",
other_column="id",
),
] == humans_platypuses.foreign_keys
def test_foreign_keys(db):
foreign_keys = db["repos"].foreign_keys
assert [
ForeignKey(
table="repos", column="license", other_table="licenses", other_column="key"
),
ForeignKey(
table="repos", column="owner", other_table="users", other_column="id"
),
] == sorted(foreign_keys)
def test_tables(db):
assert {"users", "licenses", "repos", "releases"} == set(db.table_names())
assert {
ForeignKey(table="releases",
column="author",
other_table="users",
other_column="id"),
ForeignKey(table="releases",
column="repo",
other_table="repos",
other_column="id"),
} == set(db["releases"].foreign_keys)
def test_photos(converted):
assert [
ForeignKey(table="photos",
column="user",
other_table="users",
other_column="id"),
ForeignKey(table="photos",
column="source",
other_table="sources",
other_column="id"),
] == converted["photos"].foreign_keys
photos = list(converted["photos"].rows)
assert [
{
"id": "5b3840f34a7aae002c7845ee",
"createdAt": 1530413299,
"source": 1,
"prefix": "https://fastly.4sqi.net/img/general/",
"suffix":
"/15889193_ptDsf3Go3egIPU6WhwC4lIsEQLpW5SXxY3J1YyTY7Wc.jpg",
"width": 1920,
"height": 1440,
"visibility": "public",
"created": "2018-07-01T02:48:19",
"user": "******",
},
{
"id": "5b38417b16fa04002c718f84",
"createdAt": 1530413435,
"source": 1,
"prefix": "https://fastly.4sqi.net/img/general/",
"suffix":
"/15889193_GrExrA5SoKhYBK6VhZ0g97Zy8qcEdqLpuUCJSTxzaWI.jpg",
"width": 1920,
"height": 1440,
"visibility": "public",
"created": "2018-07-01T02:50:35",
"user": "******",
},
{
"id": "5b38417d04d1ae002c53b844",
"createdAt": 1530413437,
"source": 1,
"prefix": "https://fastly.4sqi.net/img/general/",
"suffix":
"/15889193__9cPZDE4Y1dhNgrqueMSFYnv20k4u1hHiqPxw5m3JOc.jpg",
"width": 1920,
"height": 1440,
"visibility": "public",
"created": "2018-07-01T02:50:37",
"user": "******",
},
] == photos
def test_transform_foreign_keys_persist(authors_db):
assert authors_db["books"].foreign_keys == [
ForeignKey(table="books",
column="author_id",
other_table="authors",
other_column="id")
]
authors_db["books"].transform(rename={"title": "book_title"})
assert authors_db["books"].foreign_keys == [
ForeignKey(table="books",
column="author_id",
other_table="authors",
other_column="id")
]
def test_foreign_keys(db):
assert [
ForeignKey(
table="issue_comments",
column="issue",
other_table="issues",
other_column="id",
),
ForeignKey(
table="issue_comments",
column="user",
other_table="users",
other_column="id",
),
] == db["issue_comments"].foreign_keys
def test_add_foreign_key(fresh_db):
fresh_db["authors"].insert_all([{
"id": 1,
"name": "Sally"
}, {
"id": 2,
"name": "Asheesh"
}],
pk="id")
fresh_db["books"].insert_all([
{
"title": "Hedgehogs of the world",
"author_id": 1
},
{
"title": "How to train your wolf",
"author_id": 2
},
])
assert [] == fresh_db["books"].foreign_keys
t = fresh_db["books"].add_foreign_key("author_id", "authors", "id")
# Ensure it returned self:
assert isinstance(t, Table) and t.name == "books"
assert [
ForeignKey(table="books",
column="author_id",
other_table="authors",
other_column="id")
] == fresh_db["books"].foreign_keys
def test_add_foreign_key(fresh_db):
fresh_db["authors"].insert_all([{
"id": 1,
"name": "Sally"
}, {
"id": 2,
"name": "Asheesh"
}],
pk="id")
fresh_db["books"].insert_all([
{
"title": "Hedgehogs of the world",
"author_id": 1
},
{
"title": "How to train your wolf",
"author_id": 2
},
])
assert [] == fresh_db["books"].foreign_keys
fresh_db["books"].add_foreign_key("author_id", "authors", "id")
assert [
ForeignKey(table="books",
column="author_id",
other_table="authors",
other_column="id")
] == fresh_db["books"].foreign_keys
def test_add_foreign_key(db_path, args, assert_message):
db = Database(db_path)
db["authors"].insert_all(
[{"id": 1, "name": "Sally"}, {"id": 2, "name": "Asheesh"}], pk="id"
)
db["books"].insert_all(
[
{"title": "Hedgehogs of the world", "author_id": 1},
{"title": "How to train your wolf", "author_id": 2},
]
)
assert (
0 == CliRunner().invoke(cli.cli, ["add-foreign-key", db_path] + args).exit_code
), assert_message
assert [
ForeignKey(
table="books", column="author_id", other_table="authors", other_column="id"
)
] == db["books"].foreign_keys
# Error if we try to add it twice:
result = CliRunner().invoke(
cli.cli, ["add-foreign-key", db_path, "books", "author_id", "authors", "id"]
)
assert 0 != result.exit_code
assert (
"Error: Foreign key already exists for author_id => authors.id"
== result.output.strip()
)
# Error if we try against an invalid column
result = CliRunner().invoke(
cli.cli, ["add-foreign-key", db_path, "books", "author_id", "authors", "bad"]
)
assert 0 != result.exit_code
assert "Error: No such column: authors.bad" == result.output.strip()
def test_redact(connection, tmpdir, cli_runner):
db_path = str(tmpdir / "test_redact.db")
result = cli_runner(
[
connection,
db_path,
"--all",
"--redact",
"products",
"name",
"--redact",
"products",
"vendor_id",
]
)
assert 0 == result.exit_code, (result.output, result.exception)
db = sqlite_utils.Database(db_path)
assert [
{"id": 1, "name": "***", "cat_id": 1, "vendor_id": "***"},
{"id": 2, "name": "***", "cat_id": 1, "vendor_id": "***"},
] == list(db["products"].rows)
assert [
ForeignKey(
table="products",
column="cat_id",
other_table="categories",
other_column="id",
)
] == sorted(db["products"].foreign_keys)
def test_tables(db):
assert {"users", "issue_comments", "issues",
"repos"}.issubset(db.table_names())
assert {
ForeignKey(
table="issue_comments",
column="issue",
other_table="issues",
other_column="id",
),
ForeignKey(
table="issue_comments",
column="user",
other_table="users",
other_column="id",
),
} == set(db["issue_comments"].foreign_keys)
def test_transform_drop_foreign_keys(fresh_db, use_pragma_foreign_keys):
if use_pragma_foreign_keys:
fresh_db.conn.execute("PRAGMA foreign_keys=ON")
# Create table with three foreign keys so we can drop two of them
fresh_db["country"].insert({"id": 1, "name": "France"}, pk="id")
fresh_db["continent"].insert({"id": 2, "name": "Europe"}, pk="id")
fresh_db["city"].insert({"id": 24, "name": "Paris"}, pk="id")
fresh_db["places"].insert(
{
"id": 32,
"name": "Caveau de la Huchette",
"country": 1,
"continent": 2,
"city": 24,
},
foreign_keys=("country", "continent", "city"),
)
assert fresh_db["places"].foreign_keys == [
ForeignKey(table="places",
column="city",
other_table="city",
other_column="id"),
ForeignKey(
table="places",
column="continent",
other_table="continent",
other_column="id",
),
ForeignKey(table="places",
column="country",
other_table="country",
other_column="id"),
]
# Drop two of those foreign keys
fresh_db["places"].transform(drop_foreign_keys=("country", "continent"))
# Should be only one foreign key now
assert fresh_db["places"].foreign_keys == [
ForeignKey(table="places",
column="city",
other_table="city",
other_column="id")
]
if use_pragma_foreign_keys:
assert fresh_db.conn.execute("PRAGMA foreign_keys").fetchone()[0]
def test_tables(db):
assert {"issues", "users", "labels", "issues_labels",
"milestones"} == set(db.table_names())
assert {
ForeignKey(
table="issues",
column="milestone",
other_table="milestones",
other_column="id",
),
ForeignKey(table="issues",
column="assignee",
other_table="users",
other_column="id"),
ForeignKey(table="issues",
column="user",
other_table="users",
other_column="id"),
} == set(db["issues"].foreign_keys)
def test_converted_workouts(converted):
actual = list(converted["workouts"].rows)
assert [{
"id": "e615a9651eab4d95debed14c2c2f7cce0c31feed",
"workoutActivityType": "HKWorkoutActivityTypeRunning",
"duration": "5.19412346680959",
"durationUnit": "min",
"totalDistance": "0.4971749504535062",
"totalDistanceUnit": "mi",
"totalEnergyBurned": "48.74499999999999",
"totalEnergyBurnedUnit": "kcal",
"sourceName": "Apple\xa0Watch",
"sourceVersion": "3.1",
"creationDate": "2016-11-14 07:33:49 -0700",
"startDate": "2016-11-14 07:25:41 -0700",
"endDate": "2016-11-14 07:30:52 -0700",
"metadata_HKTimeZone": "America/Los_Angeles",
"metadata_HKWeatherTemperature": "56 degF",
"metadata_HKWeatherHumidity": "96 %",
"workout_events": "[]",
}] == actual
assert [
ForeignKey(
table="workout_points",
column="workout_id",
other_table="workouts",
other_column="id",
)
] == converted["workout_points"].foreign_keys
actual_points = list(converted["workout_points"].rows)
assert [
{
"date": "2016-11-14 07:25:44 -0700",
"latitude": 37.7777,
"longitude": -122.426,
"altitude": 21.2694,
"horizontalAccuracy": 2.40948,
"verticalAccuracy": 1.67859,
"course": -1.0,
"speed": 2.48034,
"workout_id": "e615a9651eab4d95debed14c2c2f7cce0c31feed",
},
{
"date": "2016-11-14 07:25:44 -0700",
"latitude": 37.7777,
"longitude": -122.426,
"altitude": 21.2677,
"horizontalAccuracy": 2.40059,
"verticalAccuracy": 1.67236,
"course": -1.0,
"speed": 2.48034,
"workout_id": "e615a9651eab4d95debed14c2c2f7cce0c31feed",
},
] == actual_points
def test_foreign_keys(db):
assert [
ForeignKey(table="issues",
column="repo",
other_table="repos",
other_column="id"),
ForeignKey(
table="issues",
column="milestone",
other_table="milestones",
other_column="id",
),
ForeignKey(table="issues",
column="assignee",
other_table="users",
other_column="id"),
ForeignKey(table="issues",
column="user",
other_table="users",
other_column="id"),
] == db["issues"].foreign_keys
def test_m2m_lookup(fresh_db):
people = fresh_db.table("people", pk="id")
people.insert({"name": "Wahyu"}).m2m("tags", lookup={"tag": "Coworker"})
people_tags = fresh_db["people_tags"]
tags = fresh_db["tags"]
assert people_tags.exists()
assert tags.exists()
assert [
ForeignKey(
table="people_tags",
column="people_id",
other_table="people",
other_column="id",
),
ForeignKey(
table="people_tags", column="tags_id", other_table="tags", other_column="id"
),
] == people_tags.foreign_keys
assert [{"people_id": 1, "tags_id": 1}] == list(people_tags.rows)
assert [{"id": 1, "name": "Wahyu"}] == list(people.rows)
assert [{"id": 1, "tag": "Coworker"}] == list(tags.rows)
def test_transform_foreign_keys_survive_renamed_column(
authors_db, use_pragma_foreign_keys):
if use_pragma_foreign_keys:
authors_db.conn.execute("PRAGMA foreign_keys=ON")
authors_db["books"].transform(rename={"author_id": "author_id_2"})
assert authors_db["books"].foreign_keys == [
ForeignKey(
table="books",
column="author_id_2",
other_table="authors",
other_column="id",
)
]
def test_add_foreign_key_if_column_contains_space(fresh_db):
fresh_db["authors"].insert_all([{"id": 1, "name": "Sally"}], pk="id")
fresh_db["books"].insert_all([
{
"title": "Hedgehogs of the world",
"author id": 1
},
])
fresh_db["books"].add_foreign_key("author id", "authors", "id")
assert fresh_db["books"].foreign_keys == [
ForeignKey(table="books",
column="author id",
other_table="authors",
other_column="id")
]
"name": col.name,
"type": col.type
} for col in fresh_db["table"].columns]
@pytest.mark.parametrize(
"foreign_key_specification,expected_exception",
(
# You can specify triples, pairs, or a list of columns
((("one_id", "one", "id"), ("two_id", "two", "id")), False),
((("one_id", "one"), ("two_id", "two")), False),
(("one_id", "two_id"), False),
# You can also specify ForeignKey tuples:
(
(
ForeignKey("m2m", "one_id", "one", "id"),
ForeignKey("m2m", "two_id", "two", "id"),
),
False,
),
# If you specify a column that doesn't point to a table, you get an error:
(("one_id", "two_id", "three_id"), NoObviousTable),
# Tuples of the wrong length get an error:
((("one_id", "one", "id", "five"),
("two_id", "two", "id")), AssertionError),
# Likewise a bad column:
((("one_id", "one", "id2"), ), AlterError),
# Or a list of dicts
(({
"one_id": "one"
}, ), AssertionError),