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

learn more… | top users | synonyms

0
votes
1answer
1k views

First time using an Index - SQLite / Android

I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. ...
3
votes
1answer
47 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", ...
0
votes
1answer
245 views

nesting MAX for SELECT

I have a table, containing TV series episodes: series: | series_id | season | episode | title | 1 | 1 | 18 | xxx | 1 | 2 | 3 | xxy | 3 | ...
0
votes
1answer
680 views

Why does SQlite return incorrect sum?

CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL ); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES ...
2
votes
2answers
59 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 ...
0
votes
0answers
22 views

Performance One Row With Total Vs Multiple Rows with Seperate Values?

Not 100% sure how to explain this situation but here it goes: I am building an Inventory Database that all items received are entered into and a label is created for each Container, that is going to ...
2
votes
1answer
50 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 ...
1
vote
1answer
250 views

Delete word, its meanings, its meaning's example sentences from DB

I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer ...
2
votes
1answer
829 views

Enable autocomplete in an sqlite3 interactive shell

I am using sqlite3 on a machine where I can use tab completion (ie .read abc<tab> will autocomplete to .read abcdefghij.db. I would like to know how to enable this on my personal machine. Edit ...
4
votes
2answers
5k 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 ...
6
votes
2answers
6k 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 ...
0
votes
0answers
863 views

Storing currency with variable floating point in SQLite

We are working with currency and handle currencies from various countries. As a result, we may work with currencies that require no decimal values, two decimal places, three, sometimes four. We also ...
1
vote
0answers
183 views

In-memory database vs RAM-disk

In SQLite, are there any differences in performance/features/reliability/... between: 1) an in-memory database: rc = sqlite3_open("file::memory:", &db); 2) a database on a RAM-disk? The ...
0
votes
1answer
43 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
26 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
624 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
504 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
74 views

Performance difference search on INT or TEXT in SQLite 3

I'm a little bit confused about SQLite 3 type system. SQLite 2 was typeless with only exception of INTEGER PRIMARY KEY. I don't get if SQLite 3 are same or not.. I have in my table PRIMARY KEY of INT ...
0
votes
0answers
963 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
929 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
40 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
168 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
125 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
58 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
711 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
1k 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
269 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
357 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
86 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
127 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
860 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
153 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
92 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
1k 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
240 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
558 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
411 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
11k 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
408 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
1k 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
121 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
486 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 ...
0
votes
1answer
555 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
142 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
881 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
357 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
158 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
674 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 ...