swarm-to-sqlite/tests/test_save_checkin.py

from swarm_to_sqlite import utils
import pytest
import json
import sqlite_utils
from sqlite_utils.db import ForeignKey
import pathlib
 
 
def load_checkin():
    json_path = pathlib.Path(__file__).parent / "checkin.json"
    return json.load(open(json_path, "r"))
 
 
@pytest.fixture(scope="session")
def converted():
    db = sqlite_utils.Database(":memory:")
    utils.save_checkin(load_checkin(), db)
    utils.ensure_foreign_keys(db)
    utils.create_views(db)
    return db
 
 
def test_tables(converted):
    assert {
        "venues",
        "categories",
        "with",
        "users",
        "likes",
        "categories_venues",
        "sources",
        "checkins",
        "photos",
        "categories_events",
        "events",
        "posts",
        "post_sources",
        "stickers",
    } == set(converted.table_names())
 
 
def test_venue(converted):
    venue = list(converted["venues"].rows)[0]
    assert {
        "id": "453774dcf964a520bd3b1fe3",
        "name": "Restaurant Name",
        "address": "Address",
        "crossStreet": "at cross street",
        "postalCode": "94xxx",
        "cc": "US",
        "city": "City",
        "state": "State",
        "country": "Country",
        "formattedAddress": '["Address (at cross street)", "City, State, Zip", "Country"]',
        "latitude": 38.456,
        "longitude": -122.345,
    } == venue
    # Venue categories
    categories = list(
        converted["categories"].rows_where(
            "id in (select categories_id from categories_venues where venues_id = '453774dcf964a520bd3b1fe3')"
        )
    )
    assert [
        {
            "id": "4bf58dd8d48988d10c941735",
            "name": "Category Name",
            "pluralName": "Category Names",
            "shortName": "Category",
            "icon_prefix": "https://ss3.4sqi.net/img/categories_v2/food/french_",
            "icon_suffix": ".png",
            "primary": 1,
        }
    ] == categories
 
 
def test_event(converted):
    event = list(converted["events"].rows)[0]
    assert {"id": "5bf8e4fb646e38002c472397", "name": "A movie"} == event
    categories = list(
        converted["categories"].rows_where(
            "id in (select categories_id from categories_events where events_id = '5bf8e4fb646e38002c472397')"
        )
    )
    assert [
        {
            "id": "4dfb90c6bd413dd705e8f897",
            "name": "Movie",
            "pluralName": "Movies",
            "shortName": "Movie",
            "primary": 1,
            "icon_prefix": "https://ss3.4sqi.net/img/categories_v2/arts_entertainment/movietheater_",
            "icon_suffix": ".png",
        }
    ] == categories
 
 
def test_sticker(converted):
    sticker = list(converted["stickers"].rows)[0]
    assert {
        "id": "56312102498e50c6f99f1d9b",
        "name": "Foodie",
        "stickerType": "unlockable",
        "group": '{"name": "collectible", "index": 47}',
        "pickerPosition": '{"page": 1, "index": 23}',
        "teaseText": "teaseText",
        "unlockText": "unlockText 😉",
        "image_prefix": "https://igx.4sqi.net/img/sticker/",
        "image_sizes": "[60, 94, 150, 300]",
        "image_name": "/foodie_a56e26.png",
    } == sticker
 
 
def test_likes(converted):
    likes = list(converted["likes"].rows)
    assert [
        {"users_id": "314", "checkins_id": "592b2cfe09e28339ac543fde"},
        {"users_id": "323", "checkins_id": "592b2cfe09e28339ac543fde"},
        {"users_id": "778", "checkins_id": "592b2cfe09e28339ac543fde"},
    ] == likes
 
 
def test_with_(converted):
    with_ = list(converted["with"].rows)
    assert [{"users_id": "900", "checkins_id": "592b2cfe09e28339ac543fde"}] == with_
 
 
def test_users(converted):
    users = list(converted["users"].rows)
    assert [
        {
            "id": "900",
            "firstName": "Natalie",
            "lastName": "Downe",
            "gender": "female",
            "relationship": "friend",
            "photo_prefix": "https://fastly.4sqi.net/img/user/",
            "photo_suffix": "/nd.jpg",
        },
        {
            "id": "314",
            "firstName": "J",
            "lastName": "T",
            "gender": "female",
            "relationship": "friend",
            "photo_prefix": "https://fastly.4sqi.net/img/user/",
            "photo_suffix": "/jt.jpg",
        },
        {
            "id": "323",
            "firstName": "A",
            "lastName": "R",
            "gender": "male",
            "relationship": "friend",
            "photo_prefix": "https://fastly.4sqi.net/img/user/",
            "photo_suffix": "/ar.png",
        },
        {
            "id": "778",
            "firstName": "J",
            "lastName": None,
            "gender": "none",
            "relationship": "friend",
            "photo_prefix": "https://fastly.4sqi.net/img/user/",
            "photo_suffix": "/j",
        },
        {
            "id": "15889193",
            "firstName": "Simon",
            "lastName": "Willison",
            "gender": "male",
            "relationship": "self",
            "photo_prefix": "https://fastly.4sqi.net/img/user/",
            "photo_suffix": "/CNGFSAMX00DB4DYZ.jpg",
        },
    ] == users
 
 
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": "15889193",
        },
        {
            "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": "15889193",
        },
        {
            "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": "15889193",
        },
    ] == photos
 
 
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 test_checkin_with_no_event():
    checkin = load_checkin()
    # If no event in checkin, event column should be None
    del checkin["event"]
    db = sqlite_utils.Database(":memory:")
    utils.save_checkin(checkin, db)
    assert 1 == db["checkins"].count
    row = list(db["checkins"].rows)[0]
    assert row["event"] is None
 
 
def test_view(converted):
    assert {"checkin_details", "venue_details"} == set(converted.view_names())
    assert [
        {
            "id": "592b2cfe09e28339ac543fde",
            "created": "2017-05-28T20:03:10",
            "venue_id": "453774dcf964a520bd3b1fe3",
            "venue_name": "Restaurant Name",
            "latitude": 38.456,
            "longitude": -122.345,
            "venue_categories": "Category Name",
            "shout": "7th wedding anniversary — with Natalie",
            "createdBy": None,
            "event_name": "A movie",
        }
    ] == list(converted["checkin_details"].rows)
    assert [
        {
            "first": "2017-05-28T20:03:10",
            "last": "2017-05-28T20:03:10",
            "count": 1,
            "venue_categories": "Category Name",
            "id": "453774dcf964a520bd3b1fe3",
            "name": "Restaurant Name",
            "address": "Address",
            "crossStreet": "at cross street",
            "postalCode": "94xxx",
            "cc": "US",
            "city": "City",
            "state": "State",
            "country": "Country",
            "formattedAddress": '["Address (at cross street)", "City, State, Zip", "Country"]',
            "latitude": 38.456,
            "longitude": -122.345,
        }
    ] == list(converted["venue_details"].rows)