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

learn more… | top users | synonyms

1
vote
1answer
105 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
0
votes
1answer
514 views

configure: error: SQLite3 library not found

I'm getting the below configure error while doing the install the Zabbix reporting tool on Linux. i have tried both Versions(zabbix-2.0.9,zabbix-1.8.17), but getting same issue. Log: checking for ...
2
votes
1answer
204 views

Configuration options for relational algebra evaluator “ra.jar”

I am participating in the Stanford Database Class, and there are some problem sets to be solved in "mathy" relational algebra syntax. The course instructors demand from students to use this ...
0
votes
1answer
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
1answer
101 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
0
votes
2answers
61 views

Group by + Order by Behavior Explanation?

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...
3
votes
2answers
1k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
0answers
12 views

Unable to open MONyogs

I was trying to run through the docs for manually working with the MONyog internal sqlite dbs for purposes of configuration automation. At the point trying to open the data file I get an error $ ...
0
votes
1answer
248 views

Constructing an SQL query with time intervals

I've a simple but large table in an SQL database which has four fields (id(int PK), time (unix timestamp, not null), value (double, not null) and an ID_fk (integer foreign key). Data is recorded ...
-1
votes
0answers
16 views

can not make trigger same as query in sqlite3?

My Table CREATE TABLE sensor(sdata integer, timestamp DATE DEFAULT (datetime('now', 'utc')), colnew integer); sqlite query :- SELECT q.sdata, q.timestamp, strftime('%s',q.timestamp) - ...
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 ...
0
votes
1answer
26 views

SQLITE table joining with group

MESSAGE TABLE USERS msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name 1 1 2 hi 22/1/14 ...
0
votes
2answers
19 views

sqlite group_concat error while executing query

I have table: CREATE TABLE www ( Col_1 VARCHAR, Col_2 VARCHAR ); select count(*) from www; --1496 And I have error: "Error while executing query: aggregate function are not allowed in ...
5
votes
1answer
92 views

Many-to-many relationship with attributes

I'm working on a project where I need a book database. The problem is as follows: A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to ...
1
vote
1answer
37 views

Joins in Update statement in sqlite

CREATE TABLE Table_1 ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , EmailId VARCHAR ) INSERT INTO Table_1 VALUES (1,'raj@gmail.com'); INSERT INTO Table_1 VALUES (3,'test@gmail.com'); ...
0
votes
1answer
37 views

What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL? [closed]

What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL? I'm used to MySQLWorkbench for working with MySQL databases. Now I have some sqlite databases to work with and would ...
1
vote
1answer
50 views

How to get the rowid in a TRIGGER for another table in SQLite

Given the below schema CREATE TABLE Item ( _id INTEGER NOT NULL, name NVARCHAR NOT NULL, parent INTEGER NULL CONSTRAINT fk_Recursive ...
2
votes
2answers
64 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
51 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
60 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
42 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
230 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
135 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
45 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
53 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
64 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
62 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
0answers
13 views

Datastructure to save relations between more than two entities and lists

I am trying to building an Information Extraction system(IE). I wrote a program that will detect entities and extract relations between them. Now I am saving the relations in the following SQLITE ...
0
votes
1answer
35 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
47 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
23 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
51 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
135 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
45 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
59 views

Problem in Nested Query

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
106 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 ...
0
votes
1answer
524 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
117 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
374 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
51 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
19 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
43 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
237 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
681 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 ...
3
votes
2answers
3k 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
5k 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
689 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
158 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
42 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 ...