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

learn more… | top users | synonyms

0
votes
2answers
22 views

How to optimize a large join to create a WHERE IN

The following sqlite database query takes about 40 seconds on my Core i7. SELECT t.a FROM titles t WHERE t.a IN ( SELECT e.id FROM erpubtbl e JOIN abstract_mesh am ON am.pmid = e.pmid ...
2
votes
1answer
9 views

Why does an sqlite3 .dump and restore result in a smaller database file size?

I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was ...
1
vote
1answer
30 views

Order By with duration strings having time units

I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as : 7Months 26Days 3Years I'd like to order a query results by timeToMaturity ...
1
vote
0answers
21 views

Efficient way to load referenced data in one query [migrated]

My application uses a database to save its data. I have table Objects that looks like localID | title | content 1 Test "1,embed","3,embed","5,append" and another table ...
1
vote
1answer
27 views

parent child records - cascade deletes or transactions?

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data. I'm using SQLite. I know ...
0
votes
0answers
19 views

Updating values with randomly generated values?

I've got an .sqlite3 database here. I want to replace all the values of a set distinct number of columns in the database with randomly generated values (the values should be unique). At the moment ...
0
votes
1answer
37 views

SQLite optimizations for possible huge data storage

I want use SQLite database as the storage for possible huge data size, I need some comments about optimization to keep stability (like unexpexted epic fails, BSOD for example) with fast access speed. ...
3
votes
0answers
29 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
0
votes
0answers
17 views

Storage strategy for polynomials

What is the most space efficient way to store arbitrary length univariate polynomials? I have a large amount of polynomials that look like 3*x^7 + 12*x - 13 9*x^32 - x^14 + 2*x^3 - 9 ... Each ...
2
votes
0answers
28 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here ...
1
vote
1answer
53 views

Query for hierarchical RBAC scenario

I have a SQLite database with a hierarchical role-based access control (RBAC) data model. In this model, users are granted permissions to perform certain actions against various resources either ...
1
vote
0answers
19 views

INSERT DATA The database disk image is malformed

We use sqllite to store our data. Today,when I wanted to insert data in sqllite db,the log showed The database disk image is malformed
0
votes
2answers
49 views

Install DBeaver SQLite driver offline?

I'd like to use DBeaver with SQLite, but the network firewall disallows external connections. So DBeaver cannot download the driver automatically. Where can I download the SQLite driver for it and ...
0
votes
1answer
78 views

SQLite3 - remove duplicates by multiple columns

I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns. Example: id name age x 1 peter 25 ...
0
votes
0answers
28 views

Get milliseconds from a DateTime in Sqlite3?

I have this DateTime in my database: 20130420070028401 is suppose to be: 2013-04-20T07:00:28.401 To "get" the milliseconds from 1970 I'm sub-stringing the DateTime: ...
0
votes
2answers
40 views

Sqlite query slow whilst it should be fast

I have the following query: SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z FROM BlocksBrokenByExplosion WHERE dimension=0 AND y>60 AND y<70 AND x>150 AND ...
0
votes
1answer
51 views

Having four different tables or having one table with one more field?

I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and ...
4
votes
2answers
62 views

Converting outer apply with Correlated subquery in SQL Server to SQLite

I need to convert a query in SQL Server to SQlite and I have replaced the outer apply to left outer join, but how to limit the subquery to return only 1 in this situation? And how to pass the ...
2
votes
0answers
43 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
0
votes
1answer
129 views

SQLite CSV import won't create a table automatically

According to https://www.sqlite.org/cli.html#csv, sqlite> .mode csv sqlite> .import C:/work/somedata.csv tab1 There are two cases to consider: (1) Table "tab1" does not previously ...
0
votes
1answer
27 views

Issue with accented characters in SQLiteStudio

I downloaded a SQLite 3 dump of Northwind, and opened it in SQLiteStudio. Accented characters aren't displayed nicely. How to solve the issue? I suspect some encoding issue.
0
votes
1answer
48 views

Add row after every rows of a table from select statement

There are two tables. I want to add every record of one table under every record of the other table. The two tables and the result table are shown below. TABLE_1 TABLE_2 TABLE_3 TYPE ...
0
votes
1answer
43 views

can't create foreign key in sqlite

I'm trying to run this code in sqlite: sqlite> CREATE TABLE test(id integer primary key AUTOINCREMENT, page varchar(10), dp_record_id integer, foreign key (webserver_id) REFERENCES ...
1
vote
1answer
58 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
1
vote
0answers
83 views

Are there any known benchmarks/statistics on UnQLite?

I came across UnQLite the other day in Stack Overflow and it has me intrigued for some software I'm working on. However, very few tests have been published on how well it performs. There are plenty of ...
1
vote
0answers
52 views

SQLite insertion with junction table

I need to write a transaction for SQLite, that adds a blog post and links it to content tags, and it seems to be a bit too complicated for me. Here's an example schema: CREATE TABLE blogpost( ...
1
vote
2answers
353 views

Create database with multiple columns using SQLite in Android Studio

I want to create a database with 3 columns (name of point of interest, longitude and latitude). Since I am completely new to databases I am using an example I found online and try to change it to my ...
1
vote
2answers
84 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
0
votes
1answer
76 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
1
vote
1answer
20 views

Should I use VACUUM on a SQLite db with a log-structured filesystem?

I'm runnig a SQLite database on nilfs (which is a log-structured filesystem). Every now and then I delete old records, so that the size of the database never surpass a certain amount. But since the ...
0
votes
0answers
63 views

How to divide a big SQL Query into small query

I am trying to traverse the whole file system. I am picking each and every file and then trying to insert it into the SQLite Database. Before inserting I am also calculating the checksum of each file. ...
0
votes
0answers
23 views

Help in Designing a CBT database

Please i need help in modifying or remaking the database so that i can mark answers to questions immediately. The problem is some courses have 50 questions, while others have 100. How can i design ...
0
votes
0answers
58 views

trying to a lag a column in SQLite

Sorry if this is too basic for this forum, but I have had no luck in StackOverflow. I am a newbie to SQL. I am using SQLite. I have a table where rows represent individuals in a point in time. I am ...
0
votes
1answer
35 views

Is it correct to dynamically add rows to a table in sqlite, or should I use csv? (I'm working with objective c.)

Just like when adding a new contact in the ios contact app, you can add multiple phone numbers, and it doesn't know how many there will be, so therefore they don't know how many rows they should ...
0
votes
0answers
33 views

Bitfield in Database

What is the best way to store a Bitfield of 960 values in a Sqlite3-Database? I need to do queries of testing if the x-bit is set.
0
votes
1answer
142 views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
1
vote
1answer
225 views

SQLite disk I/O error (3850)

I can't seem to figure out what is causing the I/O Error: $sqlite3 db.sqlite < data.sql (3850) disk I/O error Error: near line 2: disk I/O error (3850) disk I/O error Error: near line 3: disk ...
0
votes
1answer
52 views

How to define the table structure for measured data in sqlite

I have an embedded system with an sqlite database. How should I define the table structure for the measured data from 4 sensors? Each sensor give us every second an numeric measured value. The ...
0
votes
0answers
112 views

Options for fastest guaranteed writes to a SQLite Shared cache in memory database

I have a multi-threaded python process, and I am using SQLite to store data between the processes. The flow is as follows: Main process created named in memory database, and table within the ...
0
votes
0answers
66 views

SQLite 3 Tree List Without CTE

I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite ...
1
vote
1answer
21 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
2
votes
1answer
62 views

How to enforce an “exactly-one-per-group” constraint?

I have a table x that I've defined like this: CREATE TABLE x ( xid INTEGER NOT NULL PRIMARY KEY, yid INTEGER NOT NULL REFERENCES y(yid), is_principal BOOLEAN NOT NULL ); This definition ...
1
vote
1answer
54 views

Will my sqlite database occupy less space if I insert nulls instead of zeros

I have a table where most of the values inserted in one of the columns are 0. Should the database be smaller if I insert nulls instead of zeros?
0
votes
0answers
56 views

Unable to sort this table for the specified condition in SQLite

I have this table given below with the following details. I am trying to sort this table on a specific way. Here's a sample screen shot of the table. Following is the table structure: Table ...
-2
votes
1answer
71 views

SQL: how to scan an entire table sequentially from top to bottom

How do I 'scan' all the way through an SQL table (approx 120k rows/50 columns) from the top/beginning, all the way to the bottom/end, and for each row found, I will need to 'do something'. The 'do ...
3
votes
1answer
62 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type ...
1
vote
1answer
56 views

Child records under the parent record in SQLite [closed]

I have two tables for Billing, one Bill_Master and other is Bill_Detail. The records in both tables are as follows... **BILL_MASTER** id party bill_amount 1 abc 500 2 def ...
1
vote
2answers
108 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
0
votes
1answer
221 views

DBEAVER: SQL error or missing database (near “,”: syntax error)

I'm using DBeaver (because Base only works on OS X) to work with an SQLite database. When I attempt to execute the following query, I get the error message 'SQL error or missing database (near ",": ...
0
votes
0answers
107 views

Insert values from one database table to other database table through shell scripting

I am working with 2 databases say logdb.db and logdb2.db . I have created a table named Original in logdb.db Sqlite database such as follows : CREATE TABLE Original (s_id INT PRIMARY KEY NOT NULL ...