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

0 I roll my own. > On Mar 16, 2018, at 4:37 PM, 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 AUTOINCREMENT? > > I just need a single integer, the count of uses of the

Re: [sqlite] How to store as integer

How it's stored depends on how the messages table is defined (which type the message column has been given), which you haven't shown, and whether storeMessage quotes the message argument when forming the string. My advice is to remove any column type and make sure numbers are not quoted when they

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

What about invalid and reused MAC addresses and devices with no MAC address at all? What about time resets to the epoch which are not restored, user time changes, daylight saving or leap seconds? It sounds even more probabilistic than the probabilistic methods. Does anyone actually use it? >

Re: [sqlite] How to create primary key from two another PK's?

You don't, that's not how relational databases work. You need to create a separate field for each foreign key (student and workpiecelist) and together they form the primary key for the uniqueworkpc table. See David's reply for details. > On Oct 20, 2017, at 9:56 PM, csanyipal

Re: [sqlite] vfs question: how to pass binary/blob arg to xCreate()?

You could try Unicode binary encoding to increase the efficiency when passing binary as strings: https://qntm.org/unicodings > On Oct 20, 2017, at 7:20 PM, Liam Staskawicz wrote: > > Hi, > > I'm just getting started exploring the vfs capabilities

Re: [sqlite] Grouping and grabbing one item

select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; > On Oct 18, 2017, at 7:23 PM, jose isaias cabrera > wrote: > > > CREATE TABLE Tasks ( > id INTEGER PRIMARY KEY, > Pid

Re: [sqlite] PRAGMA optimize; == no busy handler?

In the case of a database that ran hot all the time a better strategy would be to have an online backup of the database running at all time and then run analyze on that periodically, then update the stats table in the live database when that was finished. I'm not sure what the rules are for

Re: [sqlite] XOR operator

> However, '&' and '|' and '<<' and '>>' do not seem to be defined, either > there or in Almost none of the operators are defined. All that section needs is a sentence at the start saying "The operators have the same meaning as ANSI C except as described below." > On Oct 6, 2017, at 11:31

Re: [sqlite] ADO.NET driver upgrade puzzle

If you're accessing a database concurrently from different processes then you may want to look at this: https://sqlite.org/faq.html#q5 as your file system may be causing you problems. > On Sep 25, 2017, at 11:09 AM, Roberts, Barry (FINTL)

Re: [sqlite] ADO.NET driver upgrade puzzle

That's not strictly true, it depends on the threading mode: https://sqlite.org/threadsafe.html It sounds like the driver is trying to manage concurrency on its own and failing. For the purposes of calling sqlite, if the library is used in the default

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

If you're looking for a simple/easy/clean way of doing it, there isn't one. You have to modify the library to do it properly. But I still find it an interesting design challenge. Maybe instead of going the eager route you can go lazy and just cache subexpressions which might be called again.

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

) will be guaranteed to be evaluated before any other in a particular statement? > On Sep 14, 2017, at 4:13 PM, Richard Hipp <d...@sqlite.org> wrote: > > On 9/14/17, Darko Volaric <li...@darko.org> wrote: >> I think people are missing the point, probably becuase

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

I think people are missing the point, probably becuase it's not a great example. Consider the following statement: SELECT funca(slow(10)), funkb(slow(10)) and lets say slow(10) takes an hour to compute, and funka and funkb take almost no time to execute. With common subexpression optimization

Re: [sqlite] SQLite list spam from raypoker79

Is it possible that the spammer got a hold of the subscriber list? > On Sep 13, 2017, at 1:55 AM, Richard Hipp wrote: > > Thanks for reporting this, Darren. I didn't see it because those > emails went straight into my spam folder. > > I don't know how they got through,

Re: [sqlite] Common subexpression optimization of deterministic functions

12, 2017, at 11:27 PM, Jens Alfke <j...@mooseyard.com> wrote: > > >> On Sep 12, 2017, at 1:41 PM, Darko Volaric <li...@darko.org >> <mailto:li...@darko.org>> wrote: >> >> You can implement this by using user defined functions to implement row

Re: [sqlite] Common subexpression optimization of deterministic functions

You can implement this by using user defined functions to implement row "local variables" or "registers". They're single assignment storage that keeps intermediate results, namely the common subexpressions. You'd define two functions, something like Get(rowid, name) and Set(rowid, name,

Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

If you're preparing that statement more than once then you are wasting time, there's no reason whatsoever to do it. You're also wasting time if you make a bind call to set any column that hasn't changed since the last time you executed the statement. The entire row is rewritten when updating

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

Maybe you misunderstood that sentence: "auto_vacuum=FULL" mode will reduce the file size but so will a "VACUUM" command, independent of the auto_vacuum setting. It makes that crystal clear by detailing how it works later on that page: > The VACUUM command works by copying the contents of the

Re: [sqlite] hex and char functions

Actually a maximum of 4 bytes are required to encode a single valid code-point in UTF-8. > On Aug 8, 2017, at 2:44 AM, Jens Alfke wrote: > > >> On Aug 7, 2017, at 8:29 AM, x wrote: >> >> I thought I had learned enough about this string lunacy to

Re: [sqlite] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

stuff in persisted hidden column bits works > against the "correct by inspection" objective of good design. > > > > > > On Sun, Jul 23, 2017 at 9:13 PM, Darko Volaric <li...@darko.org> wrote: > >> You can't have sqlite3_bind_subtype() because i

Re: [sqlite] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

You can't have sqlite3_bind_subtype() because it would require a change to the database file format - there would be no real performance penalty besides increased record size. There is no existing place to store a per-value subtype in the file format as it stands, although I did have a design

Re: [sqlite] Article: UUID or GUID as Primary Keys? Be Careful!

And between centrally assigned "consecutive integer keys" and elaborate, probabilistic UUIDs are centrally allocated number ranges of a natively supported integer, eg 64 bit on SQLite. Problem solved. > On Jun 10, 2017, at 6:27 AM, Jens Alfke wrote: > > >> On Jun 9,

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

If you really wanted to have stored procedures and did not mind calling them using a function syntax, you could write your own stored procedure extension. You'd store them in their own table, write a custom function that evaluates them and call them something like this: sp("name", param1,

Re: [sqlite] Compressed schema in memory?

Is it time to break out an API for schema lookup? That would seem to be the least work for the developers and would give people the chance to implement whatever strategy they need to manage large schemas, including storing them in the database in a structured manager, or a compressed in-memory

Re: [sqlite] Incremental BLOB IO

I haven't got an example but how about inserting the record and then updating the blob in question? On Wed, Mar 15, 2017 at 12:33 AM, Mike King wrote: > Hi, > > I'm trying to understand incremental BLOB IO using the latest > System.Data.Sqlite and C#. > > I've got some

Re: [sqlite] Why is this so much more efficient?

You can actually index functions or expression: https://www.sqlite.org/expridx.html On Thu, Feb 16, 2017 at 9:32 PM, Cecil Westerhof wrote: > 2017-02-16 21:10 GMT+01:00 Dominique Pellé : > > > Cecil Westerhof wrote: > >

Re: [sqlite] Storing a INTEGER in a TEXT field

The problem is that you're giving your column a type when you don't want it to have. If the second last line was "message NOT NULL" you'd get exactly what you're asking for. On Wed, Feb 15, 2017 at 1:22 AM, Cecil Westerhof wrote: > I have the following table: > CREATE

Re: [sqlite] View column data type affinity?

Yes, by giving the column a type, that determines its affinity. On Sun, Jan 15, 2017 at 8:08 PM, Joe Seeley wrote: > Is there a way when creating a view to specify the data type affinity for > each column? > ___ > sqlite-users

Re: [sqlite] Why this query plan?

Actually all that would happen is a massive number of hidden bugs would be revealed. He would be doing the world a favour. On Thu, Jan 12, 2017 at 5:13 PM, Jean-Christophe Deschamps wrote: > At 15:13 12/01/2017, you wrote: > >> Re: "I read this as a provocative joke." >> >>

Re: [sqlite] Why this query plan?

Your example is entirely wrong. Spreadsheet apps explicitly define the behavior, and provide functionality, for defaulting the attributes for unused cells. A better example is this: looking at your paper mail and asking "why didn't mail posted on the same day from the same sender arrive on the

Re: [sqlite] SQLite 3.16.0 enters testing

What are you basing that theory on? On Thu, Dec 29, 2016 at 10:29 PM, Dominique Pellé wrote: > Richard Hipp wrote: > > > On 12/29/16, Bob Friesenhahn wrote: > >> Is there a way to know how well cachegrind CPU > >>

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

Or use a collation instead, although "collate" is an operator it's not treated as a function: select 'abc' n union select 'ABC' n order by n collate nocase On Tue, Dec 27, 2016 at 1:34 AM, Jean-Christophe Deschamps wrote: > At 00:45 27/12/2016, you wrote: > > The work

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

It's not a bug, it's a documented restriction, see the last point below. The work arounds is using a WITH clause or putting the upper function expression in the output of each select. From http://www.sqlite.org/lang_select.html : Each ORDER BY expression is processed as follows: 1. If

[sqlite] Missing source code in 3.14 release

Though clearly a man committed to open source, it's a bit shocking that Dr Hipp has not released the source code for that pie. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Third test of json and index expressions, now it works

Slavin wrote: > > On 5 Sep 2015, at 7:07pm, Darko Volaric wrote: > > > That's not a valid reason since it's trivial for SQLite to transform > > aliases by substituting their names with their definitions. It could be > > handled in the parser code. > > An

[sqlite] Third test of json and index expressions, now it works

? What are the motivations for not having it? On Sat, Sep 5, 2015 at 9:23 AM, Simon Slavin wrote: > > On 5 Sep 2015, at 5:07pm, Darko Volaric wrote: > > > That's not what I said, why don't you read/quote the whole sentence: > > > > "Besides being part of the standard

[sqlite] Third test of json and index expressions, now it works

Sat, Sep 5, 2015 at 8:49 AM, Simon Slavin wrote: > > On 5 Sep 2015, at 3:19pm, Darko Volaric wrote: > > > Besides being part of the standard (I assume) > > You assume incorrectly. In the classic SQL model, aliases to column names > are assigned after the results have been

[sqlite] Third test of json and index expressions, now it works

Besides being part of the standard (I assume), what's the rationale for this restriction? It would seem that standard SQL is being willfully less efficient and more error prone by making the user rewrite expressions. Isn't this in the same category as manifest typing, where a more liberal

[sqlite] Password protection to sqlite3 db file

Yes. There are free and paid options for this. For instance: https://www.sqlite.org/see/doc/trunk/www/index.wiki https://www.zetetic.net/sqlcipher/ On Wed, Sep 2, 2015 at 9:36 AM, techi eth wrote: > Hi, > > Is it possible to protect sqlite3 db file with password protection in > Linux. > >

[sqlite] why I don't get an error ?

SQLite records have fields that are variable sized and encode type and length information for each field and no table constraint changes this. The table constraints only change how some values are interpreted. On Thu, Aug 27, 2015 at 7:06 AM, Nicolas J?ger wrote: > Hi Darko, Igor and others. >

[sqlite] why I don't get an error ?

Columns do not have a fixed type and will accept any type. It's not a bug, it's a feature: http://sqlite.org/datatype3.html On Wed, Aug 26, 2015 at 8:51 PM, Nicolas J?ger wrote: > Hi, > I have a table built by: > > CREATE TABLE IF NOT EXISTS TAGS (ID INTEGER PRIMARY KEY AUTOINCREMENT, > NAME

[sqlite] Lua inside SQLite

Wow, what a great feature. This saves me so much hacking! I planned on using virtual tables but per-query instance creation and parameter passing was going to be such a mess. On Sun, Aug 23, 2015 at 1:55 PM, Richard Hipp wrote: > On 8/23/15, Abilio Marques wrote: > > > > 1. sqlite3 CLI

[sqlite] Tables and Columns of Database of Whatsapp

It should be kept in mind that lawyers routinely put things in these "click-through agreements" that is unenforceable or illegal. It may be perfectly legal to reverse engineer, for example: ( from https://www.eff.org/issues/coders/reverse-engineering-faq ) "Courts have found that reverse

[sqlite] Will SQLite break a join query?

You can get SQLite to show exactly what it's doing using "explain", see http://sqlite.org/opcode.html On Thu, Jun 25, 2015 at 10:16 PM, Sairam Gaddam wrote: > I know that SQLite will perform some internal select statements when > executing a join query but will it break a join query and execute

[sqlite] A story of breaking the encryption of a SQLite database

That has nothing to do with SQLite or encryption. It's basically saying "I broke into Fort Knox - I found the key under the mat." On Sun, Jun 21, 2015 at 10:26 AM, Simon Slavin wrote: > For those of you who might be interested in a high-tech attempt at busting > SQLCipher encryption: > >

[sqlite] Mozilla wiki 'avoid SQLite'

The irony of your comment (which I entirely agree with) is that because SQLite (and similar) does so much incredibly important stuff for you, it ends up being difficult for programmers to use it, especially the lower level the programming is. They don't understand the model of how the system

[sqlite] Optimizer and redundant joins

I think without your actual schema and query plan it might be hard to tell, but generally the optimizer is not a panacea, it's limited in what it can divine from your queries. On Tue, Jun 9, 2015 at 10:52 AM, giles burgess wrote: > Hi, I have a question about the SQLite query optimizer. > > I

[sqlite] User-defined types

015 15:11:55 -0700 > Darko Volaric wrote: > > > Are you seriously saying that that SQL syntax is friendly? How can you > > defend SQL syntax other than on grounds of history or > > standardization? > > The first and best defense of SQL is that it has at least som

[sqlite] User-defined types

There's a bit of confusion as to what I'm actually proposing. I can't reply to everyone so I'll just post the APIs and/or patches when they're done and we can argue those on their merits. On Thu, Jun 4, 2015 at 5:03 PM, Darko Volaric wrote: > Well, I've been using SQL for about 30 years so

[sqlite] User-defined types

Well, I've been using SQL for about 30 years so I'm unlikely to change my view, but I think you bring up a much more important point: instead of arguing online I should get back to work! On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp wrote: > On 6/4/15, Darko Volaric wrote: > >

[sqlite] User-defined types

M, R.Smith wrote: > > > On 2015-06-04 11:16 PM, Darko Volaric wrote: > >> My point about JSON, etc is that there is no reason not to use that as a >> query language if that makes it easier. If your system is efficient with >> JSON, why not accept a query that is for

[sqlite] User-defined types

I'm saying that SQL is alien to the platform it's being used on and native is better. I'm trying to make a general point (in vain it seems), I don't use JSON. On Thu, Jun 4, 2015 at 2:46 PM, Simon Slavin wrote: > > On 4 Jun 2015, at 10:16pm, Darko Volaric wrote: > > > He

[sqlite] User-defined types

, the die is cast. Actually, I'm focusing on my own work which require all these things one way or another. On Thu, Jun 4, 2015 at 2:30 PM, Nico Williams wrote: > On Thu, Jun 04, 2015 at 02:16:22PM -0700, Darko Volaric wrote: > > { > > operation: "insert" > >

[sqlite] User-defined types

PM, Richard Hipp wrote: > On 6/4/15, Darko Volaric wrote: > > My point about JSON, etc is that there is no reason not to use that as a > > query language if that makes it easier. If your system is efficient with > > JSON, why not accept a query that is formatted as JSON? It's

[sqlite] User-defined types

generated (say into your XML query library), or virtual table instance are created or the query is transformed appropriately. On Thu, Jun 4, 2015 at 12:05 PM, Nico Williams wrote: > On Thu, Jun 04, 2015 at 11:45:28AM -0700, Darko Volaric wrote: > > Which sort of leads me to my next f

[sqlite] User-defined types

. I'll definitely release some patches when it's done. Maybe I'll call it "ite" - SQLite without the SQL. On Thu, Jun 4, 2015 at 11:23 AM, Nico Williams wrote: > On Wed, Jun 03, 2015 at 06:04:29PM -0700, Darko Volaric wrote: > > Yep, references a another one. Just like the

[sqlite] User-defined types

3, 2015 at 11:52 PM, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric wrote: > > > In my case I'm already modifying and maintaining my own version of > SQLite. > > [...]. The last time I brought these ideas up I was > > practically chased off by

[sqlite] SourceForge seems to have grabbed the "sqlite" project there

Sourceforge is rapidly digging its own grave with its awful behavior. It's hardly "taken control" of the project, it's just another fork, essentially. SQLite isn't even copyrighted and has no licence, so no problem there. Using the SQLite trademark might be a problem though. On Wed, Jun 3, 2015

[sqlite] User-defined types

and requirements instead of the database's or its API. PgSQL is also a poor fit for me because it's huge and assumes a (huge) server, I'm running on small nodes with relatively little memory. On Wed, Jun 3, 2015 at 4:26 PM, Nico Williams wrote: > On Wed, Jun 03, 2015 at 03:55:04PM -0700, Da

[sqlite] Recursive CTE optimization

I think the difficulty here is that the optimizer is oriented toward the "low level" and mainly concerned with choosing indexes, processing order etc (see https://www.sqlite.org/optoverview.html ) and has sort of a narrow view of the task. Approaching it from the other end and breaking down the

[sqlite] User-defined types

I've tackled this problem from a couple of different angles. My goal was to allow arbitrary user defined types, based on the builtin types (essentially subtypes of the existing types), with a minimum of work and minimum disruption of the normal/existing use of the database and API. The approaches

[sqlite] Download Image from Internet and add directly to Database

Use incremental blob I/O, see http://sqlite.org/c3ref/blob_open.html Note that you'll need to use INSERT or UPDATE SQL to allocate or expand space for the blob in the database field. On Thu, May 21, 2015 at 7:17 PM, reddy ykk wrote: > Hi All, > > I am facing a problem in downloading the

[sqlite] Appropriate Uses For SQLite

I think that IR would be something like first order predicate logic, to which SQL and the relational calculus is closely related. Now that we have WITH and recursive queries, you've basically got a bottom-up evaluation of the declarative subset of Prolog (if you ignore issues relating to logic

[sqlite] Appropriate Uses For SQLite

to jump through, complicating things along the way. Obviously this is not terribly mainstream, but I think would greatly improve the usefulness and structure of SQLite. Just my 2 cents on the subject... On Wed, Feb 18, 2015 at 11:43 AM, Richard Hipp wrote: > On 2/18/15, Darko Volaric wrote: >

[sqlite] Appropriate Uses For SQLite

I second this notion. I think SQLite is uniquely suited to server based applications of all kinds. Its light footprint and the fact that it's a library rather than a full system gives it a flexibility and raw performance that other systems cannot. We use it at the core of each node in a

Re: [sqlite] ordinary CTE containing sum()

I'm interested in this too (since I rely on it working). According to the documentation this should be perfectly legal and seems like an arbitrary limitation (or a bug). It says: "An ordinary common table expression works as if it were a view that exists for the duration of a single statement."

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

you want to address anything to do with me, then email me directly at take it off list, so as to stop wasting other people's time. On Wed, Nov 26, 2014 at 8:20 AM, RSmith <rsm...@rsweb.co.za> wrote: > > On 2014/11/26 15:58, Darko Volaric wrote: > >> I'm not looking for confi

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

his should be doable and will be so elegant a > solution. We all go through that. You probably came here looking for > confirmation of your ideas rather than advice, but many of the people here > have already done what you are trying now, this is why they know and this > is why this list is

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

n be stored. On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden <jklow...@schemamania.org> wrote: > On Tue, 25 Nov 2014 04:41:51 -0800 > Darko Volaric <li...@darko.org> wrote: > > > I have a need to implement per-value custom typing in SQLite. In my > > case

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

There's nothing vague about select statements, they're logical formulas involving the data in your database and as exact as any other programming language, albeit in a very different domain. Relational databases are based on first order predicate logic and have operations that are are rigorously