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

learn more… | top users | synonyms

0
votes
1answer
48 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
1
vote
1answer
27 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
856 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
0answers
730 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 ...
0
votes
0answers
2k views

SQLite Loop from SELECT QUERY

I have this long query which works one for processing one sale. What I'm trying to do is loop it until no more B.boughtPending = 0 and S.soldPending = 0 and then Select all the ones that ...
0
votes
0answers
1k views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
0
votes
1answer
42 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
0
votes
1answer
279 views

Keep rows only if Date exists in both table

This is my first time here, so please bear with me. Assuming i have 2 table, Table 1 Date | Closing 1/1/2013 | 1 2/1/2013 | 2 Table 2 Date | Closing 1/1/2013 | 3 3/1/2013 | 4 What sql statements ...
0
votes
2answers
211 views

How to guess or recover a timestamp conversion in sqllite?

I am in the middle of tiny forensic effort =) I am trying to transfer transactions from Money IQ (IOS) to My Wallet+ (IOS) as both apps use sqlite. Money IQ uses a standard unixepoch timestamp. ...
0
votes
1answer
66 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
1k 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
2k 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
314 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
501 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 ...
0
votes
1answer
89 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
144 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 ...
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 ...
0
votes
2answers
101 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
2k 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
335 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
846 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
464 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
15k 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 ...
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
131 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
625 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 ...
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 | ...
1
vote
1answer
169 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
973 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
438 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
205 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
999 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
338 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
139 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
696 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
204 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
554 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
558 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
337 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
96 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
530 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
716 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
575 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
160 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
115 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. ...