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

[Bernardo Sulzbach]

>
> I don't think that explanation holds water.
>

I don't have proof that I am correct, but you can try to test my
hypothesis the following way:

Let there be 4 databases:

-- f1.db
create table t(a real unique); insert into t values(9223372036854775807.0);
-- f2.db
create table t(a real unique); insert into t values(9223372036854775806.0);
-- i1.db
create table t(a real unique); insert into t values(9223372036854775807);
-- i2.db
create table t(a real unique); insert into t values(9223372036854775806);

Hash all 4 of them:

-- sha256sum *.db
a50f856cefb0c6cedb3ca66a6539685a2463b47e9a4dc53c174d0a14b6f5  f1.db
a50f856cefb0c6cedb3ca66a6539685a2463b47e9a4dc53c174d0a14b6f5  f2.db
c12ffcb265433dc61ed848962c4f5a05e215bba81ee6ad6db17b71f22cdbb463  i1.db
b2f58d7f372023145537978a0d13296d0bc47b1d75694feabf377908d0a5a7a8  i2.db

The ones which insert an integral-looking literal are storing
something different from what the ones that insert a
fractional-looking literal are. Also see that i1.db and i2.db are
different while f1.db and f2.db are equal.

Then, one might assume that internally the comparisons executed to
ensure uniqueness will behave differently too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Bernardo Sulzbach]

It is not a bug AFAIK. SQLite uses what the documentation calls
dynamic typing for its actual values. So if you are inserting integers
into a real column, you are going to store integers. However, when you
select from it they are presented as reals and mix up (looking as if
there were duplicates, even though the stored values are truly
unique).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Change name part of a JSON object

[Bernardo Sulzbach]

On 03/31/2018 09:58 AM, Robert M. Münch wrote:


A hack would be to search & replace “Untitled“: with the new name… but might be 
dangerous… any better idea?



Unless you are certain that the text you are replacing cannot occur 
anywhere else, this is asking for problems.

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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Bernardo Sulzbach]

On 11/15/2016 08:53 PM, Nico Williams wrote:

Another one that I find difficult to analyze is a possible out-of-bounds
read in vdbeSorterCompareInt():

 85712 static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 };
 85713 int i;
 85714 res = 0;
 85715 for(i=0; i0 && s1<7) || s1==8 || s1==9 );
 85701   assert( (s2>0 && s2<7) || s2==8 || s2==9 );
 85702
 85703   if( s1>7 && s2>7 ){
 85704 res = s1 - s2;
 85705   }else{
 85706 if( s1==s2 ){

At 85715 we know that (s1 <= 7 || s2 <= 7) && s1 == s2, and we also know
that either s1 or s2 can be 8 or 9, so aLen[s1] at 85715 could very well
have s1 > 6, which would read past the bounds of aLen[].

In both of these cases very detailed knowledge of the VDBE being handled
might show that these uninitialized reads do not happen.  If so, I don't
have that knowledge.

I'll hold off on other reports for the time being.

Nico



  if( s1>7 && s2>7 ){
res = s1 - s2;
  }else{
if( s1==s2 ){
  // Accesses to aLen as mentioned above

If s1 > 7 && s2 > 7 is false, then at least one of s1 and s2 is not 
above 7. If they are equal, then neither s1 nor s2 is above 7.


> and we also know that either s1 or s2 can be 8 or 9,

This is false, unless I am mistaken. See my reasoning above.

The issue is valid, and the message your analyzer (or compiler) wrote is 
correct: it is not guaranteed to be < 7, which it should be.


I am unsure whether or not this is actually a bug, but it almost 
certainly is a mistake.

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

Re: [sqlite] VACUUM causes Out of memory error

[Bernardo Sulzbach]

On 11/02/2016 05:18 PM, Christian Czech wrote:

It is a fundamental bug. I hope one day it gets fixed. Otherwise 3.15.0
is useless.



It has been fixed: http://www.sqlite.org/src/info/3028845329c9b7ac

--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@gmail.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange thing!

[Bernardo Sulzbach]

On 10/28/2016 01:42 PM, cont...@comadd.fr wrote:


I think the value of 'match' should be FULL instead of NONE!



From the docs, "SQLite parses MATCH clauses (i.e. does not report a 
syntax error if you specify one), but does not enforce them. All foreign 
key constraints in SQLite are handled as if MATCH SIMPLE were specified".


I think this is the issue you are facing.

--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@gmail.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[Bernardo Sulzbach]

On 10/17/2016 08:29 AM, Quan Yong Zhai wrote:

"The database file format is cross-platform - you can freely copy a database 
between 32-bit and 64-bit systems or between big-endian and little-endian 
architectures.

Quote:
"In your machine, implementation, and SQLite installation the
two value representations may be identical, while in others it may not be."

Does it mean the promise about cross-platform is broken?


A fair question, but no. Not as I see it, at least.

The statement - which you called a promise - still holds. It will work 
by just copying the data between 32-bit and 64-bit or BE and LE 
architecture.


The fact that two different implementations approximate a rational 
number differently does not violate that. See that fabs(a - b) > ε for 
some ε relatively close to zero and even if the value of fabs(a - b) may 
differ on these machines, the number is still a correct approximation 
for the rational in question. For instance, if it became a large 
negative number instead, it would be a portability problem, but this is 
a mere characteristic of a how floating point arithmetic works.


This is different from integer arithmetic, which is exact and should 
behave the same way on all systems, according to that statement.


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[Bernardo Sulzbach]

On 10/17/2016 07:12 AM, Quan Yong Zhai wrote:

I can’t reproduce the problem,


As it has already been pointed out, this is normal for floating point 
arithmetic. In your machine, implementation, and SQLite installation the 
two value representations may be identical, while in others it may not be.


fabs(a - b) is the simplest way to have an idea of how close two values 
are, and dividing by the magnitude of one of them (after checking that 
it is not zero, etc.) afterwards is also a good idea in some cases.


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Q about integer overflow in sqlite3MulInt64().

[Bernardo Sulzbach]

In time, ignore my previous reply to this thread as SQLite portability 
requirements make it invalid (at least I think they would). According to 
the C language standard, signed overflow is undefined behavior and, 
therefore, should not be relied upon.


There is also a simpler way to check it using a division of the maximum 
possible value by the multiplier (which will never overflow).


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Q about integer overflow in sqlite3MulInt64().

[Bernardo Sulzbach]

On 09/20/2016 04:51 PM, Scott Hess wrote:


No patch suggested, though I wouldn't be surprised if my brain makes a
suggestion after things simmer for an hour or so.  If either value
needs less than 31 bits, it can't happen, but there's not a simple bit
pattern to check, AFAICT.



Unless performance is of uttermost importance, just try and see if it 
fails or not.


prod = x * y;
if (y != 0) {
  if (prod / y != x) {
/* Overflow. */
  }
}

This also works for multiplications such as 1152921504606846976 * 3, 
which do not overflow, but have much bigger multiplicands.


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] IS a SQLite db of small size as good as reliable cache?

[Bernardo Sulzbach]

I don't really care about all the MS hate, but will add mine[1].

I am not a participant of that question, but it shows the same
problem. I had over 50% CPU usage for hours when the computer was
"idle" because Windows was indexing my stuff, forever. A friend of
mine had the same problem and after contacting MS the "solution" was
to disable it. There were no patches, no bugs being tracked, nothing.
Just "disable the feature and it will be fixed".

After all, it is simpler and easier to disable Windows as a whole and
replace it by something that shuts down in less than 5 seconds and
does not make its files unmodifiable when turned off by default.

[1] https://goo.gl/05EllC

[sqlite] hard links and SQLite

[Bernardo Sulzbach]

> What have folks here done to avoid the race condition originally described? 
> I?ve seen some documentation of SQLite and hard links but

Your message appears to be truncated.

What if you move the hard link creation part to right after the commit?

[sqlite] whish list for 2016

[Bernardo Sulzbach]

Thanks a lot for this, Stephen. It will very likely help me make a
decision in the future.

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Mon, Jan 4, 2016 at 1:50 PM, Stephen Chrzanowski  
wrote:
>
> I did the test to compare SSD vs 5200rpm and 7200rpm drives.  I should
> throw this at my 7200rpm hybrid at this app and see what happens.
>

Could you please share any performance data you still have? Thank you.

-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Mon, Jan 4, 2016 at 12:28 PM, Simon Slavin  wrote:
>
> That's 3 hours 23 minutes.  For a 38 Gigabyte database including a table with 
> half a billion rows.
>
> Details: Running in the SQLite 3.8.5 shell tool on a four year old iMac with 
> a spinning rust storage system.  VACUUM was running in the background while I 
> was doing light work (editing web pages, a bit of email, etc.) in the 
> foreground.
>
> So you can criticise how VACUUM works if you like, but on a cheap old iMac, 
> working in the background, it can still get through a big database in just a 
> few hours.
>
> Simon.

You wouldn't have monitored disk usage of that, would you? I am
curious about how faster a good SSD would make it as it clearly
doesn't look like a CPU or memory bound operation.

-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Fri, Dec 25, 2015 at 12:24 AM, John McKown
 wrote:
> On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz <
> realbasiclists at monkeybreadsoftware.de> wrote:
>
>> Hi,
>>
>> better ALTER command would be very welcome.
>>
>> e.g. RENAME/DELETE column or field.
>>
>
> RENAME exists.
>
> ALTER TABLE table-name RENAME COLUMN column_field_name TO
> new_column_field_name;
>

Are you sure? The documentation does not have anything about this and
I get a syntax error using 3.9.2 (a bit outdated, I know).

Anyway, if you are right, the documentation likely should be updated.

-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 3:16 PM, Simon Slavin  wrote:
>
> On 24 Dec 2015, at 5:10pm, Richard Damon  wrote:
>
>> being able to directly add a field would be nice.
>
> You can directly add a field.
>
> <https://www.sqlite.org/lang_altertable.html>
>
> Simon.

Good catch, you cannot place it wherever you want, but alter table is
currently capable of "directly adding a field".

-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 3:10 PM, Richard Damon  
wrote:
> On 12/24/15 10:26 AM, Bernardo Sulzbach wrote:
>>
>> I think you focused too much on the ordering issue. He or she may  > just
>> want a more versatile alter table for convenience. However, I >
>
> don't know if alter table is used at all in production anywhere (why > would
> it be? the column names and ordering should not be part of the > data). They
> are useful for prototyping because by having a stronger > alter table
> command you don't need to drop and create so many times. > In the end, if
> you spent enough time in the design phase to prepare > all your schemas, you
> should not have to drop (or alter) any of the > tables at all. It is a
> feature, a nice one, but too far from > necessary (from my standpoint) to be
> worthy of the developers' time. >
>
> I am in the process of building an application that has the need to be able
> to add columns to tables in response to user actions. It is primarily in the
> stage where the user is customizing the program to their needs, but such
> customizations might happen after the program has been in use for awhile.
> The main case is to be able to add a 'Flag' to records to allow the filter
> records or save the set of records found in a search. For now, the current
> method of create new, drop and rename, isn't unworkable (and mostly hidden
> in an abstraction layer), but being able to directly add a field would be
> nice.
>
> --
> Richard Damon
>
>

Interesting. I suppose that most of this "table editing" should rely
on an abstraction layer anyway: think about supporting other RDBMS in
the future. However, I recognize that in your case a more capable
alter table may simplify things a lot.

-- 
Bernardo Sulzbach

[sqlite] wish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 2:30 PM, Tim Streater  wrote:
> On 24 Dec 2015 at 15:26, Bernardo Sulzbach  
> wrote:
>
>> want a more versatile alter table for convenience. However, I don't
>> know if alter table is used at all in production anywhere (why would
>> it be? the column names and ordering should not be part of the data).
>
> If I distribute an app with a certain schema, and later make a new version 
> with added features that needs more columns in the database, I have to detect 
> the user's version, and run an extra function that updates the schema. If the 
> user skipped a few versions, several such functions may run. Generally this 
> does not affect things particularly, but I suppose after a few versions the 
> order of the columns could become sub-optimal.
>

But this is not a blocker, right? You could also create another table
with the desired schema and insert from the old one then drop it. It
is a convenience in the end.


-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 1:12 PM, John McKown
 wrote:
>
> On Thu, Dec 24, 2015 at 6:49 AM, gunnar  wrote:
>
> > I would like a less limited 'alter table' statement, to be able to drop
> > columns and to add columns at a position of my own choice instead of always
> > at the end.
> >
> >
> I'm curious as to why. Doing so would, most likely, require rewriting the
> entire table. If you want a SELECT * to get the columns in a particular
> order, just create a VIEW with the columns in the order in which you want
> them.
>

I think you focused too much on the ordering issue. He or she may just
want a more versatile alter table for convenience. However, I don't
know if alter table is used at all in production anywhere (why would
it be? the column names and ordering should not be part of the data).
They are useful for prototyping because by having a stronger alter
table command you don't need to drop and create so many times. In the
end, if you spent enough time in the design phase to prepare all your
schemas, you should not have to drop (or alter) any of the tables at
all.

It is a feature, a nice one, but too far from necessary (from my
standpoint) to be worthy of the developers' time.



-- 
Bernardo Sulzbach

[sqlite] Problem with accumulating decimal values

[Bernardo Sulzbach]

On Wed, Dec 16, 2015 at 1:54 PM, Simon Slavin  wrote:
>
> On 16 Dec 2015, at 3:46pm, Adam Devita  wrote:
>
> When writing accounting software, there will be a specific rule for rounding 
> attached to each calculation.  For instance a process for working out a 
> mortgage will include its own instruction "once you have multiplied by the 
> number of days, round down to the next dollar".  But the rules for working 
> out interest rates might state "round to the nearest cent, round half to 
> even".
>

Exactly as Simon said, the rules **will** (or at least should) be part
of the requirement. Also, some cases tell you to preserve fractions
until a final rounding. So you will have to go with decimals or
"scale" your integers (multiply them by a power of ten) somewhere.

At least here in Brazil there seems to be a lot of: if it is money
going away {round down as many times as possible} if it is money
coming our way {round up as many times as possible}. I think many
other places will use this too.

-- 
Bernardo Sulzbach

[sqlite] Problem with accumulating decimal values

[Bernardo Sulzbach]

On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf  wrote:
>
>> Hello, so in short, rounding the column anywhere it is used, is
>> another solution. I confirmed this below. Thanks, E. Pasma.
>>
>> BEGIN;
>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>> (repeat a 1.000.001 times
>> END;
>> SELECT bal FROM fmtemp;
>> 123450123.45
>
> Absolutely not!  You should NEVER round the value and store it back in the 
> datastore.  Rounding is ephemeral for the convenience of 
> ugly-bags-of-mostly-water who are fixed in their world-view so that data can 
> be DISPLAYED to them in a format that fits their limited view.
>

Although I agree about not rounding and updating the store with
"corrected" values. I don't think there is a need to call the
ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't
want myself to see 22.99 instead of 23.00 in the frontends I
use either. In a practical sense, I believe the latter reduces the
amount of processing my brain has to do and I can better focus on what
matters. But then again, just use string formatting on the view of the
project.

On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma  wrote:

> Ok this does not work of any scale of numbers. But a solution with integers
> neither does
>
> E.Pasma
>

Preferences aside, no solution ever devised will work with **any**
scale with numbers as we have finite data storage. That is very
pedantic, but just to be clear. I like integer better than floating
points and text for currencies, some will have other preferences, it
does not really matter as long as we are not working together.

-- 
Bernardo Sulzbach

[sqlite] Running Sqlite on 64-bit/Client-server data base

[Bernardo Sulzbach]

It runs on 64-bit computers.

It does not need a special process to serve other processes.

Read the documentation to answer trivial questions like these.

On Tue, Dec 15, 2015 at 9:47 PM, Hamdan Alabsi
 wrote:
> Greetings Everyone,
> Hope all is well. I am wondering if I can run Sqlite on 64-bit machine?
> Also, does sqlite support client-server database engine ? I hope I can get
> the answers from you very soon. Thank you.
>
> Best regards,
> Hamdan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] Problem with accumulating decimal values

[Bernardo Sulzbach]

On Sat, Dec 12, 2015 at 6:51 AM, Darren Duncan  
wrote:
>
> Per another suggestion, the best workaround is to use an INTEGER type
> instead, and store an even multiple of whatever your smallest currency unit
> size is, eg cents rather than dollars.
>

As I understood, he is doing the math in Python and saving strings
(that are produced by Python decimal arbitrary precision classes) to
the database, what should work perfectly.

[sqlite] Problem with accumulating decimal values

[Bernardo Sulzbach]

Looks like you thought you could have a DECIMAL type (such as MySQL
DECIMAL) here. But SQLite does not allow for this.

My workaround usually is:

create table accounts(account_number integer, balance integer);
create view accounts_view as select account_number, balance /
100.0 from accounts;

You may want to use text (or another relational system) if you get to
gargantuan values as integer is limited to signed 8 bytes (which I
think means up to positive 9223372036854775807, needs confirmation).

[sqlite] Problem with accumulating decimal values

[Bernardo Sulzbach]

Hi Frank,

You want to store an INTEGER type using the lowest used unit (cents or mills).

This page https://www.sqlite.org/datatype3.html may be of assistance next time


-- 
Bernardo Sulzbach

[sqlite] website documentation wording

[Bernardo Sulzbach]

On Sun, Dec 6, 2015 at 4:39 PM, Niall O'Reilly  wrote:
> On Fri, 04 Dec 2015 18:46:27 +,
> Keith Medcalf wrote:
>>
>> Intel's Management has decided -- for the imperfect tense.
>> Intel's Managemant have decided -- for the past perfect tense.
>
>   Eh?  These examples show the same tense.
>

Yes, that is true. Also, there is a typo in the second line.

They just present different agreements. I believe both are
grammatically correct. However, they differ in meaning, as the first
one treats management as a single unit and the second one emphasizes
that all managers decided something.

-- 
Bernardo Sulzbach

[sqlite] website documentation wording

[Bernardo Sulzbach]

Agreed, almost everyone around here says "sequel", too.

On Fri, Dec 4, 2015 at 1:53 PM, 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
>
> On Thu, Dec 3, 2015 at 3:41 PM, Bernardo Sulzbach  gmail.com
>> wrote:
>
>> Good catch, Dirk
>>
>> On Thu, Dec 3, 2015 at 4:47 PM, Dirk Jagdmann  wrote:
>> > I'm currently looking at https://www.sqlite.org/autoinc.html
>> >
>> > I suggest you change "You can access the ROWID of an SQLite table using
>> > one {of} the special column names..." and insert the word "of".
>> >
>> > --
>> > ---> Dirk Jagdmann
>> > > http://cubic.org/~doj
>> > -> http://llg.cubic.org
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Bernardo Sulzbach
>> ___
>> 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



-- 
Bernardo Sulzbach

[sqlite] website documentation wording

[Bernardo Sulzbach]

Good catch, Dirk

On Thu, Dec 3, 2015 at 4:47 PM, Dirk Jagdmann  wrote:
> I'm currently looking at https://www.sqlite.org/autoinc.html
>
> I suggest you change "You can access the ROWID of an SQLite table using
> one {of} the special column names..." and insert the word "of".
>
> --
> ---> Dirk Jagdmann
> > http://cubic.org/~doj
> -> http://llg.cubic.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] TEXT columns with Excel/VBA

[Bernardo Sulzbach]

I understand Smith's point. But when I am sure that some schema is
going to stay in SQLite for the foreseeable future, I like using text.
It is simple, adequate, and expresses my intent completely: TEXT.
That's what the column has, TEXT, not 40, not 60, not less than 30,
just freaking text.

Lastly, reading VARCHAR(40) makes my mind expect a programmatic limit
(not on the business logic) that does not exist. This tingles.

Everyone that commented on this secondary issue (about using TEXT or
VARCHAR for clarity of intent) has solid arguments, and any decision
then is mostly a matter of taste.

> Don't make mistakes.
I try not to. But it happens.

[sqlite] TEXT columns with Excel/VBA

[Bernardo Sulzbach]

On Wed, Dec 2, 2015 at 2:34 PM, Erwin Kalvelagen
 wrote:
> A user suggested that I should not use type TEXT but rather type VARCHAR for 
> character columns, due to some issue with Excel/VBA.

If he or she turns out to be correct, do it. As Slavin and Hipp
mentioned, SQLite won't care about the change. However, it is easier
on the eyes (at least mine) to see TEXT used with SQLite queries.


-- 
Bernardo Sulzbach

[sqlite] SQLiteman crashed

[Bernardo Sulzbach]

Spam filters went mad on you. Consider using a better email address /
name. Dr. Hipp saved the thread.

I think that http://sqliteman.yarpen.cz/ may be what you are looking
for. Try contacting petr at yarpen.cz

[sqlite] Warnings for non-deterministic queries?

[Bernardo Sulzbach]

On Fri, Nov 27, 2015 at 8:29 PM, Simon Slavin  wrote:
>
> And just as you write, all the above behaviours can change in different 
> versions of SQLite so even if you do detailed detective work using the 
> current version it might all be obsolete next week.
>
> Simon.
>

Exactly. One of the biggest advantages of not formally documenting
what will be selected (even if it at some point in time this was
"deterministic") is that the implementation can be more flexibly
manipulated (the developers didn't sign any contract).

A **warning** about this seems (to me) excessive. In the end of the
day, you shouldn't be relying on these arbitrary results anyway,
databases are dynamic (most of the tables are, at least) and you
should be prepared to handle a different query result from time to
time.


-- 
Bernardo Sulzbach

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

Oh, OK. I thought they were in the same table.

On Thu, Nov 26, 2015 at 11:56 AM, Simon Slavin  wrote:
>
> On 26 Nov 2015, at 1:52pm, Bernardo Sulzbach  
> wrote:
>
>> I did not mean that. But if you want to check that a certain row has a
>> valid value like you described,
>>
>>check ((job='dealer' or job='agent') or can_discount='no')
>
> That works only when the "job" column is in the table the constraint is in.  
> In my case, there are two tables: client and sale.  The "job" column is in 
> the client table, but it's the sale table where I need to know if the client 
> deserves a discount.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

I did not mean that. But if you want to check that a certain row has a
valid value like you described,

check ((job='dealer' or job='agent') or can_discount='no')

Could do the trick, right? If you want, you can be more aggressive and go for

check can_discount='no' or ((job='dealer' or job='agent') and
can_discount='yes')

On Thu, Nov 26, 2015 at 11:38 AM, Simon Slavin  wrote:
>
> On 26 Nov 2015, at 1:36pm, Bernardo Sulzbach  
> wrote:
>
>> I am not against this feature request, Simon. But enlighten my why
>> this cannot be done with a CHECK constraint? I may be missing
>> something very simple. Thanks.
>
> Can you refer to another table in a CHECK constraint ?  It never occurred to 
> me.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

I am not against this feature request, Simon. But enlighten my why
this cannot be done with a CHECK constraint? I may be missing
something very simple. Thanks.

[sqlite] drop, create and copy a table

[Bernardo Sulzbach]

What are you trying to do? Rename the table? You can use
https://www.sqlite.org/lang_altertable.html for that.

Please make your intentions clearer.

On Wed, Nov 25, 2015 at 6:43 PM, H?ctor Fiandor  
wrote:
> Dear members:
>
>
>
> I have asked previously about this matter but nobody answer me.
>
> I have a table to be copied in another one with other name as a temp.
>
> Then I drop the first one.
>
> Then I create another one with same as the dropped table.
>
> Then I copy from the temp to the new created.
>
>
>
> I do all this but in three phases I may answer is It is possible to do this
> in one phase?
>
>
>
> Thanks in advance
>
>
>
> Ing. H?ctor Fiandor
>
> hfiandor at ceniai.inf.cu
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Bernardo Sulzbach]

On Wed, Nov 25, 2015 at 1:14 PM, Simon Slavin  wrote:
>
> CREATE TABLE use_mytype (...
>...
>one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.
>
> Simon.
>

I know this works, but I would suggest using INTEGER instead of text
for one_type. It seems like better design.

Simon S., DRY makes a lot of sense (and is pretty awesome) *in
application code*. You may have been doing it a lot and just didn't
know the name. Data models may be more referential than replicated
(point to where the text is instead of copying it), but I don't think
that this brings huge advantages.

-- 
Bernardo Sulzbach

[sqlite] INSERT DEFAULT literal-value

[Bernardo Sulzbach]

On Mon, Nov 23, 2015 at 7:00 PM,   wrote:
>
>
>
> Maybe there's a technical reason that this functionality wasn't added to 
> SQLite. Does anyone know?
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I don't think there is a technical reason for this, it seems rather
trivial to implement it (disclaimer: I am not a SQLite developer and
may be wrong). Ultimately, it is rather unnecessary, even if it would
be "convenient".

-- 
Bernardo Sulzbach

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Bernardo Sulzbach]

On Wed, Nov 18, 2015 at 12:00 AM, Simon Slavin  wrote:
>
>
> Since SQLite4 works differently to SQLite3 it's possible that adding this 
> feature to SQLite4 would be 'cheaper' than adding it to SQLite3.  We'll have 
> to wait and see.
>
> Simon.

Yes. Maybe it can be shipped into SQLite 4.

-- 
Bernardo Sulzbach

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Bernardo Sulzbach]

If there was a vote, I would also vote for better message. But I don't
think there is one. The impact may be significant for applications
that have already been written. Updating your dependency and seeing it
slower is not something any developer likes to do. I would understand
if this just ends up as a "wontfix".


-- 
Bernardo Sulzbach

[sqlite] [AGAIN] SQLite on network share

[Bernardo Sulzbach]

You said you wanted something that didn't require too much skill to
set up? PostgreSQL seems safer and easier than implementing VFS
yourself. One is **slightly** less error-prone than the other.

On Fri, Nov 13, 2015 at 7:08 PM, Keith Medcalf  wrote:
> You realize that the marketing translation of "support" is "make money from"? 
>  It does not mean "works".
>

This. If you use SQLite or not, the list loses **very** little. But
they could always use another customer. You shouldn't ask their
support this questions, they are not giving you any formal guarantees.

-- 
Bernardo Sulzbach

[sqlite] [AGAIN] SQLite on network share

[Bernardo Sulzbach]

On Fri, Nov 13, 2015 at 5:04 PM, Niall O'Reilly  wrote:
>   People on this mailing list can't do your risk assessment or
>   impact analysis for you.
>
>   Best regards,
>   Niall O'Reilly
>

Seconded.

You asked if there was a way to safely use it. I don't think there is.
You also mentioned "max 5". In some cases your current "max" is far
below what your true "max" is going to be. If you need a DB that works
over a network, SQLite doesn't look like the best candidate to me.


-- 
Bernardo Sulzbach

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

[Bernardo Sulzbach]

On Tue, Nov 10, 2015 at 10:38 PM, Richard Hipp  wrote:
> 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
>

Not only that, but the #1 Certified SQLite Professional.

-- 
Bernardo Sulzbach

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

[Bernardo Sulzbach]

On Tue, Nov 10, 2015 at 7:20 PM, Christian Werner
 wrote:
> I wonder what the knife will be ;-)

Maybe there is no knife this time. Just kidding.

But yeah, kudos for R. Hipp and everyone that contributed to the project.


-- 
Bernardo Sulzbach

[sqlite] Random performance issues with SQLite

[Bernardo Sulzbach]

On Mon, Nov 9, 2015 at 4:41 PM, R Smith  wrote:
> All this assumes it /IS/ in fact the anti-virus causing the problem - you
> need to check that first.

Just let him run with the AV disabled for a while or ignore the folder
and try to replicate the problem.

However, I would **uninstall the program completely**, as many AVs
like to keep some things running even when they are "disabled".


-- 
Bernardo Sulzbach

[sqlite] A little light reading

[Bernardo Sulzbach]

On Sun, Nov 8, 2015 at 4:17 PM, Nicolas J?ger  
wrote:
> people with non-latin-1 name knows problem too... even in their own country...
>
> regards,
> Nicolas

Yes, but I think this is getting better as there is more high-level
programming and better and more tested libraries being used. Still,
the problem is not going to disappear anytime soon.

-- 
Bernardo Sulzbach

[sqlite] A little light reading

[Bernardo Sulzbach]

On Sun, Nov 8, 2015 at 1:25 PM, Nicolas J?ger  
wrote:
>
> reminds me when, because of some "idiots database programmers", I got (during 
> the same month) three
> cards automatically from the health care system with three times my name 
> wrongly printed (because of
> the "?"). Of course having more than one card like this is illegal...
>

Did you sent the cards back? Can't you use a "plain" a for legal purposes?

-- 
Bernardo Sulzbach

[sqlite] A little light reading

[Bernardo Sulzbach]

On Sun, Nov 8, 2015 at 2:42 PM, John McKown
 wrote:
> (...) What if the person is alive? What if you don't now the birth day?).

NULLs, as you mentioned, are questioned in many domains. They provide
an easy solution, but never the only one there is. In an OOP system,
for instance, LivingPerson and DeadPerson may be two different classes
and have different properties. In a RDBS, a table could store the
people that have not died yet. Or you could use strings of the form
"-" and have some "1995-02-11-" strings for the ones still
living. But there is a "semantic" nullity there if you think about it.

-- 
Bernardo Sulzbach

[sqlite] CSV excel import

[Bernardo Sulzbach]

> P.S. As Simon noted, it seems to be defined here: 
> 

> I was probably referring to the first sentence of chapter 2: "While there are 
> various specifications and implementations for the CSV format"

OK for that document existing and all, but just a quotes:

  "Each field _may or may not_ be enclosed in double quotes"

So if I want to represent "" I can use either "" or "".
Noting that if I cannot tell the parser if I am using quotes or not
"" becomes either "" or "". And if I need to use a crlf in
there, quotes are mandatory. So it may or may not be enclosed but must
be in some scenarios. Fantastic.

This kind of BS (at least to me) reduces the relevance of a "definition".
It also may or may not have headers, but that's a smaller issue.

[sqlite] CSV excel import

[Bernardo Sulzbach]

> =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");")

This is so neat. Supposing you are not migrating from a spreadsheet
because it got too big (millions of rows will take a time for this to
finish) and that your data respects a logical grouping, this is very
handy.

[sqlite] CSV excel import

[Bernardo Sulzbach]

> My point is that I have seen so many emails regarding this incorrect csv 
> import, that it would be so easy for us if it just simply works in the CLI 
> and delivered in standard in the sqlite3 executable.

I don't think I understand what you mean by this. Also, most of the
problems seems to arise from the fact that CSV is just too weakly
specified. See how better defined JSON is and how it solves a lot of
problems (not suggesting JSON here).

[sqlite] CSV excel import

[Bernardo Sulzbach]

I can remember two times when my life would have been easier if I
could throw big .ods into sqlite3 dbs. So I would also like such a
project.

[sqlite] Help with understanding sqlite3_mprintf_int test expected result

[Bernardo Sulzbach]

Richard,

He wrote (ans I wasted my time reading)

> If you are reading this email, but you are not gcc-help administrator, 
> please, please, forward this email to the administrator or simple reply to 
> all. GCC-help should know what to do with this message.

He seems to be trying to contact the admin of that mailing list (a
"popular?" GCC mailing list). Likely just good-old SPAM for us.

[sqlite] Possible substr() optimization?

[Bernardo Sulzbach]

> That's not quite the same. BETWEEN is inclusive of both ends. ('b' BETWEEN 
> 'a' and 'b') is true.

Yes, you are right. I'm sorry for that, just wanted to point out that
(as most would expect) .. >= 'a' and .. < 'b' would not match
uppercase beginnings.

[sqlite] Possible substr() optimization?

[Bernardo Sulzbach]

> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like 'a_c%';

Just adding to Igor's answer:
 col1 between 'a' and 'b' or col1 between 'A' and 'B'
if you need this case sensitive behavior

[sqlite] Broken link on System.Data.SQLite homepage

[Bernardo Sulzbach]

I guess this site is not a responsibility of sqlite.org, right?

[sqlite] Doc page revision request

[Bernardo Sulzbach]

Just to add some to the list of silly filters and silly silliness: at
home I can access everything, my school blocks wikipedia. I am not
kidding.

[sqlite] Doc page revision request

[Bernardo Sulzbach]

I understand that the filter is not something his father hacked with
Python in 10 minutes, but I don't think this will make anyone change
the page.
cnt is used by Oracle, Microsoft (there is even a .cnt extension if I
am not mistaken), and I'd also say that any big software company has
cnt somewhere.
And as Smith said, there are other languages out there. Even words
that are OK in Portugal Portuguese (puto, for instance) are not
family-friendly in Brazilian Portuguese.
So I guess that the lab computers will be the solution.

BTW, Google pointed me this https://en.wikipedia.org/wiki/Penistone
Damn, it exists.

[sqlite] Doc page revision request

[Bernardo Sulzbach]

> The problem seems to be with the web filter and not the abbreviation cnt.

Exactly. Let's not forget to mention that dick is a synonym for
detective and that bitch is a female dog. "cnt" is fine in that
context and the filter should likely be deactivated or updated.
Although it may be easier to just ask someone to replace stuff on that
page, as I said before, "cnt" is used to mean "count" in many places,
making it even clearer that the filter is the problem.

Good luck asking Oracle to update this:
http://docs.oracle.com/javase/7/docs/api/java/awt/List.html

On Tue, Jul 21, 2015 at 12:46 PM, Paul Sanderson
 wrote:
> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their
> filter to prevent filtering of an innocuous word (its only
> rude/offensive if the u is added).
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 21 July 2015 at 16:34, Jim Callahan  
> wrote:
>> I Simon's point about idiotic web filters is valid.
>>
>> "Cnt" is innocuous in formal documentation where by context it clearly
>> means "count", but think of how people type text messages. If an online
>> chat board in html had text like messages then a machine learning algorithm
>> (for a web filter) would tend to associate "cnt" with sexually explicit and
>> racially offensive language that would not be appropriate for an elementary
>> school aged child.
>>
>> By middle school the student and their friends are probably experimenting
>> with the language
>>
>> Web  filters are sometimes used in corporations, government agencies and
>> public facilities, so I can see why it might be an issue, even though "cnt"
>> is completely innocuous in formal documentation in a way it would not be in
>> a "how many ... does it take to change light bulb" joke or in a string of
>> offensive expletives.
>>
>> It is a matter of context. And to a crudely programmed machine learning
>> algorithm it is all html text with no context.
>>
>> Jim
>>
>>
>> On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
>> mafagafogigante at gmail.com> wrote:
>>
>>> About using "cnt", it is by far not just this page. There are tons of
>>> documentation and programming pages out there that use "cnt" instead
>>> of "count".
>>>
>>> The last part of your message seems more valid, though.
>>> ___
>>> 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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] Doc page revision request

[Bernardo Sulzbach]

About using "cnt", it is by far not just this page. There are tons of
documentation and programming pages out there that use "cnt" instead
of "count".

The last part of your message seems more valid, though.

[sqlite] storing data across multiple tables

[Bernardo Sulzbach]

> I have multiple tables of data already, say TableA, TableB. These tables have 
> different representations, one may contain a TEXT column, the other may 
> contain and INTEGER column, but all of them contain an INTEGER field called 
> time, which is unique.

I don't understand your specifications well enough to give you a
solution, but if you have many tables with (Time IPK, Number INT) and
many with (Time IPK, String TEXT) you can avoid creating identical
tables (by column definitions) by merging these tables together.

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Bernardo Sulzbach]

You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which,
clearly, is not true.
http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite
see this link for more on the subject.

2014-11-16 13:56 GMT-02:00 Igor Tandetnik <i...@tandetnik.org>:

> On 11/16/2014 10:51 AM, Paul Sanderson wrote:
>
>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>>
>
> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY.
> Which part of the error message do you find unclear?
>
> For details, see http://www.sqlite.org/autoinc.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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