[sqlite] Help with sqlite3TreeViewSelect

[x]

I’ve been using this while debugging by inserting the following code just 
before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to 
take the output from TreeView.txt and rebuild the query from that so I have a 
version of the query where I know which schema, table and column each ID token 
refers to. It looks fairly easy given the tables and columns are all numbered 
but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement 
but it also seems to incorporate the associated select(s) into the main SELECT 
such that the WITH statement can be effectively ignored. Is that always the 
case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT 
below that line is what was contained in the WITH statement)?

|-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
|-- result-set
|   |-- {0:-1}  flags=0x82
|   |-- {0:7}  flags=0x82
|   '-- IF-NULL-ROW 8
|   '-- SELECT-expr flags=0x2220800
|   '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
|   |-- result-set
|   |   '-- {9:1}  flags=0x82
|   |-- FROM
|   |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
|   |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
|   |-- WHERE
|   |   '-- AND
|   |   |-- EQ
|   |   |   |-- {9:-1}  flags=0x82
|   |   |   '-- 28
|   |   '-- EQ
|   |   |-- {9:-1}  flags=0x20008
|   |   |   '-- 28
|   |   '-- {11:-1}  flags=0x82
|   '-- LIMIT
|   '-- 1
|-- FROM
.

Also, is there any way I could redirect the stdout to a memory buffer rather 
than a file (I’m using clang compiler on windows)? It would be great if there 
was a function along the lines of sqlite3_normalized_sql(stmt) that returned 
the sqlite3TreeViewSelect text.

___
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 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

[sqlite] Help with loading .DAT files

[Pablo Boswell (US - ASR)]

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

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

[sqlite] Help with sqlite3_value_text

[x]

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

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

[sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

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

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

[sqlite] HELP!

[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

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

[sqlite] Help!

[am...@juno.com]

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

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

[sqlite] Help!

[am...@juno.com]

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

How To Remove Eye Bags & Lip Lines Fast (Watch)
Fit Mom Daily
http://thirdpartyoffers.juno.com/TGL3131/5bce42dea708f42de5ea5st03vuc
___
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

[sqlite] help, sqlite3_get_table return 21，

[W J]

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 */

the version information is follow:
#define SQLITE_VERSION"3.8.4.3"
#define SQLITE_VERSION_NUMBER 3008004
#define SQLITE_SOURCE_ID  "2014-04-03 16:53:12 
a611fa96c4a848614efe899130359c9f6fb889c3"

What is the real meaning of this error? How to correct it?

thanks very much for your reply.

___
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

[sqlite] Help!

[am...@juno.com]

 October 2, 2018 Dear Good People: While I have both SQLite DB and SQlite 
Studio on I my computer, I have not been able to export files from SQLite DB to 
SQLite Studio--so that I get all the database functions I need. This involves 
say putting a field in ascending order, filtering out information that I want 
via a search. 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. Thank you very much in advance for helping me out on this dilemma 
without charging me. Respectfully yours, Alex Stavis P.S. If you can answer me 
within one hour that would be great--as sometimes I get an overflow message 
after one hour.

1 Simple Trick Removes Eye Bags & Lip Lines in Seconds
ourhealthpros.com
http://thirdpartyoffers.juno.com/TGL3131/5bb3eacc20cc96acc25c5st01vuc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with ICU

[Jeremy Hill]

I used win-builds to install gcc 4.8.3 on windows 10.  I am now trying to
compile sqlite-amalgamation-324 with ICU enabled.

I receive the following:

C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa335a):
undefined reference to `u_errorName_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa366f):
undefined reference to `u_foldCase_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3681):
undefined reference to `u_foldCase_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa388f):
undefined reference to `utf8_nextCharSafeBody_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3909):
undefined reference to `uregex_close_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa39af):
undefined reference to `uregex_open_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3a10):
undefined reference to `uregex_setText_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3a47):
undefined reference to `uregex_matches_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3a87):
undefined reference to `uregex_setText_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3bea):
undefined reference to `u_strToUpper_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3c30):
undefined reference to `u_strToLower_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3cc3):
undefined reference to `ucol_close_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3d1e):
undefined reference to `ucol_strcoll_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3dc6):
undefined reference to `ucol_open_51'
C:\Users\Dev\AppData\Local\Temp\cckZbRTz.o:sqlite3.c:(.text+0xa3e33):
undefined reference to `ucol_close_51'

what am I missing?

Thank you!
___
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

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

[J Decker]

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

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

[sqlite] Help getting started

[Larry Mullings]

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?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with json1 query?

[Charles Leifer]

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

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

[sqlite] Help with row values

[x]

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

Surely it should be 3 in both cases?

If t is created as ‘create table t(a)’ both return 3.

Tried it in 3.21 and 3.22.
___
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

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

[e-mail]

This "view" works in "DB Browser for SQLite" to get my Firefox Bookmarks . . . 
I then export them to Desktop as a .CSV, and run an Excel macro on them . . .

SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -

But then I thought, why not use SQLite3 in a .BAT file, and make it "one-click" 
?

The following code is what I have put into a .BAT file - I don't see any 
errors, and both just create a blank .CSV on my Desktop.1.) Broken out, for 
examination
2.) Quotes and apostrophes - not sure what's necessary - it seems like quotes 
are probably necessary, if there's a space in the path
3.) Just has apostrophes - not sure what's necessary
Any idea why this isn't working?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv

"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"

"SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id"

> "C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"
 "SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > 
"C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite
 SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,'unixepoch','localtime') AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id > 
C:\Users\SLUDGE\Desktop\output.csv
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
___
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

[sqlite] help with EXPLAIN QUERY PLAN

[x]

Can anyone tell me why the detail column sometime states ‘USING INDEX ’ and 
other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only 
the first m columns of an n column index (m less than n) but I’ve also seen 
USING COVERING INDEX when the query was searching all of the columns in the 
index.

___
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

[sqlite] Help with left joins

[x]

Suppose

TblB has primary key ColB and contains a column ColBX
TblC has primary key ColC and contains a column ColCX
TblD has primary key ColD and contains a column ColDX

TblA has primary key ColA and also contains columns ColB, ColC and ColD

(i.e. TblB, TblC and TblD are effectively lookup tables for TblA).

If I run the following query

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.

If i run the following query

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

there will be no trace of TblD as it's redundant but what I need explaining is 
why is TblB included in the explain.

I'm guessing that the sqlite query optimiser finds it easy to ignore trailing 
redundant tables but checking for other redundant tables is too expensive. Is 
that the case or am I missing something and the inclusion of TblB is necessary 
to get the correct result?
___
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

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

[Ausama Majeed]

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

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

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 
<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

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

[sqlite] Help file has no information

[Rob Richardson]

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

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

[sqlite] Help with Backup API please

[Brett Goodman]

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

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