Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
704 questions
0
votes
1
answer
23
views
How to link a virtual FTS table to another table in SQLite and enforce referential integrity?
I've been reading up on the full text search documentation in SQLite and it all makes sense, except there appears to be no accommodation made for actually being able to link FTS rows with rows in ...
- 697
3
votes
3
answers
509
views
Table structure with multiple foreign keys and values
I'm developing a desktop application to help the technician to easily provide updates to an embedded system.
The background is very simple the database (SQLite) have several tables, not identical but ...
- 143
1
vote
1
answer
41
views
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 ...
- 111
0
votes
1
answer
26
views
Migrating an existing sqlite database to postgresql
I'm trying to use pgloader to migrate an existing SQLite database to PostgreSQL. In a table of the database there are some columns of type boolean, since there is no dedicated boolean type in SQLite ...
- 101
0
votes
1
answer
50
views
How to know wether a DELETE CASCADE will delete child records?
Lets say that I have a table A which is referenced by table B. Each record in table A may be referenced by multiple records from table B. This would be the classic "A has many B".
Table B ...
- 11
0
votes
1
answer
50
views
Sqlite fetching speed problem - how to understand explain and compare two queries
I need to join two tables product_features and feature_text to get this output
From a previous question i got two answers that both return the expected result for a small sample set: join so that
...
- 219
0
votes
2
answers
64
views
How can I select all rows from two SQLite tables, but prefer one if there are matching rows in both?
I have two tables, each with a round and an id column and additional data columns. One table is the planned version, the other one is the actual one. E.g.:
planned:
round | id | value
------+----+-----...
- 137
-1
votes
1
answer
50
views
Return data in the same order that I have inserted it with Sqlite3
I'm working with, I think, the lastest version of Sqlite and C++. I have a table with data, and I need to return this data in the order I inserted it.
Do I have any guarantee that the columns will ...
- 1,873
-1
votes
1
answer
33
views
I keep getting NULL results
I have 2 tables i am working on.
dbo_Returns1 - which has customer-id, return_date, returns, irder_id and
dbo_Sales1 which has customer_id, sales, sales_date, order_id
Question 1: What % of sales ...
0
votes
1
answer
58
views
I need to move from sqlite to a distributed setup. What are my options?
I have an sqlite db that has grown to 30gb and I'm still pushing data to it everyday. I have a couple services that write to it using libsqlite3. Soon it will become too big to keep locally. What is ...
- 103
2
votes
1
answer
94
views
Foreign Key Error in SQLite3 CREATE TABLE Command
Looking for fresh eyes on this, I'm running the command in sqlite3 interactive and can't see the problem. Here's the code:
CREATE TABLE Personas(
PersonID INTEGER NOT NULL PRIMARY KEY,
...
- 123
2
votes
2
answers
75
views
How can I find a topmost row and count the rows with only one scan?
Suppose that I have a schema like the following:
-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);
-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
SELECT ...
- 123
0
votes
1
answer
81
views
When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?
I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
- 439
0
votes
1
answer
371
views
How Whatsapp Search/Index Encrypted Chats specially IndexedDB, Searchable Encryption
Our team has developed a Hybrid app which is based on IndexedDB to support cross platforms. This app now has a chat feature and we want to store chats for offline readability. But as a rule of thumb, ...
- 101
0
votes
1
answer
85
views
Create an affiliate system (also known as a referral program like Rewardful, Tapfiliate) in SQLite?
I want to create a simple affiliate system that pays affiliates who refer a customer a commission of $100.
I currently have the following tables generated via an ORM.
CREATE TABLE `session` (
`id` ...
1
vote
1
answer
56
views
SQLite: How to include an Sqlean define statement in a `.sql` file?
I need to generate an SQL file containing UDF defined with the extension 'define' of Sqlean.
Here is a minimum non-working dump.sql file:
SELECT load_extension('path/to/sqlean/define');
SELECT define('...
- 121
0
votes
1
answer
115
views
Alternative to multi-master replication between local and cloud db when writes are infrequent and only 1 db at a time
Background:
I have a closed-source app on my laptop that saves its data in a local SQLite database. I'm working on creating a mobile app for myself that would replicate the functionality of the ...
-1
votes
1
answer
154
views
Embedding PHP variable into Sqlite statement
I have been struggling with this for hours. I am pretty new to Sqlite and have been trying to write a prepared statement, everything works up until I try to get my variable in.
The following works as ...
- 99
0
votes
1
answer
58
views
What is the best data type for an array like structure in SQLite?
I have a table that stores tracks and I want to add sector times for each track. Each track could have a different number of sectors, the same track will always have the same number of sectors and the ...
- 115
0
votes
2
answers
61
views
Select random records until a cumulative total is exceeded
I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X.
select ...
- 103
0
votes
1
answer
63
views
Is my schema "correct"?
Is my proposed schema below prone to make overly complex and slow running queries? Is there a better way to layout this database?
The situation:
I have no formal DBA training.
tl;dr section below this ...
0
votes
1
answer
178
views
Stop sqlite resetting autoincrement on primary key
If you delete the rows on the end of the table, the autoincrement resets:
sqlite> CREATE TABLE my_table (
id INTEGER PRIMARY KEY
);
...
sqlite> SELECT * FROM my_table;
1
2
3
sqlite> ...
- 103
1
vote
1
answer
61
views
How do you only get data from the table with the foreign key using SQLite?
I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table.
I have tried an inner join and I am getting weird ...
- 115
1
vote
1
answer
43
views
SQLite - Update of a field in two steps without variable (SpatiaLite)
I work with SpatiaLite, managing databases through SQLite. Unfortunately, this system doesn't allow the use of variables, or at least, I haven't discovered how to use them yet.
My objective is to ...
- 121
0
votes
1
answer
28
views
Syntaxis Problem when creating a trigger in Mysqlite
how u doing?,
I have a problem that I don't understand, when I create a Trigger in Mysqlite, check it:
c.execute("""CREATE TRIGGER ahoraSI BEFORE INSERT ON Productos
FOR ...
- 1
0
votes
1
answer
27
views
select user with least amount of tiquets between all other users
I have a table where I store users info like this
user
tickets
s.lewis
0
a.barrantes
2
x.mendez
3
j.robert
0
I want to create a query to select the user that has the least amount of tickets ...
- 11
1
vote
0
answers
35
views
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer ...
- 984
1
vote
3
answers
246
views
Single-tenant SQLite vs Postgres + SQLite
About the app
I am building an application which will have 2 clients: web and mobile. Both clients will need to auth to sync the user data. On average, each user will generate 2000 rows of data.
The ...
- 123
1
vote
1
answer
58
views
Optimizing the SQLite database as source data for R scripts
I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
- 111
-1
votes
1
answer
855
views
sqlite3.OperationalError: no such column: stock_name
#user_input.py
from flask import Flask, request, jsonify
import sqlite3
app = Flask(__name__)
def process_user_input(data):
# Retrieve user input from the data
stock_name = data.get('...
- 107
1
vote
0
answers
26
views
How do I migrate SQLite to Postgres for OWASP Juice Shop?
I tried to make a .sql but it's failing.
My files here: https://drive.google.com/drive/folders/1BAs5YFPWpShzIgtd-NppL0iWZ_HywWNf?usp=sharing
I also tried using pgloader but that was even more ...
- 115
1
vote
1
answer
102
views
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 ...
- 119
3
votes
2
answers
356
views
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 ...
- 418
2
votes
0
answers
23
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 219
2
votes
1
answer
153
views
Column accepting more characters than defined
I have a column defined as character varying(20). But it is accepting more than 20 characters. I've already searched but couldn't find anything about it (maybe I'm searching wrong). Has anyone ...
- 23
1
vote
1
answer
327
views
SQL Lite is giving an "index out of range" error
When I run this code in Rust
statement.read::<i64, _>("creation_utc").unwrap(),
I get,
Error { code: None, message: Some("the index is out of range (creation_utc)") }
But ...
- 64.7k
1
vote
1
answer
321
views
Query for repeating sequences in SQLite
I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.
consider this example (http://sqlfiddle....
- 167
0
votes
1
answer
27
views
How do we recurse through this table?
I have a simple family tree table that includes:
PIN Numeric, primary key
FATHER_PIN ID of father
MOTHER_PIN ID of mother
I want to recurse through the tree with:
WITH RECURSIVE
parent_of(PIN, ...
- 111
0
votes
3
answers
50
views
Can you formulate an SQL command for this case?
I have an SQLite table PERSONS with the following columns, all integers:
PIN (unique ID)
SEX (1 = male, 2 = female)
FATHER_PIN (PIN of person's father)
MOTHER_PIN
I want to find all couples that had ...
- 111
0
votes
2
answers
39
views
In GRASS GIS db.execute IN statement works, but NOT IN doesn't
Here is my command for GRASS GIS database (SQL Lite driver used):
db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream IN (SELECT number FROM ...
2
votes
1
answer
263
views
DBMI-SQLite driver error: Error in sqlite3_prepare(): near ".": syntax error in db.execute command
I try to make a query with inner join from grass gis (but in that community this question was concerned as offtopic, so it seems to concern only SQL lite):
db.execute sql="UPDATE A SET A.next2=B....
0
votes
2
answers
79
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 123
0
votes
0
answers
139
views
SQLite Upsert - syntax error
I'm trying to use SQLite's upsert syntax as described in their documentation
However I get the following error, in both WebSql (SQLite v3.41.2) and SQLite on Android (SQLite v3.40.0)
could not prepare ...
- 51
1
vote
1
answer
135
views
storing histogram in sqlite database
I am not a database administrator, but only a scientist who would appreciate your help in solving my issue with storing histogram data in an SQLite database.
I have several of them to be stored and to ...
- 111
0
votes
1
answer
153
views
Order the same column by ascending or descending dependant on the value of another column
I've got a situation where I want to order a column by ascending or descending based on the value of a second column (in essence, if column A is odd, sort column B ascending, otherwise descending). ...
- 3
2
votes
1
answer
78
views
Database design suggestion for improving existing design
Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
Each sensor can support multiple features....
- 121
2
votes
2
answers
91
views
What is the evaluation clause order in SQLite?
Traditionally in SQL, the main clauses are evaluated as:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Among other things, it explains why you can’t use alias from the SELECT clause in other clauses ...
- 2,919
1
vote
1
answer
52
views
update row with age calculation with DOB in related table
SQLite database with a table "events" with a columns events.eventDate, events.ageAtEvent and events.foreignKey_participants, and a table "participants" with columns participants....
1
vote
0
answers
14
views
Sum profits of sales for every sale in SQLite using avg purchase price?
In my SQLite database i have the following tables and data:
Products:
pro_id. pro_name
1. Item1
Purchases:
pur_id pro_id. pur_qty. pur_price
1. 1. 10. 10
2. 1. ...
- 111
0
votes
2
answers
40
views
How to sum profits of all products using AVG prices in SQLite?
In my SQLite I have the following tables:
Products:
Pro_id. Pro_name
1. Item1
2. Item2
3. Item3
4. Item4
Purchases:
Pro_id. Pur_Qty Pur_Price
1. 10. 10
...
- 111