Questions tagged [sqlite3]
41 questions
8
votes
2 answers
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) without rowid;
filled with millions of unique…
std_unordered_map
- 83
- 4
3
votes
2 answers
Sqlite comparison of the same operand types behaves differently
Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:
If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC…
mvorisek
- 428
- 1
- 5
- 20
3
votes
2 answers
Retrieving the local timezone inside SQLite
I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:
date +%FT%T%:z
I can almost generate it from inside SQLite by…
Tobias Leupold
- 137
- 1
- 7
3
votes
2 answers
1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "
I have 2 processes that use the same Sqlite3 database.
1st one -- in NodeJs, is a program which constractly writes data into a database, with the frequency of around 1 write / 1 second. It only writes data.
2nd one -- in Golang, is a web…
Kum
- 47
- 1
- 5
2
votes
1 answer
How to group by with similar group_name in sql
How can I perform a
GROUP BY
in SQL when the group_name values are similar but not exactly the same?
In my dataset, the group_name values may differ slightly (e.g., "Apple Inc.", "AAPL", "Apple"), but conceptually they refer to the same entity.…
Ahamad
- 129
- 4
2
votes
1 answer
Understanding `WHERE x = NEW.x` Syntax
I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application.
The first time a request is made to the…
студент001
- 123
- 5
1
vote
1 answer
How to know, when it is time to vacuum an sqlite database file?
Sqlite, contrary most SQL-compatible DB engine, is working directly on files instead over network sockets. It is also not very good in concurrency, beside that deletion operators are mostly advisory (i.e. do not free the places of the deleted…
peterh
- 2,137
- 8
- 28
- 41
1
vote
3 answers
Sqlite transaction not released when there are other readers of the database
If I have multiple database readers, which are basically instances of Sqlite that open the db file in "read" mode, is it normal that for a single writer that opens the db in "create" mode to not be able to release a transaction until all readers…
Alex
- 181
- 3
- 11
1
vote
1 answer
Issues with Self-Referencing Foreign Key in SQLite
I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table definition:
CREATE TABLE Employees (
Employee_ID…
reubenjohn
- 111
- 3
1
vote
2 answers
SQLite Join 2 tables with table1 having two columns that reference table2
I have two tables:
jobs
CREATE TABLE IF NOT EXISTS jobs (
job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
job_name TEXT,
prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id),
scnd_desc INTEGER NOT NULL REFERENCES…
Erich4792
- 13
- 3
1
vote
1 answer
Is frequent opening and closing of the SQLite file expensive operation?
I need to build a multi-tenant solution with a requirement for strict data isolation (meaning isolated Databases at tenant level). Even the authentication is different for each tenant?
I find that SQLite with all its compromises to be a right…
Harshal Patil
- 119
- 3
1
vote
1 answer
insert trigger to auto-set key fields for composite primary key
Basically the next step for Error "UNIQUE constraint failed" with composite primary key:
I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one.
Basically:
CREATE TRIGGER TRG_NAMES_ADD2 AFTER INSERT ON NAMES…
U. Windl
- 125
- 6
1
vote
0 answers
is it possible to repair a partially downloaded sqlite3 file
I was only able to partially download a SQLite3 database file, is it possible to repair the file so that it can be opened?
Unfortunately the device where the file came from is out of power and irretrievable.
If anybody can point me in the right…
Ben Bird
- 111
- 2
1
vote
0 answers
How do I make sure a specific TRIGGER will be executed only when other TRIGGERS are finished executing and return?
foo table
| row# | foo_col1 | foo_col2 | foo_col3 |
| -----|----------|----------|----------|
| 0 | 1 | 5 | 1 |
| 1 | 2 | 3 | 3 |
| 2 | 3 | 2 | 4 |
foo_1 table
| row# |…
Paulo
- 11
- 2
1
vote
0 answers
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type = heading then it can only have one Heading…
Riajul
- 111
- 3