We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

157

Because "multiple concurrent writes" is much, much harder to accomplish in the core database engine than single-writer, multiple-reader. It's beyond SQLite's design parameters, and including it would likely subvert SQLite's delightfully small size and simplicity. Supporting high degrees of write concurrency is a hallmark of large database engines such as ...


101

Short version: Web SQL was deprecated because standards are really important and turning Web SQL into a proper standard would have been prohibitively difficult. Since existing implementations of Web SQL are basically wrappers around SQLite, any attempt to define a standard of it was basically "do what SQLite does." This isn't good enough; a true standard ...


26

The realistic limit (of the size of some Sqlite database) is the same as the realistic limit for a data file. And that limit depends a lot of your computer & system. On my current Linux desktop I cannot afford much bigger than a 350Gbyte file (because as a rule of thumb I avoid having one single file eating more than half a disk partition). BTW, that ...


25

You are looking for the File Locking And Concurrency documentation. SQLite processes use a series of locks to handle concurrency; to read, several processes can obtain a SHARED lock. A process that writes, will need to obtain a RESERVED lock, and only when actually having to flush changes to disk does it move to the PENDING state. Any reading process will ...


23

I recommend reading the official answer to your question, Appropriate Uses For SQLite. Specifically, the "Situations Where Another RDBMS May Work Better" warns that SQLite does not support concurrent writing: SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, ...


19

Store the words using a text format, and build a database during the build. Instead of storing the information in binary format and storing the binary file in the version control, keep the pairs in a CSV file. This allows you to easily change it, commit the changes and, later, diff the changes with ease. Later, when you actually need this data in a ...


17

Josh Kelley's answer is so far the BEST answer ever I've found about the reason of the standard work to be stopped. That said, I think there is an additional perspective to consider regarding the user-base. Eventhough, I disagree on Ido Green's approach to the subject ("This is a recommendation for web developers to no longer use the technology as ...


14

You absolutely could write SQL queries and store whatever data you need in a SQLite database or just write raw data to a file. In fact you are free to do so right now in an iOS app. Why then might we want a different abstraction? Immediately you're going to notice that your application is working with objects and you need to translate them into some form ...


12

Because there's no server which can tell whether things are to be written to the same place or not. There are just two processes trying to write to a file. As pointed in a comment, concurrent writes could also be supported by an internal thread. Not sure how well this would work (did not think much about it either). Anyway here is why SQLite does not use ...


10

Just wanted to follow up and let everyone know that the implementation was successful. Working with SQLite was a real pleasure, and with only one process writing it at a time we never had issues with lock-up... even with very rapid concurrent reads from a secondary process.


9

It is the naming convention chosen to be used in Core Data SQLite. There are several reasons why it was chosen. One of the main reasons is Z is one of the least used letters of the alphabet so they surely felt by pre-fixing table names and entity names with Z they open more options for developers to name their own tables with fewer chances of stomping on ...


9

making queries on things like temperature to sales of specific categories of products This (Queries) is one area relational databases are designed for. You are better off with a database with SQL support.


9

I don't agree that storing settings in a database table is a bad separation of concerns. Your "concerns" will be separated by not defining a relationship between the settings table and other tables in your database. This is fine. The main thing to consider is how you expect the administrator of your system to make changes to the settings. It's much easier ...


7

You must consider the particular pressures that apply to your situation and decide based on that. The general truths are: If you put every possible attribute into your definition of 'item' as a field, many items will have redundant fields, which might waste space if you are very constrained on space. Also, introducing a new attribute will require a database ...


7

No, dynamic typing requires both more storage space and more processing time, especially since they also add type affinity, meaning it has a preferred type that the programmer is free to ignore. It is truly an intentional feature with real trade off costs. Those costs are effectively negligible for the use cases SQLite targets, but they are still there. ...


7

Sqlite is an in-process database, which means that its performance characteristic is going to be similar to a well optimized application that uses a file to store data. In an IaaS or PaaS cluster, an Sqlite website can be infinitely scaled out extremely easily for small, read-only sites. Simply deploy a new application server with a copy of the sqlite file,...


5

Don't guess, measure When you have an issue related to performance, don't guess. Grab a profiler, measure your code, and decide what to optimize based on the results, not your or somebody else's guesses. Your first guess is that sqlite3 is slow. Your second guess is that you have memory leaks. Chances are, you'll find when profiling your app that neither ...


5

I can't add comments to expand on k3b's answer... so I have to add a new answer. YOU should decide how to handle the onUpgrade process yourself. Notice the arguments in onUpgrade: public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {...} You are provided with the "oldVersion" and the "newVersion". oldVersion is the ...


5

I asked the same question years ago and actually built the app both ways. The build-my-own database version was a lot of fun, but it was a toy. Using a real database enabled a commercial product that performs well under stress. SQL Database SQL lets you write one-liners for most of the kinds of reports you are talking about - one-liners that other ...


4

This is something I've long desired as well. But a kernel scheduler only has to decide what task to run right now, not when in the future to run other tasks. So those schedulers may help you with part of the problem, but there is a lot more here than they solve. And they have a key bit of information you aren't keeping; namely if a task is blocked or not. ...


4

Putting on my IT Director hat I see a few no-gos here: Risk of data corruption. Perhaps more percieved than real but end of the day this is a non-transactional file type DB that does not have much if any recourse to bad writes besides asking if you've got a recent backup. Speaking of which . . . How do I back this thing up? In a manner I know I've got a ...


4

Because SQLite is a simple self-contained library, and because most SQLite databases (on an Android tablet or Google Chrome computer) are very probably accessed by only one (or a few) processes or threads at a time. (Accessing or saving cookies happens rarely in a browser, probably at most on each page reload, and they are probably also cached in the browser)...


4

There are a couple benefits to storing the day of the week as a bitarray. Fewer resources - as you've said, there would be 7 integer columns to support a separated day of the week schema. This probably won't be a big issue in a small database like the one you're describing. I wouldn't base a decision on this advantage alone. Easy querying - I think this is ...


4

The largish legacy application I maintain has gone back and forth on this over the years, but has evolved towards keeping most settings in the DB. Yes, the DB connection string is read from the config file. This is a desktop application that heavily uses a DB and which has quite a few settings. If you have very few this will be less applicable, but these are ...


4

SQLite is an in-process database. If you are only ever going to have a single process running your website, then it is fine. If you ever need to have more than one process (and likely even more than one thread) writing to the same database, SQLite is not for you. It's that simple. This basically means that you can only be handling one request at a time [1],...


4

It happens to be possible to use SQLite databases as a data interchange format, but it's not a particularly good solution. Why? You are looking for a compact data exchange format. In contrast, SQLite's goal is to store data in a manner that is easily queried and modified on disk. This is not necessarily a compact format, e.g. if the database contains free ...


4

You should look into what kind of barcode the books are using you are going to store. Take a look here for more information about bar codes https://barcode.tec-it.com/en/EAN13?data=978020137962 You should also look into if you need to store leading zeros. And your primary key does not have to be the value from the bar code.


3

I wrote this stuff a long time ago for a C++ application and made a post on my blog. Here's a quick summary (since the original article is quite long and contains file attachments): In SQLite you write callbacks in raw C++: Header file (file.h) #include <iostream> #include <sqlite3.h> #include <cstdlib> #include <assert.h> void ...


3

@Samuel's answer is fine, nevertheless I like to play the devil's advocate here and argue for a slightly different point of view. IMHO It is - more or less - a matter of taste. If you store one or seven integers per record in a database will be most likely negligible for any kind of real world application, as long as you do not expect to manage several ...


3

The .sqlite file is the complete database. It's one of the big features of sqlite in that it's single-file with binary compatibility between platforms. From the About SQLite page (emphasis added): SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ...


Only top voted, non community-wiki answers of a minimum length are eligible