Re: [sqlite] Regarding CoC

[Wout Mertens]

On Oct 19, 2018 11:00 PM, "Roger Schlueter"  wrote:

There's no atheists/freethinkers at SQLite?


There's at least one (me), but they can simply ignore all the God-related
rules, easy.

It gets a bit harder for Hindus though, they have to mentally add "(your
favorite for this use case)" in front of every mention of God.

If the second half of 54 is observed, it would be a dreary world indeed.


Hmm yes, I did not take any of the asceticism rules seriously. I am and
will continue to be in frequent non-compliance of them.

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

Re: [sqlite] Regarding CoC

[Petite Abeille]

> On Oct 19, 2018, at 11:05 PM, Wout Mertens  wrote:
> 
>  I can live with that.

Yes, let’s get along for once.

In the memorable words of President Dale:

"Why can't we work out our differences? Why can't we work things out? Little 
forum people, why can't we all just get along?”
Mars Attacks! (1996)

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

Re: [sqlite] Regarding CoC

[Wout Mertens]

Well in the preface it does say that full compliance is near impossible and
not required.

I wouldn't call it antagonistic, it has many rules that are very inclusive
of anyone.

I must say it's a bit messy though, there are quite a few rules and not all
of them are nicely orthogonal. Most of them boil down to Bill and Ted's "Be
excellent to each other", with a dash of YOLO and a plea to maximize your
karma score. I can live with that.

Wout.

On Fri, Oct 19, 2018, 8:34 PM Scott Perry  wrote:

> I have to admit I was a lot more excited about the concept of SQLite
> having a Code of Conduct until I actually read it. Regardless of the fact
> that I seem to fail a great many of its provisions, it seems fairly deaf—if
> not antagonistic—to the issues of our times that have created demand for
> such documents.
>
> Scott
>
> On Oct 19, 2018, at 7:11 AM, Richard Hipp  wrote:
> >
> > On 10/19/18, Mantas Gridinas  wrote:
> >>
> >> I found code of conduct in documentation and I was wondering if it were
> >> true. Checking the version history it appears to have been added on
> >> 2018-02-22.
> >>
> >
> > Yes.  Clients were encouraging me to have a code of conduct.  (Having
> > a CoC seems to be a trendy thing nowadays.)  So I looked around and
> > came up with what you found, submitted the idea to the whole staff,
> > and everybody approved.
> >
> > --
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Roger Schlueter]

There's no atheists/freethinkers at SQLite?

If the second half of 54 is observed, it would be a dreary world indeed.

Roger


On 10/19/2018 10:30, Simon Slavin wrote:

On 19 Oct 2018, at 6:26pm, Andrew Brown  
wrote:


I looked it up, wondering what it would say, and I have to say, I love it.

Yeah, that's gonna magically appear on Hacker News within the month.  For those 
curious ...



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] geopoly data input options

[Noel Frankinet]

There a WKB and WKT (text) representation).
You can probably find everything : http://www.opengeospatial.org
Spatialite is also a good source
.


On Fri, 19 Oct 2018 at 22:47, Richard Hipp  wrote:

> On 10/19/18, Thomas Kurz  wrote:
> >> Beginning with the next release, polygons will always be stored in the
> >> binary format.
> >
> > Is the SQLite binary encoding identical to the Well-Known-Binary geometry
> > format?
>
> That might have happened, except the WKB format was not known to me...
> Where can I find information about WKB?
>
> --
> 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
>


-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] geopoly data input options

[Richard Hipp]

On 10/19/18, Thomas Kurz  wrote:
>> Beginning with the next release, polygons will always be stored in the
>> binary format.
>
> Is the SQLite binary encoding identical to the Well-Known-Binary geometry
> format?

That might have happened, except the WKB format was not known to me...
Where can I find information about WKB?

-- 
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] clarification of the .binary option of the sqlite3 command shell program.

[Graham Hardman]

thanks for that. I had forgotten about taking a look at the source code. 
All clear now.

Graham

On 20-10-2018 12:29 am, Jay Kreibich wrote:
On Oct 19, 2018, at 3:30 AM, Graham Hardman  
wrote:


Hi,

I am curious about what this option does. It is not discussed in the
documentation and my own testing with the shell program has not aided 
my

understanding. A simple example would be nice.



It controls how data is output to files/stdout.

Only relevant on Windows; does absolutely nothing on all other 
platforms.


sqlite-amalgamation-3250200/shell.c:200:

/* On Windows, we normally run with output mode of TEXT so that \n 
characters
** are automatically translated into \r\n.  However, this behavior 
needs
** to be disabled in some cases (ex: when generating CSV output and 
when

** rendering quoted strings that contain \n characters).  The following
** routines take care of that.
*/
#if defined(_WIN32) || defined(WIN32)
static void setBinaryMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_BINARY);
}
static void setTextMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_TEXT);
}
#else
# define setBinaryMode(X,Y)
# define setTextMode(X,Y)
#endif







regards,

Graham
___
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] Regarding CoC

[Mantas Gridinas]

How come it hadn't yet?

On Fri, Oct 19, 2018, 20:30 Simon Slavin  wrote:

> On 19 Oct 2018, at 6:26pm, Andrew Brown 
> wrote:
>
> > I looked it up, wondering what it would say, and I have to say, I love
> it.
>
> Yeah, that's gonna magically appear on Hacker News within the month.  For
> those curious ...
>
> 
>
> 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] geopoly data input options

[Graham Hardman]

Thanks very much for your prompt reply.
I had seen that I could use the geopoly_blob function to copy a 
json-formatted entry into a new record without getting an error from 
sqlite, however I was disconcerted when seeing that a select _shape from 
newtab (using example from documentation) displayed the first record as 
the json string and the second record as a blob (ie []).


The news about the change in next release is all good.  Thanks again.

regards,
Graham

On 20-10-2018 3:14 am, Richard Hipp wrote:

On 10/19/18, Graham Hardman  wrote:


My question now is : must I always format my list of vertices as text 
(

in json format ) as shown in the documentation.


No.  Polygons can be either in the GeoJSON format as text, or in a
binary format 
(https://www.sqlite.org/geopoly.html#binary_encoding_of_polygons).

Any routine that accepts a polygon can accept either format.

Beginning with the next release, polygons will always be stored in the
binary format.  In the previous release, polygons were stored in
whatever format was used to insert them.  But experience shows that
the binary format is smaller and faster.

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

Re: [sqlite] geopoly data input options

[Thomas Kurz]

Hmm... this seems to be different from WKB format. I was hoping that the 
geopoly extension would be compatible to established formats such as Spatialite.


- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Friday, October 19, 2018, 21:05:02
Subject: [sqlite] geopoly data input options


From the source:

/*
** Internal representation of a polygon.
**
** The polygon consists of a sequence of vertexes.  There is a line
** segment between each pair of vertexes, and one final segment from
** the last vertex back to the first.  (This differs from the GeoJSON
** standard in which the final vertex is a repeat of the first.)
**
** The polygon follows the right-hand rule.  The area to the right of
** each segment is "outside" and the area to the left is "inside".
**
** The on-disk representation consists of a 4-byte header followed by
** the values.  The 4-byte header is:
**
**  encoding(1 byte)   0=big-endian, 1=little-endian
**  nvertex (3 bytes)  Number of vertexes as a big-endian integer
**
** Enough space is allocated for 4 coordinates, to work around over-zealous
** warnings coming from some compiler (notably, clang). In reality, the size
** of each GeoPoly memory allocate is adjusted as necessary so that the
** GeoPoly.a[] array at the end is the appropriate size.
*/

typedef struct GeoPoly GeoPoly;
struct GeoPoly {
  int nVertex;  /* Number of vertexes */
  unsigned char hdr[4]; /* Header for on-disk representation */
  GeoCoord a[8];/* 2*nVertex values. X (longitude) first, then Y */
};

---
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 Thomas Kurz
>Sent: Friday, 19 October, 2018 09:17
>To: SQLite mailing list
>Subject: Re: [sqlite] geopoly data input options

>> Beginning with the next release, polygons will always be stored in
>the
>> binary format.

>Is the SQLite binary encoding identical to the Well-Known-Binary
>geometry format?

>___
>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] geopoly data input options

[Keith Medcalf]

From the source:

/*
** Internal representation of a polygon.
**
** The polygon consists of a sequence of vertexes.  There is a line
** segment between each pair of vertexes, and one final segment from
** the last vertex back to the first.  (This differs from the GeoJSON
** standard in which the final vertex is a repeat of the first.)
**
** The polygon follows the right-hand rule.  The area to the right of
** each segment is "outside" and the area to the left is "inside".
**
** The on-disk representation consists of a 4-byte header followed by
** the values.  The 4-byte header is:
**
**  encoding(1 byte)   0=big-endian, 1=little-endian
**  nvertex (3 bytes)  Number of vertexes as a big-endian integer
**
** Enough space is allocated for 4 coordinates, to work around over-zealous
** warnings coming from some compiler (notably, clang). In reality, the size
** of each GeoPoly memory allocate is adjusted as necessary so that the
** GeoPoly.a[] array at the end is the appropriate size.
*/

typedef struct GeoPoly GeoPoly;
struct GeoPoly {
  int nVertex;  /* Number of vertexes */
  unsigned char hdr[4]; /* Header for on-disk representation */
  GeoCoord a[8];/* 2*nVertex values. X (longitude) first, then Y */
};

---
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 Thomas Kurz
>Sent: Friday, 19 October, 2018 09:17
>To: SQLite mailing list
>Subject: Re: [sqlite] geopoly data input options
>
>> Beginning with the next release, polygons will always be stored in
>the
>> binary format.
>
>Is the SQLite binary encoding identical to the Well-Known-Binary
>geometry format?
>
>___
>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] Regarding CoC

[Dave Waters]

As if I needed another reason for SQLite to be my favorite database.  Well
done.

On Fri, Oct 19, 2018 at 10:11 AM Richard Hipp  wrote:

> On 10/19/18, Mantas Gridinas  wrote:
> >
> > I found code of conduct in documentation and I was wondering if it were
> > true. Checking the version history it appears to have been added on
> > 2018-02-22.
> >
>
> Yes.  Clients were encouraging me to have a code of conduct.  (Having
> a CoC seems to be a trendy thing nowadays.)  So I looked around and
> came up with what you found, submitted the idea to the whole staff,
> and everybody approved.
>
> --
> 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
>


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

Re: [sqlite] Regarding CoC

[Scott Perry]

I have to admit I was a lot more excited about the concept of SQLite having a 
Code of Conduct until I actually read it. Regardless of the fact that I seem to 
fail a great many of its provisions, it seems fairly deaf—if not 
antagonistic—to the issues of our times that have created demand for such 
documents.

Scott

On Oct 19, 2018, at 7:11 AM, Richard Hipp  wrote:
> 
> On 10/19/18, Mantas Gridinas  wrote:
>> 
>> I found code of conduct in documentation and I was wondering if it were
>> true. Checking the version history it appears to have been added on
>> 2018-02-22.
>> 
> 
> Yes.  Clients were encouraging me to have a code of conduct.  (Having
> a CoC seems to be a trendy thing nowadays.)  So I looked around and
> came up with what you found, submitted the idea to the whole staff,
> and everybody approved.
> 
> -- 
> 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] Regarding CoC

[Simon Slavin]

On 19 Oct 2018, at 6:26pm, Andrew Brown  
wrote:

> I looked it up, wondering what it would say, and I have to say, I love it.

Yeah, that's gonna magically appear on Hacker News within the month.  For those 
curious ...



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

Re: [sqlite] Regarding CoC

[Andrew Brown]

I looked it up, wondering what it would say, and I have to say, I love it.

On Fri, Oct 19, 2018 at 7:11 AM Richard Hipp  wrote:

> On 10/19/18, Mantas Gridinas  wrote:
> >
> > I found code of conduct in documentation and I was wondering if it were
> > true. Checking the version history it appears to have been added on
> > 2018-02-22.
> >
>
> Yes.  Clients were encouraging me to have a code of conduct.  (Having
> a CoC seems to be a trendy thing nowadays.)  So I looked around and
> came up with what you found, submitted the idea to the whole staff,
> and everybody approved.
>
> --
> 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
>
-- 
Andrew Brown
Senior Software Engineer, Data Delivery
[image: http://www.economicmodeling.com/wp-content/uploads/2016/05/logo.png]
Cell: 208-301-3354
www.economicmodeling.com 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A couple of questions about prepared statements

[Tim Streater]

On 19 Oct 2018, at 17:33, Simon Slavin  wrote:

> On 19 Oct 2018, at 3:43pm, Tim Streater  wrote:
>
>>  is it OK to generate the myid and goodtext parts using the usual string
>> methods from my host language, leaving only badtext as a bound variable, so
>> that my prepared statement looks like this:
>> 
>>  select somecol from mytable where myid=3 and badtext=? and
>> goodtext="somegoodtext"
>
> That will work fine.

Good.

> Just a note that your string quoting characters are wrong (this
> may have been done by your email client).  They should both be
> apostrophes:
>
> select somecol from mytable
> where myid=3 and badtext=? and goodtext='somegoodtext'

No, that was my fault. Not awake enough yet.

> Presumably you will 
> CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext)
>
>>  My other question relates to when the database is actually touched. Is it
>> the case that statement preparation and variable binding do not affect the
>> database itself and it's only when the prepared statement is actually
>> executed that the database is touched and might generate an SQLITE_BUSY
>> response?
>
> The database needs to be read, but not written, during _prepare_v2().  It
> won't be written until _step().  Depending on your journal mode, it's possible
> that _prepare() will be blocked because another connection is making a change.
>
> However, proper use of _timeout() will mean you will not care about either of
> the above.

OK - I'll bear all that in mind - thanks.



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

Re: [sqlite] Upsert syntax question

[David Raymond]

But isn't that the whole point of the whole UPSERT thing?

https://www.sqlite.org/lang_UPSERT.html

...

Ok, found my problem though
https://www.sqlite.org/lang_insert.html

To avoid a parsing ambiguity, the SELECT statement should always contain a 
WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause 
is present. Without the WHERE clause, the parser does not know if the token 
"ON" is part of a join constraint on the SELECT, or the beginning of the 
upsert-clause.


sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new where 
true on conflict (id) do update set in_new = 1;
QUERY PLAN
|--SCAN TABLE gc_new USING COVERING INDEX idx_gc_new_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 30.066 user 14.757695 sys 2.667617



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, October 19, 2018 12:36 PM
To: SQLite mailing list
Subject: Re: [sqlite] Upsert syntax question

On 19 Oct 2018, at 5:24pm, David Raymond  wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
> conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / 
FAIL / ABORT / ROLLBACK / REPLACE.



If you need anything more sophisticated, you might like to make a TRIGGER.

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] Upsert syntax question

[Simon Slavin]

On 19 Oct 2018, at 5:24pm, David Raymond  wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
> conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / 
FAIL / ABORT / ROLLBACK / REPLACE.



If you need anything more sophisticated, you might like to make a TRIGGER.

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

Re: [sqlite] A couple of questions about prepared statements

[Simon Slavin]

On 19 Oct 2018, at 3:43pm, Tim Streater  wrote:

> is it OK to generate the myid and goodtext parts using the usual string 
> methods from my host language, leaving only badtext as a bound variable, so 
> that my prepared statement looks like this:
> 
> select somecol from mytable where myid=3 and badtext=? and 
> goodtext="somegoodtext"

That will work fine.  Just a note that your string quoting characters are wrong 
(this may have been done by your email client).  They should both be 
apostrophes:

select somecol from mytable
where myid=3 and badtext=? and goodtext='somegoodtext'

Presumably you will 
CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext)

> My other question relates to when the database is actually touched. Is it the 
> case that statement preparation and variable binding do not affect the 
> database itself and it's only when the prepared statement is actually 
> executed that the database is touched and might generate an SQLITE_BUSY 
> response?

The database needs to be read, but not written, during _prepare_v2().  It won't 
be written until _step().  Depending on your journal mode, it's possible that 
_prepare() will be blocked because another connection is making a change.

However, proper use of _timeout() will mean you will not care about either of 
the above.

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

[sqlite] Upsert syntax question

[David Raymond]

Maybe it's just Friday, but I can't figure out what I'm messing up here. Would 
some kind soul point me to where I'm messing up?

Version 3.25.2

sqlite> create table ids (id int primary key, in_old bool, in_new bool);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.234 user 0.00 sys 0.109201

sqlite> insert into ids (id, in_old) select distinct id, 1 from gc_old;
QUERY PLAN
|--SCAN TABLE gc_old USING COVERING INDEX idx_gc_old_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 18.050 user 10.530067 sys 1.279208

sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
conflict (id) do update set in_new = 1;
Run Time: real 0.000 user 0.00 sys 0.00
Error: near "do": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A couple of questions about prepared statements

[J. King]

On 2018-10-19 10:43:21, "Tim Streater"  wrote:


Suppose I have an SQL statement like this:

select somecol from mytable where myid=3 and badtext="usertext" and 
goodtext="somegoodtext"


Suppose further that the values of myid and goodtext are generated by 
reliably me whereas badtext is supplied externally. If I want to use a 
prepared statement here, is it OK to generate the myid and goodtext 
parts using the usual string methods from my host language, leaving 
only badtext as a bound variable, so that my prepared statement looks 
like this:


select somecol from mytable where myid=3 and badtext=? and 
goodtext="somegoodtext"


That would simplify my life.
It might complicate your life later. If your "safe" data gets mixed with 
unsafe data somewhere that you're not aware of (or it changes in a later 
version of your application), you're opening yourself up to stealth 
bugs. Binding everything reduces guesswork and long-term maintenance.


Also, statements have a maximum size: if your embedded information is 
large (or later becomes large), you can run into problems there, too.


Note, too, that the correct way to quote string is with single-quote 
characters. SQLite will accept double-quotes when it's unambiguous, but 
it's a good habit to get out of, for the ambiguous cases.




My other question relates to when the database is actually touched. Is 
it the case that statement preparation and variable binding do not 
affect the database itself and it's only when the prepared statement is 
actually executed that the database is touched and might generate an 
SQLITE_BUSY response?


When the transaction is committed. If you've not explicitly begun a 
transaction, then when the statement is executed, yes.


--
J. King

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

Re: [sqlite] geopoly data input options

[Thomas Kurz]

> Beginning with the next release, polygons will always be stored in the
> binary format.

Is the SQLite binary encoding identical to the Well-Known-Binary geometry 
format?

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

Re: [sqlite] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Stephen Chrzanowski]

I suppose you COULD get rid of the \n in the query  Its a Linux thing.
*sigh*

On Fri, Oct 19, 2018 at 10:50 AM Stephen Chrzanowski 
wrote:

> This would be something you'd need to write something quick for.  There's
> no application in the world that knows how to use your specific schema and
> react to it.  There may be apps that will read the values and automatically
> 'understand' that http:// and https:// are addresses, but, these probably
> are custom built applications that do something completely different to
> what you're doing. I've just checked with SQLite Expert, and it doesn't
> have any knowledge of how to treat a field as a URL/URI.
>
> That said, you may be able to get an ugly query going, and dump it to an
> HTML file you can bring up in your browser.
>
> My example table:
> CREATE TABLE [Test](  [TestID] INTEGER,   [Name] CHAR,   [Address] URL);
> INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(1, 'Google', '
> http://www.google.com');
> INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(2, 'YouTube', '
> http://www.youtube.com');
>
> Then execute SQLite3.exe like this:
>
> R:\>c:\myapps\sqlite3.exe
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open TestDB.db3
> sqlite> .output test.html
> sqlite> select ''||Name||'\n' from Test;
> sqlite> .quit
>
> R:\>type test.html
> http://www.google.com;>Google\n
> http://www.youtube.com;>YouTube\n
>
> (R: for me is a RamDRIVE)
>
>
>
> On Fri, Oct 19, 2018 at 10:03 AM Winfried  wrote:
>
>> Hello,
>>
>> I have a bunch of hyperlinks in an SQLite database, and need to read each
>> page for validation before deleting the record.
>> To make it easier, the DB manager should launch the default web browser
>> when
>> I double click on a column that contains a hyperlink.
>>
>> Before I build a GUI, is there a Windows SQLite database manager that can
>> do
>> this?
>>
>> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none
>> seems
>> to support this feature.
>>
>> Thank you.
>>
>>
>>
>> --
>> Sent from: http://sqlite.1065341.n5.nabble.com/
>> ___
>> 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] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Stephen Chrzanowski]

This would be something you'd need to write something quick for.  There's
no application in the world that knows how to use your specific schema and
react to it.  There may be apps that will read the values and automatically
'understand' that http:// and https:// are addresses, but, these probably
are custom built applications that do something completely different to
what you're doing. I've just checked with SQLite Expert, and it doesn't
have any knowledge of how to treat a field as a URL/URI.

That said, you may be able to get an ugly query going, and dump it to an
HTML file you can bring up in your browser.

My example table:
CREATE TABLE [Test](  [TestID] INTEGER,   [Name] CHAR,   [Address] URL);
INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(1, 'Google', '
http://www.google.com');
INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(2, 'YouTube', '
http://www.youtube.com');

Then execute SQLite3.exe like this:

R:\>c:\myapps\sqlite3.exe
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open TestDB.db3
sqlite> .output test.html
sqlite> select ''||Name||'\n' from Test;
sqlite> .quit

R:\>type test.html
http://www.google.com;>Google\n
http://www.youtube.com;>YouTube\n

(R: for me is a RamDRIVE)



On Fri, Oct 19, 2018 at 10:03 AM Winfried  wrote:

> Hello,
>
> I have a bunch of hyperlinks in an SQLite database, and need to read each
> page for validation before deleting the record.
> To make it easier, the DB manager should launch the default web browser
> when
> I double click on a column that contains a hyperlink.
>
> Before I build a GUI, is there a Windows SQLite database manager that can
> do
> this?
>
> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none
> seems
> to support this feature.
>
> Thank you.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] A couple of questions about prepared statements

[Tim Streater]

Suppose I have an SQL statement like this:

select somecol from mytable where myid=3 and badtext="usertext" and 
goodtext="somegoodtext"

Suppose further that the values of myid and goodtext are generated by reliably 
me whereas badtext is supplied externally. If I want to use a prepared 
statement here, is it OK to generate the myid and goodtext parts using the 
usual string methods from my host language, leaving only badtext as a bound 
variable, so that my prepared statement looks like this:

select somecol from mytable where myid=3 and badtext=? and 
goodtext="somegoodtext"

That would simplify my life.

My other question relates to when the database is actually touched. Is it the 
case that statement preparation and variable binding do not affect the database 
itself and it's only when the prepared statement is actually executed that the 
database is touched and might generate an SQLITE_BUSY response?

Thanks.


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

Re: [sqlite] geopoly data input options

[Richard Hipp]

On 10/19/18, Graham Hardman  wrote:
>
> My question now is : must I always format my list of vertices as text (
> in json format ) as shown in the documentation.

No.  Polygons can be either in the GeoJSON format as text, or in a
binary format (https://www.sqlite.org/geopoly.html#binary_encoding_of_polygons).
Any routine that accepts a polygon can accept either format.

Beginning with the next release, polygons will always be stored in the
binary format.  In the previous release, polygons were stored in
whatever format was used to insert them.  But experience shows that
the binary format is smaller and faster.

-- 
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] Regarding CoC

[Richard Hipp]

On 10/19/18, Mantas Gridinas  wrote:
>
> I found code of conduct in documentation and I was wondering if it were
> true. Checking the version history it appears to have been added on
> 2018-02-22.
>

Yes.  Clients were encouraging me to have a code of conduct.  (Having
a CoC seems to be a trendy thing nowadays.)  So I looked around and
came up with what you found, submitted the idea to the whole staff,
and everybody approved.

-- 
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] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Winfried]

Hello,

I have a bunch of hyperlinks in an SQLite database, and need to read each
page for validation before deleting the record.
To make it easier, the DB manager should launch the default web browser when
I double click on a column that contains a hyperlink.

Before I build a GUI, is there a Windows SQLite database manager that can do
this?

I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none seems
to support this feature.

Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regarding CoC

[Mantas Gridinas]

Hi!

I found code of conduct in documentation and I was wondering if it were
true. Checking the version history it appears to have been added on
2018-02-22.

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

Re: [sqlite] clarification of the .binary option of the sqlite3 command shell program.

[Jay Kreibich]

> On Oct 19, 2018, at 3:30 AM, Graham Hardman  wrote:
> 
> Hi, 
> 
> I am curious about what this option does. It is not discussed in the
> documentation and my own testing with the shell program has not aided my
> understanding. A simple example would be nice. 


It controls how data is output to files/stdout.

Only relevant on Windows; does absolutely nothing on all other platforms.

sqlite-amalgamation-3250200/shell.c:200:

/* On Windows, we normally run with output mode of TEXT so that \n characters
** are automatically translated into \r\n.  However, this behavior needs
** to be disabled in some cases (ex: when generating CSV output and when
** rendering quoted strings that contain \n characters).  The following
** routines take care of that.
*/
#if defined(_WIN32) || defined(WIN32)
static void setBinaryMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_BINARY);
}
static void setTextMode(FILE *file, int isOutput){
  if( isOutput ) fflush(file);
  _setmode(_fileno(file), _O_TEXT);
}
#else
# define setBinaryMode(X,Y)
# define setTextMode(X,Y)
#endif





> 
> regards, 
> 
> Graham
> ___
> 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] How to round to an Integer

[Rob Richardson]

I don't think the cast rounds, though.  It just truncates.  Am I wrong?

RobR

On Thu, Oct 18, 2018 at 4:13 PM Richard Hipp  wrote:

> On 10/18/18, John Harney  wrote:
> > Recently figured this out.  Seems to work fine
> >
> > trim(trim(round(1.111,0),'0'),'.')   = 1
> >
>
> CAST(1.111 AS integer)
>
> --
> 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

[sqlite] clarification of the .binary option of the sqlite3 command shell program.

[Graham Hardman]

Hi, 

I am curious about what this option does. It is not discussed in the
documentation and my own testing with the shell program has not aided my
understanding. A simple example would be nice. 

regards, 

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

[sqlite] geopoly data input options

[Graham Hardman]

Hi, 

this is my first post to this group so firstly: thanks for the
oportunity to be here. I have long been in awe of sqlite's power and
thankful of its open source nature. 

I see great possibilities for me to use the shape matching functions of
the geopoly module to extend an autocad vba program I have built up over
say 15 years that basically tags polyline entities (representing
rollers) then populates an access database with properties, automates
the detail drawings and also feeds data to a cadcam machining package to
make the parts. 

I have built special versions of the commandline shell and the dll
library and shown that my ideas will succeed very well. I will relocate
the polylines to the origin and looks for clones and mirror clones using
the geopoly_xform method' 

My question now is : must I always format my list of vertices as text (
in json format ) as shown in the documentation. Of course I can do this,
but it would seem to me better to pass a blob type object. 

i hope someone can answer this. 

regards, 

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

Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

[Clemens Ladisch]

Yuri wrote:
> I noticed that my DB import process is much slower when run on the DB on 
> disk, vs. in memory.

It's possible that you forgot to wrap a single transaction around all changes.
Otherwise, you get an automatic transaction for every command, which requires
disk synchronization every time.


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