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

> On Mar 13, 2020, at 10:22 AM, Richard Hipp wrote: > > On 3/13/20, Huỳnh Trần Khanh wrote: >> [On a mailing nlist] I can >> filter the posts, sort them, search through them, archive them, >> forward them to a friend, > > You can do all of that with the SQLite Forum. Remember, all content >

Re: [sqlite] COALESCE() does not short-circuit sometimes

> On Mar 11, 2020, at 2:16 PM, Justin Ng wrote: > > They generally do short-circuit but there are edge cases where they don't. It > isn't entirely intuitive to me what the conditions are, though. > "ABS(-9223372036854775808)" is a constant expression, and as such, it makes sense that it is

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

I often describe it as “self contained.” -j Sent from my iPhone > On Jan 27, 2020, at 4:19 PM, Richard Hipp wrote: > > For many years I have described SQLite as being "serverless", as a way > to distinguish it from the more traditional client/server design of > RDBMSes. "Serverless"

Re: [sqlite] Rounding Error

> On Nov 4, 2019, at 2:59 AM, Jay Kreibich wrote: > > >> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin wrote: >> >> Hi, >> >> I would like to report the following as a bug in SQLITE: >> >> The SQLITE "round" function fails to ro

Re: [sqlite] Rounding Error

> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin wrote: > > Hi, > > I would like to report the following as a bug in SQLITE: > > The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers > correctly to x decimal places when held as x+1 decimal places. > > The simplest example I

Re: [sqlite] Lookup join

> On Oct 1, 2019, at 1:05 PM, Richard Hipp wrote: > Alas, SQLite's query planner is not perfect. ...files bug report... “lacking perfection.” ;-) -j ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Incorrect query result

Alas, the mailing list does not allow attachments. -j > On Aug 14, 2019, at 8:24 AM, Eric Boudaillier > wrote: > > Hi, > > I am experiencing incorrect query result with SQLite 3.25.2 and 3.28. > Attached are the database and a Tcl script running 3 queries. > The database and the queries

Re: [sqlite] A license plate of NULL

There are similar stories from many years back about someone that got the vanity plate “MISSING”. -j > On Aug 12, 2019, at 12:09 PM, Simon Slavin wrote: > > Some interesting things are emerging from this year's DEF CON. This one is > related to an issue we've often discussed here. I

Re: [sqlite] Question about floating point

> On Dec 17, 2018, at 1:12 PM, Keith Medcalf wrote: > > > The "nearest" representation of 211496.26 is 211496.260931323. The > two representable IEEE-754 double precision floating point numbers bounding > 211496.26 are: > > 211496.260931323 > 211496.25802094 > >

Re: [sqlite] Question about floating point

> On Dec 15, 2018, at 12:49 AM, Frank Millman wrote: > > Hi all > > I know that floating point is not precise and not suitable for financial > uses. Even so, I am curious about the following - > > SQLite version 3.26.0 2018-12-01 12:34:55 > Enter ".help" for usage hints. > Connected to a

Re: [sqlite] Strange query results.

> On Nov 29, 2018, at 7:45 AM, Maurice van der Stee wrote: > > This reproduces the issue for me: > > create table config (config_package integer, config_flags integer); > insert into config (config_package, config_flags) values (1, 2); > insert into config (config_package, config_flags) values

Re: [sqlite] Creating and dropping tables with multiple connections

> On Nov 20, 2018, at 10:27 AM, Simon Slavin wrote: > On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz > wrote: > >> ExecuteInTransaction(writeDb1, KCreateTable); > > I can't answer your question but the above line shows a misunderstanding of > SQL. Transactions are for commands which

Re: [sqlite] HELP!

> On Nov 11, 2018, at 1:24 AM, Clemens Ladisch wrote: > > It's not; SQLite is file based. The only way to share this would be to > make a file share in the company-wide network, i.e., to make the file > \\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from > everywhere. (This

Re: [sqlite] minor nit pick of "When To Use"

> On Nov 11, 2018, at 7:38 AM, Dennis Clarke wrote: > > On 11/11/18 8:25 AM, J. King wrote: >> On November 11, 2018 8:04:51 AM EST, Dennis Clarke >> wrote: >>> >>> this : https://www.sqlite.org/whentouse.html > >> he.net is Hurricane Electric, an Internet backbone. > > An IX ? HE is not

Re: [sqlite] clarification of the .binary option of the sqlite3 command shell program.

> On Oct 19, 2018, at 3:30 AM, Graham Hardman wrote: > > Hi, > > I am curious about what this option does. It is not discussed in the > documentation and my own testing with the shell program has not aided my > understanding. A simple example would be nice. It controls how data is output

Re: [sqlite] CAST AS STRING always returns 0 for STRING columns

> On Sep 4, 2018, at 9:30 AM, Ben Caine wrote: > > CAST AS STRING always returns 0 for columns that are already of STRING type. “STRING” is not a known type affinity. Use “TEXT” https://www.sqlite.org/lang_expr.html#castexpr > > Steps to

Re: [sqlite] How to find records in a table which are not in a second table?

> On Aug 28, 2018, at 1:22 PM, David Raymond wrote: > > Embarrassing confession time: I didn't think you could use "using" to do this > while selecting "a.*" > > https://www.sqlite.org/lang_select.html > "For each pair of columns identified by a USING clause, the column from the > right-hand

Re: [sqlite] How to find records in a table which are not in a second table?

> On Aug 28, 2018, at 11:30 AM, Joe wrote: > > A (perhaps silly ) beginners question: > My sqlite database contains several tables, two of them, table A and table B, > have text colums called 'nam'. The tables have about 2 millions lines. > What's the most efficient way to select all lines

Re: [sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...

> On Jul 16, 2018, at 3:01 PM, rob.sql...@robertwillett.com wrote: > > Hi, > > We're doing a massive tidy on our database which is approx 50GB. > > One table is approx 49GB of that 50GB which we need to delete as we have > recorded the information in a far better format thats approx 99% more

Re: [sqlite] Return a column's contents to the free list

> On Jun 9, 2018, at 10:16 AM, Ben Asher wrote: > > To further clarify on VACUUM, we actually want to actively avoid this > because it's expensive, and we'd be running these on small devices like > iPhones and iPads with large-ish DBs. If a full VACUUM is not feasible, you can simply copy the

Re: [sqlite] Return a column's contents to the free list

hole row record is deleted and it tends to free up bigger chunks. But clearing the data out of a column only clears values in the middle of row records, so it is unlikely to free up pages by itself. -j > Ben > > On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich wrote: > >> &g

Re: [sqlite] Return a column's contents to the free list

> On Jun 9, 2018, at 9:52 AM, Ben Asher wrote: > > Hi! I've read a lot of discussion about the constraints related to why > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, > etc.). Despite that, we still have situations where our data model changes, > and a column becomes

Re: [sqlite] random rows

I’m not entirely sure your solution will have an even distribution. It depends a lot on how many times random() is called (once per row vs once per sort operation), and how the sort algorithm works. I might do this instead: SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200;

Re: [sqlite] Resources for learning SQLite

> On Mar 29, 2018, at 12:06 PM, Mike Clark wrote: > > I suspect there are already threads on this, so apologies for the potential > duplicate... > > I'm a long-time C# developer who has used Sql Server for decades, but I'm > just getting started with SQLite. Does

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Pretty much every table of every database, with the obvious exceptions like virtual tables. -j > On Mar 16, 2018, at 10:37 AM, Richard Hipp wrote: > > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use

Re: [sqlite] Indexing multiple values per row

> On Mar 15, 2018, at 12:33 PM, Jens Alfke wrote: > > I'm wondering what the best way is to efficiently search for data values that > can appear multiple times in a table row. SQLite indexes, even expression > indexes, don't directly work for this because they obviously

Re: [sqlite] Is it a bug or "as designed"?

> On Mar 9, 2018, at 1:42 PM, Simon Slavin wrote: > > "replace" means "delete the original row, then insert a new one”. More properly, it means “delete any and all rows that might cause any conflict with inserting the new row.” There really isn’t a concept of an

Re: [sqlite] Is it a bug or "as designed"?

Foreign keys enforcement can get tricky depending on the enforcement policy, transactions, and a lot of things. I don’t have enough experience to comment on that fully. I will say this, however, because it is a common mistake with a lot of different aspects of database behavior:

Re: [sqlite] Move to Github!!?

> On Dec 25, 2017, at 12:24 PM, J. King wrote: > > SQLite source is managed in a Fossil (not Git) repository, which is software > itself designed by Dr. Hipp and based on SQLite. GitHub would be an entirely > inappropriate venue. > > SQLite is also not open source

Re: [sqlite] Why Unicode is difficult

0.00 vs whatever. I thought you meant how to represent 0.1 And the fact there are so many interpretations of “number representation” aught to give a clue about how complex something “so simple” can be. -j > >> >> On Mon, Dec 4, 2017 at 11:07 AM, Igor Korot <ikoro...@g

Re: [sqlite] Why Unicode is difficult

Next, we can talk about how dates and times are simple and straight-forward. -j > On Dec 4, 2017, at 7:08 AM, Simon Slavin wrote: > > Every so often someone asks on this list for Unicode to be handled properly. > I did it myself. Then other people have to explain

Re: [sqlite] Article on AUTOINC vs. UUIDs

There are some minor points, but I agree that it basically boils down to “serial IDs break security-by-obscurity.” That’s true, but…. -j > On Nov 30, 2017, at 9:00 AM, Keith Medcalf wrote: > > > Well, in my opinion the guy is an idiot. The way to avoid the issues

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

On Aug 18, 2017, at 7:37 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > Jay Kreibich wrote: >> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch <clem...@ladisch.de> wrote: >>> sanhua.zh wrote: >>>> 1. Conn A: Open, PRAGMA journal_mode=WAL >>>&g

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

On Aug 18, 2017, at 4:04 AM, sanhua.zh wrote: > I am using SQLite in multi-thread mode, which means that different threads > using different SQLite connection. > And now I find an issue that the results of SQLite C interface returned is > expired while the schema of

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

On Aug 18, 2017, at 5:33 AM, Clemens Ladisch wrote: > sanhua.zh wrote: >> 1. Conn A: Open, PRAGMA journal_mode=WAL >> 2.ConnB: Open, PRAGMA journal_mode=WAL >> 3.ConnA: CREATE TABLE sample (i INTEGER); >> 4.ConnB: PRAGMA table_info('sample') >> >> Firstly, both thread 1 and

Re: [sqlite] group_concat() reverses order given where clause?

On Aug 15, 2017, at 10:39 AM, Bob Friesenhahn wrote: > On Tue, 15 Aug 2017, Dan Kennedy wrote: > >> On 08/15/2017 10:12 PM, Bob Friesenhahn wrote: >>> select group_concat(name, ' ') AS 'names' from moca_config where enable == >>> 1 order by name; >> >> Maybe

Re: [sqlite] (no subject)

I’d look at creating a virtual table that can be setup to “shadow” any existing table. Basically pass-through any read or write operations, possibly shunting off writes. This has the advantage of not requiring any kind of patching or modifications to the core library. Sounds a lot simpler

Re: [sqlite] How does one block a reader connection?

On Mar 25, 2017, at 5:52 PM, petern wrote: > So finally, here is the question. Is there a SQLite API way for reader > connections to block and wait for a meaningful change, like a new row, in > the 'cmd' table instead of madly polling and using up database

Re: [sqlite] More built-in functions for basic math

The main downside is that SQLite builds on a ton of platforms, including embedded devices. In some cases, those platforms don’t even support floating point numbers, never mind high-level math functions. It would add a mess of new #defs. There used to be a standard math extension that

[sqlite] Simple web query tool

I'm looking for an *extremely* simple web tool that will allow me to configure a dozen or so stored queries, which people can then select and run on an internal server. If the system supports a query variable or two, that would be fantastic, but I don't even need that. Any thoughts? Or do I

Re: [sqlite] multiple processes working on one database file

On Wed, Oct 5, 2016 at 11:53 AM, Simon Slavin wrote: > > On 5 Oct 2016, at 4:30pm, Jens Alfke wrote: > > > I did find that SQLite’s own locking was less effective/fair than using > a mutex, and in situations with a lot of write contention could lead to

Re: [sqlite] Order of fields for insert

All I/O is done via page sized blocks. So the minimum amount of data to be fetched will always be a page. The bigger issue is, as you said, when you need to follow a chain of pages to get a small value at the end. -j On Thu, Oct 6, 2016 at 9:53 AM, Paul Sanderson

Re: [sqlite] Order of fields for insert

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter wrote: > SQLite compresses rows before storing and decompresses rows before > returning fields. BLOB fields are the most time consuming to process and so > should be placed at the end of the row. Often used fields - i.e. (foreign) >

Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

What was the size of the original database? To VACUUM a database, the process is: 1) Read the logical components of the database, write them to a new file. This will generate at least 1x reads (original size), and 1x writes (final size). In most cases the reads will be larger and the

Re: [sqlite] Exec vs Prepare, step, finalize.

On Aug 11, 2016, at 12:53 AM, Michael Falconer wrote: > Thanks Jay, > > excellent response. I'll ask for clarity on one statement though. > > That’s the basic theory, but even knowing that, most people get it wrong. >> In short, if you’re using string

Re: [sqlite] Exec vs Prepare, step, finalize.

On Aug 10, 2016, at 9:21 PM, Michael Falconer wrote: > Hi all, > > just seeking some opinions, and perhaps some dev indications about > deprecation, in relation to the sqlite3_exec facility. I kind of like the > callback functionality in certain cases as it is

Re: [sqlite] SQLite Logo

The current SQLite logo is several years old. The Apache Foundation's vertical feather logo is very new (less than a year, IIRC). Perviously the ASF logo was a horizontal feather. So the correct question is, "Why is the Apache logo basically the same as the SQLite logo?" -j On Tue, Jul 5,

[sqlite] Multiple in-memory database table query

On Apr 20, 2016, at 8:10 AM, Dominique Devienne wrote: > Thanks. I mistakenly assumes ":memory:" was a "singleton" memory DB > for that particular connection. Thanks to your example, and a little testing > on my own, I now realize > each one is an independent memory DB, and not just different

[sqlite] Multiple in-memory database table query

On Apr 20, 2016, at 3:22 AM, Dominique Devienne wrote: > On Wed, Apr 20, 2016 at 4:40 AM, Jay Kreibich wrote: >> >> On Apr 19, 2016, at 1:34 PM, Jarred Ford wrote: >> >>> Is it possible to create multiple in-memory databases and be able to >> access tab

[sqlite] Multiple in-memory database table query

On Apr 19, 2016, at 1:34 PM, Jarred Ford wrote: > Is it possible to create multiple in-memory databases and be able to access > tables with a single query between them? > For example, select * from db1.dbo.table1 db1 join db2.dbo.table1 db2 on > db1.x = db2.x. > Sure. Like any other

[sqlite] Working with booleans

On Apr 14, 2016, at 12:42 PM, Cecil Westerhof wrote: > When working with booleans at the moment I use: >isActive INTEGER NOT NULL CHECK(isActive in (0, 1)) > > Is this a good way, or would be using a CHAR with a check op 'T', or 'F' be > better? > Integer 1 and 0 will be the most

[sqlite] Last time analyze was ran

On Apr 12, 2016, at 2:38 AM, Simon Slavin wrote: > > On 12 Apr 2016, at 2:56am, Jose I. Cabrera wrote: > >> Maybe this should be something to think about, and perhaps add it as part of >> the results or reported items of .schema. Also, only update the date if >> completion successful.

[sqlite] In-Memory DB cache_size

An in-memory DB is held 100% in the cache (there is no backing store), so I believe it ignores this number and is allowed to grow up to the maximum DB size. The cache does not hold duplicate copies of pages held else-where in memory. -j On Mar 17, 2016, at 8:17 AM, Michele Pradella

[sqlite] SQLite Pronunciation

IIRC, the SQL ISO standard defines the pronunciation of the term ?SQL? as "ess-cue-ell?, although if you trace SQL back to the IBM days of SEQUEL, there is a strong argument that the term ?sequel? makes more sense. I know when the SQLite development team speaks about it, they tend to use the

[sqlite] Recover corrupt database

The backup API is not designed to recover corrupted databases. Generally speaking, there is no standard way to recover from a corrupt database. The best policy is not to corrupt it in the first place. In practice, if you follow the rules, it is very very hard to corrupt a database. Just be

[sqlite] website documentation wording

I?d rather be a dinosaur with a clue, then buzzword compliant whipper-snapper. The amusing thing is if you?re really been doing databases since the early 1970s, you likely do call it ?sequel,? since that is what IBM actually called the first relational database language (SEQUEL - Structured

[sqlite] website documentation wording

It is actually in the ISO standard that the proper pronunciation is ?ess cue ell?. It became ?sequel? in some circles, mostly thanks to Microsoft. The ?Using SQLite? O?Reilly book also uses ?an? (e.g. ?an SQL statement?) for the same reasons. -j On Dec 4, 2015, at 9:53 AM, Don V Nielsen

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

On Nov 10, 2015, at 6:38 PM, Richard Hipp wrote: > On 11/10/15, Jay Kreibich wrote: >> >> I want to be a Certified SQLite Professional. >> > > The inventors and developers of the SQLite database engine to all to > whom these presents may come, Greetings:

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

On Nov 11, 2015, at 3:25 AM, Simon Slavin wrote: > There's a story behind this. > > During the review leading to HTML5 the web consortium (the one which decides > which features should be introduced to HTML/CSS/JavaScript) listed a > 'webdatabase' specification so that browsers could

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

> On Nov 10, 2015, at 2:45 PM, Warren Young wrote: > >> On Nov 10, 2015, at 1:29 PM, Rousselot, Richard A > centurylink.com> wrote: >> >> What no love for their own Access DB? > > This from the same company that gave us ODBC, ESQL, OLE DB, MDAC/Jet, DAO, > RDO, ADO, ADO.NET, ADO Entity

[sqlite] Simple Math Question

On Oct 23, 2015, at 10:36 AM, Scott Hess wrote: > > Indeed, and the cost was the need to have two completely independent math > systems, one precise and one fast. For obvious reasons over time people > who did a lot of math just figured out how to make the fast one precise > enough for their

[sqlite] Simple Math Question

On Oct 23, 2015, at 7:41 AM, Jean-Christophe Deschamps wrote: > AFAICT system 360 machines had BCD in microcode, just like any other basic > datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes". > The Motorola 680x0 chips (used in the original Macintosh systems) also had

[sqlite] busy_timeout() not working always?

The sqlite3_busy_timeout() function is just a wrapper for sqlite3_busy_handler(). As explained at http://www.sqlite.org/c3ref/busy_handler.html, the use of a busy handler does not avoid all SQLITE_BUSY situations: The presence of a busy handler does not guarantee that it will be invoked when

[sqlite] This mailing list seems to be public

On Jun 15, 2015, at 6:42 AM, wrote: > I was under the impression that this mailing list was restricted to members. > However: > > http://sqlite.1065341.n5.nabble.com/Under-what-circumstances-can-a-table-be- > locked-when-the-database-is-first-opened-td82371.html >

[sqlite] "=" should not be a word character

On Mar 14, 2015, at 6:45 PM, Richard Hipp wrote: > On 3/14/15, Skip Montanaro wrote: >>> >> Then I retrieved that statement with Ctl-P, then clear the id with Ctl-B >> ESC-Del. I then entered the id of the next record to correct, backed up and >> adjusted the date, and hit enter. >> >>

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On Mar 5, 2015, at 12:30 PM, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically:

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

> > On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich wrote: >> >> On Mar 1, 2015, at 5:33 PM, Richard Hipp wrote: >> >>> On 3/1/15, Paolo Bolzoni wrote: >>>> Dear everyone, >>>> >>>> I find strange and confusing that bi

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

On Mar 1, 2015, at 5:33 PM, Richard Hipp wrote: > On 3/1/15, Paolo Bolzoni wrote: >> Dear everyone, >> >> I find strange and confusing that bind indexes start from 1 (docs in >> [1]) and instead column indexes start from 0 (doc in [2]). Is there >> any technical reason or it is just an

[sqlite] read uncommitted data consistency

On Feb 21, 2015, at 5:36 PM, Simon Slavin wrote: > > On 21 Feb 2015, at 9:01pm, Yuriy Stelmakh wrote: > >> When using read uncommitted pragma, is it possible to get a row of data >> where some columns reflect state of that row at one point, while others at >> another? For example when you

[sqlite] Appropriate Uses For SQLite

On Feb 18, 2015, at 3:13 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 02/18/2015 11:43 AM, Richard Hipp wrote: >> but I think the truth is we are probably stuck with SQL for a while >> yet. > > In theory there could be an intermediate representation form

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

On Feb 10, 2015, at 11:21 AM, Peter Aronson wrote: > You could add VFS creation if you ever do a revised edition (along with a > virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

> On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian <julian.ja...@capgemini.com> > wrote: > >> So my question is, if there is any additional information, how to write a >> VFS? >> Or does anyone have a clue how to start best? >> > > This book: > &g

Re: [sqlite] TEMP tables with the same name as of main tables

On Jan 23, 2015, at 9:35 AM, Aldo Buratti wrote: > I had a bad programming experience with temporary tables and after some > googling I found this old post > > [sqlite] How to select from a temp table with same name as a main table. > dochsm Tue, 18 Jun 2013

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Jan 16, 2015, at 6:56 AM, Richard Hipp wrote: > On 1/16/15, Scott Robison wrote: >> LIKE & GLOB can be overridden with user defined functions. According to >> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument >> function, GLOB

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Jan 16, 2015, at 5:06 AM, Scott Robison wrote: > LIKE & GLOB can be overridden with user defined functions. According to > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument > function, GLOB can be a 2 argument function, and neither MATCH nor

Re: [sqlite] Error: database disk image is malformed

On Jan 4, 2015, at 3:31 AM, Neo Anderson wrote: > I have a customer database which appears corrupt. When I run sqlite3 mydb and > PRAGMA integrity_check I got (literally, no other lines): > > Error: database disk image is malformed > > However, I can .dump and .read

Re: [sqlite] VACUUM requires 6.7 times space ?

On Jan 5, 2015, at 5:03 PM, Eduardo Morras <emorr...@yahoo.es> wrote: > On Mon, 5 Jan 2015 14:42:28 -0600 > Jay Kreibich <j...@kreibi.ch> wrote: > >> >> >> On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson <pri...@gmail.com> wrote: >> >>>

Re: [sqlite] VACUUM requires 6.7 times space ?

On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson wrote: > Simon - instead of using vacuum, it's much faster to create a new > database from the old one, then rename it. It's easy to do this in > Python using iterdump(), or you can connect to the new (empty) > database, do your

Re: [sqlite] VACUUM requires 6.7 times space ?

On Jan 5, 2015, at 8:43 AM, Nelson, Erik - 2 wrote: > RSmith wrote on Monday, January 05, 2015 7:43 AM >> >> >>> On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an

Re: [sqlite] Virtual table implementation details

You seem to be forgetting one important detail… if a Rollback is called, the underlying data and undo tables will be rolled back automatically. They are, after all “real” tables in the database, and will be managed as such. Unless you’re storing data outside the SQLite database, there is

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

When the command line and an application do different things, it is usually a versioning issue. I’d verify what version of the SQLite library your Python application is using. My guess is something older, possibly with a bug or edge-case in the way it handles aliasing of views. -j On

Re: [sqlite] Bug in division?

On May 6, 2014, at 5:26 PM, Gene Connor wrote: > Subject: Re: [sqlite] Bug in division? > From: j...@kreibi.ch > Date: Tue, 6 May 2014 17:02:02 -0500 > CC: neothreeei...@hotmail.com > To: sqlite-users@sqlite.org > > > The system does not return 0 any time you divide

Re: [sqlite] Bug in division?

On May 6, 2014, at 4:29 PM, John Drescher wrote: >> Interesting. It makes NO sense to return 0 when dividing two integers. >> > > Never took a C/C++ class? The system does not return 0 any time you divide two integers, but it does return zero for 2 / 4. After all,

Re: [sqlite] select 1 where 1 - 1;

On May 5, 2014, at 6:16 PM, Scott Robison <sc...@casaderobison.com> wrote: > On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich <j...@kreibi.ch> wrote: > >> So far this whole discussion seems to boiled down to the fact that SQLite >> doesn’t have a native Boolean typ

Re: [sqlite] select 1 where 1 - 1;

On May 5, 2014, at 3:03 PM, Petite Abeille wrote: > > On May 5, 2014, at 9:15 PM, RSmith wrote: > >> Je suis desole mon ami… > > Moi aussi :P > > I have no quarrel with you, good Sir Knight, but I must cross this bridge: > > select 1 where 1

Re: [sqlite] select 1 where 1 - 1;

The WHERE clause takes only one parameter. WHERE expects an expression, which evaluates down to one value. That expression does not need to be a comparison. It commonly is, but it can be anything, such as a CASE expression, an EXISTS subselect, or a function. As far as SQLite cares, all

Re: [sqlite] select 1 where 1 - 1;

On May 2, 2014, at 10:07 PM, Keith Medcalf wrote: > >> On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > >>> I'm guessing that Mr. Abeille is upset that SQLite ... > >> ... doesn't even bother with SQL syntax and will happily accept any old >> junk as a

Re: [sqlite] Bug in division?

On Apr 30, 2014, at 1:00 PM, Petite Abeille wrote: > > On Apr 30, 2014, at 2:22 PM, John McKown wrote: > >> PostgreSQL likewise returns 0 for 2/4 and .5 for 2/4.0 . This is likely a >> part of the SQL standard. > > Just to be

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On Feb 10, 2014, at 10:20 AM, Jay Kreibich <j...@kreibi.ch> wrote: > On Feb 10, 2014, at 10:15 AM, Richard Hipp <d...@sqlite.org> wrote: > >> What if, instead of opening a standard database, the sqlite3.exe >> command-line shell just issued a warn

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On Feb 10, 2014, at 10:15 AM, Richard Hipp wrote: > What if, instead of opening a standard database, the sqlite3.exe > command-line shell just issued a warning message reminding the user that > they are working on a transient in-memory database and suggesting the use > of the

Re: [sqlite] pragmas in subselects?

Chapter 10 of Using SQLite covers virtual tables. One of the examples given shows how to wrap a PRAGMA statement, so it can be used as system catalog and used in normal SELECT statements. It would be pretty easy to expand the given example to cover almost any SQL statement (including any

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

On Jan 7, 2014, at 4:13 AM, Woody Wu wrote: > Thanks for the clear guide. _busy_timeout is easier to use. By the way, i > want confirm that if i am not in an explicit transaction, i can simply redo > the _step() invoking, right? No. You executed a statement.

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

Yes, of course. Sorry. It would seem I shouldn't answer email that early in the morning on a day off. -j On Dec 31, 2013, at 7:48 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Tue, Dec 31, 2013 at 2:33 PM, Jay Kreibich <j...@kreibi.ch> wrote: > >>

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

On Dec 30, 2013, at 8:48 PM, Bob Cochran <bcochra...@verizon.net> wrote: > I'm working on a tough project that requires me to use Node.js and SQLite. > I'm building a database with SQLite version 3.8.2. > > My question is, would purchasing the book "Using Sqlite&qu

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 3:36 PM, Darren Duncan wrote: > Petite Abeille wrote: >> On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: >>> ALTER TABLE DROP COLUMN requires writing every row of the table. >> Could be a 'logical delete' instead. In other words, a

Re: [sqlite] "inaccuracy" in code/docu of sqlite3_finalize

Sent from my iPhone On Oct 13, 2011, at 12:45 AM, Wolfgang Stöcher wrote: > From the docu of sqlite3_finalize: > "The application must finalize every prepared statement [1] in order > to avoid resource leaks." > Ok, fine. But the given reason(s) seem not to be

Re: [sqlite] PRAGMA user_version

On Sep 3, 2011, at 8:21 PM, Walter wrote: > > Thank you Igor I had the database name but did not think of the the dot in > between. Perhaps some one could update the documentation to show this Cheers > everyone Walter Please see "PRAGMA Command Syntax"

Re: [sqlite] Question about database design

On Feb 3, 2011, at 3:38 AM, Simon Slavin wrote: > SQLite creates some indexes the programmer doesn't specifically ask for: on > the rowid, on the primary key, and on any column declared as UNIQUE. Of > course, in a particular table all three of these might actually be