Re: [sqlite] Help with sqlite3_value_text

[x]

Thanks Keith, that I understand. Thanks also to everyone who contributed to 
this thread. I’ve learned a lot from it.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Monday, April 15, 2019 4:09:02 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text


sqlite3_value_text and sqlite3_value_bytes will cause the conversion (if 
required) to UTF-8, and then return the data requested.
sqlite3_value_text16 and sqlite3_value_bytes16 will cause the conversion (if 
required) to UTF-16 and then return the data requested.

So if you call sqlite3_value_text AND THEN sqlite3_value_bytes16 your original 
UTF-8 text pointer will be invalid.  (Because the data must be converted to 
UTF-16 so that you can get the bytes count of that, thus the original UTF-8 no 
longer exists).

If you call only "like for like" functions, then the conversion will only be 
carried out the first time it is required and not for the subsequent calls to 
the "other function" that does not require conversion ...

---
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 x
>Sent: Monday, 15 April, 2019 04:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>>As long as you use _value_bytes after _text you're fine... so if any
>>conversion did take place the value will be right of the last
>returned
>>string type.
>
>JD, Could you explain that to me? I’m not sure why any conversion
>takes place and, on reading the text below, I would’ve thought it
>would be better to call sqlite3_value_bytes first (if it’s called
>“subsequently” the pointer returned by sqlite3_value_text “can be
>invalidated”).
>
>Please pay particular attention to the fact that the pointer returned
>from sqlite3_value_blob()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html> can
>be invalidated by a subsequent call to
>sqlite3_value_bytes()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_bytes16()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html>.”
>
>
>___
>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] Help with loading .DAT files

[Stephen Chrzanowski]

.DAT files can be anything.  If you can just use sqlite3.exe to open the
.DAT and do proper queries on it (IE: sqlite3.exe yourfile.dat), then it's
a proper SQLite3 database, so then you SHOULD be able to use the Attach
command.  Otherwise, you need to change the .DAT contents to something else
that can be understood by IMPORT and OPEN.


On Mon, Apr 15, 2019 at 12:25 PM Pablo Boswell (US - ASR) <
pablo.bosw...@pwc.com> wrote:

> I am trying to use Command Line Interface (CLI) sqlite3.exe to import .DAT
> files to an in-memory SQLite database.  I cannot get the following commands
> to load anything reasonable (the engine always decides to load the data as
> a single TEXT column with a column name of "sqlite3 data"):
>
> - .ATTACH
> - .IMPORT
> - .OPEN
>
> What am I doing wrong?
>
> --
> *Pablo Boswell*
>
> __
> The information transmitted, including any attachments, is intended only
> for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited, and all liability arising therefrom is disclaimed. If you
> received this in error, please contact the sender and delete the material
> from any computer. PricewaterhouseCoopers LLP is a Delaware limited
> liability partnership.  This communication may come from
> PricewaterhouseCoopers LLP or one of its subsidiaries.
> ___
> 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] Help with loading .DAT files

[Simon Slavin]

On 15 Apr 2019, at 4:48pm, Pablo Boswell (US - ASR)  
wrote:

> I cannot get the following commands
> to load anything reasonable (the engine always decides to load the data as
> a single TEXT column with a column name of "sqlite3 data"):

Please copy-and-paste the first line, and another line from lower down the 
file, into a followup message.  Make very sure to keep spacing and punctuation 
exactly the same as it is in the original file.

If the numbers or text must be kept secret, you can swap digits for other 
digits and letter for other letter.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Simon Slavin]

I don't know about any of this, but it seems that someone needs to write a 
'Unicode' (or 'Multibyte charaacters') page for the SQLite documentation.

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

Re: [sqlite] Help with sqlite3_value_text

[Keith Medcalf]

sqlite3_value_text and sqlite3_value_bytes will cause the conversion (if 
required) to UTF-8, and then return the data requested.
sqlite3_value_text16 and sqlite3_value_bytes16 will cause the conversion (if 
required) to UTF-16 and then return the data requested.

So if you call sqlite3_value_text AND THEN sqlite3_value_bytes16 your original 
UTF-8 text pointer will be invalid.  (Because the data must be converted to 
UTF-16 so that you can get the bytes count of that, thus the original UTF-8 no 
longer exists).

If you call only "like for like" functions, then the conversion will only be 
carried out the first time it is required and not for the subsequent calls to 
the "other function" that does not require conversion ...

---
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 x
>Sent: Monday, 15 April, 2019 04:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>>As long as you use _value_bytes after _text you're fine... so if any
>>conversion did take place the value will be right of the last
>returned
>>string type.
>
>JD, Could you explain that to me? I’m not sure why any conversion
>takes place and, on reading the text below, I would’ve thought it
>would be better to call sqlite3_value_bytes first (if it’s called
>“subsequently” the pointer returned by sqlite3_value_text “can be
>invalidated”).
>
>Please pay particular attention to the fact that the pointer returned
>from sqlite3_value_blob()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html> can
>be invalidated by a subsequent call to
>sqlite3_value_bytes()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_bytes16()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html>.”
>
>
>___
>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] Help with sqlite3_value_text

[Clemens Ladisch]

x wrote:
>> As long as you use _value_bytes after _text you're fine... so if any
>> conversion did take place the value will be right of the last returned
>> string type.
>
> Could you explain that to me? I’m not sure why any conversion takes place
> and, on reading the text below, I would’ve thought it would be better to
> call sqlite3_value_bytes first

As shown in the table, conversion from TEXT to BLOB does not change anything.
However, conversion from BLOB to TEXT might require appending a zero terminator.


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

Re: [sqlite] Help with sqlite3_value_text

[x]

>As long as you use _value_bytes after _text you're fine... so if any
>conversion did take place the value will be right of the last returned
>string type.

JD, Could you explain that to me? I’m not sure why any conversion takes place 
and, on reading the text below, I would’ve thought it would be better to call 
sqlite3_value_bytes first (if it’s called “subsequently” the pointer returned 
by sqlite3_value_text “can be invalidated”).

Please pay particular attention to the fact that the pointer returned from 
sqlite3_value_blob(), 
sqlite3_value_text(), or 
sqlite3_value_text16() can be 
invalidated by a subsequent call to 
sqlite3_value_bytes(), 
sqlite3_value_bytes16(), 
sqlite3_value_text(), or 
sqlite3_value_text16().”


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

Re: [sqlite] Help with sqlite3_value_text

[J Decker]

On Sun, Apr 14, 2019 at 5:40 AM x  wrote:

> On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case
> values(1) contains more than a single unicode character. This looks OK.
>
> Bytes are what you need though; it doesn't matter how big the buffer is,
as long as you have all of it.

As long as you use _value_bytes after _text you're fine... so if any
conversion did take place the value will be right of the last returned
string type.


> # define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))
>
> char *c = (char *)sqlite3_value_text(values[0]);
> char *Sep = (char *)sqlite3_value_text(values[1]);
> int Count=0, Len, SepLen = CHARLEN(*Sep);
>
> while (*c)
> {
>if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0)
> Count++; // at start of Sep
>c += Len;
> }
> sqlite3_result_int(ctx, Count);
>
> ___
> 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] Help with sqlite3_value_text

[x]

On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case 
values(1) contains more than a single unicode character. This looks OK.

# define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))

char *c = (char *)sqlite3_value_text(values[0]);
char *Sep = (char *)sqlite3_value_text(values[1]);
int Count=0, Len, SepLen = CHARLEN(*Sep);

while (*c)
{
   if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0) Count++; // 
at start of Sep
   c += Len;
}
sqlite3_result_int(ctx, Count);

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

Re: [sqlite] Help with sqlite3_value_text

[x]

From: J Decker<mailto:d3c...@gmail.com>
Sent: 13 April 2019 20:05
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with sqlite3_value_text

>> char *c = (char *)sqlite3_value_text(values[0]);
>> char *Sep = (char *)sqlite3_value_text(values[1]);
>> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);

>you could use sqlite3_value_bytes(values[1]); instead.

Hi JD, Would I have to worry about this

“Please pay particular attention to the fact that the pointer returned from 
sqlite3_value_blob()<https://sqlite.org/c3ref/value_blob.html>, 
sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or 
sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html> can be 
invalidated by a subsequent call to 
sqlite3_value_bytes()<https://sqlite.org/c3ref/value_blob.html>, 
sqlite3_value_bytes16()<https://sqlite.org/c3ref/value_blob.html>, 
sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or 
sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html>.”

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

Re: [sqlite] Help with sqlite3_value_text

[J Decker]

On Sat, Apr 13, 2019 at 12:04 PM x  wrote:

> This seems to work OK as a sqlite function.
>
>
>
> // assume values[0] & [1] are supplied and not null
>
> // find Count of values[1] in values[0]
>
>
>
> char *c = (char *)sqlite3_value_text(values[0]);
>
> char *Sep = (char *)sqlite3_value_text(values[1]);
>
> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);
>
you could use sqlite3_value_bytes(values[1]); instead.

>
>
>
> while (*c)
>
> {
>
>
>
>Byte1 = (*c) >> 4;
>
>if ((Byte1 & 8) == 0) NrBytes = 1;
>
>else if (Byte1 & 1) NrBytes = 4;
>
>else if (Byte1 & 2) NrBytes = 3;
>
>else NrBytes = 2; // (Byte1 & 4) == 4
>
>
>
>if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++;
> // at first byte of Sep
>
>c += NrBytes;
>
> }
>
> sqlite3_result_int(ctx, Count);
>
>
>
> 
> From: sqlite-users  on
> behalf of Scott Robison 
> Sent: Friday, April 12, 2019 8:40:19 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Help with sqlite3_value_text
>
> On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:
>
> >
> > Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> > variable-length encoding.  An actual "unicode character" is (at this
> > present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
> >
>
> That is some impressive compression! :)
>
> Regardless, even if you use UCS-4, you still have the issue of combining
> characters. Unicode is complex as had been observed.
> ___
> 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] Help with sqlite3_value_text

[x]

This seems to work OK as a sqlite function.



// assume values[0] & [1] are supplied and not null

// find Count of values[1] in values[0]



char *c = (char *)sqlite3_value_text(values[0]);

char *Sep = (char *)sqlite3_value_text(values[1]);

int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);



while (*c)

{



   Byte1 = (*c) >> 4;

   if ((Byte1 & 8) == 0) NrBytes = 1;

   else if (Byte1 & 1) NrBytes = 4;

   else if (Byte1 & 2) NrBytes = 3;

   else NrBytes = 2; // (Byte1 & 4) == 4



   if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++; // 
at first byte of Sep

   c += NrBytes;

}

sqlite3_result_int(ctx, Count);




From: sqlite-users  on behalf of 
Scott Robison 
Sent: Friday, April 12, 2019 8:40:19 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
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] Help with sqlite3_value_text

[x]

Thanks for all the help. Things are much clearer now.

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

Re: [sqlite] Help with sqlite3_value_text

[Warren Young]

On Apr 12, 2019, at 1:06 PM, Keith Medcalf  wrote:
> 
> Actually you would have to convert the strings to UCS-4.

UTF-32 is the new name of that standard:

   https://en.wikipedia.org/wiki/UTF-32#History

> UTF-16 is a variable-length encoding.

Only if you’re outside the BMP, which is why I restricted my answer that way.

And if you add in combining characters and such, *all* Unicode encodings are 
variable-length.

We’re not going to replace the whole books necessary to fully cover Unicode 
here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Leland Helgerson]

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Scott Robison
Sent: Friday, April 12, 2019 2:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a 
> variable-length encoding.  An actual "unicode character" is (at this 
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining 
characters. Unicode is complex as had been observed.
___
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] Help with sqlite3_value_text

[Scott Robison]

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Keith Medcalf]

Actually you would have to convert the strings to UCS-4.  UTF-16 is a 
variable-length encoding.  An actual "unicode character" is (at this present 
moment in time, though perhaps not tomorrow) 4 bytes (64-bits).

---
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, 12 April, 2019 09:45
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>On Apr 12, 2019, at 8:51 AM, x  wrote:
>>
>> How do I do the same thing if the string param is a utf-8 or utf-16
>string and the SearchChar is a Unicode character?
>
>Convert the characters to 32-bit wide characters first, then iterate
>over the array of uint32_t or similar.
>
>One method is shown by the SQLite-internal function
>sqlite3Utf8Read().  It’s static in the amalgamation build, but since
>SQLite is public domain, you can just copy that function’s text out
>into your program and use it there or modify it to suit your
>purposes.
>
>Your platform libraries may have UTF-8 to UTF-32 or similar
>mechanisms.
>
>On POSIX platforms, the most common of these is iconv(3).
>
>On Windows, the legacy of UCS-2 and UTF-16 makes this difficult, but
>if you can stick to the Basic Multilingual Plane, converting UTF-8 to
>UCS-2 gives the same effect.  See MultiByteToWideChar(…, CP_UTF8, …):
>
>https://docs.microsoft.com/windows/desktop/api/stringapiset/nf-
>stringapiset-multibytetowidechar
>
>For all platforms, there’s ICU.  That’s of particular interest with
>SQLite since there’s an included ICU extension you can include to get
>more Unicode power in SQLite:
>
>   https://www.sqlite.org/compile.html#enable_icu
>
>There are complications that your questions don’t push into, but
>beware that if you start getting beyond simple “character in string”
>questions, you’ll eventually have to confront them: combining
>characters, etc.
>___
>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] Help with sqlite3_value_text

[Richard Damon]

> On Apr 12, 2019, at 12:58 PM, x  wrote:
> 
> I’ve been asking myself if I could have done the above more efficiently as 
> sqlite’s converting the original string then I’m converting it and copying 
> it. While thinking about that I started to wonder how c++ handled utf8/16. 
> E.g. To access the i’th character does it have to rattle through all previous 
> I-1 characters to find the start of character i, how pointer arithmetic was 
> handled when pointing to utf8/16 chars etc.
> 

Basically, if you are dealing with a variable width encoding (UTF-8/UTF-16), 
then finding the nth character requires scanning the string counting beginning 
of characters. If this is an important operation, you pay the cost of 
conversion and work in UCS-4. On the other hand, UTF-8 has a lot of nice 
properties such that it can be a fairly seamless upgrade for processing plain 
ASCII text, and if reasonably efficient for typical text. (There are a number 
of complications if you try to support ALL of Unicode, like the composed 
characters, where you use several code-point together to define a single 
character), where you need to decide how you want to normalize and need some 
big character tables for the instructions of how to do this.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[x]

Thanks for the replies. There’s plenty for me to look at there.

I’ve been in poor health the last 5 years and after almost a year’s break I’m 
trying to get back into sqlite to preserve my sanity. I’m so rusty my opening 
post is riddled with errors.

I’ve just realised that, before my break, I was dealing with this as follows

#define VALTXT(i) String((wchar_t*)sqlite3_value_text16(values[i]))

and inside the function I’d access the parameter with

String S = VALTXT(0);

The String type is an embarcadero c++ builder typedef which I think is a wide 
string. I then accessed the i’th character using S[I]. The above involves 
copying the parameter to another string.

In an old post I made on this forum someone told me I should be using 
sqlite3_value_text( as that was sqlite’s default storage and would save sqlite 
having to convert it to utf16.

I’ve been asking myself if I could have done the above more efficiently as 
sqlite’s converting the original string then I’m converting it and copying it. 
While thinking about that I started to wonder how c++ handled utf8/16. E.g. To 
access the i’th character does it have to rattle through all previous I-1 
characters to find the start of character i, how pointer arithmetic was handled 
when pointing to utf8/16 chars etc.

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

Re: [sqlite] Help with sqlite3_value_text

[Warren Young]

On Apr 12, 2019, at 8:51 AM, x  wrote:
> 
> How do I do the same thing if the string param is a utf-8 or utf-16 string 
> and the SearchChar is a Unicode character?

Convert the characters to 32-bit wide characters first, then iterate over the 
array of uint32_t or similar.

One method is shown by the SQLite-internal function sqlite3Utf8Read().  It’s 
static in the amalgamation build, but since SQLite is public domain, you can 
just copy that function’s text out into your program and use it there or modify 
it to suit your purposes.

Your platform libraries may have UTF-8 to UTF-32 or similar mechanisms.

On POSIX platforms, the most common of these is iconv(3).

On Windows, the legacy of UCS-2 and UTF-16 makes this difficult, but if you can 
stick to the Basic Multilingual Plane, converting UTF-8 to UCS-2 gives the same 
effect.  See MultiByteToWideChar(…, CP_UTF8, …):


https://docs.microsoft.com/windows/desktop/api/stringapiset/nf-stringapiset-multibytetowidechar

For all platforms, there’s ICU.  That’s of particular interest with SQLite 
since there’s an included ICU extension you can include to get more Unicode 
power in SQLite:

   https://www.sqlite.org/compile.html#enable_icu

There are complications that your questions don’t push into, but beware that if 
you start getting beyond simple “character in string” questions, you’ll 
eventually have to confront them: combining characters, etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Shawn Wagner]

Welcome to the wonderful world of multibyte encodings, and Unicode in
particular.

Unless you're looking for an ASCII character, you're looking for a
substring, not a character. And you're really looking for what's called a
codepoint (The entire concept of character gets kind of fuzzy with
Unicode). If you're not careful, looking for 'a' (U+0061 LATIN SMALL LETTER
A) will match the start of á, which is actually a two codepoint grapheme
(U+0061 and U+0301 COMBINING ACUTE ACCENT) that renders as a single entity.
And if you're okay with matching that, what about á (U+00E1 LATIN SMALL
LETTER A WITH ACUTE), the single codepoint composed version?

Unicode is hard. There are libraries like ICU and libunistring which help a
bit. I have a bunch of sqlite extensions at
https://github.com/shawnw/useful_sqlite_extensions (That I really need to
polish up for an actual release) including a string library that expands a
lot on the build in ICU extension to make working with graphemes and
unicode in general in sqlite a lot easier.


On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = _value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
> does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
> ___
> 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] Help with sqlite3_value_text

[J Decker]

http://utf8everywhere.org/


On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = _value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>

You'd decide on the common operating mode of your system for 1... there are
of course middleware libraries that need to cater to both and consume
databases instead of just writing it.


>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
>
https://www.sqlite.org/c3ref/value_blob.html

const void *sqlite3_value_text16(

returns wchar data, converting if nessecary from utf8 if it was stored as
utf8.

value_text only returns utf8 (char* really, but suffices for storage and
retreival of utf8 phrases)

for utf8 it would just be matter of matching 1,2,3, or 4 bytes (depending
on the codepoint you were matching).



does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
>
If it is converted, a temporary value is allocated by the library (at
runtime, well beyond the compiler), ...


> ___
> 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] Help with sqlite3_value_text

[Dominique Devienne]

On Fri, Apr 12, 2019 at 4:51 PM x  wrote:

> I’m still confused by utf strings. [...  I want to scan the string to
> count the number of occurrences of a certain character. [...]
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);


That's very wrong. _text() always returns UTF8. the _text16*() variants
return UTF16.

As to how many bytes a UTF8-encoded "codepoint" takes, it's well documented
on Wikipedia.
Based on the leading bits, one can know unambiguously whether this is the
1st, 2nd, 3rd, or 4th
byte of a 1 to 4 multi-byte sequence.

Even UTF16 can lead to "surrogate pairs" for codepoints beyond the
so-called "CMP".

And that's not even getting into the fact the encoding may not be "unique",
and Unicode "normalization".
This is not an easy subject...

You can play with the char() built-in SQL function to see how different
code point values are encoded in UTF8. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Igor Tandetnik]

On 4/12/2019 10:51 AM, x wrote:

I’m still confused by utf strings. For simplicity, suppose I set up an sqlite 
function that takes a single string parameter and I want to scan the string to 
count the number of occurrences of a certain character . If I knew the string 
was made up entirely of ascii chars I’d do this

char *c = _value_text(0)[0];
int count=0;
while (*c) if (*c++ == SearchChar) count++;

How do I do the same thing if the string param is a utf-8 or utf-16 string and 
the SearchChar is a Unicode character?


The problem you need to solve is "count occurrences of a substring in a 
string". The substring in question could consist of one byte representing a single 
ASCII character, or a sequence of bytes comprising a UTF-8 encoding of one Unicode 
character. This really has nothing to do with SQLite.


I’m confused by the fact that Unicode characters are not a fixed number of 
bytes so if I do this e.g.

wchar_t *c = (wchar_t*) sqlite3_value_text(0);


That's just wrong. sqlite3_value_text does *not* return a pointer to a sequence 
of wchar_t. Any attempt to actually use `c` pointer would exhibit undefined 
behavior.


does this mean a complete temporary copy of the value of sqlite3_value_text(0) 
has to be constructed by the compiler such that all characters of the newly 
constructed string are fixed width? If so, I’m just wanting to check if there’s 
a way  of avoiding this overhead.


You seem to ascribe some magical properties to a cast. Nothing is "constructed" by it - 
it simply tells the compiler "take this pointer to a memory block, and believe that it 
contains something different than what the type of the original pointer suggests; trust me, I know 
what I'm doing".

If you prefer UTF-16 encoding over UTF-8, there's sqlite3_value_text16 for that.

If you are unsure what UTF-8 and UTF-16 mean, see

https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

--
Igor Tandetnik


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

Re: [sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

Thanks.  I didn't know this.


From: Luuk 
Sent: Wednesday, April 3, 2019 02:34 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help with INDEXing a query


On 3-4-2019 19:34, Jose Isaias Cabrera wrote:
> Never mind, guys.  I was missing the INDEX for the table for the first left 
> join:
>
> CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);
>
> Everything is nice, now.  Thanks.
>
>
> From: Jose Isaias Cabrera
> Sent: Wednesday, April 3, 2019 01:02 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Help with INDEXing a query
>
>
> Greetings!
>
> I am trying to speed up this query,
>
.

sqlite> .expert
sqlite> select * from testing where a=42;
CREATE INDEX testing_idx_0061 ON testing(a);

SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?)

sqlite>


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

Re: [sqlite] Help with INDEXing a query

[Luuk]

On 3-4-2019 19:34, Jose Isaias Cabrera wrote:

Never mind, guys.  I was missing the INDEX for the table for the first left 
join:

CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);

Everything is nice, now.  Thanks.


From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query


Greetings!

I am trying to speed up this query,


.

sqlite> .expert
sqlite> select * from testing where a=42;
CREATE INDEX testing_idx_0061 ON testing(a);

SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?)

sqlite>

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

Re: [sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

Never mind, guys.  I was missing the INDEX for the table for the first left 
join:

CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);

Everything is nice, now.  Thanks.


From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query


Greetings!

I am trying to speed up this query,

SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a
LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID
LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
WHERE a.ProjID IN
(
  SELECT a.ProjID FROM Project_List WHERE
a.Progress != 'Completed'
AND
a.PMO_Board_Report != 'No'
AND
(
  (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
  (a.Target_Go_Live_Date = '' AND a.Finish_Date >  
substr(date('now'),1,4) || '-01-15')
)
AND
a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
  )
  ORDER BY a.ProjID;";

By creating an INDEX for it.  I have created these INDEXes:
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID, 
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);

I thought this last one would fix it, but it still takes a good 14.88 seconds. 
The above query results in 128 records, out of 128 * 10. Any help would be 
greatly appreciated. Thanks.

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

Re: [sqlite] HELP!

[Chris Locke]

> it is almost guaranteed to corrupt the database file if more than one
connection tries to access it at the same time.

I understand the risks and reasons, but have had numerous databases on our
Windows network accessed by 20+ users throughout the day without issue.


Thanks,
Chris


On Sun, Nov 11, 2018 at 7:12 PM Jay Kreibich  wrote:

>
> > On Nov 11, 2018, at 1:24 AM, Clemens Ladisch  wrote:
> >
> > It's not; SQLite is file based.  The only way to share this would be to
> > make a file share in the company-wide network, i.e., to make the file
> > \\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from
> > everywhere.  (This is likely to be inefficient.)
>
> Not just inefficient, it is almost guaranteed to corrupt the database file
> if more than one connection tries to access it at the same time.  There
> isn’t a remote file system out there (in the Windows or Unix world) that
> correctly implements the locking structures SQLite requires.
>
>   -j
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP!

[Reid Thompson]

On Sun, 2018-11-11 at 00:49 +, am...@juno.com wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> November 10, 2018 Dear Good People: I work for a company that has a many 
> locations with more than one person in every location. I want to share the 
> databases I have built using SQLITE with some of
> the people in each location. Do any of you good people know is SQLITE on the 
> cloud? lf so, I  would be most appreciative if you would tell me how to 
> access it. 2) If SQLITE is on the cloud, how do I
> restrict access--so that one information (not like spelling errors) is 
> inputted, the information cannot be removed. 3) Is there a spell checker 
> function on SQLITE? ?if so, please advise very
> specifically how to find and use it. If any of you good people can help me 
> solve these dilemmas, I would be most appreciative. Thank you very much in 
> advance for helping me solve these problems.
> Respectfully yours, Alex Stavis
> 
> 

see https://bedrockdb.com

Bedrock was built by Expensify, and is a networking and distributed transaction 
layer built atop SQLite, the fastest, most reliable, and most widely 
distributed database in the world.

reid


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

Re: [sqlite] HELP!

[Jay Kreibich]

> On Nov 11, 2018, at 1:24 AM, Clemens Ladisch  wrote:
> 
> It's not; SQLite is file based.  The only way to share this would be to
> make a file share in the company-wide network, i.e., to make the file
> \\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from
> everywhere.  (This is likely to be inefficient.)

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

  -j

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

Re: [sqlite] HELP!

[Clemens Ladisch]

am...@juno.com wrote:
> I work for a company that has a many locations with more than one
> person in every location. I want to share the databases I have built
> using SQLITE with some of the people in each location. Do any of you
> good people know is SQLITE on the cloud?

It's not; SQLite is file based.  The only way to share this would be to
make a file share in the company-wide network, i.e., to make the file
\\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from
everywhere.  (This is likely to be inefficient.)

Consider replacing SQLite with a client/server database:



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

Re: [sqlite] HELP!

[Dingyuan Wang]

There are some cloud solutions:
https://dbhub.io/ for sharing
https://bedrockdb.com/ for distributed hosting

2018/11/11 8:49, am...@juno.com:
> November 10, 2018 Dear Good People: I work for a company that has a many 
> locations with more than one person in every location. I want to share the 
> databases I have built using SQLITE with some of the people in each location. 
> Do any of you good people know is SQLITE on the cloud? lf so, I  would be 
> most appreciative if you would tell me how to access it. 2) If SQLITE is on 
> the cloud, how do I restrict access--so that one information (not like 
> spelling errors) is inputted, the information cannot be removed. 3) Is there 
> a spell checker function on SQLITE? ?if so, please advise very specifically 
> how to find and use it. If any of you good people can help me solve these 
> dilemmas, I would be most appreciative. Thank you very much in advance for 
> helping me solve these problems. Respectfully yours, Alex Stavis
> 
> 1 Simple Trick Removes Lip Lines & Eye Bags in Seconds
> Fit Mom Daily
> http://thirdpartyoffers.juno.com/TGL3131/5be77c818908f7c813b2est01vuc
> ___
> 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] HELP!

[Jens Alfke]

> On Nov 10, 2018, at 4:49 PM, am...@juno.com wrote:
> 
> November 10, 2018 Dear Good People: I work for a company that has a many 
> locations with more than one person in every location. I want to share the 
> databases I have built using SQLITE with some of the people in each location. 
> Do any of you good people know is SQLITE on the cloud?

No. SQLite is a library (with a command-line tool.)

There are some GUI apps that use SQLite, but they’re not part of SQLite itself. 
If you’re using one of those apps, you should  contact its developer (i.e. not 
this list.)

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

Re: [sqlite] Help!

[Clemens Ladisch]

am...@juno.com wrote:
> how to take a database in database (not structure) form--and copy and paste 
> it into an e-mail

A database file is binary; the easiest way would be to attach it.

If you want to paste it into the e-mail itself, you have to convert it to text 
somehow.
Either create a bunch of SQL commands (which includes all the data):

  $ sqlite3 test.db .dump
  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  CREATE TABLE t(x);
  INSERT INTO t VALUES('hello');
  INSERT INTO t VALUES('world');
  CREATE INDEX tx on t(x);
  COMMIT;

Or create a hexdump of the entire file:

  $ od -Ax -tx1 < test.db
  00 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00
  10 10 00 01 01 00 40 20 20 00 00 00 04 00 00 00 03
  20 00 00 00 00 00 00 00 00 00 00 00 02 00 00 00 04
  30 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00
  40 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  50 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 04
  60 00 2e 28 6a 0d 00 00 00 02 0f b7 00 0f df 0f b7
  70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  000fb0 00 00 00 00 00 00 00 26 02 06 17 11 0f 01 3b 69
  000fc0 6e 64 65 78 74 78 74 03 43 52 45 41 54 45 20 49
  000fd0 4e 44 45 58 20 74 78 20 6f 6e 20 74 28 78 29 1f
  000fe0 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43
  000ff0 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29
  001000 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00
  001010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  001fe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02
  001ff0 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f
  002000 0a 00 00 00 02 0f ed 00 0f f7 0f ed 00 00 00 00
  002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  002fe0 00 00 00 00 00 00 00 00 00 00 00 00 00 09 03 17
  002ff0 01 77 6f 72 6c 64 02 08 03 17 09 68 65 6c 6c 6f
  003000

The biggest problem is that the recipient has to know how to turn that back
into a database.


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

Re: [sqlite] Help!

[Mike King]

Hi,

Surely it’s just a case of attaching the database file as an attachment to
the e-mail.

Cheers

On Tue, 6 Nov 2018 at 19:52, am...@juno.com  wrote:

> November 6, 2018 Dear Good People: I would be most appreciative if any of
> you how know how to take a database in database (not structure) form--and
> copy and paste it into an e-mail, please explain in extremely explicit
> detail. Thanks much in advance.  Respectfully yours, Alex Stavis
> 
> Judge Judy Steps Down After 23 Years Over This Controversy
> glancence-hality.com
> http://thirdpartyoffers.juno.com/TGL3131/5be1f0f4d2d2270f42c4est02vuc
> ___
> 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] Help!

[am...@juno.com]

10/27/18 Thanks so much Clements, Respectfully yours, Alex Stavis

-- Original Message --
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help!
Date: Sat, 27 Oct 2018 21:55:28 +0200

am...@juno.com wrote:
> how do I make a field wrap the text. In other words, rather than having
> the text in a field keep going from right to left, when I hit the right
> hand margin, how do I make the text automatically go to the next line.

The purpose of SQLite is to store data, and to give it back to you
unchanged.  How the data is formatted for displaying is not the database's
concern.

It is likely that the software that you're using to display fields has an
option for automatic word wrapping, but nobody here knows what software
that is.

> how do I simultaneously nest two or more fields. For instance, if I have
> four fields: name, department, clock number, department, how do I nest so
> that I alphabetically, sort by the clock number, then last name within
> each department, and ten clock number within in each name?

Just put the column names in this order in the ORDER BY clause when doing
the SQL query.  (Assuming that whatever software you're using allows you
to customize the query.)


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

No More Tinnitus: 1 Odd Trick Ends The Ringing Overnight
fighttinnitusnow.com
http://thirdpartyoffers.juno.com/TGL3131/5bd4e14437d6a61444dd4st04vuc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help!

[am...@juno.com]

10/27/18 Thanks so much Clements, Respectfully yours, Alex Stavis

-- Original Message --
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help!
Date: Sat, 27 Oct 2018 21:55:28 +0200

am...@juno.com wrote:
> how do I make a field wrap the text. In other words, rather than having
> the text in a field keep going from right to left, when I hit the right
> hand margin, how do I make the text automatically go to the next line.

The purpose of SQLite is to store data, and to give it back to you
unchanged.  How the data is formatted for displaying is not the database's
concern.

It is likely that the software that you're using to display fields has an
option for automatic word wrapping, but nobody here knows what software
that is.

> how do I simultaneously nest two or more fields. For instance, if I have
> four fields: name, department, clock number, department, how do I nest so
> that I alphabetically, sort by the clock number, then last name within
> each department, and ten clock number within in each name?

Just put the column names in this order in the ORDER BY clause when doing
the SQL query.  (Assuming that whatever software you're using allows you
to customize the query.)


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

Study Reveals What Just A Single Diet Soda Does To Your...
nucific.com
http://thirdpartyoffers.juno.com/TGL3131/5bd4e08e9cb8608d3d62st04vuc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help!

[Igor Korot]

Hi,

On Sat, Oct 27, 2018 at 1:41 PM am...@juno.com  wrote:
>
> 10/27/18 Dear Good People: I have two issues which I have not been able to 
> solve. Hopefully at least one of you good people know how to do these. The 
> first is: how do I make a field wrap the text. In other words, rather than 
> having the text in a field keep going from right to left, when I hit the 
> right hand margin, how do I make the text automatically go to the next line. 
> The second is: how do I simultaneously nest two or more fields. For instance, 
> if I have four fields: name, department, clock number, department, how do I 
> nest so that I alphabetically, sort by the clock number, then last name 
> within each department, and ten clock number within in each name? As always, 
> if you have any questions, need more information, and/or need any 
> clarifications, ask away. Respectfully yours, Alex Stavis

On top of all other people told you - you most likely have an error in
you schema.
The department field appears twice in you table.

Thank you.

> 
> Gut Doctor Begs Americans - "Throw This Vegetable Now"
> food-frauds.com
> http://thirdpartyoffers.juno.com/TGL3131/5bd4b12c4eb0b312c7c70st03vuc
> ___
> 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] Help!

[Clemens Ladisch]

am...@juno.com wrote:
> how do I make a field wrap the text. In other words, rather than having
> the text in a field keep going from right to left, when I hit the right
> hand margin, how do I make the text automatically go to the next line.

The purpose of SQLite is to store data, and to give it back to you
unchanged.  How the data is formatted for displaying is not the database's
concern.

It is likely that the software that you're using to display fields has an
option for automatic word wrapping, but nobody here knows what software
that is.

> how do I simultaneously nest two or more fields. For instance, if I have
> four fields: name, department, clock number, department, how do I nest so
> that I alphabetically, sort by the clock number, then last name within
> each department, and ten clock number within in each name?

Just put the column names in this order in the ORDER BY clause when doing
the SQL query.  (Assuming that whatever software you're using allows you
to customize the query.)


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

Re: [sqlite] Help!

[Simon Slavin]

On 22 Oct 2018, at 10:43pm, Petite Abeille  wrote:

> You could try the following perhaps:
> 
> Import a CSV File Into an SQLite Table
> http://www.sqlitetutorial.net/sqlite-import-csv/

That is a good page.  Documentation for the CLI tool it refers to can be found 
in chapter 8 of



You may find this easier to read.

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

Re: [sqlite] Help!

[Petite Abeille]

> On Oct 22, 2018, at 11:35 PM, am...@juno.com wrote:
> 
> October 22, 2018 Hello Good People: I need to import a large bunch of names 
> (first and last), and id numbers into SQ Lite. How do I do en-mas--as opposed 
> to copying and pasting each individual name, clock number, etc? I would be 
> most appreciative if you would give me very explicit directions. Thanks very 
> much in advance. Respectfully yours, Alex Stavis

You could try the following perhaps:

Import a CSV File Into an SQLite Table
http://www.sqlitetutorial.net/sqlite-import-csv/



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

Re: [sqlite] help, sqlite3_get_table return 21，

[Richard Hipp]

On 10/22/18, W J  wrote:
> Hello ,
>
> I'm using sqlite3_get_table() to query some information from sqlite. but
> sometimes( not every time), I got error return value: 21.
> #define SQLITE_MISUSE  21   /* Library used incorrectly */
>
> What is the real meaning of this error? How to correct it?
>

It means that you are violating the rules in some way.  SQLite is nice
and returned error 21 instead of segfaulting.

To get more information, run your application in a debugger and set a
breakpoint on the sqlite3MisuseError() function.  Then show us a stack
trace when that breakpoint is hit.

-- 
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] Help!

[Keith Medcalf]

Just pick "File -> Open" off the menu and then point and click that hooey-gooey 
at the database file ... (Assuming here -- most hooey-gooeys have a File Open 
menu clickety-pokey to open a file ...)  

Though since the problem is with "SQLite Studio" I would suggest either RTFM or 
contacting the authors of that software for assistance.  I doubt that anyone 
here can help you with SQLite Studio anymore than they could help you with 
Firefox (which also uses SQLite3 databases) or even Windows itself (the 
MickeyMouse Store uses an SQLite3 database).

 ---
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 Simon Slavin
>Sent: Tuesday, 2 October, 2018 18:26
>To: SQLite mailing list
>Subject: Re: [sqlite] Help!
>
>On 2 Oct 2018, at 11:00pm, am...@juno.com wrote:
>
>> As such, I would be most appreciative if someone would e-mail me
>incredibly explicit directions on how to import a file in SQLite DB
>to SQLite studio.
>
>The two programs should be able to open the same database files.
>SQLite has only one format, and all SQLite files are in that format.
>Make a copy of the SQlite DB file and put it in a folder that SQLite
>studio has access 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] Help!

[Simon Slavin]

On 2 Oct 2018, at 11:00pm, am...@juno.com wrote:

> As such, I would be most appreciative if someone would e-mail me incredibly 
> explicit directions on how to import a file in SQLite DB to SQLite studio.

The two programs should be able to open the same database files.  SQLite has 
only one format, and all SQLite files are in that format.  Make a copy of the 
SQlite DB file and put it in a folder that SQLite studio has access to.

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

Re: [sqlite] Help using 'attach database' or Why is this an error?

[J Decker]

On Wed, Apr 11, 2018 at 7:54 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Try this:
>
> attach database 'test.db' as con2;
>
ahh that makes sense.
I'll split this into a separte thread for the real issue...


>
> On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
>
> When I attempt to use attach database I get an error about 'no such
> column'
>
>
> M:\sqlite3\sqlite3\build>sqlite3 test.db
>
> SQLite version 3.23.0 2018-04-02 11:04:16es
> Enter ".help" for usage hints.
> sqlite> attach database test.db as con2;
> Error: no such column: test.db
> sqlite>
>
>
> I was trying to debug this set of commands, which is actually run on
> two
> connections within the same process... (oh I had misspelled foreign as
> foriegn)
>
>
>
>
> PRAGMA foreign_keys=on;
>
> create table `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);
>
> create table `option4_map` (`option_id` char(36) NOT
> NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id`
> char(36)
> NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
> UNIQUE
> (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
> REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
> CASCADE,
> FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
> CASCADE ON DELETE CASCADE);
>
> create table `option4_values` (`option_id` char(36) default
> '0',`string`
> varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
> `value_id`
> UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
> (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
> DELETE CASCADE);select tbl_name,sql from sqlite_master where
> type='table'
> and
>
> BEGIN TRANSACTION;
>
> insert into option4_name (name_id,name) values(
> '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );
>
> insert into option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> );
>
> #SQLITE ERROR:Result of prepare failed? foreign key mismatch -
> "option4_map" referencing "option4_name" at char 185[] in [insert into
> option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> )]
> ___
> 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] Help using 'attach database' or Why is this an error?

[Peter Da Silva]

Try this:

attach database 'test.db' as con2;

On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" 
 
wrote:

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
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] Help with json1 query?

[Charles Leifer]

No, the keys would be arbitrarily chosen by the user. The rtree extension
could be a possibility, I'll check it out.

On Thu, Mar 15, 2018 at 12:56 AM, Wout Mertens 
wrote:

> Can you elaborate on the metadata? Are the keys always the same, in which
> case you could store them as columns?
>
> There's also the https://sqlite.org/rtree.html extension which lets you
> efficiently query multidimensional range data.
>
> If there is truly no schema, what you propose is the only way AFAIK.
>
> On Wed, Feb 28, 2018, 10:52 PM Charles Leifer,  wrote:
>
> > Hi,
> >
> > I'm prototyping a little graph library using SQLite. My idea is to store
> > vertices in a simple table like this:
> >
> > CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON);
> > CREATE TABLE "edge" (
> > "id" INTEGER NOT NULL PRIMARY KEY,
> > "src" TEXT NOT NULL,
> > "dest" TEXT NOT NULL,
> > "metadata" JSON,
> > FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
> > FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));
> >
> > What I'd like to do is allow querying of edges (or vertices) using a
> > *partial* metadata object. So if I had the following JSON object stored
> in
> > an edge's metadata:
> >
> > {"k1": "v1", "k2": "v2", "k3": "v3"}
> >
> > The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
> > would be able to match the above edge's metadata.
> >
> > I can see decomposing the user-provided dictionary and building up
> multiple
> > equality tests using the json_extract() function, e.g.:
> >
> > select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
> > json_extract(metadata, '$.k3') = 'v3';
> >
> > But I was hoping there would be a more elegant way to express this that
> > someone would be able to share? It seems as though I should be able to
> use
> > `json_each()` (or even `json_tree()` if metadata could be nested?), but
> I'm
> > not sure how to formulate the query.
> >
> > It'd be great if there were a JSON function like "json_contains()" where
> I
> > could write:
> >
> > select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
> > "v3"}');
> >
> > Any help appreciated!
> >
> > Charlie
> > ___
> > 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] Help with json1 query?

[Wout Mertens]

Can you elaborate on the metadata? Are the keys always the same, in which
case you could store them as columns?

There's also the https://sqlite.org/rtree.html extension which lets you
efficiently query multidimensional range data.

If there is truly no schema, what you propose is the only way AFAIK.

On Wed, Feb 28, 2018, 10:52 PM Charles Leifer,  wrote:

> Hi,
>
> I'm prototyping a little graph library using SQLite. My idea is to store
> vertices in a simple table like this:
>
> CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON);
> CREATE TABLE "edge" (
> "id" INTEGER NOT NULL PRIMARY KEY,
> "src" TEXT NOT NULL,
> "dest" TEXT NOT NULL,
> "metadata" JSON,
> FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
> FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));
>
> What I'd like to do is allow querying of edges (or vertices) using a
> *partial* metadata object. So if I had the following JSON object stored in
> an edge's metadata:
>
> {"k1": "v1", "k2": "v2", "k3": "v3"}
>
> The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
> would be able to match the above edge's metadata.
>
> I can see decomposing the user-provided dictionary and building up multiple
> equality tests using the json_extract() function, e.g.:
>
> select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
> json_extract(metadata, '$.k3') = 'v3';
>
> But I was hoping there would be a more elegant way to express this that
> someone would be able to share? It seems as though I should be able to use
> `json_each()` (or even `json_tree()` if metadata could be nested?), but I'm
> not sure how to formulate the query.
>
> It'd be great if there were a JSON function like "json_contains()" where I
> could write:
>
> select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
> "v3"}');
>
> Any help appreciated!
>
> Charlie
> ___
> 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] Help getting started

[Simon Slavin]

On 9 Mar 2018, at 4:42pm, Richard Hipp  wrote:

> On 3/9/18, Larry Mullings  wrote:
>> I have a SQLite Bible database.  It has
>> Bible verses with Strong's numbers and Hebrew.
> 
> Are you willing to share your database?  Can you send me a copy via
> private email attachment?

Anyone interested in this might also be interested in the ".sql" files listed 
here:



However, these are English and Greek, not Hebrew.

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

Re: [sqlite] Help getting started

[Richard Hipp]

On 3/9/18, Larry Mullings  wrote:
> I have a SQLite Bible database.  It has
> Bible verses with Strong's numbers and Hebrew.

Are you willing to share your database?  Can you send me a copy via
private email attachment?
-- 
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] Help getting started

[Donald Griggs]

Hi Larry,

Since you mention sqlite3.exe, I assume you're on Windows.

Kudos for compiling your own exe, but if, in future, you find you don't
need special compile features, you can always download the current version
exe from http://sqlite.org/download.html  .

If I understand you correctly, you'll want to go to a Windows cmd prompt
screen,
then type
   sqlite3   YourDatabasename

This should open your database and give you a prompt for
sqlite sql commands and "dot" commands

https://sqlite.org/cli.html

There are also many windows GUI utilities (written by third-parties) which
you may find useful.

As to adding a column, the simplest method is to use the ALTER TABLE command
http://sqlite.org/lang_altertable.html
but you may also choose to copy the existing table into a newly-defined
table, then remove the old one and rename.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help getting started

[Simon Slavin]

On 9 Mar 2018, at 3:48pm, Larry Mullings  wrote:

> I'm in need of some serious help.  I'm a first timer to anything database.  I 
> have a SQLite Bible database.  It has
> Bible verses with Strong's numbers and Hebrew.  I'd like to add some fields 
> to the database.  I downloaded
> sqlite-amalgamation-322 and compiled it.  Now I have sqlite3.exe and .obj 
> files.  Now what do I need to do?

What operating system are you using ?  If its Windows or Macintosh, include the 
version number.

You probably already have a compiled version of the sqlite3 executable, but to 
tell you where to fine it we need to know your operating system.

Once you can actually run a copy of sqlite3.exe, see this page:



I advise you to experiment with a new blank file as the page shows, and learn 
how to do things first, before you start changing /a copy of/ your bible 
database.

If you have successfully got through sections 11 and 2 of this page, post again 
and someone will show you how to add more columns to an existing table.

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

Re: [sqlite] Help with row values

[Simon Slavin]

On 14 Feb 2018, at 7:50am, Dominique Devienne  wrote:

> Thanks. That's interesting. But then, why use it in this context?
> Why DRH wants to purposely bypass the index in this case?
> How is that relevant to testing tuple / row-values comparisons? --DD

In the original example, column a is the PRIMARY KEY.  SQLite automatically 
makes an index for the PRIMARY KEY.  So SQLIte might use that index if there's 
a WHERE clause on a.  In the case of the ticket DRH wants to avoid this, since 
the bug shows up only when an index is not used.

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

Re: [sqlite] Help with row values

[Dominique Devienne]

On Wed, Feb 14, 2018 at 8:44 AM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> > to fix above ticket, I see queries with order by +a,
> > but in https://www.sqlite.org/lang_select.html#orderby
> > I don't see any obvious mention about that +.
>
> "Unary plus" does not change the value:
>
>   sqlite> select 1;
>   1
>   sqlite> select -1;
>   -1
>   sqlite> select +1;
>   1
>
> It's used to change the expression from a column reference to something
> that is computed, which means that SQLite cannot use an index for it
> (unless you created an expression index on +a):
> 


Thanks. That's interesting. But then, why use it in this context?
Why DRH wants to purposely bypass the index in this case?
How is that relevant to testing tuple / row-values comparisons? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Clemens Ladisch]

Dominique Devienne wrote:
> in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> to fix above ticket, I see queries with order by +a,
> but in https://www.sqlite.org/lang_select.html#orderby
> I don't see any obvious mention about that +.

"Unary plus" does not change the value:

  sqlite> select 1;
  1
  sqlite> select -1;
  -1
  sqlite> select +1;
  1

It's used to change the expression from a column reference to something
that is computed, which means that SQLite cannot use an index for it
(unless you created an expression index on +a):



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

Re: [sqlite] Help with row values

[Dominique Devienne]

On Tue, Feb 13, 2018 at 7:09 PM, Richard Hipp  wrote:

> On 2/13/18, Simon Slavin  wrote:
> > On 13 Feb 2018, at 5:32pm, x  wrote:
> >
> >> Surely it should be 3 in both cases?
> >
> > I agree.  Here's verification with a version number:
>
> https://www.sqlite.org/src/tktview/f484b65f3d623059


in https://www.sqlite.org/src/info/f3112e67cdb27c1a
to fix above ticket, I see queries with order by +a,
but in https://www.sqlite.org/lang_select.html#orderby
I don't see any obvious mention about that +.

Could someone please explain what this + is? Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[x]

Thanks Richard.

From: Richard Hipp<mailto:d...@sqlite.org>
Sent: 13 February 2018 19:17
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with row values

Now fixed on trunk.  Thanks for the bug report.

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

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

Re: [sqlite] Help with row values

[Richard Hipp]

Now fixed on trunk.  Thanks for the bug report.

-- 
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] Help with row values

[Richard Hipp]

On 2/13/18, Simon Slavin  wrote:
> On 13 Feb 2018, at 5:32pm, x  wrote:
>
>> Surely it should be 3 in both cases?
>
> I agree.  Here's verification with a version number:

https://www.sqlite.org/src/tktview/f484b65f3d623059
-- 
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] Help with row values

[Simon Slavin]

On 13 Feb 2018, at 5:32pm, x  wrote:

> Surely it should be 3 in both cases?

I agree.  Here's verification with a version number:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
3
sqlite> select count(*) from t where (a,1)>(0,0);
0

but also

sqlite> select * from t WHERE (a,0)>(0,0);
sqlite> SELECT (3,0) > (0,0);
1
sqlite> select a,typeof(a) FROM t;
1|integer
3|integer
5|integer
sqlite> select 3,typeof(3);
3|integer
sqlite> 

weird.

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

Re: [sqlite] Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

[Clemens Ladisch]

You have to do two things to run SQL statements from a batch file:
1. Use quotes so that all parameters to sqlite3.exe are a single parameter; and
2. Use quotes so that SQL strings are delimited correctly.

e-mail wrote:
> sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... 
> datetime(...,"unixepoch","localtime") AS Date FROM ..."

Double quotes are interpreted by cmd.exe to delimit parameters to sqlite3.exe,
so they will not show up in the actual SQL statement as seen by sqlite3.

> sqlite3.exe -csv C:\...\places.sqlite SELECT ... 
> datetime(...,'unixepoch','localtime') AS Date FROM ...

Without quotes, there are too many parameters.


In SQL, strings should use single quotes (double quotes are just for bug
compatibility with MySQL); and cmd.exe expects double quotes for parameters.
So this is easy:

  sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... 
datetime(...,'unixepoch','localtime') AS Date FROM ..."


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

Re: [sqlite] help with EXPLAIN QUERY PLAN

[Lutz Horn]

Ho,

Am 23.11.17 um 17:33 schrieb x:
> Can anyone tell me why the detail column sometime states ‘USING INDEX
> ’ and other time ‘USING COVERING INDEX ...’?

See https://sqlite.org/queryplanner.html#_covering_indices

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

Re: [sqlite] Help with left joins

[x]

Thanks Ryan. When I saw the redundant table still in the explain I was worried 
I had misunderstood something about left joins.



Tom




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
R Smith <rsm...@rsweb.co.za>
Sent: Monday, November 20, 2017 3:55:42 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help with left joins


On 2017/11/20 5:33 PM, x wrote:
>> Only if ColB, ColC and ColD are unique in their tables. Otherwise each join 
>> has the potential of returning multiple rows, which will carry over to the 
>> next joins.
> Thanks David, I did say ColB, ColC & ColD were primary keys.

Any kind of Query (especially of the SELECT variety) can have multiple
redundancies or  indeed omit-able specified values. An optimization to
ensure non-used left-joins on Primary keys are not used is probably a
negligible improvement.

To answer your question: You are correct to assume the same output -
There is no difference in the SET-Theory/Algebra producing those
specific rows whether you omit the left joins or not, BUT that only
holds true while the Indexes are indeed all PRIMARY (or at least Unique)
and the joins are of the "LEFT" variety. Change any of those and the
query output may look very different.

___
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] Help with left joins

[R Smith]

On 2017/11/20 5:33 PM, x wrote:

Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has 
the potential of returning multiple rows, which will carry over to the next 
joins.

Thanks David, I did say ColB, ColC & ColD were primary keys.


Any kind of Query (especially of the SELECT variety) can have multiple 
redundancies or  indeed omit-able specified values. An optimization to  
ensure non-used left-joins on Primary keys are not used is probably a 
negligible improvement.


To answer your question: You are correct to assume the same output - 
There is no difference in the SET-Theory/Algebra producing those 
specific rows whether you omit the left joins or not, BUT that only 
holds true while the Indexes are indeed all PRIMARY (or at least Unique) 
and the joins are of the "LEFT" variety. Change any of those and the 
query output may look very different.


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

Re: [sqlite] Help with left joins

[x]

>Only if ColB, ColC and ColD are unique in their tables. Otherwise each join 
>has the potential of returning multiple rows, which will carry over to the 
>next joins.

Thanks David, I did say ColB, ColC & ColD were primary keys.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with left joins

[David Raymond]

Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has 
the potential of returning multiple rows, which will carry over to the next 
joins.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Monday, November 20, 2017 8:22 AM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins

I’m not sure what you’re saying Simon or maybe you’re not sure what I’m asking. 
I’m not complaining about the fact SQLite fails to drop what I think is a 
redundant table in the second explain, I’m merely wanting to check that it is a 
redundant table in case I’ve got a wrong idea about left joins. Put it this way 
will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Simon Slavin <slav...@bigfraud.org>
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins



On 20 Nov 2017, at 11:09am, x <tam118...@hotmail.com> wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
>
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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

Re: [sqlite] Help with left joins

[x]

I’m not sure what you’re saying Simon or maybe you’re not sure what I’m asking. 
I’m not complaining about the fact SQLite fails to drop what I think is a 
redundant table in the second explain, I’m merely wanting to check that it is a 
redundant table in case I’ve got a wrong idea about left joins. Put it this way 
will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Simon Slavin <slav...@bigfraud.org>
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins



On 20 Nov 2017, at 11:09am, x <tam118...@hotmail.com> wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
>
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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] Help with left joins

[Simon Slavin]

On 20 Nov 2017, at 11:09am, x  wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
> 
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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

Re: [sqlite] Help with establishing a connection on NS3 and sqlite DB

[Dan Kennedy]

On 03/22/2017 05:58 AM, Ausama Majeed wrote:

Hello guys,

I am trying to do a connection between a database created with Sqlite and
my application in ns3. the sqlite engine is installed on ubuntu 16.04
machine and the output is enabled with ns3.26. I cann't do a simple select
query from ns3, however it working through the terminal.
I install sqlite-autoconf-317 as API to deal with the database.

the BD is opened successfully in the fallowing code:

sqlite3 *db;
 int rc;
 char *error = 0;
 rc = sqlite3_open("/home/mypc/Desktop/ns-3.26/ns-3.26/testDB.db", );
 if (rc) {
 cerr << "Error opening SQLite3 database: " << sqlite3_errmsg(db) <<
endl << endl;
 sqlite3_close(db);
 return 1;
 } else {
 cout << "\n Successfully connected to the database \n";
 int n = 0;
 cin >> n;
 // Print this info

  cout << GARIComposeAlgo(db, error, n);
 cout << "\nclose the db\n";
 sqlite3_close(db);
}


But, select query returns only the table field headers instead of the
required record   in the following code


As expected, I think. If your SELECT statement returns N rows of M 
columns, get_table() gives you an array of (N+1)*M nul-terminated 
strings. The first M strings in the array are the column headers, the 
next M are the first row of results, and so on.


  https://sqlite.org/c3ref/free_table.html

Dan.






string Query = " select ActorId, ActorType from ActorInfo where ID =" +
tempProcess.str() +";";


 char **results = NULL;
 int rows, columns;
 const char *sqlSelect = Query.c_str();
 int rc;
 rc = sqlite3_get_table(db, sqlSelect, , , ,
);
 if (rc != SQLITE_OK) {
 cerr << "Error executing SQLite3 query: " << sqlite3_errmsg(db) <<
endl << endl;
 sqlite3_free(error);
 }
 else {
for (int i= 0; i<4; i++) {cout << results[i]<< endl;}
}

Could anyone advice me what could the problem and how to check it, solve it
please.

Thanks
___
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] Help with establishing a connection on NS3 and sqlite DB

[Simon Slavin]

On 21 Mar 2017, at 10:58pm, Ausama Majeed  wrote:

> But, select query returns only the table field headers instead of the
> required record   in the following code
> 
> string Query = " select ActorId, ActorType from ActorInfo where ID =" +
> tempProcess.str() +";";

For debugging purposes, please have your program display the value of sqlSelect 
(or log it to an error channel, or some such thing) before it tries to execute 
it.  This will allow you to figure out whether you’re getting the right value 
for ID.

However, I suspect that you should worry more that you’re using 
sqlite3_get_table().  That’s an obsolete call and should not be used for new 
code. Instead use sqlite3_exec().

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

Re: [sqlite] Help file has no information

[Rob Richardson]

Thank you.  That worked.  (The button was labelled "unblock", not "unlock")

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Graham Holden
Sent: Monday, March 13, 2017 2:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help file has no information

Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
<rdrichard...@rad-con.com> Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
<sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Help file has no 
information The help file that is linked from the System.Data.SQLite home page 
appears to have a table of contents but no information.  No matter what page I 
select, the page does not appear.

RobR
___
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] Help file has no information

[Graham Holden]

Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
 Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
 Subject: [sqlite] Help file has no 
information 
The help file that is linked from the System.Data.SQLite home page appears to 
have a table of contents but no information.  No matter what page I select, the 
page does not appear.

RobR
___
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] Help file has no information

[Simon Slavin]

On 13 Mar 2017, at 6:14pm, Rob Richardson  wrote:

> The help file that is linked from the System.Data.SQLite home page appears to 
> have a table of contents but no information.  No matter what page I select, 
> the page does not appear.

This is the result of a bug in Microsoft’s display code.  Please see



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

Re: [sqlite] Help with Backup API please

[Barry]

Ok. My bad for singling out SQLite, I should have rather mentioned that the
problem exists with all memory allocation routines (malloc/free or
new/delete), at least on windows.

A workaround is to pass around allocator / deallocator function pointers
with any data structure which contains pointers which might be free'd or
realloc'd in a different module. My assumption was that SQLite does not do
this, since being Lite is a main priority and if one is using multiple
modules then option to dynamically link to SQLite avoids the problem.

My reason for assuming memory corruption (or invalid call to free(...)) was
the fact that Brett is encountering an exception rather than an invalid
return code.

On 8 February 2017 at 14:16, Richard Hipp  wrote:

> On 2/8/17, Barry Smith  wrote:
> >
> > I believe SQLite doesn't use the standard memory allocation routines, but
> > instead has its own routines.
>
> SQLite has its on memory allocation routines (if you use the right
> compile-time and start-time options) but it uses system
> malloc()/realloc()/free() by default.
>
> There are internal wrappers routines for the memory allocator that
> dispatch requests to the appropriate implementation.  Again, unless
> you take special actions to change it, memory allocation request are
> satisfied by malloc().
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Backup API please

[Richard Hipp]

On 2/8/17, Barry Smith  wrote:
>
> I believe SQLite doesn't use the standard memory allocation routines, but
> instead has its own routines.

SQLite has its on memory allocation routines (if you use the right
compile-time and start-time options) but it uses system
malloc()/realloc()/free() by default.

There are internal wrappers routines for the memory allocator that
dispatch requests to the appropriate implementation.  Again, unless
you take special actions to change it, memory allocation request are
satisfied by malloc().

-- 
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] Help with Backup API please

[Barry Smith]

Hi Brett,

I believe SQLite doesn't use the standard memory allocation routines, but 
instead has its own routines. These (might) use global variables. If each 
module of your application statically links to the SQLite source rather than 
having SQLite in a common DLL, then each module will have its own set of global 
variables. As such, passing raw SQLite pointers across module boundaries can 
result in undefined behaviour. I suspect other issues could also occur with 
other global variables in SQLite.

Note that passing the raw pointer is not the real culprit, but rather having 
different modules attempt to work on the pointer. For example:, the following 
should work fine:
sqlite3* pDb = DLL1GetDb();
DLL1DoSomething(pDb);

But either of the following could fail:
sqlite3* pDb = DLL1GetDb();
DLL2DoSomething(pDb);
// or, if the calling module is not DLL1
sqlite3_exec(pDb,...);

Personally, to enforce this I do not expose (either in arguments or return 
values) raw sqlite objects (dbs, statements, or anything else) in exported 
functions.

If you already have SQLite in a common DLL, or you perform all sqlite3_* calls 
from the same module, I apologise for wasting your time.

Also, SQLite is written in C so it uses error return codes rather than throwing 
exceptions. The exception you are catching would have to be thrown by another 
library that SQLite is calling. This would mean that the output of 
sqlite3_errmsg is likely unrelated to the exception since SQLite would never 
have intercepted it. Perhaps your debugging environment could tell you more 
details of the real proximate cause of the error (unless the you mean that the 
error message of the exception, by coincidence, was the same as a standard 
SQLite error message)

Cheers,

 - Barry 

> On 8 Feb 2017, at 9:30 AM, Brett Goodman  wrote:
> 
> Hello Sqlite users.  I have a problem I can't solve.  I have a C++ DLL 
> project in which I compile the Sqlite amalgamation code. The purpose is to 
> wrap the key Sqlite functions with exported functions I can call from another 
> C++ COM DLL.   I'm using VS2003 for this because its part of a legacy 
> product.  I've been using this project for years, updating the Sqlite source 
> code from time to time with no problems.  I can call functions like 
> sqlite3_open_v2, sqlite3_prepare, sqlite3_step, etc. with no problems.  Now 
> I'm trying to integrate the Backup API.  When I call sqlite3_backup_init it 
> throws this error: _/"library /__/
> /__/routine called out of sequence"/_.  I want to step into the Sqlite code 
> to find out why but for some reason my debugger won't allow me to step into 
> the C code from my C++ code.  So I'm hoping someone can help me.  Below is my 
> C++ implementation which I modeled from the sample on this page: 
> sqlite.org/backup.html.  My calling code is from a VB6 project which simply 
> creates/opens a memory DB and calls the below function (via my COM DLL).  Any 
> help is much appreciated.
> 
> Thanks,
> BrettG
> 
> 
> SQLITE_DLL_API int backup( /*sqlite3*/ void* pdb, const WCHAR *pFilename, 
> BOOL toFile )
> {
>  int rc;
>  sqlite3 *pFileDb;
>  sqlite3_backup *pBackup;
>  sqlite3 *pTo;
>  sqlite3 *pFrom;
> 
>  CTextConverter c(CP_UTF8);
>  //rc = sqlite3_open_v2( c.U2M(pFilename), , SQLITE_OPEN_READWRITE, 
> NULL);
>  rc = sqlite3_open( c.U2M(pFilename), );
> 
>  if( rc==SQLITE_OK )
>  {
>try
>{
>sqlite3 *pMemoryDb = (sqlite3*) pdb;
>pFrom = (toFile ? pMemoryDb : pFileDb);
>pTo   = (toFile ? pFileDb : pMemoryDb);
> 
>// this line throws exception:  "library routine called out of 
> sequence"
> *pBackup = sqlite3_backup_init(pTo, "main", pFrom, "temp");*
>if( pBackup )
>{
>sqlite3_backup_step(pBackup, -1);
>sqlite3_backup_finish(pBackup);
>}
>rc = sqlite3_errcode(pTo);
>sqlite3_close(pFileDb);
>}
>catch(...)
>{
>sqlite3_close(pFileDb);
>const char* err = sqlite3_errmsg( (sqlite3*) pdb );
>throw err;
>}
>  }
>  return rc;
> }
> 
> ___
> 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] Help with Backup API please

[Clemens Ladisch]

Brett Goodman wrote:
> When I call sqlite3_backup_init it throws this error: _/"library /__/
> /__/routine called out of sequence"/_.

To you get an error code, or an exception?
In the first case, try calling sqlite3_errmsg().

The documentation says:
| A call to sqlite3_backup_init() will fail, returning NULL, if there is
| already a read or read-write transaction open on the destination
| database.


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

Re: [sqlite] Help with custom collation

[x]

Thanks for the advice Jens. My problem is the software I use (Embarcadero c++ 
builder) is more utf16 orientated. It does support utf8 but many of the built 
in functions return System.UnicodeString which is utf16.





From: Jens Alfke<mailto:j...@mooseyard.com>
Sent: 02 February 2017 17:32
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with custom collation



Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to 
work with legacy APIs that require it. It has the same difficulties as UTF-8 
(having to handle characters broken into multi-element sequences) but uses more 
RAM and isn’t downward compatible with ASCII. Also, since you’re less likely to 
run into multi-word character sequences in normal use [if you’re non-Asian], 
it’s easier to miss bugs in your multi-word character handling. (Tip: put some 
emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since 
SQLite will need to copy/expand the UTF-8 strings from the database when it 
calls it. And yes, collators can easily become performance bottlenecks; I’ve 
seen it.)

—Jens
___
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] Help with custom collation

[Jens Alfke]

Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to 
work with legacy APIs that require it. It has the same difficulties as UTF-8 
(having to handle characters broken into multi-element sequences) but uses more 
RAM and isn’t downward compatible with ASCII. Also, since you’re less likely to 
run into multi-word character sequences in normal use [if you’re non-Asian], 
it’s easier to miss bugs in your multi-word character handling. (Tip: put some 
emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since 
SQLite will need to copy/expand the UTF-8 strings from the database when it 
calls it. And yes, collators can easily become performance bottlenecks; I’ve 
seen it.)

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

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 2/2/17, Shaobo He  wrote:
> May I ask that do you see null pointer deferences
> during development regularly?

Sometimes, but not too often.  We get assertion faults more.  Or just
incorrect answers.
-- 
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] Help with custom collation

[x]

Thanks Dan. Replacing “Compare” with L”Compare” and casting to wchar_t* inside 
the function solved the problem.



Thanks for all the replies.





From: Dan Kennedy<mailto:danielk1...@gmail.com>
Sent: 02 February 2017 08:42
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with custom collation



On 02/02/2017 02:08 AM, x wrote:
> Thanks Clemens. You’re right about changing the UTF8String* to char* as it 
> now works but when trying it with a column containing Unicode characters it 
> didn’t. I’d have liked to have tried it with windows wchar_t* type but If I 
> try using
>
>
>
>   if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, 
> NULL, ) != SQLITE_OK)
>
>   throw Exception("Collation creation error");
>
>
>
> I get the message “no such collation sequence: Compare” when running the 
> query.

The second argument passed to sqlite3_create_collation16() should point
to a buffer containing a utf-16 string. Not utf-8.

Dan.



>
>
>
>
>
>
>
> From: Clemens Ladisch<mailto:clem...@ladisch.de>
> Sent: 01 February 2017 17:32
> To: 
> sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Help with custom collation
>
>
>
> x wrote:
>> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
>> {
>>  const UTF8String *S1 = static_cast(s1),
>>  *S2 = static_cast(s2);
>>  return 0;
>> }
>>
>>  if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, 
>> NULL, ) != SQLITE_OK)
>>  throw Exception("Collation creation error");
>>
>> S1 and S2 appear to point to NULL values.
> What is "UTF8String"?  If it is anything different from "char", the code is 
> wrong.
>
>> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
>> the Compare function is never entered.
>> Is this because ID is an integer column?
> No, it is because the values in that column are integer values.
>
>> Is there no way to implement a custom collation on an integer column?
> There is no way to implement a custom collation for integer values.
> Collations are used only for string values.
>
>
> Regards,
> Clemens
> ___
> 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] Help with custom collation

[Dan Kennedy]

On 02/02/2017 02:08 AM, x wrote:

Thanks Clemens. You’re right about changing the UTF8String* to char* as it now 
works but when trying it with a column containing Unicode characters it didn’t. 
I’d have liked to have tried it with windows wchar_t* type but If I try using



  if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)

  throw Exception("Collation creation error");



I get the message “no such collation sequence: Compare” when running the query.


The second argument passed to sqlite3_create_collation16() should point 
to a buffer containing a utf-16 string. Not utf-8.


Dan.











From: Clemens Ladisch<mailto:clem...@ladisch.de>
Sent: 01 February 2017 17:32
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with custom collation



x wrote:

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
{
 const UTF8String *S1 = static_cast(s1),
 *S2 = static_cast(s2);
 return 0;
}

 if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
 throw Exception("Collation creation error");

S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.


If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
the Compare function is never entered.
Is this because ID is an integer column?

No, it is because the values in that column are integer values.


Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
___
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] Help with custom collation

[Hick Gunter]

Sorry misread that you are attempting to write a custom collation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Donnerstag, 02. Februar 2017 09:06
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Help with custom collation

The interface your (simple) function must support is:

void xFunc(sqlite3_context*,int,sqlite3_value**)

with the first parameter being the sqlite3_context, the second parameter being 
the number of arguments passed in, and the third parameter being an array of 
pointers to unprotected sqlite3_value objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for 
aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
const UTF8String *S1 = static_cast(s1),
*S2 = static_cast(s2);
return 0;
}

Which was registered with the following code

if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ 
line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 
params with correct looking values but the S1 and S2 appear to point to NULL 
values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate 
Compare’ the Compare function is never entered. Is this because ID is an 
integer column? Is there no way to implement a custom collation on an integer 
column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");
it seems to register OK but running the select yields a “no such 
collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the 
sqlite3.c amalgamation included in my project.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Help with custom collation

[Hick Gunter]

The interface your (simple) function must support is:

void xFunc(sqlite3_context*,int,sqlite3_value**)

with the first parameter being the sqlite3_context,
the second parameter being the number of arguments passed in, and
the third parameter being an array of pointers to unprotected sqlite3_value 
objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for 
aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
const UTF8String *S1 = static_cast(s1),
*S2 = static_cast(s2);
return 0;
}

Which was registered with the following code

if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ 
line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 
params with correct looking values but the S1 and S2 appear to point to NULL 
values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate 
Compare’ the Compare function is never entered. Is this because ID is an 
integer column? Is there no way to implement a custom collation on an integer 
column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");
it seems to register OK but running the select yields a “no such 
collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the 
sqlite3.c amalgamation included in my project.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Help with confirming a couple of error traces

[Shaobo He]

Thanks, Richard. I think that I fully understand what happens now. Thanks
again for your patience. May I ask that do you see null pointer deferences
during development regularly?

Shaobo

Richard Hipp 于2017年2月1日周三 下午6:52写道：

> On 2/1/17, Shaobo He  wrote:
> > Thanks for your reply. I repeated the experiment by setting
> > db->mallocFailed upon return. You are right that there is no segmentation
> > fault (there were some assertion failures: e.g, "sqlite3OomClear:
> Assertion
> > `db->lookaside.bDisable>0' failed"). Instead I got error messages saying
> > out of memory. It makes sense now. May I ask where the unwinding is done?
> > Does it mean the program stops execution at sqlite3SrcListAppend()?
> >
>
> The assertion fault is probably because you are playing games with the
> memory allocator - pretending that a fault occurred when it did not.
> You might be able to work around that by compiling with
> -DSQLITE_OMIT_LOOKASIDE.
>
> An OOM in sqlite3SrcListAppend() will likely cause the parser to abort
> at https://www.sqlite.org/src/artifact/25ccc63ae?ln=547.
>
> The SQLite parser works by extracting tokens from the input string and
> sending them one by one into the pushdown automaton that implements
> the recognizes the LALR(1) grammar.  If you break out of that loop, it
> stops the parser dead in its tracks.  After that, all the left-over
> memory allocations are cleaned up and the tokenizer returns the
> SQLITE_NOMEM error.
>
> There are other places where a prior OOM can cause processing to
> abort.  Grep for "mallocFailed" to find them.  But the tokenizer loop
> is the most likely spot.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 2/1/17, Shaobo He  wrote:
> Thanks for your reply. I repeated the experiment by setting
> db->mallocFailed upon return. You are right that there is no segmentation
> fault (there were some assertion failures: e.g, "sqlite3OomClear: Assertion
> `db->lookaside.bDisable>0' failed"). Instead I got error messages saying
> out of memory. It makes sense now. May I ask where the unwinding is done?
> Does it mean the program stops execution at sqlite3SrcListAppend()?
>

The assertion fault is probably because you are playing games with the
memory allocator - pretending that a fault occurred when it did not.
You might be able to work around that by compiling with
-DSQLITE_OMIT_LOOKASIDE.

An OOM in sqlite3SrcListAppend() will likely cause the parser to abort
at https://www.sqlite.org/src/artifact/25ccc63ae?ln=547.

The SQLite parser works by extracting tokens from the input string and
sending them one by one into the pushdown automaton that implements
the recognizes the LALR(1) grammar.  If you break out of that loop, it
stops the parser dead in its tracks.  After that, all the left-over
memory allocations are cleaned up and the tokenizer returns the
SQLITE_NOMEM error.

There are other places where a prior OOM can cause processing to
abort.  Grep for "mallocFailed" to find them.  But the tokenizer loop
is the most likely spot.
-- 
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] Help with confirming a couple of error traces

[Shaobo He]

Thanks for your reply. I repeated the experiment by setting
db->mallocFailed upon return. You are right that there is no segmentation
fault (there were some assertion failures: e.g, "sqlite3OomClear: Assertion
`db->lookaside.bDisable>0' failed"). Instead I got error messages saying
out of memory. It makes sense now. May I ask where the unwinding is done?
Does it mean the program stops execution at sqlite3SrcListAppend()?

Shaobo

Richard Hipp 于2017年2月1日周三 下午5:28写道：

> On 2/1/17, Shaobo He  wrote:
> >
> > Basically, the error trace indicate that `sqlite3SrcListAppend` can
> return
> > a null pointer under the presence of OOM and this return value can
> > propagate to somewhere in the program, resulting in a null pointer
> > deference.
>
> An OOM condition inside of sqlite3SrcListAppend() will set the
> db->mallocFailed flag, which will cause the stack to unwind, and
> thereby prevent NULL pointer dereferences.
>
> Please repeat your experiment by forcing sqlite3SrcListAppend() to
> return NULL but at the same time set db->mallocFailed.  If you
> continue hit a segfault, that is an issue.  But I'm guessing you will
> not.
>
> We do a lot of OOM testing in SQLite.  See
> https://www.sqlite.org/testing.html#oomtesting for a summary of the
> technique.  There are 829 OOM test loops in TH3 and more in the TCL
> test suite.  We do, rarely, find OOM problems in release builds, but
> because of our OOM testing procedures such findings are quite rare.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 2/1/17, Shaobo He  wrote:
>
> Basically, the error trace indicate that `sqlite3SrcListAppend` can return
> a null pointer under the presence of OOM and this return value can
> propagate to somewhere in the program, resulting in a null pointer
> deference.

An OOM condition inside of sqlite3SrcListAppend() will set the
db->mallocFailed flag, which will cause the stack to unwind, and
thereby prevent NULL pointer dereferences.

Please repeat your experiment by forcing sqlite3SrcListAppend() to
return NULL but at the same time set db->mallocFailed.  If you
continue hit a segfault, that is an issue.  But I'm guessing you will
not.

We do a lot of OOM testing in SQLite.  See
https://www.sqlite.org/testing.html#oomtesting for a summary of the
technique.  There are 829 OOM test loops in TH3 and more in the TCL
test suite.  We do, rarely, find OOM problems in release builds, but
because of our OOM testing procedures such findings are quite rare.
-- 
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] Help with confirming a couple of error traces

[Shaobo He]

Hi Richard, all,

It's so nice of you to help out. Now we understand better what we should do
to reduce the number of false positives. Thanks a lot.

I'm sorry to bother you again. But it would be great if you could provide
some feedback on the new error trace returned by our tool.

Basically, the error trace indicate that `sqlite3SrcListAppend` can return
a null pointer under the presence of OOM and this return value can
propagate to somewhere in the program, resulting in a null pointer
deference. For instance, `targetSrcList` calls `sqlite3SrcListAppend` and
returns a null pointer if its callee does it, too. `codeTriggerProgram`
calls `sqlite3Insert` with a call expression to `targetSrcList` as its
second argument, which can be a null pointer following the deduction
before. Finally, its second argument `pTabList` gets dereferenced without a
null test.

I tried to do a dummy experiment by setting the return value of
`sqlite3DbMallocRawNN` inside `sqlite3SrcListAppend` to null and ran
regression tests. A number of them failed with segmentation fault. I don't
know if this experiment is meaningful or not.

Please let me know if it makes sense. Thanks for your time and I am looking
forward to your reply.

Shaobo
Richard Hipp 于2017年1月31日周二 下午9:41写道：

> On 1/31/17, Shaobo He  wrote:
> > Hi there,
> >
> > My name is Shaobo He and I am a graduate student at University of Utah. I
> > am applying a couple of static analysis tools to C projects. The tools I
> am
> > using reports a few partial error traces about null pointer
> dereferences. I
> > was wondering if you could help me to identify whether they (described
> > below) were true bugs or just false positives. Your feedback is really
> > appreciated.
>
> They are both false-positives.
>
> >
> > 1) In function `statGet`, `sqlite3_value_blob` can return a null pointer.
> > One possible case is that `ExpandBlob(p)` returns `SQLITE_OK` and the
> > condition expression `p->n ? p->z : 0;` evaluates to null given `p->n ==
> > 0`. I tried to figure out if `p->n` can be 0 by adding an assertion
> before
> > the call site to `sqlite_value_blob` and running all regression tests. It
> > seems it cannot be for these test cases. My question is that if the case
> > described above can happen. Moreover, function `statPush` has a similar
> > error trace.
>
> The first parameter to statGet() and statPush() will always be a
> sizeof(void*)-byte blob that is in fact a pointer to an object.  So
> sqlite3_value_blob() will never return NULL there.
>
> >
> > 2) In function `walCleanupHash`, `aHash` is initialized to null and is
> > probably updated by function `walHashGet`. However, the update may not
> > happen if `walIndexPage` returns a status not equal to `SQLITE_OK`. So
> > `aHash` remains null and got dereferenced.
>
> In walCleanupHash(), the pages of the -shm file that contains the hash
> have already been allocated and initialized - otherwise
> walCleanupHash() would have never been called.  But if the -shm file
> has already been allocated and initialized, then there is no way for
> walHashGet() to fail and leave aHash uninitialized.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with custom collation

[x]

Thanks Clemens. You’re right about changing the UTF8String* to char* as it now 
works but when trying it with a column containing Unicode characters it didn’t. 
I’d have liked to have tried it with windows wchar_t* type but If I try using



 if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, 
NULL, ) != SQLITE_OK)

 throw Exception("Collation creation error");



I get the message “no such collation sequence: Compare” when running the query.







From: Clemens Ladisch<mailto:clem...@ladisch.de>
Sent: 01 February 2017 17:32
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with custom collation



x wrote:
> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
> const UTF8String *S1 = static_cast(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
> ) != SQLITE_OK)
> throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
> the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
___
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] Help with custom collation

[Clemens Ladisch]

x wrote:
> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
> const UTF8String *S1 = static_cast(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
> ) != SQLITE_OK)
> throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
> the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


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

Re: [sqlite] Help with confirming a couple of error traces

[Richard Hipp]

On 1/31/17, Shaobo He  wrote:
> Hi there,
>
> My name is Shaobo He and I am a graduate student at University of Utah. I
> am applying a couple of static analysis tools to C projects. The tools I am
> using reports a few partial error traces about null pointer dereferences. I
> was wondering if you could help me to identify whether they (described
> below) were true bugs or just false positives. Your feedback is really
> appreciated.

They are both false-positives.

>
> 1) In function `statGet`, `sqlite3_value_blob` can return a null pointer.
> One possible case is that `ExpandBlob(p)` returns `SQLITE_OK` and the
> condition expression `p->n ? p->z : 0;` evaluates to null given `p->n ==
> 0`. I tried to figure out if `p->n` can be 0 by adding an assertion before
> the call site to `sqlite_value_blob` and running all regression tests. It
> seems it cannot be for these test cases. My question is that if the case
> described above can happen. Moreover, function `statPush` has a similar
> error trace.

The first parameter to statGet() and statPush() will always be a
sizeof(void*)-byte blob that is in fact a pointer to an object.  So
sqlite3_value_blob() will never return NULL there.

>
> 2) In function `walCleanupHash`, `aHash` is initialized to null and is
> probably updated by function `walHashGet`. However, the update may not
> happen if `walIndexPage` returns a status not equal to `SQLITE_OK`. So
> `aHash` remains null and got dereferenced.

In walCleanupHash(), the pages of the -shm file that contains the hash
have already been allocated and initialized - otherwise
walCleanupHash() would have never been called.  But if the -shm file
has already been allocated and initialized, then there is no way for
walHashGet() to fail and leave aHash uninitialized.

-- 
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] Help with assigning default values...

[jose isaias cabrera]

Thanks.  Yes, saw it.

-Original Message- 
From: Simon Slavin

Sent: Tuesday, December 20, 2016 11:54 PM
To: SQLite mailing list ; jic...@barrioinvi.net
Subject: Re: [sqlite] Help with assigning default values...


On 20 Dec 2016, at 9:37pm, jose isaias cabrera <jic...@barrioinvi.net> 
wrote:


Trying to learn or understand constraints.  Will you please look at the 
following...


Answers were posted to the mailing list.  If you’re not seeing them there’s 
something wrong with your subscription.


Simon. 


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

Re: [sqlite] Help with assigning default values...

[Simon Slavin]

On 20 Dec 2016, at 9:37pm, jose isaias cabrera  wrote:

> Trying to learn or understand constraints.  Will you please look at the 
> following...

Answers were posted to the mailing list.  If you’re not seeing them there’s 
something wrong with your subscription.

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

Re: [sqlite] Help Installing SQLite on a Windows 8.1 PC

[Simon Slavin]

On 6 Jul 2016, at 8:29pm, Krista M Whipple  wrote:

> I have downloaded the two 64-bit Windows files on my Windows 8.1 PC, but I
> cannot get SQLite to install on my PC.
> 
> 
> 
> Any help or directions would be greatly appreciated,

I understand the source of your confusion.  Unlike other database systems there 
is no 'SQLite installation'.  Each programmer who wants to use SQLite includes 
it in their program.  There's no central library, no SQLite server, and no 
place to look for configuration files.  Programs which use SQLite have 
everything they need inside the program and don't depend on any external files.

If you want to play with SQLite yourself without having to write your own 
software then you should download the 'Precompiled Binaries' from the web site. 
This includes a SQLITE3.EXE which is a command line shell which lets you issue 
your own SQL commands and see the results.  Just like other programs it doesn't 
depend on a library or server: everything it uses is compiled into the 
application.

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

Re: [sqlite] Help Installing SQLite on a Windows 8.1 PC

[Donald Griggs]

Hi Krista,

Regarding: I have downloaded the two 64-bit Windows files on my Windows 8.1
PC,

Are you referring to sqlite.exe and maybe sqlite.dll ?


Regarding:  but I cannot get SQLite to install on my PC.

I'm not sure I understand you.   Maybe you could rewrite this in form of
something like:

I did X and I expected Y, but instead I see Z.

Are you trying to install the command line standalone program, sqlite.exe?
  You just place it either in your current directory or somewhere in your
windows path.   I assume you're sure you have a 64-bit version of windows
installed.

Your fellow students and instructor should also be able to help I should
hope.

Donald

___
> 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] help with query

[snowbiwan]

Maybe something like this would work for you:

SELECT *
  FROM table
 WHERE data1 IN (SELECT data1
   FROM table
   GROUP BY data1
 HAVING count(*)>=3);

~snowbiwan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/help-with-query-tp79978p79979.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with query

[Keith Medcalf]

A correlated subquery:

select *
  from t
 where (select count(*)
  from t as b
 where b.data1 = t.data1) >= 3;

or with a subselected set of valid rows:

select *
  from t
 where data1 in (select data1
   from t as b
   group by data1
 having count(*) >= 3);


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Hajo Locke
>Sent: Tuesday, 13 January, 2015 00:30
>To: sqlite-users@sqlite.org
>Subject: [sqlite] help with query
>
>Hello list,
>
>i have a problem finding right query, hope you can help me.
>I have a sample table like this:  http://pastebin.com/8qyBzdhH
>I want to select all lines where at least 3 lines in column data1 have
>same value.
>My expected result-set ist this: http://pastebin.com/UcaXLVx9
>How can this be done in one query?
>I tested with something like this:
>SELECT *,count(*) as mycount FROM `table` group by data1 having
>mycount>=3;
>But this results in summarized output, but i need every single line.
>I would need something like: select * from `table` where count(data1)>3;
>But this is not allowed.
>Do you have any hints for me?
>
>Thanks,
>Hajo
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] help with query

[Hajo Locke]

Hello,

thanks a lot.  Works like a charm!
I should really do more sql.

Thanks,
Hajo


Am 13.01.2015 um 09:03 schrieb Hick Gunter:

Step 1: count the occurrences:

SELECT data1,count() AS count FROM table GROUP BY data1;

Step 2: get the rows with a count above the limit

SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3;

Step 3: get the keys from the rows

SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING 
count >= 3);

Step 4: retrieve the original rows

SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS 
count FROM table GROUP BY data1 HAVING count >= 3));

-Ursprüngliche Nachricht-
Von: Hajo Locke [mailto:hajo.lo...@gmx.de]
Gesendet: Dienstag, 13. Jänner 2015 08:30
An: sqlite-users@sqlite.org
Betreff: [sqlite] help with query

Hello list,

i have a problem finding right query, hope you can help me.
I have a sample table like this:  http://pastebin.com/8qyBzdhH I want to select 
all lines where at least 3 lines in column data1 have same value.
My expected result-set ist this: http://pastebin.com/UcaXLVx9 How can this be 
done in one query?
I tested with something like this:
SELECT *,count(*) as mycount FROM `table` group by data1 having mycount>=3; But 
this results in summarized output, but i need every single line.
I would need something like: select * from `table` where count(data1)>3; But 
this is not allowed.
Do you have any hints for me?

Thanks,
Hajo

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


___
  Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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


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

Re: [sqlite] help with query

[Hick Gunter]

Step 1: count the occurrences:

SELECT data1,count() AS count FROM table GROUP BY data1;

Step 2: get the rows with a count above the limit

SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3;

Step 3: get the keys from the rows

SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 
HAVING count >= 3);

Step 4: retrieve the original rows

SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS 
count FROM table GROUP BY data1 HAVING count >= 3));

-Ursprüngliche Nachricht-
Von: Hajo Locke [mailto:hajo.lo...@gmx.de]
Gesendet: Dienstag, 13. Jänner 2015 08:30
An: sqlite-users@sqlite.org
Betreff: [sqlite] help with query

Hello list,

i have a problem finding right query, hope you can help me.
I have a sample table like this:  http://pastebin.com/8qyBzdhH I want to select 
all lines where at least 3 lines in column data1 have same value.
My expected result-set ist this: http://pastebin.com/UcaXLVx9 How can this be 
done in one query?
I tested with something like this:
SELECT *,count(*) as mycount FROM `table` group by data1 having mycount>=3; But 
this results in summarized output, but i need every single line.
I would need something like: select * from `table` where count(data1)>3; But 
this is not allowed.
Do you have any hints for me?

Thanks,
Hajo

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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