-
-
Notifications
You must be signed in to change notification settings - Fork 653
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Ensure insert API has good tests for rowid and compound primark key tables #1873
Comments
Relevant TODO: datasette/datasette/views/table.py Lines 1131 to 1135 in c35859a
|
If a table has an auto-incrementing primary key, should you be allowed to insert records with an explicit key into it? I'm torn on this one. It's something you can do with direct database access, but it's something I very rarely want to do. I'm inclined to disallow it and say that if you want that you can get it using a writable canned query instead. Likewise, I'm not going to provide a way to set the |
I checked and SQLite itself does allow you to set the
|
Some notes on what Datasette does already https://latest.datasette.io/fixtures/tags.json?_shape=array returns: [
{
"tag": "canine"
},
{
"tag": "feline"
}
] That table is defined like this: CREATE TABLE tags (
tag TEXT PRIMARY KEY
); Here's a https://latest.datasette.io/fixtures/binary_data.json?_shape=array [
{
"rowid": 1,
"data": {
"$base64": true,
"encoded": "FRwCx60F/g=="
}
},
{
"rowid": 2,
"data": {
"$base64": true,
"encoded": "FRwDx60F/g=="
}
},
{
"rowid": 3,
"data": null
}
] CREATE TABLE binary_data (
data BLOB
); https://latest.datasette.io/fixtures/simple_primary_key has a text primary key: https://latest.datasette.io/fixtures/simple_primary_key.json?_shape=array [
{
"id": "1",
"content": "hello"
},
{
"id": "2",
"content": "world"
},
{
"id": "3",
"content": ""
},
{
"id": "4",
"content": "RENDER_CELL_DEMO"
},
{
"id": "5",
"content": "RENDER_CELL_ASYNC"
}
] CREATE TABLE simple_primary_key (
id varchar(30) primary key,
content text
); https://latest.datasette.io/fixtures/compound_primary_key is a compound primary key. https://latest.datasette.io/fixtures/compound_primary_key.json?_shape=array [
{
"pk1": "a",
"pk2": "b",
"content": "c"
},
{
"pk1": "a/b",
"pk2": ".c-d",
"content": "c"
}
] CREATE TABLE compound_primary_key (
pk1 varchar(30),
pk2 varchar(30),
content text,
PRIMARY KEY (pk1, pk2)
); |
Actually, for simplicity I'm going to say that you can always set the primary key, even for auto-incrementing primary key columns... but you cannot set it on pure |
One last case to consider: https://www.sqlite.org/withoutrowid.html
|
So maybe they're not actually worth worrying about separately, because they are guaranteed to have a primary key set. |
I think the key feature I need here is going to be the equivalent of |
Design decision: {
"rows": [{"id": 1, "title": "The title"}],
"ignore": true
} Or |
It's a bit surprising that you can send Three options:
I'm picking 3 - I'm going to change it to be called |
Also removed the rule that you cannot include primary keys in the rows you insert. And added validation that catches invalid parameters in the incoming JSON. And renamed "inserted" to "rows" in the returned JSON for return_rows: true
I forgot to document
I think my validation logic here will get caught out by the fact that datasette/datasette/views/table.py Lines 1151 to 1160 in 9bec7c3
|
Following:
I need to design and implement various edge-cases or primary keys:
rowid
The text was updated successfully, but these errors were encountered: