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
2
votes
0answers
18 views

History of updated rows

Imagine we have a CRUD application in Python and are using SQLite. Let's say we have in SQLite a table called CUSTOMERS: CREATE TABLE CUSTOMERS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT ...
0
votes
1answer
37 views

sqlite - very slow performance of WHERE EXISTS without index on EXISTS

I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause. For example: UPDATE tbl1 SET col1 = "Y" WHERE EXISTS (SELECT ...
0
votes
1answer
24 views

How can I maintain the dateonly field

Hello I have a table in Postgres and one of the fields is type Date with the format '2019-09-10' and I'm transferring this table into a SQLite one BUT in this SQLite I'm getting this field as Date ...
0
votes
0answers
13 views

Determine whether SQLite database is locked

I've read other answers on how to detect whether the SQLite database is locked, and it suggests to use sqlite3_busy_handler/sqlite3_busy_timeout. I'm using Qt which has support of SQLite databases but ...
0
votes
1answer
17 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
0
votes
0answers
28 views

How do I migrate mysql .frm and .ibd files to sqlite?

Mysql seems to not work anymore on my machine. I have spend way too much time trying to fix it but without success. But I found out that sqlite does all the stuff I need so why not just change to it. ...
0
votes
1answer
29 views

SQLite - Get all other rows with any duplicate column value

I have a table of users, with information about them and the combination of the fields and UserName is unique (A row as a whole, not only on one field) Users +----------+--------+--------+--------+ |...
0
votes
2answers
30 views

Database design question: Tags with values(duration) linked to them

I am creating a database that takes in daily inputs such as: Date Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...) Rating of the date There's more but these are probably the ...
0
votes
1answer
34 views

Syntax error in simple trigger

I have a SQLite table with these fields among others Fld_QuoteNumberRoot string Fld_Revision integer Fld_QuoteNumber string I need to fill ...
1
vote
2answers
66 views

how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)

My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages. Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (...
2
votes
2answers
34 views

How to find exact string from a sentence

I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
1answer
28 views

Listing IDs that match values in two columns of a table

I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense. My use case is that I have a letter (in ...
0
votes
3answers
41 views

SQLite: Leave only n rows for each field

I have a table +-----------------------------+---------------+-----------+ | Painter | Painting | Rate | +-----------------------------+---------------+-----------+ | ...
1
vote
2answers
36 views

How to set “PRAGMA foreign_keys = ON” each time I'm connecting to sqlite db via Datagrip

In my pet project I'm working with small sqlite db and I've just came to realization that, in order to ON DELETE CASCADE actually do the thing, I need to set PRAGMA foreign_keys = ON each time I'm ...
1
vote
1answer
36 views

Is there any advantage to putting some columns in another table, or should I just put them in the same table?

I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows. An example of some of the columns in this table are: location_contrivedkey --(...
1
vote
3answers
75 views

Split Multiple Columns Into Multiple Columns by Delimiter

I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary. An example table, showing how the table ...
0
votes
0answers
36 views

Common way to handle a changing reference in dependency of status

I am new to SQL an SQLite, so this is probably a silly question. I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
1
vote
2answers
63 views

Deduplication/normalization of string column on insert

I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
0
votes
0answers
37 views

Laying out the database schema for an interactive novel

I'm trying to create an interactive story telegram bot on Node-red. To store the story I'm using SQLite. How it works: Bot shows you a piece of the story, then shows some answers (buttons) regarding ...
0
votes
1answer
94 views

Updating a column in one table based on matches with column in another table

I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = ...
0
votes
1answer
14 views

How to AUTOINCREMENT against another column rather than the whole table in sqlite?

I'm using a table to track messages sent in several different channels. I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'...
1
vote
1answer
49 views

How to insert values to a table from 2 foreign keys

Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the ...
0
votes
1answer
58 views

What is the correct way to select rows from either side of a bridge table?

I want to do an SQL query for three columns. The database has five tables, two of which are bridge tables for the other three. When I attempt to select from the tables, I get a very large number of ...
0
votes
0answers
41 views

How to deal with refrencing a foreign key to two tables?

I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible. The issue I have is with the section shown above as it is a sub-...
0
votes
3answers
51 views

Usage of aggregate function with when self-joining a table

I want to find people from a database who has a weight bigger then the average weight: SELECT * FROM Player p1, Player p2 WHERE p1.weight > avg(p2.weight) But I have an error: Result: misuse of ...
0
votes
1answer
36 views

Looping over column names in SQLite and running a command for each column name

I'm trying to run the following command UPDATE attachments SET colname=TRIM(colname); on every column name in a SQLite table. I found the following approach to getting a list of column names in an ...
1
vote
1answer
20 views

Which part of a DB bulk insert takes the most time?

One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
2
votes
1answer
92 views

How to design a parent-child relationship that includes child priority/order

I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a ...
0
votes
1answer
20 views

SQLite return sum of duplicate rows based on array

If I have a simple table fruits that looks like this: +---+---------+------+ | | name | lbs | +---+---------+------+ | 1 | apple | 0.25 | | 2 | melon | 3.00 | +---+---------+------+ In a ...
0
votes
1answer
23 views

SELECT column for a row only if its value is not empty

I want to display a certain column only if its value is not empty. Pseudo code: SELECT column1, IF_NOT_EMPTY(column2), ... FROM table; Will display all columns except column2 if column2 is empty.
-1
votes
1answer
33 views

MySql data export to import in sqlite [closed]

This question has been asked a few times. However the links all lead to how to export the db such as schema and import that into sqlite. My scenario I already have an sqllite db with the tables. I ...
0
votes
1answer
19 views

What kind of a lock does IN keyword takes in SQL

If I have a query in which I have used IN keyword than what kind of lock will be taken by IN keyword. Query is : UPDATE table1 SET b_id where id in (SELECT id from table1 where id IS NULL ) I ...
0
votes
0answers
26 views

Getting Database is locked error [duplicate]

I have a query in which I am updating a table and have used IN keyword in the query. The query is something like this: UPDATE q SET b_id = 123 WHERE id IN (SELECT id FROM q WHERE b_id IS NULL ...
1
vote
2answers
170 views

sqlite Json grouping json_each

I want to generate json grouping users by year. I have got a "user" table in sqlite with the following columns: date name surname value I would like to create a JSON with the following structure: ...
1
vote
1answer
52 views

Multiple concurrent connections to same large SQLite file on a network share

I have a 7GB SQLite file and I am running multiple queries on it from different virtual machines in the cloud. Some of the queries are running in Python using the built in sqlite3 package, and others ...
1
vote
1answer
55 views

Top 10,20,30 in Group

Morning all! I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things. One of the ...
0
votes
0answers
13 views

Want to be able to use sqlite to organize photos so that they can be selected by who is in the photograh

I want to create an sqlite db that has photograhs of a band's various gigs they have played at. I want to be able to search the database by event,but also (and more importantly) by who is in the ...
0
votes
1answer
29 views

How to multi-match two tables efficiently?

I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both ...
0
votes
1answer
112 views

Overhead of triggers and foreign key constraints?

I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, ...
0
votes
1answer
34 views

sqlite3 command does not save database after creation?

I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL. It appears the database and tables are created, but then sqlite3 command does ...
0
votes
1answer
271 views

Comparing multiple tables in Sqlite 3 using python:

I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with ...
0
votes
0answers
28 views

NoSQL big DB retrieve performance

A question about retrieving performance for simple but bug key-value DBs on python3. I am working on a pipeline to compare several entries based on key-values (genetic variants found in patients with ...
0
votes
1answer
83 views

Group json data in db using a query

I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really ...
1
vote
1answer
41 views

How to correctly use group by for getting the record with the closest time for each rec_id

Background I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of ...
5
votes
1answer
60 views

Why an incorrect JOIN using correlated sub-query is so much slower

I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
1
vote
1answer
51 views

SQLITE: Select row ID based on only when the column all the values are same

I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done. tb tbid ttid status ----------------------------- 216 6 5 done 216 6 ...
0
votes
0answers
133 views

How to ignore timezone in sqlite?

I have a table: CREATE TABLE "date" ( "datetime" TEXT, PRIMARY KEY("datetime") ); and rows: datetime -------- 2019-02-01 01:30+03:00 2019-02-01 02:30:10-03:00 ... For example, my timezone ...
1
vote
1answer
537 views

SQLITE: Multiple foreign key referenced to multiple table cascade delete

In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
0
votes
1answer
32 views

How to denormalize many-to-many relation without nested select?

I have the following schema: house has many students, student has one house gender has many students, student has one gender game has many students. student has many games For instance, consider ...
1
vote
1answer
385 views

how to filter the resulted table in dbeaver?

I have big sqlite table that I filtered one column with IN statement from the GUI. It worked will but I want to filter another second column also based on the first selected. eg full table looks like. ...