Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

On Fri, 13 Mar 2020 11:22:46 -0400 Richard Hipp wrote: > 4. Your email address is never displayed, even to subscribers. While I have no illusions of altering the online trajectory of this ML, I'd like to point out that email addresses foster community. Studies and experience both show that

Re: [sqlite] Fwd: inserting new data only

On Tue, 25 Feb 2020 12:02:24 -0500 Przemek Klosowski wrote: > and I want to avoid storing repetitive data, so that the database > should contain > 10:32 12 > 10:35 15 > 10:39 13 > 10:46 18 > where only the earliest time with the unchanging value is stored. Be careful what you wish for.

Re: [sqlite] "Standard SQL" ?

On Sun, 2 Feb 2020 10:05:11 +0100 Markus Winand wrote: > When you say ?many standards? do you mean the different releases > those standards have? Yes. > IMHO, there is only one SQL standard, namely ISO/IEC 9075. The > current and technically only valid version is that of 2016 (even > though

Re: [sqlite] Check constrain execution timing change? (Now a bug)

On Mon, 3 Feb 2020 10:45:50 +0100 Dominique Devienne wrote: > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote: > > > On 2/1/20, Thomas Kurz wrote: > > > Does this mean there will be no possibility to prevent inserting > > > a string into an integer column anymore? > > > > > > create table

Re: [sqlite] importing data to a table that has generated-columns

On Mon, 27 Jan 2020 12:00:52 -0700 "Keith Medcalf" wrote: > If you mean importing into a pre-existing table as above that is > declared with "check typeof(a) in ('integer', 'real')" then yes. The > check constraint is run before affinity is applied rather than after > (unfortunately) ... Thank

Re: [sqlite] New word to replace "serverless"

On Mon, 27 Jan 2020 17:18:45 -0500 Richard Hipp wrote: > But more recently, "serverless" has become a popular buzz-word that > means "managed by my hosting provider rather than by me." Many > readers have internalized this new marketing-driven meaning for > "serverless" and are hence confused

Re: [sqlite] "Standard SQL" ?

On Thu, 30 Jan 2020 19:19:53 + Simon Slavin wrote: > Have any of you been using this term for a meaning other than "Fully > conforming to SQL:2019 (or whatever version you think current) ? Do > you have documentation somewhere ? Or are my suspicions correct and > there's no such thing ? I

Re: [sqlite] importing data to a table that has generated-columns

On Sun, 26 Jan 2020 12:01:32 -0700 "Keith Medcalf" wrote: > Now that the table exists, use "SELECT * FROM " to determine > the number of columns in the table (which will include computed > always columns, if any). ... > Otherwise, Richard *may* make some changes to the .import logic which >

Re: [sqlite] Unexplained table bloat

On Sun, 12 Jan 2020 16:24:53 -0700 "Keith Medcalf" wrote: > while one may be tempted to claim that "consistency is the hobgoblin > of little minds" You might have forgotten that the much overpraised Ralph Waldo specified "a foolish consistency". He only meant: don't try to hang your hat on

Re: [sqlite] Unexplained table bloat

On Sun, 12 Jan 2020 15:48:05 -0700 "Keith Medcalf" wrote: > SQL is simply a Structured Query Language that can be overlaid on a > variety of datastore models, one of which is the Relational model. I think that's misleading, Keith, for a couple of reasons. SQL was invented by IBM expressly

Re: [sqlite] Best way to store key,value pairs

On Mon, 13 Jan 2020 10:37:57 -0800 Jens Alfke wrote: > > On Jan 12, 2020, at 4:12 PM, James K. Lowden > > wrote: > > > > What is the motivation behind this advice? It's completely > > unnecessary. > > Thanks for your opinion, James! I disagree. >

Re: [sqlite] Best way to store key,value pairs

On Tue, 7 Jan 2020 17:11:45 -1000 Jens Alfke wrote: > Consider encoding the headers as JSON and storing them in a single > column. SQLite has a JSON extension that makes it easy to access > values from JSON data in a query. What is the motivation behind this advice? It's completely

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

On Mon, 9 Dec 2019 22:02:07 -0500 Richard Damon wrote: > If we assume that over-committing has been removed, then the fact > that the fork succeeded is the promise that both processes have the > right to access all of their address space. Any page that is writable > needs to have swap space

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

On Sat, 7 Dec 2019 05:23:15 + Simon Slavin wrote: > (Your operating system is allowed to do this. Checking how much > memory is available for every malloc takes too much time.) Not really. Consider that many (all?) operating systems before Linux that supported dynamic memory returned an

Re: [sqlite] Things you shouldn't assume when you store names

On Wed, 13 Nov 2019 15:37:15 -0600 Peter da Silva wrote: > My last name has a space in it. Don't get me started. My phone number has dashes in it, two to be exact. I don't remember the last website that accepted it verbatim. We're pretty far from a database discussion. It is a wonder,

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Mon, 28 Oct 2019 12:10:38 -0600 "Keith Medcalf" wrote: > CREATE TABLE t1 (a, b); > INSERT INTO t1 VALUES(1,2); > INSERT INTO t1 VALUES(2,3); > INSERT INTO t1 VALUES(1,3); > CREATE TABLE t2 (b, c); > INSERT INTO t2 VALUES(2,3); > INSERT INTO t2 VALUES(3,4); > select * from t1 left join t2 on

Re: [sqlite] Single or double quotes when defining alias?

On Fri, 25 Oct 2019 23:55:20 +0200 Thomas Kurz wrote: > SELECT column1 AS 'c' > --or-- > SELECT column2 AS "d" > > On the one hand, the name refers to a column or table identifier. The SQL-92 standard refers to that kind of name as a "correlation name", and its BNF grammar designates a

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Sun, 27 Oct 2019 15:09:03 -0700 Benjamin Asher wrote: > My colleagues and I are trying to understand the role of ON > constraints vs WHERE clauses in JOINs. ON applies before JOIN. WHERE applies after. That's a loose interpretation, but IMO it's nevertheless a useful way to think about

Re: [sqlite] Date time input

On Mon, 7 Oct 2019 18:17:14 +0100 Simon Slavin wrote: > Converting data to and from a convenient storage format is not the > job of a DBMS. While I have no quarrel with your specific point about date strings, this particular statement is too broad. Arguably, data conversion is an inherent,

Re: [sqlite] Date time input

On Tue, 8 Oct 2019 09:06:24 -0700 Jens Alfke wrote: > I think the idea of a semi-official ?SQLite++? has been floated here > before OK, but it needs a better name. What better place than here to debate that? ;-) What the opposite of "Lite"? I don't know. It's like asking for the opposite

Re: [sqlite] DBMS Normalization Query

On Wed, 2 Oct 2019 21:42:13 -0700 (MST) shivambhatele wrote: > 1. When is the process of normalization used? > 2. Boyce and Codd Normal Form > 3. 1NF, 2NF, and 3NF Good information on database theory is remarkably scarce on the web, and difficult for the beginner to distinguish from bad

Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))

On Tue, 13 Aug 2019 16:47:43 -0400 Richard Hipp wrote: > On 8/13/19, Jose Isaias Cabrera wrote: > > > > I see all of you smart programmers using this > > non-column matching behavior, and I ask myself why? > > Because that's the way Dennis Richie did it. :-) That's right. Like many of a

Re: [sqlite] DEF CON (wasL A license plate of NULL)

On Mon, 12 Aug 2019 14:14:08 -0600 "Keith Medcalf" wrote: > Perhaps I am just lazy but I see no point in engaging in extra work > for no advantage bool is_true (bool tf) { if (tf == true) { return true; } return false; } --jkl

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

On Mon, 12 Aug 2019 12:02:33 +0200 Kira Backes wrote: > I would change the error description for SQLITE_BUSY_SNAPSHOT so that > it no longer says "read transaction" but instead says "read > transaction or ongoing select statement". While I agree the documentation could be clarified, that

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

On Wed, 31 Jul 2019 12:05:05 +0200 Olivier Mascia wrote: > Nothing stops any piece of your own programming or anyone using the > CLI to do: > > INSERT INTO "VALUES"(value_int) VALUES('something'); Not nothing, just nothing automatic. value_int INTEGER not NULL

Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0

On Thu, 11 Jul 2019 10:21:10 -0400 Richard Hipp wrote: > If you have personally experienced some unusual or unexpected feature > of SQLite that you think should be added to "quirks.html", please > follow-up to this thread Thank you for publishing this page. I would suggest these additions: 1.

Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

On Sat, 22 Jun 2019 23:14:08 -0700 Ben Earhart wrote: > can't be bothered to write example sql code While I'm sure you're irritated, that criticism is misplaced. You might want to take a step back. Tools that work with standardized languages don't define the language they process. You won't

Re: [sqlite] Understanding the WITH clause

On Mon, 17 Jun 2019 20:46:41 -0400 Sam Carleton wrote: > It is my view that peace is not something that can be defined with > some words, rather it is a universal experience. "But peace is not merely the absence of this tension, but the presence of justice." -- MLK, Letter

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On Wed, 12 Jun 2019 18:40:19 -0400 Richard Hipp wrote: > On 6/12/19, James K. Lowden wrote: > > what kind of computation > > would lead to a value in memory representing -0,0? > > 0.0/-1.0 Fine. I suspect the reason -0.0 has never cropped up as an issue in my experienc

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

On Thu, 13 Jun 2019 10:44:52 -0400 Doug Currie wrote: > > Except by the rules of IEEE (as I understand them) > > > > -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" > > > > Except that 0.0 is also an approximation to zero, not "true zero." Sure, because floating point

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On Wed, 12 Jun 2019 12:57:22 -0400 Richard Hipp wrote: > On 6/12/19, Thomas Kurz wrote: > >> For an SQL engine, the next-best-thing to strict binary IEEE754 is > >> not > > sloppy binary IEEE754, its probably strict decimal IEEE754. > > > > That would be a *really great* improvement! > > It

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On Wed, 12 Jun 2019 10:28:20 -0600 "Keith Medcalf" wrote: > Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 > 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or > "license" for more information. > >>> a = -0.0 > >>> b = 0.0 > >>> print(a,b) > -0.0 0.0 > > Really

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On Wed, 12 Jun 2019 09:35:13 -0400 Richard Hipp wrote: > Question: Should SQLite be enhanced to show -0.0 as "-0.0"? No. 1. Prior art. I can't think of a single programming language that displays -0.0 without jumping through hoops. 2. Math. Negative zero is not a mathematical

Re: [sqlite] New Information Schema Views

On Fri, 07 Jun 2019 01:43:18 -0600 "Keith Medcalf" wrote: > -- Catalog Views using sqlite_master for SysObjects (Object Names) > -- requires that the eval() extension function be loaded and available > -- all TEXT columns in views have "collate nocase" attachmented to > the output > -- columns

Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4

On Tue, 4 Jun 2019 11:26:12 -0700 "Doug" wrote: > select songfile_id,dancename,dancegroupname from songfiletable where > dancename like "Waltz"; What Shawn Wagner's answer shows you is that 'Waltz' is a string and "Waltz" is a column name, because in SQL double-quotes

Re: [sqlite] round function inconsistent

On Fri, 24 May 2019 13:10:49 + Jose Isaias Cabrera wrote: > >Consider these two queries: > > > > SELECT round(3.255,2); > > SELECT round(3.2548,2); > > > >Do you expect them to give different answers? > > 3.26 > 3.25 sqlite> SELECT cast(100 * (0.005 + 3.2548) as

Re: [sqlite] How to set access permissions to protect a database file?

On Sun, 26 May 2019 19:52:29 +0800 Adrian Ho wrote: > Finally, create a "reading_room" script that your users will run: > > #!/usr/bin/env bash > > sudo -u reading_room /path/to/reading_room.tcl This script is more efficient and portable: #! /bin/sh sudo -u reading_room

Re: [sqlite] SQL Features That SQLite Does Not Implement

On Wed, 22 May 2019 17:56:23 -0700 Jens Alfke wrote: > > On May 22, 2019, at 3:55 PM, James K. Lowden > > wrote: > > > > I've always thought user-defined types were unnecessary except as a > > convenience. > > User-defined types are quite important i

Re: [sqlite] User Defined Types implementations ...

On Wed, 22 May 2019 21:16:04 -0600 "Keith Medcalf" wrote: > Basically, when you declared something as a UDT you were giving a > "blob" a type-domain. Whenever you tried to do something with a UDT > a "mangled function name" was generated that took that blob as the > first argument and you

Re: [sqlite] SQL Features That SQLite Does Not Implement

On Wed, 22 May 2019 14:20:11 -0600 "Keith Medcalf" wrote: > (such as was added to DB2 back in the late 80's early 90's, and > which I do not think anyone else has implemented as nicely anywhere > else) That's an interesting aside. It would make an interesting OT thread, if you're inclined to

Re: [sqlite] SQLite with single writer on Windows network share

On Wed, 8 May 2019 21:36:43 +0200 Clemens Ladisch wrote: > Andrew Moss wrote: > > ... an SQLite database hosted on a windows network share (using > > server 2012 R2 or later). We are well aware this is not advisable > > There are three possible sources of network filesystem data > corruption: >

Re: [sqlite] Custom collation of blobs

On Fri, 26 Apr 2019 10:36:34 -0700 Jens Alfke wrote: > The only workaround I can think of is to define a custom function > that converts a blob to a string and collate using those strings ? > e.g. `? ORDER BY collatable_blob(b)`. But this requires expensive > string conversions, Your UDF need

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

On Fri, 26 Apr 2019 02:09:33 +0100 Lullaby Dayal wrote: > We use sqlite3 in an embedded automotive system based on QNX > hypervisor running multiple virtual machines. Am I the only one who reads a sentence like that and thinks, "I don't want to drive that car"? I hope the embedded automotive

Re: [sqlite] Multiple Independent Database Instances

On Mon, 22 Apr 2019 21:25:31 + "Lee, Jason" wrote: > I have a set of several million database files sitting on my > filesystem. Each thread will open a previously unprocessed database > file, do some queries, close the database, and move on to the next > unprocessed database file.

Re: [sqlite] compressed sqlite3 database file?

On Thu, 11 Apr 2019 18:45:01 -0600 Warren Young wrote: > Sure, but what *is* on the disk after a crash is always consistent > with ZFS, so any decent database engine can recover. It's been some years, but I saw a presentation about running Postgres on ZFS. Every "victory" was a way to

Re: [sqlite] Problem with SELECT by rowid after INSERT

On Fri, 12 Apr 2019 11:40:13 -0400 Jim Dossey wrote: > CREATE TABLE "sessiond" ( > "journal" VARCHAR(4) DEFAULT '' NOT NULL, > "session" VARCHAR(16) DEFAULT '' NOT NULL, > "pid" INTEGER DEFAULT 0 NOT NULL, > rowid INTEGER PRIMARY KEY > ); Although it has nothing to do with the problem you

Re: [sqlite] Option to control implicit casting

On Thu, 11 Apr 2019 11:35:04 +1000 John McMahon wrote: > > SELECT x * y & ~1 AS even_numbered_area FROM squares; > > Suggestion: "Don't Do That", use database purely as a storage medium. You yourself don't really believe that! A disk is a storage medium. A file is an undifferntiated stream

Re: [sqlite] compressed sqlite3 database file?

On Wed, 10 Apr 2019 15:14:59 -0600 Warren Young wrote: > On Apr 10, 2019, at 2:12 PM, Keith Medcalf > wrote: > > > > It is far cheaper and much more reliable to just buy some file > > storage space. > > If you?re going to buy some more storage, you should put ZFS on it > then, too. :)

Re: [sqlite] compressed sqlite3 database file?

On Wed, 10 Apr 2019 15:20:32 -0500 Peter da Silva wrote: > > Why would anyone fart about with added complication and the > > concomittant increased unreliability when storage is so damn cheap? > > Embedded systems and mobile devices. > > But of course those probably don't apply here. :) Are

Re: [sqlite] Option to control implicit casting

On Mon, 8 Apr 2019 23:08:18 -0400 Joshua Thomas Wise wrote: > I propose there should be a compile-time option to disable all > implicit casting done within the SQL virtual machine. You can use SQLite in a "strict" way: write a CHECK constraint for every numerical column. Just don't do that

Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 18:54:18 + Jose Isaias Cabrera wrote: > Why does this work I don't know what "work" means, but I can explain the difference. With an outer join, JOIN and WHERE are not the same. In analyzing the query, we consider JOIN before WHERE. > select > a.*, b.* from t as a

Re: [sqlite] Remove row to insert new one on a full database

On Fri, 5 Apr 2019 15:45:10 +0300 Arthur Blondel wrote: > The data is always the same. That's why removing one row should be > enough to insert a new one. > My problem is that some times I need to remove many rows to add one > new one. SQLite *could* avoid that problem by pre-allocating space

Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 14:01:20 + Jose Isaias Cabrera wrote: > The owners of the business said that "there will never be...", The perfect opportunity for a CHECK constraint or to enforce a foreign key. --jkl ___ sqlite-users mailing list

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Thu, 4 Apr 2019 11:21:41 -0400 Joshua Wise wrote: > > On the other hand, what table has a floating point number in its > > key? > > > > How do you even express the value of such a key for an exact > > match? > > Well I imagine it can be very useful for range queries. Imagine > Julian

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Thu, 4 Apr 2019 17:30:29 +0200 Lifepillar wrote: > On 4 Apr 2019, at 17:15, James K. Lowden > wrote: > > On Wed, 3 Apr 2019 14:30:52 +0200 > > Lifepillar wrote: > >> SQLite3 Decimal is an extension implementing exact decimal > >> arithmetic for SQLite3

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Wed, 3 Apr 2019 14:30:52 +0200 Lifepillar wrote: > SQLite3 Decimal is an extension implementing exact decimal arithmetic > for SQLite3. It is currently unfinished and under development. ... > I welcome any feedback, from the super-technical to the > end-user oriented. There is no manual so

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Wed, 3 Apr 2019 11:37:47 -0600 Warren Young wrote: > Put another way, your defaults are already so large that no > conceivable physical entity could build a computer big enough to > simultaneously contain every distinct state your data type represents. Exactly (as it were). Physical

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Wed, 3 Apr 2019 17:29:47 -0400 Richard Hipp wrote: > On 4/3/19, Joshua Wise wrote: > > From my naive understanding, memcmp() is used to efficiently > > compare long strings of bytes. But where in SQLite3 is it necessary > > to compare long strings of floating point numbers? I, of course, > >

Re: [sqlite] UPSERT with multiple constraints

On Wed, 27 Mar 2019 23:59:47 +0100 Thomas Kurz wrote: > Sure. I have a table of items. Each item has a type, a name, and > properties A, B, C (and some more, but they're not relevant here). > > I want to enforce ... UNIQUE (type, name). ... > Furthermore, items of a certain type that have

Re: [sqlite] .import fails CHECK constraint on valid data

On Sun, 10 Mar 2019 17:04:46 -0400 "James K. Lowden" wrote: > Why does the .import command cause the CHECK constraint to fail, when > an ordinary INSERT does not? On Sun, 10 Mar 2019 14:12:33 -0700 Shawn Wagner wrote: > The check constraint is probably being evaluated

Re: [sqlite] printf() - Local decilmal separator

On Tue, 12 Mar 2019 16:08:24 + Alexandre Billon wrote: > 1st question : Is there a way to tell printf() to display the decimal > separator set in the OS ? > > For example, the decimal separator in my OS is set to comma (,), I > would like printf() to display the comma as the decimal

Re: [sqlite] Recursive CTE on tree with doubly linked items

On Mon, 11 Mar 2019 10:39:06 +0100 Jean-Luc Hainaut wrote: > Your implementation of trees is that of network databases at the > pointer-based physical level but definitely not relational. Try this: > > create table TREE( >ID integer not null primary key, >Parent integer references

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

On Tue, 12 Mar 2019 10:36:37 -0400 ted.goldbl...@gmail.com wrote: > The problem is basically that as part of a test, the customer wants > to power fail the device, and then to be able to recover (after the > device restarts) data that was generated/stored as close to the power > failure as

[sqlite] .import fails CHECK constraint on valid data

$ sqlite3 db "create table T (t integer not null);" $ sqlite3 db "create table Tc (t integer not null check(typeof(t) = 'integer'));" $ echo 1 > dat $ sqlite3 db ".import 'dat' T" $ sqlite3 db ".import 'dat' Tc" dat:1: INSERT failed: CHECK constraint failed: Tc $

Re: [sqlite] Equiv stmts, different explain plans

On Tue, 05 Mar 2019 13:58:06 -0700 "Keith Medcalf" wrote: > >The query requests no such thing. SQL makes no request or > >suggestion for how to execute a query. It simply describes a result. > >It's up to the implementation to determine how to produce that > >result. > > You are, of course,

Re: [sqlite] Equiv stmts, different explain plans

On Mon, 04 Mar 2019 20:20:08 -0700 "Keith Medcalf" wrote: > In the first query the subselect that creates the list is independent. > In the second query the subselect that creates the list is correlated. Yes, and if it can be shown that the two queries are logically equivalent under relational

Re: [sqlite] [EXTERNAL] Equiv stmts, different explain plans

On Tue, 5 Mar 2019 08:13:32 + Hick Gunter wrote: > The second statement uses a *correlated* subquery as the RHS of an IN > expression. The QP needs to actually run this query for every record > of t1 that matches the condition t1.c == 1. I'm not sure what you mean be "needs", above. If

Re: [sqlite] Is there a tool to convert `where`s to equivalent `join`s?

On Fri, 1 Mar 2019 13:44:57 +0530 Rocky Ji wrote: > So for learning sake, is there a tool that converts a query using > `WHERE` to a query (that yields identical results) using JOINs? Like > a English -> side-by-side translator. SQL-92 was introduced a long time ago. At that time, I don't

Re: [sqlite] Checking differences in tables

On Tue, 12 Feb 2019 15:05:29 + Jose Isaias Cabrera wrote: > >SELECT * From t WHERE datestamp = "20190208" > > Ok, Simon, I'll bite; :-) Imagine this table: > > t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate) That's better. > how would I find the differences in the fields based on the

Re: [sqlite] Min/Max and skip-scan optimizations

On Mon, 4 Feb 2019 18:55:33 +0100 Gerlando Falauto wrote: > I remember reading ORDER BY is only allowed in > the outer query As Keith said, SQLite allows ORDER BY in subqueries. The SQL standard does not. Logically, ORDER BY makes sense only for the outer query. An SQL SELECT statement

Re: [sqlite] SQLite slow when lots of tables

On Tue, 29 Jan 2019 12:00:49 +0100 (CET) mzz...@libero.it wrote: > what happens if I put all data in a single table and this table > become very huge (for example millions of rows)? Big tables are your friend, actually. A binary search on 1 million rows requires at most 20 operations. A

Re: [sqlite] SQLite slow when lots of tables

On Mon, 28 Jan 2019 16:28:41 + Simon Slavin wrote: > SQL is not designed to have a variable number of tables in a > database. All the optimization is done assuming that you will have a > low number of tables, and rarely create or drop tables. This. The table name should not be meaningful

Re: [sqlite] Is this rewrite of a query OK

On Fri, 25 Jan 2019 23:35:51 +0100 Cecil Westerhof wrote: > SELECT MIN(totalUsed) AS minimum > , MAX(totalUsed) AS maximum > , MAX(totalUsed) - MIN(totalUsed) AS range > FROM quotes > > But I did not like it because I repeated the MIN and MAX.

Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 18:12:25 -0500 Richard Damon wrote: > Some operations can be order of microseconds if the data resides in > cache, Thank you, I hadn't considered that. I was thinking that seek times on "spinning rust" -- which is the only economically feasible technology for large

Re: [sqlite] Database locking problems

On Sun, 20 Jan 2019 21:51:19 + wrote: > > insert into t > > select :pid, nrows, N > > from (select 1 as N union select 2 union select 3) as cardinals > > cross join (select :pid, count(*) as nrows from t) as how_many; > > > > By using a single SQL statement, you avoid a user-defined > >

Re: [sqlite] Database locking problems

On Sun, 20 Jan 2019 17:01:25 -0700 "Keith Medcalf" wrote: > SQLite3 however has latencies on the order of microseconds Is that really true? Are there machines for which SQLite's throughput can be measured in transactions per millisecond? I think you're referring to the latency of the

Re: [sqlite] Variable Declaration

On Sat, 19 Jan 2019 12:01:34 -0700 "Keith Medcalf" wrote: > Microsoft took the OS/2 3.0 Beta 2 code and generated their OS/2 New > Technology. The "New Technology" part was considered to be a bit to > long, so Microsoft shortened it to NT, replaced the Presentation > Manager with with Windows

Re: [sqlite] Database locking problems

On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: > The busy timeout is not working because you start out your transaction > using a read operation - the first SELECT statement - which gets a > read lock. Later when you go to COMMIT, this has to elevate to a > write lock. But SQLite sees

Re: [sqlite] SQLite 3.21.0 for z/OS UNIX - installation issues

On Fri, 4 Jan 2019 16:14:22 +0100 Mario Bezzi wrote: > awk '($0  ~ /^CC = /) { $0 = "CC = xlc" } > ($0  ~ /^CFLAGS = /) { $0 = "CFLAGS = -O2 -q32 -qfloat=ieee > -qnolist" } ($0  ~ /^LDFLAGS = /) { $0 = "LDFLAGS = -q32" } > ($0  ~ /^DEFS = /) { for (i = 1; i <= NF; i++) \ > { if ($i ==

Re: [sqlite] Using sqlite3_interrupt with a timeout

On Mon, 31 Dec 2018 14:25:41 -0700 "Keith Medcalf" wrote: > def run_query_with_timeout(db, query, timeout, whizround) > stmt = prepare(db, query) > create_thread A interrupt_function(db, stmt, timeout, > whizround) while sqlite3_step(stmt) == SQLITE_ROW > ... process

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

On Thu, 20 Dec 2018 15:42:27 + Chris Locke wrote: > and a model number is a numeric number My phone's model number is VVX 500. > set the column affinity to the type of data Yes, and not everything that looks like a number is a number. Some things that start out looking like numbers

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

On Wed, 19 Dec 2018 10:55:11 + Chris Locke wrote: > Fields with '_no' are read as 'number' and so should be a number. > OK, that doesn't always work for 'telephone_no' (they usually start > with a 0 Lots of numbers are labels that aren't meant to be calculated on. Item number, part number,

Re: [sqlite] Question about floating point

On Tue, 18 Dec 2018 17:34:29 -0500 Dennis Clarke wrote: > some serious reading and experiments are needed to get a good > handle on why numerical computation is as much art as it is science. > If we wander into the problem without sufficient study and VERY > careful consideration then we are

Re: [sqlite] Question about floating point

On Mon, 17 Dec 2018 17:35:54 + Simon Slavin wrote: > On 17 Dec 2018, at 5:16pm, James K. Lowden > wrote: > > > IEEE > > double-precision floating point is accurate to within 15 decimal > > digits. > > First, the problem is not storage it's calculation. &g

Re: [sqlite] add constant to INTEGER PRIMARY KEY

On Thu, 13 Dec 2018 16:06:04 -0700 "Keith Medcalf" wrote: > You cannot do that. The PRIMARY KEY is required to be unique at each > "step" along the way, For the OP's benefit, this is longstanding, er, idiosyncrasy of SQLite. It does not conform to the SQL standard. The SQL rule is there

Re: [sqlite] Question about floating point

On Sat, 15 Dec 2018 01:24:18 -0800 Darren Duncan wrote: > If yours is a financial application then you should be using exact > numeric types only Color me skeptical. That very much depends on the application. IEEE double-precision floating point is accurate to within 15 decimal digits. The

Re: [sqlite] Question about floating point

On Sat, 15 Dec 2018 10:35:01 -0700 "Keith Medcalf" wrote: > And the propensity to apply intermediate rounding still exists. Yes. The only significant errors I've ever seen using floating point were not due to the computer, but to the programmer. --jkl

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

On Wed, 5 Dec 2018 05:21:30 + Simon Slavin wrote: > On 5 Dec 2018, at 5:16am, Ryan Schmidt wrote: > > > https://kb.vmware.com/s/article/1008542 > > > > "VMware ESX acknowledges a write or read to a guest operating > > system only after that write or read is acknowledged by the > >

Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)

On Fri, 30 Nov 2018 23:25:48 +0900 Simon Walter wrote: > > SELECT id, data FROM val WHERE id IN ("1, 893, 121212"); ... > I have no idea yet if MySQL and/or PostgreSQL can handle this > scenario and how they do it. The important thing to understand about parameterized queries is that they are

Re: [sqlite] Suitability for Macintosh OS 9.2?

On Mon, 19 Nov 2018 17:16:37 +0100 Dominique Devienne wrote: > Most of the SQLite code is platform agnostic, but at some point, it > must interface with the local filesystem on the local platform. While your first challenge will probably be to get a sufficiently modern C compiler for OS 9,

Re: [sqlite] forming sqlite3_statements using stored sql

On Fri, 2 Nov 2018 15:24:51 -0700 Jens Alfke wrote: > > On Nov 2, 2018, at 12:50 AM, Thomas Kurz > > wrote: > > > > My opinion is that the logic for database queries should be held > > together with the data. > > Why? The logic has to be in the application itself (where else would > it come

Re: [sqlite] forming sqlite3_statements using stored sql

On Thu, 1 Nov 2018 08:41:51 +0100 Clemens Ladisch wrote: > > It strikes me that this would be nicer if sqlite offered this as > > an intrinsic capability. > > How would SQLite know what the table and column names are? When the SQL is loaded, it can be parsed and analyzed. SQLite could, if

Re: [sqlite] Bug: float granularity breaking unique contraint?

On Thu, 1 Nov 2018 01:18:26 +0100 szmate1618 wrote: > But there seems to be an unintended > workaround > > DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL > UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO > TestReal values (9223372036854775807 - 1);INSERT INTO

Re: [sqlite] Question about a query

On Tue, 9 Oct 2018 10:22:12 -0700 Jens Alfke wrote: > You could implement a custom query function to do this (custom > functions are quite simple, and there are examples online). http://www.schemamania.org/sql/sqlite/udf/ Been there, done that. :-) --jkl

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On Sat, 6 Oct 2018 21:21:38 +0100 Simon Slavin wrote: > There is never any point in this process when a manager looks at > what's being done with Excel and says "Okay we need to hire a > programmer to turn this into a proper App.". Hmm, there is such a point. I used to do work like that, and

Re: [sqlite] Persistent snapshots and rollbacks

On Fri, 5 Oct 2018 17:39:57 +0200 Daniel Kraft wrote: > I need the ability to make multiple changes / commits to my SQLite > database but keep snapshots of previous states and potentially roll > back to those states later on. All of that needs to be persistent, > i.e. survive closing the

[sqlite] using a custom memory allocator in TH3

After stumbling on SQLite's description of its memory allocators, I decided to write a one for testing that might be slow but is certainly robust.[1] I thought it might be interesting to hook it up to TH3, but I don't understand how to do that. The instructions say: 1. Generate the code 2.

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

On Fri, 29 Sep 2017 16:55:05 -0400 Igor Korot wrote: > But then why not give it some default value ("0" maybe") and default > it to "1" only if needed during configure? Because complexity. It takes effort --- unnecessary effort -- to set up that default. That effort could

Re: [sqlite] Does prepare do arithmetic?

On Mon, 29 May 2017 16:18:17 +0200 R Smith wrote: > Actually what Richard is saying is that you SHOULD do the arithmetic > yourself when you can, because you can boldly make assumptions about > the code that you write, but he, or the SQLite engine to be precise, > cannot be

Re: [sqlite] NOT NULL integer primary key

On Fri, 26 May 2017 18:04:14 +0200 (CEST) Eric wrote: > Why should the INSERT return an error? It is quite OK to, when > inserting a row, not specify a value for a NOT NULL column - as long > as the DDL has specified some way of constructing a value. The SQL in question is >>>

  1   2   3   4   5   6   7   >