Re: [sqlite] column types and constraints

[Keith Medcalf]

But there is no API which will permit you to retrieve "unsigned" integers from 
SQLite3.
Nor is there an API which will let you "send" an unsigned integer to SQLite3 
for storage in the database.
All the sqlite3_column and sqlite3_bind interfaces deal only with signed 
integers.

If you are using using compiler magic (ie, a cast) to fiddle-diddle with the 
declared interpretation of the SQLite3 APIs, then it is probably best that you 
know what you are doing.  (Similarly if you use "some bits" of a pointer for 
other than their intended purpose (that is, pointing to an address in linear 
virtual memory) then you should expect the explosions and faults which will 
ensue).

It is entirely possible to use a bag-o-bytes to declare all data values in your 
application, and then use compiler magic (ie, casts) to cause the compiler into 
generating whatever machine code your little heart might desire, however, you 
better really really know what you are doing if you go that route.

The same applies if you expect SQLite3 to behave in a manner different than 
having only the datatypes integer, double, text, and bag-o-bytes, and using 
prayerful declarations that do not reflect the inherent data interfaces falls 
into the same bucket.

In other words, what is stored is what you put, and what you get back is what 
you asked for.  If you want to know what was put, you need to ask.  If you 
don't ask and make an assumption, then you best be prepared to deal with the 
result of what was put converted to what you asked for.

So what you really want is (a) to be able to error-out on SQL operations that 
do not use proper affinity words (ie, only accept INTEGER, DOUBLE, TEXT and 
BLOB), and; (b) to crash or return an error instead of converting the data that 
you put or at least, that you ask.  That way at least if you use a stupid table 
declaration like

CREATE TABLE theTable (theColumn unsigned integer(1024));

you will get a syntax error since neither the word "unsigned" nor a value in 
(brackets) are proper affinity declarations.

Similarly if a datavalue is "text" and you ask for an "integer" you get an 
error since you are not "asking" for data which matches what was stored.

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Friday, 29 June, 2018 19:35
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>On Jun 29, 2018, at 4:36 PM, Keith Medcalf 
>wrote:
>>
>> All of the issues raised are "application" problems, not database
>problems.
>
>Computers are here to aid humans, not the other way around.
>
>> Clearly if you retrieved a value from the database and want to use
>it as an index you have to do bounds checking.
>
>Why?  I told the DBMS that the values in that column will be unsigned
>integers, yet it accepted a non-integer for storage and then yielded
>a negative value on retrieval.
>
>This code will yield a complaint from a sufficiently on-the-ball C
>compiler:
>
>unsigned foo = external_function();
>if (foo >= 0) do_happy_path();
>
>It will rightly complain that the condition is always true.
>
>> The ability to forsee that the world may not be entirely as you
>expect is the root of the difference between a mere coder and a
>professional software programmer.
>
>Therefore, all of the bugs written in C that we can attribute to
>language design issues were perpetrated by mere coders.  No true
>Scotsman^Wprogrammer would ever make such a mistake.  Compiler
>diagnostics are for the weak.  TODAY IS A GOOD DAY TO WRITE SOFTWARE.
>___
>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] column types and constraints

[Warren Young]

On Jun 29, 2018, at 4:36 PM, Keith Medcalf  wrote:
> 
> All of the issues raised are "application" problems, not database problems.

Computers are here to aid humans, not the other way around.

> Clearly if you retrieved a value from the database and want to use it as an 
> index you have to do bounds checking.

Why?  I told the DBMS that the values in that column will be unsigned integers, 
yet it accepted a non-integer for storage and then yielded a negative value on 
retrieval.

This code will yield a complaint from a sufficiently on-the-ball C compiler:

unsigned foo = external_function();
if (foo >= 0) do_happy_path();

It will rightly complain that the condition is always true.

> The ability to forsee that the world may not be entirely as you expect is the 
> root of the difference between a mere coder and a professional software 
> programmer.

Therefore, all of the bugs written in C that we can attribute to language 
design issues were perpetrated by mere coders.  No true Scotsman^Wprogrammer 
would ever make such a mistake.  Compiler diagnostics are for the weak.  TODAY 
IS A GOOD DAY TO WRITE SOFTWARE.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Keith Medcalf]

pragma application_written_by=coder;

-vs the default, which is-

pragma application_written_by=programmer;

;-)

All of the issues raised are "application" problems, not database problems.  
Clearly if you retrieved a value from the database and want to use it as an 
index you have to do bounds checking.  The ability to forsee that the world may 
not be entirely as you expect is the root of the difference between a mere 
coder and a professional software programmer.  (Not to mention, it was the same 
error that allowed the errant value to get into the database in the first 
place).

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Friday, 29 June, 2018 13:00
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>On Jun 29, 2018, at 11:46 AM, Richard Hipp  wrote:
>>
>> On 6/29/18, Bob Friesenhahn  wrote:
>>>
>>> Without adding all the necessary safe-guards to ensure that only
>valid
>>> data goes into the database, sqlite puts the using application at
>risk
>>> (security and stability) with its wishy-washy ways.
>>
>> Can you provide an example of a security of stability problem
>caused
>> by flexible typing?
>
>The following is an answer to your challenge, not a feature request.
>I’d enable strong typing support in SQLite if it were available, but
>I started using SQLite knowing its stance on typing, so I can’t
>honorably demand it now.
>
>Nevertheless:
>
>1. In defining a table schema, declare a column as UNSIGNED INTEGER;
>SQLite ignores the “UNSIGNED” and uses INTEGER affinity.
>
>2. Store “-1FRED” in that column, for which SQLite uses TEXT
>affinity, so as to not lose any data.
>
>3. Retrieve the value with sqlite3_column_int(), which forces a CAST
>to INT, resulting in -1, that being the longest prefix that is INT-
>like.
>
>4. The application uses the value as an index into an array.  If the
>application passes int* to sqlite3_column_int() to avoid compiler
>complaints, they’ll get a negative index.  If they pass unsigned*
>instead, casting it to int* to placate the compiler, they get
>UINT_MAX on a 2’s complement machine, which will certainly crash the
>program when used as an array index.  Either way, a security exploit
>is probably available.
>
>If your reaction is that the application shouldn’t have allowed input
>of “-1FRED” for an integer value, that’s true, but it would be nice
>if SQLite would backstop the application’s restrictions.  The
>application tried to tell SQLite it wanted help enforcing its limits
>when giving the UNSIGNED attribute in declaring the table schema.
>
>If you say that the application shouldn’t have trusted the value it
>got from SQLite, why not?  From the application programmer’s
>perspective, it’s now validated data.
>
>It is of course possible to work around all of this.  The application
>programmer “just” has to write checks in layers closer to the end
>user, checks which are not strictly necessary with other DBMSes.
>People coming from those other DBMSes reasonably expect the data to
>be implicitly trustworthy once it is finally at rest.
>___
>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] column types and constraints

[R Smith]

On 2018/06/30 12:05 AM, Thomas Kurz wrote:

Indeed, but that option does exist, it's called CHECK constraints

You're clearly right, but from my point of view, it's redundant to say COLUMN xy INTEGER 
CHECK type=integer, because "COLUMN INTEGER" already implies that the column is 
integer. And, btw, as CHECK already exists, it shouldn't be too complicated to have a 
mode where this CHECKing is automatically done//...


I don't disagree, but this means we lose sight of the important point 
that, if you distill the problem to just "INTEGER", then sure, it looks 
silly, and sure, you can fix it with internal auto-CHECKing, but in 
SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT, 
SMALLINT, INT, BIT, BOOL and possibly a slew of other types that escapes 
me now. Some of which will cease to function the same once you bind 
INTEGER to a stiff type format. Now you need to start introducing these 
as their own types, which means SQLite INTEGER affinity is no longer 
feasible. The same holds true for other affinities.  It's either a 
type-affinity system, or a Type-system, it can't/shouldn't be both.



My problem is rather that SQLite treats a column declared with an invalid type 
as NUMERIC. This can lead to problems that aren't quite obvious. COLUMN xy 
DOG_CAT_MOUSE should definitely raise an error.


Okay... what about:
COLUMN xy VARCHAR(50)
or
COLUMN xy MEDIUMTEXT
or
COLUMN xy BIT
or
COLUMN xy DECIMAL(5,2)

Should those raise errors? Because to SQLite those are the exact same 
gibberish as:


COLUMN xy DOG_CAT_MOUSE(99)

It's either accepting unknown types, or not. There is no middle road.

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

Re: [sqlite] unique values from a subset of data based on two fields

[Keith Medcalf]

>I want a query that returns all of the records with status = 1 and
>unique records, based on name, where the status =0 and the name is 
>not in the list status=1

Translation into SQL using English to SQL Translator, using the most direct 
translation on the "problem statement" above directly into SQL:

create table names (id int, status int, name text);
insert into names values (1, 1, 'paul');
insert into names values (2, 1, 'helen');
insert into names values (3, 0, 'steve');
insert into names values (4, 0, 'steve');
insert into names values (5, 0, 'pete');
insert into names values (6, 0, 'paul');

-- I want a query that returns all of the records with status = 1

SELECT id,
   status,
   name
  FROM names
 WHERE status == 1 

-- and

UNION

-- unique records, based on name, where the status = 0 and the name is not in 
the list [of names where] status=1

SELECT id,
   status,
   name
  FROM names
 WHERE status == 0
   AND name NOT IN (SELECT name
  FROM names
 WHERE status == 1)
GROUP BY name;

Returns the rows:

1|1|paul
2|1|helen
3|0|steve
5|0|pete

If the table is bigger than trivial (ie, contains more than the number of rows 
you can count with your fingers) then you will need the appropriate indexes to 
achieve performant results.

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
>Sent: Friday, 29 June, 2018 09:50
>To: General Discussion of SQLite Database
>Subject: [sqlite] unique values from a subset of data based on two
>fields
>
>I have a table
>
>Create table names (id int, status int, name text)
>
>
>
>1, 1, 'paul'
>
>2, 1,'helen'
>
>3, 0, 'steve'
>
>4, 0, 'steve'
>
>5, 0, 'pete'
>
>6, 0, 'paul'
>
>
>
>I want a query that returns all of the records with status = 1 and
>unique
>records, based on name, where the status =0 and the name is not in
>the list
>status=1
>
>
>
>So from the above I would want to see
>
>
>
>1, 1, paul
>
>2, 1, helen
>
>3, 0, steve (or 4, 0, steve)
>
>5, 0, pete
>
>
>
>I could do something like
>
>
>
>Select * from names where status = 1 or name not in (select name from
>names
>where status = 1)
>
>
>
>But this gets both rows for steve, e.g.
>
>
>
>1, 1, paul
>
>2, 1, helen
>
>3, 0, steve
>
>4, 0, steve
>
>5, 0, pete
>
>while I am not bothered about which of the two steves I get back, I
>must
>have all occurences of names with status = 1
>
>I am probably missing somethng obvious
>
>Paul
>www.sandersonforensics.com
>SQLite Forensics Book 
>___
>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] column types and constraints

[Thomas Kurz]

> Indeed, but that option does exist, it's called CHECK constraints

You're clearly right, but from my point of view, it's redundant to say COLUMN 
xy INTEGER CHECK type=integer, because "COLUMN INTEGER" already implies that 
the column is integer. And, btw, as CHECK already exists, it shouldn't be too 
complicated to have a mode where this CHECKing is automatically done without 
having me to declare (and forget) it in every column. (Just the way foreign key 
checks can be turned on or off. It can default to off without any problem.)

My problem is rather that SQLite treats a column declared with an invalid type 
as NUMERIC. This can lead to problems that aren't quite obvious. COLUMN xy 
DOG_CAT_MOUSE should definitely raise an error.

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

Re: [sqlite] column types and constraints

[R Smith]

On 2018/06/29 10:23 PM, Thomas Kurz wrote:
b) I think that noone wants type affinity to be actually removed. I'd 
just like an option to make SQLite behave like any other DBMS and

- respect the data type declared for a column
- reject column definitions with invalid types (e.g. "STRING")


Indeed, but that option does exist, it's called CHECK constraints (and 
if you would like to get elaborate, even Triggers), but I also note 
Bob's point that these do consume more resources (probably significantly 
more in the case of Triggers) than had it been a Type-constraint.  A 
major advantage is that you can be the author of when to check and when 
not to, you can be the author of what /exactly/ to check - DB Engines 
vary in how they check[*], but with SQLite you are the master of your 
destiny.


[*] - A good example is a VARCHAR(3) constraint. Adding the string 
"MAMA" into that field will, depending on the DB:
- FAIL your query with an error in MSSQL, one that may never have popped 
up during dev cycle but suddenly do in production.
- Truncate the string to "MAM" in MySQL, without an error at all, just 
kill some data and move on!

- Put the string "MAMA" into the DB without an error in SQLite.

Which of these do you prefer? Whichever it is, you can mimic it in 
SQLite with a trigger or check constraint. What you cannot do though, is 
mimic SQLite's behaviour in any other Engine. Think about that.



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

Re: [sqlite] column types and constraints

[Simon Slavin]

On 29 Jun 2018, at 9:23pm, Thomas Kurz  wrote:

> a) Why would I put strings in an integer column? If I need to do so, I have a 
> concept error in my database schema.
> 
> b) I think that noone wants type affinity to be actually removed. I'd just 
> like an option to make SQLite behave like any other DBMS and
> - respect the data type declared for a column
> - reject column definitions with invalid types (e.g. "STRING")

Here's the question: do you want consistency or do you want safety ?

If you try to put TEXT into a REAL column you should get an error message, 
right ?

If you try to put REAL into an INTEGER column, you should get an error message, 
right ?

Or not ?  Do you want automatic truncation rather than an error message ?  Do 
you know what PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL, 
Firebird, SQL Anywhere, and Borland Interbase do ? Do you want compatibility 
with all of them ?

Those are rhetorical questions.  I don't really want you to answer them.  But 
I'm trying to point out that the SQL standard doesn't say what should happen 
and many other SQL engines have made their own choices.

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

Re: [sqlite] column types and constraints

[Bob Friesenhahn]

On Fri, 29 Jun 2018, Richard Hipp wrote:


On 6/29/18, Bob Friesenhahn  wrote:


Without adding all the necessary safe-guards to ensure that only valid
data goes into the database, sqlite puts the using application at risk
(security and stability) with its wishy-washy ways.



Can you provide an example of a security of stability problem caused
by flexible typing?


It is only necessary for the database to return something that the 
application is not designed for in order to cause problems for the 
application.  The ability to inject wrong data depends on the 
interfaces which are exposed for introducing the wrong data (possibly 
including the sqlite3 shell), and any added safeguards in the database 
itself.


Assuring that the expected type is returned is just part of the 
problem since often only particular values or ranges are allowable.


This is why our database includes many checks (including using 
triggers) to defend against errant data (including the underlying 
type) on the way in.  Unfortunately, such checks make the schema 
larger, consuming more RAM in all applications using the database.


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

Re: [sqlite] column types and constraints

[Bob Friesenhahn]

On Fri, 29 Jun 2018, R Smith wrote:
Type-constraining here would merely protect the programmer against 
him/herself. I know this is not entirely without merit, but a feature I would 
happily forego when weighed against even just one of the points made in the 
previous paragraph.


The 'lite' in 'sqlite3' does not prohibit the same database from being 
developed by many programers using a variety of programming languages 
and scripts to solve very large problems.  The stability of the sqlite 
file format and column type integrity are two different things.


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

Re: [sqlite] column types and constraints

[Abroży Nieprzełoży]

SQLite returns what has been saved in the database, the application
should control what it writes to the database.

If the database can be edited by an untrusted entity, the application
should treat the content of the database as untrusted.

Even if SQLite enforced data types, someone who can edit the database
could also change the schema accordingly to allow to store malicious
data.


2018-06-29 21:00 GMT+02:00, Warren Young :
> On Jun 29, 2018, at 11:46 AM, Richard Hipp  wrote:
>>
>> On 6/29/18, Bob Friesenhahn  wrote:
>>>
>>> Without adding all the necessary safe-guards to ensure that only valid
>>> data goes into the database, sqlite puts the using application at risk
>>> (security and stability) with its wishy-washy ways.
>>
>> Can you provide an example of a security of stability problem caused
>> by flexible typing?
>
> The following is an answer to your challenge, not a feature request.  I’d
> enable strong typing support in SQLite if it were available, but I started
> using SQLite knowing its stance on typing, so I can’t honorably demand it
> now.
>
> Nevertheless:
>
> 1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite
> ignores the “UNSIGNED” and uses INTEGER affinity.
>
> 2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as
> to not lose any data.
>
> 3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT,
> resulting in -1, that being the longest prefix that is INT-like.
>
> 4. The application uses the value as an index into an array.  If the
> application passes int* to sqlite3_column_int() to avoid compiler
> complaints, they’ll get a negative index.  If they pass unsigned* instead,
> casting it to int* to placate the compiler, they get UINT_MAX on a 2’s
> complement machine, which will certainly crash the program when used as an
> array index.  Either way, a security exploit is probably available.
>
> If your reaction is that the application shouldn’t have allowed input of
> “-1FRED” for an integer value, that’s true, but it would be nice if SQLite
> would backstop the application’s restrictions.  The application tried to
> tell SQLite it wanted help enforcing its limits when giving the UNSIGNED
> attribute in declaring the table schema.
>
> If you say that the application shouldn’t have trusted the value it got from
> SQLite, why not?  From the application programmer’s perspective, it’s now
> validated data.
>
> It is of course possible to work around all of this.  The application
> programmer “just” has to write checks in layers closer to the end user,
> checks which are not strictly necessary with other DBMSes.  People coming
> from those other DBMSes reasonably expect the data to be implicitly
> trustworthy once it is finally at rest.
> ___
> 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] column types and constraints

[R Smith]

On 2018/06/29 9:00 PM, Warren Young wrote:

The following is an answer to your challenge, not a feature request//

4. The application uses the value as an index into an array.  If the 
application passes int* to sqlite3_column_int() to avoid compiler complaints, 
they’ll get a negative index.  If they pass unsigned* instead, casting it to 
int* to placate the compiler, they get UINT_MAX on a 2’s complement machine, 
which will certainly crash the program when used as an array index.  Either 
way, a security exploit is probably available.


I agree this demonstrates very well that SQLite can be one of the tools 
that touches data along a gauntlet of horribly bad programming practices 
that ends in a possible security loophole. However, it in no way 
demonstrates  duck-typing to have a causal relationship to the 
vulnerability.  You may as well have argued that the programmer read the 
value -7 from a file he expected would have a positive integer and then 
massaged it into a bad array-pointer through the same programming steps 
- this doesn't render fopen() a security risk.


Further to this, how is this different from reading a very valid integer 
from, say MySQL, with a value of 722 when your array is only 120 long? 
If you are not going to range-check it, there's an immediate security 
risk right there[*]. Is this MySQL's fault?




It is of course possible to work around all of this.  The application 
programmer “just” has to write checks in layers closer to the end user, checks 
which are not strictly necessary with other DBMSes.  People coming from those 
other DBMSes reasonably expect the data to be implicitly trustworthy once it is 
finally at rest.


I think this statement is a great summation of the real problem, and I 
have to agree, perhaps in an "Introduction to SQLite" section on the 
website one can add/improve a "Types in SQLite" section that better 
prepares such programmers coming from alternate engines. We often see 
people posting here being confused by it in some way - so definitely an 
important thing.



Cheers!
Ryan

[*] - Yes, the original demonstration hinged on the "unsigned" type 
constraint being violated, but the end-result vulnerability proposed was 
a range-check failure, not a sign failure.


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

Re: [sqlite] unique values from a subset of data based on two fields

[Paul Sanderson]

Thanks Ryan

As often is the case the the actual problem is more complex than my example
- sometimes we over simplify to, well, simplify - but you have both given
me some ideas and I'll go away and play.

Paul

Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 29 June 2018 at 20:24, R Smith  wrote:

> The solution from David works perfectly, just want to point out some CTE
> things since you mention getting into it.
> 1st - A nice thing about CTE is that, in the case of a non-recursive CTE
> (like this one), it can well be replaced by a simple sub-query, however,
> the CTE can be referenced more than once, unlike a sub-query.
> 2nd - One can even use the CTE in other sub-queries.
>
> With this in mind, here is another option for the query which is
> functionally equivalent (i.e. it's not better, simply showing alternate CTE
> use):
>
> with status_one as (
>   select *
> from names
>where status = 1
> )
> select min(id), status, name
>   from names
>  where status = 0 and name not in (select name from status_one)
>  group by status, name
> union all
> select * from status_one
> ;
>
>
> CTE capability is one of my favourite additions ever to SQLite (I may have
> mentioned this before), so I hope you too find them useful and joyful.
>
> Cheers,
> Ryan
>
>
> On 2018/06/29 6:45 PM, David Raymond wrote:
>
>> with status_one as (
>>select *
>>from names
>>where status = 1
>> ),
>> one_names as (
>>select distinct name
>>from status_one
>> )
>> select min(id), status, name
>> from names
>> where status = 0
>>and name not in one_names
>> group by status, name
>>
>> union all
>>
>> select * from status_one;
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Paul Sanderson
>> Sent: Friday, June 29, 2018 11:50 AM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] unique values from a subset of data based on two fields
>>
>> I have a table
>>
>> Create table names (id int, status int, name text)
>>
>>
>>
>> 1, 1, 'paul'
>>
>> 2, 1,'helen'
>>
>> 3, 0, 'steve'
>>
>> 4, 0, 'steve'
>>
>> 5, 0, 'pete'
>>
>> 6, 0, 'paul'
>>
>>
>>
>> I want a query that returns all of the records with status = 1 and unique
>> records, based on name, where the status =0 and the name is not in the
>> list
>> status=1
>>
>>
>>
>> So from the above I would want to see
>>
>>
>>
>> 1, 1, paul
>>
>> 2, 1, helen
>>
>> 3, 0, steve (or 4, 0, steve)
>>
>> 5, 0, pete
>>
>>
>>
>> I could do something like
>>
>>
>>
>> Select * from names where status = 1 or name not in (select name from
>> names
>> where status = 1)
>>
>>
>>
>> But this gets both rows for steve, e.g.
>>
>>
>>
>> 1, 1, paul
>>
>> 2, 1, helen
>>
>> 3, 0, steve
>>
>> 4, 0, steve
>>
>> 5, 0, pete
>>
>> while I am not bothered about which of the two steves I get back, I must
>> have all occurences of names with status = 1
>>
>> I am probably missing somethng obvious
>>
>> Paul
>> www.sandersonforensics.com
>> SQLite Forensics Book 
>> ___
>> 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] column types and constraints

[Thomas Kurz]

> At least, enough-so in my mind to defend David's assessment of "an 
> excellent decision".

Sorry, I cannot follow the point.

a) Why would I put strings in an integer column? If I need to do so, I have a 
concept error in my database schema.

b) I think that noone wants type affinity to be actually removed. I'd just like 
an option to make SQLite behave like any other DBMS and
- respect the data type declared for a column
- reject column definitions with invalid types (e.g. "STRING")

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

Re: [sqlite] unique values from a subset of data based on two fields

[R Smith]

The solution from David works perfectly, just want to point out some CTE 
things since you mention getting into it.
1st - A nice thing about CTE is that, in the case of a non-recursive CTE 
(like this one), it can well be replaced by a simple sub-query, however, 
the CTE can be referenced more than once, unlike a sub-query.

2nd - One can even use the CTE in other sub-queries.

With this in mind, here is another option for the query which is 
functionally equivalent (i.e. it's not better, simply showing alternate 
CTE use):


with status_one as (
  select *
from names
   where status = 1
)
select min(id), status, name
  from names
 where status = 0 and name not in (select name from status_one)
 group by status, name
union all
select * from status_one
;


CTE capability is one of my favourite additions ever to SQLite (I may 
have mentioned this before), so I hope you too find them useful and joyful.


Cheers,
Ryan

On 2018/06/29 6:45 PM, David Raymond wrote:

with status_one as (
   select *
   from names
   where status = 1
),
one_names as (
   select distinct name
   from status_one
)
select min(id), status, name
from names
where status = 0
   and name not in one_names
group by status, name

union all

select * from status_one;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul Sanderson
Sent: Friday, June 29, 2018 11:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] unique values from a subset of data based on two fields

I have a table

Create table names (id int, status int, name text)



1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'



I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete



I could do something like



Select * from names where status = 1 or name not in (select name from names
where status = 1)



But this gets both rows for steve, e.g.



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
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] column types and constraints

[Warren Young]

On Jun 29, 2018, at 11:46 AM, Richard Hipp  wrote:
> 
> On 6/29/18, Bob Friesenhahn  wrote:
>> 
>> Without adding all the necessary safe-guards to ensure that only valid
>> data goes into the database, sqlite puts the using application at risk
>> (security and stability) with its wishy-washy ways.
> 
> Can you provide an example of a security of stability problem caused
> by flexible typing?

The following is an answer to your challenge, not a feature request.  I’d 
enable strong typing support in SQLite if it were available, but I started 
using SQLite knowing its stance on typing, so I can’t honorably demand it now.

Nevertheless:

1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite 
ignores the “UNSIGNED” and uses INTEGER affinity.

2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as to 
not lose any data.

3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT, 
resulting in -1, that being the longest prefix that is INT-like.

4. The application uses the value as an index into an array.  If the 
application passes int* to sqlite3_column_int() to avoid compiler complaints, 
they’ll get a negative index.  If they pass unsigned* instead, casting it to 
int* to placate the compiler, they get UINT_MAX on a 2’s complement machine, 
which will certainly crash the program when used as an array index.  Either 
way, a security exploit is probably available.

If your reaction is that the application shouldn’t have allowed input of 
“-1FRED” for an integer value, that’s true, but it would be nice if SQLite 
would backstop the application’s restrictions.  The application tried to tell 
SQLite it wanted help enforcing its limits when giving the UNSIGNED attribute 
in declaring the table schema.

If you say that the application shouldn’t have trusted the value it got from 
SQLite, why not?  From the application programmer’s perspective, it’s now 
validated data.

It is of course possible to work around all of this.  The application 
programmer “just” has to write checks in layers closer to the end user, checks 
which are not strictly necessary with other DBMSes.  People coming from those 
other DBMSes reasonably expect the data to be implicitly trustworthy once it is 
finally at rest.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[R Smith]

On 2018/06/29 6:25 PM, Bob Friesenhahn wrote:

On Fri, 29 Jun 2018, David Burgess wrote:


"This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values//...

This is an important feature of SQLite.  In hindsight, an excellent 
decision.


It is sloppy and absent additional constraints and checks added to the 
schema, it puts a burden on the consumer of the data to assure that it 
is the correct type.

It is not so clear that it was an excellent decision.
If the application requires an integer value, why do you think that it 
is appropriate to pass it the string 'xyzzy'?


Unlike other SQL Engines that operates in a User-interaction-space, 
SQLite operates in an application-interaction space. It is part and 
parcel of the application (or at least via a library that services 
direct API calls), and so where user-interactive databases have Type 
constraints as a way to force those from outside your app, it comes with 
penalties. To name a few:
- it has to define very many kinds of variable types to cope with the 
different constraint needs, and
- add more over time, which apart from being hefty, doesn't support the 
future-proof file format that SQLite does (that's why you need to run 
upgrade programs when installing a new MySQL DB, for example),
- Once committed to a type, changing the nature of data that can be 
added requires a Schema change (not too hard if your DB is one server 
computer, but what if it runs in a billion separate devices?).
- Extending the last point - this is even true if you want to do as 
simple an exercise as allowing 40-char strings where you previously had 
30-char strings, because the client thought they knew their own requirement.


While this all does serve to protect you from external users adding 
non-conformant data, in SQLite's case the DB is application-interactive, 
and apart from solving all of the above, any data that gets pushed to 
your DB file typically is either generated by the application, inputted 
via the application, or at a minimum vetted/controlled by the 
application. Type-constraining here would merely protect the programmer 
against him/herself. I know this is not entirely without merit, but a 
feature I would happily forego when weighed against even just one of the 
points made in the previous paragraph.


At least, enough-so in my mind to defend David's assessment of "an 
excellent decision".


What it definitely doesn't do, is create a security vulnerability or 
system-level risk.  (i.e. Any potential risk it does create is one that 
can easily be mitigated by code in your own application)


Maybe not the perfect solution, but I'd say a good alternative to the 
other good-but-non-perfect Types employed in other Engines.



I hope that answers your question somewhat,
Cheers!
Ryan

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

Re: [sqlite] column types and constraints

[Richard Hipp]

On 6/29/18, Bob Friesenhahn  wrote:
>
> Without adding all the necessary safe-guards to ensure that only valid
> data goes into the database, sqlite puts the using application at risk
> (security and stability) with its wishy-washy ways.
>

Can you provide an example of a security of stability problem caused
by flexible typing?
-- 
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

Re: [sqlite] unique values from a subset of data based on two fields

[Paul Sanderson]

Thank You David - I was just starting to play with CTEs



Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 29 June 2018 at 17:45, David Raymond  wrote:

> with status_one as (
>   select *
>   from names
>   where status = 1
> ),
> one_names as (
>   select distinct name
>   from status_one
> )
> select min(id), status, name
> from names
> where status = 0
>   and name not in one_names
> group by status, name
>
> union all
>
> select * from status_one;
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Friday, June 29, 2018 11:50 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] unique values from a subset of data based on two fields
>
> I have a table
>
> Create table names (id int, status int, name text)
>
>
>
> 1, 1, 'paul'
>
> 2, 1,'helen'
>
> 3, 0, 'steve'
>
> 4, 0, 'steve'
>
> 5, 0, 'pete'
>
> 6, 0, 'paul'
>
>
>
> I want a query that returns all of the records with status = 1 and unique
> records, based on name, where the status =0 and the name is not in the list
> status=1
>
>
>
> So from the above I would want to see
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve (or 4, 0, steve)
>
> 5, 0, pete
>
>
>
> I could do something like
>
>
>
> Select * from names where status = 1 or name not in (select name from names
> where status = 1)
>
>
>
> But this gets both rows for steve, e.g.
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve
>
> 4, 0, steve
>
> 5, 0, pete
>
> while I am not bothered about which of the two steves I get back, I must
> have all occurences of names with status = 1
>
> I am probably missing somethng obvious
>
> Paul
> www.sandersonforensics.com
> SQLite Forensics Book 
> ___
> 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] column types and constraints

[Tim Streater]

On 29 Jun 2018, at 17:46, Bob Friesenhahn  wrote:

> On Fri, 29 Jun 2018, danap wrote:
>>
>> Unless your trying to create a generic user interface.
>>
>> I have spent the last month trying to solve affinity with the columns.
>> The only way it seems to me to guarantee to solve the issue is to test
>> every retrieved column value and test its affinity.
>
> Affinity is only a hint and not an assurance of anything.
>
> I solve the problem by bloating the schema with checks like this:
>
>foo integer default 1234
>   check (typeof(foo) == 'integer'),
>
> This enforces that someone can't put "Hello world" where an integer 
> belongs.
>
> Without adding all the necessary safe-guards to ensure that only valid 
> data goes into the database, sqlite puts the using application at risk 
> (security and stability) with its wishy-washy ways.

No one is forcing you, or anyone else, to use SQLite.


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

Re: [sqlite] column types and constraints

[Bob Friesenhahn]

On Fri, 29 Jun 2018, danap wrote:


Unless your trying to create a generic user interface.

I have spent the last month trying to solve affinity with the columns.
The only way it seems to me to guarantee to solve the issue is to test
every retrieved column value and test its affinity.


Affinity is only a hint and not an assurance of anything.

I solve the problem by bloating the schema with checks like this:

  foo integer default 1234
 check (typeof(foo) == 'integer'),

This enforces that someone can't put "Hello world" where an integer 
belongs.


Without adding all the necessary safe-guards to ensure that only valid 
data goes into the database, sqlite puts the using application at risk 
(security and stability) with its wishy-washy ways.


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

Re: [sqlite] unique values from a subset of data based on two fields

[David Raymond]

with status_one as (
  select *
  from names
  where status = 1
),
one_names as (
  select distinct name
  from status_one
)
select min(id), status, name
from names
where status = 0
  and name not in one_names
group by status, name

union all

select * from status_one;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul Sanderson
Sent: Friday, June 29, 2018 11:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] unique values from a subset of data based on two fields

I have a table

Create table names (id int, status int, name text)



1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'



I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete



I could do something like



Select * from names where status = 1 or name not in (select name from names
where status = 1)



But this gets both rows for steve, e.g.



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
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] column types and constraints

[danap]

"This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values into columns - it
tries to convert if it can do so without loss of information but if it
cannot do a reversible type conversion it simply stores whatever you
give it.  Hence if you store a string '3456' into an INT column, it
converts the string into an integer, but if you store a string 'xyzzy'
in an INT column it will actually store the string value."


> From: David Burgess

This is an important feature of SQLite.  In hindsight, an excellent decision.


Unless your trying to create a generic user interface.

I have spent the last month trying to solve affinity with the columns.
The only way it seems to me to guarantee to solve the issue is to test
every retrieved column value and test its affinity.

The easier approach chosen just check the column affinity and assume
thats what is going to be stored there.

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

Re: [sqlite] column types and constraints

[Bob Friesenhahn]

On Fri, 29 Jun 2018, David Burgess wrote:


"This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values into columns - it
tries to convert if it can do so without loss of information but if it
cannot do a reversible type conversion it simply stores whatever you
give it.  Hence if you store a string '3456' into an INT column, it
converts the string into an integer, but if you store a string 'xyzzy'
in an INT column it will actually store the string value."

This is an important feature of SQLite.  In hindsight, an excellent decision.


It is sloppy and absent additional constraints and checks added to the 
schema, it puts a burden on the consumer of the data to assure that it 
is the correct type.


It is not so clear that it was an excellent decision.

If the application requires an integer value, why do you think that it 
is appropriate to pass it the string 'xyzzy'?


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

Re: [sqlite] unique values from a subset of data based on two fields

[Michele Pradella]

Select DISTINCT name,id,status from names where status = 1



*Michele Pradella*
/R Software Engineer
/ michele.prade...@selea.com 
Office: +39 0375 889091




Selea s.r.l

V. Aldo Moro, 69
46019 Cicognara MN - Italy
Phone: +390375889091
Fax: +390375889080
http://www.selea.com



Note: The information contained in this message may be privileged and 
confidential and protected from disclosure. If the reader of this 
message is not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, you 
are hereby notified that any dissemination, distribution or copying of 
this communication is strictly prohibited. If you have received this 
communication in error, please notify us immediately by replying to the 
message and deleting it from your computer. Thank you.


Il 29/06/2018 17.50, Paul Sanderson ha scritto:

I have a table

Create table names (id int, status int, name text)



1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'



I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete



I could do something like



Select * from names where status = 1 or name not in (select name from names
where status = 1)



But this gets both rows for steve, e.g.



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
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] unique values from a subset of data based on two fields

[Paul Sanderson]

I have a table

Create table names (id int, status int, name text)



1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'



I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete



I could do something like



Select * from names where status = 1 or name not in (select name from names
where status = 1)



But this gets both rows for steve, e.g.



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Precompiled Binaries for Windows

[Simon Slavin]

On 29 Jun 2018, at 4:33pm, Ruslan Yakauleu  wrote:

> I solve my problem. I use MSVS 2017

Ruslan, very good.  Thank you for posting your solution.  It may help someone 
else.  Happy programming.

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

Re: [sqlite] Precompiled Binaries for Windows

[Ruslan Yakauleu]

Hi Simon.

I solve my problem. I use MSVS 2017

My steps:
Download
  sqlite-amalgamation-324.zip
  icu4c-57_1-Win32-msvc10.zip

Extract archives into directories named by archive names.
Open "Developer Command Prompt for VS 2017", go to
sqlite-amalgamation-324  and execute next commands

  cl sqlite3.c -DSQLITE_API=__declspec(dllexport) -DSQLITE_ENABLE_FTS5
-DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE -DSQLITE_USE_ZLIB
-DSQLITE_ENABLE_ICU -DSQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION -O2 /I
../icu4c-57_1-Win32-msvc10/icu/include -link
../icu4c-57_1-Win32-msvc10/icu/lib/*.lib -dll -out:sqlite3.dll

  dumpbin /exports sqlite3.dll > sqlite3.dump

Then prepare def file from sqlite3.dump
Also next files must be saved near sqlite3.dll
  icudt57.dll
  icuin57.dll
  icuuc57.dll

Now all works

With best regards,
Ruslan Yakauleu


С уважением, Яковлев Руслан

2018-06-29 17:57 GMT+03:00 Simon Slavin :

>
>
> On 28 Jun 2018, at 10:00am, Ruslan Yakauleu  wrote:
>
> > I need this build but with a small change: ICU builtin support required.
> > But in some cases (searched in web) I can't build actual sources. In
> other
> > cases DLL or DEF is wrong and can't be used properly in MSVS projects.
>
> You told us MSVS.  Please tell us which version you're using.  Also, when
> you tell us you got error messages can you tell us the exact error messages
> you're getting ?  Copy & Paste if you want to.
>
> 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] Problem with sqlite3 .import command

[Simon Slavin]

On 28 Jun 2018, at 9:21am, Константин Краснов  wrote:

> However, a table with name " my-test-table" was created in the database and
> is empty
> 
> sqlite> .schema
> CREATE TABLE [my-test-table](
>  "col1" TEXT,
>  "col2" TEXT
> );

Dear Konstantin,

The above is not showing a table with the name "my-test-table".  The brackets 
you see are part of the table name.  So the table is called "[my-test-table]".  
This is the cause of your problem:

SQLite version 3.22.0 2017-12-05 15:00:17
[...]
sqlite> .import 'my-test-table.csv' [my-test-table]
Error: no such table: [my-test-table]
sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);
sqlite> .import 'my-test-table.csv' testTable
sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);
CREATE TABLE testTable(
  "col1" TEXT,
  "col2" TEXT
);
sqlite> .import 'my-test-table.csv' test-table
Error: near "-": syntax error
sqlite> .import 'my-test-table.csv' [testTable]
Error: no such table: [testTable]

The problem seems to be that either the shell tool or SQLite itself is not 
consistent in how it understands square brackets around table names.

Using square brackets around identifiers is not standard SQL.  It's used by SQL 
Server and SQLite allows it in some places for compatibility with SQL Server.  
And of course, as the above shows, without the square brackets around the 
identifier you can't use the '-' character as part of an identifier name.

Whether these is considered bugs or not, and whether they will be fixed or not, 
are things I don't know.  Perhaps one of the developer team will comment.

If you are writing code from scratch I recommend that you use identifiers like 
"MyTestTable" instead of "[my-test-table]".  On the other hand if you are 
trying to transfer hundreds of lines of existing SQL Server code then you might 
want to wait for a response from the SQLite developer team.

Hope this helps.

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

Re: [sqlite] Precompiled Binaries for Windows

[Simon Slavin]

On 28 Jun 2018, at 10:00am, Ruslan Yakauleu  wrote:

> I need this build but with a small change: ICU builtin support required.
> But in some cases (searched in web) I can't build actual sources. In other
> cases DLL or DEF is wrong and can't be used properly in MSVS projects.

You told us MSVS.  Please tell us which version you're using.  Also, when you 
tell us you got error messages can you tell us the exact error messages you're 
getting ?  Copy & Paste if you want to.

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

[sqlite] Precompiled Binaries for Windows

[Ruslan Yakauleu]

Hi!

Please can anybody describe how can I reproduce this build from sources?

https://www.sqlite.org/download.html
- Precompiled Binaries for Windows
-- sqlite-dll-win32-x86-324.zip (444.18 KiB) 32-bit DLL (x86)
for SQLite version 3.24.0

I need this build but with a small change: ICU builtin support required.
But in some cases (searched in web) I can't build actual sources. In other
cases DLL or DEF is wrong and can't be used properly in MSVS projects.

With best regards,
Ruslan Yakauleu
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with sqlite3 .import command

[Константин Краснов]

Then i try to import following .csv file

col1;col2
1;1
2;2
3;3

with .import command into a table with name what contents "-" sign

sqlite> .open test.db
sqlite> drop table if exists [my-test-table];
sqlite> .mode csv
sqlite> .separator ;
sqlite> .import 'my-test-table.csv' [my-test-table]

And here sqlite3 returns the following error: "Error: no such table:
[my-test-table]"

However, a table with name " my-test-table" was created in the database and
is empty

sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);

and the command
sqlite> SELECT * FROM [my-test-table];
returns none.

I try the latest precompiled sqlite3 binary from sqlite.org and binary
compiled from sqlite-amalgamation-324.zip source.

So, when I replace
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
with
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable);
in function do_meta_command on line 13700 of shell.c file
from sqlite-amalgamation-324.zip and recompile sqlite3 all works fine.

The CREATE TABLE statement on line 13658 in this file use %s modifier for
table name
char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);

I think, that the same %s modifier should be used in INSERT INTO statement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users