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

learn more… | top users | synonyms

2
votes
1answer
69 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
2k 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
2answers
94 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
236 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
297 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 ...
2
votes
1answer
1k 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
517 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
1answer
2k views

Can I use SQLite to share data among different applications?

Can I use SQLite as a database to which 2 or more applications connect? I.e. can I use it to share data among different applications?
1
vote
2answers
861 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
1k 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
2answers
71 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
95 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
47 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
96 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
628 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
307 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
1answer
63 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
26 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
45 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
47 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
310 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
231 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 ...
1
vote
2answers
2k 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
819 views

How do I query for leaf nodes in a hierarchy using a closure table?

I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file. The nesting depth of leaf nodes will vary because some ...
1
vote
1answer
323 views

Database schema for a variable-level hierarchy with variable types?

A client is asking for a database structure for a medical translation app that involves narrowing down a health issue and then asking specific questions on it. The tricky part of representing this in ...
1
vote
2answers
5k views

disk I/O error in SQLite

What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file ...
1
vote
1answer
158 views

Database design: nested tagging

I have a situation that after an initial thought ended in the following tables: section: id, name section_tag: id, name section_tag_map: section_id, tag_id item: id, name ...
1
vote
1answer
432 views

Referencing multiple tables from a single table and store additional data

I have a database where I want to store lap time information from a racing game. I have tables for car, track, game, tuning and upgrade data and want to put all these together to create a single ...
1
vote
1answer
264 views

Delete word, its meanings, its meaning's example sentences from DB

I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer ...
1
vote
1answer
314 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 ...
1
vote
1answer
113 views

Check ruby gem and SQLite3 for FTS3 or FTS4 support

I have installed SQLite3 on Mountain Lion via MacPorts and Ruby along with sqlite3 gem using RVM. I'd like to know if there's any way for me to understand if my SQLite3 installation supports FTS4. ...
1
vote
1answer
3k views

Combine column from multiple rows into one row in SQLITE

I have a sqlite-database with the schema Articles Id Name Tags Id Name ArticlesTags Id ArticleId TagId I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want ...
1
vote
1answer
1k views

Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite [closed]

Our old product supports 4 different database types for storing data: SQLite MySql Sql Server CE Sql Server There is also an export feature, which allows to export some data and here is the ...
1
vote
1answer
14 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 ...
1
vote
1answer
20 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 ...
1
vote
0answers
106 views

Migrate from SQLite to MySQL (then back)

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on ...
1
vote
1answer
34 views

Finding records not present across 3 tables - Sqlite3

From the diagram provided, what I'm trying to achieve is to get all the Users of a particular workspace that are NOT associated with a task within user_task. user_workspace table contains two FKs: ...
1
vote
1answer
2k views

First time using an Index - SQLite / Android

I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. ...
1
vote
0answers
700 views

SQLite Design: Better to have many tables with few columns or few tables with many columns?

I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs. I have an ...
1
vote
1answer
147 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 ...
1
vote
0answers
136 views

Regular Expression Integrity Constraints in SQLite3

In a SQLite3 CREATE TABLE statement, can I use a regular expression to enforce a particular constraint? Specifically, I am trying to require that an inputted URL is valid. I know there are other ways ...
1
vote
0answers
194 views

Calculate lower and upper quartile of SQLiteDB via query

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 ...
0
votes
2answers
4k views

What program should I use to open .db3 files?

I have a series of .db3 files in my computer. They are like filename1.db3, filename2.db3, filename3.db3, filename4.db3 and so on. I can open filename1.db3 with sqlite editor. But when I open ...
0
votes
1answer
354 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
0
votes
2answers
34 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
0
votes
1answer
1k 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 ...
0
votes
1answer
44 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
2answers
96 views

Group by + Order by Behavior Explanation? [duplicate]

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...
0
votes
1answer
130 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...