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

learn more… | top users | synonyms

2
votes
1answer
92 views

Rewrite sqlite trigger to postgresql

Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format: In sqlite: CREATE TRIGGER connections_INSERT_update_connection_root_trg ...
0
votes
0answers
29 views

Approaching multiple values in same column

I'm developing an Android-application that scrapes the schedule for my university and presents it nicely since the current system is a nightmare. I'm processing a lot of data and it gets down to ...
0
votes
1answer
166 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...
1
vote
2answers
3k views

join for between multiple tables without common fields

table E : id, title, datetime table L : id, place, xpos, ypos table M : id, title, datetime, contact, agenda table ER : parentid, childid, parenttype, child type data in ER : e.id, l.id, e, l -> E ...
1
vote
1answer
82 views

Joining three tables

I have a question Find the titles of all movies not reviewed by Chris Jackson Where the relations are 3 movie(mID, title, year, director), reviewer(rID, name), rating(rID, mID, stars, ratingDate) ...
0
votes
2answers
93 views

Problem in Nested Query [closed]

I have a query like this SELECT m.title FROM Movie AS M WHERE (SELECT MAX(r.stars) FROM rating AS r WHERE r.mID = M.mID); I want to show the MAX(r.stars) in my result set, what am I doing wrong?
0
votes
1answer
273 views

No such column error jdbc

Suppose I have a table: SERVICE={SID,NAME,PORT} and with the help of a Java program, I want to select the SID of the known port with the help of a SQL query: Select SID from SERVICE ...
1
vote
1answer
3k 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
55 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
604 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 | ...
1
vote
1answer
2k 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
100 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
1answer
66 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
278 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
1k 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 ...
7
votes
2answers
9k 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
1k 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 ...
2
votes
0answers
300 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
54 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
31 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
1k 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
873 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
2k 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
48 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
335 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
288 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
68 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 ...
3
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
340 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
622 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
91 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
163 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
2k 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
106 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
3k 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
383 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
1k 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
499 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
18k 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
3k 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
136 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
727 views

How to output query to file using SQLite3

Is there an equivalent to Oracle's spool for sqlite3?
-1
votes
2answers
84 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 ...
5
votes
1answer
2k 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
197 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
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 ...
1
vote
1answer
470 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 ...