attempting to isolate an issue with sqlite and large integer values in piccolo

test-sqlite-bigints.py

"""
Original code by @dantownsend
Minor tweets by @scott2b
See: https://github.com/piccolo-orm/piccolo/issues/662
"""
from piccolo.table import Table
from piccolo.columns.column_types import Integer, JSON
from piccolo.engine.sqlite import SQLiteEngine


DB = SQLiteEngine(log_queries=True)


class Tweet(Table, tablename="tweets", db=DB):
    tweet_id: int = Integer(primary_key=True)
    data: dict = JSON()


async def main():
    if await Tweet.table_exists():
        await Tweet.alter().drop_table()

    await Tweet.create_table()

    #start = 1000000000 # this works
    start = 1587623774714830850 # this does not

    ###
    # raw insert fails with a returning statement. The returned ID value does not
    # match the value that is actually in the db
    ###

    await Tweet.delete(force=True)
    for i in range(start, start + 100):
        r = await Tweet.raw("insert into tweets (tweet_id, data) values ({}, {}) returning tweet_id",
            i, '{"message": "hello world 1"}' )
        tweet_id = r[0]["tweet_id"]
        assert tweet_id == i, f"ID from raw insert call with return not correct. {tweet_id} != {i}"

    """ The returned value is not the value in the DB!!!
    AssertionError: ID from raw insert call with return not correct. 1587623774714830848 != 1587623774714830850

    sqlite> select * from tweets;
    1587623774714830850|{"message": "hello world 1"}
    """

    """And yet:
    sqlite> insert into tweets (tweet_id, data) values (1587623774714830850, '{"message": "hello world 1"}') returning tweet_id;
    1587623774714830850
    """

    ###
    # others do not work for large IDs
    ###

    # Make sure `create` assigns primary key values correctly.
    await Tweet.delete(force=True)
    for i in range(start, start + 100):
        tweet = await Tweet.objects().create(
            tweet_id=i,
            data={'message': 'hello world 2'}
        )
        assert tweet.tweet_id == i, f"ID from create call is not correct. {tweet.tweet_id} != {i}"

    # Make sure `get_or_create` assigns primary key values correctly for existing rows.
    for i in range(start, start + 100):
        tweet = await Tweet.objects().get_or_create(
            Tweet.tweet_id == i,
            defaults={ 'data': { 'message': 'hello world 3' } }
        )
        assert tweet.tweet_id == i, f"ID from get_or_create call is not correct for existing row. {tweet.tweet_id} != {i}"

    # Make sure `get_or_create` assigns primary key values correctly for non-existing rows.
    await Tweet.delete(force=True) # make them all non-existing
    for i in range(start, start + 100):
        tweet = await Tweet.objects().get_or_create(
            Tweet.tweet_id == i,
            defaults={'data': { 'message': 'hello world 4'} }
        )
        assert tweet.tweet_id == i, f"ID from get_or_create call is not correct for non-existing row. {tweet.tweet_id} != {i}"

    print(await Tweet.select())


if __name__ == '__main__':
    import asyncio
    asyncio.run(main())