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

learn more… | top users | synonyms

2
votes
1answer
3k views

Is there any way to get the position of a value in a table returned by a SELECT statement?

I have the following statement: SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id which returns a table like this one for example: id 1 3 4 5 But what I need is to know the position of a ...
2
votes
4answers
175 views

Is there a “standard way” to log network messages (events) into a database?

I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event. Messages are heterogeneous, meaning that the first ...
2
votes
2answers
48 views

Create Sqlite3 database

I am simply trying to create a database. In my account's home directory, I type in sqlite3 test.db Then once I am in sqlite, I type .quit That should create the database file in the ...
2
votes
2answers
66 views

Strategy to find the unique bivariate polynomials

I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is: Since the ...
2
votes
1answer
893 views

Why is SQLite adding unused disk space to “free-list” after deleting a lot of data in a database?

The SQLite FAQ states the following: (12) I deleted a lot of data but the database file did not get any smaller. Is this a bug? No. When you delete information from an SQLite database, the ...
2
votes
1answer
1k views

Track daily view count of youtube videos

I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such ...
2
votes
1answer
729 views

Auto Increment Index Field For Create and Update

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created ...
2
votes
1answer
593 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 ...
2
votes
1answer
179 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 ...
2
votes
1answer
118 views

Rewrite sqlite trigger to postgresql

Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format: In sqlite: CREATE TRIGGER connections_INSERT_update_connection_root_trg ...
2
votes
1answer
80 views

What does 'Vacuum' do in Menial's Base 2 application?

I've had a look at the help documentation for Base 2, as well as Menial's product page, but haven't found an answer there. I've even clicked on the vacuum button and not noticed any change. What does ...
2
votes
1answer
224 views

File database vs application server database for concurrent performance & bulk data

I asked a question about Ripple's database implementation, and received this response: The ripple server uses SQLite for structured data and a configurable "back end" for unstructured "bulk" ...
2
votes
1answer
555 views

How can I create a MySQL database based on a SQLite schema?

I have MySQL on my server and different SQLite databases on a client. I'm trying to find a way to create the same databases on the server. Since a SQLite schema is actually a CREATE query, one way ...
2
votes
1answer
3k views

Default values in SQLite3

When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
2
votes
1answer
1k views

How to create a train schedule in SQLite3?

I am interested in what a SQLite3 database schema would look like for a train timetable. Generally this is presented in table format as the final result. Sample Schedule for M to F STATION A ...
2
votes
0answers
107 views

Is it possible to store and query JSON in SQLite?

I need to store JSON objects in a SQLite database, and then do complex queries on it. I did a table like this: +--------------------------------------+ |document | property | string | number| ...
2
votes
0answers
22 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE ...
2
votes
1answer
63 views

SQLite WITH + UNION issue

my UNION queries cannot work when tables created by WITH are used, is this a SQLite limitation? for example - This works: with T_one as ( select * from T ), T_two as ( select * from T ) ...
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 ...
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 ...
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, ...
2
votes
2answers
141 views

Create a result for a line chart with dates

I have this table in SQLite which I use to record the risk level identified in vulnerability assesments according to the day the scan was performed. This table looks like this: Scan Date | Risk ...
2
votes
0answers
413 views

In-memory database vs RAM-disk

In SQLite, are there any differences in performance/features/reliability/... between: 1) an in-memory database: rc = sqlite3_open("file::memory:", &db); 2) a database on a RAM-disk? The ...
2
votes
1answer
2k views

Enable autocomplete in an sqlite3 interactive shell

I am using sqlite3 on a machine where I can use tab completion (ie .read abc<tab> will autocomplete to .read abcdefghij.db. I would like to know how to enable this on my personal machine. Edit ...
2
votes
0answers
632 views

SQLite Performance Tuning

I have the following query, which fetches data from only one table. It is a query which is supposed to return data for an autocomplete function. Autocomplete data can be in text1 or text2. The ...
1
vote
2answers
2k views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
1
vote
4answers
2k views

RAM/memory based databases

I am looking for a RAM/memory based database, and these two seem to be interesting: SQLite Oracle Berkeley DB Do you have any other suggestions? I need more than one executable to be able to ...
1
vote
1answer
52 views

Postgresql equivalent to sqlite pragma

What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql. pragma synchronous = OFF; pragma journal_mode = OFF; pragma count_changes = OFF; ...
1
vote
2answers
286 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) ...
1
vote
1answer
107 views

sqlite database: advanced SQL or redesign

I've been programming for several years, but I'm pretty new to SQL(ite). I'm designing a database for a sort of a bookkeeping program. What I've got so far: orders (o INTEGER PRIMARY KEY, ...
1
vote
1answer
812 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 ...
1
vote
1answer
3k views

Why does SQlite return incorrect sum?

CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL ); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES ...
1
vote
1answer
20 views

Square root in SQLite studio

SQLite studio does not support square root function, so I have defined it in function editor as proc SqrtA {num} { set ans [expr {sqrt($num)}] return $ans } with function nameSqrtA and one input ...
1
vote
1answer
43 views

SQLite query rows nearest to hour

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class ...
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 ...
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 ...
1
vote
1answer
78 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?
1
vote
2answers
133 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} ...
1
vote
1answer
2k views

Joins in Update statement in sqlite

CREATE TABLE Table_1 ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , EmailId VARCHAR ) INSERT INTO Table_1 VALUES (1,'raj@gmail.com'); INSERT INTO Table_1 VALUES (3,'test@gmail.com'); ...
1
vote
1answer
656 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
1
vote
2answers
5k views

join for between multiple tables without common fields

table E : id, title, datetime table L : id, place, xpos, ypos table M : id, title, datetime, contact, agenda table ER : parentid, childid, parenttype, child type data in ER : e.id, l.id, e, l -> E ...
1
vote
1answer
97 views

Joining three tables

I have a question Find the titles of all movies not reviewed by Chris Jackson Where the relations are 3 movie(mID, title, year, director), reviewer(rID, name), rating(rID, mID, stars, ratingDate) ...
1
vote
1answer
32 views

How do I query for fields that have AT LEAST a given range?

Say I have a table of the form: +-----+-------+ | Key | Value | +-----+-------+ | A | 1 | | A | 3 | | A | 4 | | B | 2 | | B | 3 | | C | 3 | | C | 4 | | C | ...
1
vote
1answer
48 views

Which database should I look at for analyzing ~30gb, 700M records dataset?

I'm looking at analyzing a kaggle dataset which is pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables patients(2M) <-> diagnosis(700M) <-> doctors(2M). I ...
1
vote
1answer
143 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 ...
1
vote
1answer
204 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
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 ...
1
vote
1answer
77 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
1answer
771 views

How to get the rowid in a TRIGGER for another table in SQLite

Given the below schema CREATE TABLE Item ( _id INTEGER NOT NULL, name NVARCHAR NOT NULL, parent INTEGER NULL CONSTRAINT fk_Recursive ...
1
vote
1answer
418 views

SQLite and Write Ahead Logging: why so large files

I have been doing some operations on SQLite databases recently. The other day I ran a command to DELETE a bunch of rows from a 1GB database file and the WAL file got over 8GBs at which point it ...