[sqlite] Warnings for non-deterministic queries?

[Török Edwin]

On 11/27/2015 08:30 PM, R Smith wrote:
> 
> 
> On 2015/11/27 4:44 PM, Adam Devita wrote:
>> I think the OP meant to write:
>> "If the expression is an aggregate expression, it is evaluated across
>> all rows in the group. Otherwise, it is evaluated against a single
>> arbitrarily chosen row from within the group. "
>>
>> Is there a way I could programmatically determine that a query is
>> going to use an arbitrarily chosen row from within the group at query
>> prepare time?

Thanks, non-deterministic was probably the wrong term to use.
I wanted to find situations where a query's result depends on an implementation 
detail of SQLite, and the behaviour is not fully specified by the query itself,
i.e. it could change from one version to the next, or even with same version by 
slight changes to the DB internal structures.
So far I know of two possible situations like this: the 'arbitrary row choice' 
in the question above, and order of results in an unordered select.

For the unordered select there is 'PRAGMA reverse_unordered_selects' that I 
could use to find bugs, and I wanted to know if there is an equivalent 
functionality for the arbitrary row choice.
However as shown below this is probably the wrong question to ask, I should 
avoid using columns that are not part of an aggregate function/group by in the 
first place (if using group by).

> 
> I think you are correct about what the OP meant. Which makes it easier to 
> answer:
> 
> There is no need to programmatically check whether the row selected will be 
> arbitrary or not. If the reference is inside an aggregate function, then it 
> is evaluated across all rows (i.e the result is some unknown), and if it 
> isn't, then an arbitrary row is picked and the result is equally unknown 
> before the query completes.
> 
> Programmatically you can just always assume the result will be "some" value 
> in the database.
> 
> Let me explain better, let's assume the query contains MAX(x)... The result 
> will be from whichever row contains the max, and if the x was not in the 
> aggregate function, it will be from some row (which may or may not be the one 
> with the max value).
> In both cases, you have NO chance of knowing which row that would be until 
> the query finishes, so ALWAYS assume an arbitrary row (or 
> Always-non-deterministic, to use the OP's words).
> 
> Other SQL engines enforces a more strict (and I have to say: more correct) 
> semantic where every term NOT in the GROUP BY clause MUST be contained in an 
> aggregate function.

Thanks, this is actually what I was looking for. Can I enforce/detect when a 
query adheres to this more strict semantics in SQLite?

> Either way, the returned result will never be pre-determinable by an onlooker 
> function (which isn't inspecting the data via other means) and as such there 
> is no difference between being contained in an aggregate function or not - 
> the resulting value will never be pre-determinable - and in the case of 
> SQLite (where not contained in an aggregate function), the result may even 
> differ from a previous run (according to documentation, though my experience 
> is that it always returns the exact same result, so it maintains determinism, 
> but you shouldn't bank on it.)
> 
> Bottom line: Just put the darn query fields in aggregate functions.

Good advice, using SUM() for all columns in this case would've avoided my 
original problem (even where only one row is expected because a primary key for 
same table was in group by).

> If you are making an SQLite wrapper, I would even advise enforcing this 
> behaviour even though SQLite can technically make do without it.

I'm not writing a wrapper, just adding some checks to my application from 
time-to-time that print more details when it is possible to determine what went 
wrong via an SQLite API.
For example if sqlite3_close fails with SQLITE_BUSY I loop through 
sqlite3_next_stmt and print the queries that were not finalized properly.

-- 
Edwin T?r?k | Co-founder and Lead Developer

Skylable open-source object storage: reliable, fast, secure
http://www.skylable.com

[sqlite] Warnings for non-deterministic queries?

[Simon Slavin]

On 27 Nov 2015, at 9:08pm, T?r?k Edwin  wrote:

> Thanks, non-deterministic was probably the wrong term to use.
> I wanted to find situations where a query's result depends on an 
> implementation detail of SQLite, and the behaviour is not fully specified by 
> the query itself,
> i.e. it could change from one version to the next, or even with same version 
> by slight changes to the DB internal structures.
> So far I know of two possible situations like this: the 'arbitrary row 
> choice' in the question above, and order of results in an unordered select.

Okay.  That's a sensible thing to want.  The bad news is that there are many of 
them.

For instance, in your post you mention "and order of results in an unordered 
select".  However, even in an ordered SELECT the order of results can change 
because two rows may have the same value.  The order that SQLite returns those 
rows can depend on the order in which the rows were INSERTed or UPDATEd, and on 
which indexes are available.

Another category of unpredictable things involve JOINs where the programmer of 
the JOIN assumed that only one row will satisfy the 'ON' clause.  Again 
depending on indexes and data changes a row in one table might be matched with 
one row for one command but another for another command.

Other unpredictable things are things involving random numbers (i.e. the random 
core function or external functions) and anything involving multiple threads or 
processes.

And just as you write, all the above behaviours can change in different 
versions of SQLite so even if you do detailed detective work using the current 
version it might all be obsolete next week.

Simon.

[sqlite] Warnings for non-deterministic queries?

[Bernardo Sulzbach]

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

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

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


-- 
Bernardo Sulzbach

[sqlite] Warnings for non-deterministic queries?

[R Smith]

On 2015/11/27 4:44 PM, Adam Devita wrote:
> I think the OP meant to write:
> "If the expression is an aggregate expression, it is evaluated across
> all rows in the group. Otherwise, it is evaluated against a single
> arbitrarily chosen row from within the group. "
>
> Is there a way I could programmatically determine that a query is
> going to use an arbitrarily chosen row from within the group at query
> prepare time?

I think you are correct about what the OP meant. Which makes it easier 
to answer:

There is no need to programmatically check whether the row selected will 
be arbitrary or not. If the reference is inside an aggregate function, 
then it is evaluated across all rows (i.e the result is some unknown), 
and if it isn't, then an arbitrary row is picked and the result is 
equally unknown before the query completes.

Programmatically you can just always assume the result will be "some" 
value in the database.

Let me explain better, let's assume the query contains MAX(x)... The 
result will be from whichever row contains the max, and if the x was not 
in the aggregate function, it will be from some row (which may or may 
not be the one with the max value).
In both cases, you have NO chance of knowing which row that would be 
until the query finishes, so ALWAYS assume an arbitrary row (or 
Always-non-deterministic, to use the OP's words).

Other SQL engines enforces a more strict (and I have to say: more 
correct) semantic where every term NOT in the GROUP BY clause MUST be 
contained in an aggregate function. Either way, the returned result will 
never be pre-determinable by an onlooker function (which isn't 
inspecting the data via other means) and as such there is no difference 
between being contained in an aggregate function or not - the resulting 
value will never be pre-determinable - and in the case of SQLite (where 
not contained in an aggregate function), the result may even differ from 
a previous run (according to documentation, though my experience is that 
it always returns the exact same result, so it maintains determinism, 
but you shouldn't bank on it.)

Bottom line: Just put the darn query fields in aggregate functions. If 
you are making an SQLite wrapper, I would even advise enforcing this 
behaviour even though SQLite can technically make do without it.

[sqlite] sqlite3_errmsg and wide char

[Dan Kennedy]

On 11/27/2015 12:13 PM, Igor Korot wrote:
> Hi,
> Is there any way to have "sqlite3_errmsg" function return a wide char string?
> Or do a conversion in a portable way?

Will sqlite3_errmsg16() work for you?

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

Dan.

[sqlite] sqlite3_errmsg and wide char

[Simon Slavin]

On 27 Nov 2015, at 7:03pm, Igor Korot  wrote:

> But what about Linux/Mac?
> Can I use that function there?

Code pages are a Microsoft thing.  Macs just have ASCII and Unicode, with 
easy-to-use system functions to convert between them.  There's no need to worry 
about locales and code pages and Latin-1 and wstring.

For Linux, I don't know.  But it would be a lousy Linux implementation which 
required anything fancy.

Simon.

[sqlite] Warnings for non-deterministic queries?

[Simon Slavin]

On 27 Nov 2015, at 6:30pm, R Smith  wrote:

> Let me explain better, let's assume the query contains MAX(x)... The result 
> will be from whichever row contains the max, and if the x was not in the 
> aggregate function, it will be from some row (which may or may not be the one 
> with the max value).
> In both cases, you have NO chance of knowing which row that would be until 
> the query finishes, so ALWAYS assume an arbitrary row (or 
> Always-non-deterministic, to use the OP's words).

You missed the general principle: there may be any number of rows with the max 
value.  For example, 8,1,6,3,8,4,8,7,6,8 .  So even if you're using MAX you 
still can't predict which row is supplying the other values.  So as you (R 
Smith) says, always assume an arbitrary row.

Simon.

[sqlite] optimization for outer join with most simple views

[E.Pasma]

Hello, I like to post this remark again as it seems closely related to  
"Query flattening for left joins involving subqueries on the right- 
hand side".

I have a complete different reason though. For playing with sudoku  
solving, I have a table representing the digits 1..9:

   CREATE TABLE digit (z integer primary key, i integer);

Besides I have an entity part for representing squares on a line and  
this has excatly the same content as digit. For the sake of economy I  
made it a view:

   CREATE VIEW part AS SELECT z AS p, i FROM digit AS part;

This executes just as fast as a table and the addition "AS part"  
conveniently appears in query plan explanations.

It is just the outer join that is treated differently:

   CREATE TABLE t (x);
   EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN digit ON z=x;
0|0|0|SCAN TABLE t
0|1|1|SEARCH TABLE digit USING INTEGER PRIMARY KEY (rowid=?)

   EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN part ON q=x;
1|0|0|SCAN TABLE digit AS part
0|0|0|SCAN TABLE t
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (p=?)

This is indeed well documented in rule 3 of 
.
I also wonder if this rule could be relaxed.
May be a query that does not include any of the special cases  
mentioned in the document,  might be tolerated.
Or may be for a casual reader this things look easier than they are.

Thanks, E. Pasma

[sqlite] SQLite crashes

[Zsbán Ambrus]

On the SQLITE_OMIT_LOOKASIDE macro.

On Fri, Nov 27, 2015 at 2:45 PM, Richard Hipp  wrote:
> The lookaside memory allocator is a fast memory pool used by
> individual database connections for short-lived memory allocations.
> It makes SQLite run faster, but by bypassing the system
> malloc()/free() it can mask some types of heap corruption.  Best to
> turn it off when dealing with suspected heap problems.

This is useful to know.  Can you put it to the documentation under
http://sqlite.org/compile.html#omit_lookaside please?

-- Ambrus

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Kirill Müller]

On 27.11.2015 10:38, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> I see no reason why the following two queries can't be executed with the 
>> same plans:
>>
>> ... t1 LEFT JOIN t2 ...
>> ... t1 LEFT JOIN (SELECT * FROM t2) ...
> In this case, the queries are identical.  But SQLite's query optimizer
> does not try to optimize this because such trivial subqueries are
> (thought to be) unlikely to occur in practice.
Thanks. It seems to work better for inner joins, though. The practical 
application is a query generator that relies on the SQL engine to be 
able to optimize this. Is there any chance that SQLite will be able to 
treat such queries more efficiently?


-Kirill

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Kirill Müller]

Exactly. And I'd pretty much like SQLite to figure that out for me ;-)


-Kirill


On 27.11.2015 03:19, Keith Medcalf wrote:
> Would it not be more efficient to say:
>
> select 1 from t1 limit 1;
>
> ?
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller
>> Sent: Thursday, 26 November, 2015 15:03
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Query flattening for left joins involving subqueries
>> on the right-hand side
>>
>> On 26.11.2015 21:12, Clemens Ladisch wrote:
>>> Kirill M?ller wrote:
 On 25.11.2015 16:32, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> For a left join with a subquery on the right-hand side, that subquery
>> doesn't seem to be flattened.
> This is rule 3 of .
 I wonder if this rule might be relaxed a bit.
>>> Only if you relax your requirement that the results must be correct.
>>>
>>>
>>> In the general case, a left outer join can be rewritten like this:
>>>
>>> SELECT ... FROM A JOIN B ON ...
>>> UNION ALL
>>> SELECT ... FROM A WHERE NOT EXISTS (look up in B)
>>>
>>> This query would be more likely to be flattenable, but also be slower.
>>>
>> Thanks. Let's not focus on terminology -- I thought "flattening" was the
>> right word to use, but it probably isn't. Of course I'm looking for
>> correct results.
>>
>> Originally, I attached a script but it seems that it's been stripped.
>> I've pasted it below. I see no reason why the following two queries (1
>> and 3 in the script) can't be executed with the same plans:
>>
>> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)
>> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2
>> USING (a) LIMIT 1)
>>
>> This is for two tables t1 and t2 with a single column "a". The script
>> creates them and populates them with 20 rows each.
>>
>>
>> -Kirill
>>
>>
>> #!/bin/bash
>>
>> db=test.sqlite
>>
>> #if false; then
>> rm -f $db
>>
>> n=20
>>
>> sqlite3 $db "CREATE TABLE t1 (a int primary key)"
>> seq 1 $n | sqlite3 $db ".import /dev/stdin t1"
>>
>> sqlite3 $db "CREATE TABLE t2 (a int primary key)"
>> seq 1 $n | sqlite3 $db ".import /dev/stdin t2"
>> #fi
>>
>> q() {
>>   sqlite3 $db "EXPLAIN QUERY PLAN $1"
>>   time sqlite3 $db "$1"
>> }
>>
>> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN
>> t2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2)
>> zzz2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2)
>> zzz2 USING (a) LIMIT 1)"
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg and wide char

[Dominique Devienne]

On Fri, Nov 27, 2015 at 2:45 PM, OBones  wrote:

> Dominique Devienne wrote:
>
>> That's UTF-16, while a wstring is expected to be in the current locale
>> (which won't be UTF-16 on Windows).
>>
> Excuse me, but the std::wstring type is based on wchar and has no codepage
> assigned to it.
> And under windows, that's the standard type to use to talk to the W suffix
> APIs, thus giving them the UTF-16 that they are expecting.
>

Apologies to Dan, and thank you OBones for setting the record straight. I
was wrong, -1 to me. --DD

[sqlite] sqlite3_errmsg and wide char

[OBones]

Dominique Devienne wrote:
> On Fri, Nov 27, 2015 at 2:16 PM, Dan Kennedy  wrote:
>
>> On 11/27/2015 12:13 PM, Igor Korot wrote:
>>
>>> Hi,
>>> Is there any way to have "sqlite3_errmsg" function return a wide char
>>> string?
>>> Or do a conversion in a portable way?
>>>
>> Will sqlite3_errmsg16() work for you?
>>
>>https://www.sqlite.org/c3ref/errcode.html
>
> That's UTF-16, while a wstring is expected to be in the current locale
> (which won't be UTF-16 on Windows).
Excuse me, but the std::wstring type is based on wchar and has no 
codepage assigned to it.
And under windows, that's the standard type to use to talk to the W 
suffix APIs, thus giving them the UTF-16 that they are expecting.

I'm using the sqlite3_errmsg16 function here under windows, and gives me 
exactly what I need to be sent to, say, the MessageBoxW Win32 API.

[sqlite] Warnings for non-deterministic queries?

[Darren Duncan]

On 2015-11-27 5:46 AM, Keith Medcalf wrote:
>> Is there a way I could programatically determine that a query is non-
>> deterministic at query prepare time?
>
> What do you mean, non-deterministic?  The result is deterministic in all 
> cases.
>
> It may be complicated and/or difficult for you to compute, but it is always 
> deterministic.  The result is generated by running an unchanging algorithm on 
> unchanging data.  If there is no random inputs and the computer hardware is 
> not broken, then the results are entirely determined by the algorithm 
> executed and the state of the data upon which it is operating.

While what you say is true, deterministic if same algorithm and same data, I 
think there's a higher standard for determinism.  The concept of "arbitrary 
row" 
presumably is based on certain implementation details like the structure of an 
index or other hidden metadata, which can change even if there are no 
user-visible changes to the database.  Unless the algorithm guarantees that the 
exact same row will be selected whenever the user-visible parts of the database 
have the exact same value, it is not actually deterministic from the user's 
point of view, which I think is what really matters here.  Selecting an 
"arbitrary row" can only be called deterministic otherwise if the user is able 
to query all of the conditions that would make it chosen, such as index 
details, 
as are applicable.  Any visible change is significant; if the user can get a 
different answer to any question about the database, including 'hidden' parts, 
then the database is different, whereas if all questions they can ask return 
the 
same answer, then the "arbitrary row" should be the same row. -- Darren Duncan

[sqlite] sqlite3_errmsg and wide char

[Dominique Devienne]

On Fri, Nov 27, 2015 at 2:16 PM, Dan Kennedy  wrote:

> On 11/27/2015 12:13 PM, Igor Korot wrote:
>
>> Hi,
>> Is there any way to have "sqlite3_errmsg" function return a wide char
>> string?
>> Or do a conversion in a portable way?
>>
>
> Will sqlite3_errmsg16() work for you?
>
>   https://www.sqlite.org/c3ref/errcode.html


That's UTF-16, while a wstring is expected to be in the current locale
(which won't be UTF-16 on Windows).
Furthermore, wstring can be 2-byte or 4-byte per char / rune / codepoint,
depending on OS, whilieSQLite's "*16" APIs are uint16_t based I suppose
(can't say for sure, since void*) --DD

[sqlite] sqlite3_errmsg and wide char

[Igor Korot]

Hi, guys.
But what about Linux/Mac?
Can I use that function there?

Thank you.
On Nov 27, 2015 9:54 AM, "Dominique Devienne"  wrote:

> On Fri, Nov 27, 2015 at 2:45 PM, OBones  wrote:
>
> > Dominique Devienne wrote:
> >
> >> That's UTF-16, while a wstring is expected to be in the current locale
> >> (which won't be UTF-16 on Windows).
> >>
> > Excuse me, but the std::wstring type is based on wchar and has no
> codepage
> > assigned to it.
> > And under windows, that's the standard type to use to talk to the W
> suffix
> > APIs, thus giving them the UTF-16 that they are expecting.
> >
>
> Apologies to Dan, and thank you OBones for setting the record straight. I
> was wrong, -1 to me. --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite crashes

[Harmen de Jong - CoachR Group B.V.]

> (1) Recompile with the following compile-time options:  -DSQLITE_DEBUG 
> -DSQLITE_OMIT_LOOKASIDE
> 
> (2) Enable whatever AddressSanitizer tools
> (https://en.wikipedia.org/wiki/AddressSanitizer) you have available on your 
> platform, in addition to (1) above.
>
> (3) If you do not have an easily accessible address sanitizer utility, 
> consider recompiling SQLite using -DSQLITE_MEMDEBUG (in > > addition to the 
> compile-time options shown in (1) above).
>
> --
> D. Richard Hipp

@D. Richard Hipp:
We have recompiled SQLITE with the following options and have it up and running 
now:
Recompiled with: -DSQLITE_DEBUG,  -DSQLITE_OMIT_LOOKASIDE and -DSQLITE_MEMDEBUG.

It is clear to us why we have to compile with -DSQLITE_OMIT_LOOKASIDE, but what 
about the other two options?
1. What is the effect of -DSQLITE_DEBUG?
2. What is the effect of -DSQLITE_MEMDEBUG?
Could we expect any extra output or extra info while analyzing crash dumps by 
using these compile-time options?


> Can you run
>
> PRAGMA integrity_check
>
> on the database ?  If you don't want to tie up your live copy, copy it 
> somewhere else and run the PRAGMA on the copy.
>
> Simon.

@Simon: The result of this pragma was "Ok".

[sqlite] sqlite3_errmsg and wide char

[Scott Robison]

On Fri, Nov 27, 2015 at 12:03 PM, Igor Korot  wrote:

> Hi, guys.
> But what about Linux/Mac?
> Can I use that function there?
>

It will always give you a UTF-16 encoded string as a void*. To get it into
a wstring would involve extracting the 16 bit code points and possibly
converting them to 32 bit code points depending on the platform.

Perhaps at this point the question should be asked "why do you need a
wstring"? The answer to that question might help figure out how your needs
might be best met. Portability covers an entire spectrum of needs. At one
end of the spectrum you know you have ASCII or Latin-1 bytes and conversion
to wchar_t is trivial. At the other you have zero knowledge of the
character encodings in use or the platforms to be targeted so the answer is
"no, it is not possible to do it portably".

SDR


>
> Thank you.
> On Nov 27, 2015 9:54 AM, "Dominique Devienne"  wrote:
>
> > On Fri, Nov 27, 2015 at 2:45 PM, OBones  wrote:
> >
> > > Dominique Devienne wrote:
> > >
> > >> That's UTF-16, while a wstring is expected to be in the current locale
> > >> (which won't be UTF-16 on Windows).
> > >>
> > > Excuse me, but the std::wstring type is based on wchar and has no
> > codepage
> > > assigned to it.
> > > And under windows, that's the standard type to use to talk to the W
> > suffix
> > > APIs, thus giving them the UTF-16 that they are expecting.
> > >
> >
> > Apologies to Dan, and thank you OBones for setting the record straight. I
> > was wrong, -1 to me. --DD
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison

[sqlite] Warnings for non-deterministic queries?

[Török Edwin]

Hi,

I mistakenly used the wrong side of the ON caluse in a group by clause for a 
query and I was wondering why SQLite didn't return the same results always:
- SELECT SUM(x) FROM (SELECT files.size + LENGTH(CAST(files.name AS BLOB)) 
+ SUM(COALESCE(LENGTH(CAST(fmeta.key AS BLOB)) + LENGTH(fmeta.value),0)) AS x 
FROM files LEFT JOIN fmeta ON files.fid = fmeta.file_id WHERE files.volume_id = 
:volid AND age >= 0 GROUP BY fmeta.file_id
+   SELECT SUM(x) FROM (SELECT files.size + LENGTH(CAST(files.name AS 
BLOB)) + SUM(COALESCE(LENGTH(CAST(fmeta.key AS BLOB)) + LENGTH(fmeta.value),0)) 
AS x FROM files LEFT JOIN fmeta ON files.fid = fmeta.file_id WHERE 
files.volume_id = :volid AND age >= 0 GROUP BY files.fid

Then I realized that fmeta.file_id can be NULL, and then it no longer groups by 
file id and just selects a random row as documented:
"If the expression is an aggregate expression, it is evaluated across all rows 
in the group. Otherwise, it is evaluated against a single arbitrarily chosen 
row from within the group. "

Is there a way I could programatically determine that a query is 
non-deterministic at query prepare time?

-- 
Edwin T?r?k | Co-founder and Lead Developer

Skylable open-source object storage: reliable, fast, secure
http://www.skylable.com

[sqlite] method for thousands separator via sed post processing

[Rowan Worth]

Hi Bruce,

On 27 November 2015 at 10:59, Bruce Hohl  wrote:

> Thanks to suggestions on this list I explored the sed post processing
> avenue and found a solution.  There are quite a few sed docs at
> http://sed.sourceforge.net  For my needs I adapted an example from
> http://www-rohan.sdsu.edu/doc/sed.html (These docs are like ancient
> scrolls.)
>
> # add commas to interger strings, changing "1234567" to "1,234,567"
> gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta'
>
> # add commas to numbers with decimal points and minus signs
> gsed ':a;s/\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\1\2,\3/g;ta'
>
> I adjusted the 2nd as follows to remove a space for each comma added which
> preserves column layout:
> sed ':a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta'
>

This modification is the reason it doesn't match numbers in column 1. But
also it will only insert as many commas as it has spaces to eat. eg. if the
number 1234567.89012 appears four times with zero, one, two, and then three
leading spaces, we get:

1234567.89012
 1234567.89012
 1234,567.89012
 1,234,567.89012

You can improve it by moving the leading space inside the second group and
making it optional, so that it eats spaces when it can but still inserts
commas when it can't:

sed ':a;s/\(^\| \?\([^0-9.]\)\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta';

The output becomes:

1,234,567.89012
 1,234,567.89012
 1,234,567.89012
 1,234,567.89012


The date problem is harder to solve, especially with sed. lex makes it
feasible - stick the following into a file called thousandify.lex and
compile it with the command:

flex thousandify.lex && gcc lex.yy.c -o thousandify

Then put the resulting 'thousandify' binary in your path and you can run
sqlite3 test.db | thousandify


/* START thousandify.lex */
%option noyywrap

DATE " "*[0-9]{4}-[0-9]{2}-[0-9]{2}
INTEGER " "*[0-9]+
FRACTION \.[0-9]*([eE][+-]?[0-9]*)?

%%
{DATE}printf("%s", yytext);
{FRACTION}printf("%s", yytext);

{INTEGER}{
char *cp;
int i, r, n, len;
for (cp=yytext; *cp == ' '; ++cp); // skip leading whitespace
len = strlen(cp);
r = len % 3;
n = (len - 1) / 3; // number of commas we'll insert
for (i = 0; i < (cp - yytext) - n; i++) {
putchar(' ');
}
putchar(cp[0]);
for (i = 1; i < len; i++) {
if (--r == 0) {
putchar(',');
r = 3;
}
putchar(cp[i]);
}
}

.printf("%s", yytext);

%%
int
main(int argc, char **argv) {
yylex();
return 0;
}
/* END thousandify.lex */


-Rowan

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Clemens Ladisch]

Kirill M?ller wrote:
> I see no reason why the following two queries can't be executed with the same 
> plans:
>
> ... t1 LEFT JOIN t2 ...
> ... t1 LEFT JOIN (SELECT * FROM t2) ...

In this case, the queries are identical.  But SQLite's query optimizer
does not try to optimize this because such trivial subqueries are
(thought to be) unlikely to occur in practice.


Regards,
Clemens

[sqlite] sqlite3_errmsg and wide char

[Dominique Devienne]

On Fri, Nov 27, 2015 at 6:13 AM, Igor Korot  wrote:

> Is there any way to have "sqlite3_errmsg" function return a wide char
> string?
> Or do a conversion in a portable way?
>

Portably, not using standard C++ AFAIK. But there are several OSS
frameworks that hide platform differences.

Since you're in C++, and particularly if you're already a Boost user, you
may be interested in http://cppcms.com/files/nowide/html/

OTOH you want a wstring, and NoWide specifically stays on the UTF-8 side,
but perhaps what you want to do with that wstring is covered by nowide. --DD

[sqlite] Warnings for non-deterministic queries?

[Adam Devita]

I think the OP meant to write:
"If the expression is an aggregate expression, it is evaluated across
all rows in the group. Otherwise, it is evaluated against a single
arbitrarily chosen row from within the group. "

Is there a way I could programmatically determine that a query is
going to use an arbitrarily chosen row from within the group at query
prepare time?


Adam

On Fri, Nov 27, 2015 at 8:46 AM, Keith Medcalf  wrote:
>
>
>> Is there a way I could programatically determine that a query is non-
>> deterministic at query prepare time?
>
> What do you mean, non-deterministic?  The result is deterministic in all 
> cases.
>
> It may be complicated and/or difficult for you to compute, but it is always 
> deterministic.  The result is generated by running an unchanging algorithm on 
> unchanging data.  If there is no random inputs and the computer hardware is 
> not broken, then the results are entirely determined by the algorithm 
> executed and the state of the data upon which it is operating.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] SQLite crashes

[Richard Hipp]

On 11/27/15, Harmen de Jong - CoachR Group B.V.  wrote:
>> (1) Recompile with the following compile-time options:  -DSQLITE_DEBUG
>> -DSQLITE_OMIT_LOOKASIDE
>>
>> (2) Enable whatever AddressSanitizer tools
>> (https://en.wikipedia.org/wiki/AddressSanitizer) you have available on
>> your platform, in addition to (1) above.
>>
>> (3) If you do not have an easily accessible address sanitizer utility,
>> consider recompiling SQLite using -DSQLITE_MEMDEBUG (in > > addition to
>> the compile-time options shown in (1) above).
>>
>> --
>> D. Richard Hipp
>
> @D. Richard Hipp:
> We have recompiled SQLITE with the following options and have it up and
> running now:
> Recompiled with: -DSQLITE_DEBUG,  -DSQLITE_OMIT_LOOKASIDE and
> -DSQLITE_MEMDEBUG.
>
> It is clear to us why we have to compile with -DSQLITE_OMIT_LOOKASIDE, but
> what about the other two options?

The lookaside memory allocator is a fast memory pool used by
individual database connections for short-lived memory allocations.
It makes SQLite run faster, but by bypassing the system
malloc()/free() it can mask some types of heap corruption.  Best to
turn it off when dealing with suspected heap problems.

> 1. What is the effect of -DSQLITE_DEBUG?

This option enables assert() statements.  If there are any internal
inconsistencies within SQLite, they are likely to be caught by the
numerous assert() statements in the code.

> 2. What is the effect of -DSQLITE_MEMDEBUG?

This adds a wrapper around system malloc()/free() that works to test
the integrity of the heap.  It is not a great wrapper.  (Things like
AddressSanitizer are better.)  But it is better than nothing.  Errors
are printed if heap problems are detected.

> Could we expect any extra output or extra info while analyzing crash dumps
> by using these compile-time options?
>
>
>> Can you run
>>
>> PRAGMA integrity_check
>>
>> on the database ?  If you don't want to tie up your live copy, copy it
>> somewhere else and run the PRAGMA on the copy.
>>
>> Simon.
>
> @Simon: The result of this pragma was "Ok".
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite3 API

[Cousin Stanley]

> I have written (in COBOL) an SQLite3 interface for GnuCOBOL
>  

  Robert  

Any plans for distribution 
of your cobol/sqlite  interface ?


-- 
Stanley C. Kitching
Human Being
Phoenix, Arizona

[sqlite] SQLite3 API

[Richard Hipp]

On 11/26/15, Robert W.Mills (Phoenix)  wrote:
>
> But how do I get the number of rows UPDATEd or DELETEd?
>

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

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] drop, create and copy a table

[Héctor Fiandor]

Dear Mr. Smith and Dr. Hipp>

Finally, I have an application that works, using your suggestions.

Something is not working as I want, but finally, it works.

Thanks very mucho for your help.

Regards,
Ing. H?ctor Fiandor
hfiandor at ceniai.inf.cu



-Mensaje original-
De: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de H?ctor
Fiandor
Enviado el: jueves, 26 de noviembre de 2015 07:22 a. m.
Para: 'SQLite mailing list'
Asunto: Re: [sqlite] drop, create and copy a table

Dear Mr. Smith and Dr. Hipp:

Thanks very much for your answers. I have learned a lot from your messages.

Reviewing old message I found one from Mr. Smith that confirm this routines,
that I have found previously but I miss something, and need to do all this
in three "phases", I need to close the application after 1st, start again in
the 2nd, close again, a so for.

As you say, it can be done easily in one "phase". I have to trial and learn.

Thanks very much again.

I will tell you about my progress.

Ing. H?ctor Fiandor
hfiandor at ceniai.inf.cu


-Mensaje original-
De: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de R Smith
Enviado el: jueves, 26 de noviembre de 2015 02:46 a. m.
Para: sqlite-users at mailinglists.sqlite.org
Asunto: Re: [sqlite] drop, create and copy a table



On 2015/11/26 4:08 AM, H?ctor Fiandor wrote:
> Dear Mr. Hipp and other members:
>
> I have a table where I have introduced a lot of information during 2015.
>
> In December 31, I have to select the records to be continue in 2016.
> Previously, I have obtained a copy of the 2015 table for any consult.
>
> In 2016, the year start with a table "cleaned" of the old records, with
the
> records to be continued as the firsts, for these reasons, I thought to
drop
> the table and create a new one, "cleaned" of any history related to 2015,
> with the Id starting in "1".
>
> Really, I don?t know that making the step (2) suggested by Mr. Hipp
followed
> by step (3) without dropping the table "clean" the history of the table
and
> start the Id with "1".
>
> Really, I have not used de BEGIN...COMMINT commands.
>
> I will test the routines as suggested by Mr. Hipp.
>
> Any suggestion will be received as a gift.

Hi Hector,

This above statement reads very different to what you have asked before. 
There is obvious a language difficulty between us so I will say the next 
bit as verbose as possible (please forgive me if it sounds overly 
convoluted).

We now understand that you wish to create the new table and copy from 
the old table, but only SOME information, not all of it, because you 
want to remove very old data that is no longer needed and begin a new 
set when starting the year 2016.

The best way to do that is as Dr. Hipp suggested by first renaming the 
current (old) table to something else, such as "temp_mytable",
ALTER TABLE "mytable" RENAME TO "temp_mytable";

then create the new empty table (which will reset all the AUTO-INC 
values etc.). In this step be sure to recreate all Index and Trigger 
objects for the new table,
CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol);

then copy the values you want to keep from the old table with a 
select-insert, like this:
INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... 
LastCol FROM temp_mytable WHERE DateStamp > '2013';  (Or however you 
want to filter out the unneeded records),

then, after that is done, simply remove the old table:
DROP TABLE "temp_mytable";

I understand from your original writing you were looking for a faster 
way to do it, or to do it with less steps. This is something that will 
not be done many times, just once, so there is no need to find a more 
efficient way. You can easily script the process above and just execute 
it using the command line utility or from inside your program.

Hope that sheds a bit more light, Cheers.
Ryan



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

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

[sqlite] Warnings for non-deterministic queries?

[Keith Medcalf]

> Is there a way I could programatically determine that a query is non-
> deterministic at query prepare time?

What do you mean, non-deterministic?  The result is deterministic in all cases. 
 

It may be complicated and/or difficult for you to compute, but it is always 
deterministic.  The result is generated by running an unchanging algorithm on 
unchanging data.  If there is no random inputs and the computer hardware is not 
broken, then the results are entirely determined by the algorithm executed and 
the state of the data upon which it is operating.

[sqlite] .NET Linux problem with Microsoft.Data.Sqlite.dll and sqlite3_prepare_v2

[Frank Chang]

Richard Hipp or Igor Tadetnik,
Here is the C# code for Microsoft.Data.Sqlite.dll, designed for
.NET users on Ubuntu Linux and Windows, I am using the latest version of
libsqlite3.so compiled as follows;

gcc -shared -g -o libsqlite3.so -fPIC sqlite3.c


I was able to get sqlite3_exec running properly calling it from an
C# DLLImport when I run mono Program.exe
However, I am getting the following exception which says the return
value is being returned by slqite3_prepare_v2 is incorrect:

--- End of inner exception stack trace ---
  at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC (Int32 rc,
Microsoft.Data.Sqlite.Interop.Sqlite3Handle db) <0x414bfb70 + 0x0008f> in
:0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior
behavior) <0x414c09e0 + 0x0022f> in :0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () <0x414c09b0 +
0x00015> in :0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery () <0x414c0860 +
0x0005e> in :0
  at Microsoft.Data.Sqlite.Tests.Program.ManagedWrapperMainOne
(System.String Name) <0x414bbea0 + 0x0019b> in :0
  at Microsoft.Data.Sqlite.Tests.Program.Main () <0x414bbd50 + 0x00013> in
:0


   public static int sqlite3_prepare_v2(Sqlite3Handle db, string zSql, out
Sqlite3StmtHandle ppStmt, out string pzTail)
{
int nByte;
var zSqlPtr = MarshalEx.StringToHGlobalUTF8(zSql, out nByte);

try
{
// TODO: Something fancy with indexes?
IntPtr pzTailPtr;
var rc = sqlite3_prepare_v2(db, zSqlPtr, nByte, out ppStmt,
out pzTailPtr);
pzTail = MarshalEx.PtrToStringUTF8(pzTailPtr);

return rc;
}
finally
{
Marshal.FreeHGlobal(zSqlPtr);
}
}




public new virtual SqliteDataReader ExecuteReader(CommandBehavior
behavior)
{
if ((behavior & ~(CommandBehavior.Default |
CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection)) != 0)
{
throw new
ArgumentException(Strings.FormatInvalidCommandBehavior(behavior));
}

if (Connection == null
|| Connection.State != ConnectionState.Open)
{
throw new
InvalidOperationException(Strings.FormatCallRequiresOpenConnection("ExecuteReader"));
}

if (string.IsNullOrEmpty(CommandText))
{
throw new
InvalidOperationException(Strings.FormatCallRequiresSetCommandText("ExecuteReader"));
}

if (Transaction != Connection.Transaction)
{
throw new InvalidOperationException(
Transaction == null
? Strings.TransactionRequired
: Strings.TransactionConnectionMismatch);
}

//TODO not necessary to call every time a command is executed.
Only on first command or when timeout changes
NativeMethods.sqlite3_busy_timeout(Connection.DbHandle,
CommandTimeout * 1000);

var hasChanges = false;
var changes = 0;
var stmts = new Queue>();
var tail = CommandText;

do
{
Sqlite3StmtHandle stmt;
var rc = NativeMethods.sqlite3_prepare_v2(
Connection.DbHandle,
tail,
out stmt,
out tail);
MarshalEx.ThrowExceptionForRC(rc, Connection.DbHandle);

// Statement was empty, white space, or a comment
if (stmt.IsInvalid)
{
if (!string.IsNullOrEmpty(tail))
{
continue;
}

break;
}

var boundParams = 0;

if (_parameters.IsValueCreated)
{
boundParams = _parameters.Value.Bind(stmt);
}

var expectedParams =
NativeMethods.sqlite3_bind_parameter_count(stmt);
if (expectedParams != boundParams)
{
var unboundParams = new List();
for (var i = 1; i <= expectedParams; i++)
{
var name =
NativeMethods.sqlite3_bind_parameter_name(stmt, i);

if (_parameters.IsValueCreated
||

!_parameters.Value.Cast().Any(p => p.ParameterName ==
name))
{
unboundParams.Add(name);
}
}
throw new
InvalidOperationException(Strings.FormatMissingParameters(string.Join(", ",
unboundParams)));
}

try
{
var 

[sqlite] sqlite3_errmsg and wide char

[Igor Korot]

Hi, Scott,

On Fri, Nov 27, 2015 at 1:01 AM, Scott Robison  
wrote:
> On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot  wrote:
>>
>> Hi,
>> Is there any way to have "sqlite3_errmsg" function return a wide char
> string?
>> Or do a conversion in a portable way?
>>
>> Thank you.
>
> The portable way would be to use the mbstowcs function from stdlib.h,
> though it depends on what locales are supported by the system, so maybe not
> as portable as you would like.

I should've been more explicit here, sorry.
I'm developing in C++ and need to convert to std::wstring.

Or maybe I can just generate my own error message based on the sqlite
return code?

Thank you.

>
> There isn't really a truly portable way of converting from char to wchar_t
> based strings, given that there is no real guarantee about what exactly
> wchar_t *is*. The ISO C90 standard merely defined it as "an integral type
> whose range of values can represent distinct codes for all members of the
> largest extended character set specified among the supported locales". In
> theory, wchar_t could be a typedef of char if a platform only supported an
> 8 bit character set. Unicode was being designed at the time of C
> standardization, but the first version of Unicode wasn't published for a
> year or more after the C90 standard. Thus the wchar_t type doesn't have to
> be Unicode,
>
> In practical terms, I generally consider wchar_t to be a pre-2011 method of
> storing Unicode. Even here there are no portability guarantees. Microsoft
> went all in on Unicode in the early 1990s, back when it was only a two byte
> encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte type.
> Unicode 2.0 extended the Unicode character set in 1996 (I think) and
> introduced UTF-16 as a compromise way of allowing systems that embraced
> Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support the
> full space of Unicode code points from U+ to U+10 via surrogate
> pairs. Modern posix systems (as far as I know) define wchar_t to be a 32
> bit type, so you can't really convert to wchar_t in a portable way, because
> you have to handle surrogate pairs on Windows vs simple code points in
> posix (though this should be handled by mbstowcs if the platform supports
> wchar_t as unicode).
>
> If you only use ASCII or Latin-1 8 bit characters in your code, or are
> willing to treat all char objects as ASCII or Latin-1, then you can convert
> char strings to wchar_t strings by simply zero extending each character
> while copying it. Something like this (without any error checking):
>
> void copy_narrow_to_wide(wchar_t* dst, const char* src)
> {
>   while (*src) *(dst++) = (unsigned char)(*(src++));
>   *dst = 0;
> }
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg and wide char

[Scott Robison]

On Fri, Nov 27, 2015 at 1:19 AM, J Decker  wrote:

> On Fri, Nov 27, 2015 at 12:17 AM, Scott Robison 
> wrote:
> > On Thu, Nov 26, 2015 at 11:42 PM, Igor Korot  wrote:
> >
> >> Hi, Scott,
> >>
> >> On Fri, Nov 27, 2015 at 1:01 AM, Scott Robison  >
> >> wrote:
> >> > On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot 
> wrote:
> >> >>
> >> >> Hi,
> >> >> Is there any way to have "sqlite3_errmsg" function return a wide char
> >> > string?
> >> >> Or do a conversion in a portable way?
> >> >>
> >> >> Thank you.
> >> >
> >> > The portable way would be to use the mbstowcs function from stdlib.h,
> >> > though it depends on what locales are supported by the system, so
> maybe
> >> not
> >> > as portable as you would like.
> >>
> >> I should've been more explicit here, sorry.
> >> I'm developing in C++ and need to convert to std::wstring.
> >>
> >> Or maybe I can just generate my own error message based on the sqlite
> >> return code?
> >>
> >
> > Most of the comments I made previously still apply, since wstring is
> just a
> > basic_string using wchar_t as the character type. C++ doesn't mandate the
> > character set that wchar_t encompasses or the encoding it uses for that
> > character set any more than C does. I like the C++11 char16_t & char32_t
> > types (when available) since they are explicitly designed for unicode.
> >
> > Some useful answers can be found at
> >
> http://stackoverflow.com/questions/2573834/c-convert-string-or-char-to-wstring-or-wchar-t
>
> or ...
>
> http://stackoverflow.com/questions/7153935/how-to-convert-utf-8-stdstring-to-utf-16-stdwstring
>
> don't see what converting utf8 to utf16 has to do with locale ...
> or why in the last decade a useful standard function hasn't been
> included in standard libraries...
>

Locales are the way that standards for C & C++ support conversion in a
portable way. If you don't care about universal portability, or only
supporting specific encodings, that makes the job somewhat easier.

C11 & C++11 have actually added a fair amount of support for utf8, utf16 &
even utf32, which is great, but it's still "optional" with regard to
unicode. The standards bodies really seem to hate to tell implementations
"you must support unicode" any more than they insist 8 bit characters are
encoded as ascii or latin-1. Since a given platform might not have a need
for unicode (hard as that might be to imagine) they create the
infrastructure to support it.


>
> > ...
> >
> > A C++-ish way of converting a string to a wstring (assuming the
> characters
> > in string are all ASCII or Latin-1 and wchar_t is unicode):
> >
> > // horribly inefficient but illustrative
> > void copy_narrow_to_wide(std::wstring& dst, const char* src)
> > {
> >   while (*src) dst.push_back((unsigned char)(*(src++)));
> > }
> >
> > Not that I'd necessarily recommend that, but it can work. The link above
> in
> > particular is useful if all you need are to convert narrow strings to
> wide
> > strings for internal usage (logging or such).
> >
> > However, I think you hit the nail on the head with your last thought
> > (especially if you need to display these in potentially localized text):
> > generate your own error messages based on the sqlite return code.
> >
> >
> >>
> >> Thank you.
> >>
> >> >
> >> > There isn't really a truly portable way of converting from char to
> >> wchar_t
> >> > based strings, given that there is no real guarantee about what
> exactly
> >> > wchar_t *is*. The ISO C90 standard merely defined it as "an integral
> type
> >> > whose range of values can represent distinct codes for all members of
> the
> >> > largest extended character set specified among the supported
> locales". In
> >> > theory, wchar_t could be a typedef of char if a platform only
> supported
> >> an
> >> > 8 bit character set. Unicode was being designed at the time of C
> >> > standardization, but the first version of Unicode wasn't published
> for a
> >> > year or more after the C90 standard. Thus the wchar_t type doesn't
> have
> >> to
> >> > be Unicode,
> >> >
> >> > In practical terms, I generally consider wchar_t to be a pre-2011
> method
> >> of
> >> > storing Unicode. Even here there are no portability guarantees.
> Microsoft
> >> > went all in on Unicode in the early 1990s, back when it was only a two
> >> byte
> >> > encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte
> >> type.
> >> > Unicode 2.0 extended the Unicode character set in 1996 (I think) and
> >> > introduced UTF-16 as a compromise way of allowing systems that
> embraced
> >> > Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support
> >> the
> >> > full space of Unicode code points from U+ to U+10 via
> surrogate
> >> > pairs. Modern posix systems (as far as I know) define wchar_t to be a
> 32
> >> > bit type, so you can't really convert to wchar_t in a portable way,
> >> because
> >> > you have to handle surrogate pairs on Windows vs simple code points in
> >> > posix (though this should be handled by 

[sqlite] sqlite3_errmsg and wide char

[Scott Robison]

On Thu, Nov 26, 2015 at 11:42 PM, Igor Korot  wrote:

> Hi, Scott,
>
> On Fri, Nov 27, 2015 at 1:01 AM, Scott Robison 
> wrote:
> > On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot  wrote:
> >>
> >> Hi,
> >> Is there any way to have "sqlite3_errmsg" function return a wide char
> > string?
> >> Or do a conversion in a portable way?
> >>
> >> Thank you.
> >
> > The portable way would be to use the mbstowcs function from stdlib.h,
> > though it depends on what locales are supported by the system, so maybe
> not
> > as portable as you would like.
>
> I should've been more explicit here, sorry.
> I'm developing in C++ and need to convert to std::wstring.
>
> Or maybe I can just generate my own error message based on the sqlite
> return code?
>

Most of the comments I made previously still apply, since wstring is just a
basic_string using wchar_t as the character type. C++ doesn't mandate the
character set that wchar_t encompasses or the encoding it uses for that
character set any more than C does. I like the C++11 char16_t & char32_t
types (when available) since they are explicitly designed for unicode.

Some useful answers can be found at
http://stackoverflow.com/questions/2573834/c-convert-string-or-char-to-wstring-or-wchar-t
...

A C++-ish way of converting a string to a wstring (assuming the characters
in string are all ASCII or Latin-1 and wchar_t is unicode):

// horribly inefficient but illustrative
void copy_narrow_to_wide(std::wstring& dst, const char* src)
{
  while (*src) dst.push_back((unsigned char)(*(src++)));
}

Not that I'd necessarily recommend that, but it can work. The link above in
particular is useful if all you need are to convert narrow strings to wide
strings for internal usage (logging or such).

However, I think you hit the nail on the head with your last thought
(especially if you need to display these in potentially localized text):
generate your own error messages based on the sqlite return code.


>
> Thank you.
>
> >
> > There isn't really a truly portable way of converting from char to
> wchar_t
> > based strings, given that there is no real guarantee about what exactly
> > wchar_t *is*. The ISO C90 standard merely defined it as "an integral type
> > whose range of values can represent distinct codes for all members of the
> > largest extended character set specified among the supported locales". In
> > theory, wchar_t could be a typedef of char if a platform only supported
> an
> > 8 bit character set. Unicode was being designed at the time of C
> > standardization, but the first version of Unicode wasn't published for a
> > year or more after the C90 standard. Thus the wchar_t type doesn't have
> to
> > be Unicode,
> >
> > In practical terms, I generally consider wchar_t to be a pre-2011 method
> of
> > storing Unicode. Even here there are no portability guarantees. Microsoft
> > went all in on Unicode in the early 1990s, back when it was only a two
> byte
> > encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte
> type.
> > Unicode 2.0 extended the Unicode character set in 1996 (I think) and
> > introduced UTF-16 as a compromise way of allowing systems that embraced
> > Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support
> the
> > full space of Unicode code points from U+ to U+10 via surrogate
> > pairs. Modern posix systems (as far as I know) define wchar_t to be a 32
> > bit type, so you can't really convert to wchar_t in a portable way,
> because
> > you have to handle surrogate pairs on Windows vs simple code points in
> > posix (though this should be handled by mbstowcs if the platform supports
> > wchar_t as unicode).
> >
> > If you only use ASCII or Latin-1 8 bit characters in your code, or are
> > willing to treat all char objects as ASCII or Latin-1, then you can
> convert
> > char strings to wchar_t strings by simply zero extending each character
> > while copying it. Something like this (without any error checking):
> >
> > void copy_narrow_to_wide(wchar_t* dst, const char* src)
> > {
> >   while (*src) *(dst++) = (unsigned char)(*(src++));
> >   *dst = 0;
> > }
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison

[sqlite] sqlite3_errmsg and wide char

[J Decker]

On Fri, Nov 27, 2015 at 12:17 AM, Scott Robison  
wrote:
> On Thu, Nov 26, 2015 at 11:42 PM, Igor Korot  wrote:
>
>> Hi, Scott,
>>
>> On Fri, Nov 27, 2015 at 1:01 AM, Scott Robison 
>> wrote:
>> > On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot  wrote:
>> >>
>> >> Hi,
>> >> Is there any way to have "sqlite3_errmsg" function return a wide char
>> > string?
>> >> Or do a conversion in a portable way?
>> >>
>> >> Thank you.
>> >
>> > The portable way would be to use the mbstowcs function from stdlib.h,
>> > though it depends on what locales are supported by the system, so maybe
>> not
>> > as portable as you would like.
>>
>> I should've been more explicit here, sorry.
>> I'm developing in C++ and need to convert to std::wstring.
>>
>> Or maybe I can just generate my own error message based on the sqlite
>> return code?
>>
>
> Most of the comments I made previously still apply, since wstring is just a
> basic_string using wchar_t as the character type. C++ doesn't mandate the
> character set that wchar_t encompasses or the encoding it uses for that
> character set any more than C does. I like the C++11 char16_t & char32_t
> types (when available) since they are explicitly designed for unicode.
>
> Some useful answers can be found at
> http://stackoverflow.com/questions/2573834/c-convert-string-or-char-to-wstring-or-wchar-t

or ...
http://stackoverflow.com/questions/7153935/how-to-convert-utf-8-stdstring-to-utf-16-stdwstring

don't see what converting utf8 to utf16 has to do with locale ...
or why in the last decade a useful standard function hasn't been
included in standard libraries...

> ...
>
> A C++-ish way of converting a string to a wstring (assuming the characters
> in string are all ASCII or Latin-1 and wchar_t is unicode):
>
> // horribly inefficient but illustrative
> void copy_narrow_to_wide(std::wstring& dst, const char* src)
> {
>   while (*src) dst.push_back((unsigned char)(*(src++)));
> }
>
> Not that I'd necessarily recommend that, but it can work. The link above in
> particular is useful if all you need are to convert narrow strings to wide
> strings for internal usage (logging or such).
>
> However, I think you hit the nail on the head with your last thought
> (especially if you need to display these in potentially localized text):
> generate your own error messages based on the sqlite return code.
>
>
>>
>> Thank you.
>>
>> >
>> > There isn't really a truly portable way of converting from char to
>> wchar_t
>> > based strings, given that there is no real guarantee about what exactly
>> > wchar_t *is*. The ISO C90 standard merely defined it as "an integral type
>> > whose range of values can represent distinct codes for all members of the
>> > largest extended character set specified among the supported locales". In
>> > theory, wchar_t could be a typedef of char if a platform only supported
>> an
>> > 8 bit character set. Unicode was being designed at the time of C
>> > standardization, but the first version of Unicode wasn't published for a
>> > year or more after the C90 standard. Thus the wchar_t type doesn't have
>> to
>> > be Unicode,
>> >
>> > In practical terms, I generally consider wchar_t to be a pre-2011 method
>> of
>> > storing Unicode. Even here there are no portability guarantees. Microsoft
>> > went all in on Unicode in the early 1990s, back when it was only a two
>> byte
>> > encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte
>> type.
>> > Unicode 2.0 extended the Unicode character set in 1996 (I think) and
>> > introduced UTF-16 as a compromise way of allowing systems that embraced
>> > Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support
>> the
>> > full space of Unicode code points from U+ to U+10 via surrogate
>> > pairs. Modern posix systems (as far as I know) define wchar_t to be a 32
>> > bit type, so you can't really convert to wchar_t in a portable way,
>> because
>> > you have to handle surrogate pairs on Windows vs simple code points in
>> > posix (though this should be handled by mbstowcs if the platform supports
>> > wchar_t as unicode).
>> >
>> > If you only use ASCII or Latin-1 8 bit characters in your code, or are
>> > willing to treat all char objects as ASCII or Latin-1, then you can
>> convert
>> > char strings to wchar_t strings by simply zero extending each character
>> > while copying it. Something like this (without any error checking):
>> >
>> > void copy_narrow_to_wide(wchar_t* dst, const char* src)
>> > {
>> >   while (*src) *(dst++) = (unsigned char)(*(src++));
>> >   *dst = 0;
>> > }
>> >
>> > --
>> > Scott Robison
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg and wide char

[Igor Korot]

Hi,
Is there any way to have "sqlite3_errmsg" function return a wide char string?
Or do a conversion in a portable way?

Thank you.