[sqlite] vsv module documentation

[Keith Medcalf]

Simon,

I hope you don't mind me sending this to you directly, but what do you think of 
the following as the VSV documentation:

/*
** 2020-02-08 modified by Keith Medcalf who also disclaims all copyright
** on the modifications and hereby places this code in the public domain
**
** This file contains the implementation of an SQLite virtual table for
** reading VSV (Variably Separated Values), which are like CSV files,
** but subtly different.  VSV supports a number of extensions to the
** CSV format as well as more processing options.
**
**
** Usage:
**
**  create virtual table temp.vsv using vsv(...);
**  select * from vsv;
**
** The parameters to the vsv module (the vsv(...) part) are as follows:
**
**  filename=STRING the filename, passed to the Operating System
**  data=STRING alternative data
**  columns=N   columns parsed from the VSV file
**  header=BOOL whether or not a header row is present
**  skip=N  number of leading data rows to skip
**  rsep=STRING record separator
**  fsep=STRING field separator
**  validatetext=BOOL   validate UTF-8 encoding of text fields
**  affinity=AFFINITY   affinity to apply to each returned value
**
**
** Defaults:
**
**  filename / data nothing.  You must provide one or the other
**  it is an error to provide both or neither
**  schema  nothing.  If not provided then one will be
**  generated for you from the header, or if no
**  header is available then autogenerated using
**  field names manufactured as cX where X is the
**  column number
**  columns nothing.  If not specified then the number of
**  columns is determined by counting the fields
**  in the first record of the VSV file (which
**  will be the header row if header is specified),
**  the number of columns is not parsed from the
**  schema even if one is provided
**  header=no   no header row in the VSV file
**  skip=0  do not skip any data rows in the VSV file
**  fsep=','default field separator is a comma
**  rsep='\n'   default record separator is a newline
**  validatetext=no do not validate text field encoding
**  affinity=none   do not apply affinity to each returned value
**
**
** Parameter types:
**
**  STRING  means a quoted string
**  N   means a whole number not containing a sign
**  BOOLmeans something that evaluates as true or false
**  it is case insensitive
**  yes, no, true, false, 1, 0
**  AFFINITYmeans an SQLite3 type specification
**  it is case insensitive
**  none, blob, text, integer, real, numeric
**
** STRING means a quoted string.  The quote character may be either
** a single quote or a double quote.  Two quote characters in a row
** will be replaced with a single quote character.  STRINGS do not
** need to be quoted if it is obvious where they begin and end
** (that is, they do not contain a comma).  Leading and trailing
** spaces will be trimmed from unquoted strings.
**
**filename =./this/filename.here, ...
**filename =./this/filename.here , ...
**filename = ./this/filename.here, ...
**filename = ./this/filename.here , ...
**filename = './this/filename.here', ...
**filename = "./this/filename.here", ...
**
**  are all equivalent.
**
** BOOL defaults to true so the following specifications are all the
** same:
**
**  header = true
**  header = yes
**  header = 1
**  header
**
**
** Specific Parameters:
**
** The platform/compiler/OS fopen call is responsible for interpreting
** the filename.  It may contain anything recognized by the OS.
**
** The separator string containing exactly one character, or a valid
** escape sequence.  Recognized escape sequences are:
**
**  \t  horizontal tab, ascii character 9 (0x09)
**  \n  linefeed, ascii character 10 (0x0a)
**  \v  vertical tab, ascii character 11 (0x0b)
**  \f  form feed, ascii character 12 (0x0c)
**  \xhhspecific byte where hh is hexadecimal
**
** For the affinity setting, the following processing is applied to
** each value returned by the VSV virtual table:
**
**  noneno affinity is applied, all fields will be
**  returned as text just like in the original
**  csv module, embedded nulls will terminate
**  the text.  if validatetext is in effect then
**  an error will be thrown if the field does
**  not contain validly encoded text or contains
**  embedded nulls
**
**  bloball fields will be 

Re: [sqlite] Documentation improvement request

[Warren Young]

On Feb 8, 2020, at 11:56 AM, Simon Slavin  wrote:
> 
> ?  I don't know whether this can be done automatically.  Possibly for any 
> page which has section headers.

Yes, it can be done, and I’ve lined out several options for doing so here:

https://www.fossil-scm.org/forum/forumpost/a5e1391eb3

Someone now needs to decide this is their itch and teach Fossil how to do one 
of these things.

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

[sqlite] Documentation improvement request

[Simon Slavin]

Some pages in the documentation started small but had stuff gradually added to 
them over the years.  For the pages




can a "Table Of Contents" be added at the top, like the one on



?  I don't know whether this can be done automatically.  Possibly for any page 
which has section headers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Simon Slavin]

On 8 Feb 2020, at 6:25pm, Andy KU7T  wrote:

> IF isnull(version) THEN
> -- create table, indexes etc.
> 
> ELSE
> -- make incremental changes
> 
> END IF

You're going to have to give up the idea that you can do all this in one 
complicated SQL command.  You're going to end up doing a lot of

1) use your software to execute SQL command
2) use your software to check the result and decide what to do

For instance you can't do the enclosing if checking the version number.  You 
have to do that in your software.

There is a form of of CREATE TABLE which goes

CREATE TABLE IF NOT EXISTS …

which might help.  Also SQL does have an 'IF' construction, but can't be used 
to make different changes, it is used to return different values.  Look for 
"The CASE expression" on this page:


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

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Andy KU7T]

Ok, your reasoning sounds good.

I am struggling to come up with a SQL statement that runs a few expressions 
conditionally by a book expression. Can you help me get going?

Something like:

IF isnull(version) THEN
-- create table, indexes etc.

ELSE
-- make incremental changes

END IF


I'm getting a syntax error 'near IF'

Thanks
Andy
Sent from my T-Mobile 4G LTE Device
Get Outlook for Android

From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Saturday, February 8, 2020 9:42:25 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

On 8 Feb 2020, at 3:10pm, Andy KU7T  wrote:

> I'm looking for best practices on when and where to create/initialize your 
> tables when using SqlLite.Net as well as how to properly handle schema 
> changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and 
convert tables.  That way anyone who knows SQL will understand what you're 
doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which 
is ideal for that:



You can store whatever integer you like in there and recall it whenever you 
want.  Put a 1 in there for the first version of your program.  If you ever 
need to make schema changes, start using it to keep track of your schema 
versions.

If you would prefer not to use a SQLite-only facility, you might create a 
configuration table, with key and value rows, and have a row of that table 
indicate your schema version.
___
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] SqlLite.Net: Tables creation and schema changes strategies

[Simon Slavin]

On 8 Feb 2020, at 3:10pm, Andy KU7T  wrote:

> I'm looking for best practices on when and where to create/initialize your 
> tables when using SqlLite.Net as well as how to properly handle schema 
> changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and 
convert tables.  That way anyone who knows SQL will understand what you're 
doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which 
is ideal for that:



You can store whatever integer you like in there and recall it whenever you 
want.  Put a 1 in there for the first version of your program.  If you ever 
need to make schema changes, start using it to keep track of your schema 
versions.

If you would prefer not to use a SQLite-only facility, you might create a 
configuration table, with key and value rows, and have a row of that table 
indicate your schema version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Andy KU7T]

Hello,

I'm looking for best practices on when and where to create/initialize your 
tables when using SqlLite.Net as well as how to properly handle schema changes 
from one version to another.

It seems I could roll my own using large SQL files or use some library that can 
do the updates for me…
I have found some code on the internet but I do not seem to have the right 
references:


Core.Instance.dbConn = new SQLiteConnection (Core.Instance.DatabaseName, true);

Core.Instance.dbConn.CreateTable ();

Where can I find the CreateTable, and is it re-entrant, and so it can run on 
every launch of my program?  Any help is appreciated.

Thanks
Andy

Sent from Mail for Windows 10

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

[sqlite] gmane.comp.db.sqlite.general

[Wolfgang Enzinger]

Hi,

for those of us who - like me - prefer to access this mailing list via
NNTP: the Newsserver's address has changed from

news.gmane.org

to

news.gmane.io

(sorry if this is old news; I couldn't find any information about that
here.)

Wolfgang

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

Re: [sqlite] case-insensitivity of keywords are hardly documented

[Jean-Luc Hainaut]

On 7/02/2020 20:08, Aapo Rantalainen wrote:

Hi, I'm just doing some 'software archeology' and I found that:
on Nov 21 01:02:00 2004
FossilOrigin-Name: ac72a1d5518f7b505ae2a1bd3be3d71db461ae7e
git: f8565825622a1ed48bdaa835968a1137b2ffa593

This sentence have been dropped out of documentation:
"Keyword matching in SQLite is case-insensitive."

https://github.com/sqlite/sqlite/commit/f8565825622a1ed48bdaa835968a1137b2ffa593#diff-b43337792fa9656f4e2ae1351e18bee6L1556

Can anybody say what happened at that time? This sentence seems to not
been there ever since. Just by using SQLite man can say that keywords
are case-insensitive, but why it is not documented explicitly anymore?


Keyword case-insensitivity is a requirement of the SQL standard(s), not 
an SQLite feature. One can suppose that removing this from SQLite info 
was just a way to remove noise from SQLite documentation.


J-L Hainaut

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