Questions tagged [sqlite]

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

137 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
358 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-...
4
votes
0answers
73 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
3
votes
1answer
866 views

Column length error querying SQLite via SQL Server Linked Server

I am attempting to query SQLite to copy data into corresponding tables in SQL Server. This is the first stage of an ETL process I'm putting together. Windows 10 Pro, SQL Server 2017 Developer Edition,...
3
votes
0answers
42 views

History of updated rows

Imagine we have a CRUD application in Python and are using SQLite. Let's say we have in SQLite a table called CUSTOMERS: CREATE TABLE CUSTOMERS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT ...
3
votes
1answer
244 views

Commit() takes 50 seconds after inserting about 100000 rows

I have a sqlite database that is, (I think), fairly small. I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb. I then open a transaction and insert about ...
3
votes
0answers
538 views

Maximum number of columns per table in SQLite Manager

I'm trying to increase the number of available columns of my table to about 25 or even more. However, the SQLite Manager add-on for Mozilla products that I'm using supports the maximum of 20 columns ...
3
votes
1answer
473 views

investigate contents of a sqlite3 file

I have created a new, clean firefox profile from scratch, and added couple of bookmarks. These, I have learned, are stored in places.sqlite file inside the profile directory. This file seems to be too ...
3
votes
0answers
4k views

SQLite Design: Better to have many tables with few columns or few tables with many columns?

I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs. I have an ...
3
votes
0answers
5k views

Occasional disk I/O errors in SQLite

I have a very simple website running PHP and SQLite 3.7.9 (with PDO). After establishing the SQLite connection I immediately execute the following queries: PRAGMA busy_timeout=0; PRAGMA cache_size=...
2
votes
0answers
19 views

App Crash due to Sqlite database Locked Exception

I am having an Android app with read only local sqlite database. Also I have use Sqlite Asset Manager for the local database. import android.content.Context; import com.readystatesoftware.sqliteasset....
2
votes
1answer
672 views

Setting up cloud database for syncing to local SQLite database

Background I have a SQL Server database hosted on AWS RDS and there are web applications and WEB APIs that talk to the database. The database is a multi-tenant database and we are currently using SQL ...
2
votes
1answer
125 views

SQLite Internals - Records

Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (...
2
votes
1answer
2k views

Storing JSON in Postgres as BYTEA or other types

I need to have a generic key-value data-store in Postgres and Sqlite3. I originally planned to store this as a JSONB type but the SQL statements I execute needs to be compatible with both Postgres and ...
2
votes
0answers
318 views

How get process that is locking Database on SQLite?

I'm developing a iOS app using SQLite. I've got a class to manage SQL's operations, basically read, write and update. In many cases I get a SQLITE_BUSY error, so I want know which process is using the ...
2
votes
0answers
423 views

Inserting two NULLs values into an UNIQUE column in SQLite

I use: sqlite-jdbc-3.21.0.jar Error received: SQLite_CONSTRAINT_UNIQUE] A UNIQUE constraint failed: companies.ncf I ask a few things to the user, there is two fields that are optional to complete ...
2
votes
0answers
187 views

SQLite and LibreBase

I have an SQLite database and a LibreBase interface, my interface in LibreBase will not work because it says "Database does not support relations" and "Database does not support FULL or LEFT Joins". I ...
2
votes
0answers
1k views

Return Maximum Rolling/Moving Count For Each Person For Any 90-day Period

Basics I have a table of patients with visit dates. There can be an arbitrary number of patients each with an arbitrary number of visits. A visit is recorded by saving the date of the visit. Only one ...
2
votes
0answers
102 views

Is this user-storage database system efficient, effective and secure?

I am using: PHP Version 5.2.17-MIT-BACKPORT-GnuTLS SQLite 2.8.17 I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...
2
votes
0answers
1k 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? ...
2
votes
0answers
325 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
2
votes
0answers
1k 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 ...
2
votes
0answers
984 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
0answers
11 views

Can sqlite-utils convert function select two columns for conversion?

I'm using sqlite-utils to load a very large CSV into sqlite for later use in Datasette. In the CSV there are two columns, likes and dislikes, which I would like to use to create a third column quality-...
1
vote
0answers
21 views

Creating the right index/es for querying views in SQL(ite3)

I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
1
vote
0answers
56 views

SQLite, Sort by Index, faster without index

I'm just reading this excellent guide to relational database query planners and indexes on SQLite site. In section 2.2. Sort By Index it says the following about sorting by index: But does this ...
1
vote
1answer
12 views

sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question

All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in ...
1
vote
1answer
87 views

How should I shift values to one place back in SqLite?

I have a SqLite DB in which i have 16 columns as id, and f1 to f15. Now Mistakenly I entered data from 2 column, leaving the first one empty. Now my all values are one place ahead i.e value of last ...
1
vote
0answers
50 views

Copying a dependency structure

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
1
vote
1answer
33 views

Equivalent to BIT_OR function in SQLite?

I have code that runs against a MySQL database but the unit tests for the code use SQLite. The issue is one code method uses a SQL query which aggregates using the BIT_OR function of MySQL. Is there ...
1
vote
0answers
46 views

Why does adding an index increase the execution time in SQLite?

I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
1
vote
0answers
205 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
1
vote
1answer
27 views

How to query/join tables where foreign key constraint is set in schema

I'm relatively new to sqlite and databases in general. I've successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key ...
1
vote
2answers
50 views

Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

I am facing the following problem: I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
1
vote
0answers
82 views

Slow insert on very large tables, (even in transactions)

I have a table with about 50 million rows, the table itself is fairly simple CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE) I then do an insert INSERT OR IGNORE INTO Word (word) ...
1
vote
0answers
446 views

SQLite database: many-to-many optimization (simple but large example) without IDs available?

I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
29 views

Getting text surround search queries?

I'm looking for a way to retrieve search results from an sqlite3 db, including some text around the query, to show context in the search results. I'm not finding any examples of this. Is this ...
1
vote
1answer
1k views

Creating a database structure for a quiz

My app has this menu: TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes. LearningMode - make a quiz with all the questions from my db, displaying the correct ...
1
vote
0answers
20 views

Optional for one entity, optional for another entity: many end optional

Please could you help me in sqlite : agents may make bookings for performers, and performers may also make bookings for themselves. It is also possible for agents to make bookings for other functions ...
1
vote
0answers
67 views

Computer runs out of space when indexing database on external disk

I have an SQLite database of about 80 GB on an external harddrive. I want to index some of the columns there, but when I do the operation fails after some time, with this error: sqlite3....
1
vote
0answers
462 views

Attendance and payroll management design

I am developing an Android app for the first time. For the database I am using SQLite . I am new to the concept of relational database. I am creating an app for attendance and payroll management. I am ...
1
vote
0answers
300 views

Is there a concept of file locking In SQLite?

I am using five threads to insert data into a SQLite table, and five threads to read data from the same table. I am not getting any locking error. Is there any file locking / table locking concept in ...
1
vote
1answer
281 views

SQLLite query datatime divided into week of the year, and month of the year

In my database I have two tables: type_of_activity CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20)) activity CREATE TABLE IF NOT EXISTS activity( ...
1
vote
0answers
1k views

Necessary tuning settings for SQLite databases

I am working on a SQLite DB containing ~30 .db files. Each of these .db files corresponds to one table, e.g., the "table1.db" file contains a "table1" table, "table2.db" contains "table2" table and so ...
1
vote
1answer
815 views

SQLite database replication on the same server

We have an application written in C and we are looking for a way to do transparent replication of SQLite database on one server (but on different disks). I read lots of guides about replication across ...
1
vote
0answers
258 views

How to import sqlite from plain text dump?

This excellent XKCD article about color-poll data at the very end mentions: If you have ideas and want to analyze these results further, I’ve posted the raw data as an SQLite dump here (84 MB .tar....
1
vote
0answers
763 views

SQL: Selecting from single column as multiple columns, dealing with 'missing' entries?

Data format in table of interest: id (Foreign Key), field_id (numbers fields within each id, from 0), field (primary identifier of type of data stored in field_data), subfield (secondary ...
1
vote
0answers
31 views

Great slowdown of Django object list retrieval after moving from sqlite3 to mySQL on Amazon RDS

Be warned, there will be some Python programming stuff; my problem is tightly connected to database engine differences though - or at least I think so. tl;dr: Querying for list of items and all ...
1
vote
0answers
255 views

SQLite vs MemSQL vs other SQLAlchemy compatible in memory databses

What is the recommended in-memory database which connects to SQLAlchemy? Requirements: Connects to SQLAlchemy. Minimal memory usage when empty (it will store <50MB). Easy to work with (e.g. Kafka ...
1
vote
0answers
389 views

Would it be good practice to represent MongoDB ObjectId as a String or a Blob type in SQL Lite (RDMS world)?

All: We have a software application with 1) Web-based component 2) Mobile-based component Our 1) Web-based component uses a) MongoDB as the Database b) ASP.NET C# Our 2) Mobile-based component ...
1
vote
0answers
270 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 ...