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

learn more… | top users | synonyms

2
votes
2answers
177 views

Create a result for a line chart with dates

I have this table in SQLite which I use to record the risk level identified in vulnerability assesments according to the day the scan was performed. This table looks like this: Scan Date | Risk ...
1
vote
1answer
801 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
0
votes
1answer
2k views

How to select non unique values from 1 column if a value doesn't exist in column 2

I have a table containing (among other things) 2 integer values, neither of which are unique. See below for example data: --------------- | a | b | --------------- | 1 | 1 | --------------...
2
votes
2answers
76 views

Strategy to find the unique bivariate polynomials

I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is: Since the ...
0
votes
1answer
1k views

Database design for mobile app

At first I would like to say hello to all of you, since it is my first post here on DB Administrators. I am writing an app which store drinks and their data. At the beginning it was storing all in ...
1
vote
2answers
2k views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
0
votes
1answer
66 views

Query executes fast, but keeps the table locked for some time

Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table. It ...
0
votes
2answers
139 views

One table with a flag or two tables

I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list ...
2
votes
1answer
262 views

File database vs application server database for concurrent performance & bulk data

I asked a question about Ripple's database implementation, and received this response: The ripple server uses SQLite for structured data and a configurable "back end" for unstructured "bulk" ...
1
vote
1answer
549 views

SQLite and Write Ahead Logging: why so large files

I have been doing some operations on SQLite databases recently. The other day I ran a command to DELETE a bunch of rows from a 1GB database file and the WAL file got over 8GBs at which point it ...
0
votes
1answer
153 views

PHP SQLite Admin is running on PHP 5.4

I tried phpsqliteadmin But it doesn't seem to work anymore on PHP 5.4. There are several alternatives, but none of them seems to be "The One" that is maintained and widely used. Is there a standard ...
2
votes
1answer
142 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
38 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
242 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
7k 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
105 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
120 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
447 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
4k 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
60 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", "...
1
vote
1answer
3k 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
141 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
88 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
305 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
2k 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 ...
9
votes
2answers
12k 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
489 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
33 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
2k 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
1k 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
3k 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 B....
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
55 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
616 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
532 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
89 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 A,B,...
0
votes
0answers
2k 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 cache_size=...
4
votes
1answer
4k 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
412 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
915 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
101 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
213 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 ...
3
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
114 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
5k 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
504 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
3k 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 runs,...
2
votes
4answers
2k 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 ...