Re: [sqlite] Question about: Adding a record to a table with select failure

[Peter da Silva]

Assuming I'm understanding what the original message was about.

Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using application_id

[Peter da Silva]

> If you stick to lower or upper case letters, could encode up to 6 chars in
> the app_id. --DD

The return of RADIX-50.

https://en.wikipedia.org/wiki/DEC_Radix-50

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

Re: [sqlite] What is the C language standard to which sqlite conforms ?

[Scott Robison]

On Mon, Nov 18, 2019 at 3:44 PM Dennis Clarke  wrote:

>
> Same question as a few days ago.
>
> This may have been asked many times before but always seems to be a
> valid question.  On some machines with different compilers I get good
> results using C99 strict compliance. On other machines, such as those
> running Red Hat Enterprise Linux, I get terrible results.
>

Per https://www.sqlite.org/howtocompile.html it is "ANSI-C". C89 is the
ANSI-C standard, C90 is the first ISO-C standard. They are practically
identical.

Note that it is not strict ANSI-C, since ANSI-C doesn't provide for 64 bit
integers, and it does not provide for platform specific APIs or functions.
But as much as is possible, it is written to work with standard C as it has
existed for about 30 years now.

Different compilers have various degrees of compliance with their C89 / C90
/ C99 implementations. C99 is more strict about some things that C89 did
not care about, and the developers have made concessions on occasion to
conform to C99 when it did not compromise C89 compatibility. But as much as
possible it is desired to maintain C89 compatibility because there are
platforms that still are stuck with older compiler standards.

Note: I am not a member of the dev team, just recounting my understanding
of how things are.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Entity Framework Core support

[Mike King]

I've done some reading and kind of side stepped the issue a bit by adding a
reference to System.Data.SQLite and use that connection object to connect
to the database and pass the connection to the MS SQLite driver. I can now
password encrypt databases and (from my very brief tests) it looks like I
can do the basic insert / updates I wanted to.

For future reference here's the OnConfiguring override in my DbContext.
I've commented out the password code for the moment so I can edit the
database using a tool that doesn't support encryption.

 protected override void OnConfiguring(DbContextOptionsBuilder
optionsBuilder)
{
SQLiteConnection conn = new SQLiteConnection($"Data
Source={_fileName}");
//conn.Open();
//SQLiteCommand command = conn.CreateCommand();
//command.CommandText = "SELECT QUOTE(@Password);";
//command.Parameters.AddWithValue("@Password", _password);
//string quotedPassword = (string)command.ExecuteScalar();
//command.CommandText = $"PRAGMA {(!File.Exists(_fileName) ?
"rekey" : "key")} = {quotedPassword};"; // rekey sets / changes password
//command.Parameters.Clear();
//command.ExecuteNonQuery();
optionsBuilder.UseSqlite(conn);
}
}

Cheers,

Mike


On Mon, 18 Nov 2019 at 01:24, Joe Mistachkin  wrote:

>
> Mike King wrote:
> >
> > "It needs to be clearly stated somewhere that EF 6.3 is meant only as a
> > tool for migrating from .NET Framework, and that EF Core is the version
> > that should be used." *2
> >
>
> Given the phrasing here, it sounds like this guidance is relatively new
> and/or unofficial at this point?
>
> >
> > . But
> > because it's a new code base, it also lacks some features that EF6 has."
> *3
> >
>
> To clarify, if it lacks features that EF6 has, it sounds a lot less like
> a porting effort and a lot more like a rewriting effort.  That does not
> necessarily mean it won't get done (at some point); however, that will
> certainly impact it's relative priority on the roadmap.
>
> --
> Joe Mistachkin
>
> ___
> 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: Adding a record to a table with select failure

[Keith Medcalf]

On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera  
wrote:

>Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>>
>> This relies on two implementation details particular to SQLite3 which
>> hold at present, but may of course change at any time:
>> (1) that selecting a non-aggregate scalar column will return a value
>> from (one of) the row(s) matching the value of the aggregate (most 
>> RDBMS used to do this, most now throw an error at this construct); and,
>> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
>> into 'p006' since the result must always be 'p006' which would of 
>> course render the select to be a simple select and not an aggregate 
>> causing "all hell to break loose".

>Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't
>know much to argue, but is working. If I take out the first IfNull, and
>there is not, at least one instance of 'p006' in the table, the INSERT
>never works. I was thinking of using COALESCE, but that would also mean
>that one or the other would have to be not null. Any suggestion would be
>appreciated.

No, what I am saying is that this particular insert is working with the current 
version of SQLite3 and is unlikely to work with any other RDBMS that uses SQL 
as this is using an "implementation detail" specific to SQLite3 that does not 
exist elsewhere; and, secondly that the particular construction used putting 
the aggregate function in the second argument of a coalesce where the first 
argument is a constant is dependent on the query optimizer not "optimizing way" 
the entire expression based on the fact that the first argument is a not-null 
constant (though if this optimized away the aggregate-ness of the query this 
would be a bug in the optimizer, since the optimization process should not 
cause different results to occur).

What I am saying is that you need to be aware of this and if you change 
versions of SQLite3 then you need to make sure that this query still operates 
as you intend it to operate.

So far as I know the first constraint (the scalar use of columns that are not 
part of the group by clause in a query) is unlikely to change because this is a 
significant change to backwards compatibility and Richard is unlikely to make 
such a change without warnings in second coming type.

The second is unlikely with SQLite3 because the optimizer is not based on a 
query re-write which would be more prone to this sort of issue.

The second issue can be bypassed entirely by using bound parameters (since 
there is no way except by examining the bound parameter at execution time to 
know that a bound parameter is not NULL) thus precluding the possibility of 
optimizing away the aggregate function.  As in:

select ifnull(:a, max(idate)),
   ifnull(b, 1),
   ifnull(c, 2),
   :y,
   ifnull(e, 4),
   :idate
  from t
 where a == :a

and binding values for :a, :y and :idate.  There is no way to know at prepare 
time that parameter :a cannot be null so therefore the ifnull(:a... and the 
evaluation of the aggregate cannot be optimized away.

>[clip]
>
>> >SELECT IfNull('p006', Max(idate)),
>> >   IfNull(b, 1),
>> >   IfNull(c, 2),
>> >   'y',
>> >   IfNull(e, 4),
>> >   '2019-20-12'
>> >  FROM t
>> >  WHERE a = 'p006';
>> >

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] What is the C language standard to which sqlite conforms ?

[Jens Alfke]

> On Nov 18, 2019, at 2:44 PM, Dennis Clarke  wrote:
> 
>  On some machines with different compilers I get good
> results using C99 strict compliance. On other machines, such as those
> running Red Hat Enterprise Linux, I get terrible results.

Why does it matter to you? I usually worry about compliance in my own code, not 
in 3rd party code that's known to work.

The parts of SQLite source code I've seen seem to be C90, in that they don't 
use "//" comments and put all their variable declarations at the top of a 
function. But I have no idea whether it's expected to conform to that or not.

> On other machines, such as those running Red Hat Enterprise Linux, I get 
> terrible results.

What kind of results? RHEL tends to have old versions of everything, so it may 
be that its version of Clang/GCC has bugs with checking language compliance.

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

[sqlite] What is the C language standard to which sqlite conforms ?

[Dennis Clarke]

Same question as a few days ago.

This may have been asked many times before but always seems to be a
valid question.  On some machines with different compilers I get good
results using C99 strict compliance. On other machines, such as those
running Red Hat Enterprise Linux, I get terrible results.

Also I am using a variety of LLVM/Clang and also GCC 8.x and 9.x as
well as Oracle Studio 12.6 and also a few tests with less obvious
compilers.  This would be on 32-bit i386 and 32-bit armv7 and 64-bit
ppc64 and Fujitsu sparcv9 and AMD Opteron and Intel x86_64 various
types.

--
Dennis Clarke
RISC-V/SPARC/PPC/ARM/CISC
UNIX and Linux spoken
GreyBeard and suspenders optional
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jim Morris]

Not sure this helps, a way to a conditionally insert based on if record
already exists, is a select with literals left outer joined to the maybe
record and use a where test value is null.

Something like this pseudo SQL

insert into T  (valueA, valueB') (select 'ValueA', 'ValueB' left outer
join T where T.valueA ='valueA' and T.valueA is null);


On 11/18/2019 2:14 PM, Simon Slavin wrote:
> On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera  wrote:
>
>> Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't 
>> know much to argue, but is working. If I take out the first IfNull, and 
>> there is not, at least one instance of 'p006' in the table, the INSERT never 
>> works. I was thinking of using COALESCE, but that would also mean that one 
>> or the other would have to be not null. Any suggestion would be appreciated.
> Being completely serious, whenever I see "undocumented" or "implementation 
> dependent" or "optimization side-effect", or a SQL statement I can't parse in 
> my head, I usually decide to do it in my programming language instead.  This 
> simplifies testing and debugging, and makes things easier for the poor 
> engineer who has to understand my code.
>
> You can do clever things in a language like SQL which allows recursive 
> construction clauses.  But what strikes me as ingenious when I'm writing it 
> can look bizarre and impenetrable to me, or someone else, a year later.
> ___
> 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: Adding a record to a table with select failure

[Simon Slavin]

On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera  wrote:

> Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know 
> much to argue, but is working. If I take out the first IfNull, and there is 
> not, at least one instance of 'p006' in the table, the INSERT never works. I 
> was thinking of using COALESCE, but that would also mean that one or the 
> other would have to be not null. Any suggestion would be appreciated.

Being completely serious, whenever I see "undocumented" or "implementation 
dependent" or "optimization side-effect", or a SQL statement I can't parse in 
my head, I usually decide to do it in my programming language instead.  This 
simplifies testing and debugging, and makes things easier for the poor engineer 
who has to understand my code.

You can do clever things in a language like SQL which allows recursive 
construction clauses.  But what strikes me as ingenious when I'm writing it can 
look bizarre and impenetrable to me, or someone else, a year later.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>
> This relies on two implementation details particular to SQLite3 which hold> 
> at present,
> but may of course change at any time:
> (1) that selecting a non-aggregate scalar column will return a value from 
> (one of) the
> row(s) matching the value of the aggregate (most RDBMS used to do this, most 
> now throw
> an error at this construct); and,
> (2) that the optimizer will not optimize "IfNull('p006', max(idate))" into 
> 'p006' since
> the result must always be 'p006' which would of course render the select to 
> be a simple
> select and not an aggregate causing "all hell to break loose".

Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know 
much to argue, but is working. If I take out the first IfNull, and there is 
not, at least one instance of 'p006' in the table, the INSERT never works. I 
was thinking of using COALESCE, but that would also mean that one or the other 
would have to be not null. Any suggestion would be appreciated.

[clip]

> >SELECT IfNull('p006', Max(idate)),
> >   IfNull(b, 1),
> >   IfNull(c, 2),
> >   'y',
> >   IfNull(e, 4),
> >   '2019-20-12'
> >  FROM t
> >  WHERE a = 'p006';
> >
> >versus this:
> >SELECT (a,b,c,d,e,idate) from t where a = "p006"
> >
> >Doesn't the where clause that cannot be satisfied in both cases guarantee
> >that no rows will be selected, when there are no records in the database?
> >Doug
> >
> >> -Original Message-
> >> From: sqlite-users, on

Jose Isaias Cabrera
> >> Sent: Monday, November 18, 2019 12:11 PM
> >
> >> Subject: Re: [sqlite] Question about: Adding a record to a table
> >> with select failure
> >>
> >>
> >> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> >> Jose Isaias Cabrera
> >> [clip]
> >> > > >
> >> > > > INSERT INTO t (a, b, c, d, e, idate)​
> >> > > > SELECT IfNull('p006', Max(idate)),​
> >> > > >IfNull(b, 1),​
> >> > > >IfNull(c, 2),​
> >> > > >'y',​
> >> > > >IfNull(e, 4),​
> >> > > >'2019-20-12'​
> >> > > >   FROM t​
> >> > > >  WHERE a = 'p006';​
> >> >
> >> > I think that you will never insert the first record with a query
> >> like this, since
> >> > the select returns 0 records of there are none in the database
> >> yet.
> >>
> >> Well, it does...
> >> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> >> idate));
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p001', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-20-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p001';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-20-11
> >> sqlite>
> >>
> >> And, since I put an uniqueness on a and idate, now these can not
> >> be repeated, so if I run the same command again,
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p001', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-02-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p001';
> >> Error: UNIQUE constraint failed: t.a, t.idate
> >> sqlite>
> >>
> >> I do not get a repeated record for 'p001' and 2019-02-11; But if
> >> they are different,
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p002', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-02-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p002';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-02-11
> >> p002|1|2|n|4|2019-02-11
> >>
> >> It'll work. Thanks.
> >>
> >> josé

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Keith Medcalf]

No.  This is an aggregate query that relies on the fact that SQLite3 will 
choose the values from (one of) the row(s) containing the aggregate to satisfy 
select scalars that are not aggregates.  Consider the query:

select a, max(idate), b from t where a == 'p006';

This will return the maximum value of idate for all the rows in t where a == 
'p006', and the values of a and b from (the same) one of the rows on which this 
maximum is found.  If no rows matching the condition a == 'p006' then the 
maximum is NULL and the values of a and b are also NULL since there is no row 
from which the values may be taken.

So, this query will return the values b, c, e from the (one of the rows) with 
the max(idate) from all the rows in t having a == 'p006', or NULL for those 
values if no such row exists.  If no such row exists then the IfNull function 
will convert those NULL values into the given values.  The first ifnull will 
never actually be executed (since the first value is not null, the second, 
max(idate), will never be used).  However, since IfNull is a function, in this 
case taking two arguments, all the arguments must be evaluated BEFORE the 
function can be evaluated.

This relies on two implementation details particular to SQLite3 which hold at 
present, but may of course change at any time:
(1) that selecting a non-aggregate scalar column will return a value from (one 
of) the row(s) matching the value of the aggregate (most RDBMS used to do this, 
most now throw an error at this construct); and,
(2) that the optimizer will not optimize "IfNull('p006', max(idate))" into 
'p006' since the result must always be 'p006' which would of course render the 
select to be a simple select and not an aggregate causing "all hell to break 
loose".

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Doug
>Sent: Monday, 18 November, 2019 12:49
>To: 'SQLite mailing list' 
>Subject: Re: [sqlite] Question about: Adding a record to a table with
>select failure
>
>I'm really confused now. I don't understand the semantics of:
>SELECT IfNull('p006', Max(idate)),
>   IfNull(b, 1),
>   IfNull(c, 2),
>   'y',
>   IfNull(e, 4),
>   '2019-20-12'
>  FROM t
>  WHERE a = 'p006';
>
>versus this:
>SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
>Doesn't the where clause that cannot be satisfied in both cases guarantee
>that no rows will be selected, when there are no records in the database?
>Doug
>
>> -Original Message-
>> From: sqlite-users 
>> On Behalf Of Jose Isaias Cabrera
>> Sent: Monday, November 18, 2019 12:11 PM
>> To: 'SQLite mailing list' 
>> Subject: Re: [sqlite] Question about: Adding a record to a table
>> with select failure
>>
>>
>> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
>> Jose Isaias Cabrera
>> [clip]
>> > > >
>> > > > INSERT INTO t (a, b, c, d, e, idate)​
>> > > > SELECT IfNull('p006', Max(idate)),​
>> > > >IfNull(b, 1),​
>> > > >IfNull(c, 2),​
>> > > >'y',​
>> > > >IfNull(e, 4),​
>> > > >'2019-20-12'​
>> > > >   FROM t​
>> > > >  WHERE a = 'p006';​
>> >
>> > I think that you will never insert the first record with a query
>> like this, since
>> > the select returns 0 records of there are none in the database
>> yet.
>>
>> Well, it does...
>> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
>> idate));
>> sqlite> INSERT INTO t
>>...> SELECT IfNull('p001', Max(idate)),
>>...>IfNull(b, 1),
>>...>IfNull(c, 2),
>>...>IfNull(d,'n'),
>>...>IfNull(e, 4),
>>...>'2019-20-11'
>>...>  FROM t
>>...>  WHERE a = 'p001';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-20-11
>> sqlite>
>>
>> And, since I put an uniqueness on a and idate, now these can not
>> be repeated, so if I run the same command again,
>> sqlite> INSERT INTO t
>>...> SELECT IfNull('p001', Max(idate)),
>>...>IfNull(b, 1),
>>...>IfNull(c, 2),
>>...>IfNull(d,'n'),
>>...>IfNull(e, 4),
>>...>'2019-02-11'
>>...>  FROM t
>>...>  WHERE a = 'p001';
>> Error: UNIQUE constraint failed: t.a, t.idate
>> sqlite>
>>
>> I do not get a repeated record for 'p001' and 2019-02-11; But if
>> they are different,
>> sqlite> INSERT INTO t
>>...> SELECT IfNull('p002', Max(idate)),
>>...>IfNull(b, 1),
>>...>IfNull(c, 2),
>>...>IfNull(d,'n'),
>>...>IfNull(e, 4),
>>...>'2019-02-11'
>>...>  FROM t
>>...>  WHERE a = 'p002';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-02-11
>> p002|1|2|n|4|2019-02-11
>>
>> It'll work. Thanks.
>>
>> josé
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> users
>

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Monday, November 18, 2019 02:48 PM, wrote...
>
> I'm really confused now. I don't understand the semantics of:
> SELECT IfNull('p006', Max(idate)),
>IfNull(b, 1),
>IfNull(c, 2),
>'y',
>IfNull(e, 4),
>'2019-20-12'
>   FROM t
>   WHERE a = 'p006';
>
> versus this:
> SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
> Doesn't the where clause that cannot be satisfied in both cases guarantee 
> that no rows will
> be selected, when there are no records in the database?

Imagine this select:

SELECT 'p006',1, 2, 'y', 4, '2019-02-11';

The IfNull is bringing these values back if the 'p006' does not exists.  Try 
the above select in SQLite3 tool.  You will get a record.  Even without table.  
Since the original question was how can I INSERT... this will check the table 
for 'p006', if it exists, it will bring some of the data from that existing 
record and insert a new one. Ihth.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

I'm really confused now. I don't understand the semantics of:
SELECT IfNull('p006', Max(idate)),
   IfNull(b, 1),
   IfNull(c, 2),
   'y',
   IfNull(e, 4),
   '2019-20-12'
  FROM t
  WHERE a = 'p006';

versus this:
SELECT (a,b,c,d,e,idate) from t where a = "p006"

Doesn't the where clause that cannot be satisfied in both cases guarantee that 
no rows will be selected, when there are no records in the database?
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
> 
> 
> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> Jose Isaias Cabrera
> [clip]
> > > >
> > > > INSERT INTO t (a, b, c, d, e, idate)​
> > > > SELECT IfNull('p006', Max(idate)),​
> > > >IfNull(b, 1),​
> > > >IfNull(c, 2),​
> > > >'y',​
> > > >IfNull(e, 4),​
> > > >'2019-20-12'​
> > > >   FROM t​
> > > >  WHERE a = 'p006';​
> >
> > I think that you will never insert the first record with a query
> like this, since
> > the select returns 0 records of there are none in the database
> yet.
> 
> Well, it does...
> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> idate));
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-20-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> sqlite> select * from t;
> p001|1|2|n|4|2019-20-11
> sqlite>
> 
> And, since I put an uniqueness on a and idate, now these can not
> be repeated, so if I run the same command again,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> Error: UNIQUE constraint failed: t.a, t.idate
> sqlite>
> 
> I do not get a repeated record for 'p001' and 2019-02-11; But if
> they are different,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p002', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p002';
> sqlite> select * from t;
> p001|1|2|n|4|2019-02-11
> p002|1|2|n|4|2019-02-11
> 
> It'll work. Thanks.
> 
> josé
> 
> 
> ___
> 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] When to use nested transaction?

[Simon Slavin]

On 18 Nov 2019, at 7:37pm, Amirouche Boubekki  
wrote:

> I am looking for use-cases for nested transactions. When are nested
> transactions useful in a single writer context?

SQLite doesn't support nested transactions.  If you think you want them, you 
probably want savepoints instead:



But are you asking when you should use BEGIN as opposed to just issuing a 
single command like INSERT by itself ?

Well, given the 'single writer' you put in the question, you're not worried 
about two connections interfering with one-another.  So the answer is about 
cancelling changes, backing out changes, or about what happens if the system 
crashes or loses power.

Suppose I issue two commands which, together, change a row of data.  I delete 
an old row and insert a new one to replace it.  If these are two separate 
commands, SQLite helpfully handles both commands as separate transactions.  And 
it's possible that a power failure or software crash will happen between the 
two, so you may end up with no row at all: the old one was deleted and the new 
one was not inserted.

However, if you do this:

BEGIN
  DELETE FROM ...
  INSERT INTO ...
END

Then no kind of corruption, crash or power loss should be able to do that.  
Either neither of the commands will have an effect, or they both will.  So no 
matter how the data is found or recovered, it will have either the old or the 
new data in.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When to use nested transaction?

[Amirouche Boubekki]

I am looking for use-cases for nested transactions. When are nested
transactions useful in a single writer context?


Thanks in advance!

-- 
Amirouche ~ https://hyper.dev
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Monday, November 18, 2019 12:31 PM, wrote...
Jose Isaias Cabrera
[clip]
> > >
> > > INSERT INTO t (a, b, c, d, e, idate)​
> > > SELECT IfNull('p006', Max(idate)),​
> > >IfNull(b, 1),​
> > >IfNull(c, 2),​
> > >'y',​
> > >IfNull(e, 4),​
> > >'2019-20-12'​
> > >   FROM t​
> > >  WHERE a = 'p006';​
>
> I think that you will never insert the first record with a query like this, 
> since
> the select returns 0 records of there are none in the database yet.

Well, it does...
sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a, idate));
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-20-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
sqlite> select * from t;
p001|1|2|n|4|2019-20-11
sqlite>

And, since I put an uniqueness on a and idate, now these can not be repeated, 
so if I run the same command again,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
Error: UNIQUE constraint failed: t.a, t.idate
sqlite>

I do not get a repeated record for 'p001' and 2019-02-11; But if they are 
different,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p002', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p002';
sqlite> select * from t;
p001|1|2|n|4|2019-02-11
p002|1|2|n|4|2019-02-11

It'll work. Thanks.

josé


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

Re: [sqlite] Deployment question

[Les Woolsey]

Thanks - it's working now.

Les

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Joe Mistachkin
Sent: Sunday, November 17, 2019 8:20 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Deployment question


Les Woolsey wrote:
>
> 1. I deployed the app with the DLL's I had downloaded and the X86 & x64
>folders from the zip file.  Still doesn't work.
> 

This will not work.  The "System.Data.SQLite.dll" managed DLL files are
compiled with different options that dictate how it integrates with the
native library code.

> 
> 2. Deployed the app with the system.data.sqlite.dll file I have installed
>but with the ef6 & Linq dll's  & folders from your zip - Still doesn't
>work
> 

This also won't work, for the same reason as #1.

>
> 3. Deployed the app with the dlls from your zip and the folders - still
>doesn't work.
> 

What is the target operating system?  Was there an error message?

> 
> 4. As I said I my last email, I tried to rebuild my app after refoming
>the reference to my system.data.sqlite and replacing it with a
>reference you the one from the zip.  It doesn't compile but gives
>errors that SQLite does not exist in > the namespace system.data.
>(Also goves further errors that derive from that problem).
> 

I would suggest double-checking the "Target framework" setting for the
project(s) on the "Application" tab within their project properties.

For the set of DLLs from the new download, you'll want to make sure
they are all set to .NET Framework 4.6.

--
Joe Mistachkin

___
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] Specific sqlite_autoindex_* missing in recent SQLite versions

[Vincas Dargis]

2019-11-14 17:56, David Raymond wrote:

Apparently it got smarter about "primary key unique" in 3.20.0 and stopped 
making the extra index when it's a without rowid table. Don't see anything about it in 
the release notes though.

Even on the current release "primary key unique" will still make an extra index 
for the unique if it's a rowid table.


So.. that's kinda regression, a bug?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Saturday, November 16, 2019 10:43 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
> 
> 
> Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...​
> > 
> > One approach might be something like this:​
> > 
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT 'p006',​
> >Coalesce(b, 1),​
> >Coalesce(c, 2),​
> >'y',​
> >Coalesce(e, 4),​
> >'2019-20-12'​
> >   FROM (SELECT 1)​
> >   LEFT JOIN​
> >(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate
> DESC LIMIT 1);​
> > 
> > A slightly more succinct (but not universal) way:​
> > Note: see point 1 of​
> >
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_n
> on_aggregate_result_columns_that_are_not_in_the_group_by_clause​
> > 
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT IfNull('p006', Max(idate)),​
> >IfNull(b, 1),​
> >IfNull(c, 2),​
> >'y',​
> >IfNull(e, 4),​
> >'2019-20-12'​
> >   FROM t​
> >  WHERE a = 'p006';​

I think that you will never insert the first record with a query like this, 
since the select returns 0 records of there are none in the database yet.
Doug


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

Re: [sqlite] Using application_id

[Tobias Leupold]

Hi Clemens!

Thanks for your fast reply!


And/or how can one "register" an application id to prevent collisions?


Submit a patch here.


In my case, that would be:

   --- magic.txt.orig  2019-11-18 18:12:00.957789352 +0100
   +++ magic.txt   2019-11-18 18:13:13.055463773 +0100
   @@ -29,4 +29,7 @@
>68  belong  =0x47503130  OGC GeoPackage version 1.0 file -
>68  belong  =0x45737269  Esri Spatially-Enabled Database -
>68  belong  =0x4d504258  MBTiles tileset -
   +>68  belong  =0x4d75636b  Muckturnier.org tournament database
   +>>60 belong  x\b (Revision %d) -
   +!:mime application/x-muckturnier
>0   string  =SQLite  SQLite3 database

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

Re: [sqlite] Using application_id

[Dominique Devienne]

On Mon, Nov 18, 2019 at 2:41 PM Clemens Ladisch  wrote:

> Tobias Leupold wrote:
> Apparently, authors or 'private' file formats do not bother to register
> their IDs.
>

Indeed, there's little point, as those are rarely "public".

I tend to chose a 4 letter prefix related to the kind of app/report the
SQLite DB is for,
and use the hexa for each letter to generate the app_id 32-bit integer.

If you stick to lower or upper case letters, could encode up to 6 chars in
the app_id. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using application_id

[Clemens Ladisch]

Tobias Leupold wrote:
> In the docs, a magic file is linked ( https://www.sqlite.org/src/
> artifact?ci=trunk=magic.txt ) with "registered" formats.
>
> Is there another list with "taken" application ids?

No.

Apparently, authors or 'private' file formats do not bother to register
their IDs.

> And/or how can one "register" an application id to prevent collisions?

Submit a patch here.


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

Re: [sqlite] Relax "DISTINCT aggregates" error

[Jose Isaias Cabrera]

Dominique Devienne, on Monday, November 18, 2019 04:33 AM, wrote...
>
> On Fri, Nov 15, 2019 at 4:22 PM Jose Isaias Cabrera, on
> wrote:
>
> > Dominique Devienne, on Friday, November 15, 2019 09:02 AM, wrote...
> >
> > Have you tried this,
> > sqlite> select group_concat(distinct id || ', ') from t;
> > 1, ,2, ,4, ,7,
> >
> > The only problem is that when the list has a non-distinct.  H.
> >
>
> That's obviously not the same result (trailing text), and a bit hackish.
> Clever, but a no-go IMHO. I'm hoping for a "real" fix taking into account
> the constant-ness of trailing arguments, having only the first arg being
> row-dependent. --DD

Yes, understood.

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

[sqlite] Using application_id

[Tobias Leupold]

Hi list!

I recently learned about the PRAGMA application_id feature of SQLite, which is
really nice to be able to identify an application-specific SQLite database at
the file system level, even with versioning support via the user_version
header.

SQLite encourages to use an SQLite database as one's application file format,
which I do for one of my projects (like probably hundreds of other projects
do). In the docs, a magic file is linked ( https://www.sqlite.org/src/
artifact?ci=trunk=magic.txt ) with "registered" formats.

With SQLite being so widely used, I can hardly believe that only 6 projects
decided to make use of the application_id header until now.

So here's my question: Is there another list with "taken" application ids?
Some kind of wiki perhaps? And/or how can one "register" an application id to
prevent collisions?

Thanks for the information in advance!

Cheers, Tobias


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

Re: [sqlite] Official Github Mirror? Of the Amalgamation?

[Dominique Devienne]

On Mon, Nov 18, 2019 at 10:41 AM Shawn Wagner 
wrote:

> There is an official github mirror of the fossil repository:
> https://github.com/sqlite/sqlite


Thanks. I thought there was one, but search below didn't find it:
https://www.google.com/search?q=sqlite+github+official+mirror

It's weird the official repo mirror doesn't rank very high.

Maybe if https://sqlite.org/whynotgit.html linked to it, it might.

At least I would have found it that way, since as the 3rd result from
Google I visited that Why-not-GIT link,
but it's not mentioned there either apparently (not at the top at least) .
Probably should IMHO.


> The amalgamation is two files, though. When you want to upgrade a bundled
> sqlite, to a new version it's trivial to update them. Setting up submodules
> or whatever seems like massive overkill.


OK, after a few more searches, seems like it's not worth it indeed.

Thanks again, --DD

PS: It feels wrong to me, it should be super easy to cherry pick files
(at given tag, hash, date, etc...) from another same-SCM repo IMHO.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Official Github Mirror? Of the Amalgamation?

[Shawn Wagner]

There is an official github mirror of the fossil repository:
https://github.com/sqlite/sqlite

The amalgamation is two files, though. When you want to upgrade a bundled
sqlite, to a new version it's trivial to update them. Setting up submodules
or whatever seems like massive overkill.


On Mon, Nov 18, 2019 at 1:30 AM Dominique Devienne 
wrote:

> In searches,  https://github.com/mackyle/sqlite comes up first,
> but given that Fossil has good/better interop with GIT these days,
> why not an official mirror?
>
> Also, mirrors are for the normal repo, while ability to refer to an
> amalgamation
> in one's project by directly linking to it via a GIT submodule (or
> something like
> that, I'm no GIT expert) instead of copying it in one's project(s), would
> be useful IMHO.
>
> Just my $0.02c. --DD
> ___
> 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] Relax "DISTINCT aggregates" error

[Dominique Devienne]

On Fri, Nov 15, 2019 at 4:22 PM Jose Isaias Cabrera 
wrote:

> Dominique Devienne, on Friday, November 15, 2019 09:02 AM, wrote...
>
> Have you tried this,
> sqlite> select group_concat(distinct id || ', ') from t;
> 1, ,2, ,4, ,7,
>
> The only problem is that when the list has a non-distinct.  H.
>

That's obviously not the same result (trailing text), and a bit hackish.
Clever, but a no-go IMHO. I'm hoping for a "real" fix taking into account
the constant-ness of trailing arguments, having only the first arg being
row-dependent. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Official Github Mirror? Of the Amalgamation?

[Dominique Devienne]

In searches,  https://github.com/mackyle/sqlite comes up first,
but given that Fossil has good/better interop with GIT these days,
why not an official mirror?

Also, mirrors are for the normal repo, while ability to refer to an
amalgamation
in one's project by directly linking to it via a GIT submodule (or
something like
that, I'm no GIT expert) instead of copying it in one's project(s), would
be useful IMHO.

Just my $0.02c. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users