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

learn more… | top users | synonyms

14
votes
7answers
12k views

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

Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
14
votes
1answer
1k views

Limits of SQLite

How far can one take the sqlite database from a single-user , embedded , prototype oriented db engine ?
13
votes
1answer
1k views

What does collation mean in database parlance?

I am learning sqlite from a book which has mentioned collation and collating sequence multiple times. What does it mean exactly in the database world ?
10
votes
4answers
5k views

Tools to migrate from SQLite to PostgreSQL

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
8
votes
3answers
1k 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 ...
8
votes
2answers
8k 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 ...
7
votes
2answers
8k views

Storing prices in SQLite, what data-type to use?

I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store ...
7
votes
1answer
294 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 ...
6
votes
1answer
314 views

How many disk seeks does it take to write one row on SQLite?

As far as I understand, there can be one disk-seek per rotation of a harddisk, therefore I am interested to know how many disk seeks are needed for writing a row to an SQLite table, including locking ...
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 ...
5
votes
1answer
1k views

Estimating impact of indexes SQLite database size

I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to ...
5
votes
1answer
1k views

Is there a free software addition to SQLite available for data-replication?

I am aware of the possibility to create my own trigger(ed) solution, but I didn't want to reinvent the wheel :-)
5
votes
1answer
128 views

Many-to-many relationship with attributes

I'm working on a project where I need a book database. The problem is as follows: A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to ...
5
votes
1answer
396 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 ...
5
votes
1answer
553 views

Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database?

I have a database with about 50 tables with use together about 1 GB space. The SQLite database will be readonly. This copying will be done once each month. EDIT: I see one possible solution using ...
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 ...
4
votes
1answer
1k views

How to properly format sqlite shell output?

If I go to mysql shell and type SELECT * FROM users I get - +--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+ | USERID | ...
4
votes
2answers
59 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 ...
4
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 ...
4
votes
1answer
595 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 ...
4
votes
2answers
13k 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 ...
3
votes
2answers
640 views

Using desktop computers to manage an SQLite database on a fileserver?

Sometimes, it can take months to get a proper database server up and running in large companies. They have all kinds of policies, which means that this process takes months. An alternative in the ...
3
votes
1answer
472 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 ...
3
votes
1answer
225 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 ...
3
votes
2answers
987 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 ...
3
votes
1answer
332 views

Using CoreData on a client-side application

This may be a bit more of an SO question, but... If I'm writing a client-side application for OS X that's going to sync with an online MySQL database, is there any difference on if I have CD use ...
3
votes
1answer
722 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 ...
3
votes
2answers
346 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: ...
3
votes
1answer
62 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type ...
3
votes
1answer
53 views

How to show the related (suggested) keywords?

We do a program for cataloging images by assigning keywords (tags) to them. An image has the following keywords "John", "Jane", "Portrait". Another image has the following keywords "John", "Jane", ...
3
votes
1answer
470 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 ...
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
1answer
771 views

Casesensitive vs. Caseinsensitive Collatesequences?

I just started working with the SQLite database places.sqlite. Fact is that this database uses case sensitive collate sequences making writing queries as funny as in Oracle. I want to know, are ...
3
votes
0answers
29 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 ...
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 ...
2
votes
4answers
921 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 ...
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
90 views

EAV-like table with both primary column pointing to the same foreign column

Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features ...
2
votes
2answers
132 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 ...
2
votes
2answers
582 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 ...
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
4answers
161 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
56 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
2answers
1k views

How do I specify a timeout in sqlite3 from the command-line?

I have a couple basic scripts that kick out a little information while my sqlite3 DB is being populated, but about half the time the command instantly fails: $ sqlite3 outgoing.db "select * from ...
2
votes
1answer
752 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
721 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
58 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
62 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
572 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 ...