SQLite Forum

Does your device generate write single commands at a regular interval, e.g. a data-logger, or a batch of write commands in a rush, and then a pause until the next batch ?

What medium is your database stored on ? Flash memory ?

One day, we should actually do someone's homework for them. You know … just to mess with them.

SQLite's journal modes are a picky detail of the internal workings of SQLite. Switching from one journal mode to another does not change what commands a programmer can give SQLite, or what SQL commands SQLite can implement unless the programmer disables journalling entirely.

To find out SQLite's abilities relating to 'undo' and 'redo', read these two pages in the order I give them:

https://www.sqlite.org/lang_transaction.html

https://www.sqlite.org/lang_savepoint.html

Usually you don't store ranks. Calculate a rank by iterating through an index of scores until you find the row you want.

If you have a situation where it is useful to store ranks, and ranks don't change often, don't store ranks in the same table where your scores are stored. Make a special table for just id, score and rank, and have a procedure which deletes all the rows in the table and makes new ones. Call this procedure when a new set of scores are in.

Most important and easiest:

If you have a known set of SELECT statements to execute, for example you know you're going to need to look up four values, put them in a transaction, i.e. surround them in BEGIN and END.

Less easy:

Where convenient, use a number of pre-prepared statements and just rebind and restep them, rather than preparing a new statement every time.

Are you inserting many rows in one big task ? If so, combine them in a transaction, by putting BEGIN before the first INSERT and COMMIT after the last one.

In new step 2 will you be detecting both 'unixepoch' and ' unixepoch' (space character before the word) ? If you're allowing both in a liberal manner, can you allow 'epoch' and ' epoch' too ? But keeping compatibility by allowing just 'unixepoch' is okay too.

What happens the value doesn't qualify for any of new steps 1 to 4 ? I suppose that would be either NULL or BLOB but perhaps there are other things. Perhaps the value is TEXT but the first character is not digit, plus, minus, or decimal separator.

One SELECT is meant to return a number of rows, and the values on each row relate to those rows. In other words, SQL does processing based on that idea: it tries to use one operation per table to get the results you asked for. Let's look at your SELECT:

select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;

The max(id) only needs one row to be retrieved: the one which has the biggest value for id. Since there's an index on this value, it can do this by looking at whatever row is last in this index. Fast and easy to find.

But then in the same query you ask for count(distinct id). Which requires more than one row to be read from the table. It has to scan a whole index. Well, that's okay, it has the index it needs.

But in the combined query, SQL is meant to retrieve both these figures with the same operation. That could be a lot more complicated. Let's see what it really does. Let's look at EXPLAIN QUERY PLAN on all three of those queries:

sqlite> EXPLAIN QUERY PLAN select 'max id:', max(id) from pro_comment;
QUERY PLAN
`--SEARCH pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'count distinct:', count(distinct id) from pro_comment;
QUERY PLAN
`--SCAN pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;
QUERY PLAN
|--USE TEMP B-TREE FOR count(DISTINCT)
`--SCAN pro_comment USING COVERING INDEX pro_comment_id

As you see, SQLite, in trying to get both figures from the same search, thinks it has to make a temporary index of the data. The solution is to accept that you want two different figures that aren't related, and ask for them in separate statements. Which is what you did yourself in your demonstration of the problem. Well done.

It could be that this particular optimisation is easy and fast to identify. In which case, the developers (who are reading your thread) might add it. But if you have SQLite check for every possible optimisation every time it does a search, every search is going to take a long time while SQLite checks to see if it qualifies for every possible optimisation. Which, for most SELECTs most of the time, will slow SQLite down.

I don't think there is one solution which I could recommend in all cases. It comes down to different levels of data.

A) You could make two different database files. One has the strings in English, one in Spanish.

B) You could make one database with two tables: one table with the English strings, one with the Spanish strings.

C) You could make one table but have two different rows for each purpose: one row has the English strings, the other row has the Spanish strings.

D) Or you could have both languages in the same row:

CREATE TABLE IF NOT EXIST Headers (HE1, HE2, HE3, HS1, HS2, HS3);

Part of it is about whether you might add another language to your solution. For instance, if you might eventually add another language, you would not want to use the HE1, HS2 solution because making the changes would be tedious.

That was my solution: pipe the output from the SQLite CLI's .dump command into a compactor. Problem is, because of how efficient compactors work, even that will temporarily use about the same amount of space as the original file.

With regard to a file system with compression built-in, we should take the concept to its limit and imagine doing it on Apple's Filesystem APFS. In that system two sectors with identical contents can be collapsed into the same sector, and two files with the same contents can be collapsed into the same file. If you call the OS API to duplicate a file, all that happens is another directory entry is created, pointing to the original data. (It's more complicated than this, but you get the idea.). If one of the two files is changed the change is stored as deltas until the OS decides that it's not saving any space by doing so.

Using that, you can create as many copies of the original file as you want, and use hardly any extra space. But does it do what the OP wanted ? Does this qualify as a backup in their case ? If there's a disk failure, you lose both files.

In the light of later posts to this thread, can we start again ? If I understand correctly, you have two requirements:

1. Make a backup copy of the database.
2. Do not use as much filespace for the backup as the database file takes.

How do you expect to do both of these things ? Unless you have a lot of indexes, SQLite is a compact file format. You can't do both of these things at once. Or did I misunderstand one of your requirements ?

Just make each of the connections open the database in read-only mode. SQLite will figure out behind the scenes what this means for locking.

Because Docker is a bit unpredictable to do with levels of caching, I would test out real lifelike situations using WAL mode and classic journalling mode, and see which is fastest. I do not mean that you should write special testing code, or simulate your data using special random data. I mean run your actual software on actual data and see whether there's any noticeable difference in time taken. There probably won't be until your data files get big.

That does not execute the two statements at the same time. It executes them in the order shown. One copy of the CLI, running in one process, can do only one thing at a time.

How do you have the CLI execute 2 statements at a time ?

Don't do that. Opening SQLite files using anything but the SQLite API leads to unexpected locks and errors. Plus you don't know how to ensure that a SQLite database file and a WAL file are 'compatible'.

If you're trying to capture the changes to a file, you might find this useful: https://www.sqlite.org/sessionintro.html

Alternatively, call your own function instead of the SQLite one to execute changes. Your function logs the SQL command, then calls the SQLite to execute it.

How is this the responsibility of the SQLite team ? I see something that the TeXnicard team might want registered in the magic database. Surely it's their responsibility to do so.

Tens of thousands of applications use SQLite databases to keep their data. I don't expect the SQLite team to register them all, keep track of the registrations, cancel a registration if the company switches to another format, etc..

On this forum, we often discuss differences between different implementations of SQL, or discuss picky questions about tiny details of SQL and how they 'should' work. Typical examples are those concerning what NULL means when used in various contexts.

We can rarely answer these questions because, as some of you know, the SQL standard is declared by the ISO, and to get a copy of what they think 'SQL' means, you have to pay a lot of money. I think last time I added it all up it was somewhere around US$11,000 for copies of all documents related to SQL. And the standard changes every four to six years and the change notes are not free, so that sum might last just four years.

The ISO earns almost as much from selling copies of the standards as it does from membership fees. It has been categorising more of its publications as chargeable documents without consulting ISO members or the authors of those documents.

A number of high-profile technical writers have called on the ISO to make all their standards publicly available for free. You can find their reasons and names here (eight page document)

https://docs.google.com/document/d/12Gmy2s4Nmkw6VDv2B6b5K1DLYhPrTUqSntrlmYzJpNw/edit

If you're just making a database for a few thousand songs, that will do fine. You would want to find songs which have words which include a substring, and the simplest way to do that in SQLite is to use LIKE as shown here

https://www.sqlitetutorial.net/sqlite-like/

If you're going to be searching hundreds of thousands of songs, it would be faster using SQLite's FTS5. But it's extra work and then you wouldn't be asking us how to do it.

A couple of notes:

A) Song titles aren't unique. Four different songs called "The Power of Love" have been hits. Often two versions were written in the same year, so you can't use year as a disambiguator.

B) Many songs have different versions with different lyrics. Often because the album version has an extra verse, or includes a swearword which would prevent airplay. You're going to have to figure out what to do about this.

Note that using sqlite3_open() doesn't do anything with the file at all. It just sets up a structure in memory. SQLite only looks at the file on storage when you try to do the first thing that needs to know what's in it.

I'd go with your other answer: check the file header to see whether it matches the file-structure documentation.

It is normal to include a copy of the 'starting' database inside the program. This can be a database with schema and possibly data too, and you will probably have run VACUUM on it just before including it in your project.

On startup, the program checks to see if a database file exists in the 'working' folder where it has read/write/delete permissions. If not, it duplicates the one from its own package to that folder. Then it opens that one.

This is the fastest way to get a pre-made copy of schema+data to your working folder. However, it is platform-dependent since you have to know how each platform handles file creation/protection.

Your problem appears to be related to Clarion, not SQLite.

Use the SQLite Command Line Shell to import one of the CSV files, as someone upthread asked. Does it work ? In that case, the problem is with your code, or is internal to Clarion. In that case, you are probably better off asking for help in a forum about Clarion.

If the SQLite Command Line Shell fails to import the CSV file please tell us. We willl be able to investigate since it was written and is supported by the same team which wrote SQLite itself.

Very clever. I'm impressed.

Do you get the same problem if you do an INSERT or DELETE FROM ?

Please be aware that if you try to make a change to a database, SQLite creates a new file in the same folder as the database. Which means in your case that you will need write permissions for the folder your source code is in. Supplying a read-only database inside your project is often done and works fine. Giving your application write permissions to itself represents a horrible security problem and tempts malware.

The standard way to start with a SQLite database containing empty tables and indexes, or pre-prepared data, is to include that database file with the project and have your program duplicate it to a folder where it has read/write permissions.

A SQL (not just SQLite) database is a set of rows. Not an ordered set. The rows of a table are in no particular order. Theoretically you can execute

SELECT * FROM MyTable

twice and get the same answers in a different order each time.

Row numbers normally don't matter: you don't bother assigning them and you would never care enough to retrieve them. If, on the other hand, you want specific row numbers for each of the values you insert, you can assign your row numbers to a column.

Write a little program using .Net Core to create a text file in the same folder on C: . Does it work ?

Hmm. After doing this, if not adding more data back in, would one do VACUUM ?

This is an aspect of "everything must be a SQLite database' I've never considered before.

Think of it as the equivalent of

SELECT FROM MyTable;

It's not allowed in the SQL spec, which insists that at least one value must be returned. So I can see the argument for the same thing when supplying values for insertion.

Please don't use SQLite Studio for debugging. We don't know how it works and we don't know what its error messages mean. Instead, please use the SQLite Command Line Shell:

https://sqlite.org/cli.html

Which you can download as part of the 'Precompiled Binaries' package from

https://sqlite.org/download.html

This program was written by the same team which wrote SQLite itself. If it does anything wrong, they know whether the problem is with that program or with SQLite itself.

Make sure your PAMGuard program – and everything else – doesn't have the SQLite database open. Then use the command line program to open the database and examine it using commands like .schema and .table and by just typing SQL commands. Does that program report that the database file contains what you expected ?

I recommend that right now, not even waiting for a date for updating to a newer SQLite, you use your existing version of SQLite to open the encrypted database and dump it as the set of SQLite commands needed to reconstruct the database. This is easiest done using the .dump command in the SQLite command line shell.

You will probably use that file to get your data into the newer version of SQLite.

Since you felt it worth encrypting the original database you might want to encrypt the dump file, perhaps as an encrypted .zip file.

I can't answer your specific question because I'm not familiar enough with NFS. However before considering using NFS for a SQLite database you should read this:

https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations

Having said that, there's now a lock-testing suite for NFS4:

https://sourceforge.net/projects/locktests/

which looks useful, but I don't know enough to understand the results.

Define your own busy handler which does whatever you want:

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

You can, of course, copy the default one and modify it for your own purposes.

However, along with Ivan, I suspect something weird is going on if a pause of 24 days is not enough for your purposes. It might be to your advantage to tell us what you're really trying to do.

There's no problem with storing a SQLite database on an SD card. You need all privs for card access, including the privs needed to create new files, and to change file attributes.

You don't really 'install' SQLite. SQLite is an API, a programmer's library, a set of functions your program can call. You build it into your program. If you don't want to call Android's own library

https://developer.android.com/training/data-storage/sqlite

then download the amalgamation source code

https://sqlite.org/download.html

add it to your project, and call that. You can find precompiled binaries for Android on the same download page.

The stemmer deliberately changes the word you supply into something else. Stemmers you might see in action right now might produce something that looks like the original word, but the stem is an internal representation, for use by the computer only. When doing your programming you should imagine that the stemmer takes the word 'something' and turns it into '174JGS72'.

You need to keep both strings of text: the original, as supplied by your program/user, and whatever your stemmer produces. They are not the same thing, and if you want them both you have to store them both.

You have 1,000,000 rows, and only 4 possible values. You are searching for all rows other than those with 1 of those 4 values. The column is indexed, so an index is available.

Under those circumstances the index may be useful. SQLite would figure that out automatically, and you don't need to write any special SQL to make it happen. If SQLite doesn't take advantage of the index, it's because it has figured out that reading the whole table and skipping the rows where type=1 is faster than using the index. (Please note that this is more complicated than you think, since SQLite has to retrieve information from the table for the rows you selected, so it has to read the table anyway.)

If you want to be sure SQLite has the best information to choose the fastest methods, put realistic data in the table and use the ANALYZE command:

https://www.sqlite.org/lang_analyze.html

You can use the command just once. The results are stored in the database and, unless the character of your data changes radically, you don't need to ANALYZE again.

In that case, instead of using .dump, the OP should be using

https://www.sqlite.org/sqldiff.html

and then compressing the result. But the analysis phase of sqldiff might take a long time.

Your major complaint seems to be that the text representation of BLOBs doubles their length. I can understand you wanting a custom-written solution for your particular SQLite project. But years of experience tells me you'll be better off with this solution.

1. Use the existing .dump facility to produce the text dump file
2. Use pkzip or some other command-line facility to compress the dump file.

Not only will this compress the BLOBs, it'll compress everything else too. I haven't tried it on databases with big BLOBs but it's very good at compressing normal SQLite database dumps. And it'll do it using standard tools, widely available on many platforms, that you don't have to write and maintain yourself.

Can I ask where you got the idea to use 'excluded.' in those commands ?

Ah, got it.

With regard to all values returned being 5, did you define the column types when you defined the table ? Or was the table definition like

CREATE TABLE MyTable (first, second, third);

? Also note that SQLite does not enforce values to be the same type as a column definition. For instance, you can define a column as INTEGER but put a text value into one row for it. If that row happens to the first one returned by a query, sqlite3_column_type() will return SQLITE_TEXT for that column.

From the page you quoted …

The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value. The value returned by sqlite3_column_type() is only meaningful if no automatic type conversions have occurred for the value in question. After a type conversion, the result of calling sqlite3_column_type() is undefined, though harmless. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion.

SQLite doesn't work in a way that would need that table. For instance, it doesn't use numeric codes, and its internal storage doesn't use classes you might see elsewhere (e.g. C++).

One table which resembles the table you asked for appears here:

https://www.sqlite.org/datatype3.html#affinity_name_examples

but actually I'd recommend you read the whole page since it explains why you won't see the table you asked about.

For technical details about how values of all types are stored in the database, see this page:

https://sqlite.org/fileformat.html

If I misunderstood your question, or can help, please post a reply to this thread.

The RETURNING clause apparently doesn't have access to internal aliases. If you change

old.name

demo.name

Out of interest, can you leave your memory monitoring hooks in place but test this ?

1. Create the table, allowing SQLite to assign ROWIDs, and without the PRIMARY KEY.
2. Load your 77 million rows of data, using transactions to bunch together INSERTs as before
3. Create a UNIQUE INDEX on ('col1','col2','col3')

Does this turn out faster or slower than the procedure you describe above ? Does it allocate more or less memory ?

So you have read-only access to the database but you have read-write access to the drive the database is on. Why would an admin give you this combination of access ?

To get back to your question

1. do you have sole access or is there a chance that some program not under your control has it open ?
2. by 'the state when it was created' do you mean all tables are empty, or there are no tables ?

This worries me, because it suggests you can delete these files while the database is in use.

If SQLite closes the last connection to a database in WAL mode, it deletes the .shm file. So if you see a .shm file on disk SQLite thinks that one or more connection is still using the database, so you shouldn't delete any of the above files.

If you think a previous run of the database has crashed, and these flies really aren't being used, you can check this by using

sqlite_open()
"PRAGMA user_version" (or any other operation which requires reading the file)
sqlite_close()

on the database. If no other connections are using the database then this will delete the .shm file.