A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
0
votes
1answer
13 views
-1
votes
2answers
64 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 ...
0
votes
1answer
34 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
36 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
...
1
vote
1answer
46 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 ...
2
votes
1answer
50 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
107 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
51 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
65 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
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
0answers
54 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 ...
1
vote
1answer
66 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
0answers
215 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
56 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 ...
1
vote
1answer
115 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
2answers
105 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
1answer
179 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 ...
2
votes
2answers
109 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 *, ...
1
vote
0answers
191 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 ...
6
votes
2answers
963 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 ...
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
106 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
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 ...
1
vote
1answer
146 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
1answer
173 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 ...
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
169 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
432 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?
3
votes
2answers
210 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:
...
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.
...
-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 ...
4
votes
1answer
273 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 ...
-1
votes
1answer
189 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
95 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 ...
0
votes
2answers
101 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 ...
0
votes
0answers
304 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 ...
1
vote
1answer
386 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 ...
6
votes
1answer
177 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
...
2
votes
1answer
188 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 ...
3
votes
1answer
136 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 ...
0
votes
0answers
78 views
Database structure advice for large data text searching
I want to search scanned text (comprised of a few gigabytes of text).
The problem with this text (which is why I cannot make an index of words) is that it was OCRed, so there could be (and are) ...
2
votes
1answer
399 views
Is there any way to get the position of a value in a table returned by a SELECT statement?
I have the following statement:
SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id
which returns a table like this one for example:
id
1
3
4
5
But what I need is to know the position of a ...
1
vote
1answer
343 views
How to create a train schedule in SQLite3?
I am interested in what a SQLite3 database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Sample Schedule for M to F
STATION A ...
3
votes
2answers
1k 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 ...
1
vote
1answer
407 views
Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite
Our old product supports 4 different database types for storing data:
SQLite
MySql
Sql Server CE
Sql Server
There is also an export feature, which allows to export some data and here is the ...
