SQLite Forum

Timeline
Login

48 forum posts by user marcob

2021-10-19
14:04 Reply: SQLite Page Cache Subsystem as Library (artifact: 169655ddc5 user: marcob)

There is the LSM extension which is working as a key value store. But I think nobody is using it.

2021-10-08
14:01 Delete reply: mmap and blobs (artifact: 989d200b75 user: marcob)
Deleted
13:34 Reply: mmap and blobs (artifact: 5b2976f4ea user: marcob)

To my understanding there is a mmap option for the db. So it can be that the blob pointer is memory mapped in that case.

2021-10-07
06:21 Reply: journal file created when journal_mode=OFF (artifact: 6e6d8fd1b4 user: marcob)

Have you looked into https://www.sqlite.org/pragma.html#pragma_locking_mode? With locking node exclusive there is only an in-memory Wal log. So you get only your database file.

2021-10-06
16:35 Reply: Insert within CTE (artifact: 8ac7f208b3 user: marcob)

You could use an insert together with RETURNING. Could you describe what you want to archive?

2021-09-30
21:52 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: ed9d97410b user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
)
WHERE counter > 1

or 

WITH counted_persons AS (
  SELECT 
    *,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1
21:49 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 7292966730 user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
)
WHERE counter > 1

or 

WITH counted_persons AS (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1
21:25 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 92f3446f4e user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name
)
WHERE counter > 1

or 

WITH counted_persons AS (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1
21:24 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 694e4d5eac user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name
)
WHERE counter > 1

or 

WITH counted_persons(person_no, name, birthday, counter) AS (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1
21:23 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 474ab665cb user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name
)
WHERE counter > 1

or 

WITH counted_persons(person_no, name, birthday, counter) AS (
  SELECT 
    person_no,
    name,
	birthday,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1
20:56 Reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 5cdf5c2b90 user: marcob)
You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
  ORDER BY name
)
WHERE counter > 1
2021-09-23
07:35 Post: Best way to use carray (artifact: 2c80593154 user: marcob)

Hello

I have a table foo:

CREATE TABLE foo ( sourceId INTEGER, data TEXT, id INTEGER PRIMARY KEY, );

Now I query the table by

SELECT id, data FROM foo WHERE sourceId IN carray(?1)

The carray is normally a smaller subset but sometimes it can be a really large subset too. After looking into the carray source my understanding is that there is no index optimization. I am not sure if Sqlite is maybe creating a temporary index. Would be there an advantage to provide a sorted_carray implementation?

Best regards, Marco

2021-09-17
06:32 Reply: about "strict" mode (artifact: 98d5032ab4 user: marcob)

Please read the documentation of the session extension. Primary keys are not arbitrary. Your second example would simply not worked like intended because if you merge with external data the rowid is not known. Sory for the short answer, I explained it already in a different post in this thread. There is also a whole thread about it.

2021-09-15
12:56 Reply: about "strict" mode (artifact: 502f36cd81 user: marcob)

But implicit rowids behave differently to explicit rowids. For example they don't survive a vacuum. ;-)

12:54 Reply: about "strict" mode (artifact: 5f97081b26 user: marcob)

RowIds are not stable if the are generated by the DB. I used sessions for an staging area. So I was reverting them, then applied the external changes and applied the reverted once again. If the external changes were semantically the same as my changes it could not work because the primary key was a rowid. The primary key had to be a key which is the same for the external changes and my changes.

11:47 Reply: about "strict" mode (artifact: c0dac2506e user: marcob)

I think I was not clear enough. If I want to use an other key as primary key I cannot use the rowid anymore as primary key. Why I want to do that? Because first it is more clear what the primary key is and second the session extension is not working very well with row ids. ;-)

2021-09-14
09:11 Reply: about "strict" mode (artifact: 7ca4d56936 user: marcob)

What about adding a column type ROWID?

rowid is used by many internal api's and I use it as primary key very often. But it is not semantically my primary key. I use then a unique index for that. But then I run into problems because the session extension need that primary key. So for I would find it handy to define a ROWID column which is then created as a special integer index. I use this index very often to implement a handle for fetching the row. Having integer handles is a really powerful pattern.

08:51 Reply: Retrieve a record and delete it (artifact: 97566359fa user: marcob)

Like Harald alrady mentioned since 3.35.0 you can write:

DELETE FROM dickiedee WHERE yummy=fruitcake RETURNING whopee

This is really useful and I do it all the time.

2021-09-05
19:44 Reply: STRICT tables and UNIQUE indices (artifact: 9fc3741a8a user: marcob)

Actually I think a distict index is what I really want. Thank you for your clarification.

To some other responders. I really appreciate every response but I don't think metaphysical discurses are not really helpful. But if you wish we can have a deeper discussion about metaphysical terms like 'truth', 'logic' etc. in a historical context. ;-)

2021-09-01
15:15 Reply: STRICT tables and UNIQUE indices (artifact: eca683a013 user: marcob)

I think a non distinct null parameter would be fine.

My general argumentation is not about a closed argumentation system(standard) like I think you propose but by problems who are embedded into their context. I think good tools can be adapted to their context. It is not always easy and sometimes not even worth it. But you can argument about it.

So my argumentation is that it is not important if it is sensible to the standard(curch ;-) but to the context it is used. So in my context a customization would be very useful. You can argument that my context is very special. Actually I cannot see so many cases there you want a unique index which is not constrained on null. And for that you can always use a partial index which makes it much more clearer. ;-)

My point was not that the STRICT mode would change the behavior but would add some customization for null values.

Anyway it was only an idea and I think I need to convince my company to pay for it which is not so probable. ;-)

14:59 Reply: STRICT tables and UNIQUE indices (artifact: 3037d36928 user: marcob)

This is what I doing all the time but I would say it is not making the design easier to read and maintain. ;-)

12:19 Reply: STRICT tables and UNIQUE indices (artifact: 4ddf1d23b4 user: marcob)

I think we misunderstand each other. I was referencing https://sqlite.org/faq.html#q26.

So null is distinct for many but not in all databases. I am asking for a non distinct null value in unique indices as 'non value'.

I know how to handle the -1 case but this is not helping if -1 is a valid value. I try to give an example. You can have an value which is an int64 and you use it as an key. Lets assume that all integers can be a valid value and there can be the case of a non value. This could be mapped to null but can't because 'non value' is not distinct in most cases. It is only a special case. So you cannot use null for that case which comes up very often in many pattern. I know ways to work around it. I thought that would be clear.

Why I was coming up with it in the strict table case? There you can set a column to integer and it would be not anymore possible to workaround it with an other type like an empty bytearray. You have to add an extra column.

09:48 Post: STRICT tables and UNIQUE indices (artifact: 4ed2acea07 user: marcob)

First thank you for this new table mode! It will be very helpful, especially the primary key mode which cannot not null anymore.

What about unique indices and null. I know that Sqlite like to follow Postgresql but I already run into some bugs because of it. Maybe there could be some STRICT unique indices too which would only allow one null value in an index. Very often I work around it by defining some special value like -1 but sometimes this is not easy and using null as unique "non value" would be very helpful.

Thank you again for the STRICT tables. :-)

Marco

2021-07-26
21:36 Reply: how can i identify the data is just inserted into the database or updated the existed item when i use upsert-clause (artifact: 8c9d574068 user: marcob)

The update hooks work but I think it's easier to update first and if there was nothing updated to insert. You can check the update by the last changed rowid which can be reseted to -1 before you update or by RETURNING. If you use the update hook you have to filter the table or activate or deactivate it around the CALL. RETURNING is working with non rowid tables which the upate hook is not. I used the update hook but RETURNING is in my view much simpler.

11:03 Reply: sqlite3_carray_bind and constness (artifact: e3be05933c user: marcob)

I don't care if gravity is real. Real is a metaphysical word. ;-)

Gravity is useful.

I studied a little bit about metaphysics which in the common term often is described by a two world model of reality and perception. If you create two worlds you need words to connect them like 'true', 'false' and 'sense' etc.. Instead of them you can use words like knowledge, experience etc. which are practical very similar but still shows that they are context dependent even if their context is huge. This stop also people who like to argument that if you cannot show it's 100% true(which you never can if somebody is not stopping argumenting) it must be false(and for them useless).

I don't mean anything goes. Empirical evidence shows that you cannot stand with one leg on the ground as your other legs tries to find ground on the other side of the cliff. You simply will fall. Sometimes this will take some time so you can tell everybody it is fine but ...

07:51 Reply: sqlite3_carray_bind and constness (artifact: 430a264db4 user: marcob)

I call sentences like 'this is true', 'that is false', 'that makes sense' "vulgar metaphysics". Many Programmer like to use it all the time. It is very often a good sign you get conformation bias.

I prefer people who say 'I wrote a test and the values does not match. After some time I could pin it down to that function. After fixing it I wrote some more test to cover the cornercases.'

Many programmers love to use metaphysics and logic in a quite fundamentalistic way. I my experience this is not very productive. ;-)

2021-07-25
21:02 Reply: sqlite3_carray_bind and constness (artifact: 228e721788 user: marcob)

Actually I care about undefined behaviour. I have seen already some quite nasty bugs because the compiler can do what ever it wants for UB. I thought it is the same for C. Personally I dislike discussions about code beauty. In my experience people who love to discuss code beauty are similar unproductive like people who thinks tests are useless and spending 50% of your time debugging is normal. ;-)

Personally I think const as a contract in interfaces is a very useful information. So if somebody is doing a const cast and is changing the data...

And if you want to get philosophical we can exchange our thoughts about pragmatism as an ideology. But I think this is still preferable to the vulgar metaphysics many programmers use in their arguments. ;-)

I will follow your advise and patch carray.

19:11 Reply: sqlite3_carray_bind and constness (artifact: f4cbf4fc88 user: marcob)

But then you run into int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void()(void)) where the pointer is not const. I imagine adding sqlite3_bind_const_pointer will result into internal changes. sqlite3_bind_blob is const but ...

2021-07-22
10:29 Post: sqlite3_carray_bind and constness (artifact: 891c2e5835 user: marcob)

sqlite3_carray_bind is using a non const pointer which is forcing a copy if you use const data. You can cast to non const but then you are in undefined behavior area. A const version like sqlite3_const_carray_bind could prevent bugs in that erroneous usage. Or is there an other way?

2021-07-07
14:10 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: 688de0aec3 user: marcob)

I can imagine that reading and comparing can be produce quite some overhead for many cases. So I am not so sure about it's done by default. My use case is the synchronization of source text files where only minimal changes are done. So very often many tables are not changed. Otherwise I would have not bothered. Maybe a language extension like UPDATE CHANGED or INSERT CHANGED would be a nice idea? I think I will go now the safe route like proposed in the UPSERT documentation and use the WHERE clause.

2021-07-06
09:30 Edit: Shortcut to change a non changing update or upsert in a noop (artifact: 03c59d5bca user: marcob)
Hello

I try to avoid updates or upsert which are not changing values. So I write
very often (quite long) WHERE clauses. Is there maybe a more elegant way like a pragma to always check writes to avoid this code?


here a simple example:

INSERT INTO types(importId, name,  accessSemantics, sourceId)
VALUES(?1, ?2, ?3, nullif(?4, -1)) 
ON CONFLICT DO UPDATE SET prototypeId=excluded.prototypeId, accessSemantics=excluded.accessSemantics, sourceId=excluded.sourceId 
WHERE prototypeId IS NOT excluded.prototypeId OR accessSemantics IS NOT excluded.accessSemantics OR sourceId IS NOT excluded.sourceId 
RETURNING typeId

Thank you, Marco
09:06 Post: Shortcut to change a non changing update or upsert in a noop (artifact: cb052309a4 user: marcob)
Hello

I try to avoid updates or upsert which are not changing values. So I write
very often (quite long) WHERE clauses. Is there maybe a more elegant way like a pragma to always check writes to avoid this code?


here a simple example:

INSERT INTO types(importId, name,  accessSemantics, sourceId)
VALUES(?1, ?2, ?3, nullif(?4, -1)) 
ON CONFLICT DO UPDATE SET prototypeId=excluded.prototypeId, accessSemantics=excluded.accessSemantics, sourceId=excluded.sourceId 
WHERE prototypeId<>excluded.prototypeId OR accessSemantics<>excluded.accessSemantics OR sourceId<>excluded.sourceId 
RETURNING typeId

Thank you, Marco
2021-03-20
14:37 Reply: Constexpr parsing of sql statement (artifact: bbdc93c3d3 user: marcob)

To my understanding the parser is generated. So it could generate constexpr C++. In C++ 20 you have even constexpr dynamic allocations so I guess it can work. But I decided now to write my own simple 'parser'. Should be enough for what I need. But it would be nice if they add constexpr to C too.

2021-03-19
15:39 Reply: Constexpr parsing of sql statement (artifact: df3d19fed9 user: marcob)

To my understanding this is more a very simple constexpr database. Actually constexpr was already introduced in C++11 but hardly useful. With C++ 20 they even added dynamic allocations. So why not use the C parser? C++ doesn't mean that you have to use every C++ feature. ;-)

14:56 Post: Constexpr parsing of sql statement (artifact: bd01343927 user: marcob)

Hello

I know the Sqlite parser is generated and it would be nice to have some information at compile time. So would it be possible to change the parser to use constexpr so I could get some information about the statements like the count of columns of the select statement?

Best regards, Marco

2021-03-15
11:28 Post: SELECT and RETURNING in one statement (artifact: 5e1ef49f35 user: marcob)

Hello

I want to optimize my code a little bit. So far I was inserting always to trigger a hook for the rowid but I think it is not so smart because I will always write even if 90% of the entries are not changed. So I thought to test if they are equal. I think one lookup would be better than two.

CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''))

I know the following code is not working but I think it shows my intention.

SELECT id FROM translatables WHERE translationTextId=?2 UNION ALL INSERT INTO translatables (id, translationTextId) (?1, ?2) ON CONFLICT(translationTextId) DO NOTHING RETURNING id; LIMIT 1

So is there a way to archive that? I found this PostgreSql solution but to my understanding this is not working in Sqlite.

Later I use the ids to delete all not inserted or "updated" rows.

2021-02-11
16:43 Reply: Returning and insert with conflict (artifact: 6b14076446 user: marcob)

Yes, I could add an SELECT before every INSERT but that would increase the complexity of code. Maybe a CTE is working.

WITH cte(rowid) AS (INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid UNION ALL SELECT rowid FROM mytable WHERE foo = ?) SELECT rowid FROM cte

But I am not sure you can even do that. Anyway in this case I would go for the dummy column because the code would be more readable.

13:14 Reply: Returning and insert with conflict (artifact: c2b2622b34 user: marcob)

I don't know if Sqlite is optimizing the writing case where the old and the new value is the same? I am not even sure if that has an advantage but I think it would be very similar to this because the row would be not changed.

12:57 Post: Returning and insert with conflict (artifact: 6c153c0a6e user: marcob)
Hello

I have recurring pattern where I overwrite something and then remove the non updated and inserted rows. For that I get all rowids and then at the end use carray to delete the ones which are not in the array.

But what happens if I write?

INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid
INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO NOTHING RETURNING rowid

Is the rowid returned if there was already an entry? To my understanding it will not. Maybe I could work around by adding a dummy column but it would be very handy.

Maybe a different syntax like would be better?

INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO RETURNING rowid

Best regards, Marco
2021-02-10
18:18 Reply: QT Framework and SQLite SEE (artifact: 7da5636af7 user: marcob)

int rc = sqlite3_prepare_v2(_pdb, query.toUtf8().constData(), -1, &stmt,NULL);

Is that not a dangling pointer? You call constData() on a temporary.

You could use sqlite3_prepare16_v2 or stop to use QString?

2020-06-05
09:43 Reply: Non primary key alias to rowid (artifact: f5b76570c2 user: marcob)

I have done something similar. I get max(id) before I upsert and as I do all my upserts I do ++id. I need the rowid too because I capture all upserts with the update hook to delete all not upserted entries. carray is very handy for that. So I still think I would be nice to have somthing like CREATE TABLE foo(id AS (rowid), gobalID TEXT PRIMARY KEY, data...).

Thank you much for your help!

07:06 Reply: Non primary key alias to rowid (artifact: 662bd435ff user: marcob)

I need the RowId for the update hook, I had actually the same idea like you but RowId is hardcoded to many C APIs. The text id is quite long, so I thought it would better not to use it as a foreign key. Actually I do not understand your last sentence. But it looks like there is no easy way to get rid off the extra unique id. I can live with that. Like I said I am not an expert, especially to the session extension, and I am trying to avoid to do a really stupid mistake. Thank you for you help. :-)

06:53 Reply: Non primary key alias to rowid (artifact: feea5f94b7 user: marcob)

This was my original design and it is not working with sessions in all cases. For example if there is a change set from an other database the global key has to be used. In this cases translationTextId. It would be nice if if you could write:

CREATE TABLE IF NOT EXISTS translatables(id AS (rowid), translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT);

2020-06-04
17:12 Reply: Non primary key alias to rowid (artifact: 4c3d137a09 user: marcob)

Yes, I know I read it in the documentation. But the rowid is special because this is what you get in callbacks etc.. So it would be nice if you could map the rowid simply to an unique index.

17:08 Reply: Non primary key alias to rowid (artifact: e591bad9c8 user: marcob)

Because if remove the it locally and but get it externally again as an import you have two different rowids. So after an import your changeset is not deleting it if it is not using the global text it. So your global id which is used everythere has to be the primary key. It's only working if your global key is a text id and this is a primary key.

14:34 Reply: Non primary key alias to rowid (artifact: ac4cd17326 user: marcob)

First I need the ROWID for the update hook. Second I need the PRIMARY KEY of a text field for the sessions. Third I need an integer id for efficiency in the child table because the PRIMARY KEY in the parent table is large. Then I cannot use ROWID for it because it is unstable and does not work together with the FOREIGN KEYS. So I think what I really need is an alias of ROWID which is not a PRIMARY KEY. Like you see in this examples I have an extra unique index which could be an alias to ROWID but this is impossible because I use already an other PRIMARY KEY. I am not an expert in Sqlite, so maybe somebody knows a better way.

CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT)

CREATE TABLE IF NOT EXISTS translations(id INTEGER NOT NULL UNIQUE, translatableId INTEGER NOT NULL REFERENCES translatables(id) ON DELETE CASCADE, language TEXT NOT NULL CHECK (language != ''), translation TEXT, PRIMARY KEY(translatableId, language))

14:23 Edit: Non primary key alias to rowid (artifact: 622d013b11 user: marcob)

Hello

I used the rowid with the INTEGER PRIMARY KEY as an alias but with the session api I had to use a different primary key because otherwise I could not track deleted entries. But now I want to use FOREIGN KEYS too. And anyway rowid is not stable. Is there a way to map the rowid to a INTEGER UNIQUE column? Or what is the best to create a id column which is generating unique default integer ids(like rowid)?

Here some snippets from the table design: CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT)

CREATE TABLE IF NOT EXISTS translations(id INTEGER NOT NULL UNIQUE, translatableId INTEGER NOT NULL REFERENCES translatables(id) ON DELETE CASCADE, language TEXT NOT NULL CHECK (language != ''), translation TEXT, PRIMARY KEY(translatableId, language))

Thanks and best regards, Marco

09:30 Post: Non primary key alias to rowid (artifact: df09d93498 user: marcob)

Hello

I used the rowid with the INTEGER PRIMARY KEY as an alias but with the session api I had to use a different primary key because otherwise I could not track deleted entries. But now I want to use FOREIGN KEYS too. And anyway rowid is not stable. Is there a way to map the rowid to a INTEGER UNIQUE column? Or what is the best to create a id column which is generating unique default integer ids(like rowid)?

Thanks and best regards, Marco