SQLite Forum

Timeline
Login

50 events by user anonymous occurring on or before 2021-10-13 13:46:09.

More ↑
2021-10-13
13:46 Reply: Data Types artifact: 03b7203053 user: anonymous

did you define the column types when you defined the table?

The tables are defined with types for each column; I queried sqlite3_column_type() inside a user-defined function. I could equally query the column type inside a callback function ... but is is not reliable to assume the column type from the current row ... as you say, note that SQLite does not enforce values to be the same type as a column definition.

10:40 Reply: Data Types artifact: 00aa7257d7 user: anonymous

Return codes for sqlite3_column_type are here

https://sqlite.org/c3ref/c_blob.html

Not sure what your other columns are about.

10:13 Reply: binary .dump format artifact: e511a9cb23 user: anonymous

each copy has many incidental pointer-level differences depending on the precise page layouts, reducing the effectiveness of deduplicating backup tools

How reproducible are the resulting files if you VACUUM them afterwards?

Maybe we could look for other ways of "canonicalizing" an SQLite3 database?

09:18 Reply: Build fails with SQLITE_OMIT_WAL and SQLITE_MAX_MMAP_SIZE=0 artifact: dfcb495ca6 user: anonymous

I'm trying to compile Subversion with the latest Amalgamation and this problem is causing it to fail.

I'd rather not remove SQLITE_OMIT_WAL from the configuration. How can I get an older SQLite that doesn't have the issue?

06:45 Reply: Data Types artifact: a7da004e3b user: anonymous

The reason for seeking that table is for me to (have a quick way to) understand (rather than use it to coerce a desired behaviour in SQLite3) the results that SQLite produces.

Also, In one place I saw NULL associated with numeric code 1 and in another INTEGER was associated with numeric code 1; granted that such associations must be interpreted in context. I tried to use sqlite3_column_type against a query (not a table) to see what values were returned but the only value I get is 5 which is BLOB.

2021-10-12
20:11 Post: Data Types artifact: b521c8c40e user: anonymous

I am looking for a table showing

[Type Name] [Numeric Code] [Storage Class Code] [Affinity Type Code]
Null
Integer
Real
Text
BLOB
12:34 Reply: Writer Concurrency - server-process-edition artifact: 3c12ed1110 user: anonymous

Hi Dan Kennedy,

Thanks for the great work.

I has few question for the begin-concurrent action:

If I use singe-process mode, will the sqlite3_commit_hook/sqlite3_rollback_hook execute inside a lock for all threads (for multi connection to same database)?

I want get a 64bit unique transaction id for a database(after app reboot it still not get duplicate), is there a sqlite buildin value can be use for this purpose? (I like to read this value from sqlite3_commit_hook)

2021-10-11
06:42 Reply: Question about the WAL checkpoint artifact: 889de5c7df user: anonymous

Sorry, I mean single process mode.

05:48 Post: Question about the WAL checkpoint artifact: 1bee6a8e8d user: anonymous

quote from https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

"Checkpointers do not block writers, and writers do not block checkpointers. "

If i want to do checkpoint in other thread and write in main thread, I has to create 2 connection for the same database in single thread mode?

" There are also circumstances in which long-running readers may prevent a checkpointer from checkpointing the entire wal file"

If a statment finished with sqlite3_step, but not closed so I can reuse it late. In this case that mean a long-running readers block prevent a checkpointer?

2021-10-09
20:18 Reply: Understanding memory allocation behavior of SQLite artifact: 2d6d974890 user: anonymous

Same anonymous here!

Forgot to say, one way to quickly test this (and reduce allocations a lot as well) is to try using a smaller page size, unless your records are really fat may be you should try 1KB or even 512B

20:13 Reply: Understanding memory allocation behavior of SQLite artifact: 0e1b00c901 user: anonymous

SQLite's in memory db uses the same page layout that a disk based db would, it just never writes to the disk, thus if your 77M inserts are done separately, or even in batches but they involve wildly spaced primary keys then each row will probably have to allocate a full page, thus 4KB per inserted row, Multiply that by 77M and you get 300+ GB of allocated memory, now index inserts will also require page sized memory allocations, so you should double that to 600+ GB, roughly what you are seeing.

14:39 Reply: sqlite3_busy_handler missing artifact: c42f11b064 user: anonymous

I needed this for a workaround. Maybe this is not needed anymore, but as until now it was working alright I didn´t look into it anymore. My app opens and closes connections frequently. It does not reuse a single connection to SQLite (and this behavior cannot be easily changed). SQLite cannot handle multiple open connections so I have to be careful not to open a new connection when one was already open, but that was not a problem because the system is not multitasking and connections are short-lived. The thing is that starting from System.Data.SQLite version 1.0.82 connections started being freed through sqlite3_close_v2, which left "zombie" native connections (blocking the db) if there were pending statements that could be processed. Sometimes it was the case the some statements generated by IDataReaders would not be garbage collected in time or they would be in the finalization queue at commit time, so the commit would be blocked waiting for the GC to eliminate the statement and it would eventually timeout. I hooked sqlite3_busy_handler to get notified of the impending timeout and so force a GC. It was hacky but that did the trick, and I could not find any proper workaround for this problematic behavior. Maybe it was an internal problem from the data reader when handling the statements (I did close them properly but the native statement still remained alive). Do you happen to know if this behavior has been modified or any other way to handle it without the need of sqlite3_busy_handler?

2021-10-08
16:07 Reply: x86_64/amd64 sqlite3 binaries artifact: 255893ceec user: anonymous

Pretty sure the project already has such a machine.

AMD64 is the original moniker for x86-64.

AMD continued the x86 codebase when Intel decided to change things up with Itanium (IA-64).

13:59 Reply: ThreadSantizer warning for sqlite3_enable_shared_cache() artifact: f1279cd997 user: anonymous

Hello. A random search lead me here, but I figured I can answer this for you since you haven't gotten a response...

The short answer is No. ThreadSanitizer sees a data race and is reporting correctly, however, depending on the situation, that is okay. It's not a false positive, but without the _Atomic keyword, it cannot make the assumption that the operation is indeed atomic.

A fix for this not to show up may require a mutex and locking. This is a super expensive (relatively) change to something that was a single instruction, so there is a very real performance hit here. It's really overkill to use a mutex here.

When writing a threaded application, sometimes we need to access data that another thread may be writing to. However, I may not care if I get the pre-updated value or the post-updated value. The only thing I care is that it is one of them. As long as the operation is atomic, I get what I want, however, by definition, it's still a race condition.

-Jason

07:47 Post: mmap and blobs artifact: e69e4d5034 user: anonymous

Hello,

I've read https://www.sqlite.org/intern-v-extern-blob.html

when storing blobs in external files they can be mmapped and their address passed around.

When using internal blobs it seems at least one allocation and a copy is made when reading the blob, even if sqlite is used in mmap mode.

Am I right in concluding that directly mapping internal blobs to memory is not possible?

thank you

2021-10-07
16:44 Reply: Natural sort order artifact: 65814bc11d user: anonymous

Hi Richard, I'm using this as a loadable extension and it's working great. Is there any update on whether this will ship in SQLite without having to use a loadable module any time soon?

2021-10-05
20:48 Post: Insert within CTE artifact: 485611cffc user: anonymous

Given this CTE:

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 1000000 ) SELECT x FROM cnt;

Is it possible to add an INSERT INTO statement within the CTE to write the values to table?

17:40 Post: Double section number artifact: c45d9e5a17 user: anonymous

This prevents having the section number twice, see here.

By the way is hdom.tcl in the public domain like the rest of SQLite?

Index: pages/atomiccommit.in
==================================================================
--- pages/atomiccommit.in
+++ pages/atomiccommit.in
@@ -1327,11 +1327,11 @@
 modern journalling filesystems are immune, we believe) then you might
 want to consider putting each SQLite database file in its own private
 subdirectory.</p>
 
 <tcl>hd_fragment future</tcl>
-<h1>10.0 Future Directions And Conclusion</h1>
+<h1> Future Directions And Conclusion</h1>
 
 <p>Every now and then someone discovers a new failure mode for
 the atomic commit mechanism in SQLite and the developers have to
 put in a patch.  This is happening less and less and the
 failure modes are becoming more and more obscure.  But it would

2021-10-02
12:27 Post: Build fails with SQLITE_OMIT_WAL and SQLITE_MAX_MMAP_SIZE=0 artifact: 5caddd03ae user: anonymous

When building SQLite 3.36.0 with SQLITE_OMIT_WAL and SQLITE_MAX_MMAP_SIZE=0 on OS X (and probably other unix systems) the build fails with:

Undefined symbols for architecture x86_64: "_unixFcntlExternalReader", referenced from: _unixFileControl in sqlite3-ede8e2.o ld: symbol(s) not found for architecture x86_64 clang: error: linker command failed with exit code 1 (use -v to see invocation) make: *** [sqlite3] Error 1

The problem seems that unixFcntlExternalReader is omitted when SQLITE_OMIT_WAL. Though this method references to unixShmNode which is omitted with SQLITE_MAX_MMAP_SIZE=0.

06:53 Reply: MicrosoftODBC Driver Manager Data source name not found and no default driver specified error when use VBA connect Sqlite artifact: 7a2b4a977e user: anonymous

The 32- and 64-bit ODBC drivers can co-exist.

The problem is Excel 64-bits ... it can only use 64 bit software. ADODB is 32-bit and, as far as I am aware, no 64-bit version exists.

Option 1: Check your code using 32-bit Excel for reassurance.. I use ADODB, the 32-bit driver with Excel 32-bit & it works.

Option 2: Re-install 32-bit Excel

If re-installing 32-bit Excel is NOT an option, execute your queries in the SQLite CLI i.e. export to a CSV and import the CSV in your 64-bit Excel. You can do all of this using Excel VBA.

The ODBC drivers are statically linked to a much earlier version of SQLite3 & do not come with the SQLite3 CLI - you can download it but it will be the latest version, thereby introducing compatibility issues between the CLI and the earlier DLL embedded in the ODBC driver.

As Larry points out, much of this is off topic here ...

2021-10-01
18:34 Reply: MicrosoftODBC Driver Manager Data source name not found and no default driver specified error when use VBA connect Sqlite artifact: 7c4a3b5fc3 user: anonymous

Architectures do not mix.

ADODB is 32-bit & you have installed the 64-bit ODBC driver; try again with the 32-bit ODBC driver.

07:34 Reply: fts5 and diacritics artifact: bffb495577 user: anonymous

Unicode doesn't define decompositions for the letters ø and Ø (nor for the diameter sign ⌀).

2021-09-30
21:33 Post: fts5 and diacritics artifact: 676b5ba481 user: anonymous

Hi,

I'm using FTS5 w/ sqlite version 3.35. And am trying to match search for 'O' character w/ the expectation that it will prefix match any characters w/ diacritics. This works for many of the common diacritics but in order to find 'Ø', I have to specifically search for 'Ø'.

Example:

INSERT into test(DESCRIPTION, TEXT_TO_MATCH) values ("regular", "Oz"); INSERT into actor(ACTOR_UUID, DISPLAY_NAME) values ("circumflex", "Ôz"); INSERT into test(DESCRIPTION, TEXT_TO_MATCH) values ("stroke", "Øz");

CREATE virtual table ftsTest using fts5(DESCRIPTION, TEXT_TO_MATCH, content=test, tokenize = "unicode61 remove_diacritics 2");

INSERT INTO ftsTest (ftsTest) VALUES('rebuild');

select * from ftsTest where TEXT_TO_MATCH match '"Oz"'; -- doesn't match "Øz"; matches "Oz" and "Ôz" select * from ftsTest where TEXT_TO_MATCH match '"Øz"'; -- matches "Øz";

Many of the other diacritics for O seem to match other diacritics correctly. Do I have fts table misconfigured? Is this a bug? Maybe I misunderstand how diacritics work? Any help would be appreciated

Thanks ahead!

17:28 Reply: sqlite3_exec: Any way to validate the first argument? artifact: 59a63263c7 user: anonymous

If I open multiple databases, I can track the handles: DBName DBHandle D:/SQLITE32/DB/AJAY.DB 145696128 D:/SQLITE32/DB/CHINOOK.DB 145762024 D:/SQLITE32/DB/SALES.DB 145953624

The last database that I open figures in the list of attached databases. ALIAS DATABASE main D:SQLITE32DBSALES.DB

It is quite easy to transpose the digits in the handles of opened databases.

  • a.When an 'invalid' handle is specified, there is no result.
  • b. When there are no rows returned, there is no result.

The point in asking the question is simply to be able to distinguish between a & b.

Also, is there a (default) handle I can specify to coerce SQLite3 to check whether I have specified a schema in my SQL

e.g. select * from main.sales limit 10;

and to return the results?

17:09 Reply: Multithread processing, storing in memory and writing to database artifact: 6253aa77ef user: anonymous

Time-series databases certainly do exists, but are there any as small and beautiful as SQLite

Indeed, and its increasingly common to see SQLite at the endpoints in IOT etc for storing configurations and such, for ex, but then the already present SQLite cannot be used for time-series data and that data has to be stored in some other way for ex, in CSVs or specialized tools or data structures in application language and ETL-ed to a db with time-series features for aggregation etc.

While this does not address OP's immediate problem, I would deviate a little from the thread trying to find workarounds or alternative solutions for OP and suggest a new feature in SQLite instead. PostgreSQL, which SQLite derives a lot of inspiration from, has a time series DB called TimeScale which uses two core concepts of hypertable and chunks - it uses BTrees underneath and keeps all relational features intact. It also claims very fast single-machine ingestion rates so it certainly seems possible with BTrees (I am no expert, though).

Unless something in SQLite design rules it out completely, I think, the chunking approach might be a great feature to add to SQLite to improve SQLite ingestion rate and add basic timeseries capabilities.

https://blog.timescale.com/blog/what-is-high-cardinality-how-do-time-series-databases-influxdb-timescaledb-compare/

https://blog.timescale.com/blog/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c/

https://docs.timescale.com/timescaledb/latest/overview/core-concepts/hypertables-and-chunks/#hypertable-benefits

16:39 Reply: Feature request: Stored Procedures artifact: 6a0434ab12 user: anonymous

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

True. However in a multi-language environment (say Python, C, PHP) that would mean implementing all 'stored' procedures multiple times. And of course it will not be a question IF these implementations will be out of sync one day but WHEN - with potentially fatal results for your data.

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

16:04 Post: sqlite3_exec: Any way to validate the first argument? artifact: 72160f58e5 user: anonymous

Is there a way to validate (check whether it exists) the first argument to sqlite3_exec?

15:57 Reply: VACUUM INTO for vfs conversion artifact: 4379bfa403 user: anonymous

Something to wish for is support for passing custom uri parameters to VFSes.

This already exists.

13:07 Reply: Heading Row Repeat artifact: 6cfd2e1b3a user: anonymous

SELECT * from (select * FROM sales limit 1 offset 0) UNION SELECT * from (select * FROM sales limit 64 offset 1);

11:42 Post: Heading Row Repeat artifact: 54f4b6eb48 user: anonymous

Imagine a table contains a multi-column report which is read in batches of 64 records with the first row being a record having the same number of columns coming from another table.

I tried the following SQL; for simplicity, I am using the first row comes from the same table.

SELECT * FROM sales limit 1 offset 0 UNION SELECT * FROM sales limit 64 offset 1;

returns

Error: LIMIT clause should come after UNION NOT before

  • Is the error referring to the statement above UNION or to the one below?
  • Looking for a quick way to refactor my SQL. Ideas?
2021-09-29
21:31 Reply: Feature request: Stored Procedures artifact: d3bd261ad9 user: anonymous

I've been gnawing on a similar problem for the past 3 days, and this solves it beautifully.

Great post, thank you.

18:19 Reply: HTML generation issue in sqlite.org's search engine artifact: 11783e2228 user: anonymous

[For clarity, I am the original poster and the above anonymous is not]

My recollection was that my broken search acted like this, but those numbers are all after the point at which the markup went off the rails. (I don't see multiple pages at all anymore for my original query, which appears to be as it should have been in the first place.)

17:51 Reply: Multithread processing, storing in memory and writing to database artifact: fe5867b4e0 user: anonymous

A fourth approach,

Have each thread write to its own database, but instead of an in-memory database, just use SQLite in WAL mode and set synchronous to zero. The main thread could then perform checkpointing on these databases synchronously without blocking the writer threads.

And no, you don't need to aggregate the data later in a single database, you can attach all the databases from a single connection and query it as you like.

16:09 Reply: HTML generation issue in sqlite.org's search engine artifact: 2aaf9cf6f9 user: anonymous

I'm not able to reproduce it exactly after your fix went live, but the link is https://www.sqlite.org/search?q=sqlite&i=20.

14:27 Reply: HTML generation issue in sqlite.org's search engine artifact: b97646a725 user: anonymous

The search results page numbering also needs fixing, on the second page(or any other page after the first) of the search results page one is highlighted but not clickable.

13:05 Reply: User-defined functions in the CLI? artifact: 86df486c48 user: anonymous

Maybe that one? https://github.com/0x09/sqlite-statement-vtab/

Mentioned here and here.

Can be (ab)used as 'function' by wrapping the table-valued-function as scalar subquery ...

sqlite> .load ./statement_vtab

sqlite> CREATE VIRTUAL TABLE twice USING statement((SELECT 2 * :x as y));
sqlite> select (select y from twice(9)) as result;
result
18

or - arguably less clumsy - with a join

sqlite> create table t1(v);
sqlite> insert into t1 values(4),(7),(99);

sqlite> select t1.v, twice.y as r from t1 left join twice(t1.v);
v|r
4|8
7|14
99|198
01:15 Reply: Unable to search an FTS5 trigram table when using external content artifact: 281c93ee10 user: anonymous

Thank you so much. I set up and trigger and it's working now. I'm assuming the act of inserting data into the table is what actually causes the index to be built for searching.

2021-09-28
21:28 Reply: Reset database artifact: 0428279e45 user: anonymous

I have read/write access; can't delete.

the state when it was created' = no tables no views, nothing i.e. size = 0

20:11 Reply: Reset database artifact: 6a043319d0 user: anonymous

After

.open mydb.db

On checking the properties of mydb.db, its size is 0.

I am able to set the length of the file to 0 and can open or attach it using the sqlite3 APIs.

There is no DROP DATABASE or TRUNCATE DAATABASE. I did not want to simply drop the tables and views as I believe that will lead to fragmentation. I am not sure whether VACUUM locks the database.

Setting the file length to zero is faster than getting the list of objects and deleting each one and vacuuming the database; so, I'll settle for this ... unless there are side effects that I haven't thought of?

19:11 Post: Reset database artifact: 57759a16cd user: anonymous

Is there an SQL i.e. a programmatic way to reset a database to the same state as it was when it was created?

I can do this by deleting the file and recreating the database with sqlite3_open.

But I am pre-empting failure when I have just read access to the database; hence the question.

15:06 Reply: User-defined functions in the CLI? artifact: b318c85ada user: anonymous

The command for running SQL code from a file is .read, not .load.

11:36 Reply: User-defined functions in the CLI? artifact: 1d2b886c62 user: anonymous

(Other 'anonymous' than OP)

No, not directly. But using an extension you can create functions using Lua in the CLI.

Check out Lua for SQLite

08:29 Post: Unable to search an FTS5 trigram table when using external content artifact: 948f1d4208 user: anonymous

Hello. I'm attempting to create an FTS5 trigram table to text search a separate table. I used the external content option and I know it at least sees the data because when I select all with no where clause it does show data. Below is a minimal example to see it not working. SQLite version 3.34.1

sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
sqlite> INSERT INTO test(data) VALUES('testing foo bar');
sqlite> SELECT * FROM test;
1|testing foo bar
sqlite> CREATE VIRTUAL TABLE fts USING fts5(data, content=test, content_rowid=id, tokenize'trigram');
sqlite> SELECT * FROM fts;
testing foo bar
sqlite> SELECT * FROM fts('foo');
sqlite> SELECT * FROM fts WHERE fts = 'foo';
sqlite> SELECT * FROM fts WHERE fts MATCH 'foo';
sqlite> SELECT * FROM fts WHERE data MATCH 'foo';
sqlite> SELECT * FROM fts WHERE data = 'foo';

You can see none of the 5 queries I tried with searches returned anything. I tested non external content and it worked fine. Am I doing something wrong?

03:09 Reply: User-defined functions in the CLI? artifact: a1a22aca66 user: anonymous

Really? The docs say:

From the command-line shell, extensions can be loaded using the ".load" dot-command. For example:

.load ./YourCode

This worked fine when I tested it a moment ago.

00:45 Post: HTML generation issue in sqlite.org's search engine artifact: 6c412f37a7 user: anonymous

The following search query is generating oddly corrupted HTML for me as of the time of writing:

https://www.sqlite.org/search?s=d&q=regexp

It seems to be going off the rails around line 198 (as I write this) in the generated HTML, when what looks like it's meant to be part of a CSS color specification (rgb(0, 0, 0);) gets plunked into the page text:

<b>&nbsp;...&nbsp;</b>0,0,0);" />

Things go downhill from there; firefox ends up interpreting a fair chunk of HTML as attributes belonging to the next <path> element, and the same general pattern repeats until the end of the results list.

I apologize if this is not the right place to report bugs with the webpages, and would be happy to follow pointers to that right place.

2021-09-27
22:04 Post: User-defined functions in the CLI? artifact: 074359e193 user: anonymous

Is it possible to use sqlite3_create_Function to create user-defined function in the CLI?

07:49 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately artifact: b0680bf448 user: anonymous

Are you setting PRAGMA journal_mode=WAL; on the connection you're using to run your application-initiated checkpoints on? When I last tried a similar setup to yours, I had to set it explicitly again on the checkpoint connection for those calls to work, even though WAL mode is meant to be persistent.

05:10 Post: Test SQLITE_NOMEM result code on Linux (Memory : 8Gb) artifact: 678ac0c4ec user: anonymous

Is there any way to test SQLITE_NOMEM result code on the following interfaces : 1. sqlite_open(:memory:) 2. sqlite_prepare_v2

I am using it in a library and have to simulate for test purpose.

Also please provide any pointers on how to use sqlite_test_control for limiting memory and fault injections.

Sqlite Version : 3.22

2021-09-25
21:37 Reply: V3.36 artifact: 9dcc644e3c user: anonymous

In the CLI

,databases

or

PRAGMA database_list

tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

Programmatically, I will need to use sqlite3_open--which creates handles and not aliases as the CLI--to open databases.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically .

It looks like I have to manage this within my application. Correct?

20:08 Post: V3.36 artifact: da4d820656 user: anonymous

Can I get a dictionary (name = database name, value = handle) for all opened databases?

More ↓