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

learn more… | top users | synonyms

1
vote
1answer
303 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 ...
4
votes
1answer
2k views

Why doesn't INSERT AFTER Trigger add all results?

I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger ...
2
votes
4answers
759 views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
3
votes
1answer
439 views

Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress?

I'm trying to monitor the progress of a sqlite3 command importing a large .sql file into a database using zenity --progress. I've tried the following which will import the file, however progress is ...
0
votes
1answer
13k views

How do I prevent SQLite database locks?

From SQLite FAQ I've known that: Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be ...
0
votes
2answers
463 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 ...
2
votes
2answers
2k views

How to use CREATE TABLE … AS with Primary Key in SQLite

From SQLite documentation for CREATE TABLE http://www.sqlite.org/lang_createtable.html: A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. So is there any ...
2
votes
2answers
126 views

Query to find Maximum friends not giving correct resuts [closed]

Table Schema for the Likes table: id id1 1 2 1 3 2 1 I count the total number of Likes for each student with this: select id,count(*) from friends group by id I have to find the students with ...
0
votes
1answer
572 views

How to output query to file using SQLite3

Is there an equivalent to Oracle's spool for sqlite3?
-1
votes
2answers
81 views

checking number of occupied occupied neighbours [closed]

Assuming a pixel grid with x and y of the size 1000 over 1000. In each iteration\run, using a random number generator, certain cells will be marked as occupied. Those cells will be written in a SQLite ...
2
votes
1answer
823 views

How to properly format sqlite shell output?

If I go to mysql shell and type SELECT * FROM users I get - +--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+ | USERID | ...
1
vote
1answer
157 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 ...
8
votes
3answers
935 views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
1
vote
1answer
419 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 ...
3
votes
1answer
184 views

Read-only on an SQLite database which is being modified by another program

I wrote a program that has an SQLite database, modifying it every few seconds. Now I want to write a small gadget program that would just read a bit of info from that database (read-only) and display ...
-1
votes
1answer
859 views

How to create view in SQLite using INSERT INTO? [closed]

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly ...
1
vote
1answer
288 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
0answers
134 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 ...
0
votes
1answer
612 views

Delete with subquery with no unique columns

I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns. Database is SQLite delete from LIKES where ID1 = 10 /// Wrong there more then one record with ID1=10 delete from LIKES ...
1
vote
0answers
192 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 ...
2
votes
1answer
520 views

SQLite or CouchDB for Locational Data Mining Project?

I am currently designing a Data Mining Project where I am going to harvest rather large volumes of Twitter data in order to analyse locational data (geocoded tweets) and do some machine learning with ...
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 ...
2
votes
2answers
509 views

Reordering data in a table

I have a table that stores sensor data (in SQLite) and looks like this; TABLE Timestream: idTimestream PK autoincrementing integer, time int not null, value float not null, idSensor integer not ...
3
votes
2answers
330 views

Query for fetching records from SQLite database on Blackberry

I want to fetch data from SQLite database for Blackberry. There is one table with four columns: ID, FROM, TO, TIME I want to fetch record on the basis of FROM, and TO values. Where condition 1: ...
5
votes
3answers
1k views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
1
vote
1answer
91 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, ...
2
votes
0answers
509 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 ...
2
votes
1answer
680 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 ...
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?
4
votes
1answer
550 views

SQLite3 simple database design question

I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database. The ...
2
votes
4answers
159 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 ...
1
vote
1answer
112 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. ...
7
votes
1answer
276 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
-1
votes
1answer
647 views

How to link data from a table to another table in sqlite database?

I need to link data from a table to another table. I'm not sure how to do this. I have never link a table to another table. Anyone can help me? Or maybe can give me examples or sample codes? Thanks =) ...
0
votes
1answer
250 views

Store a small log file as a blob in sqlite

I am utilizing a small sqlite database to keep track of some jobs that run on a linux box. Most of them have small (less than 50 lines) log files... I'd like to store those log files as a record in my ...
5
votes
1answer
383 views

Design for file based with many events

I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into ...
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 ...
0
votes
2answers
123 views

Very slow request with small difference

I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert. I have three requests. First and second are fast (0.2s), and the third is very slow ...
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 ...
3
votes
1answer
415 views

Single AND operator and multiple OR operators

I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3. Every SELECT statement should return 1 record. View my sample table here: http://i49.tinypic.com/2ezjtk8.png How ...
2
votes
1answer
2k 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
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 ...
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
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 ...
3
votes
1answer
1k 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 ...
3
votes
2answers
930 views

MySQL vs SQLite on Amazon EC2

I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database. On ...
0
votes
1answer
352 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 ...
3
votes
1answer
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 ...
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 ...
3
votes
1answer
698 views

SQL Server Embedded Edition vs SQLite

Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't". How is efficiency affected as the database grows ...