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

learn more… | top users | synonyms

0
votes
1answer
52 views

SQL - Referential integrity

seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question: I have three tables or more, refer to those as ...
0
votes
0answers
470 views

Occasional disk I/O errors in SQLite

I have a very simple website running PHP and SQLite 3.7.9 (with PDO). After establishing the SQLite connection I immediately execute the following queries: PRAGMA busy_timeout=0; PRAGMA ...
2
votes
1answer
766 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 ...
0
votes
1answer
239 views

Structuring SQLite database with 2 relationships?

I'm current working on a SQLite-database in a android project which will store 3 types of objects; feed, users and posts. There will be several feeds containing several varying users(a user can be ...
0
votes
0answers
247 views

how to restore sqlite dump file to sqlite database using java?

I generated dump file from java using .dump command so my back-up is ready now I want to restore it to once again so can anyone help me how to restore? In stackoverflow I got this but nothing restore ...
1
vote
1answer
3k 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 ...
0
votes
1answer
82 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
1answer
95 views

SQLite: updating string values

I couldn't find any answer for my question so I'm writing. Are there any restrictions for updating strings in SQLite database ? I'm wondering if any new value is written into database file there ...
11
votes
6answers
7k 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?
2
votes
2answers
570 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 ...
1
vote
0answers
134 views

What are some light-weight clients of SQL Server 2012 that can utilize SQL spatial functions? [closed]

We're building our new enterprise database and it will include SQL server spatial (native) data (moved from ArcSDE). This is great news for our SQL analysts who will now be able to consume our spatial ...
0
votes
2answers
84 views

Advice on scripting a “whitewashed” and downsized development db from main db

Currently we (an active opensource project) have a large PostgreSQL production database which contains all the site records. Since we can't just allow every developer or contributor access to the ...
-1
votes
1answer
663 views

'ambiguous column name' error refers to database, table, and column [closed]

sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as ...
1
vote
1answer
164 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
342 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
370 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
8k 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
0answers
2k views

how to import .xls file into SQLite database?

I have an issue with importing .xls file into SQLite database. I am working on windows xp and using a SQLite manager for maintain the database. I can import the .csv file to the database by using ...
0
votes
2answers
316 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 ...
0
votes
2answers
929 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 ...
1
vote
2answers
105 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
367 views

How to output query to file using SQLite3

Is there an equivalent to Oracle's spool for sqlite3?
8
votes
2answers
4k 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 ...
-1
votes
2answers
80 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 ...
0
votes
1answer
359 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
122 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
801 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
281 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 ...
10
votes
4answers
4k 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 ...
2
votes
1answer
136 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
485 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
216 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
118 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
307 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
156 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
402 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 ...
-1
votes
2answers
3k 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
346 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
300 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
610 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
78 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
419 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
538 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 ...
0
votes
1answer
1k 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
461 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
154 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 ...
0
votes
1answer
94 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
240 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
498 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 =) ...