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

learn more… | top users | synonyms

0
votes
2answers
44 views

How to JOIN each row ON NOT IN ( SELECT … )?

Say I have a table of users, songs, and songs played. users id: int songs id: int songs_played user_id: int song_id: int If I want to, for each user, find a song that has not yet been played. How ...
1
vote
1answer
255 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
6k views

how to import .xls file into SQLite database?

I have an issue with importing .xls file into SQLite database. I am working on windows xp and using a SQLite manager for maintain the database. I can import the .csv file to the database by using ...
0
votes
1answer
999 views

configure: error: SQLite3 library not found

I'm getting the below configure error while doing the install the Zabbix reporting tool on Linux. i have tried both Versions(zabbix-2.0.9,zabbix-1.8.17), but getting same issue. Log: checking for ...
0
votes
0answers
22 views

SQLite table design for PK and/or UNIQUE

I'm trying to design a table in SQLite3, where I can perform these 2 actions. INSERT new row where a unique primary key is created. And INSERT new row with an given primary key Think of a ...
3
votes
2answers
2k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
1answer
516 views

Constructing an SQL query with time intervals

I've a simple but large table in an SQL database which has four fields (id(int PK), time (unix timestamp, not null), value (double, not null) and an ID_fk (integer foreign key). Data is recorded ...
2
votes
0answers
15 views

SQLITE Foreign key has to be re-added? [closed]

Currently I have a mysql database on my computer which I convert to sqlite database. On the sqlite database, I have transaction_detail table with a foreign key to transaction_master table. The ...
2
votes
1answer
325 views

Configuration options for relational algebra evaluator “ra.jar”

I am participating in the Stanford Database Class, and there are some problem sets to be solved in "mathy" relational algebra syntax. The course instructors demand from students to use this ...
3
votes
1answer
2k views

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

For a new SASS app with postgres 9, that will support disconnected clients. The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other ...
1
vote
1answer
159 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
0
votes
2answers
36 views

Looking for SQLite Free Hosting [closed]

i spend couple of hours on research in google for SQLite Free hosting. I would like to develop a new app using Django (for web app) and Android (for mobile app). Unfortunately i did not find anything. ...
0
votes
1answer
23 views

Text string stored in SQLite Integer column?

I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line: sqlite> .schema mytable CREATE ...
0
votes
2answers
45 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 ...
3
votes
1answer
28 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
31 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
2answers
447 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 ...
15
votes
7answers
13k views

Is it possible to use SQLite as a client-server database? [closed]

Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
1
vote
0answers
55 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
1answer
41 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
20 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
41 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. ...
4
votes
2answers
14k views

select all rows with a minimum value

In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value. I think that I'm limited by not knowing enough of the related terminology to effectively search google. The table ...
1
vote
0answers
209 views

Calculate lower and upper quartile of SQLiteDB via query [closed]

I want to calculate the upper and the lower quartile of the value what I call "diffs" in these fiddle with my DB and the query How can I do this with SQLite? There's an extension on the ...
3
votes
0answers
32 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
31 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
83 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
21 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
75 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
138 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
38 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
63 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
56 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 ...
0
votes
2answers
175 views

How to optimize query

I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there? Select Name, Sum(Case When Used ...
4
votes
2answers
75 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
45 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
202 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
33 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
52 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
67 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
64 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 ...
0
votes
2answers
572 views

How can I simplify this query?

I have written the following SQLite query: SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight' FROM ( SELECT "transaction".transamount AS ...
1
vote
0answers
100 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
2answers
99 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
79 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
82 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
24 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
72 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 ...