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

[Jay Kreibich]

> 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
> is still delivered directly to your in-box, just like with a mailing
> list,

I’m not sure I’d agree with that.  People tend to quote when replying to mail, 
even if just one line, to provide context.  That’s a lot less true of the 
forums, especially if you’re responding to a one line question or comment on 
the message right above yours.   Since the forum emails only quote the new 
message, there is much less context in any given message.  Part of why I like 
email lists is that they may be old school, but people that use them well make 
sure there is very very little state in the conversation.  That’s the exact 
opposite with forum posts.

I’m not saying the forum isn’t better for the team, simply that they’re not 
equal.  And at the end of the day, forums must be engaged in actively (I need 
to go visit it), while email is passive (it comes to me in a media I scan 
regularly).  For someone that is largely a lurker these days, it was easy to 
just watch messages go by and jump in if needed, having most of the context of 
the conversation.  With a forum, the email notifications are much less useful 
(and less likely to trigger the “Oh, I know that..” response), and I’m never 
going to go visit the forum just to see what’s up.

This might be better for the community, but it largely means I’m out of the 
community.  I’m not sure that’s a particular loss for me, or the community, as 
I’ve not been very active for a decade or so.

  -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 evaluate during the parse/prepare phase of the processing, not 
the execution.  There are similar problems in more traditional languages 
(especially scripting languages) that attempt to optimize out or pre-compute 
constant expressions.

If that’s the case, then the issue is not so much that the COALESCE() is 
failing to short-circuit, but rather than the SQL statement failing to 
“compiling” an invalid statement.

If you’re doing this in code as separate prepare/step/finalize, it would be 
interesting to see where it fails.  My guess is prepare, not step.

  -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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" seemed like the natural term to use, as it
> seems to mean "without a server".
> 
> 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 when they see my claim that
> "SQLite is serverless".
> 
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
> 
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
> 
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
> 
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
> 
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Jay Kreibich]

> 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 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 have found with x=1 is:
>> "select round(1.15,1)"
>> Result: "1.1" (should be 1.2)
> 
> SQLite uses the IEEE-754 floating point format for real values.
> 
> In that format, the value “1.150…” does not exist.
> 
> The closest value that can be represented is 1.1497615814208984375
> 
> Hence, the rounding.

OK, no, I’m wrong.  Because 1.05 rounds to 1.1, even though the representation 
is 1.049523162841796875.

Well, half wrong.  It is because of IEEE-754, but not because of representation.

It would appear the round() function simply uses Banker’s Rounding: when given 
a value that ends in exactly 5, use the previous digit to figure out which way 
to go: even goes up, odd goes down.

According to Wikipedia, this is the default rounding mode for IEEE-754
https://en.wikipedia.org/wiki/Rounding#Round_half_to_even 
<https://en.wikipedia.org/wiki/Rounding#Round_half_to_even>

 -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Jay Kreibich]

> 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 have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

SQLite uses the IEEE-754 floating point format for real values.

In that format, the value “1.150…” does not exist.

The closest value that can be represented is 1.1497615814208984375

Hence, the rounding.

  -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lookup join

[Jay Kreibich]

> 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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect query result

[Jay Kreibich]

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 have been reduced to the minimum, so they are
> not really relevant, but demonstrates better where is the problem.
> Also note that the result is correct using SQLite 3.15.2.
> 
> The first query fills an array with number of rows returned per "sens"
> attribute.
> The second query is the same, but using "WHERE ITI1.sens = 1".
> The third query is the same as the second, but without the GROUP BY in the
> WITH clause.
> 
> The second query returns nothing, which is not expected, as shown in the
> result of the first query.
> 
> Thank you and kind regards,
> 
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A license plate of NULL

[Jay Kreibich]

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 hope you'll indulge this 
> slightly off-charter post.
> 
> 
> 
> "  Droogie decided to buy a vanity California license plate that simply said 
> "NULL," [...]
> 
> Apparently, when they didn't have the right data for a vehicle, a privately 
> operated citation processing center used the word NULL in the license plate 
> field for many tickets. Since that just happens to be Droogie's license 
> plate, he got all of them. "
> 
> Most people reading this will notice the error in simplification.  But it 
> still indicates that the DMV needs to review its programming.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Jay Kreibich]

> 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
> 
> The difference between 211496.252 (which is itself a truncated 
> representation -- the actual stored value should presumably be 
> 211496.252200173) and 211496.260931323 is 
> 8.73114913702011e-11, or 3 ULP.
> 
> Applying half-even rounding at the second "decimal place" to 
> 211496.252 (which when multiplied by 100 is 21149625.25494194 
> which half-even rounds to 21149626.0 which when divided by 100 is 
> 211496.260931323 or exactly the "nearest representable IEEE-754 
> double precision floating point number" to 211496.26


Which brings up an important point…. When saying "double-precision floating 
point is accurate to within 15 decimal digits” it does NOT mean floating point 
can perfectly represent any number up to 15 digits long, it means that the 
delta between intent and representation is 15 digits smaller than the largest 
represented digit.  In this case, the delta is 10 digits under the radix point, 
plus another 5 or 6 digits from the other side of the radix point.

In short, the problem _is_ storage… or at least representation.  And that’s 
before calculations muck everything up further.

  -j


>> 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.
>> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Jay Kreibich]

> 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 transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open /sqlite_db/ccc
> sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4 and 
> tran_date between '2015-05-01' and '2015-05-31';
> 211496.26
> 
> Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit 
> (AMD64)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
 import sqlite3
 sqlite3.sqlite_version
> '3.26.0'
 conn = sqlite3.connect('/sqlite_db/ccc')
 cur = conn.cursor()
 cur.execute("select sum(amount_cust) from ar_trans where cust_row_id = 4 
 and tran_date between '2015-05-01' and '2015-05-31'")
> 
 cur.fetchone()
> (211496.252,)
> 
> With the same version of sqlite3 and the same select statement, why does 
> python return a different result from sqlite3.exe?


Because the shell is altering the output to make it easier to read.  Consider:

$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 211496.252;
211496.26


-j



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange query results.

[Jay Kreibich]

> 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 (2, 4);
> insert into config (config_package, config_flags) values (3, 6);
> select config_package, config_flags, (config_flags & '4') from config
> where (config_flags & '4') != '4';
> 
> This produces:
> 
> 1|2|0
> 2|4|4
> 3|6|4

You’re putting single-quotes around the 4, so it is a one character string, not 
a number.

In the case of “ config_flags & ‘4’ ”, the “&” operator only accepts numbers, 
so the string is converted to a number.

In the case of “  != ‘4’ “, the equality operator is checking to see if 
the number 4 is equal to the string ‘4’.  They are not.  != already returns 
true.

You can fix this by changing all instances of “ ‘4’ “ to just “4”.

  -j



> While it should only have returned the first row.
> -- 
> ===
> Maurice van der Stee (s...@planet.nl)
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 modify tables: INSERT, UPDATE, 
> DELETE.  Commands which modify the database schema fall outside the scope of 
> transactions and cannot be handled within the framework of COMMIT and 
> ROLLBACK.
> 
> So don't use BEGIN and COMMIT for anything except INSERT, UPDATE, DELETE .  
> Perhaps you could make that change to your code and see whether the problem 
> remains.

While that is true in many RDBMS products, in SQLite DDL commands are 
transactional.

  -j



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP!

[Jay Kreibich]

> 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 is likely to be inefficient.)

Not just inefficient, it is almost guaranteed to corrupt the database file if 
more than one connection tries to access it at the same time.  There isn’t a 
remote file system out there (in the Windows or Unix world) that correctly 
implements the locking structures SQLite requires.

  -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 an IX, they’re a transit provider.

Their big claim to fame was that they were one of the pioneers of IPv6, making 
tools, tunnels, and prefixes available to a wide range of organizations, 
including individuals, long before most of the rest of the internet even cared 
about IPv6.

> Regardless the whole reference to "fopen()" is a bit of a joke.

Not really… it is exactly as it says.  SQLite is designed to be used in 
situations were a developer might be tempted to just use CSV or TSV files.  The 
link is only there to clue in non-programmers (or those that have never used C) 
what they heck the webpage is talking about.  They don’t need to understand it, 
beyond the fact it is an API call.

 -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 to files/stdout.

Only relevant on Windows; does absolutely nothing on all other platforms.

sqlite-amalgamation-3250200/shell.c:200:

/* On Windows, we normally run with output mode of TEXT so that \n characters
** are automatically translated into \r\n.  However, this behavior needs
** to be disabled in some cases (ex: when generating CSV output and when
** rendering quoted strings that contain \n characters).  The following
** routines take care of that.
*/
#if defined(_WIN32) || defined(WIN32)
static void setBinaryMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_BINARY);
}
static void setTextMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_TEXT);
}
#else
# define setBinaryMode(X,Y)
# define setTextMode(X,Y)
#endif





> 
> regards, 
> 
> Graham
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 reproduce:
> 
> sqlite> CREATE TABLE A(col1 STRING);
> sqlite> INSERT INTO A VALUES('asdf');
> sqlite> SELECT CAST(col1 AS STRING) FROM A;
> 0
> 
> We are generating SQL code programmatically, and it would be useful to not
> have to special-case casting based on column type.
> -- 
> --
> 
> Ben Caine | Software Engineer | Verily
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 dataset is omitted from the joined dataset. This is the only 
> difference between a USING clause and its equivalent ON constraint."
> 
> I thought it literally took that column out of the result set. So "a SOME 
> SORT OF JOIN b USING (foo)" literally got rid of a.foo and b.foo and put the 
> value of a.foo into a "foo" column, or literally got rid of b.foo. And that 
> trying to select a.foo, or especially b.foo would raise an error of it not 
> being an existing column.
> 
> I didn't realize that the column omission  happens with you "select * 
> from" and not for anything else.
> 
> The more you know.


Yeah, this relates to Relational Theory, SQL, and how the two are almost, 
sorta, kinda, but not-really the same.

One thing to keep in mind is that the source columns and the “working set” of 
columns (SELECT *) are not the same.  You can always explicitly name a source 
column.  While USING “collapses” the two columns in the working set (as does a 
NATURAL JOIN), it does not eliminate the ability to explicitly reference a 
specific source column using the table notation… that’s why the WHERE clause in 
my example works as well.  And that’s kind of what the .* notation does, 
just brings in all the columns from a specific source table.  In a NATURAL JOIN 
(or a standard [CROSS] JOIN...USING) the two columns will always be the same, 
so there is little need to explicitly reference a source column.  That’s not 
true with OUTER JOINs, however, but as shown that can be really useful.

  -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 from table A with nam 
> values, which are not present in the nam values of table B?
> Thanks —  Joe

Not sure about performance vs other suggestions, but this is a common way of 
doing that:

SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL


  -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 
> efficient. If only we had been this clever when we started….


Depending on the complexity of the scheme, it sounds like it might be easier to 
just dump the other tables, re-create the database without this jumbo table, 
and re-import the other data.

 -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 table after the 
column is removed.  This may or may not be feasible, depending on if you have a 
small number of large tables, or a large number of medium/small tables.  For 
smaller tables you can clear the column, then create a copy of the table with a 
new name, use something like INSERT INTO…SELECT to copy the data, then drop the 
old table, and finally rename the new one back to the old name.  You’ll want to 
do all that in a transaction which will lock the database, but the individual 
tables are not huge that might be much faster than a VACUUM.  Of course, it 
requires two copies of the table to exist in the database at the same time, but 
when you’re done, the new table will be smaller and all the pages used by the 
old table will go into the free list.

I’d also suggest you get a copy of sqlite_analyze running, as it will tell you 
stats about page usage and such.  That will give you some insight into if this 
process is worth the effort.

 -j





> We'd also expect lots more writes in
> the future (after obsoleting the column), so it seems preferable to focus
> on making sure that the space that was used by the column is just returned
> to be used by SQLite for those future writes.
> 
> Ben
> 
> On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin  wrote:
> 
>> On 9 Jun 2018, at 3:52pm, Ben Asher  wrote:
>> 
>>> we would like to make sure that the space occupied by the columns
>> contents is zeroed and returned to SQLite's free list (
>> https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the
>> column's contents to "" (for a TEXT column
>>> specifically) sufficient to do that?
>> 
>> I recommend you use NULL instead:
>> 
>> UPDATE myTable SET deadColumn = NULL WHERE [whatever];
>> VACUUM;
>> 
>> I have three reasons for preferring NULL:
>> 
>> 1) NULL takes the minimum amount of space to store
>> 2) handing NULLs is much faster than handling strings
>> 3) NULL in SQL literally means "value missing" or "value unknown".
>> 
>> [later seeing a followup]
>> 
>> If you can't use NULL, use the integer 0.  Even if the column has TEXT
>> affiliation.  The values NULL, 0 and 1 each take no storage (they are
>> special value types) and all three values are handled very efficiently.
>> 
>> VACUUM will do the actual returning of space and shrink the file size.
>> Doing it with VACUUM does it all at once at a time of your controlling
>> rather than slowing down subsequent operations.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> -- 
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> On Jun 9, 2018, at 10:04 AM, Ben Asher  wrote:
> 
> Right we'll prefer NULL, but some columns are NON NULL. In this scenario,
> we're not worried about returning space to the OS, but good point out
> VACUUM. Thanks!

Without digging through some detailed docs, I’m pretty sure empty string and 
NULL require the same amount of storage space.  If not, the difference is maybe 
one byte.

As for freeing the pages, the issue is that row values are stored in leaf 
pages, kind of like a big array or list, packed together— not only are the 
values in a row packed, the rows themselves are them packed into pages.  If you 
just set existing column value to NULL (or empty string) it is going to re-pack 
and re-write the values in that page, but it isn’t going to globally recover 
the space used by that column because it is intermixed with all the other 
columns.  The only case when a single column change would trigger page recovery 
is if the value in that column is so big the row overflows a single page and 
requires spill pages.  Since a column clear generally won’t free whole pages, 
there is nothing to put on the free list.

The point of the VACUUM is not to release free pages back to the OS, but to 
force the database to re-write (and therefore re-pack) all the rows, so that 
the whole table will require fewer pages, and whatever is left over can be 
freed (or in the case of a VACUUM, never re-written).

It is different when you delete rows, since the the whole 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:
> 
>> 
>>> 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 obsolete. Given the constraints, we've decided to
>>> create a column "graveyard" at the application level: basically a list of
>>> columns that exist but are obsolete. While we cannot drop columns, we
>> would
>>> like to make sure that the space occupied by the columns contents is
>> zeroed
>>> and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12)
>> to
>>> be re-used. Is setting the column's contents to "" (for a TEXT column
>>> specifically) sufficient to do that?
>> 
>> That or NULL.  You also have to vacuum the database to re-pack the
>> database pages and actually recover the disk space.  Some of the space may
>> be recovered if the TEXT records were really long (and required spill
>> pages) but if most of the column were a dozen bytes or so (more typical)
>> you’ll want to do a VACUUM.
>> 
>> -j
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> -- 
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 obsolete. Given the constraints, we've decided to
> create a column "graveyard" at the application level: basically a list of
> columns that exist but are obsolete. While we cannot drop columns, we would
> like to make sure that the space occupied by the columns contents is zeroed
> and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12) to
> be re-used. Is setting the column's contents to "" (for a TEXT column
> specifically) sufficient to do that?

That or NULL.  You also have to vacuum the database to re-pack the database 
pages and actually recover the disk space.  Some of the space may be recovered 
if the TEXT records were really long (and required spill pages) but if most of 
the column were a dozen bytes or so (more typical) you’ll want to do a VACUUM.

 -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random rows

[Jay Kreibich]

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;

As this makes sure random() is only called once per row.  I’m pretty sure this 
is actually equivalent to yours, but it makes things a bit more explicit.



If you only needed one, I’d do something like:

SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT 
count(*) FROM table);

Or even just call that multiple times if you need a moderate number.  The ORDER 
BY should be free, but it could also be eliminated.

  -j



> On May 31, 2018, at 7:12 PM, Torsten Curdt  wrote:
> 
> I need to get some random rows from a large(ish) table.
> 
> The following seems to be the most straight forward - but not the fastest.
> 
>  SELECT * FROM table ORDER BY random() limit 200
> 
> Is there a faster/better approach?
> 
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Resources for learning SQLite

[Jay Kreibich]

> On Mar 29, 2018, at 12:06 PM, Mike Clark <cyberherbal...@gmail.com> 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 anyone have any recommendations for
> books or online resources?
> 
> I'm particularly interested in resources that use C#.
> 
> I've been working with "Using SQLite" by Jay A. Kreibich, and it's very
> useful, but if there's something more advanced I'd love to know about it.

Glad it’s useful.  It’s also a bit out of date, as it was published in 2010 
when the latest version was 3.6.  The core stuff is still valid, but there have 
been a LOT of advancements in the last eight years.

  -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 AUTOINCREMENT?
> 
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexing multiple values per row

[Jay Kreibich]

> 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 only index one 
> value per row. Traditional relational-database design says to normalize the 
> schema by storing the multiple values in separate rows, but what if the data 
> to be indexed is JSON and you need to leave it in that form?
> 
> For example, let's say I have a table like
>   patient_id: 12345
>   temps: "[98.6, 99.1, 101.3, 100.0, 98.9]"
> and I want to run queries on temperature data, like 'patients who've had a 
> temperature above 101'. And I need better than O(n) performance.

Recognize the fact that if you’re storing data in a JSON string, to the 
database that is just one single value: a string.  The database has no 
knowledge and understanding of that value beyond the fact it is a string.  
Asking the database to index or do something with the arbitrarily constructed 
sub-values you’ve created is outside the scope of what the database can do, 
because you’re storing data in a format outside the scope of the database.

If you do want to use database functions and queries to deal with this kind of 
thing, store the data in a way the database understands it and can use it… for 
example, a table that includes “patient_id, timestamp, temp” with “temp” being 
a SINGLE numeric value.  The patient_id column can be a foreign key back the 
full patient record.  Such a design is easier to insert, update, and just about 
everything else.


> In the past my project used map/reduce to support this, essentially 
> implementing its own index system on top of SQLite tables. In this case it 
> would create a table (patient_id integer primary key, temp number) and 
> populate it by scanning the patient table. This can obviously be indexed 
> easily, but updating the table before a query when the source table has 
> changed is a pain in the butt.

Then get rid of the array and just store the values that way for everything.  
Don’t have a “source table.”  It’s a very non-relational way to store data 
anyways.

  -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

> 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 “original” 
row, it just happens that the most common conflict is primary key.  It’s also 
true that inserting a single row with “insert or replace” can cause multiple 
rows to be deleted (if there are multiple constraints across multiple columns, 
for example).

 -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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:

“Insert or replace” is NOT “insert or update.”

Insert always inserts a new row.  The only question is if it deletes 
conflicting rows (yes, plural) first.



With that in mind, the FK concept gets a little fuzzy.  It is a new row that 
just happens to have the same ID as an old row.  Does that mean the FK in B 
should still reference it?  I’d say no, because it is a brand new row… a 
different entity. If you want B to keep referencing the existing row, then 
update the row that is already there, don’t delete it and insert a new row on 
top of it.  I’d look into deferrable constraints to see if the behavior you’re 
looking for is supported.  Hopefully others can comment more on that.

 -j





> On Mar 9, 2018, at 1:11 PM, John Found  wrote:
> 
> 
> I have two tables with foreign constraint:
> 
>create table A ( id primary key not null, single_data );
>create table B ( aid references A(id) on delete cascade, multi_data);
> 
> Now I am periodically inserting data in A and B with the following queries:
> 
>insert or replace into A values (?1, ?2);
>insert into B values (?1, ?2); 
> 
> Unfortunately, after replacing some row in A, all previously inserted rows in 
> B got deleted, even if the value of ID does not changes. Here SQLite works 
> exactly as it first deletes the conflicting row from A and then inserting new.
> 
> Now, if I define the table B without "on delete":
> 
>create table B ( aid references A(id), multi_data);
> 
> "insert or replace" succeed without deleting the old rows from B.
> 
> In my opinion this behaviour is not consistent. The consistent solutions IMHO 
> are two:
> 
> 1. "insert or replace" succeed both in the first and in the second case 
> without deleting rows from B,
> 
> 2. "insert or delete" succeed in the first case, deleting all constrained 
> rows from B and fails in the second case with "FOREIGN KEY constraint failed".
> 
> The first case IMHO is more intuitive and natural. At least this was my 
> expectation when writing the code.
> 
> 
> -- 
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[Jay Kreibich]

> 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 software in the conventional sense. SQLite is 
> written by a small team of people, and outside contributions are far less 
> common than in most open source software. 


Indeed.  The core SQLite source code is free (as in beer, as in freedom), but 
its development is carefully controlled by Hwaci, the company responsible for 
employing SQLite developers.  Hwaci also owns the trademark to the name SQLite. 
Hwaci has several products, including extensions such as the SQLite encryption 
extensions, that are NOT free (as in beer, nor as in freedom).  The testing 
system used for SQLite, which represents a source base several times larger 
than the SQLite core, is also completely private.  Hosting such systems on 
Github would require a subscription fee and, frankly, why should they bother?

   -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why Unicode is difficult

[Jay Kreibich]

> On Dec 4, 2017, at 1:33 PM, Igor Korot <ikoro...@gmail.com> wrote:
> 
> Stephen,
> 
> On Mon, Dec 4, 2017 at 1:01 PM, Stephen Chrzanowski <pontia...@gmail.com> 
> wrote:
>> ... as in how 1 != "1"?
> 
> No.
> 1000 vs 1,000 vs 1.000 vs 1,000.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...@gmail.com> wrote:
>> 
>>> Hi,
>>> 
>>> On Mon, Dec 4, 2017 at 7:42 AM, Jay Kreibich <j...@kreibi.ch> wrote:
>>>> 
>>>> Next, we can talk about how dates and times are simple and 
>>>> straight-forward.
>>> 
>>> And then the number representation...
>>> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why Unicode is difficult

[Jay Kreibich]

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 how hard this is.  So 
> here’s an article which, after introductory material, discusses the hard 
> questions in Unicode:
> 
> 
> 
> Are two strings the same?
> How long is a string?
> How do you sort things in alphabetical order?
> 
> The first and third questions are requirements for implementing COLLATE in 
> SQLite.  And the fact that the second question is a difficult one emphasises 
> that one shouldn’t take Unicode as simple.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Jay Kreibich]

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 he is 
> describing as the problems with serial IDs (or using the RowID) are simple to 
> avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a 
> URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny 
> would say "What a maroon!".

>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>> Sent: Thursday, 30 November, 2017 07:16
>> To: SQLite mailing list
>> Subject: [sqlite] Article on AUTOINC vs. UUIDs
>> 
>> Thought some of you might enjoy seeing this article.  I make no
>> comment on what I think of the reasoning therein.  It’s set in the
>> PostgreSQL world, but you could make an external function for SQLite
>> which generates UUIDs.
>> 
>> > increment-is-a-terrible-idea/>
>> 
>> "Today, I'll talk about why we stopped using serial integers for our
>> primary keys, and why we're now extensively using Universally Unique
>> IDs (or UUIDs) almost everywhere."
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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
>>>> 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 2 do initialization for their own conn, which 
>>>> is to read to schema into memory.
>>>> Then, Conn A creates a table with Conn A.
>>>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
>>>> it returns nothing.
>>>> 
>>>> I do know the reason should be the expired in-memory-schema.
>>> 
>>> No, SQLite automatically detects schema changes.
>> 
>> …but only automatically re-prepares the expired statements if the statement
>> was originally prepared using sqlite3_prepare*_v2 or _v3.
> 
> The word "expired" in the OP is misleading; this problem has nothing to do
> with schema expiration (that would result in an SQLITE_SCHEMA error).

Ah…. OK yes.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Aug 18, 2017, at 4:04 AM, sanhua.zh <sanhua...@foxmail.com> 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 database is changed.
> 
> 
> The following sample runs in different threads, but I force them to 
> runsequentially.
> 
> 
> Thread 1:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> Thread 2:
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> Thread 1:
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> Thread 2:
> 4.ConnB: PRAGMA table_info('sample')
> 
> 
> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
> to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
> returns nothing.
> The same thing could happen if I change the step 4 to 
> `sqlite3_table_column_metadata` or some other interfaces.
> 
> 
> I do know the reason should be the expired in-memory-schema. But I find no 
> docs about which interface will or will not update the schema and what should 
> I do while I call a non-update-schema interface ?


See the bottom of the sqlite3_prepare*() docs:

https://www.sqlite.org/c3ref/prepare.html

And the SQLITE_SCHEMA docs:

https://www.sqlite.org/rescode.html#schema



As the docs say, make sure you’re using sqlite3_prepare*_v2() or _v3().  If a 
statement is prepared with these newer versions, it will handle most expiration 
situations automatically by re-preparing the statement.

Generally speaking, if you do get an SQLITE_SCHEMA error, you need to rollback 
the current transaction, re-prepare the statements, and try again.

   -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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
>> 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 2 do initialization for their own conn, which is 
>> to read to schema into memory.
>> Then, Conn A creates a table with Conn A.
>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
>> it returns nothing.
>> 
>> I do know the reason should be the expired in-memory-schema.
> 
> No, SQLite automatically detects schema changes.

…but only automatically re-prepares the expired statements if the statement
was originally prepared using sqlite3_prepare*_v2 or _v3.


> It's likely that the second connection started its transaction before
> the first connection committed its own, so it still sees the old state
> of the database.

Unlike most RDBMS environments, SQLite handles DDL as part of
normal transactions.  I don’t think this situation would be a problem,
and would be handled by the normal locking mechanisms.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Aug 15, 2017, at 10:39 AM, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> 
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 this:
>> 
>> select group_concat(name, ' ') AS 'names' from (
>> SELECT name FROM moca_config where enable == 1 order by name
>> );
> 
> That does return the expected order.  Was my expectation unreasonable?


Unreasonable?  Not really; it is a simple mistake, but it is a mistake.

You need to remember that the different clauses of an SQL statement are 
processed in a specific order.  Relevant to this case, ORDER BY is applied 
*after* GROUP BY, or any other aggregation.  Because you have an aggregate 
function in the SELECT clause, but no explicit GROUP BY, you have an implied 
GROUP BY across the whole output of the FROM clause.  So the aggregation 
happens and *then* the ORDER BY is applied…  except the final output of this 
statement, as defined by the SELECT clause, has no column named “name” ...so no 
ordering is enforced.

This is also why the sub-select works.  It generates a full output, with 
ordering, and then passes it to the outer statement to do the aggregation.

  -j




> 
> Bob
> -- 
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Jay Kreibich]

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 too.

 -j





On Jul 17, 2017, at 1:08 PM, Ron <ron.a...@gmail.com> wrote:

> Hi,
> 
> Before I dive into the SQLite internals, I'd like to ask the mailing list
> first.
> 
> I am looking into the possibility of putting the changes in SQLite database
> into kafka, similar to this:
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> .
> 
> Would it be hard to get this info out of SQLite?
> PostgreSQL calls it 'logical decoding'. I may not need the full feature,
> but I would need all changed records.
> 
> Thanks for any insights.
> Ron Arts
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Mar 25, 2017, at 5:52 PM, petern <peter.nichvolo...@gmail.com> 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 concurrency
> resources?  [Block with timeout would be even more desirable of course.]


No, there is not.  As others have pointed out, this is not a very “databasey” 
thing.  The whole point of relational, ACID transaction databases is that they 
provide an atomic “snapshot in time” view of a data set.  They do everything 
they can hide changes within a transaction, and there is no way to issue a 
SELECT outside of a transaction.

It sounds like you really need a message queue of some type, more of a pub/sub 
infrastructure.  There are dozens of products out there that do this.  That 
said, if all the processes are on a single system (as they would have to be to 
be using SQLite to communicate) it might make a lot more sense to use a simple 
text file.  One writer (or more) can append lines, and multiple processes can 
read from the end, using blocking I/O on the file, not unlike a “tail -f” in 
the UNIX world.  If you keep track of which line number you’re on, you can 
retry or re-pickup if a reader process needs to restart, plus the file provides 
a log of all messages.  Simple, easy, and straight forward.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 brought in a large number of 
statistical math functions in a consistent way.  I’m not sure what ever 
happened to it, but that seems like a much better approach.

 -j





On Mar 9, 2017, at 7:49 AM, Eric Grange <egra...@glscene.org> wrote:

> A bonus of having them defined in the core is that it avoids the minor
> inconsistencies that are bound to arise in custom implementations (starting
> with the name of math functions)
> 
> Main downside is probably not going to be the size, but that it reserves
> more names, and may conflict with existing custom implementations.
> 
> Eric
> 
> On Thu, Mar 9, 2017 at 1:16 PM, R Smith <rsm...@rsweb.co.za> wrote:
> 
>> I second this - Been having a hard time making basic queries with a simple
>> x^y function in SQL for SQLite since there is no guarantee what the
>> end-user's system will have it compiled-in. I can version-check or
>> version-enforce easily, but compile-option check or enforce is a no-go.
>> 
>> If we can shift the basic "Auto-included" feature set a few notches up, we
>> can still have hardcore minimalist users compile their own (as they
>> probably already do), but it would be nice to know a query running on a
>> standard linux or Apple OS on the included SQLite will support some wider
>> functions as a rule[1] without having to keep track. I realize this will
>> take a time to permeate through the world, but it would be great to start
>> asap.
>> 
>> [1] Yes, there are threads on this same forum where I myself kicked
>> against bloating SQLite with unneeded functionality as a rule, but perhaps
>> the definition of "needed" needs revisiting. I think good math and string
>> functions certainly qualify.
>> 
>> Cheers,
>> Ryan
>> 
>> 
>> 
>> On 2017/03/09 11:45 AM, Dominique Devienne wrote:
>> 
>>> I find that I'm often missing basic mathematical functions in the default
>>> shell.
>>> Many SQLite clients add many, but given that the official SQLite shell
>>> misses
>>> them you can't use them in views for predefined "reports" within the DB
>>> file itself, for example.
>>> 
>>> There's [1] which is 50KB, but only a tiny part of that is for math
>>> functions, so math functions are only a few KBs away.
>>> 
>>> Adding basic math functions and stddev, median, variance, etc... wouldn't
>>> add much,
>>> and they could be added to the shell at least, if deemed too big for the
>>> amalgamation,
>>> but given that many things can be turned on/off in the amalgamation, that
>>> would be just
>>> one more IMHO.
>>> 
>>> The goal here would be to move the "minimum expectations" of what can be
>>> done with the official shell, out-of-the-box, w/o the need to resort to
>>> .load of an extension which is not readily available in compiled form for
>>> many non-programmer users.
>>> 
>>> And IMHO, the ability to use math functions in views is why "moving the
>>> baseline" is necessary,
>>> since without those being built-in, the views will just error out in the
>>> official shell.
>>> 
>>> My $0.02, despite the upcoming chorus about lite or do-it-in-your-app
>>> naysayers. Thanks, --DD
>>> 
>>> PS: Sure SQLite's primary use case is as an *embedded* DB, so the host-app
>>> can add
>>> whatever it wants/needs in terms of UDFs, but I also think the
>>> "standalone"
>>> use of SQLite independently of the app that generated the DB file is
>>> important, and we should raise the
>>> bar of the minimum number of built-in functions, starting with the
>>> official
>>> shell.
>>> 
>>> [1] https://www.sqlite.org/contrib/download/extension-functions.c?get=25
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple web query tool

[Jay Kreibich]

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 dust off the PHP tutorials and spend an afternoon throwing something
together?

 -j


-- 
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but
showing it to the wrong people has the tendency to make them feel
uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

[Jay Weinstein]

Hi Keith,

Made sure the timeouts are set to 1000 ms for both C and java
implementations.  Same error.   So, you are suggesting this is an issue
with the guys who wrote the driver for sqlite?   Not sure who these guys
are.

On Fri, Dec 9, 2016 at 11:22 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Sounds like a java problem with the java interface, not an SQLite
> problem.  Two observations however:
>
> 1)  With the C API you set the busy timeout to 1000 ms but with the java
> api you set it to 100 ms
> 2)  With the C API you set the busy timeout to the integer 1000 yet in the
> java api you set it to the string "100"
>
> In any case, the problem is in the Java API.  You have demonstrated that
> the problem is not caused by SQLite itself ...
>
> Perhaps a chat with the Java wrapper developers is in order.
>
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jay Weinstein
> > Sent: Friday, 9 December, 2016 11:50
> > To: SQLite mailing list
> > Subject: Re: [sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The
> > database file is locked (database is locked)
> >
> > When I run the two C programs using
> >
> >   rc = sqlite3_busy_timeout(db, 1000);
> >   if ( rc != SQLITE_OK ) {
> > fprintf(stderr, "SQL error: sqlite3_busy_timeout
> > failed.\n");
> > exit(0);
> >   }
> >
> > everything works perfectly.  No sqlite busy erros or table locked.
> >
> > But, when I to do this using a java app using the following the code
> > doesn't work.  Basically, config.setBusyTimeout() doesn't seem to be
> > working as I anticipated --  see below.   Can you advise?
> >
> > Thanks, Jay
> >
> > Class.forName("org.sqlite.JDBC");
> > String s = "jdbc:sqlite:"+db;
> >
> > config = new SQLiteConfig();
> > config.setBusyTimeout("100");
> > conn = DriverManager.getConnection(s, config.toProperties());
> > conn.setAutoCommit(false);
> > stmt = conn.createStatement();
> >
> > On Wed, Dec 7, 2016 at 4:17 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> > >
> > > On 7 Dec 2016, at 11:28pm, Jay Weinstein <jpwe...@gmail.com> wrote:
> > >
> > > > Is it correct to say busy timeout will work for two separate
> processes
> > > and
> > > > I don't have use fork in C/C++ or ProcessBuilder in java to execute
> > one
> > > as
> > > > a child and a parent?
> > >
> > > The timeout setting is attached to the database connection.  If you
> > > execute _open() twice you need to set it once for each connection.
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

[Jay Weinstein]

When I run the two C programs using

  rc = sqlite3_busy_timeout(db, 1000);
  if ( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: sqlite3_busy_timeout
failed.\n");
exit(0);
  }

everything works perfectly.  No sqlite busy erros or table locked.

But, when I to do this using a java app using the following the code
doesn't work.  Basically, config.setBusyTimeout() doesn't seem to be
working as I anticipated --  see below.   Can you advise?

Thanks, Jay

Class.forName("org.sqlite.JDBC");
String s = "jdbc:sqlite:"+db;

config = new SQLiteConfig();
config.setBusyTimeout("100");
conn = DriverManager.getConnection(s, config.toProperties());
conn.setAutoCommit(false);
stmt = conn.createStatement();

On Wed, Dec 7, 2016 at 4:17 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 7 Dec 2016, at 11:28pm, Jay Weinstein <jpwe...@gmail.com> wrote:
>
> > Is it correct to say busy timeout will work for two separate processes
> and
> > I don't have use fork in C/C++ or ProcessBuilder in java to execute one
> as
> > a child and a parent?
>
> The timeout setting is attached to the database connection.  If you
> execute _open() twice you need to set it once for each connection.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

[Jay Weinstein]

Hi,

Is it correct to say busy timeout will work for two separate processes and
I don't have use fork in C/C++ or ProcessBuilder in java to execute one as
a child and a parent?

Thanks,
Jay

On Wed, Dec 7, 2016 at 11:02 AM, R Smith <rsm...@rsweb.co.za> wrote:

> Yes, multiple access is a feature, a basic necessity even.
>
> The answer is here:
> http://www.sqlite.org/pragma.html#pragma_busy_timeout
>
> Explanation:
> https://www.sqlite.org/c3ref/busy_timeout.html
>
> and
> http://www.sqlite.org/tclsqlite.html
> see the section: The "timeout" method
>
>
>
> On 2016/12/07 8:54 PM, Jay Weinstein wrote:
>
>> Hi,
>>
>> I’m running two programs, one written in java and one written in C, which
>> are both trying to access a sqlite database.  I'm getting the error
>> org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked
>> (database is locked) when I run both as separate processes and when the
>> java app launches the c program with ProcessBuilder.   Is there away for
>> two process to read and write a sqlite database?
>>
>> Thanks,
>> Jay
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

[Jay Weinstein]

Thanks for  the info!

On Wed, Dec 7, 2016 at 11:02 AM, R Smith <rsm...@rsweb.co.za> wrote:

> Yes, multiple access is a feature, a basic necessity even.
>
> The answer is here:
> http://www.sqlite.org/pragma.html#pragma_busy_timeout
>
> Explanation:
> https://www.sqlite.org/c3ref/busy_timeout.html
>
> and
> http://www.sqlite.org/tclsqlite.html
> see the section: The "timeout" method
>
>
>
> On 2016/12/07 8:54 PM, Jay Weinstein wrote:
>
>> Hi,
>>
>> I’m running two programs, one written in java and one written in C, which
>> are both trying to access a sqlite database.  I'm getting the error
>> org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked
>> (database is locked) when I run both as separate processes and when the
>> java app launches the c program with ProcessBuilder.   Is there away for
>> two process to read and write a sqlite database?
>>
>> Thanks,
>> Jay
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

[Jay Weinstein]

Hi,

I’m running two programs, one written in java and one written in C, which are 
both trying to access a sqlite database.  I'm getting the error 
org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked 
(database is locked) when I run both as separate processes and when the java 
app launches the c program with ProcessBuilder.   Is there away for two process 
to read and write a sqlite database?

Thanks,
Jay


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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
> starvation and timeouts. It’s possible I was doing things wrong, as the
> wrapper library I started with included a SQLite busy-handler that would
> wait-and-retry. (But IIRC, if I removed the busy handler altogether, SQLite
> would just immediately return a db-busy error if another thread had a lock.)
>
> SQLite has its own built-in busy-handler which is written to work
> extremely efficiently with the things SQLite usually needs to do (though
> not necessarily with the way you're using SQLite).  It's not obvious
> whether the wrapper library you're using simply calls this one or
> implements its own, possibly less-efficient, one.
>


I'll admit I haven't done this low level type of SQLite programming in some
versions, but the traditional issue is that very, very few applications
handle deadlocking correctly.  Even with an intelligent busy-handler, there
are situations when multiple processes can get into a deadlock and the only
way out is for one or more processes to abort their current transactions.
I've seen very few applications that handle that situation correctly, and
it is generally something that can't be put in a wrapper or utility library
(unless it is abstracting out all transactions and database interactions).

 -j
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

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  wrote:

> > Long columns, especially TEXT or BLOBs which may have lots of data in,
> should go at the end.  Because you don't want SQLite to have to fetch all
> that data from storage just to get at the column after it.
>
> To be pedantic SQLite does not need to "fetch" all of the data from
> strorage before a needed column, it just needs to be able to skip it -
> unless the data oveflows in to one or more overflow pages then it will
> need to fetch each page until it reaches the one with the data in it.
> If the complete row is held in one page and your query just needs the
> last column - SQLite just needs to know the size of all of the data
> that preceedes the column you want. There is still the overhead of
> decoding every serial type before the column you require.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

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)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
>
Sorta, kinda, but not really.  SQLite does not use a traditional data
compression algorithm in storing row data, but it does "pack" rows into a
compact format (including variable size integers).  As such, a row's worth
of data, as stored in the raw database, acts very similar to a compressed
block of data... you have to read it from the start and can't directly jump
to a field in a middle of it.

This is the issue with column ordering; the data engine will only read and
unpack the columns it needs, but it has to read and unpack the columns in
the order they're defined until it gets all the columns it needs.  This
makes it generally better to put more frequently accessed and/or smaller
values at the start of a row.

 -j
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 writes will be larger, because 
some blocks are touched more than once.  This is especially true if there are 
large indexes.

2) The new file is then copied back to the original file, one block at a time.  
This requires 1x (final) read and 1x write (final).

3) Except, to make the write-back ACID safe, each block of the original 
database needs to be copied during the write-back process, which requires 
another 1x read (final) and 1x write (final) in journal mode.  WAL numbers are 
similar, if not higher.

In other words, in the ideal case you’re going to have a bare minimum of 3x 
final writes and 2x final + 1x original reads, but even on a freshly VACUUMed 
database, you’ll never see ideal numbers— especially if there are indexes… 
indexes are rebuilt by insertion, so if the source table data is not really in 
order, that can require a lot of data shuffling (i.e. extra read/writes).


In a similar note, SQLite typically requires about ~2x final size of free 
storage space to complete a VACUUM.


There are a number of ways to improve this.  Most of the I/O is in the 
write-back process, which is required for ACID proof VACUUM transactions.  In 
2010 I proposed a “VACUUM TO” command that would VACUUM one database file to a 
new database file, essentially making a copy.  This would only require 1x 
original reads, and ~1x+ final writes, and only 1x final free space.  The 
disadvantage is that you end up with a new file that would require closing all 
connections (including those in other applications) and re-opening them.  
SQLite also does not trust OS filesystem commands (such as renaming a new file 
over and old one) to operate in any type of transaction/rollback safe way, so 
it avoids them.  There seems to be a number of times when that’s an acceptable 
alternative, however.

See:

http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg87972.html
http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg50941.html


  -j, author of “Using SQLite”, O’Reilly Media



On Oct 1, 2016, at 3:27 PM, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

> Hello !
> 
> I'm using sqlite (trunk) for a database (see bellow) and for a final database 
> file of 22GB a "vacuum" was executed and doing so it  made a lot of I/O ( 
> 134GB reads and 117GB writes in 2h:30min).
> 
> Can something be improved on sqlite to achieve a better performance ?
> 
> The data is public available just in case it can be useful to perform tests.
> 
> Cheers !
> 
> 

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Aug 11, 2016, at 12:53 AM, Michael Falconer <michael.j.falco...@gmail.com> 
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 manipulation functions to build your query
>> string, you’re very very very very likely doing it wrong.
>> 
> 
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc. So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness.

If the values are truly 100% application derived, with no source or root 
material
from any user-generated inputs, config files, etc., then you’re good… assuming
you still quote correctly, and so forth.

The thing is, that’s rarely the case, and as the program changes and evolves, 
it is
almost inevitable that user-driven values make it into the statements.   Yes, 
that’s a
broad, sweeping, generalization, and you know your specific development needs
best.  But remember that SQL injection attacks are essentially impossible if
you correctly use bound parameters, yet it is also considered one of the top
10 security vectors.  In 99% of cases, they're really easy to avoid, and yet
they’re all over the place.  There is a lesson in caution in that.


> It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.

Be aware that bound parameters are for *values* only.  You cannot bind
identifiers, which includes database names and table names.  From what
I gathered in the rest of this discussion, that might be an issue in your case.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Aug 10, 2016, at 9:21 PM, Michael Falconer <michael.j.falco...@gmail.com> 
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 convenient in some
> circumstances where the sql injection problem is not an issue.
> 
> Ok I say it is not an issue, but am I right. I am no security expert and
> have often been surprised at some of the hack techniques used over the
> years. The sql injection issue as far as I can tell depends on where the
> offending sql originates, but don't hesitate to contradict that assumption
> if you believe it is wrong.

That’s the basic theory, but even knowing that, most people get it wrong.
In short, if you’re using string manipulation functions to build your query
string, you’re very very very very likely doing it wrong.

> In a scenario where the sql supplied to the callback routine is application
> generated or indeed application constant based does the sql injection
> threat disappear?

Yes.  The threat is when you start to use sprintf() to built your query strings.
Even more so if some of those inputs can trace their origin to user generated
values.

> user supplied sql via arguments, with only database name and table name
> required from the user. This would appear to be immune to that technique or
> am I misguided? I'm never certain when it comes to security stuff, I hate
> it.

You are misguided.  The whole idea behind injections is that you can alter that
database name so that it is much more than a database name.  If you can’t
understand the whys of it, you can’t defend against it.  And that’s important
in this case, since you can’t use bound parameters for database names or
table names.  Switching to _prepare() won’t help in this specific case because
you have no choice but to build the query from string primitives.


Another issue with sqlite_exec() is that all the values are returned as strings.
You have no idea what the type of the field is, and if it is a non-string value,
it is converted to a string before the callback is called.  This can be a big 
issue
for many designs.

> In a similar vein I noted in an O'Reilly publication it mentioned that the
> exec method was semi depracated and should be avoided. I wondered what the
> view of the SQLite dev crew was. and if there were any plans in the future
> to drop the exec function? In light of the teams focus on backward
> compatibility I suspect there are no such plans but I thought I'd ask
> anyway just to be sure. Thanks in advance for any helpful comments.

  -j  (Author, Using SQLite).


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Logo

[Jay Kreibich]

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, 2016 at 2:30 PM, Zsbán Ambrus  wrote:

> On Fri, Jun 24, 2016 at 2:03 PM, R.A. Nagy  wrote:
> > I am putting together a commercial training for SQLite. I would like to
> use
> > the SQLite logo - as seen on the website - on the cover of the materials.
>
>
> SQLite has a logo...
>
> wow.  I didn't notice that before.  I just checked the homepage
> "http://sqlite.org/;, and it shows a feather in the corner that looks
> basically the same as the Apache logo.  Is there a reason why they're
> so similar?  I mean, they're both for software, so it can be confusing
> for people who (unlike me) actually pay attention to logos.
>
> Ambrus
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple in-memory database table query

[Jay Kreibich]

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 "aliases" to 
> just one memory DB.
> 
> And I was also confused by something we did a few weeks ago, which is to have 
> several named
> memory DBs, and connecting several times (as independent connections) to one 
> of those mem DB's by name.
> I believe this use case is only possible with URI style file type. All 
> in-process of course. Am I wrong on that too? ?DD

Yea, pretty sure you can?t do that.  Generally speaking, no matter how they are 
created, a given in-memory database has one and only one connection.  You 
cannot, for example, use a URI ?filename? with mode=memory to open the same 
in-memory database more than once (I assume that?s what you mean by ?by 
name??).  For example:


$ ./sqlite3
SQLite version 3.8.4.2 2014-03-26 18:51:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db1;
sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db2;
sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db3;
sqlite> .databases
seq  name file  
---  ---  --
0main   
2db1
3db2
4db3
sqlite> CREATE TABLE main.t_main ( c );
sqlite> CREATE TABLE db1.t_db1( c );
sqlite> CREATE TABLE db2.t_db2( c );
sqlite> select * from main.sqlite_master;
table|t_main|t_main|2|CREATE TABLE t_main ( c )
sqlite> select * from db1.sqlite_master;
table|t_db1|t_db1|2|CREATE TABLE t_db1( c )
sqlite> select * from db2.sqlite_master;
table|t_db2|t_db2|2|CREATE TABLE t_db2( c )
sqlite> select * from db3.sqlite_master;
sqlite> 

You can see that even though I?ve opened the same ?file:data.db?mode=memory? 
database more than once, it is actually three distinct databases.  I?m pretty 
sure that when mode=memory, the path/filename are ignored.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Multiple in-memory database table query

[Jay Kreibich]

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 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 database, you?ll need to open the additional
>> databases using the ATTACH DATABASE command.  Just open an in-memory
>> database first, then run:
>> 
>> ATTACH DATABASE ?:memory:? AS db2
>> 
> 
> To attach several memory DBs, it's more like
> 
> ATTACH DATABASE "mem1?mode=memory" as db1;
> ATTACH DATABASE "mem2?mode=memory" as db2;
> etc... ?DD


There is no requirement to use the URI style file type, you can just re-opening 
the special value ?:memory:?.


$ ./sqlite3
SQLite version 3.8.4.2 2014-03-26 18:51:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ATTACH DATABASE ":memory:" AS db2;
sqlite> ATTACH DATABASE ":memory:" AS db3;
sqlite> ATTACH DATABASE ":memory:" AS db4;
sqlite> ATTACH DATABASE ":memory:" AS db5;
sqlite> .databases
seq  name file  
---  ---  --
0main   
2db2
3db3        
4db4
5db5 



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Multiple in-memory database table query

[Jay Kreibich]

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 database, you?ll need to open the additional databases 
using the ATTACH DATABASE command.  Just open an in-memory database first, then 
run:

ATTACH DATABASE ?:memory:? AS db2

See https://www.sqlite.org/lang_attach.html for more details.  In specific:

The filename for the database to be attached is the value of the expression 
that occurs before the AS keyword. The filename of the database follows the 
same semantics as the filename argument to sqlite3_open() and 
sqlite3_open_v2(); the special name ":memory:" results in an in-memory database 
and an empty string results in a new temporary database.

Note that to run a query across databases (regardless of the type of database), 
the additional databases must be opened with ATTACH.  You cannot run queries 
across databases if you use sqlite3_open*() for anything except the first 
database.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Working with booleans

[Jay Kreibich]

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 compact representation in the file format.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Last time analyze was ran

[Jay Kreibich]

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.  The reason why it's important is that I have a 
>> script that runs every week, but lately, it appears as if it is failing, but 
>> the log files say it is running.  However, the searches are getting slower 
>> and slower.
> 
> You should not need to run ANALYZE very often.  Perhaps once a year.  The 
> only exceptions are when your database schema changes or if the entire nature 
> of the data in it changes.
> 



Yeah? the last time VACUUM was run seems like a much more useful data point.  
Or the number of database edits made since the last run, which is likely a more 
useful metric.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] In-Memory DB cache_size

[Jay Kreibich]

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  
wrote:

> I check the default cache_size of a In-Memory DB and it's 2000
> Do you think for that kind of DB I can put cache_size to 0 like default for 
> TEMP DB? or you think it's better leave 2000?
> Just wandering if it's correct to have ram cache of a In-Memory DB
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] SQLite Pronunciation

[Jay Kreibich]

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 
term ?ess cue ell lite? (technically doubling the ?L?).

https://www.youtube.com/watch?v=Jib2AmRb_rk

When writing the book ?Using SQLite,? I actually had a rather long discussion 
with my editor about this, since the pronunciation affects the choice of words 
when talking about ?an ess-cue-ell lite database? vs ?a sequel-lite database? 
(?an? vs ?a?).  Knowing the development team tends to pronounce the letters, I 
went with ?an.?

  -j



On Mar 16, 2016, at 1:38 PM, danap at dandymadeproductions.com wrote:

> Hello,
> 
> Please grant me some leeway here since as someone who has
> not been in an academic school for years and is mainly self
> taught. I have Mainly deriving information from reading
> manuals and occasionally viewing some videos.
> 
> Maybe I'm wrong, but according to Wikepedia SQLite appears
> to be pronounced the same has it is spelled,
> (sikwl.lat).
> Maybe not a long A there perhaps.
> 
> Where as I first heard Microsoft's MSSQL pronounce (sequent),
> which I have also heard in academic videos by professors.
> Following that logic, SQLite, (sequent.light)?
> 
> Dana Proctor
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Recover corrupt database

[Jay Kreibich]

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 sure you?re not breaking one of the rules; if the database is inside 
another database, I question how locks and matched auxiliary files (journal, 
for example) are being handled.

https://www.sqlite.org/howtocorrupt.html

  -j


On Jan 27, 2016, at 7:47 AM, J Decker  wrote:

> I was wondering if the backup api (sqlite3_backup_init, et al. ) will
> help to recover a corrupt DB?  All the other mechanisms are an export
> and re-import into a new DB...
> 
> The database exists in a encrypted database that it's really difficult
> to get the keys outside of the program and I don't look forward to
> doing something that iterates through sqlite_master to get all the
> tables do all the selects and re-insert... so I browsed the API and
> found the _backup_ stuff.. but; I'm starting to think that it will
> just be a faithful page replication and the result will still be
> corrupted.
> ( I don't know what the corruption is)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] website documentation wording

[Jay Kreibich]

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 English QUEry 
Language), but it was changed to SQL right before launch due to trademark 
issues.  My guess is, at the time, "ess-cue-ell,? was preferred since there was 
a need to differentiate it from the existing trademark.

 -j, who had Eggplant Normal Form (baked) for lunch today



On Dec 4, 2015, at 12:08 PM, Keith Medcalf  wrote:

> 
> Well, a Gartner Report paid for by Microsoft, which said that if you 
> pronounced it "ess queue ell" you were labelling yourself as a professional 
> programmer who understood relational database technologies, had probably used 
> them since the 1970's or before, and belonged in a dinosaur pen.  
> 
> On the other hand, the modern "buzzword compliant" pronunciation for "mere 
> coders" that had no knowledge of how relational database systems worked and 
> could not distinguish Boyce-Codd Normal Form from an Eggplant was "sequel".
> 
>> 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  wrote:
>> 
>>> Tangeant off this note, kind of a history question.  "an SQLite".  I
>>> personally would write "a SQL" because it is "sequel" to me.  When did
>>> SQL--sequel become SQL--ess queue ell?  I always remember it as being
>>> sequel, and it rolls off the tongue easier.  And as sequel, it would be
>> "a
>>> SQLite".
>>> 
>>> Happy Holidays, all.
>>> dvn
>>> 
>> 
>> --
>> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>> 
>> "Intelligence is like underwear: it is important that you have it, but
>> showing it to the wrong people has the tendency to make them feel
>> uncomfortable." -- Angela Johnson
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] website documentation wording

[Jay Kreibich]

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  wrote:

> Tangeant off this note, kind of a history question.  "an SQLite".  I
> personally would write "a SQL" because it is "sequel" to me.  When did
> SQL--sequel become SQL--ess queue ell?  I always remember it as being
> sequel, and it rolls off the tongue easier.  And as sequel, it would be "a
> SQLite".
> 
> Happy Holidays, all.
> dvn
> 

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

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:  Whereas Mr. Jay Kreibich has
> exhibited detailed knowledge of the use and inner workings of SQLite
> by publishing an outstanding textbook on that subject, we do hereby
> confirm unto him the title of
> 
>Certified SQLite Professional
> 
> with all the rights, honors, and duties thereunto appertaining.  In
> witness whereof, the electronic signature of the BDLF of SQLite is
> hereto subscribed.  Given in Charlotte on this the tenth day of
> November and in the year of our Lord two thousand and fifteen.


w00t!  Time to update my resume!

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

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 maintain data in a 
> database-like structure.  The specification boiled down to "Implement a 
> simple SQL engine with an API like [this].".
> 
> Unfortunately all the leading browser publishers implemented this happily and 
> quickly.  "Unfortunately" because they all did it by including SQLite in 
> their project.  This is a problem because the only way to ensure 
> compatibility in that case is for the web consortium to include the full 
> specification of SQLite inside the specification for a compatible web 
> browser.  Otherwise people would start doing things like calling PRAGMA 
> commands from web pages, and then an unofficial standard would evolve which 
> required PRAGMA commands to be supported from the web API.
> 
> So they did.  And it failed.  And that's where we are today.

Sorta-kinda.  The standard was called WebSQL, and it was an extension to 
client-side data stores.  W3C setup a working group and sent out a request for 
implementations, but as you said, everyone in the working group implemented the 
standard by basically wrapping SQLite.  The standard was then killed due to 
lack of unique implementations.  As you said, there was concern that without 
independent implementations, aspects specific to SQLite would ?leak? into the 
standards and commonly used APIs, so the standard was killed.

The official working group notes are here:

http://www.w3.org/TR/webdatabase/

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

> 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 Framework, LINQ, the registry, Access, SQL 
> Server Express?

I want to be a Certified SQLite Professional.

  -j

[sqlite] Simple Math Question

[Jay Kreibich]

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 needs, so mostly nobody wanted the precise one.  The
> obvious choice at that point was to reclaim that silicon space to make
> everything else faster, and we ended up here.

Also the fact that most older CISC chips did BCD instructions in microcode, in 
a time when a non-trivial amount of software was written in assembly or with 
very crude compilers.

With modern, RISC-ish systems, it is often almost as fast to do such things in 
application code, especially with a good compiler that can optimize the 
lower-level instructions for a specific core design.

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Simple Math Question

[Jay Kreibich]

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 BCD 
instructions.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] busy_timeout() not working always?

[Jay Kreibich]

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 
there is lock contention. If SQLite determines that invoking the busy handler 
could result in a deadlock, it will go ahead and return SQLITE_BUSY to the 
application instead of invoking the busy handler. Consider a scenario where one 
process is holding a read lock that it is trying to promote to a reserved lock 
and a second process is holding a reserved lock that it is trying to promote to 
an exclusive lock. The first process cannot proceed because it is blocked by 
the second and the second process cannot proceed because it is blocked by the 
first. If both processes invoke the busy handlers, neither will make any 
progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping 
that this will induce the first process to release its read lock and allow the 
second process to proceed.

Basically, if SQLite detects a dead-lock situation, that no amount of waiting 
will resolve, the busy handlers will immediately return an SQLITE_BUSY.  The 
only way to resolve this situation is for the connection to rollback its 
current transaction, release all locks currently being held, and allow the 
other connection to continue.

In short, locking is hard, there is no easy way out.

  -j



On Jul 9, 2015, at 6:45 AM, Constantine Yannakopoulos  
wrote:

> ?Hello all,
> 
> I have a multithreaded server that may perform intense database actions
> (both reads and writes) concurrently from different threads against two
> database connection (sqlite3* objects) to the same database file. The
> database connections are in shared-cache mode and I have implemented the
> unlock notification mechanism (sqlite3_unlock_notify()) in order to
> facilitate table-level locking according to instructions given here:
> https://www.sqlite.org/unlock_notify.html. I also use sqlite_busy_timeout()
> to set an internal retry mechanism with a reasonably large timeout value in
> order to avoid SQLITE_BUSY errors.
> ??
> 
> While in rudimentary tests the whole mechanism seems to work as expected,
> in normal server operation under heavy load some statements (different ones
> each time) seem to return SQLITE_BUSY immediately, which seems to indicate
> that busy_timeout() dos not work in some cases. And this does not change
> even if I set the busy timeout to a very large value, e.g. 0x7FFF
> ?, and calling PRAGMA busy_timeout reports the expected value?
> . Also, it doesn't seem to have a difference if the database is in WAL or
> DELETE mode.
> 
> What may I be doing wrong? Or is there something regarding busy_timeout() I
> have missed? A scenario where it is bypassed? Unfortunately this behaviour
> appears only under heavy load in customer sites and is very hard to
> reproduce and debug.
> 
> SQLite version is 3.8.10.1, OS is Win7 x64 and the server is compiled in
> 32-bit and SQLite is dynamically linked via separate library (sqlite3.dll),
> locking_mode is NORMAL, synchronous is FULL and threading mode is
> SERIALIZED.
> 
> Thank you in advance.
> --
> Constantine.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] This mailing list seems to be public

[Jay Kreibich]

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
> http://comments.gmane.org/gmane.comp.db.sqlite.general/95119
> http://osdir.com/ml/general/2015-06/msg09791.html
> 
> Is this official, or what?


I?m not sure about official, but it is known.  There are several web interfaces 
to the mailing list, and they?re referenced from the SQLite site:

http://www.sqlite.org/support.html#mailinglists

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

[Jay Smith]

Nicolas
there are other people who also receive this mail.
if you have code that will help someone else.
Please post the information Mr Hipp requested.

Jay

On Tue, Apr 28, 2015 at 5:08 PM, Nicolas Boullis  wrote:

> Hi,
>
> On Mon, Apr 20, 2015 at 06:25:21AM -0400, Richard Hipp wrote:
> >
> > Please send us:
> >
> > (1) The output of "SELECT sqlite_source_id();"
> >
> > (2) The complete text of you SELECT statement.
> >
> > (3) The output of the ".fullschema" command from the most recent
> > version of the sqlite3.exe command-line shell run on your database
> > file after it is ANALYZE-ed.
>
> Sorry for the very late answer, I?ve been very busy lately.
>
> I investigated a little further, measuring the time taken by each SELECT
> request that was run, and then using EXPLAIN QUERY PLAN to understand
> why two of them were so slow. Knowing the data, I added a few indices,
> and now the whole program runs in about 1 second, much better than what
> I had before.
>
> As far as I am concerned, everything is fine, now. Many thanks for your
> help.
>
> Now, if you are realy interested by that surprisingly-good performance I
> had without indices and without running ANALYZE, I may try to provide
> you with more details.
>
>
> Thanks to all,
>
> --
> Nicolas Boullis
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Bill & Steve
Thanks guys
I am trying to learn and  make some progress with the information you both
provided.

On Mon, Apr 27, 2015 at 9:57 AM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> Jay,
>
> I don't do VB.Net development but it should be very similar to C#.Net in
> Visual Studio.  Try to follow these steps:
>
> 1. Add a Dataset Item to your project.  I think you have done this already.
> Once the Dataset is created you will get a blank dataset designer, create a
> connection to your Sqlite database on the server explorer panel.  Drag the
> tables/views you want into the dataset designer.
> 2. Once you save that dataset, you will see the Dataset and corresponding
> Table Adapter in your project components list on the toolbox
> 3. Drag your dataset and table adapter onto your form that contains the
> control that you want to bind to
> 4. In the toolbox under data controls, drag a Binding Source to your form
> 5. Select the binding source and on the properties, set the DataSource to
> your dataset added in step 1; set the data member to the table or view
> added
> to your dataset in step 1
> 6. For each form control you want to bind to a column from your dataset,
> select the control on properties under databindings, click the attribute
> (like Text for a textbox) and drill into the Binding Source created in step
> 4, then the data table and finally the column you want.
>
> Like I said, this is how you do it in C#.net and it is probably very
> similar
> if not exactly the same way for VB.net.
>
> Steve
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Drago,
> William @ CSG - NARDA-MITEQ
> Sent: Monday, April 27, 2015 8:38 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] building SQLite DLL with Visual C++
>
> I'm afraid I can't help you with that, Jay. I don't use data bindings and
> all that other built-in visual studio db stuff because performance is slow
> and it's hard to maintain. I just read from the db into a data table. From
> there you can do whatever you want with the data.
>
> Search on line for VB.NET examples and I'm sure you'll find some examples.
>
> Good luck,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > Sent: Sunday, April 26, 2015 3:44 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> >
> > Thanks Bill
> >
> > That worked
> >
> > BUT
> >
> > In my form when I go to "DataBindings" I get a Pop-up to "Add Project
> > Datasource" then the 2 screens to choose Datasource Type( I select
> > Database) then I choose DataSet. This is where it breaks down."Choose
> > Data Connection there are 4 choices "Access" and 3 server types, then
> > there is  which I select. And that leaves me with a single
> > "Data provider"
> > Microsoft OLE DB simple provider  This connection works, but is it
> > correct?
> > The connection is MSDAOSP
> >
> > Then I get to the screen "Choose your database objects" and an ERROR
> >
> > "Selecting Objects of type 'Table' is not supported.
> >
> > Then we get to the next part of the problem. I am in my late 70's and
> > have not done any SQL for over 15 years ( I was pretty good at one
> > time.) Where do I go from here.
> >
> > Jay
> >
> > On Sat, Apr 25, 2015 at 5:12 PM, Drago, William @ CSG - NARDA-MITEQ <
> > William.Drago at l-3com.com> wrote:
> >
> > > I don't know what error you're getting so I can't offer specific
> > help.
> > >
> > > This is what works for us:
> > >
> > > Open your project in Visual Studio.
> > >
> > > Click Tools > NuGet Package Manager > Manage NuGet Packages for
> > Solution...
> > >
> > > Select Online > nugget.org
> > >
> > > In the search box enter system.data.sqlite
> > >
> > > A list of packages should be displayed after a short wait.
> > >
> > > Select System.Data.SQLite Core (x86/x64) or, if you need LINQ and
> > > Entity Framework support, select System.Data.SQLite (x86/x64), then
> > > click Install (we use Core here)

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Bill

That worked

BUT

In my form when I go to "DataBindings" I get a Pop-up to "Add Project
Datasource" then the 2 screens to choose Datasource Type( I select
Database) then I choose DataSet. This is where it breaks down."Choose Data
Connection there are 4 choices "Access" and 3 server types, then there is
 which I select. And that leaves me with a single "Data provider"
Microsoft OLE DB simple provider  This connection works, but is it correct?
The connection is MSDAOSP

Then I get to the screen "Choose your database objects" and an ERROR

"Selecting Objects of type 'Table' is not supported.

Then we get to the next part of the problem. I am in my late 70's and have
not done any SQL for over 15 years ( I was pretty good at one time.) Where
do I go from here.

Jay

On Sat, Apr 25, 2015 at 5:12 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> I don't know what error you're getting so I can't offer specific help.
>
> This is what works for us:
>
> Open your project in Visual Studio.
>
> Click Tools > NuGet Package Manager > Manage NuGet Packages for Solution...
>
> Select Online > nugget.org
>
> In the search box enter system.data.sqlite
>
> A list of packages should be displayed after a short wait.
>
> Select System.Data.SQLite Core (x86/x64) or, if you need LINQ and Entity
> Framework support, select System.Data.SQLite (x86/x64), then click Install
> (we use Core here)
>
> After installation you should see a little green checkmark next to the
> package you selected.
>
> Click Close and you're done. Check on line for VB.NET code samples using
> SQLITE.
>
> Good luck,
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > Sent: Saturday, April 25, 2015 3:50 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> >
> > Hi Bill
> >
> > I have been trying for 2 days and I am totally lost.
> >
> > I have NuGet Package Manager. I don't know how to use it.
> >
> > From the  PM>   System.Data.SQLiteI get an error.
> >
> > Can you be a little more specific.
> >
> > Jay
> >
> > On Thu, Apr 23, 2015 at 4:08 PM, Drago, William @ CSG - NARDA-MITEQ <
> > William.Drago at l-3com.com> wrote:
> >
> > > > -Original Message-
> > > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > > > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > > > Sent: Thursday, April 23, 2015 3:47 PM
> > > > To: General Discussion of SQLite Database
> > > > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> > > >
> > > > Thank you Adam for responding to my post. I have the windows
> > > > binaries downloaded.
> > > >
> > > > At this point I am just following the instructions in the book. And
> > > > I really am not sure what I need the dll for.
> > > >
> > > > Here's the scenario. I have created a program in vb2012. The
> > program
> > > > stores less than 20 fields of data. I am currently saving the data
> > > > in a html format. I just recently discovered SQLite. I am now in
> > the
> > > > process of changing over to a database to store data. I studied SQL
> > > > and Oracle
> > > > 10 years ago. I have almost completed the database for the project.
> > > > My problem is how to integrate the SQL db into my VB program.
> > >
> > > Your subject line says C++, but the line above says VB, so is your
> > > program written in C++ or VB?
> > > If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager,
> > > then search for System.Data.SQLite Core).
> > >
> > > -Bill
> > >
> > >
> > > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> > > any attachments are solely for the use of the addressee and may
> > > contain information that is privileged or confidential. Any
> > > disclosure, use or distribution of the information contained herein
> > is
> > > prohibited. In the event this e-mail contains technical data within
> > > the definition of the Int

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Hi Bill

I have been trying for 2 days and I am totally lost.

I have NuGet Package Manager. I don't know how to use it.

>From the  PM>   System.Data.SQLiteI get an error.

Can you be a little more specific.

Jay

On Thu, Apr 23, 2015 at 4:08 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > Sent: Thursday, April 23, 2015 3:47 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> >
> > Thank you Adam for responding to my post. I have the windows binaries
> > downloaded.
> >
> > At this point I am just following the instructions in the book. And I
> > really am not sure what I need the dll for.
> >
> > Here's the scenario. I have created a program in vb2012. The program
> > stores less than 20 fields of data. I am currently saving the data in a
> > html format. I just recently discovered SQLite. I am now in the process
> > of changing over to a database to store data. I studied SQL and Oracle
> > 10 years ago. I have almost completed the database for the project.  My
> > problem is how to integrate the SQL db into my VB program.
>
> Your subject line says C++, but the line above says VB, so is your program
> written in C++ or VB?
> If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager, then
> search for System.Data.SQLite Core).
>
> -Bill
>
>
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Bill
I got in a hurry and was trying to follow the book.
I have never used C++ so the subject line is wrong.
I am now looking into NuGet and will get back if I need more help (and I am
sure I will.)
Jay


On Thu, Apr 23, 2015 at 4:08 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > Sent: Thursday, April 23, 2015 3:47 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> >
> > Thank you Adam for responding to my post. I have the windows binaries
> > downloaded.
> >
> > At this point I am just following the instructions in the book. And I
> > really am not sure what I need the dll for.
> >
> > Here's the scenario. I have created a program in vb2012. The program
> > stores less than 20 fields of data. I am currently saving the data in a
> > html format. I just recently discovered SQLite. I am now in the process
> > of changing over to a database to store data. I studied SQL and Oracle
> > 10 years ago. I have almost completed the database for the project.  My
> > problem is how to integrate the SQL db into my VB program.
>
> Your subject line says C++, but the line above says VB, so is your program
> written in C++ or VB?
> If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager, then
> search for System.Data.SQLite Core).
>
> -Bill
>
>
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Adam
I will take a look
Jay

On Thu, Apr 23, 2015 at 4:19 PM, Adam Devita  wrote:

> Good day,
> I don't work in VB, so I can't help you in specifics on that. A quick
> search on a search engine  pops up some videos on how to use sqlite in
> a Visual Basic project.  The archives of this list have a lot of
> questions where people are introduced to open, prep sql, (bind), step,
> fetch, finalize, close. It is often a good idea to specify which book
> you are working through for a question relating to a published
> tutorial, as some future person may be reading the same book and could
> find the thread helpful.  Some people on the list likely have a copy
> of the same book on their shelf.  Someone on  this list might even be
> the author.
>
> Adam
>
>
>
>
> On Thu, Apr 23, 2015 at 3:47 PM, Jay Smith  wrote:
> > Thank you Adam for responding to my post. I have the windows binaries
> > downloaded.
> >
> > At this point I am just following the instructions in the book. And I
> > really am not sure what I need the dll for.
> >
> > Here's the scenario. I have created a program in vb2012. The program
> stores
> > less than 20 fields of data. I am currently saving the data in a html
> > format. I just recently discovered SQLite. I am now in the process of
> > changing over to a database to store data. I studied SQL and Oracle 10
> > years ago. I have almost completed the database for the project.  My
> > problem is how to integrate the SQL db into my VB program.
> >
> > On Wed, Apr 22, 2015 at 10:16 AM, Adam Devita 
> wrote:
> >
> >> Good day,
> >>
> >>
> >> Why are you compiling a dll instead of using the pre-compiled windows
> >> binaries at http://www.sqlite.org/download.html?
> >>
> >> Are you adding some sort of extra wrapper?
> >>
> >> Why are you not adding the amalgamated c source in your project (turn
> >> off use pre-compiled headers for that file) ?
> >>
> >> If you insist on creating your own dll, try the vs wizard to create a
> >> dll project, then add code to it.
> >>
> >> regards,
> >> Adam DeVita
> >>
> >>
> >> On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik 
> >> wrote:
> >> > On 4/21/2015 11:01 AM, Jay Smith wrote:
> >> >>
> >> >> Before I sent the last message I had signed up to become a user.
> >> >> My previous message was bounced.  WHY
> >> >
> >> >
> >> > I, for one, have received both your original and this new message.
> >> > --
> >> > Igor Tandetnik
> >> >
> >> >
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users at mailinglists.sqlite.org
> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >>
> >> --
> >> --
> >> VerifEye Technologies Inc.
> >> 151 Whitehall Dr. Unit 2
> >> Markham, ON
> >> L3R 9T1
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Igor
the only response I got to the first message was "it has been bounced"


On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik  wrote:

> On 4/21/2015 11:01 AM, Jay Smith wrote:
>
>> Before I sent the last message I had signed up to become a user.
>> My previous message was bounced.  WHY
>>
>
> I, for one, have received both your original and this new message.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thank you Adam for responding to my post. I have the windows binaries
downloaded.

At this point I am just following the instructions in the book. And I
really am not sure what I need the dll for.

Here's the scenario. I have created a program in vb2012. The program stores
less than 20 fields of data. I am currently saving the data in a html
format. I just recently discovered SQLite. I am now in the process of
changing over to a database to store data. I studied SQL and Oracle 10
years ago. I have almost completed the database for the project.  My
problem is how to integrate the SQL db into my VB program.

On Wed, Apr 22, 2015 at 10:16 AM, Adam Devita  wrote:

> Good day,
>
>
> Why are you compiling a dll instead of using the pre-compiled windows
> binaries at http://www.sqlite.org/download.html?
>
> Are you adding some sort of extra wrapper?
>
> Why are you not adding the amalgamated c source in your project (turn
> off use pre-compiled headers for that file) ?
>
> If you insist on creating your own dll, try the vs wizard to create a
> dll project, then add code to it.
>
> regards,
> Adam DeVita
>
>
> On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik 
> wrote:
> > On 4/21/2015 11:01 AM, Jay Smith wrote:
> >>
> >> Before I sent the last message I had signed up to become a user.
> >> My previous message was bounced.  WHY
> >
> >
> > I, for one, have received both your original and this new message.
> > --
> > Igor Tandetnik
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Dear sir
Before I sent the last message I had signed up to become a user.
My previous message was bounced.  WHY
copy of previous message:

I am new to SQLite. I have a book I am learning from and I am a novice.

I have a fairly good handle on SQL

I want to include SQLite in my VS2012 program.

I am attempting to run " LIB  /DEF:sqlite3.def "

I get error This error "LIB is not recognized as an internal or external
command."

What am I doing wrong?

On Mon, Apr 20, 2015 at 10:21 AM, Jay Smith  wrote:

> I am new to SQLite. I have a book I am learning from and I am a novice.
>
> I have a fairly good handle on SQL
>
> I want to include SQLite in my VS2012 program.
>
> I am attempting to run " LIB  /DEF:sqlite3.def "
>
> I get error This error "LIB is not recognized as an internal or external
> command."
>
> What am I doing wrong?
>
> Jay
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

I am new to SQLite. I have a book I am learning from and I am a novice.

I have a fairly good handle on SQL

I want to include SQLite in my VS2012 program.

I am attempting to run " LIB  /DEF:sqlite3.def "

I get error This error "LIB is not recognized as an internal or external
command."

What am I doing wrong?

Jay

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

[Jay Kreibich]

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.
>> 
>> Imagine my surprise when what I actually typed (without checking,
>> obviously!) was something like this:
>> 
>> update swimmeet set end="1991-04-21" where 4193;
>> 
> 
> The sqlite3 command-line shell program will link against a variety of
> (third-party) command-line editing libraries:
> 
> (1) GNU readline
> (2) Editline
> (3) Linenoise (https://github.com/antirez/linenoise)
> 
> Do you know which one your particular build of sqlite3 is using?
> 
> Furthermore, it seems to me that the problem you describe has much
> more to do with the command-line editing library than it does with
> SQLite, does it not?  Assuming there is a bug in the third-pary
> command-line editing library, what could SQLite do about it?
> 


Also, don?t use double quotes for strings.  SQL specifies single quotes for 
strings, double quotes for identifiers (table names, column names, etc.)

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

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:
> 


?LIKE? is not a supported virtual table index constraint.  See: 
http://www.sqlite.org/c3ref/c_index_constraint_eq.html   As such, it cannot be 
passed to a virtual table implementation, and the constraint is handled by the 
SQLite engine above the virtual table implementation.

Given that the LIKE expression is translated into an SQL function, which the 
application can override, it would be difficult for a virtual table to 
correctly implement a LIKE operation internally, while matching the exact 
functionality of the current LIKE function.

Consider a statement like this:

SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( 
vt.col1 );

If returnThisRow_CustomFunction() is a function returns a true or false based 
on? well, who knows what? there is no way for a virtual table implementation to 
understand the inter-workings of that function and pre-filter the rows.  LIKE 
is no different.



It should be noted that MATCH is a supported virtual table index constraint 
supported.  Along with the virtual table xFindFunction() function allows a 
virtual table to implement a table specific filter function.  This is how the 
FTS extensions implement searches.

Consider providing a virtual table specific MATCH function, over-ride use on 
your table with xFindFunction(), and rewrite statements using MATCH rather than 
LIKE.

See the FTS modules as examples.  You might want to start here: 
https://www.sqlite.org/fts3.html


 -j



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

On Mar 2, 2015, at 8:51 AM, Paolo Bolzoni  
wrote:

> I kinda get your point, but still we are speaking of the C interface
> all the time.

Not exactly.  SQL parameters are defined in SQL, and they?re part of the SQL 
language.  The parameter placement (and from that, their indexing and binding) 
can be considered an integral part of the SQL expression.  After all, in a 
given SQL statement, which parameter is ?parameter #3? shouldn?t really change, 
regardless of if you are using C, Python, Pascal, or some other language 
(including raw SQL at a prompt[1]).  This is more explicit when you use 
numbered or named parameters, but the same logic applies: the definition of the 
parameter enumeration and placement is more squarely in the domain of the SQL 
language, not the host access language.

[1] consider, for example, if the CLI tool sqlite3 allowed one to store 
statements and then execute them with a parameter list, perhaps taken from a 
CSV file or something.


Getting data back out, i.e. the column functions, on the other hand, is more of 
a language and database driver specific thing.  SQL doesn?t tend to enumerate 
columns, it just provides them in the order requested.  How a DB API chooses to 
return those values into a language specific construct is really up to the 
database driver and API designer.  Consider a language that has a native array 
or vector type, for example? it might make sense for an API to just return a 
whole row, rather than picking out column values one at a time, eliminating the 
whole need to index columns outside of the native language access syntax.  The 
sqlite3_exec() API kind of works that way, for example.  From that standpoint, 
it makes some amount of sense to use the C semantics, and index off zero.

Actually, I?m guessing the sqlite3_exec() function is the whole reason for 
column indexes starting at zero.  SQLite2 didn?t have prepared statements and 
ONLY had the sqlite_exec() API.  My guess is to keep the APIs as similar as 
possible, output columns are also indexed from zero.

Although? there are times when SQL does reference output columns.  For example, 
you can use a numerical reference to an output column in a GROUP BY or ORDER BY 
clause, and in those cases SQL is consistent and considers the first column to 
be ?1?, just as it does with parameters.  From that standpoint, it might make 
sense to say columns should be referenced from 1, regardless of the host 
language.

All of this is a little fuzzy and gray.  The fact they?re different is very 
unfortunate, and I think most of us would agree that if anything is ?broken?, 
it is the column access functions, not the parameters.  But regardless, it is 
what it is.

 -j



> Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.
> 
> Oh, well... I just have to wrap my mind around it. Definitely not a
> real problem, I was merely curious.
> 
> 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 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 unlucky legacy?
>>> 
>>> Seems like there was a reason for this, 11 years ago when it went in,
>>> but I cannot now call that reason to mind right this moment.  So lets
>>> just call it unlucky legacy.
>> 
>> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
>> sure it is part of the SQL standard, but it is more or less the defacto 
>> standard of SQL APIs, and might be considered part of the SQL language.
>> 
>> I assume column indexes start at 0 because of C.  Column indexes are used 
>> within the context of the C language API, so it makes some sense to use C 
>> conventions.
>> 
>> 
>> I know they?re very easy to confuse (I still do it myself), but I can kind 
>> of see why we ended up there, even if it isn?t a very strong reason.
>> 
>> -j
>> 
>> 
>> --
>> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>> 
>> "Intelligence is like underwear: it is important that you have it, but 
>> showing it to the wrong people has the tendency to make them feel 
>> uncomfortable." -- Angela Johnson
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> __

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

[Jay Kreibich]

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 unlucky legacy?
> 
> Seems like there was a reason for this, 11 years ago when it went in,
> but I cannot now call that reason to mind right this moment.  So lets
> just call it unlucky legacy.

Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
sure it is part of the SQL standard, but it is more or less the defacto 
standard of SQL APIs, and might be considered part of the SQL language.

I assume column indexes start at 0 because of C.  Column indexes are used 
within the context of the C language API, so it makes some sense to use C 
conventions.


I know they?re very easy to confuse (I still do it myself), but I can kind of 
see why we ended up there, even if it isn?t a very strong reason.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] read uncommitted data consistency

[Jay Kreibich]

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 are reading in one thread while writing in
>> another.
> 
> No.  They'll all be either one thing or the other.  In other words, even with 
> the PRAGMA set, SQLite is still transaction-safe.  It uses a lock on the 
> entire database to prevent the problem you described.
> 


No, not with columns, but it is possible with rows, depending on how the two 
treads are interacting.

SQLite rows are essentially self-contained, so any time a row is updated it is 
updated as a complete unit.  This is not true of rows within a table, however.  

Understand that READ UNCOMMITTED only applies to connections within the same 
process that are also using a shared cache (sqlite3_enable_shared_cache()).  
If, in the situation you describe, the threads are using different connections 
that are NOT using a shared cache, then the writer thread will ?see? any 
updates it has already made within the uncommitted transaction, while another 
reader thread (using a different connection) will not? it will see the data as 
it existed when the transaction was started.  This has nothing to do with READ 
UNCOMMITTED, however, that?s how it always works when not using a shared cache 
(or when not using the same connection for reads and writes).

Basically the connection context that created the transaction will see the 
actions it has performed, while all other connections will not see the changes 
until the transaction is committed (which, of course, is the whole point of 
transactions).

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Appropriate Uses For SQLite

[Jay Kreibich]

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 (like
> compilers do) that is publicly available, with the (now optional) SQL
> part producing IR, as well as any other query language
> implementations.  The LLVM project is an example of doing a design
> like this.

SQLite kind of already does this, if you consider VDBE instructions to be an IR.

I?ve often wondering how difficult it would be to put a new front-end on SQLite 
to parse Tutorial D (or some other ?true relational? language) and generate 
VDBE instructions.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

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

[Jay Kreibich]

On Feb 10, 2015, at 11:21 AM, Peter Aronson <pbaron...@att.net> 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 
3.6.x, there are a lot of things that would need to go into a revised edition… 
including a lot more examples of everything, according to reviews.  We greatly 
underestimated the number of SQLite developers that were touching SQL for the 
first time, and I would have never guessed people would have considered yet 
another SQL lesson to be so important, given that there are a million books and 
a bazillion websites on learning SQL basics.  You can literally find books on 
“SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and 
everything in-between.  That last book (or books, actually) is awesome, BTW, 
and the “Advanced SQL Programming” one should be on the shelf of every database 
programmer doing anything more advanced than an address book.

Regardless, if we do a second edition (and at this point that’s an extremely, 
exceptionally big “if”), VFS is unlikely to make the cut.  Consider that out of 
the thousands of SQLite applications and billions of installed databases, there 
are likely less than 100 production VFS modules in the whole world.  Spending a 
lot of time and pages, driving up the cost of the book, covering an extremely 
advanced and obscure topic is a poor trade-off (every page averages about a day 
to write/edit/prep, and adds about $0.10 to the price of the book).  If you 
need that level of integration and detail, working in the guts of the I/O and 
locking system, you should likely hand the task to a systems engineer that is 
familiar with the problem domain and isn’t afraid of looking through a few 
headers and examples to figure it all out.  It’s advanced, custom stuff that 
is, almost by definition, not textbook work.  It is the kind of work that 
requires digging through nitty-gritty code, documentation, and examples from 
both SQLite and your environment.  This is the kind of thing that’s learned 
from years of experience, not by reading it in a book.

That isn’t meant to be a criticism of the original poster— there is a huge 
difference between asking if anyone knows where to start looking, and asking 
for detailed step-by-step instructions.  In fact, if we did decide to put some 
information about VFS modules in a book, it would likely be a discussion of how 
the structures and APIs fit together, what they’re used for, and the types of 
things that can be done with them— exactly the kind of info you need to get 
started, but not much beyond that.  After all, what goes in those functions is 
going to be extremely dependent on the environment the VFS is trying to use.

I might say similar things about the xBestIndex() and xFilter() functions.  
While the APIs and how they are used is a tad confusing, their purpose and 
function should be reasonably straight forward to someone comfortable with 
relational data management and design.  While the book attempts to cover how 
the APIs are meant to perform their tasks (and has a six page discussion on 
their purpose and use), actually writing such a function is extremely dependent 
on understanding the virtual table being design— and the data in it. I feel it 
is something that just needs to be done by a skilled engineer, with a lot of 
detailed knowledge about the problem that’s trying to be solved.  Again, there 
aren’t any real textbook examples here; yes, I could write a contrived example, 
but if they didn’t understand from a general description, a single specific 
example is unlikely to help anyone in their specific case.  At the end of the 
day, both functions are an optimizations anyways.  You can write a functional 
virtual table without them, it might just run a tad slower.  If you really need 
that last bit of performance, you need an engineering that knows how to get 
detailed work done, and can verify the correctness of the system when they’re 
done— even if it involves a little of banging heads against walls, and cursing 
at the debugger.  That’s where the magic happens anyways.


 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

No, it does not.  Using SQLite covers Virtual Tables in great detail, but not 
VFS systems.  They’re somewhat unusual, after all.

I assume you’ve read the VFS docs: http://www.sqlite.org/vfs.html

>From there, my suggestion is to look closely at the code for the VFS structure 
>(http://www.sqlite.org/c3ref/vfs.html) and the IO Methods structure 
>(http://www.sqlite.org/c3ref/io_methods.html).  Both those structs contain a 
>bunch of function pointers for which you need to provide code.

 -j



On Feb 10, 2015, at 10:31 AM, Stephan Beal <sgb...@googlemail.com> wrote:

> 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:
> 
> http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/
> 
> resp.
> 
> http://shop.oreilly.com/product/9780596521196.do
> 
> covers VFS creation in detail with a step-by-step example.
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Jan 23, 2015, at 9:35 AM, Aldo Buratti <aldo.w.bura...@gmail.com> 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 05:39:04 -0700
> 
> that illustrated exactly the same troubles.
> In short, if you have a table named A and a temporary table named TEMP.A,
> then if you want to refer to the A table, it is strongly recommended to
> explicitly call it MAIN.A ( otherwise if you simply refer to A, you will
> pick ... TEMP.A )


For what it is worth, this behavior is documented:

https://www.sqlite.org/lang_naming.html

Database Object Name Resolution

[…] If no database is specified as part of the object reference, then SQLite 
searches the main, temp and all attached databases for an object with a 
matching name. The temp database is searched first, followed by the main 
database, followed all attached databases in the order that they were attached. 
The reference resolves to the first match found. […]


> In my opinion, and for my experience, I find this behavior
> counter-intuitive,

I will have to disagree, as will, I believe, the SQL language designers.

Temp tables are scoped to the connection that created them, while other 
databases are, in a sense, “global.”  Think of your main database like global 
variables in a program, while the temp database has objects (tables, indexes, 
views, etc.) that are “local” to the execution context, similar to variables 
defined within a function.  In almost all languages, object names within a 
tighter scope hide similarly named objects that live a larger scope, just as a 
function variable named “A” will hide a global variable with the name “A” in 
most languages.  The behavior is consistent with the vast majority of 
programming languages and programming paradigms.

Of course, the wisdom of using the same name for two different objects, even if 
they are in a different scope, is a different discussion— but it is a very 
similar discussion to programmers that reference function variables using the 
same name as an existing global variable.

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Jan 16, 2015, at 6:56 AM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/16/15, Scott Robison <sc...@casaderobison.com> 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 REGEXP
>> can be redefined.
> 
> Where did you read that MATCH and REGEXP could not be redefined?  That
> is a bug in the documentation that needs to be fixed.  They are
> undefined by default and are given meaning by redefining.  They would
> be useless if they were not redefinable.
> 


I think the OP interpreted the lack of a match() or regexp() function on the 
lang_corefunc.html page as meaning they could not be defined/redefined.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Jan 16, 2015, at 5:06 AM, Scott Robison <sc...@casaderobison.com> 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 REGEXP
> can be redefined.

They can all be (re)defined, some just happen to have default functions:

https://www.sqlite.org/lang_expr.html#like


  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

On Jan 4, 2015, at 3:31 AM, Neo Anderson <neo_in_mat...@msn.com> 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 to create a new database. The new database 
> works fine and the most surprising result is the old and new database files 
> are exactly of the same size. But running cmp old.db new.db gave me:
> 
> differ: char 27, line 1
> 
> My question is why .dump works but sqlite3 thinks the file is corrupt. 
> Attached please find old.db and new.db in a zip package. Hope this can help 
> improve sqlite. 


Are you using the same sqlite3 binary for both operations?

If a database has a table definition that an older version of SQLite does not 
understand, it will sometimes give this error.  If you’re using a newer version 
of SQLite to dump/load the files, there will be no error.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

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:
>> 
>>> 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 create table statements, attach the old database
>>> as olddb, then do:
>>> 
>>> insert into table1 select * from olddb.table1;
>>> insert into table2 select 8 from olddb.table2;
>>> 
>>> This also lets you do the create table stmts w/o indexes, and add
>>> the indexes after the inserts.  Not sure if that is faster or not
>>> for your data.
>> 
>> If you look at code for VACUUM, that’s more or less what it does…
>> only it is very smart about it, properly preserving ROWID values, as
>> well as exact table definitions, sequences, analytics, and all the
>> meta-data in the database.
> 
> Sqlite3 Backup API should work too, and covers your vacuum to file proposal.

No, the backup API does a page-by-page copy.  It’s goal is to make a 
bit-perfect copy of the original database file.  It makes no attempt to VACUUM 
the database as it is backed-up, and has no understanding of the data it is 
copying.  The backup-API does not remove empty space or re-order pages, nor 
does it allow changes to database page size and other alterations supported by 
VACUUM.  The backup API also operates outside the SQL transaction system.

VACUUM TO would be a simplified version of VACUUM that only requires 2x the 
disk space (rather than 3x) and much, much less I/O (possibly 1/3 or so).  It 
would be faster for many applications that control all access to a database, 
such as applications that use SQLite DBs as their document file format.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson <pri...@gmail.com> 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 create table statements, attach the old database as
> olddb, then do:
> 
> insert into table1 select * from olddb.table1;
> insert into table2 select 8 from olddb.table2;
> 
> This also lets you do the create table stmts w/o indexes, and add the
> indexes after the inserts.  Not sure if that is faster or not for your
> data.

If you look at code for VACUUM, that’s more or less what it does… only it is 
very smart about it, properly preserving ROWID values, as well as exact table 
definitions, sequences, analytics, and all the meta-data in the database.

The “copy to new database” accounts for the 2x size requirement.  In the case 
of VACUUM, the data is then copied from the fresh DB back to the original 
database file in a transaction-safe way; this touches every page in the 
original file, requiring a rollback log of some sort (journal or WAL)— and that 
accounts for the 3x space requirement.

While you can roll your own, the method you propose is not transaction safe and 
is difficult to use in a production environment unless you know you can shut 
down all services using the DB and force them to re-open connections to the new 
file.  That’s true of some applications, but far from all of them.

About five years ago I proposed a VACUUM TO  variant of VACUUM that would 
more or less do what you’re talking about, without the copy-back operation.  It 
would only require 2x the disk space, and be much faster for applications that 
were able to use it.  I still think it would be a useful, low-cost feature.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg50941.html

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

On Jan 5, 2015, at 8:43 AM, Nelson, Erik - 2 <erik.l.nel...@bankofamerica.com> 
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 index. //...
>>> Probably running out of space wherever temp files are created.
>> 
> 
> I ran into this a while ago- used pragma temp_store_directory

Which is, for good or bad, depreciated.  Not sure why.

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table implementation details

[Jay Kreibich]

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 nothing for your 
function to do… the SQLite engine will rollback all the tables and put them 
into their prior state all on its own.

 -j


On Nov 20, 2014, at 3:26 AM, Alessandro Marzocchi 
<alessandro.marzoc...@gmail.com> wrote:

> Good day,
> I'm implementing a serialization/redo engine based on sqlite. My
> implementation (particulars omitted) will work by adding a layer of virtual
> tables over real sqlite data tables plus a physical table to store the list
> of modifications made to them (to implement undo functionality). Also a
> callback to the real object is provided to notify when values are changed
> (eg for a rollback or undo). My problem comes on implementing rollback
> function. When rollback is called my thought is to restore old data directly
> from the data table or the undo table. For example (still simplified)
> 
> 1) Virtual table: dataObjectA(id INTEGER PRIMARY KEY, Foo INTEGER)
>Data table: _dataObjectA(id INTEGER PRIMARY KEY, Foo INTEGER)
>Undo table: _undo(step INTEGER PRIMARY KEY, STRING class, id INTEGER,
> variable STRING, oldValue VARIANT, newValue VARIANT),
>objectA[27]->Foo=3; so _dataObjectA contains a single entry (27,3)
> 2) BEGIN
> 3) User calls objectA[27]->setFoo(4): c++ from this calls make an UPDATE
> dataObjectA SET Foo=4 WHERE id=27;
> 4) My xUpdate gets called. Following queries are made: UPDATE _dataObjectA
> SET Foo=4 WHERE id=27; INSERT INTO _undo VALUES (0 /*incremental step number
> */, 'objectA', 27, 'Foo', 3, 4)
> 5) ROLLBACK
> 6) My xRollback gets called and has to restore previous state (and notify it
> through the callback)
> 
> Now what database state will xRollback see? The one before (4) or the one
> after? Looking at code I see xRollback in sqlite3.c (3.8.7.1 amalgamation)
> is called with following call stack:
> 112789: static void callFinaliser(sqlite3 *db, int offset)
> 112838: sqlite3VtabRollback
> 
> Which itself may be called from:
> 125934 sqlite3RollbackAll /* After sqlite3BtreeRollback is called */
> 125744 sqlite3Close /* Not meaningfull on my case */
> 
> So it seems that when my xRollback will be called it will see old data. Can
> anybody confirm me there are no corner case in which something different
> could happened and may I assume that this behavior will be kept in future
> releases too?
> 
> Also, even if I'm afraid the answer is not is there a way to force
> "reloading" of virtual table(s)? In my implementation columns can be added
> (in virtual as in real data table) through another special virtual table
> (following the example before user may do a INSERT INTO classDef VALUES
> ('ObjectA', 'Bar', 'STRING') and this would add a column to _dataObjectA.
> Have I any ways to force (other than forced DROP TABLE/CREATE VIRTUAL TABLE)
> reloading so I can notify sqlite of new schema with sqlite3_declare_vtab?
> 
> Thank you for your help
> Alessandro
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 Aug 1, 2014, at 2:11 PM, Jim Callahan <jim.callahan.orla...@gmail.com> wrote:

> On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
>> 
>>> On 1 Aug 2014, at 4:45pm, Jim Callahan <jim.callahan.orla...@gmail.com>
>> wrote:
>> 
>>> column is not present in both tables
>> 
>> This is usually the result of using the syntax "JOIN table USING column"
>> because SQL requires columns of that name to be present in both tables.
>> Instead of that phrasing see if you can use this one:
>> 
>> JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
>> 
> 
> I changed the syntax from:
> 
> LEFT JOIN Gender USING (GenderID)
> 
> to
> 
> INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
> 
> Again it worked on the SQLite command line, but not when called from
> Python.
> 
>> 
>> If that doesn't help ...
>> 
>>> SQLite Version 3.8.0.1
>> 
>> Is that the version your IPython interface is using ?  Can you give us the
>> output of
>> 
>> SELECT sqlite_version()
>> 
>> when done through the iPython interface ?
> 
> 
> pd.read_sql_query('SELECT sqlite_version()', engine)
> 0 sqlite_version()
>3.6.21
> 
>> And I'm afraid we may also need to see the view definition, so can you
>> tell us whatever you used for your CREATE VIEW command ?
>> 
> 
> CREATE VIEW ActiveVoters2
> AS SELECT
> Voters.CountyID,
> Voters.VoterID,
> LastName, Suffix, FirstName,MidName,
> Supress,
> ResAddress1,
> ResAddress2,
> ResCity, ResST, ResZip9,
> MailAddress1,
> MailAddress2,
> MailAddress3
> MailCity, MailST, MailZip9, MailCountry,
> Voters.GenderID,
> Voters.RaceID,
> BirthDate,
> RegDate,
> Voters.PartyID,
> Precinct, PGroup, PSplit, PSuffix,
> Voters.StatusID,
> CD, HD, SD, CC, SB,
> AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
> 2014 -- FVRS
> Email,   -- Added Email-- Thursday July 24,
> 2014 -- FVRS
> County.CountyName,
> Gender.GenderName,
> Race.RaceName,
> Party.PartyName,
> Status.StatusName,
> VoterHistoryCol.ENov2012,
> VoterHistoryCol.EAug2012,
> VoterHistoryCol.EPPP2012,
> VoterHistoryCol.ENov2010,
> VoterHistoryCol.EAug2010,
> VoterHistoryCol.ENov2008,
> VoterHistoryCol.EAug2008,
> VoterHistoryCol.EPPP2008,
> (CASE WHEN substr(BirthDate,6,5) <= "11-06" -- Election Day 2012:
> Nov 6, 2012
>  THEN   2012 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2012 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeENov2012,  -- Age as of Nov 6,
> 2012
> (CASE WHEN substr(BirthDate,6,5) <= "08-26" -- Election Day 2014:
> Aug 26, 2014
>  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeEAug2014,  -- Age as of Aug 26,
> 2014
> (CASE WHEN substr(BirthDate,6,5) <= "11-04" -- Election Day 2014:
> Nov 4, 2014
>  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeENov2014   -- Age as of Nov 4, 2014
> FROM Voters
> INNER JOIN County ON County.CountyID = Voters.CountyID
> INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
> INNER JOIN Race   ON Race.RaceID = Voters.RaceID
> INNER JOIN Party  ON Party.PartyID   = Voters.PartyID
> INNER JOIN Status ON Status.StatusID = Voters.StatusID
> INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;
> 
> If necessary, I can send the whole Lafayette County, FL database (public
> record extract) via private email. Lafayette County is one of the smallest
> counties in Florida with only 4,556 voters which makes it ideal for
> developing convoluted SQL before moving the SQL to the big counties like
> Orange, Broward or Miami-Dade.
> 
> Unfortunately, the Anaconda Python environment is a 250 megabyte
> (compressed) download.
> 
> I am trying to understand enough so that I can write an intelligent
> question to the Python/SQLAlchemy/SQLite list.
> 
> Why does Python get to see the innards of a View; when the query just
> i

Re: [sqlite] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 5:26 PM, Gene Connor <neothreeei...@hotmail.com> 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 two integers, but it does 
> > return zero for 2 / 4.  After all, how many *whole* times does four go into 
> > two?
> 
>  
> 
> Right.  I discovered this 'problem' when I divided a real big integer by an 
> even bigger integer.  The actual result was 0.004572 or something like that.
> 
> MS Access did it right, but SQLite returned 0.  That prompted my bug? report
> 
> I also found out SQLite might change the column affinity to INTEGER if your 
> data can be converted "losslessly”.


You’re mixing terms.  “Affinity” is the “default type” of the column.  It is 
defined by the CREATE TABLE statement and is part of the table definition, 
meaning it cannot change on a row-by-row basis.  In SQLite, a column “affinity" 
is different from the “type" of a value in a given column, which (in SQLite, 
anyways) can change from row to row.  In fact, SQLite “types” and “affinities” 
do not even share the same set of classifications… Value types are limited to 
{NULL, INTEGER, REAL, TEXT, BLOB}, while affinities are {TEXT, NUMERIC, 
INTEGER, REAL, NONE}.

In your example below, you define the columns at “NUMERIC," which results in a 
NUMERIC affinity for the two columns (although not because the names match, see 
section 2.1 of http://www.sqlite.org/datatype3.html#affinity)

NUMERIC affinities have a preference to store things as an integer, followed by 
a floating point, followed by a string or other native type.  As you’ve shown, 
if SQLite can “up convert” a value, it will.  It isn’t just floating point 
numbers that get converted.  "INSERT INTO tbl1 VALUES ( ‘2’, ‘4’ )" will also 
result in two integers, even though you’ve passed in two strings.

For what it’s worth, all this is pretty clearly documented: 
http://www.sqlite.org/datatype3.html#affinity

It is a bit weird, but it comes from the fact that SQLite allows different 
types on a row by row basis.


> sqlite> CREATE TABLE TBL1 (N1 NUMERIC, N2 NUMERIC);
> 
> Same thing happens with type DECIMAL.
> 
> This means your best bet is to store /all/ numbers in FLOAT or DOUBLE 
> columns.   Or use CAST  (ugh).

Well, not really.  Large integer values cannot be accurately stored in 
floating-point values.  If you really want floating-point values, and you’re 
using a number domain that is acceptable to use floating-point values, then 
sure.  But if you want integers, they store larger precise numbers, and are 
usually much smaller on disk.

In short, the same as any other engineering decision: use the right tool for 
the job.

  -j


--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 4:29 PM, John Drescher <dresche...@gmail.com> 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, how many *whole* times does four go into two?

 -j

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 type.  That’s it.  No, it doesn’t.  Once we
>> accept that, everything else makes perfect sense based off existing
>> computer languages and historical ideas.  Can we all move on?
>> 
>> 
> Again (not to you again, just again as in I wrote this a while ago and DRH
> commented similarly):
> 
> The whole discussion has had nothing to do with a lack of boolean type in
> SQLite. It has to do with the fact that apparently none of the common SQL
> engines will accept the statement "SELECT 1 FROM 1 - 1". If you go to
> sqlfiddle.com and type that expression in each of the SQL engines supported
> (three versions of MySQL, five of PostgreSQL, two MS SQL Server, and one
> Oracle [and a partridge in a pear tree], in addition to SQLite), none of
> the other engines will compile and execute the statement.

But the reason the statement fails is because it trips a type-check for WHERE… 
that the expression value is not a Boolean, and the WHERE clause in those 
databases is strongly type-checked to only accept a Boolean.  It isn’t about 
the “sensibility” of the expression, it is a simple type failure.

SQLite has no native Boolean type, so it cannot verify the “sensibility" of an 
expression based off if it resolves to a Boolean or not.

Of course, as a programmer that primary works in languages other than SQL, the 
idea that a condition must resolve to a Boolean strikes me as somewhere between 
odd and frustrating.

> Now, you may not think this is a problem. I don't see it as a problem
> myself, beyond the fact that it is a way that SQL implementations diverge
> and thus make it more difficult to write portable SQL code, but I'm not
> convinced that "portable" SQL code is as valuable as some people (probably
> because I don't have to support a large number of varying implementations).

“Portable” SQL is a battle that was lost 30+ years ago.  It doesn’t exist, and 
anyone that things it does, or that there is any hope in finding it, needs to 
get out a bit more and try a few other RDBMS languages.

There are dozens and dozens of ways SQLite SQL is different from “common” SQL, 
never mind the standard.  Considering some the extreme differences, such as 
manifest typing, transaction aware DDL, the lack of  NUMERC type— not to 
mention no time, date, or duration types— getting worked up about the fact that 
WHERE is not strongly typed and will accept an integer seems almost comical.  
The “nonsense” examples given seem almost common place to a programmer that 
works in any language other than SQL.

> That being said, this has nothing to do with a boolean type or boolean
> logic or tri-state logic. The observation / complaint / criticism /
> whatever is that SQLite will compile and execute a statement that many /
> most / perhaps all other SQL engines will refuse to recognize as valid.

Except it does, because the whole reason this issue exists is the lack of a 
Boolean type.  The “fix”-- if you want to make it like every other database-- 
is to introduce a Boolean type, make WHERE type aware, and prevent type 
conversation of the WHERE expression.  That’s the only way to make it “like 
other databases."  So the whole thing revolves around the existence (or lack 
there of) of a Boolean type, even if the base argument is not about the lack of 
a Boolean.

If SQLite had a native Boolean type, chances are good this whole thing would be 
a non-issue because it would have been written that way in the first place.

Actually, on second thought, I kind of doubt it would.  SQLite shows a very 
strong tie to C and the way C does things.  This is reflected in the lack of a 
NUMERIC type, and the usage of native integers and floating-point numbers.  
Even if SQLite did have a native Boolean type— which, by itself, goes against 
the “embedded, close to C” design— I’m going to guess that conditional 
statements, such as WHERE would still accept integer values and just do type 
conversion.

> The reality is that SQLite is not a stand alone SQL engine, and it already
> deviates in some significant ways from the SQL standard (which every engine
> does to one extent or another, primarily in the form of what extensions it
> makes available and syntactic details). For an engine designed to be
> embedded in C programs as its primary use case to use the integer / boolean
> logic defined in C makes perfect sense on that basis.

Exactly.  Agreed.

> So, even though I agree that changing SQLite to disallow "WHERE 1 - 1"
> would not be a gain, some people seem to be completely missing the point,
> namely that there i

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

[Jay Kreibich]

On May 5, 2014, at 3:03 PM, Petite Abeille <petite.abei...@gmail.com> wrote:

> 
> On May 5, 2014, at 9:15 PM, RSmith <rsm...@rsweb.co.za> 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 is 1; 
> select 1 where 1 is not 1; 
> select 1 where 1 is ( 1 = 1 ); 

Cross what bridge?  

You seem to be trying to use common sense and semantic meaning to make an 
argument.  To quote an old CS prof, “If you argue in English**, you’re wrong.”  
Math and formal specifications are really the only thing here, and these do 
exactly what one would expect.

** or any other spoken language.

So far this whole discussion seems to boiled down to the fact that SQLite 
doesn’t have a native Boolean type.  That’s it.  No, it doesn’t.  Once we 
accept that, everything else makes perfect sense based off existing computer 
languages and historical ideas.  Can we all move on?


> select 1 in ( null ); — oh…
> select 1 in ( not null ); — really?

Since the rules of three-valued logic make it very clear that the value of the 
expression “not NULL” is “NULL”, where you really expecting something 
different?  What do you expect the opposite of NULL to be?

http://en.wikipedia.org/wiki/Three-valued_logic#Kleene_logic

Yes, three-valued logic is confusing to people that try to think of it in terms 
of “Boolean logic with special rules,” but it is all quite formal and clearly 
defined… it just isn’t Boolean logic.

  -j

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users