Following Simons' comment, changing the schema to conform to SQL
expectations would involve having at least two tables. Consider your
second uniqueness criterion; Let's call those items a "Widget" so your
Widget table would be:
WIDGETS
{A, B, C, .} UNIQUE(A,B,C)
Let's call your items
On 27 Mar 2019, at 10:59pm, Thomas Kurz wrote:
> Now when inserting an item that already exists (according to the uniqueness
> definition above), the existing item should be updated with the new name and
> A, B, C properties.
Mmm. Your database design doesn't work the way SQL does. Your
> Can I ask what it is that you're trying to do ? This smacks of trying to add
> 1 to an existing value or something like that.
Sure. I have a table of items. Each item has a type, a name, and properties A,
B, C (and some more, but they're not relevant here).
I want to enforce that items of a
See https://www.sqlite.org/carray.html
--
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
Yes, but the problem is that I need to also retrieve the articles themselves.
If I were to embed the articles query inside the staff query (as you’ve shown),
the database would have to execute the article query twice.
> On Mar 27, 2019, at 4:42 PM, Keith Medcalf wrote:
>
>
> You mean
You mean something like this:
SELECT staff.* FROM staff, contributions
WHERE contributions.staff = staff.email
AND contributions.article IN (SELECT id FROM articles
WHERE publish_date <= CURRENT_TIMESTAMP
ORDER BY publish_date DESC LIMIT ?);
---
The fact that there's a Highway to Hell but only
I’ve commonly encountered cases where I have a many-to-many relationship, and I
would like to retrieve those relationships in a single query.
For example:
CREATE TABLE staff (
email TEXT PRIMARY KEY,
name TEXT
);
CREATE TABLE articles (
id INTEGER PRIMARY KEY,
title TEXT,
body TEXT,
On Wednesday, 27 March, 2019 12:04, Thomas Kurz wrote:
>> I wonder whether SQLite is treating each DELETE as a single
>transaction. Could you try wrapping the main delete in BEGIN ... END
>and see whether that speeds up the cascaded DELETE ? Would you be
>able to find timings (either in your
On Fri, 22 Mar 2019 09:25:24 -0500
Peng Yu wrote:
> Hi,
>
> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.
>
> https://github.com/sqlumdash/sqlumdash/
>
> Are there other packages
Integrity check is ok. I'm deleting using primary keys only, so it shouldn't be
an index problem either.
- Original Message -
From: Simon Slavin
To: SQLite mailing list
Sent: Wednesday, March 27, 2019, 19:25:17
Subject: [sqlite] Row locking sqlite3
On 27 Mar 2019, at 6:04pm, Thomas
On 27 Mar 2019, at 6:04pm, Thomas Kurz wrote:
> Ok, well very interesting and I'd never have had this idea, but indeed it
> works: within a transaction, it takes only a few seconds. This is very
> surprising as to me, a single DELETE statement is nothing more than that: a
> single atomic
> I wonder whether SQLite is treating each DELETE as a single transaction.
> Could you try wrapping the main delete in BEGIN ... END and see whether that
> speeds up the cascaded DELETE ? Would you be able to find timings (either in
> your code or in the command-line tool) and tell us whether
I'm not familiar with the C API, but the question I'll ask is this: How should
this work with triggers? Running a statement as simple as "delete from foo;"
could result in any number of different updates, deletes or inserts from any
number of different tables, so how should that be reported?
Keith Medcalf, (quoting Llyod), writes:
>> >How can I build the dll correctly?
> cl -DSQLITE_API=__declspec(dllexport) sqlite3.c -link -dll -out:sqlite3.dll
I do not claim here that “correctly” building the DLL must be done as
either I or Keith has suggested. However, the method using
Hi List,
I would like to propose a feature and I believe here is the best place.
Please keep in mind that I am ready to work on a patch for this feature if
needed, but I would like to discuss it here first.
I would like to propose a function (named `sqlite3_stmt_action` for the sake
of
On 27 Mar 2019, at 2:40pm, Thomas Kurz wrote:
> So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT
> (col1, col3, col4, col5) DO UPDATE".
Can't be done in a single instruction. The nearest you can get to this would
involve using triggers:
On Wednesday, 27 March, 2019 07:18, Lloyd wrote:
>I wish to build SQLite as a dll in Windows. As per the documentation
>here
>https://www.sqlite.org/howtocompile.html, I have executed the command
>cl sqlite3.c -link -dll -out:sqlite3.dll
>on Visual Studio x86 command prompt.
>It seems that the
Dear all,
I have a table with multiple (in this case 2) UNIQUE constraints:
UNIQUE (col1, col2)
UNIQUE (col1, col3, col4, col5)
Is it possible to use UPSERT twice? I have already tried some statements, but
neither of these were successful. This is what I want to achieve:
INSERT INTO ... ON
Lloyd writes:
I wish to build SQLite as a dll in Windows. As per the documentation here
https://www.sqlite.org/howtocompile.html, I have executed the command
cl sqlite3.c -link -dll -out:sqlite3.dll
on Visual Studio x86 command prompt.
It seems that the dll built doesn't export any symbols! I
Hi,
I wish to build SQLite as a dll in Windows. As per the documentation here
https://www.sqlite.org/howtocompile.html, I have executed the command
cl sqlite3.c -link -dll -out:sqlite3.dll
on Visual Studio x86 command prompt.
It seems that the dll built doesn't export any symbols! I checked it
On Wed, Mar 27, 2019 at 1:02 PM Simon Slavin wrote:
> On 27 Mar 2019, at 11:48am, Thomas Kurz wrote:
> Locking by rows is a slow operation. You first have to lock the entire
> database, then lock the row, then release the database. And each of those
> locks is a test-and-lock operation.
"The same name" does not imply "the same structure"
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von raanan barzel
Gesendet: Mittwoch, 27. März 2019 07:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite]
On 27 Mar 2019, at 11:48am, Thomas Kurz wrote:
> Imho quite simple: There are operations that take a long time. I observe this
> behavior especially with DELETE in combination with ON CASCADE DELETE. Can
> take half an hour, and meanwhile the database remains locked.
A timing that long
On 27 Mar 2019, at 6:46am, raanan barzel wrote:
> I would like to suggest a different default behavior (potentially as an
> option): "when the schema-name prefix is not used, then *all* the tables are
> accessed, potentially with a qualifier for distinctiveness."
This would break numerous
Imho quite simple: There are operations that take a long time. I observe this
behavior especially with DELETE in combination with ON CASCADE DELETE. Can take
half an hour, and meanwhile the database remains locked.
- Original Message -
From: Simon Slavin
To: SQLite mailing list
Good morning!
when trying to compile sqlite from Snapshop of the complete (raw) source tree
for SQLite version 3.27.2 and definingSQLITE_OMIT_VIEW and SQLITE_OMIT_TRIGGER
then compilation fails with the following messages:
###
sqlite3.c:19044:20: warning: 'sqlite3FixExprList' used but
The documentation says :Tables in an attached database can be referred
to using the syntax schema-name.table-name. If the name of the table is
unique across all attached databases and the main and temp databases,
then the schema-name prefix is not required. If two or more tables in
different
The other aspect of this is to wonder why OP wants row locking. They might
think that it's faster than locking the whole database, or that it will allow
their program to work without any pauses. Neither of these are necessarily
true.
___
Additionally, SQLite is not honoring the ON CONFLICT IGNORE, even if I add NOT
NULL ON CONFLICT IGNORE to each of the key fields. Instead, it is always
generating HaltIfNull operations, i.e. ON CONFLICT ABORT.
33 HaltIfNull 1299 2 2 vals.keyid 01 if r[2]=null halt
34
Tried both, no change with SQlite version 3.24. The reference t oval in the
WHERE clause causes SQLite to recompute the value.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Igor Tandetnik
Gesendet: Dienstag, 26. März
30 matches
Mail list logo
