A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
12
votes
1answer
665 views
Limits of SQLite
How far can one take the sqlite database from a single-user , embedded , prototype oriented db engine ?
11
votes
1answer
504 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 ?
9
votes
4answers
2k 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 ...
6
votes
3answers
351 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 ...
6
votes
2answers
1k 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 ...
6
votes
5answers
3k views
Is it possible to use SQLite as a client-server database?
Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
6
votes
1answer
187 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 ...
6
votes
1answer
181 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
...
5
votes
1answer
804 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
272 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
334 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
877 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
3answers
174 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 ...
4
votes
1answer
277 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 ...
3
votes
2answers
333 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
140 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
2answers
653 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
414 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
1k 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 ...
3
votes
1answer
674 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 ...
3
votes
2answers
213 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
737 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
...
3
votes
1answer
563 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 ...
2
votes
2answers
112 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
4answers
130 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
1answer
222 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 ...
2
votes
1answer
53 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 ...
2
votes
1answer
415 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
611 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
614 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
182 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 ...
2
votes
1answer
190 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
178 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 ...
2
votes
0answers
238 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
859 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 ...
1
vote
4answers
757 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
61 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
181 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
38 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
51 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
107 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
154 views
Working with multiple databases?
Using sqlite3 on Linux, how can one work with multiple databases?
I'd like to do something like the following in database 1?
sqlite3 database1.db
insert into database1.mytable values (select * from ...
1
vote
1answer
398 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
354 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
412 views
Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite
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
58 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 ...
1
vote
1answer
51 views
Query to find Maximum friends not giving correct resuts
Table Schema
Likes table
id,id1
1,2
1,3
2,1
I have count of total number of Likes for each student through this
select id,count(*)
from friends
group by id
Now I have to find ...
1
vote
0answers
59 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
1answer
68 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
0answers
59 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 ...
