Re: [sqlite] How to circumvent UNIQUE constraint
On 22 Jan 2017, at 2:40am, Keith Medcalf wrote: > My suggestion would be to forgo the artificial relative position being > computed by the application and replace it with the actual data used to > determine the ordering, and add an appropriate ORDER BY when retrieving the > data. Or make the field REAL instead of INTEGER. Then you can insert a new row 'between' any two existing rows by taking the mean of their two values. Well, down to the resolution of REAL, of course. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Saturday, 21 January, 2017 13:45 James K. Lowden wrote: > On Sat, 21 Jan 2017 19:33:06 +0200 > R Smith wrote: > > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > > INSERT INTO desktops ... new row for indexNo X ... ; > > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; > unless > > FOREIGN KEY indexNo references foo(bar) In this case changing the overloaded relative-record-number indexNo would not work at all, even if the update were atomic. > or > FOREIGN KEY bar references desktops(indexNo) ON UPDATE CASCADE would fix this, of course. > I don't think enough functionality is exposed to create a generalized > function that would just do the right thing. One can imagine a > C function sqlite3_exec_update that > > 1. determines the affected columns > 2. finds any applicable constraints > 3. drops the constraints > 4. begins a transaction > 5. executes the update > 6. re-adds the contraints > 7. commits > > But just for starters ALTER TABLE does not support constraints, > and SQLITE_MASTER doesn't reflect constraint definitions. > > DRH suggests renaming the table or using an index instead. I'm not > sure renaming the table works in the presence of foreign key > enforcement (so that would have to be touched, too). Even if > indexes are used, the index definitions are not exposed in a way that > the could be dropped and re-created under programatic control without > parsing the SQL. Both approaches are inefficient if only a small > proportion of rows are affected. Both impose unnecessary complexity on > the user. > > The only place all the above information is readily available is inside > the SQLite engine. There the SQL is parsed and all applicable > constraints are exposed in binary form. A simplistic decision was made > early on to enforce constraints on a row-by-row basis. That decision > was defensible at the time. As SQLite has grown in sophistication -- > WAL, foreign keys, CTE, recursion -- lack of atomic update looms > more and more as an important defect. My suggestion would be to forgo the artificial relative position being computed by the application and replace it with the actual data used to determine the ordering, and add an appropriate ORDER BY when retrieving the data. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 21 Jan 2017 18:14:06 + Simon Slavin wrote: > It is a failure of atomicity in SQLite semantics. > > This is one I do feel is a bug in SQLite. Thank you for your support. I feel it's important to understand it's a bug, not a feature. > I think I?ve seen other SQL implementations where you can state at > which point the constraints are enforced. Every other SQL DBMS I'm aware of handles things like primary key constraint and unique constraint correctly out of the box. Any constraint that affects only one table can be enforced atomically, i.e. after each SQL statement. You need deferred constraint enforcement for things that can't be expressed atomically in SQL. For example, assume two tables, orders and order_items, with two rules: 1. every order must have at least one order_item 2. every order_item must belong to an order In creating a new order, these requirements are impossible to fill simultateously, because INSERT affects only one table. A workaround like a permanent faux_item introduces needless compexity. Deferred constraint enforcement can apply contraints after both inserts. In Tutorial-D, Date uses a comma-operator to chain database updates together, to indicate that the combination is atomic. IMO deferred constraints are way outside the scope of SQLite. It's complex. Simple type enforcement and correct constraint enforcement would serve users better. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 21 Jan 2017 19:33:06 +0200 R Smith wrote: > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > INSERT INTO desktops ... new row for indexNo X ... ; > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; unless FOREIGN KEY indexNo references foo(bar) or FOREIGN KEY bar references desktops(indexNo) I don't think enough functionality is exposed to create a generalized function that would just do the right thing. One can imagine a C function sqlite3_exec_update that 1. determines the affected columns 2. finds any applicable constraints 3. drops the constraints 4. begins a transaction 5. executes the update 6. re-adds the contraints 7. commits But just for starters ALTER TABLE does not support constraints, and SQLITE_MASTER doesn't reflect constraint definitions. DRH suggests renaming the table or using an index instead. I'm not sure renaming the table works in the presence of foreign key enforcement (so that would have to be touched, too). Even if indexes are used, the index definitions are not exposed in a way that the could be dropped and re-created under programatic control without parsing the SQL. Both approaches are inefficient if only a small proportion of rows are affected. Both impose unnecessary complexity on the user. The only place all the above information is readily available is inside the SQLite engine. There the SQL is parsed and all applicable constraints are exposed in binary form. A simplistic decision was made early on to enforce constraints on a row-by-row basis. That decision was defensible at the time. As SQLite has grown in sophistication -- WAL, foreign keys, CTE, recursion -- lack of atomic update looms more and more as an important defect. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 21 Jan 2017 11:54:57 +0100, Cecil Westerhof
wrote:
>I have the following (work in progress) table:
>CREATE TABLE desktops(
>nameTEXTNOT NULL PRIMARY KEY,
>indexNo INTEGER NOT NULL UNIQUE,
>value TEXTNOT NULL UNIQUE,
>waitSeconds INTEGER NOT NULL
>);
>
>?I want to insert a record in front of? the others, so indexNo has to be
>increased with one for all records. I would think that this would work:
>UPDATE desktops
>SET indexNo = indexNo + 1
>
>But it does not, it gives:
>Error: UNIQUE constraint failed: desktops.indexNo
>
>?How can I make this work?
Considering there is no constraint on indexNo with respect to
negative or zero values, I would suggest:
INSERT INTO desktops (name,indexNo,value,waitSeconds)
VALUES ('thename',(SELECT min(indexNo) FROM desktops) - 1,
'thevalue',thewaitseconds);
--
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On 21 Jan 2017, at 4:32pm, James K. Lowden wrote: > Cecil Westerhof wrote: > >> I would think that this would work: >> UPDATE desktops >> SET indexNo = indexNo + 1 >> >> But it does not, it gives: >> Error: UNIQUE constraint failed: desktops.indexNo > > It should work. It does work in other DBMSs, but it doesn't in > SQLite. It is a failure of atomicity in SQLite semantics. This is one I do feel is a bug in SQLite. The command >> UPDATE desktops SET indexNo = indexNo + 1 can lead to violations of the UNIQUE constraint but whether it does or not is an implementation detail (depends which order the rows are processed) and not under user-control. So the proper requirement is that the UNIQUE check be made at the end of the transaction. And at the end of the transaction there would be no violations, no matter in which order the SQL engine chose to process rows. Unfortunately changing SQLite to check the constraints at the end of the transaction rather than as each change is made would require a lot of programming. Maybe it’s one for SQLite4. I think I’ve seen other SQL implementations where you can state at which point the constraints are enforced. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On 2017/01/21 12:54 PM, Cecil Westerhof wrote: I have the following (work in progress) table: CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); I want to insert a record in front of the others, so indexNo has to be increased with one for all records. I would think that this would work: UPDATE desktops SET indexNo = indexNo + 1 But it does not, it gives: Error: UNIQUE constraint failed: desktops.indexNo How can I make this work? My favourite way (only needed in SQLite as this will work in most other DBs): UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; INSERT INTO desktops ... new row for indexNo X ... ; UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; With X being the indexNo at which you wish to Insert the new row. I like this because it's simple, quick, and always works without the need to calculate anything. If this table is really big (millions of rows) it /might/ be faster to just drop and recreate the index, you should test the time difference. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 21 Jan 2017 11:54:57 +0100 Cecil Westerhof wrote: > I would think that this would work: > UPDATE desktops > SET indexNo = indexNo + 1 > > But it does not, it gives: > Error: UNIQUE constraint failed: desktops.indexNo It should work. It does work in other DBMSs, but it doesn't in SQLite. It is a failure of atomicity in SQLite semantics. As DRH mentions, one workaround is to drop the constraint temporarily. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
Here's one possibility (simplified table for example): create table desktops( indexno integer not null unique, nametextnot null primary key ); insert into desktops values (1,'CompA'), (2,'CompB'), -- we want to insert new record here bumping all above by one (3,'CompD'), (4,'CompE'); select * from desktops order by indexno; --BEFORE -- Assuming largest indexno is initially N (in this example 4) -- Add N+1 (or N+x where x > 0) to all records over and including the one spot you want freed (in this example 3) -- Subtract N from all over N+1 you added previously -- Insert the new record into the now empty slot begin; update desktops set indexno = indexno + 5 where indexno >=3; update desktops set indexno = indexno - 4 where indexno > 5; insert into desktops values(3,'CompC'); end; select * from desktops order by indexno; --AFTER -Original Message- From: Cecil Westerhof Sent: Saturday, January 21, 2017 12:54 PM To: SQLite mailing list Subject: [sqlite] How to circumvent UNIQUE constraint I have the following (work in progress) table: CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); I want to insert a record in front of the others, so indexNo has to be increased with one for all records. I would think that this would work: UPDATE desktops SET indexNo = indexNo + 1 But it does not, it gives: Error: UNIQUE constraint failed: desktops.indexNo How can I make this work? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault when inserting ascii text using python-Django with Sqlite3
Quick note: the mailing list doesn't accept attachments. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of SASSOULAS Pierre 250112 Sent: Friday, January 20, 2017 11:25 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Segfault when inserting ascii text using python-Django with Sqlite3 Hi, I've encountered a segfault in a python-django project when inserting seemingly random innocent looking data into an sqlite3 databse. It happen while testing a custom command in Django unit-test. Version : python --version Python 2.7.12 sqlite3 --version 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f pip freeze Django==1.10.5 Here's the error in gdb : gdb python (gdb) run manage.py test Starting program: /home/user/workspace/sqlitesegfaultbug/.env/bin/python manage.py test Program received signal SIGSEGV, Segmentation fault. 0x74545ad8 in ?? () from /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 The bug happened specifically for a String between "Sonde de platine" and "Thermohygromètre" initially. I enclosed a sanded down django project in case you want to reproduce. The problem happen on another string, but I don't think that a particular string is the problem. There is logging in place that could help diagnose the problem. Step to reproduce once the project is unzipped : virtualenv .env source .env/bin/activate pip install --upgrade pip pip install --requirement=requirements.txt python manage.py test Thank in advance and thank you for the time you already invested in sqlite, Regards, Pierre Sassoulas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On 1/21/17, Cecil Westerhof wrote: > I have the following (work in progress) table: > CREATE TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value TEXTNOT NULL UNIQUE, > waitSeconds INTEGER NOT NULL > ); > > I want to insert a record in front of the others, so indexNo has to be > increased with one for all records. I would think that this would work: > UPDATE desktops > SET indexNo = indexNo + 1 > > But it does not, it gives: > Error: UNIQUE constraint failed: desktops.indexNo > > How can I make this work? BEGIN; ALTER TABLE desktops RENAME TO desktops_old; CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); INSERT INTO desktops SELECT name, indexNo+1, value, waitSeconds FROM desktops_old; DROP TABLE desktops_old; COMMIT; The above is just the first method that comes to mind. There are certainly others. For example, you might enforce the uniqueness of indexNo with a separate UNIQUE index, then simply DROP the index before the update and recreate it afterwards. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :memory: + attach WAL?
On 1/20/17, Tim Uy wrote: > Hi, I currently start a :memory: database and then attach a physical > database and then set wal mode by using > > PRAGMA mydb.journal_mode=WAL > > 1. Is this safe? (I noticed default is 'delete') DELETE is the default because it works in a wider variety of situations, such as on systems that lack shared memory and on network filesystems. > 2. Can I do it automatically with the ATTACH? So I don't have to wait 150 > ms for the WAL PRAGMA statement? WAL mode is a property of the database file. Changing in and out of WAL mode is a transaction, which is why it take 150ms (for the fsync()s to run). But once you set a database file into WAL mode it should stay there until it is changed again. You should not need to set WAL mode each time you open it. I do not recall a way to create a new database file that comes up in WAL mode. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unusual behavior implementing an "upsert" statement
It seems this is intended behavior. From sqlite.org/lang_conflict.html
under REPLACE:
> If a NOT NULL constraint violation occurs, the REPLACE conflict
resolution replaces
> the NULL value with the default value for that column, or if the column
has no default
> value, then the ABORT algorithm is used.
Sorry for the trouble. Thanks,
Jonathan Koren
On Fri, Jan 20, 2017 at 9:59 PM, Jonathan Koren wrote:
> Hello everyone,
>
> I've been experimenting with an interesting form of statement that tries
> to implement an "upsert" operation and came across some unusual behavior.
> For context, my table has a notion of a "logical key" aside from the
> primary key, and this is what determines whether to update or insert a row.
> For simplicity's sake, we can use this table:
>
> CREATE TABLE demo (
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> guid TEXT UNIQUE NOT NULL,
> someInt INTEGER NOT NULL DEFAULT -1
> );
>
> The "upsert" statement looks like this:
>
> WITH new (guid, someInt) AS (VALUES('abc', 777))
> INSERT OR REPLACE INTO demo (id, guid, someInt)
> SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
> FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
>
> The idea is to maintain ROWID and merge the new and old values if I have a
> matching guid, otherwise insert a new row. (I realize there are problems
> with this approach and it's better to just use multiple statements in a
> transaction. Roll with me.)
>
> INSERT INTO demo (guid, someInt) VALUES ('abc', 7);
> INSERT INTO demo (guid) VALUES ('def');
> INSERT INTO demo (guid, someInt) VALUES ('abc', null);
> Error: NOT NULL constraint failed: demo.someInt
>
> select * from demo;
> id guidsomeInt
> -- -- --
> 1 abc 7
> 2 def -1
>
> --So far nothing unexpected. Let's see about the upsert...
>
> WITH new (guid, someInt) AS (VALUES('def', 99))
> INSERT OR REPLACE INTO demo (id, guid, someInt)
> SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
> FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
> --UPDATES OK
>
> WITH new (guid, someInt) AS (VALUES('ghi', 1234))
> INSERT OR REPLACE INTO demo (id, guid, someInt)
> SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
> FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
> --INSERTS OK
>
> select * from demo;
> id guidsomeInt
> -- -- --
> 1 abc 7
> 2 def 99
> 3 ghi 1234
>
> --Now the part that has me baffled
>
> WITH new (guid, someInt) AS (VALUES('xyz', null))
> INSERT OR REPLACE INTO demo (id, guid, someInt)
> SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
> FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
> --No Error?!
>
> select * from demo;
> id guidsomeInt
> -- -- --
> 1 abc 7
> 2 def 99
> 3 ghi 1234
> 4 xyz -1--???
>
> --How did it insert a new row with the default value for someInt?
> --Here's what the select produces for the insert:
>
> WITH new (guid, someInt) AS (VALUES('o_O?', null))
> SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
> FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
> id guidIFNULL(new.someInt, old.someInt)
> -- --
> o_O?
>
> --But wait...
>
> INSERT INTO demo (id, guid, someInt) VALUES (null, 'o_O?', null);
> Error: NOT NULL constraint failed: demo.someInt
>
>
> It seems this statement circumvents the NOT NULL constraint failed error
> and also inserts the proper default value instead of null. Is this behavior
> expected? If it is, is it also documented somewhere? If it's not, is this a
> bug?
>
> Thanks for your attention,
> Jonathan Koren
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unusual behavior implementing an "upsert" statement
Hello everyone,
I've been experimenting with an interesting form of statement that tries to
implement an "upsert" operation and came across some unusual behavior. For
context, my table has a notion of a "logical key" aside from the primary
key, and this is what determines whether to update or insert a row. For
simplicity's sake, we can use this table:
CREATE TABLE demo (
id INTEGER PRIMARY KEY AUTOINCREMENT,
guid TEXT UNIQUE NOT NULL,
someInt INTEGER NOT NULL DEFAULT -1
);
The "upsert" statement looks like this:
WITH new (guid, someInt) AS (VALUES('abc', 777))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
The idea is to maintain ROWID and merge the new and old values if I have a
matching guid, otherwise insert a new row. (I realize there are problems
with this approach and it's better to just use multiple statements in a
transaction. Roll with me.)
INSERT INTO demo (guid, someInt) VALUES ('abc', 7);
INSERT INTO demo (guid) VALUES ('def');
INSERT INTO demo (guid, someInt) VALUES ('abc', null);
Error: NOT NULL constraint failed: demo.someInt
select * from demo;
id guidsomeInt
-- -- --
1 abc 7
2 def -1
--So far nothing unexpected. Let's see about the upsert...
WITH new (guid, someInt) AS (VALUES('def', 99))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--UPDATES OK
WITH new (guid, someInt) AS (VALUES('ghi', 1234))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--INSERTS OK
select * from demo;
id guidsomeInt
-- -- --
1 abc 7
2 def 99
3 ghi 1234
--Now the part that has me baffled
WITH new (guid, someInt) AS (VALUES('xyz', null))
INSERT OR REPLACE INTO demo (id, guid, someInt)
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
--No Error?!
select * from demo;
id guidsomeInt
-- -- --
1 abc 7
2 def 99
3 ghi 1234
4 xyz -1--???
--How did it insert a new row with the default value for someInt?
--Here's what the select produces for the insert:
WITH new (guid, someInt) AS (VALUES('o_O?', null))
SELECT old.id, new.guid, IFNULL(new.someInt, old.someInt)
FROM new LEFT JOIN demo AS old ON new.guid = old.guid;
id guidIFNULL(new.someInt, old.someInt)
-- --
o_O?
--But wait...
INSERT INTO demo (id, guid, someInt) VALUES (null, 'o_O?', null);
Error: NOT NULL constraint failed: demo.someInt
It seems this statement circumvents the NOT NULL constraint failed error
and also inserts the proper default value instead of null. Is this behavior
expected? If it is, is it also documented somewhere? If it's not, is this a
bug?
Thanks for your attention,
Jonathan Koren
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] :memory: + attach WAL?
Hi, I currently start a :memory: database and then attach a physical database and then set wal mode by using PRAGMA mydb.journal_mode=WAL 1. Is this safe? (I noticed default is 'delete') 2. Can I do it automatically with the ATTACH? So I don't have to wait 150 ms for the WAL PRAGMA statement? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault when inserting ascii text using python-Django with Sqlite3
Hi, I've encountered a segfault in a python-django project when inserting seemingly random innocent looking data into an sqlite3 databse. It happen while testing a custom command in Django unit-test. Version : python --version Python 2.7.12 sqlite3 --version 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f pip freeze Django==1.10.5 Here's the error in gdb : gdb python (gdb) run manage.py test Starting program: /home/user/workspace/sqlitesegfaultbug/.env/bin/python manage.py test Program received signal SIGSEGV, Segmentation fault. 0x74545ad8 in ?? () from /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 The bug happened specifically for a String between "Sonde de platine" and "Thermohygromètre" initially. I enclosed a sanded down django project in case you want to reproduce. The problem happen on another string, but I don't think that a particular string is the problem. There is logging in place that could help diagnose the problem. Step to reproduce once the project is unzipped : virtualenv .env source .env/bin/activate pip install --upgrade pip pip install --requirement=requirements.txt python manage.py test Thank in advance and thank you for the time you already invested in sqlite, Regards, Pierre Sassoulas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 2017-01-21 at 11:54 +0100, Cecil Westerhof wrote: > I have the following (work in progress) table: > CREATE TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value TEXTNOT NULL UNIQUE, > waitSeconds INTEGER NOT NULL > ); > > I want to insert a record in front of the others, so indexNo has to > be > increased with one for all records. I would think that this would > work: > UPDATE desktops > SET indexNo = indexNo + 1 > > But it does not, it gives: > Error: UNIQUE constraint failed: desktops.indexNo > > How can I make this work? > I don't think this will work in a single SQL statement. If you start with the largest value of indexNo and work in descending order, it should work. However, this would typically be done in a procedural loop where you can depend on the ordering of a cursor. Maybe somebody knows a clever SQL trick to do it in a single statement? The problem is that you could build a subquery to return the "hole", i.e. the next indexNo to update, but you cannot modify the same table which is used in a subquery of the same UPDATE statement. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to circumvent UNIQUE constraint
I have the following (work in progress) table: CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); I want to insert a record in front of the others, so indexNo has to be increased with one for all records. I would think that this would work: UPDATE desktops SET indexNo = indexNo + 1 But it does not, it gives: Error: UNIQUE constraint failed: desktops.indexNo How can I make this work? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
