Questions tagged [sqlite]

A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.

Filter by
Sorted by
Tagged with
0 votes
0 answers
7 views

GRASS GIS lost connection with database, but connection still exist in fact

GRASS GIS is based on sqlite. After I imported a big vector file and table in sqlite.db was successfully created, there were some operations then I got an error: DBMI-SQLite driver error: Error in ...
Nikolay Yasinskiy's user avatar
0 votes
0 answers
8 views

How to employ peer-2-peer connections with Flutter (and MongoDB)?

I'm tinkering with the idea of making a Flutter application which uses MongoDB as the database. Since I especially want to use a database optimized for mobile usage, I thought MongoDB including ...
User2363562's user avatar
0 votes
1 answer
41 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, ...
Abdul Jabbar's user avatar
0 votes
1 answer
46 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` ...
deadcoder0904's user avatar
1 vote
1 answer
20 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('...
Aristide's user avatar
  • 121
0 votes
1 answer
26 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 ...
mikemykhaylov's user avatar
-1 votes
1 answer
34 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 ...
Fintan Creaven's user avatar
0 votes
1 answer
41 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 ...
av4625's user avatar
  • 115
0 votes
2 answers
43 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 ...
Ashley's user avatar
  • 103
0 votes
1 answer
56 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 ...
HDL_CinC_Dragon's user avatar
0 votes
1 answer
53 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> ...
Tom Huntington's user avatar
1 vote
1 answer
26 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 ...
av4625's user avatar
  • 115
1 vote
1 answer
25 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 ...
Giene's user avatar
  • 121
0 votes
1 answer
18 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 ...
Carlos's user avatar
  • 1
0 votes
1 answer
23 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 ...
Sarah Lewis's user avatar
1 vote
0 answers
32 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 ...
White Owl's user avatar
  • 797
1 vote
3 answers
94 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 ...
GRS's user avatar
  • 123
1 vote
1 answer
49 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 ...
tomsu's user avatar
  • 111
-1 votes
1 answer
372 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('...
Steven's user avatar
  • 107
1 vote
0 answers
13 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 ...
mLstudent33's user avatar
1 vote
1 answer
59 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 ...
Harshal Patil's user avatar
3 votes
2 answers
215 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 ...
mvorisek's user avatar
  • 418
2 votes
0 answers
18 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 ...
surfmuggle's user avatar
2 votes
1 answer
67 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 ...
FranS's user avatar
  • 23
1 vote
1 answer
87 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 ...
Evan Carroll's user avatar
  • 63.2k
1 vote
1 answer
205 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....
yossico's user avatar
  • 167
0 votes
1 answer
19 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, ...
Pierre's user avatar
  • 111
0 votes
3 answers
48 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 ...
Pierre's user avatar
  • 111
0 votes
0 answers
20 views

Visual editor for imputing sqlite foreign key ids using another field

Here's my use case: I want to build a simple database that maps a reverse DNS domain back to an email sending service, and information about that service. I have a table of email source information ...
Sean W.'s user avatar
  • 125
0 votes
2 answers
37 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 ...
Nikolay Yasinskiy's user avatar
2 votes
1 answer
178 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....
Nikolay Yasinskiy's user avatar
0 votes
2 answers
47 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 ...
user570286's user avatar
0 votes
0 answers
117 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 ...
Eriedor's user avatar
  • 51
1 vote
1 answer
105 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 ...
user41796's user avatar
0 votes
1 answer
83 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). ...
Toll's user avatar
  • 3
2 votes
1 answer
71 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....
Hem Bhagat's user avatar
2 votes
2 answers
68 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 ...
Manngo's user avatar
  • 2,819
1 vote
1 answer
28 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....
Stam Kapetanakis's user avatar
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. ...
Anti Atlas Dev's user avatar
0 votes
2 answers
33 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 ...
Anti Atlas Dev's user avatar
1 vote
1 answer
306 views

SQLite on AWS EBS vs MySQL AWS RDS

I'm planning to host a web application that consists of a Front-end, Back-end (Written in NestJS + TypeORM). This Web application requires a database like many others. Something I'm struggling with ...
CXY's user avatar
  • 11
2 votes
1 answer
83 views

script SQL equivalent

I have this SQLite script: CREATE TRIGGER ACCOUNT_UPDATE_DELETE_SESSION_ID_NULL AFTER UPDATE OF DELETE_SESSION_ID ON ACCOUNT FOR EACH ROW WHEN OLD.DELETE_SESSION_ID IS NOT NULL AND NEW....
INTERNECIO's user avatar
0 votes
1 answer
181 views

Same data stored in more rows with more repetition results in smaller SQLITE file?

I'm comparing two very barebones SQLite databases with the same data stored differently. Database A has tables like this: string (TEXT UNIQUE) count_1 (INTEGER) count_2 (INTEGER) the 23 10 it 56 44 ...
Nico's user avatar
  • 13
2 votes
1 answer
437 views

UPSERT fails for INSERT expression with SELECT

I ran into a problem with the UPSERT statement in SQLite. I have a master and a slave database, the updates to the slave database should be merged into the master database. I would like to use the ...
flacer's user avatar
  • 23
3 votes
1 answer
34 views

Help with SQLite query to generate new table based on lookups

Sorry, I can't think of a more specific title. SQLFiddle here: http://sqlfiddle.com/#!5/cfed3/1/0 I have a BaseData table Name ParamId NumericValue A 202 30 B 203 69 O 203 50 A 203 70 I 204 2 B ...
datguyray's user avatar
0 votes
1 answer
154 views

What's the easiest way to implement division in SQLite?

I'm putting together a simple cooking application that holds recipes in an SQLite database. I have three tables right now: Recipes, Ingredients, Recipe_Ingredients * Recipes ------------------- name, -...
Izzo's user avatar
  • 141
2 votes
0 answers
140 views

Is my food-tracker's database designed properly? (ER diagram + SQL Relations)

My idea: The database will store foods, days, and "inserted foods" aka diet_logs (days and diet_logs have both existence dependency) for each diet_log of a specific day, I will create a ...
climaxMa's user avatar
1 vote
2 answers
280 views

Calculating the total time a device is on per day

At the moment I've got an sqlite3 database that keeps track of the state of my smart home devices. The relevant parts of the schema for the main table are CREATE TABLE states( state_id INTEGER NOT ...
CopOnTheRun's user avatar
5 votes
1 answer
1k views

How does offline-first database design work?

I would like to make an offline-first To-do app which works on both the mobile app (SQLite) and the web app (PostgreSQL). The app will have these business models: User: id name created_datetime Todo:...
Robin's user avatar
  • 151
1 vote
0 answers
190 views

Is it possible to split aggregated text into multiple sqlite3 columns?

I'm trying to pivot rows into columns on an SQLite3 database. This is not a native function on this DB, so I thought it could have been possible to manage the task via cyclically running a set instr/...
Andrea Moro's user avatar

1
2 3 4 5
14