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

learn more… | top users | synonyms

0
votes
0answers
20 views

How to get average of views before clicking on advertisement?

I've got two tables containing information about viewing and clicking on advertisement by each user: Clicks: userID Action Time 123 c 2016-01-08 01:57:00 123 c 2016-01-11 ...
0
votes
0answers
8 views

how to directly import a csv gzip'ed file into SQlite 3?

I'd like to import a 15GB file comma delimited gzip compressed file into Sqlite 3 without having to use temporary files. I'd like to run a command like: zcat input/surgical_code.csv.gz | tail -n ...
1
vote
1answer
21 views

Square root in SQLite studio

SQLite studio does not support square root function, so I have defined it in function editor as proc SqrtA {num} { set ans [expr {sqrt($num)}] return $ans } with function nameSqrtA and one input ...
0
votes
0answers
18 views

Seeking Alternative To TEMPORARY Table For Limited Permissions [on hold]

The following query is valid but I do not have permission to create tables. I am seeking an alternative with equivalent results. CREATE TEMPORARY TABLE temp_id(temp_name VARCHAR(50),id INT); INSERT ...
0
votes
0answers
16 views

SQLite3 query question [closed]

I'm using the Ghost blogging platform (https://ghost.org/ its very nice) and I'm trying to make an external site, which accesses its database. The relevant tables, and their relevant columns for my ...
0
votes
0answers
35 views

Optimization beyond creating indexes

From the Explain query Output i already created db.execute("CREATE INDEX x1 ON TRAVELDAYS(Day, Value);") db.execute("CREATE INDEX x2 ON TRAINS(TrainDaysUID);") db.execute("CREATE INDEX x3 ON ...
0
votes
1answer
37 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
0
votes
1answer
610 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 ...
0
votes
0answers
26 views

Sqlite memory management [migrated]

I am using sqlite browser to run some queries. In case of queries which return huge data, sqlite consumes large amount of RAM and does not free it until the browser is closed / killed. Is there a way ...
-1
votes
0answers
13 views

Sqlite retaining huge memory even after transactions

I am using sqlite browser to run some queries. In case of queries which return huge data, sqlite consumes large amount of RAM and does not free it until the browser is closed / killed. Is there a way ...
1
vote
2answers
197 views

Using SQLite ODBC without installing a driver

I am currently trying to access a SQLite database via an ODBC connection but the system that I am working on is extremely locked down and it is therefore not possible to install the drivers. Is there ...
0
votes
0answers
17 views

How to get row position in sqlite after sorting?

I have table, UUID | INT | LONG AAA | 1 | 123 BBB | 3 | 241 CCC | 2 | 145 And i sort it by INT value. SELECT * FROM table ORDER BY int_val DESC then i want to get a row from table and ...
0
votes
2answers
156 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 ...
3
votes
1answer
62 views

SQLite Like clause match whole word only

I have a SQLite database with a table named: minecraft +----+----------------------+ | id | name | +----+----------------------+ | 1 | Pocket Mine MP | | 2 | Open Computers ...
1
vote
1answer
48 views

Which database should I look at for analyzing ~30gb, 700M records dataset?

I'm looking at analyzing a kaggle dataset which is pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables patients(2M) <-> diagnosis(700M) <-> doctors(2M). I ...
1
vote
1answer
43 views

SQLite query rows nearest to hour

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class ...
1
vote
1answer
9k 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 ...
2
votes
2answers
2k views

Create database with multiple columns using SQLite in Android Studio

I want to create a database with 3 columns (name of point of interest, longitude and latitude). Since I am completely new to databases I am using an example I found online and try to change it to my ...
0
votes
1answer
30 views

Inserting longer numbers in SQLite

Which data type is suitable to insert larger numbers in SQLite. Can anybody please guide me? I want to insert numbers greater than 10 digits.
3
votes
1answer
90 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type ...
3
votes
1answer
50 views

Finding records not present across 3 tables - Sqlite3

From the diagram provided, what I'm trying to achieve is to get all the Users of a particular workspace that are NOT associated with a task within user_task. user_workspace table contains two FKs: ...
0
votes
1answer
68 views

Having four different tables or having one table with one more field?

I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and ...
3
votes
2answers
56 views

How to use LIKE with subquery?

In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B. I'd like to SELECT all rows FROM table B WHERE B.name LIKE ...
2
votes
2answers
49 views

Create Sqlite3 database

I am simply trying to create a database. In my account's home directory, I type in sqlite3 test.db Then once I am in sqlite, I type .quit That should create the database file in the ...
2
votes
1answer
596 views

SQLite disk I/O error (3850)

I can't seem to figure out what is causing the I/O Error: $sqlite3 db.sqlite < data.sql (3850) disk I/O error Error: near line 2: disk I/O error (3850) disk I/O error Error: near line 3: disk ...
1
vote
0answers
26 views

Merge databases with sql only: firefox places and visits

I have two tables in each of two dbs: places: id, url (unique), visits: id, place_id, timestamp, type. I need to merge those databases: There are one main database and one "to-merge". The unique ...
0
votes
2answers
1k views

SQLite CSV import won't create a table automatically

According to https://www.sqlite.org/cli.html#csv, sqlite> .mode csv sqlite> .import C:/work/somedata.csv tab1 There are two cases to consider: (1) Table "tab1" does not previously ...
0
votes
0answers
15 views

SQLite: choosing a unique separator

I'm working on a project that will result in an SQLite database of about 6 GB of text content (encoded UTF-8). The text will be diverse: it will have a great deal of plain text writing, but also a ...
1
vote
1answer
44 views

Suitability of mysql and sqllite?

I am about to build a C# wpf application. I need to easily port the application e.g. one click installation. I am not sure should I use sqllite or mysql db? The range of data wont run into billion at ...
4
votes
2answers
3k 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 months and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
1answer
21 views

Combine 2 SQLite result sets to calculate percentage

I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage. Table columns: email Location Clicked? Test# Test# 123 has a list of users (identified ...
2
votes
0answers
116 views

Is it possible to store and query JSON in SQLite?

I need to store JSON objects in a SQLite database, and then do complex queries on it. I did a table like this: +--------------------------------------+ |document | property | string | number| ...
0
votes
0answers
16 views

Switching SQLite environments

I submitted this on SuperUser without response; someone suggested I try it here. I like Firefox's SQLite Manager plugin because it's easy to show people how to use it. This is helpful if I want to ...
0
votes
0answers
52 views

How to import data from Sqlite into SQL Server 2012

I need to import data from sqlite into SQL Server so we can include it in our data warehouse. Based on previous system volume. Id expect there to be 50-100,000 rows after a couple of years. For ...
0
votes
0answers
22 views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
6
votes
2answers
2k views

Is there a free software addition to SQLite available for data-replication?

I am aware of the possibility to create my own trigger(ed) solution, but I didn't want to reinvent the wheel :-)
0
votes
0answers
29 views

No foreign key constraint with PRAGMA foreign keys

This is my code. It's SQLITE3 in Python 3. conn = sqlite3.connect(db) c = conn.cursor() c.execute('BEGIN') c.execute("PRAGMA foreign_keys = ON") c.execute('CREATE TABLE ...
0
votes
0answers
58 views

Database design for storing historical stock exchange Level2 (depth) data

I am developing C# software using embbeded SQLite Database which shows historical level2 (market depth - asks/bids) stock exchange data graph. Example Simply speaking, there are 25 asks (price, ...
2
votes
0answers
22 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE ...
0
votes
0answers
19 views

Structure of an SQLite database for storing analytics with counters and hits

Before making this post, I referred to this and this which contain extremely complex solutions and hence, this question: I am building a spam filtering app that takes posts from a feed and deletes ...
0
votes
1answer
85 views

Derived classes with same base class and class specific ids

I have three classes in my project. One base class and two derived classes. public class Animal { public long Id { get; set; } public DateTime Created { get; set; } public string Name { ...
0
votes
0answers
48 views

SQLite no such column: table.columnName

I have a query SELECT Bookies.Name, Exchanges.Name, Bets.Completion_Date, Bets.BMStake, Bets.Liability, Bets.Profit, Bets.Type, ...
0
votes
1answer
51 views

Efficient way to get last 10 rows of each group in SQLite, can CTE help?

Consider there is a table of job runs history with the following schema: job_runs ( run_id integer not null, -- identifier of the run job_id integer not null, -- identifier of the job ...
2
votes
1answer
63 views

SQLite WITH + UNION issue

my UNION queries cannot work when tables created by WITH are used, is this a SQLite limitation? for example - This works: with T_one as ( select * from T ), T_two as ( select * from T ) ...
1
vote
0answers
27 views

SQLite reference table question

I'm using SQLite and Python for making a sensor network database. I have a table holding the nodes: NODE, another 2 tables for sensor specification: SENSOR_TYPE ("Temperature, Humidity...) and ...
1
vote
1answer
44 views

Getting data according to last hours from sqlite

I want to get records out of sqlite database according to hours. following are my questions 1) I have to extract all data from sqlite for past one hour. I have tried following query but it provide me ...
11
votes
2answers
3k views

How to properly format sqlite shell output?

If I go to mysql shell and type SELECT * FROM users I get - +--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+ | USERID | ...
0
votes
1answer
78 views

SQLite3: Remove duplicates

Given the following table CREATE TABLE `sms` ( `_id` INTEGER, `thread_id` INTEGER, `address` TEXT, `person` INTEGER, `date` INTEGER, `protocol` INTEGER, `read` ...
1
vote
0answers
45 views

How to import Sqlite database into another Sqlite database?

Hello everyone I am developing Desktop Application and using the WebSql Database this database is created into this path: C:\Users\Admin\AppData\Local\Google\Chrome\User ...
1
vote
1answer
52 views

Postgresql equivalent to sqlite pragma

What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql. pragma synchronous = OFF; pragma journal_mode = OFF; pragma count_changes = OFF; ...