A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
1
vote
1answer
116 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 ...
0
votes
1answer
18 views
6
votes
5answers
3k views
Is it possible to use SQLite as a client-server database?
Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
1
vote
1answer
68 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
2answers
106 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 ...
6
votes
2answers
991 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 ...
2
votes
1answer
177 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 ...
-1
votes
2answers
66 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
32 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 ...
1
vote
1answer
51 views
Query to find Maximum friends not giving correct resuts
Table Schema
Likes table
id,id1
1,2
1,3
2,1
I have count of total number of Likes for each student through this
select id,count(*)
from friends
group by id
Now I have to find ...
1
vote
1answer
52 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
1answer
36 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
37 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
...
6
votes
3answers
337 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
49 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 ...
9
votes
4answers
2k views
Tools to migrate from SQLite to PostgreSQL
I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
2
votes
1answer
51 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
117 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
107 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 ...
0
votes
0answers
66 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
vote
0answers
56 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
64 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 ...
0
votes
0answers
220 views
How to integrate sqlite extension in XAMPP?
There's an extension on the http://www.sqlite.org/contrib?orderby=date, extension-functions.c, which integrates the SQRT command in sqlite.
How can I integrate this and use it in my Windows-XAMPP ...
1
vote
0answers
57 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
181 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 ...
-1
votes
2answers
2k 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
111 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 ...
0
votes
0answers
41 views
Check how much space a single row takes in Sqlite
Is there a way to check how much space a single row takes in Sqlite?
I have tried using this way:
I have 4 fields in my table:
id(int)
name(text)
pat(text)
is_default(bool)
select *, ...
3
votes
2answers
212 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:
...
1
vote
0answers
199 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
3answers
174 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
60 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,
...
0
votes
1answer
108 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 ...
1
vote
1answer
151 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 ...
2
votes
0answers
234 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 ...
1
vote
1answer
176 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 ...
0
votes
1answer
438 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?
0
votes
0answers
305 views
Sync SQLite database with a remote copy
I would like to be able to keep the database for my app in sync across many different clients (mobile and desktop) by storing it on a service such as Dropbox, iCloud, Google Drive, etc.
Is there an ...
4
votes
1answer
276 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
130 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 ...
0
votes
1answer
59 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.
...
6
votes
1answer
180 views
Performance of query with a range condition and order by
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
...
-1
votes
1answer
194 views
How to link data from a table to another table in sqlite database?
I need to link data from a table to another table. I'm not sure how to do this. I have never link a table to another table. Anyone can help me? Or maybe can give me examples or sample codes? Thanks =)
...
0
votes
0answers
48 views
Comparing Sqlite databases
I would like to compare Sqlite databases with each other, both schema and data. I know that a rather crude method exists by comparing the database dumps with each other. But are there any other ...
0
votes
1answer
96 views
Store a small log file as a blob in sqlite
I am utilizing a small sqlite database to keep track of some jobs that run on a linux box. Most of them have small (less than 50 lines) log files... I'd like to store those log files as a record in my ...
5
votes
1answer
271 views
Design for file based with many events
I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into ...
2
votes
1answer
189 views
Default values in SQLite3
When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
0
votes
2answers
102 views
Very slow request with small difference
I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert.
I have three requests. First and second are fast (0.2s), and the third is very slow ...
1
vote
1answer
393 views
Combine column from multiple rows into one row in SQLITE
I have a sqlite-database with the schema
Articles
Id
Name
Tags
Id
Name
ArticlesTags
Id
ArticleId
TagId
I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want ...
3
votes
1answer
139 views
Single AND operator and multiple OR operators
I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3.
Every SELECT statement should return 1 record.
View my sample table here: http://i49.tinypic.com/2ezjtk8.png
How ...
