[sqlite] Can you enable Recursive Triggers via the connection string?

[Joe Mistachkin]

If you are using System.Data.SQLite, then this feature has been added and will 
be available in version 1.0.98.0, due out this month (August).

Sent from my iPhone

> On Aug 1, 2015, at 1:47 AM, Mark A. Donohoe  
> wrote:
> 
> I hope this is the right address to send my questions to.
> 
> I found this mailing list on the SQLite.org site under the 
> ?Support? section.
> 
> In short, can you enable Recursive Triggers via the connection string?
> 
> I?ve read in some places that you can specify PRAGMA statements on the 
> connection string, but that doesn?t appear to be the case, at least not as 
> far as Recursive Triggers go.
> 
> The problem is we have logic that depends on them, but we?re using frameworks 
> which open and close the connections automatically so it?s not easy for us to 
> manually execute the PRAGMA statement at that time.
> 
> Here?s my question on StackOverflow.com regarding 
> it.
> 
> http://stackoverflow.com/questions/30457501/in-sqlite-can-you-set-the-recursive-triggers-pragma-via-the-connection-string
> 
> As you can see, I haven?t received a single comment in over a month, let 
> alone an answer:
> 
> I?m starting to think our only course of action would be to manually 
> recompile the DLL but that seems like it would be opening a giant can of 
> worms for a simple feature.
> 
> Thanks in advance for your help.  I?m hoping this is a simple ?Yes, here?s 
> how? or ?No, it can?t be done.?
> 
> Mark
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

[sqlite] CSV excel import

[R.Smith]

On 2015-08-01 09:28 PM, Igor Tandetnik wrote:
> On 8/1/2015 12:38 PM, R.Smith wrote:
>> if I have this csv line, what values must the parser end up with?:
>>
>> 1, "2", "3" 4, 5 "6", 7
>
> This is not a valid line of CSV, at least not as specified in RFC 
> 4180. Therefore, RFC 4180-conforming parsers may differ in their 
> interpretation of this line. There is no particular set of values that 
> the parser "must" end up with, assuming you use the word "must" with 
> the meaning specified in RFC 2119.

Indeed so. The RFC calls for values to be "enclosed" by double quotes, 
or not. It does not specify this kind of dual value, which isn't valid 
csv (if you expect a specific result from another csv parser, that is.)

There is however no call to ignore it, and so made a great meta-data 
opportunity for me in the past. This kind of CSV was completely valid 
and would get parsed while simply ignoring the parts after the quotes:

V1, V2,  V3,   V4
1 , "John" STR,  "" NULL,  4
2 , "" NULL, "42" INT, 4
3 , "James" STR, "Smith" STR,  7

You get the idea... I could parse it with one tool and retrieve the 
"metadata", and it would  still work in Excel and other csv parsers who 
simply ignored any bits after the closing quote and before the comma.

Lately though, Excel has changed its ways and actually will input the 
entire bit between commas as if a string. So this trick of mine has 
become useless, but just an interesting digression since csv's flaws 
been brought up.

[sqlite] CSV excel import

[Jean-Christophe Deschamps]

At 18:38 01/08/2015, you wrote:

>Nobody mentions it because it is as irrelevant as bemoaning the fact 
>that CSV cannot store lawn-chairs or Java objects. It wasn't intended 
>to do so.

Exactly. All I mean is that with only very few additional strict rules 
it can be changed into a basic type compliant vehicle able to reliably 
transfer data between many applications, including SQLite. A 
spreadsheet internally differentiates between a string, a number and an 
empty cell, for every cell, so the text output format should clearly do 
the same, unambiguously IMHO.

>   Neither, for that matter, does it store Integers or Reals as you go 
> on to mention - It is completely typeless (moreso than SQLite).

You know very well that SQLite is far from being typeless. What you put 
in you get out thru the kaleidoscope of affinities. Its storage classes 
are perfectly defined and obey precise rules.

>  It stores only one single thing: Strings. It has only one single 
> guide: How to correctly add /the string/ to a row and column and how 
> to read it back. How you interpret those strung-together characters 
> is up to the composer/decomposer (as Simon mentioned) - the CSV 
> standard has no feelings about it.

True, but the issue with most variants of CSV files floating around is 
just that: it's the reader to decide if 12345.4890E-13 is a float or a 
string (for instance the reference of some item by a given supplier.) 
By forcing string delimiters, you gain at least SQLite type resilience 
in/out. If you follow the RFC by the letter and only use string 
delimiters when a string actually contains the delimiter, you loose the 
capacity to unambiguously determine basic types.

By adopting a set of simple rules, you can reliably import/export data 
blindly into/from SQL (and outside as well) without loosing basic type 
information even at the individual field level. Of course here I mean 
all SQLite datatypes, which is what we're talking about in this list.

>For extra fun - How must a value that are both in and not in quotes be 
>interpreted? i.e if I have this csv line, what values must the parser 
>end up with?:
>
>1, "2", "3" 4, 5 "6", 7
  ^ ??? (fixed font required)
An error there: this isn't valid CSV under all variants I know of. At 
the limit, the grammar can be enhanced (or bastardized?) to allow for 4 
being considered a comment, but I never had any incentive to handle 
this kind of cosmetic variation. Even in that case, then "6" should 
also being seen as a comment but this is going far beyond what I (and 
most people) need and routinely use.


>i.e. You've made your own file specification using the CSV standard as 
>a kick-off point.

Exactly: I don't pretend to have invented warm water, just decided for 
an easy to implement variant which fixes most of the issues with CSV 
being initially type-blind.

Additionally, I enforce UTF8 no BOM encoding and allow unsignificant 
Unicode horizontal whitespaces before and after field separators as 
well as at begin and end of line.

Obviously if one needs to use a format offering more complete 
semantics, then JSON or XML are there for use, albeit at significantly 
higher cost.

[sqlite] CSV excel import

[Simon Slavin]

On 1 Aug 2015, at 11:11pm, Jean-Christophe Deschamps  
wrote:

> At 18:38 01/08/2015, you wrote:
> 
>> Nobody mentions it because it is as irrelevant as bemoaning the fact that 
>> CSV cannot store lawn-chairs or Java objects. It wasn't intended to do so.
> 
> Exactly. All I mean is that with only very few additional strict rules it can 
> be changed into a basic type compliant vehicle able to reliably transfer data 
> between many applications, including SQLite.

Why make any changes at all ?  Just decide that all values are in JSON format 
and you don't have to violate any standards.



Simon.