SQLite Forum

Timeline
Login

50 most recent forum posts by user anonymous

2022-01-28
23:32 Post: How to keep collation after substr()? (artifact: 0f7d79f53e user: anonymous)

Although a column is defined with collate nocase, when passing the value through substr, the collation is lost.

CREATE TABLE t(s text collate nocase);

INSERT INTO t VALUES('AAA'),('aaa');
CREATE VIEW a AS SELECT s FROM t;
CREATE VIEW b AS SELECT substr(s,1,3) s FROM t;

SELECT s FROM t WHERE s = 'aaa';
+-----+
|  s  |
+-----+
| AAA |
| aaa |
+-----+
SELECT s FROM t WHERE substr(s,1,3) = 'aaa'; -- expected both rows
+-----+
|  s  |
+-----+
| aaa |
+-----+

SELECT s FROM a WHERE s = 'aaa';
+-----+
|  s  |
+-----+
| AAA |
| aaa |
+-----+
SELECT s FROM b WHERE s = 'aaa'; -- expected both rows
+-----+
|  s  |
+-----+
| aaa |
+-----+

Is this expected? If so, how to overcome?

15:43 Reply: Primary Key or Unique Index (artifact: 8a6ec21259 user: anonymous)

INTEGER PRIMARY KEY has special meaning that INTEGER UNIQUE does not. And of course the situation is completely different for WITHOUT ROWID tables, which have (and require) a true primary key.

02:24 Reply: Include extensions at compile time (artifact: 4eb852e31c user: anonymous)

Anyways, I managed to include my extension at compile time without SQLITE_EXTRA_INIT. I got a checkout of the latest source, and I simply modified the src/shell.c.in includes, and added a call to the init function near the blocks of other init functions at roughly line 4767 of src/shell.c.in.

I'm just putting this here, if anyone would like to know how it's done.

2022-01-27
23:46 Post: SQLITE_FCNTL_LAST_ERRNO (artifact: c1bd305cbc user: anonymous)

What does SQLITE_FCNTL_LAST_ERRNO do? It is not documented on the page with the rest of the op codes. https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html

Also, there is a bullet point on that page that just says "No longer in use." but does not indicate which op code it's referring to.

16:05 Post: Include extensions at compile time (artifact: afcde048be user: anonymous)

How can I add extensions to SQLite at compile time without having to run .load in the shell, or configure ~/.sqliterc and add .load commands to it?

Should I modify the INCLUDE ../ext/ statements in src/shell.c.in?

15:54 Post: Error in UPDATE FROM examples? "GROUP BY 2)"? (artifact: a05a2dff69 user: anonymous)
In your "UPDATE" page (https://sqlite.org/lang_update.html), at the section of "2.2 UPDATE FROM", there is this "GROUP BY 2)" in the example:

 UPDATE inventory
   SET quantity = quantity - daily.amt
  FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
 WHERE inventory.itemId = daily.itemId;

Is this "2)" should be "itemId"? Or maybe it is some sort of syntax I don't understand?

It also appears in the other two examples in 2.2.1.
14:42 Reply: Proposed JSON enhancements. (artifact: 05687574ab user: anonymous)

If somebody want to use an expressions there is extract. It would anyway already a complex expression. What I would expect is somthing like:

[?1]

2022-01-26
09:37 Reply: How to start a program that use SQLITE with SYSTEMD ? (artifact: a08fee6697 user: anonymous)

Thank you for your response.Your answer inspired me, and after more than ten minutes, I found the cause of the problem. That's the key:

[Service]
ProtectSystem:full
I always thought ProtectSystem:false(def)/true/full just limit access to /boot & /etc, it actually contains /usr. I delete this configuration and it ok. OR If I keep ProtectSystem:full and place the database in the /opt directory, it will also be ok

03:47 Post: How to start a program that use SQLITE with SYSTEMD ? (artifact: deaec04a2b user: anonymous)

How to start a program that use SQLITE with SYSTEMD ?

I have a program use Sqlite3, and now need to use systemd

ExecStart=/usr/local/mine/server -c /usr/local/mine/server.json > /var/log/apiserver.log
ExecStart=sudo /bin/sh -c '/usr/local/mine/server -c /usr/local/mine/server.json'
ExecStart=/bin/sh -c '/usr/local/mine/server -c /usr/local/mine/server.json > /var/log/server.log 2>&1'
but the program alweays report error:
attempt to write a readonly database
if I execute in cmd, that is ok
/usr/local/mine/server -c /usr/local/mine/server.json
I think that is cause by user permission, So I tried adding a user
The part of /usr/lib/systemd/system/apiserver.service:
[Service]
Type=simple
User=server
Group=server
ExecStart=/usr/local/mine/server -c /usr/local/mine/server.json
The directory structure :
/usr/local
drwxr-xr-x. 3 server server    121 Jan 26 11:24 server
/usr/local/server
drwxrwxrwx. 2 server server     21 Jan 26 11:24 serverProgram
drwxrwxrwx. 2 server server     21 Jan 26 11:24 db
/usr/local/server/db
-rwxrwxrwx. 1 server server 20480 Jan 26 11:24 task.db
But not work. So, how to start a program that use SQLITE with SYSTEMD ? It's been bothering me for weeks.

2022-01-25
19:47 Reply: Finding the SQLite version for a feature (artifact: 7666cfe7e0 user: anonymous)

You can search the FOSSIL.

Command fossil sea add column ends the list with the earliest relevant entry:

=== 2005-03-17 ===
05:06:28 [173e26961f] Update to keyword list for ALTER TABLE ... ADD COLUMN command. (CVS 2394)
05:03:39 [94185dd4f7] Add the ALTER TABLE ... ADD COLUMN command. (CVS 2393)
14:51 Reply: Case when (artifact: 27d62a5779 user: anonymous)

You can make that a (little) bit simpler:

select 'Q' ((cast(substr(accounting period, 1, 2) as integer) + 2) / 3) '-' substr(accounting period, 7, 4) '.csv' AS Filename from B

BTW sanitizing your input should have filtered out invalid dates, hence no need to handling the 'BUG' case.

14:10 Reply: Case when (artifact: ef423f63ea user: anonymous)

Hey everyone,

First, I just looked at my last message and it looks like the SQL did not paste nicely, so I apologize for the confusion!!

Second, I think I got it to work!

Here is my SQL (I double-checked it this time):

select CASE WHEN substr(accounting period, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(accounting period, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(accounting period, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(accounting period, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END '-' substr(accounting period,7,4) '.csv' AS Filename from B

This gave me the following result (which is exactly what I was looking for):

Q3-2021.csv

Thank you all so much for your time and effort! Clearly I am still trying to learn SQL and all the nuances that come with it but this was extremely helpful for my work and beneficial for my growth! So thank you again!

11:03 Reply: One connection per function or one shared connection? (artifact: 4b098d3891 user: anonymous)

Thank you for the reply. In this context a Golang function is a function in my Go program that contains SQL statements (single SELECTS, INSERTS and UPDATES) that run against the SQLite database.

An API client may obtain data from the database by calling an endpoint via the GET method and then modify that data before calling the same endpoint with PUT. The GET method performs a SELECT while the PUT performs a separate UPDATE.

I plan to continue using only one DB connection for now.

Thank you again for the advice!

10:57 Reply: One connection per function or one shared connection? (artifact: ff2fe46b1f user: anonymous)

Thank you for the reply. I do not explicitly use transactions in my SQL statements (BEGIN, END or COMMIT) only simple SELECTS, INSERTS and UPDATES. If I do have a need for transactions, I may experiment with multiple connections to see how slow they are compared to only one.

Thanks again!

08:11 Reply: Finding the SQLite version for a feature (artifact: 6b0d1f7d7b user: anonymous)

You can search for "ADD COLUMN" on that page: https://sqlite.org/changes.html

04:00 Reply: column constraint ascending primary key with text type (artifact: cfe7604e71 user: anonymous)

nice, exactly what i was looking for, triggers it is then. thanks a lot

01:55 Post: One connection per function or one shared connection? (artifact: 11ff514e05 user: anonymous)

Hi everyone,

I'm building an API (in Golang) and using Sqlite as the backend DB (version 3.27.2). The API is RESTful (accepts GET, UPDATE, DELETE, PUT from HTTPS clients) and returns JSON to them. I am using WAL mode in Sqlite. The only process that talks to the DB is the API. All the clients access the DB through the API.

Now, the question... I am currently using only one DB connection and passing it to all functions that run a DB query. The main Go function opens the one connection and defers conn.Close(). I also catch signals. So when I press Ctrl+C (sigint) in the terminal a clean-up process ensures conn.Close() is called before exiting. But I am concerned that the DB conn may not close properly in some cases. Would it be safer to have each function open a DB conn and close it? My gut says that would be safer, but maybe slower.

I appreciate any advice. I am using standard Go "database/sql" and this Sqlite library: https://github.com/mattn/go-sqlite3

Thanks a lot!

01:29 Reply: Case when (artifact: 6cf57f582a user: anonymous)

The data found in the "Accounting period" column all reflects "09/30/2021".

The SQL used is below:

SELECT CASE WHEN substr(accounting period, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(accounting period, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(accounting period, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(accounting period, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B AS Quarter

The result was: Q3

So this is working correctly now. But now I am trying to rename the entire file to "Q3-2021.csv". The SQL I used for this is below:

SELECT CASE WHEN substr(accounting period, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(accounting period, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(accounting period, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(accounting period, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B "-" select substr(accounting period,7,4) from B ".csv" AS Filename
The result was: error near " ": syntax error
2022-01-24
22:04 Reply: Session Extension: Changeset for Query/View (artifact: 9cfa8cef03 user: anonymous)

Okay, thank you! Do you have anything specific in mind that is less heavy-handed or pointers to prior work (code/papers)?

21:37 Reply: Session Extension: Changeset for Query/View (artifact: 8cc4cb1e8e user: anonymous)

Okay! Didn't you imply though that basically this one-liner would suffice to get the changeset/incremental view updates? I still don't know how that would work without rerunning the entire SELECT query on every database update and then somehow manually computing the difference between the stored table and the updated query result.

20:55 Reply: Session Extension: Changeset for Query/View (artifact: ba60f9879f user: anonymous)

Thanks! I understand how I can create a (temporary) table with CREATE TABLE AS SELECT ... but it was my understanding that the resulting table will not automatically reflect changes made to the tables it has been created from. Could you explain to an SQL/SQLite noob like me how the table is updated (based on the same SELECT ... query) in order to generate the desired changeset? Do these updates have to be triggered manually? If so, is there a good way to find out for which database changes an update is necessary?

20:28 Reply: Session Extension: Changeset for Query/View (artifact: deb67c5033 user: anonymous)

Sry I should have been more clear. What I meant is more like incremental view updates that reflect how a view/query result changed as a result of modifications to the database, i.e. like what https://materialize.com does for PostgreSQL.

19:57 Reply: Case when (artifact: 2f9b0dd7e2 user: anonymous)

here is the SQL that was inputted:

select substr("accounting period", 1, 2) as month from B

1.) B = table name

2.) "accounting period" = the column name found in the file. (dates found in the file are in *M/DD/YYYY format.

The results of the query above are:

Loading table: B 907 records found

month ac ac ac ac ac ac ac ac ac

...So it appears it is not recognizing the column name and is performing the substring off the word "accounting period"

19:37 Post: Session Extension: Changeset for Query/View (artifact: 8da27fbe1d user: anonymous)

Hey, does the session extension support getting changesets for SQL queries / views? If not, is there another extension that does this? If not, why not? Wouldn't this be super useful? :)

19:19 Reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. (artifact: cbc5917fbd user: anonymous)
Hi Kim. I'm currently working with the Safari history.db file myself and came across this page. Here are a couple of suggestions:

(1) Are you using DB Browser for SQLite to browse the database? If not you should :) Info and downloads: https://sqlitebrowser.org
(2) >> when I hit "Browse Data" there is no detail. It only shows one line item with a bunch of numbers in it << I think you're looking at the history_client_version table within history.db. Open your database using DB Browser for SQLite, click the "Table: " dropdown and select another table. The history_visits table (maybe also the history_items table) probably has the data you are looking for. 
(3) If you want to work with your original history.db instead of having to make a copy, you need to give DB Browser for SQLite full disk access. Go to System Preferences > Security & Privacy (click the lock to make changes) > select Full Disk Access and check the box next to DB Browser for SQLite.

Hope this is helpful :)
17:06 Reply: Is it possible to use WAL mode without using mmap() function ? (artifact: 2be556bf95 user: anonymous)

I want to use WAL mode in vxworks kernel mode. So, it is not necessary to use mmap to share memory among processes. All heap memory are visible by kernel. In this case, is it also possible to not force WAL to work in exclusive mode? Do I need to customize the VFS to accomplish this?

16:58 Reply: Is it possible to use WAL mode without using mmap() function ? (artifact: 6844815bbf user: anonymous)

Anyone can help?

14:46 Reply: Case when (artifact: 573d9a4a0e user: anonymous)

Hi again,

I confirmed the data format is MM/DD/YYYY. Below is my updated SQL:

SELECT CASE WHEN substr("accounting period", 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr("accounting period", 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr("accounting period", 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr("accounting period", 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B AS Quarter

**The ultimate goal = to rename the file something like "Q3-2021.csv" based on the data that is in the file. So there is definitely more to it than this, I need to pull the year from the "accounting period" column as well as update the SQL to rename the file like my example.

The SQL above is still giving me 11 results = "BUG!"

09:24 Reply: Initialisation (artifact: 18f1fcc4a5 user: anonymous)

I get an error 193.

Which function returns that error code?

You should provide example source code that demonstrates the issue.

06:59 Reply: Proposed JSON enhancements. (artifact: 32186abcac user: anonymous)

I use the nonstandard `...` syntax, and some others might use the nonstandard [...] syntax. Often the SQL codes are embedded in C codes, and for this purpose, it is helpful, instead of using the quotation marks (avoiding needing the additional escaping) (but when the SQL codes are in external files, the quotations marks can be in use).

I think the JSON specific syntax is not needed for core SQL. In the case of -> it is not a conflict and can be overridden anyways, it is not a problem; however, using the square brackets for JSON can be problem due to already being in use for something else.

06:53 Post: column constraint ascending primary key with text type (artifact: 1b4209200b user: anonymous)
  • is it possible to create a column constraint of type text which ensures that subsequent values are ascending?

    • CREATE TABLE calendar(date TEXT PRIMARY KEY ASC NOT NULL) does not work.
    • abusing lag function does not work either:

      CREATE TABLE calendar(date TEXT PRIMARY KEY NOT NULL CHECK (lag(DATE(date),1)<DATE(date)))

INSERT INTO calendar (date) VALUES ('2022-12-13')

  • now i should be prevented from inserting any value < 2022-12-13

  • i guess the questions boils down to, how to write a constrain which gets a previous column value

2022-01-23
23:24 Reply: binding by reference instead of value (artifact: 6592f932e8 user: anonymous)

Note that if you want to read data from outside of SQLite and insert them into a SQLite database, then one possibility would be that a virtual table might be used (if it is suitable for your usage, and I don't know that it is); then, the virtual table provides the data and you can use something like INSERT INTO ... SELECT * FROM ... to add the data into the database. (A virtual table can also accept constraints which can be used as inputs, if needed.)

20:36 Reply: What happens if I exit an app without closing the db? (artifact: 8fe28670d2 user: anonymous)

it's not always easy to track all the "exit paths" when an application closes

Usually, it's a good idea to keep track of all resources that your program allocates (the garbage collector can only help you with the memory, but not e.g. kernel objects like file handles). The Go language has a defer keyword to guarantee that your clean-up function will run. Other languages offer destructors for similar purposes.

One reason not to do that is when you know that the operating system will clean it up for you right away, but if your code might become a part of a library and be called repeatedly, behaviour like that is essentially creating a resource leak.

15:03 Reply: INSERT INTO SELECT upgrade to IMMEDIATE/EXCLUSIVE (artifact: 38f50cfef0 user: anonymous)

Thanks such a comprehensive reply.

I suspected as much given the documentation talks about statements and not expressions, but I often find behaviour like this has it's quirks and just wanted to be sure. Your answer gives me confidence it does, thanks.

I had no idea you could do something like edit(a, 'notepad.exe')! That's brilliant and I'll remember it for next time.

09:35 Reply: INSERT INTO SELECT upgrade to IMMEDIATE/EXCLUSIVE (artifact: 9b35abdfdc user: anonymous)

Thanks for the reply. I think I understand what you're saying, but I'm not certain in my mind on what constitutes a statement for the purposes of determining a write operation.

Let me add an example (apologies for having not done so before)...

Schema:

CREATE TABLE blah (a INTEGER PRIMARY KEY NOT NULL);

Query:

BEGIN DEFERRED

INSERT INTO blah SELECT COALESCE(MAX(a), 0) + 1 FROM blah;

COMMIT

INSERT INTO... is obviously a statement, so from your answer I can assume that it is recognised as a write operation and the deferred transaction is upgraded to immediate. That part I get :-)

However, what I'm wondering is whether the SELECT expression inside the INSERT INTO statement runs before the upgrade to IMMEDIATE or after? i.e. can I rely on the fact that when I perform the SELECT that no other operation can be writing and that I will never get a PK violation?

Thanks,

Ben

2022-01-22
22:17 Reply: Confusing sqlite3_vtab_distinct (artifact: 4380f29b58 user: anonymous)

Well, it has been improved now, and now the documentation is much better; now it isn't confusing like it was before.

13:15 Post: INSERT INTO SELECT upgrade to IMMEDIATE/EXCLUSIVE (artifact: ba3eb2deab user: anonymous)

Hi,

I’m wondering at what point a transaction is upgraded to IMMEDIATE/EXCLUSIVE when using INSERT INTO SELECT.

Is it before or after the SELECT?

Presumably if it’s before I can rely on this to provide me with a consistent read.

Thanks for any help,

Ben

04:45 Post: Confusing sqlite3_vtab_distinct (artifact: 2eaeea344d user: anonymous)

The documentation for sqlite3_vtab_distinct is confusing; maybe adding examples would be helpful. I am also not sure why it isn't a field of the sqlite3_index_info structure instead (that can just be read directly).

2022-01-21
22:13 Reply: Looks like some exports are missing from the Windows x64 official SQLite DLL (artifact: bf30698850 user: anonymous)

Folks, the original poster here: I redownloaded the x64 and x86 prebuilt Sqlite3 DLLs and regenerated the import files using lib /def:sqlite3.def /out:sqlite3.lib /machine:x64 and reran my test app and the problem has gone away. Not sure what the source of the original problem I had but the problem now does not exist. I also confirmed the presence of the functions using both dumpbin /exports and the old Dependency Walker application (which does take forever now<g>), so everything is good. Sorry for the false report.

Regards, PJ Naughter

17:28 Post: Looks like some exports are missing from the Windows x64 official SQLite DLL (artifact: 4ff22bc13e user: anonymous)

Just updating my code to work with the latest v3.27.2 version of SQlite and upon testing my x64 Windows binaries, I am getting the error the procedure entry point sqlite3_value_frombind could not be located in the dynamical link library... Upon checking with dependency walker it does indeed look like this function is not exported from the x64 prebuilt version of SQlite3. The x86 version of SQLite does have this function. Both the x86 and x64 def files for SQlite3 for Windows reference this function but the x64 dll does not have it. There may well be other missing functions as I did not do a comprehensive check of the exports between x86 and x64 SQLite.

Regards, PJ Naughter

08:19 Reply: Distinguishing CREATE TABLE failure (already exists) from othe failures (artifact: cdd755ce51 user: anonymous)
2022-01-20
19:59 Reply: Case when (artifact: 54d257a88c user: anonymous)

Thanks Michael!

Here is my new SQL, using yours and another user's responses:

CASE WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('01','02','03') THEN 'Q1' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('04','05','06') THEN 'Q2' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('07','08','09') THEN 'Q3' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END AS Quarter

Here is the result:

Quarter BUG! BUG! BUG! BUG! BUG! BUG! BUG! BUG! BUG!

19:30 Reply: Case when (artifact: aae50a2ad0 user: anonymous)

Thank you David! Below are my updates: SELECT || CASE WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '1','2','3', 'Q1' WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '4','5','6', 'Q2' WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '7','8','9', 'Q3' ELSE 'Q4') ||'-'|| substr("accounting period",1,4) || '.csv' from B

Still getting an error message, I'm guessing the SQL is incorrect in at least a couple spots

18:29 Reply: Case when (artifact: 9ea8174bc6 user: anonymous)

I should add: The "Account period" column format is MM/DD/YYYY. So I need to include a substr/split_part to pull only the month from the Accounting Period column, but not too sure how to accomplish that.

18:12 Post: Case when (artifact: fed03dfa39 user: anonymous)

Hello! I am trying to create a CASE WHEN for a data file. The idea is to have the CASE WHEN look at a column of data in the file (column is called "accounting period", and based on the accounting period, it will create a new file name that includes the quarter and year that the data file represents. Currently I have the following:

Select B case when ('accounting period'= '1','2','3', 'Q1'

when 'accounting period'= '4','5','6', 'Q2' when 'accounting period'= '7','8','9', 'Q3' else 'Q4') |'-'| right('accounting period',1,4) || '.csv'

B=converted file name

16:55 Reply: Is it possible to fix a more than 1G corrupted SQLite file with only the first 16K content corrupted? (artifact: 8d7fa608bd user: anonymous)

My DB uses a 4K page_size.

Yes, of course, I am looking for commercial DB recovery.

11:14 Reply: How to check if sqlite3_stmt is done? (artifact: a48df178ed user: anonymous)
int sqlite3_stmt_busy(sqlite3_stmt*);
The sqlite3_stmt_busy(S) interface returns true (non-zero) if the prepared statement S has been stepped at least once using sqlite3_step(S) but has neither run to completion (returned SQLITE_DONE from sqlite3_step(S)) nor been reset using sqlite3_reset(S)
02:41 Post: Is it possible to fix a more than 1G corrupted SQLite file with only the first 16K content corrupted? (artifact: 7498847597 user: anonymous)

Hello,

We have a 1GB+ SQLite file with the first 16K content corrupted. I am wondering, is it possible to fix/repair that file completely? Many thanks in advance.

Lirong

2022-01-19
23:06 Reply: SQLITE3.EXE V3.37.2 (artifact: 6afb109b33 user: anonymous)

Double click on sqlite3.exe ...

v3.36 - no vertical scrollbar

v3.37.2 - vertical scrollbar

I don't know how to insert screenshots (here) to make what I see clearer ...

20:51 Post: SQLITE3.EXE V3.37.2 (artifact: f52e6c09f9 user: anonymous)

Using Windows 11, SQLITE3.EXE now has a vertical scroll bar which makes the task of scanning the current session a lot easier.

More ↓