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

learn more… | top users | synonyms

0
votes
0answers
24 views

Suitability of sqlite vs sql server compact or local db [on hold]

I am currently using sqlite with my C# application. The issue why I need this is for simple deployment using the clickonce. I could not use mysql here is cause of steps needed for its depolyment. I ...
0
votes
1answer
15 views

Can sqlite dump database schema in a fixed order?

I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even ...
0
votes
1answer
26 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 ...
1
vote
0answers
13 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
22 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
36 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
40 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
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 ...
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 ...
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
44 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 ...
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
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 ...
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
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 ...
3
votes
1answer
64 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 ...
0
votes
1answer
24 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
132 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
24 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? ...
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
60 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
0answers
50 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
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
1answer
54 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 ...
1
vote
0answers
31 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 ...
2
votes
2answers
52 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 ...
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 ...
2
votes
1answer
64 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
46 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 ...
0
votes
1answer
81 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
1answer
53 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; ...
1
vote
2answers
47 views

Search across all columns in a table

As the title says, I want to create a query that searches a keyword across all columns in that table. So I want to simplify this query: SELECT * FROM tblCustomers WHERE (col1 LIKE '%keyword%' OR ...
0
votes
2answers
30 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
0
votes
1answer
31 views

SQLite Data Types In Context of Disk Space

I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space. Any guide ...
1
vote
2answers
202 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 ...
1
vote
0answers
39 views

SQLite copy a sample of the database

I have a fairly complex sqlite database with several tables and indices. I'd like to copy a small subset of all the rows into a new database that I can use to test an app. Is there any builtin ...
0
votes
1answer
94 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
0
votes
2answers
51 views

How to JOIN each row ON NOT IN ( SELECT … )?

Say I have a table of users, songs, and songs played. users id: int songs id: int songs_played user_id: int song_id: int If I want to, for each user, find a song that has not yet been played. How ...
0
votes
0answers
44 views

SQLite table design for PK and/or UNIQUE

I'm trying to design a table in SQLite3, where I can perform these 2 actions. INSERT new row where a unique primary key is created. And INSERT new row with an given primary key Think of a ...
2
votes
0answers
23 views

SQLITE Foreign key has to be re-added? [closed]

Currently I have a mysql database on my computer which I convert to sqlite database. On the sqlite database, I have transaction_detail table with a foreign key to transaction_master table. The ...
0
votes
1answer
289 views

Text string stored in SQLite Integer column?

I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line: sqlite> .schema mytable CREATE ...
1
vote
2answers
57 views

How to optimize a large join to create a WHERE IN

The following sqlite database query takes about 40 seconds on my Core i7. SELECT t.a FROM titles t WHERE t.a IN ( SELECT e.id FROM erpubtbl e JOIN abstract_mesh am ON am.pmid = e.pmid ...
3
votes
1answer
223 views

Why does an sqlite3 .dump and restore result in a smaller database file size?

I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was ...
1
vote
1answer
39 views

Order By with duration strings having time units

I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as : 7Months 26Days 3Years I'd like to order a query results by timeToMaturity ...