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

learn more… | top users | synonyms

1
vote
2answers
47 views

Search across all columns in a table

As the title says, I want to create a query that searches a keyword across all columns in that table. So I want to simplify this query: SELECT * FROM tblCustomers WHERE (col1 LIKE '%keyword%' OR ...
3
votes
1answer
433 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 ...
1
vote
1answer
174 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 ...
4
votes
2answers
2k views

How do I force SQLite to materialize a subquery?

Because SQLite sometimes evaluates functions like random() more than once if they appear in a subquery, which may be a bug, eg: select op, op from (select abs(random())%10 as op from (select 1)); 3|1 ...
0
votes
2answers
30 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
0
votes
1answer
31 views

SQLite Data Types In Context of Disk Space

I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space. Any guide ...
0
votes
1answer
94 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
1
vote
1answer
1k 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 ...
1
vote
0answers
38 views

SQLite copy a sample of the database

I have a fairly complex sqlite database with several tables and indices. I'd like to copy a small subset of all the rows into a new database that I can use to test an app. Is there any builtin ...
0
votes
2answers
51 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 ...
0
votes
0answers
44 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 ...
2
votes
0answers
23 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 ...
4
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 ...
0
votes
1answer
273 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 ...
1
vote
2answers
57 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
221 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
39 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 ...
15
votes
7answers
16k 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
110 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
145 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
23 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
86 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. ...
5
votes
2answers
24k 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
227 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
42 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
25 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
59 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
206 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
34 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
310 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 ...
1
vote
1answer
823 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
135 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
275 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
2answers
177 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
254 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
73 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
71 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
63 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
339 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
103 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
722 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 ...
3
votes
0answers
298 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
288 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
85 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
32 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
175 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
31 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
205 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 ...
12
votes
2answers
11k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
0
votes
1answer
68 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 ...