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