Comments (6)
That's odd, I wrote a test for this just now and it passes already:
def test_transform_preserves_rowids(fresh_db):
# Create a rowid table
fresh_db["places"].insert_all(
(
{"name": "Paris", "country": "France"},
{"name": "London", "country": "UK"},
{"name": "New York", "country": "USA"},
),
)
assert fresh_db["places"].use_rowid
previous_rows = list(
tuple(row) for row in fresh_db.execute("select rowid, name from places")
)
# Transform it
fresh_db["places"].transform(column_order=("country", "name"))
# Should be the same
next_rows = list(
tuple(row) for row in fresh_db.execute("select rowid, name from places")
)
assert previous_rows == next_rows
So maybe I'm wrong about the cause of that bug?
from sqlite-utils.
I tried bumping that up to 10,000 rows instead of just 3 but the test still passed.
from sqlite-utils.
I just noticed that the table where I encountered this bug wasn't actually a rowid
table after all - it had an id
column that was a text primary key.
The reason the rowid
was important is that's how the FTS mechanism in Datasette relates FTS entries to their rows.
But I tried this test and it passed, too:
def test_transform_preserves_rowids(fresh_db):
fresh_db["places"].insert_all(
[
{"id": "1", "name": "Paris", "country": "France"},
{"id": "2", "name": "London", "country": "UK"},
{"id": "3", "name": "New York", "country": "USA"},
],
pk="id",
)
previous_rows = list(
tuple(row) for row in fresh_db.execute("select rowid, id, name from places")
)
# Transform it
fresh_db["places"].transform(column_order=("country", "name"))
# Should be the same
next_rows = list(
tuple(row) for row in fresh_db.execute("select rowid, id, name from places")
)
assert previous_rows == next_rows
from sqlite-utils.
Oh! Maybe the row ID preservation here is a coincidence because the tables are created from scratch and count 1, 2, 3.
If I delete a row from the table and then insert some more - breaking the rowid
sequence - it might show the bug.
from sqlite-utils.
Yes! That recreated the bug:
> assert previous_rows == next_rows
E AssertionError: assert equals failed
E [ [
E (1, '1', 'Paris'), (1, '1', 'Paris'),
E (3, '3', 'New York'), (2, '3', 'New York'),
E (4, '4', 'London'), (3, '4', 'London'),
E ] ...
E
from sqlite-utils.
In working on this I learned that rowid
values in SQLite are way less stable than I had thought - in particular, they are often entirely rewritten on a VACUUM
:
https://www.sqlite.org/lang_vacuum.html#how_vacuum_works
The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.
So this fix wasn't as valuable as I thought. I need to move away from ever assuming that a rowid
is a useful foreign key for anything.
from sqlite-utils.
Related Issues (20)
- Move plugin directory into documentation
- `sqlite-utils transform` removes the `AUTOINCREMENT` keyword
- Pyhton 3.12 Bug report HOT 2
- Insert fails with `Error: Python int too large to convert to SQLite INTEGER`; can we use `NUMERIC` here? HOT 1
- str and int as aliases for text and integer HOT 2
- pyright and mypy showing access error messages for basic sqlite-utils usage
- Attached database tables representable by `Table`
- `table.upsert/upsert_all` fails to write row when `not_null` is absent and the schema definition includes `not_null` HOT 3
- `.transform()` effect on triggers and indices
- Automatic JSON de-serialization
- Windows: Correct way to initialize spatialite?
- sqlite-utils command fails when used with xargs HOT 2
- Drop support for Python 3.7 HOT 1
- create-table command does not handle compound primary keys HOT 2
- insert-files accept multiple --pk HOT 1
- Command to generate a visual schema diagram?
- Query with duplicate output column names drop/overwrites duplicate colums
- Doublebyte content is unicode-escaped in JSON output
- Allow an index to be dropped
- Allow mytable.create_index() to ignore an already existing index
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlite-utils.