Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Drago, William @ CSG - NARDA-MITEQ]

I skimmed through this entire thread and didn't see any mention of 
System.Data.SQLite which is the ADO.NET provider for SQLite and 
written/maintained by the authors of SQLite.

http://system.data.sqlite.org/

Perhaps I missed it or perhaps it's not really what you are looking for. I 
thought I should at least mention it.

I use System.Data.SQLite with VEE and C#, and I use xerial/sqlite-jdbc with 
Java.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Thursday, January 07, 2016 7:23 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] Wish List for 2016: High Level API for Object Oriented
> Interactive Languages
>
> At the command line interface (CLI) in SQLite (and most SQL
> implementations) is an interpreted set at a time language with implicit loops.
>
> Efficient low level languages (such as C) process data a record at a time and
> the existing API is appropriate for them.
>
> Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View or
> a Query all at once as a data set.
> I would count among the OOIL languages: R, Python, Julia Scala,
> MatLab/Octave and APL. In a slightly different category would be Java and C#
> which are object oriented and arguably interpreted, but are not intended to
> be used interactively at a command line with a Read-Evaluate-Print-Loop
> (REPL).
>
> The intent of the higher level API is to improve the reliability of the
> interfaces. The existing SQLite APIs are correct, but hard to use in the sense
> that creating an interface from an OOIL language is more involved than just
> "wrapping" one by one a set of functions. What I am proposing is a second
> set of APIs that when trivially wrapped for use in an OOIL language would
> result in a function that makes sense to an OOIL programmer and interprets
> the SQL statements in a manner consistent with the SQLite CLI (perhaps it
> could even borrow code from the CLI).
>
> I believe R has remarkably good interface packages for SQLite, but that is not
> necessarily the norm across the other OOIL languages.
>
> I am assuming that the higher level API would be hard to use in C because its
> up to the programmer to write the low level code while maintaining a
> complex abstraction in their head (because C is better suited for creating
> abstractions than using them). Header files (.h) would help some but they
> would inflate the size of the code and still be hard for the C programmer to
> keep track of. So, that's why I see the need for a second higher API that
> might be written in C, but would certainly not be used in C!
>
> I am undecided as to whether the higher level API would be useful in Java or
> C#.  Java and C# programmers might not be used to implicit loops and find
> them not worth the trouble; whereas R, Python or Julia programer would
> expect to get an entire table, view or query all at once.
>
> The higher level API would have to be optional, since it would not be
> desirable for a programmer or organization that needs SQLite to run with the
> smallest possible footprint on a phone, tablet or Internet of things
> (IOT) device.
>
> Just a wishlist idea. No rush for me because I am happy in R and will probably
> be moving from SQLite to client server SQL database before I move from R to
> Python, Julia or Java.
>
> Jim Callahan
> Orlando, FL
>
>
> <https://www.avast.com/sig-
> email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> This
> email has been sent from a virus-free computer protected by Avast.
> www.avast.com
> <https://www.avast.com/sig-
> email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the

Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

On 2016-11-06 3:50 AM, vfclists . wrote:

On 8 January 2016 at 01:47, Stephen Chrzanowski  wrote:

I personally wish the reverse.  I wish that these interpreted language
engines would incorporate the SQLite code directly into their own existence
to avoid having to write wrappers to begin with, except for those wrappers
where their method name is "DatabaseOpen" and I prefer "OpenDatabase".

SQLite has been around for years, and "R", PHP, Java, Perl, and all these
other interpreted new and old style languages have never bothered to
incorporate this public domain database engine within itself.  It isn't
like the maintainers of these languages don't know it doesn't exist, and if
they didn't, then my god they gotta get out from under that rock.  Most web
browsers use SQLite for crying out loud.

For a few years, I've considered taking the entire amalgamation and porting
it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
worries about OBJ files, no worries about dependencies, I just include a
unit and my app is now database aware.  I know 386 assembly, and I can
always read up on other specifications if I needed to.  My problem is that
gaming gets in the way.

My 2016 wish list for SQLite is that all developers who write for, or use
directly or indirectly, any database engine out on the market has a safe
and happy 2016 and beyond.


Haven't the mORMot guys already done this -
http://synopse.info/fossil/wiki?name=SQLite3+Framework? I think they have a
means of compiling sqlite access directly into Delphi and Freepascal.


If anyone feels like replying to the quoted message or thread starter, dated 
2016 January 7 PST, please first look at the SQLite list archives for Jan 7-8 
first as a discussion already occurred then.  I personally think the subject has 
already run its course. -- Darren Duncan


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

Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[vfclists .]

On 8 January 2016 at 01:47, Stephen Chrzanowski  wrote:

> I personally wish the reverse.  I wish that these interpreted language
> engines would incorporate the SQLite code directly into their own existence
> to avoid having to write wrappers to begin with, except for those wrappers
> where their method name is "DatabaseOpen" and I prefer "OpenDatabase".
>
> SQLite has been around for years, and "R", PHP, Java, Perl, and all these
> other interpreted new and old style languages have never bothered to
> incorporate this public domain database engine within itself.  It isn't
> like the maintainers of these languages don't know it doesn't exist, and if
> they didn't, then my god they gotta get out from under that rock.  Most web
> browsers use SQLite for crying out loud.
>
> For a few years, I've considered taking the entire amalgamation and porting
> it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
> worries about OBJ files, no worries about dependencies, I just include a
> unit and my app is now database aware.  I know 386 assembly, and I can
> always read up on other specifications if I needed to.  My problem is that
> gaming gets in the way.
>
> My 2016 wish list for SQLite is that all developers who write for, or use
> directly or indirectly, any database engine out on the market has a safe
> and happy 2016 and beyond.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


Haven't the mORMot guys already done this -
http://synopse.info/fossil/wiki?name=SQLite3+Framework? I think they have a
means of compiling sqlite access directly into Delphi and Freepascal.

-- 
Frank Church

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

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Gabor Grothendieck]

> It is a standard feature of pysqlite2 (the sqlite3 library shipped with 
> Python).  I am quite sure you can read the documentation just as well as I 
> can copy and paste it.  In short, you can use anything you like as the data 
> type in sqlite.  You could specify that some column contains:
>
> create table Developers
> (
>   count sillyGooses integer not null
> );
>
> You have now defined this to be of type sillyGooses integer.  It will have 
> affinity integer.  WHen you read the column metadata you can find out that 
> count is of type sillygooses and multiply the returned value count by Pi/e if 
> that is the conversion factor required.
>
> THe person writing the interface can even do this magically for you -- just 
> like pysqlite / sqlite3 python libraries do.
>

It's hard to tell what you are suggesting without the full example I
asked for but given that

1. you are showing a create statement and
2. refering to the "person writing the interface"

I assume  you are suggesting that the user define the structure of the
output table sent from sqlite and do that either on the python or
sqlite side but that was what we were trying to avoid.

Of course it's possible (and easy to do in R too) if the user is
required to specify the output types on the sqlite or R side but in R
you don't have to -- it's all done automatically for you.  In the full
R example I showed there is no create statement or other statements to
define an the "interface" that I did not show yet it works in
databases that support date types but not in sqlite (unless you rely
on heuristics that the sqldf package provides).

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Keith Medcalf]

> > There is no impedence mismatch.  Simply inadequate wattage by the
> person(s) solving the problem.  As I said, this problem has been solved
> with SQLite and Python for a decade.  So I would suggest the problem is
> that the wattage was so low, the lights were completely out.

> The impedence in the example is that on the sqlite side the type is
> numeric and on the R side it is of Date class.  It is impossible to
> know which sqlite numeric types are intended to represent dates and
> which are intended to represent numbers so when they are sent back to
> R there is no automatic assignment to Date class.  Either the
> programmer has to specify it (which is tedious) or else one has to
> rely on heuristics such as assuming that any output field having the
> same name as input field must also have the same type (but such
> heuristics do not cover all cases).
> 
> If you believe that python can know that a date is being returned from
> sqlite without the programmer specifying it even though sqlite has no
> date class, I suggest providing a self contained reproducible example
> of python code to illustrate it as I did with R.

It is a standard feature of pysqlite2 (the sqlite3 library shipped with 
Python).  I am quite sure you can read the documentation just as well as I can 
copy and paste it.  In short, you can use anything you like as the data type in 
sqlite.  You could specify that some column contains:

create table Developers
(
  count sillyGooses integer not null
);

You have now defined this to be of type sillyGooses integer.  It will have 
affinity integer.  WHen you read the column metadata you can find out that 
count is of type sillygooses and multiply the returned value count by Pi/e if 
that is the conversion factor required.

THe person writing the interface can even do this magically for you -- just 
like pysqlite / sqlite3 python libraries do.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Gabor Grothendieck]

On Mon, Feb 1, 2016 at 8:27 AM, Keith Medcalf  wrote:
>
>> > But you already have pandas.read_sql_query.  While that function
>> > isn't really what I'd call simple, the complexity afaict -- dates,
>> > floats, and chunks -- can be laid at Python's feet.
>>
>> I use R rather than python but the problem of dates is significant and
>> I assume the same problem as in python.  Consider this query:
>>
>>  select myDate + 1 from myTable
>>
>> Assume that myTable comes from an R data frame with a myDate column
>> that has R class of "Date".  Internally R stores the myDate column as
>> days since 1970-01-01 and since SQLite has no Date class it sends that
>> number to SQLite.  The select statement then adds 1 giving a number
>> representing the next day but when one gets it back to R how can R
>> know that that number is intended to represent a Date and so convert
>> it to R's Date class?  In fact it can't.   With databases that have a
>> Date type this is not a problem but it is a significant problem with
>> SQLite.
>
> What is the problem exactly?  Sounds like the R thingy is broken.  I do this 
> all the time using Python.  You simply have to compile the library with 
> column_metadata and then use it.  Sheesh.  Even pysqlite has done this just 
> peachyu fine for about a decade.
>

> There are some hackish workarounds.  For example, consider this
>> self-contained reproducible R code:
>
>> library(sqldf)  # load package and dependencies
>> myTable <- data.frame(myDate = as.Date("2000-01-31"))  # define myTable
>> sqldf("select myDate + 1 myDate from myTable")
>
>> sqldf will use the heuristic of assuming that the myDate in the output
>> has the same class as the myDate in the input (because they have the
>> same name) and so will convert the output myDate column to the R
>> "Date" class but the ability of such a heuristic to work is fairly
>> limited.
>
>> It is also possible to specify to sqldf the class of each output
>> column but this is tedious and puts SQLite at a disadvantage since it
>> is unnecessary if used with a different backend database that is able
>> to return a Date class column.
>
>> With numeric and character columns there is not much problem but as
>> soon as one gets to date and date/time columns then this impedence
>> mismatch appears and is one fo the main reasons an R user might decide
>> to use a different backend.
>
> There is no impedence mismatch.  Simply inadequate wattage by the person(s) 
> solving the problem.  As I said, this problem has been solved with SQLite and 
> Python for a decade.  So I would suggest the problem is that the wattage was 
> so low, the lights were completely out.
>

The impedence in the example is that on the sqlite side the type is
numeric and on the R side it is of Date class.  It is impossible to
know which sqlite numeric types are intended to represent dates and
which are intended to represent numbers so when they are sent back to
R there is no automatic assignment to Date class.  Either the
programmer has to specify it (which is tedious) or else one has to
rely on heuristics such as assuming that any output field having the
same name as input field must also have the same type (but such
heuristics do not cover all cases).

If you believe that python can know that a date is being returned from
sqlite without the programmer specifying it even though sqlite has no
date class, I suggest providing a self contained reproducible example
of python code to illustrate it as I did with R.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Keith Medcalf]

> > But you already have pandas.read_sql_query.  While that function
> > isn't really what I'd call simple, the complexity afaict -- dates,
> > floats, and chunks -- can be laid at Python's feet.
> 
> I use R rather than python but the problem of dates is significant and
> I assume the same problem as in python.  Consider this query:
> 
>  select myDate + 1 from myTable
> 
> Assume that myTable comes from an R data frame with a myDate column
> that has R class of "Date".  Internally R stores the myDate column as
> days since 1970-01-01 and since SQLite has no Date class it sends that
> number to SQLite.  The select statement then adds 1 giving a number
> representing the next day but when one gets it back to R how can R
> know that that number is intended to represent a Date and so convert
> it to R's Date class?  In fact it can't.   With databases that have a
> Date type this is not a problem but it is a significant problem with
> SQLite.

What is the problem exactly?  Sounds like the R thingy is broken.  I do this 
all the time using Python.  You simply have to compile the library with 
column_metadata and then use it.  Sheesh.  Even pysqlite has done this just 
peachyu fine for about a decade.

> There are some hackish workarounds.  For example, consider this
> self-contained reproducible R code:

> library(sqldf)  # load package and dependencies
> myTable <- data.frame(myDate = as.Date("2000-01-31"))  # define myTable
> sqldf("select myDate + 1 myDate from myTable")

> sqldf will use the heuristic of assuming that the myDate in the output
> has the same class as the myDate in the input (because they have the
> same name) and so will convert the output myDate column to the R
> "Date" class but the ability of such a heuristic to work is fairly
> limited.

> It is also possible to specify to sqldf the class of each output
> column but this is tedious and puts SQLite at a disadvantage since it
> is unnecessary if used with a different backend database that is able
> to return a Date class column.

> With numeric and character columns there is not much problem but as
> soon as one gets to date and date/time columns then this impedence
> mismatch appears and is one fo the main reasons an R user might decide
> to use a different backend.

There is no impedence mismatch.  Simply inadequate wattage by the person(s) 
solving the problem.  As I said, this problem has been solved with SQLite and 
Python for a decade.  So I would suggest the problem is that the wattage was so 
low, the lights were completely out.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Scott Robison]

On Sun, Jan 31, 2016 at 9:42 AM, Keith Medcalf  wrote:

>
> And I thought the "Object Oriented" jihad blew up when it was discovered
> to be counter to productivity and performance in the 1990's and that it did
> not provide a single one of the "advantages" claimed by its mujahedeen
> warriors.
>

Any time one starts using religious terminology to describe a technology,
whether pro or con, it seems suspect to me. There are many successful
projects that utilize object oriented techniques to some extent. SQLite
itself included.

It is possible to write crappy code in any language, and it is possible to
write elegant / efficient code in most languages. Often the choice is not
the language but rather the algorithms implemented.

I would never suggest that everyone must use OO, but to suggest it is
worthless or never works seems suspect to me.

-- 
Scott Robison

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Gabor Grothendieck]

On Sun, Jan 31, 2016 at 6:25 PM, James K. Lowden
 wrote:
> On Sat, 30 Jan 2016 20:50:17 -0500
> Jim Callahan  wrote:
> But you already have pandas.read_sql_query.  While that function
> isn't really what I'd call simple, the complexity afaict -- dates,
> floats, and chunks -- can be laid at Python's feet.

I use R rather than python but the problem of dates is significant and
I assume the same problem as in python.  Consider this query:

 select myDate + 1 from myTable

Assume that myTable comes from an R data frame with a myDate column
that has R class of "Date".  Internally R stores the myDate column as
days since 1970-01-01 and since SQLite has no Date class it sends that
number to SQLite.  The select statement then adds 1 giving a number
representing the next day but when one gets it back to R how can R
know that that number is intended to represent a Date and so convert
it to R's Date class?  In fact it can't.   With databases that have a
Date type this is not a problem but it is a significant problem with
SQLite.

There are some hackish workarounds.  For example, consider this
self-contained reproducible R code:

library(sqldf)  # load package and dependencies
myTable <- data.frame(myDate = as.Date("2000-01-31"))  # define myTable
sqldf("select myDate + 1 myDate from myTable")

sqldf will use the heuristic of assuming that the myDate in the output
has the same class as the myDate in the input (because they have the
same name) and so will convert the output myDate column to the R
"Date" class but the ability of such a heuristic to work is fairly
limited.

It is also possible to specify to sqldf the class of each output
column but this is tedious and puts SQLite at a disadvantage since it
is unnecessary if used with a different backend database that is able
to return a Date class column.

With numeric and character columns there is not much problem but as
soon as one gets to date and date/time columns then this impedence
mismatch appears and is one fo the main reasons an R user might decide
to use a different backend.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Yannick Duchêne]

On Sun, 31 Jan 2016 09:42:28 -0700
"Keith Medcalf"  wrote:

> 
> And I thought the "Object Oriented" jihad blew up when it was discovered to 
> be counter to productivity and performance in the 1990's and that it did not 
> provide a single one of the "advantages" claimed by its mujahedeen warriors.
> 
> Of course, it could be that there is not really very much of anything at all 
> (if there is anything at all) that implements an "object oriented" 
> architecture.  It is mostly just glossing appearances and wrappers around 
> inherently non-object oriented things.  But then again, that just goes to 
> show that OO is inherently flawed.
> 
> OO is a dead horse.  It is about time it was finally shot in the head and put 
> out of its misery.

I'm off?topic, but this is going a bit too far in the opposite direction.

I won't be so much affirmative about OO being entirely negative; there are 
cases where it matches well. These cases (as I experienced them) are when there 
are 1) not that much objects (I mean 100, as an example, not 10 000 or 1000 
000) 2) there each have a strong personality, that is, there differs enough 
from each other instance, while have enough in commons (both). This is 
typically the case with user interfaces (although state?machine are nice too in 
this area), as the human being is a complex thing :-D with many peculiarities, 
which are well matched by OO with it's deep inheritance and attributes 
everywhere (prototype based OO is nice too for this purpose).

A fact is that a user interface as in this example, works at a tempo which is 
the same as that of a human: quarter of second or something around; it normally 
consumes not that much resources (objects are not responsible for heavy 
graphics). It does not do computation, it behaves. That's the word I believe: 
OO is good at expressing peculiar behaviours, more than at expressing 
computation (if one does not confuse records and OO's objects).

The overall criticism I would still have against OO, is that in its typical 
implementations, it confuses interface inheritance and implementation 
inheritance, which in my opinion, should be separate (some OO languages have 
private inheritance at least, ? unfortunately, most famous languages don't). An 
other issue (however cleaner solved by Eiffel, unlike others did), is name 
conflicts in inheritance. The implementations and the model, are still two 
different things.


-- 
Yannick Duch?ne

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[James K. Lowden]

On Sat, 30 Jan 2016 23:03:29 +
Simon Slavin  wrote:

> On 30 Jan 2016, at 8:13pm, Yannick Duch?ne 
> wrote:
> 
> > In my opinion (which some others share), OO is a bag of
> > miscellaneous things which are better tools and better understood
> > when accosted individually. Just trying to define what OO is, shows
> > it: is this about late binding? (if it is, then there sub?program
> > references, first?class functions, or even static polymorphism and
> > signature overloading) About encapsulation? (if it is, then there
> > is already modularity and scopes) About grouping logically related
> > entities? (if it is, there is already modularity, and sometime
> > physically grouping is a bad physical design).
> 
> There are a number of problems in using a relational database for
> object-oriented purposes.  One is that to provide access to stored
> objects you need to access the database in very inefficient ways
> which are slow and are not helped by caching.  You can read about
> some of the problems here:
> 
> 

To the extent "impedance mismatch" is real, it's a matter of looking
through the wrong end of the telescope.  

Programming languages have almost universally ignored relations, logic,
and constraints, leaving programmers with primitives, pointers, and
loops.   Which is cause and which effect?  Do programmers ignorant of
set theory demand primitive languages?  Or do primitive languages beget
ignorant programmers?  I don't know.  What I do know is that a
programming language with built-in support for relational concepts
remains to be invented.  

--jkl

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[James K. Lowden]

On Sat, 30 Jan 2016 20:50:17 -0500
Jim Callahan  wrote:

> I am not interested in a complete ORM; what I am interested is when
> the object-oriented language supports a SQL-R-like object. In R, the
> object is called a data.frame and the package "Pandas" supplies a
> similar data frame object to Python.
> 
> R as I have mentioned has fantastic interfaces to SQL databases that
> allow one to pass a query and have the result populate a data frame.
> The data frame in R or Python can be fed to a machine learning
> algorithm (scikit learn on Python) or to a plotting package such as
> ggplot or bokeh.

OK, good.  It sounds lke what you're really interested in is the
simplest function possible to move data from SQLite into Pandas.  (I'll
stick with Python, since I know it better than R.)  

But you already have pandas.read_sql_query.  While that function
isn't really what I'd call simple, the complexity afaict -- dates,
floats, and chunks -- can be laid at Python's feet.  At a minimum, you
have to specify the SQL and the column names you want to use in
Pandas.  

> SQLAlchemy package ... did not understand SQLite VIEWS and one had to
> write an explicit loop to build the data frame

You don't need to use SQLAlchemy with SQLite.  And you don't need to
write loops to move query results into a Pandas DataFrame.  

So, is the problem solved, or am I missing something?  

--jkl

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Simon Slavin]

On 31 Jan 2016, at 4:42pm, Keith Medcalf  wrote:

> And I thought the "Object Oriented" jihad blew up when it was discovered to 
> be counter to productivity and performance in the 1990's and that it did not 
> provide a single one of the "advantages" claimed by its mujahedeen warriors.

Hmm.  I thing Object Oriented programming is amazing, and it provides the brain 
which a far simpler safer way to think about what it wants the program to do, 
thus speeding development and reducing errors.  I even use OO in JavaScript and 
PHP, both of which languages can do the same things without using OO.

It's just that an interface between procedural programming and OO has to deal 
with certain exceptions and rarities which OO is not good at dealing with.  As 
a previous poster commented, one of these is error-handling.  If the person who 
writes the library doesn't think all this out clearly ahead of time, you end up 
with a library where it takes four lines of programming just to tell whether 
the last command received an error.  Or some other inelegant and tedious thing 
the programmer has to do, to use the library at all.

It's possible to do this stuff well, but plan to throw away the first two 
versions, not just the first one.



Simon.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Keith Medcalf]

On Saturday, 30 January, 2016 16:03, Simon Slavin  
said:
> On 30 Jan 2016, at 8:13pm, Yannick Duch?ne 
> wrote:

> > In my opinion (which some others share), OO is a bag of miscellaneous
> things which are better tools and better understood when accosted
> individually. Just trying to define what OO is, shows it: is this about
> late binding? (if it is, then there sub?program references, first?class
> functions, or even static polymorphism and signature overloading) About
> encapsulation? (if it is, then there is already modularity and scopes)
> About grouping logically related entities? (if it is, there is already
> modularity, and sometime physically grouping is a bad physical design).
> 
> There are a number of problems in using a relational database for object-
> oriented purposes.  One is that to provide access to stored objects you
> need to access the database in very inefficient ways which are slow and
> are not helped by caching.  You can read about some of the problems here:

And I thought the "Object Oriented" jihad blew up when it was discovered to be 
counter to productivity and performance in the 1990's and that it did not 
provide a single one of the "advantages" claimed by its mujahedeen warriors.

Of course, it could be that there is not really very much of anything at all 
(if there is anything at all) that implements an "object oriented" 
architecture.  It is mostly just glossing appearances and wrappers around 
inherently non-object oriented things.  But then again, that just goes to show 
that OO is inherently flawed.

OO is a dead horse.  It is about time it was finally shot in the head and put 
out of its misery.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Yannick Duchêne]

On Sat, 30 Jan 2016 23:03:29 +
Simon Slavin  wrote:

> 
> On 30 Jan 2016, at 8:13pm, Yannick Duch?ne  
> wrote:
> 
> > In my opinion (which some others share), OO is a bag of miscellaneous 
> > things which are better tools and better understood when accosted 
> > individually. Just trying to define what OO is, shows it: is this about 
> > late binding? (if it is, then there sub?program references, first?class 
> > functions, or even static polymorphism and signature overloading) About 
> > encapsulation? (if it is, then there is already modularity and scopes) 
> > About grouping logically related entities? (if it is, there is already 
> > modularity, and sometime physically grouping is a bad physical design).
> 
> There are a number of problems in using a relational database for 
> object-oriented purposes.  One is that to provide access to stored objects 
> you need to access the database in very inefficient ways which are slow and 
> are not helped by caching. [?]

This matches what I had in mind with the short sentence ?And especially about 
OO and DB, I know there are complaints OO hides too much the data model of 
DBs?. I heard about an example with a video game engineer explaining OO 
typically impends efficiency for their use cases, in a way which can be related 
to a DB and its tables. Say an object has an attribute `x` among others. Say a 
big set of objects of this type is often iterated, but that's not really the 
objects which are iterated, that's their `x` attribute alone and no other 
attributes are accessed during this frequent long operation. Using OO, it is 
suggested `x` should be stored in the object instances (typically a kind of 
C?struct), so there is a large set of unused data coming with each `x` in an 
iteration, which prevents efficiency (poor caching, more memory access). In 
such a case, the `x` should not be stored in the object, rather in a dedicated 
list holding only the `x` attributes. That's about the same as with a DB when 
some columns are moved from a table to a specific table, while maintaining a 
relation between both.

Where `x` should belong, that's not a conceptual picture about the attributes 
alone which says it, that's the typical operations referring to the attribute, 
which do, and that's as more important as the number of object instance grows. 
That's for a similar reason a DB may need to be refactored when new queries 
come, while from an OO point of view, this would just suggest to add a new 
method to an object, not to move `x` from the object to something else 
(something OO will never suggest).

That's what make me agree a relational DB is not well suited to store objects 
(the models differ too much), unless the model of that object is broken down 
after the requirement of its individual operations, or the object instances are 
just to be stored as monolithic blobs.

There are cases where the OO view matches well, and there are cases where it 
does not. There is no need to force one view to be the other.

-- 
Yannick Duch?ne

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Yannick Duchêne]

On Sat, 30 Jan 2016 20:50:17 -0500
Jim Callahan  wrote:

> I am not interested in a complete ORM; what I am interested is when the
> object-oriented language supports a SQL-R-like object. In R, the object is
> called a data.frame and the package "Pandas" supplies a similar data frame
> object to Python.
> https://pypi.python.org/pypi/pandas/0.10.0/

The page says:
> Additionally, it has the broader goal of becoming the most powerful
> and flexible open source data analysis / manipulation tool available
> in any language.

There have been so much announcements of the like in many areas?? (open?source 
or not)

The page mentions HDF5, and Pandas descriptions seems similar to that of HDF5 
in the intents. If Pandas exists, this may be that its authors believe the HDF5 
library is not good enough. But HDF5 (which is itself subject to criticism) 
made the same promises as Pandas seems to do. Just to say this may be seeing 
something as universal, while it is not for every one or every use, so the 
urgency may not be that hight.

Is the concern mainly about hierarchical data? (SQL has a reputation for not be 
well suited to that) About heterogeneous data? (i.e. not easily matching a type 
or a pattern)

> I am not interested in a complete ORM; what I am interested is when the
> object-oriented language supports [?]

What is object oriented in this context? What properties of the object model 
raise hight in the picture when you think about in typical use cases?

> R as I have mentioned has fantastic interfaces to SQL databases that allow
> one to pass a query and have the result populate a data frame. The data
> frame in R or Python can be fed to a machine learning algorithm (scikit
> learn on Python) or to a plotting package such as ggplot or bokeh.
> https://pypi.python.org/pypi/ggplot/0.6.8
> http://bokeh.pydata.org/en/latest/
> http://scikit-learn.org/stable/
> 
> What I want to do is to demonstrate short scripts in R and Python to
> accomplish the same task.  I don't want the Python scripts to be longer and
> more complicated because Python has an lower level interface to SQLite. [?]

Why should this be SQLite's responsibility? What prevents a Python (or R) 
library to implement the desired interface? (providing a used library does not 
count in a script's length). Is this with the hope to get greater efficiency? 
(i.e. timing and consumed resources)

With my apologizes for the naive questions??

-- 
Yannick Duch?ne

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Simon Slavin]

On 30 Jan 2016, at 8:13pm, Yannick Duch?ne  wrote:

> In my opinion (which some others share), OO is a bag of miscellaneous things 
> which are better tools and better understood when accosted individually. Just 
> trying to define what OO is, shows it: is this about late binding? (if it is, 
> then there sub?program references, first?class functions, or even static 
> polymorphism and signature overloading) About encapsulation? (if it is, then 
> there is already modularity and scopes) About grouping logically related 
> entities? (if it is, there is already modularity, and sometime physically 
> grouping is a bad physical design).

There are a number of problems in using a relational database for 
object-oriented purposes.  One is that to provide access to stored objects you 
need to access the database in very inefficient ways which are slow and are not 
helped by caching.  You can read about some of the problems here:



There are databases designed from the ground up as OO databases.  Or rather as 
ways to provide persistent storage for objects.  They tend to be non-relational 
databases, optimised for efficiency in making changes to objects rather than 
searching and sorting.

You can use SQLite like this.  In fact implementing persistent object storage 
in SQLite would be a wonderful exercise for a programming class.  But it might 
result in a solution too slow to be useful for real programs.  The biggest 
pointer for this is that it hasn't been done.  There's no library to implement 
persistent object storage that everyone knows about.

Simon.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Yannick Duchêne]

On Sat, 30 Jan 2016 14:56:15 -0500
"James K. Lowden"  wrote:

> On Thu, 28 Jan 2016 16:47:40 -0500
> Jim Callahan  wrote:
> 
> > I am hopeful this new JDBC based interface will provide as
> > satisfactory high level channel between SQLite3 and Python.
> 
> As someone who's written a couple of OO DBMS libraries and uses the
> Python SQLIte module, I wonder what you're hoping to see.  What example
> do you have in mind for OO functionality not already there?
> 

I have not read the thread, and just react on this: I agree, there is not that 
much to hope from OO?? in the general case. In my opinion (which some others 
share), OO is a bag of miscellaneous things which are better tools and better 
understood when accosted individually. Just trying to define what OO is, shows 
it: is this about late binding? (if it is, then there sub?program references, 
first?class functions, or even static polymorphism and signature overloading) 
About encapsulation? (if it is, then there is already modularity and scopes) 
About grouping logically related entities? (if it is, there is already 
modularity, and sometime physically grouping is a bad physical design). This 
may be compared to AJAX, which was nothing new, simply a brand on a set of 
things known since long, and understanding AJAX is not possible, there nothing 
to understand about it, while there are things to understand about what it 
branded.

And especially about OO and DB, I know there are complains OO hides too much 
the data model of DBs (I feel to see).


-- 
Yannick Duch?ne

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

I am not interested in a complete ORM; what I am interested is when the
object-oriented language supports a SQL-R-like object. In R, the object is
called a data.frame and the package "Pandas" supplies a similar data frame
object to Python.
https://pypi.python.org/pypi/pandas/0.10.0/

R as I have mentioned has fantastic interfaces to SQL databases that allow
one to pass a query and have the result populate a data frame. The data
frame in R or Python can be fed to a machine learning algorithm (scikit
learn on Python) or to a plotting package such as ggplot or bokeh.
https://pypi.python.org/pypi/ggplot/0.6.8
http://bokeh.pydata.org/en/latest/
http://scikit-learn.org/stable/

What I want to do is to demonstrate short scripts in R and Python to
accomplish the same task.  I don't want the Python scripts to be longer and
more complicated because Python has an lower level interface to SQLite.
When I tried to do this two years ago, the SQLAlchemy package (for
example) did not understand SQLite VIEWS and one had to write an explicit
loop to build the data frame (which would make the Python code longer and
more complicated than the R code).

I can't ask for enhancements to the SQL CLI because that is a standard that
was written for communication with non-object oriented languages such as C
and COBOL which do not have standardized SQL-table-like objects (one could
of course write a library for C to implement a data frame -- R itself  is
written in C and so are most implementations of Python), but S (the
proprietary ancestor of R) was written so Bell Labs scientists
did not have to learn C or FORTRAN.

Rather than invent a whole new spec out of whole cloth, I thought the JDBC
spec might be a good start at a high level interface and would have the
added benefit of being cross platform. There seem to be a lot of SQL GUIs
written in Java that use JDBC; so JDBC must be doing something right?

There also appears to be a Python interface to ODBC. Microsoft should have
a strong interest in having a good ODBC driver for SQLite (having adopted
SQLite in MS Windows) because Microsoft products such as C#, MS Access and
MS Excel all could consume SQLite data via ODBC. Perhaps Microsoft
could issue a consulting contract (to Dr. Richard Hipp) for an high quality
ODBC driver for SQLite?
But, is ODBC cross-platform?

I agree that error messages are an issue -- is this a problem with the
JDBC/ODBC specs, the implementations or is the entire technical approach
beyond redemption?

Jim Callahan
Orlando, FL
























This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 30, 2016 at 6:03 PM, Simon Slavin  wrote:

>
> On 30 Jan 2016, at 8:13pm, Yannick Duch?ne 
> wrote:
>
> > In my opinion (which some others share), OO is a bag of miscellaneous
> things which are better tools and better understood when accosted
> individually. Just trying to define what OO is, shows it: is this about
> late binding? (if it is, then there sub?program references, first?class
> functions, or even static polymorphism and signature overloading) About
> encapsulation? (if it is, then there is already modularity and scopes)
> About grouping logically related entities? (if it is, there is already
> modularity, and sometime physically grouping is a bad physical design).
>
> There are a number of problems in using a relational database for
> object-oriented purposes.  One is that to provide access to stored objects
> you need to access the database in very inefficient ways which are slow and
> are not helped by caching.  You can read about some of the problems here:
>
> 
>
> There are databases designed from the ground up as OO databases.  Or
> rather as ways to provide persistent storage for objects.  They tend to be
> non-relational databases, optimised for efficiency in making changes to
> objects rather than searching and sorting.
>
> You can use SQLite like this.  In fact implementing persistent object
> storage in SQLite would be a wonderful exercise for a programming class.
> But it might result in a solution too slow to be useful for real programs.
> The biggest pointer for this is that it hasn't been done.  There's no
> library to implement persistent object storage that everyone knows about.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[James K. Lowden]

On Thu, 28 Jan 2016 16:47:40 -0500
Jim Callahan  wrote:

> I am hopeful this new JDBC based interface will provide as
> satisfactory high level channel between SQLite3 and Python.

As someone who's written a couple of OO DBMS libraries and uses the
Python SQLIte module, I wonder what you're hoping to see.  What example
do you have in mind for OO functionality not already there?

ODBC was created for one reason only: to define a binary interface to
DBMS client libraries.  Afaik the same is true for JDBC. 

That interface-to-a-library property has two downsides: 

1.  Configuration complexity.  With SQLite, there is no configuration
file.  Most of the features of a connection -- server, port, username,
password, etc. -- don't apply.  When a connection fails, it takes some
expertise to tease out whether the problem lies in the ODBC
configuration, the client library, the server, or the network.  With
JBDC you have still more: the added complexity of Java setup.  

2.  Opacity.  ODBC is near the top of my list for vague error
messages.  Favorite: "General ODBC error".  If I ever meet General
ODBC, I'm going to give him a piece of my mind, if I have any left.  

The second is terribly important.  Take error handling.  Instead of
getting back, say, SQLITE_BUSY, and RTFM, you get an ODBC error and
then maybe, with a little more work, the underlying status code and
message.  And you're *still* not done, because did I mention the ODBC
driver is opaque?  Your ODBC call uses the native library in
unspecified, undocumented ways.  When you get the native error status,
you then have to reason about what the ODBC driver must have done, and
from there back to what you need to do.  It's a whole layer of black
magic and obfuscation that only lengthens your day.  

And it's not just errors.  How do you map the ODBC API onto SQLite's
API?  (I've never seen an ODBC driver document the relationship of the
ODBC functions to the native ones.) What to do with sqlite3_exec or
sqlite3_blob_open? If you know the SQLite API, you'll spend quite a bit
of time discovering how it's been mapped onto the ODBC API.  And when
you're done, you'll discover pieces missing.  

You already have one layer of mediation in the Python sqlite module.
That module is thankfully a pretty thin veneer over the C API, and the
errors it produces can be straighforwardly traced to the C function it
exposes.  You have the convenience of using the connection as a context
manager, of row objects (although dict would have been better), of
fetchall.  What sort of OO garnish would you add?  

--jkl

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

Today, I found a Python package, "JayDeBeApi" that accesses SQLite via JDBC
rather than directly through the C language Call Level Interface (CLI).
https://github.com/baztian/jaydebeapi/blob/master/README.rst

This might provide the object oriented interface I have been looking for!
Has anyone tried it?

The SQLite3 CLI matches the specifications of an interface that was
intended for the C programming language. The CLI specs were intended for C
and COBOL not designed with an object oriented language in mind.
"Developed in the early 1990s, the API was defined only for the programming
languages C  and
COBOL ."
https://en.wikipedia.org/wiki/Call_Level_Interface

By  contrast, JDBC was designed for Java (an object oriented language) as a
substitute for Microsoft's ODBC.

Now that a Python developer is using JDBC instead of CLI we may get a more
object oriented interface:

"JDBC connections support creating and executing statements.  ...Query
statements return a JDBC row result set. The row result set is used to walk
over the result set . Individual
columns  in a row are
retrieved either by name or by column number. There may be any number of
rows in the result set. The row result set has metadata that describes the
names of the columns and their types."
https://en.wikipedia.org/wiki/Java_Database_Connectivity

As I recall there are at least three JDBC drivers for SQLite3, one by
Werner
http://www.ch-werner.de/javasqlite/

one by Saito (Xerial)
https://github.com/xerial/sqlite-jdbc/blob/master/README.md

and Saito's was forked (with attribution from Zentus)
https://github.com/crawshaw/sqlitejdbc

I am hopeful this new JDBC based interface will provide as satisfactory
high level channel between SQLite3 and Python.

Jim Callahan

On Thu, Jan 7, 2016 at 7:55 PM, Warren Young  wrote:

> On Jan 7, 2016, at 5:22 PM, Jim Callahan 
> wrote:
> >
> > I believe R has remarkably good interface packages for SQLite
>
> That?s the appropriate level: the high-level language's DB access layer
> should map the low-level C record-at-a-time API to an appropriate
> language-level abstraction.
>
> R almost forces you to do this because of things like data.frame.  But,
> that?s no argument for other HLL DBMS API writers not to provide similar
> affordances.
>
> I?ve been involved with two different C++ DBMS wrapper libraries, and both
> of them provide a way to get a std::vector<> as a result set instead of
> iterate over individual rows.  As with R?s SQLite wrapper, I felt it was my
> C++ code?s responsibility to do this repackaging, not the underlying C DBMS
> access API.
>
> That?s not to say that the SQLite C API has no warts:
>
> 1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses
> 1-based indices.  I can cope with either base, but please pick one!  (And
> make it the correct base for programming, 0.  (Yes, I know I just praised R
> above.  R?s use of 1-based arrays is WRONG.))
>
> 2. There is no ?preview? mechanism.  That is, you can?t bind some
> parameters to a prepared query string and then get the resulting SQL
> because SQLite substitutes the values into the query at a layer below the
> SQL parser.  This means that if you have an error in your SQL syntax or
> your parameters cause a constraint violation, your debug logging layer can
> only log the prepared query string, not the parameters that went into it,
> which makes it unnecessarily difficult to determine which code path caused
> you to get the error when looking at logs of a running system.
>
> 3. The query finalization code could be less picky.  If I prepare a new
> query without finalizing the previous one, I?d rather that SQLite didn?t
> punish me by throwing errors unless I put it into a ?lint? mode.  Just toss
> the half-finished prior query and move on, please.
>
> 4. There are several signs of backwards compatible extensions which make
> the API more complex than if it were designed with the features from the
> start.  (e.g. _v2() APIs, the various ways to get error codes, etc.)
> Hopefully those doing the SQLite4 effort will feel free to break the API,
> jettisoning this historical baggage.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[da...@andl.org]

I agree with Duncan. The original SQL code and a list of bound values.

This is a problem we know well and have already solved exactly this way in a 
different context.

Yes, it would be a good new feature request.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Saturday, 9 January 2016 9:22 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Wish List for 2016: High Level API for Object Oriented 
Interactive Languages

Okay, I think this clears some things up.

On 2016-01-08 11:36 AM, Warren Young wrote:
> On Jan 8, 2016, at 12:39 AM, Darren Duncan  wrote:
>>
>> I interpreted your request as if current systems' error outputs at execute 
>> time were printing out the problematic SQL statement with placeholder names 
>> as originally prepared, and you wanted the error outputs to have the 
>> placeholders substituted with literals for the values passed to them at 
>> execute time interpolated into them.
>
> Yes.
>
>> one can just list the bound values separately / afterwards rather than 
>> having to rewrite the SQL to interpolate those values.
>
> Of course, but the question is not whether a caller *can* do this, it?s 
> whether the caller *should have to* do this.
>
>  From the caller?s perspective, it has already passed ownership of the values 
> off to SQLite via sqlite3_bind_*().  If an error occurs, the bind calls may 
> be a few levels separate from the sqlite3_step() call that actually causes 
> the error.  SQLite still owns the values, though, and could provide them in a 
> hypothetical sqlite3_preview() call, which assembles the effective SQL it 
> tried to execute and failed.
>
> You?re asking the caller to maintain separate ownership of data that SQLite 
> needs just for the error case.  SQLite has a much better reason to have a 
> copy of that data, so it should be providing the values to the error handler, 
> not the caller.

Actually, I agree with you that SQLite should be providing everything.  What I 
disagree with is making the interpolation necessary.  The hypothetical
sqlite3_preview() should output 2 things, the SQL as originally passed to 
prepare with placeholders intact, plus a list of placeholder names and their 
bound values that failed.

>> If your problem is that the output simply says an error occurred and doesn't 
>> print out the SQL, then fair enough, I misunderstood you.
>
> It?s a bit more than that.  The problem is that a given prepared statement is 
> necessarily generic.  Just from looking at the statement in a log file, you 
> can?t tell what values were used with it, which would help you understand the 
> context in which it was used.

To further explain, I see SQL as a programming language same as C or Perl or 
whatever.

Calling prepare is effectively invoking a compiler on SQL source code where 
that SQL source defines a routine that may have parameters.  Calling execute is 
then asking to execute that compiled routine where the bind parameters are the 
runtime-provided arguments to the routine.

Do you think it makes sense in any other common programming language that, if a 
routine fails with a particular set of arguments, that the debugging message 
includes say C source code rewritten to substitute literals where references to 
its parameters were?  Or does it make more sense for the debugging message to 
print the actual routine source plus a list of the passed argument values?  I 
am arguing for the latter, all done by SQLite.

I consider what is reasonable for SQL to be the same as for other languages.

-- Darren Duncan

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

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[R Smith]

On 2016/01/08 9:51 AM, Darren Duncan wrote:
> Stephen,
>
> What you are arguing for (no shared libraries) is bad old days where 
> one had to recompile their programming language to add support for a 
> DBMS, rather than the DBMS support being a separately installable 
> library that one could choose to install or not or upgrade 
> semi-independently or not, or choose to use an alternative or not.

I can't agree more - and to add, while I can sympathize with the point, 
I absolutely love SQLite, but the amount of projects I have made without 
SQLite far outweighs those containing it (on all platforms). I would 
like it to remain optional everywhere.

Speaking of Delphi specifically (as the OP mentions, and which I do 
use), I have simply a unit that links the DLL, and another that 
maintains an object that does all data handling. It's the simplest 
solution - Sure I need to add the "sqlite3.dll" file to my installers 
and updaters, but I get a free upgrade by just dropping in the new DLL 
when it arrives - no need to recompile or re-setup or anything. 
(Likewise for iOS / Mac OSX, but Linux projects [freepascal / Lazarus] 
are more tricky in this regard - probably only due to my limited 
knowledge, I'm sure someone somewhere made stuff for it).

I'm quite willing to share any of the delphi libraries and objects if 
anyone is interested (best mail me off-list) - they can be seen in 
action if need be by simply peeking at sqlitespeed from
http://www.sqlc.rifin.co.za

Cheers,
Ryan

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

Okay, I think this clears some things up.

On 2016-01-08 11:36 AM, Warren Young wrote:
> On Jan 8, 2016, at 12:39 AM, Darren Duncan  wrote:
>>
>> I interpreted your request as if current systems' error outputs at execute 
>> time were printing out the problematic SQL statement with placeholder names 
>> as originally prepared, and you wanted the error outputs to have the 
>> placeholders substituted with literals for the values passed to them at 
>> execute time interpolated into them.
>
> Yes.
>
>> one can just list the bound values separately / afterwards rather than 
>> having to rewrite the SQL to interpolate those values.
>
> Of course, but the question is not whether a caller *can* do this, it?s 
> whether the caller *should have to* do this.
>
>  From the caller?s perspective, it has already passed ownership of the values 
> off to SQLite via sqlite3_bind_*().  If an error occurs, the bind calls may 
> be a few levels separate from the sqlite3_step() call that actually causes 
> the error.  SQLite still owns the values, though, and could provide them in a 
> hypothetical sqlite3_preview() call, which assembles the effective SQL it 
> tried to execute and failed.
>
> You?re asking the caller to maintain separate ownership of data that SQLite 
> needs just for the error case.  SQLite has a much better reason to have a 
> copy of that data, so it should be providing the values to the error handler, 
> not the caller.

Actually, I agree with you that SQLite should be providing everything.  What I 
disagree with is making the interpolation necessary.  The hypothetical 
sqlite3_preview() should output 2 things, the SQL as originally passed to 
prepare with placeholders intact, plus a list of placeholder names and their 
bound values that failed.

>> If your problem is that the output simply says an error occurred and doesn't 
>> print out the SQL, then fair enough, I misunderstood you.
>
> It?s a bit more than that.  The problem is that a given prepared statement is 
> necessarily generic.  Just from looking at the statement in a log file, you 
> can?t tell what values were used with it, which would help you understand the 
> context in which it was used.

To further explain, I see SQL as a programming language same as C or Perl or 
whatever.

Calling prepare is effectively invoking a compiler on SQL source code where 
that 
SQL source defines a routine that may have parameters.  Calling execute is then 
asking to execute that compiled routine where the bind parameters are the 
runtime-provided arguments to the routine.

Do you think it makes sense in any other common programming language that, if a 
routine fails with a particular set of arguments, that the debugging message 
includes say C source code rewritten to substitute literals where references to 
its parameters were?  Or does it make more sense for the debugging message to 
print the actual routine source plus a list of the passed argument values?  I 
am 
arguing for the latter, all done by SQLite.

I consider what is reasonable for SQL to be the same as for other languages.

-- Darren Duncan

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

On 2016-01-08 8:08 AM, Stephen Chrzanowski wrote:
> For the record, *I* personally prefer trying to get all essential resources
> built directly into my final output (For SQLite, default database
> structures, SQLite strings, and maybe that one day, SQLite itself), that
> way I'm in control of what the application does, and have no reliance on a
> 3rd party update to a resource file that breaks my code.  That is just my
> preference, and old school or not, I prefer working software, not software
> that might work after MySQL updates and breaks a resource I require when my
> application doesn't touch MySQL, or when a user deletes a critical file my
> application requires and claims they didn't do anything   I've
> never had 100% success on a fully independent database driven application
> (SQLite or not), and that is perfectly OK.  That doesn't mean I'd like to
> strive for that one day.

You are or seem to be talking about 2 different things in this thread.

I very much agree with you that it is reasonable for an APPLICATION to bundle 
its key dependent libraries in ITS executable so the proper functioning of the 
application is insulated against many changes to system-provided or separately 
installed libraries.  Especially today with abundant disk space.

But what you seemed to be arguing for before was that a programmer tool for 
making applications, that is Perl itself or R itself or what have you should be 
bundling SQLite with it, and this I disagree with.

The user base of programming language environments is programmers who are 
making 
applications, and it should be those users' decision to bundle SQLite with 
their 
application, and not having it forced on them by the creator of the programming 
language to include SQLite with all applications regardless of whether it is 
used or not.

Apples and oranges.

-- Darren Duncan

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 8, 2016, at 12:39 AM, Darren Duncan  wrote:
> 
> I interpreted your request as if current systems' error outputs at execute 
> time were printing out the problematic SQL statement with placeholder names 
> as originally prepared, and you wanted the error outputs to have the 
> placeholders substituted with literals for the values passed to them at 
> execute time interpolated into them.

Yes.

> one can just list the bound values separately / afterwards rather than having 
> to rewrite the SQL to interpolate those values.

Of course, but the question is not whether a caller *can* do this, it?s whether 
the caller *should have to* do this.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Stephen Chrzanowski]

On Fri, Jan 8, 2016 at 10:54 AM, R Smith  wrote:

>
> I can't agree more - and to add, while I can sympathize with the point, I
> absolutely love SQLite, but the amount of projects I have made without
> SQLite far outweighs those containing it (on all platforms). I would like
> it to remain optional everywhere.
>

Acceptable.  I'm not saying it should be a requirement to include it in the
language core.  A simple INCLUDE or whatever relevant should be available
by the language developers that allows for a basic interface to the SQLite
library, be it relying on the external DLL/SO or get it embedded.  But
having that INCLUDE or USES should be at the language side of things, not
SQLite.  SQLite should not cater to every language out there, regardless of
size and use.  It should be the languages that obey the laws and rules and
integration methodologies provided by SQLite.  *OF COURSE* Dr Hipp and
others should listen to what the community has to say about the interfaces,
and they should also pay attention to what kinds of integrations and
methodologies of different techs are out there, and consider if it is
viable and worth while to get implemented into the core of SQLite.  But
because "R" has an oddball connection methodology (Or whatever the wishlist
had), it shouldn't mean that other languages should now start working the
way "R" wants it to be done.


>
> Speaking of Delphi specifically (as the OP mentions, and which I do use),
> I have simply a unit that links the DLL, and another that maintains an
> object that does all data handling. It's the simplest solution - Sure I
> need to add the "sqlite3.dll" file to my installers and updaters, but I get
> a free upgrade by just dropping in the new DLL when it arrives - no need to
> recompile or re-setup or anything. (Likewise for iOS / Mac OSX, but Linux
> projects [freepascal / Lazarus] are more tricky in this regard - probably
> only due to my limited knowledge, I'm sure someone somewhere made stuff for
> it).
>
>
Half a mind to devil-advocate this, but, I just wanna go play games!
{chuckle}



> I'm quite willing to share any of the delphi libraries and objects if
> anyone is interested (best mail me off-list) - they can be seen in action
> if need be by simply peeking at sqlitespeed from
> http://www.sqlc.rifin.co.za
>
>
BM'd for later viewing.  Got Creepers and Skeletons to kill.


> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Stephen Chrzanowski]

Because this list supports many different things, not just SQLite
downloaded from sqlite.org, maybe I'm off target with my interpretation of
these wishlists.

I'm not arguing about pros and cons of shared libraries directly.  My
comments were made from a tired guy who started the day early, was busy
being active with family, and then finished late, so yeah, it was a bit
confusing.

For the record, *I* personally prefer trying to get all essential resources
built directly into my final output (For SQLite, default database
structures, SQLite strings, and maybe that one day, SQLite itself), that
way I'm in control of what the application does, and have no reliance on a
3rd party update to a resource file that breaks my code.  That is just my
preference, and old school or not, I prefer working software, not software
that might work after MySQL updates and breaks a resource I require when my
application doesn't touch MySQL, or when a user deletes a critical file my
application requires and claims they didn't do anything (Other than sort
all .EXE into c:\EXE and trashed as many DLL files since they don't RUN
properly -- Thank you BOFH for bringing that situation to mind).  I've
never had 100% success on a fully independent database driven application
(SQLite or not), and that is perfectly OK.  That doesn't mean I'd like to
strive for that one day.

The thought of my recoding SQLite C to Pascal is only a pipe dream, and
only means that I can remove another external dependency not controlled by
me, and it'd be kind of fun, AND I might finally understand the C code well
enough to contribute, *AND* it'd be my first application that'd let me read
and write to a foreign binary data structure successfully.  If I'm bored, I
might do it.  More likely to shoot my foot off, or some jazz like that
though.

What I'm arguing for is that SQLite stays as is (Allowing for enhancements,
of course), and any language out in the field that would LIKE to include
SQLite should conform to SQLites calling conventions, at least to the
basics of calling conventions for prepare, or connect, or what have you.
Obviously, making BASH scripts directly support SQLite is kind of an
oddball request, but if it were ever to be, it should be on the BASH
language developers to conform to what SQLite provides, not ask that SQLite
abides by BASHs rules and regulations.  The reason for this is that SQLite
should NEVER conform to everyone elses standards because everyone believes
their standards are first and foremost and to hell with everyone else.
There is no global standard for all language calling conventions, and some
of the wishlists I've seen are asking that SQLite bend to their views,
which is exactly what has me on edge.

A wrapper should be used for convenience or internal standardization, not
the pendulum in a lever (Otherwise you just have a stick, or a bunch of
unhappy kids who can't play on the teeter-totter).  A wrapper should take
whatever the language provides and either enhance it, or, rename functions
that makes more sense to the developers.  I'm in no way arguing that the
sqlite3.dll (and equivalent) should be embedded into every language
compiler so it is transparent and shipped with the executable with no
reliance on a shared resource.  That purely is my deal, my internal wish
list, and NOT something I'd ever DREAM of asking the SQLite devs to conform
to.  I'm arguing that any language that wants to provide access to SQLite
should have some rudimentary built in commands that permit access to the
shared resources, and then the Perl, "R", Pascal, Delphi, PHP, and DOT-NET
developers just need to write their wrappers against what the language
developers provide.  Again, I'm not arguing that the DLL/SO/whatever ends
up in the final build of the executable, but just that the language
compiler or interpreter has the fundamental functionality to allow for
simple wrappers to exist without having to go to strangers.  To be honest,
I've never heard of the language "R", so I don't know what it is, how
"high" that high-level is, or even what its syntax is like, whether it is a
compiled language or if it is JIT.

So the comments of (And I'm SUPER exaggerating with this) *"I use this
language called 'Ego' which is far superior than any other language, and
the way SQLite interacts with my language sucks.  I'd like to see SQLite
allow me to use SEL instead of SELECT because I use a lot of selects in
negative-endian, reverse notation, double-width-unicode strings only my
language supports, so I'd be saving LOTS of space!"* is what is kind of
grinding my wooden gears.


On Fri, Jan 8, 2016 at 2:51 AM, Darren Duncan 
wrote:

> Stephen,
>
> What you are arguing for (no shared libraries) is bad old days where one
> had to recompile their programming language to add support for a DBMS,
> rather than the DBMS support being a separately installable library that
> one could choose to install or not or upgrade semi-independently or not, or
> choose to use an 

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Gabor Grothendieck]

This is how R works too.  That is the RSQLite package that gives
access to SQLite includes SQLite itself right in the package itself so
one need not separately install SQLite.

Also RSQlite uses the R DBI package which defines connections as
classes which are subclassed by the various database packages
(RSQLite, RMySQL, RPostgreSQL, etc.). Thus, in the case of RSQLite it
defines a connection subclass whose methods allow one to access
SQLite.

R also has the higher level sqldf package that lets one access R
data.frames (R data frames are like memory resident database tables)
as if they were tables in a relational database.  When sqldf is run it
creates an SQLite database (or other backend that might be used but
the default is SQLite), uploads any data frames referenced in the SQL
statement, performs the SQL statements and downloads the result
destroying the database.  SQLite is sufficiently fast that this is
often faster than performing the same operation in native R despite
having to upload the inputs and download the output.

For example, the following installs sqldf and its dependencies
(RSQLite, DBI) on the first line, loads them all into the current
session's workspace in the second line and then lists the first 4 rows
of the iris data frame (iris comes with R) using SQLite as the backend
and then defines a data.frame DF and performs another SQL statement:

install.packages("sqldf")
library(sqldf)

sqldf("select * from iris limit 4")

DF <- data.frame(a = 1:26, b = LETTERS)
sqldf("select * from DF where a > 10 limit 3")



On Fri, Jan 8, 2016 at 2:51 AM, Darren Duncan  
wrote:
> Stephen,
>
> What you are arguing for (no shared libraries) is bad old days where one had
> to recompile their programming language to add support for a DBMS, rather
> than the DBMS support being a separately installable library that one could
> choose to install or not or upgrade semi-independently or not, or choose to
> use an alternative or not.
>
> Sure, SQLite is public domain, but why should every language bundle it into
> their core just because?  There are lots of other useful libraries one could
> make the same argument for.  Bundling it can make sense if the language core
> itself depends on SQLite or practically all of its users would use it, but
> that's not usually the case.
>
> I should also point out that the standard Perl interface for SQLite, the
> DBD::SQLite module, bundles the SQLite source with it, so installing that
> Perl library gives you SQLite itself, there are no DLLs or dependence on
> some system SQLite library, but Perl itself doesn't have this built-in nor
> should it.
>
> In the Perl 4 days you had to recompile Perl to make a version that can talk
> to a DBMS, eg "Oraperl", but thankfully with Perl 5 (1994 or so) we did away
> with that.
>
> -- Darren Duncan
>
>
> On 2016-01-07 5:47 PM, Stephen Chrzanowski wrote:
>>
>> I personally wish the reverse.  I wish that these interpreted language
>> engines would incorporate the SQLite code directly into their own
>> existence
>> to avoid having to write wrappers to begin with, except for those wrappers
>> where their method name is "DatabaseOpen" and I prefer "OpenDatabase".
>>
>> SQLite has been around for years, and "R", PHP, Java, Perl, and all these
>> other interpreted new and old style languages have never bothered to
>> incorporate this public domain database engine within itself.  It isn't
>> like the maintainers of these languages don't know it doesn't exist, and
>> if
>> they didn't, then my god they gotta get out from under that rock.  Most
>> web
>> browsers use SQLite for crying out loud.
>>
>> For a few years, I've considered taking the entire amalgamation and
>> porting
>> it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
>> worries about OBJ files, no worries about dependencies, I just include a
>> unit and my app is now database aware.  I know 386 assembly, and I can
>> always read up on other specifications if I needed to.  My problem is that
>> gaming gets in the way.
>>
>> My 2016 wish list for SQLite is that all developers who write for, or use
>> directly or indirectly, any database engine out on the market has a safe
>> and happy 2016 and beyond.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Simon Slavin]

On 8 Jan 2016, at 12:22am, Jim Callahan  
wrote:

> The existing SQLite APIs are correct, but hard to use in the
> sense that creating an interface from an OOIL language is more involved
> than just "wrapping" one by one a set of functions. What I am proposing is
> a second set of APIs that when trivially wrapped for use in an OOIL
> language would result in a function that makes sense to an OOIL programmer

You may be interested to know that the SQLite3 interface to PHP is object 
oriented.  Connections and prepared statements are objects.  Most things you 
can do with them are methods of those objects.

Because every language implements objects its own way, having a low-level 
object-oriented API for SQLite wouldn't help anyone trying to write an OO 
interface for their language.  It would actually be harder for them to handle 
both an SQLite3 object and their own kind of object and 'translate' between 
their needs.  Error-handling, for instance, would be a nightmare since you'd 
have to keep an errored-out SQLite object around and never quite know if you 
could dispose of it yet.

I think things work fine as they are.

Simon.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

Stephen,

What you are arguing for (no shared libraries) is bad old days where one had to 
recompile their programming language to add support for a DBMS, rather than the 
DBMS support being a separately installable library that one could choose to 
install or not or upgrade semi-independently or not, or choose to use an 
alternative or not.

Sure, SQLite is public domain, but why should every language bundle it into 
their core just because?  There are lots of other useful libraries one could 
make the same argument for.  Bundling it can make sense if the language core 
itself depends on SQLite or practically all of its users would use it, but 
that's not usually the case.

I should also point out that the standard Perl interface for SQLite, the 
DBD::SQLite module, bundles the SQLite source with it, so installing that Perl 
library gives you SQLite itself, there are no DLLs or dependence on some system 
SQLite library, but Perl itself doesn't have this built-in nor should it.

In the Perl 4 days you had to recompile Perl to make a version that can talk to 
a DBMS, eg "Oraperl", but thankfully with Perl 5 (1994 or so) we did away with 
that.

-- Darren Duncan

On 2016-01-07 5:47 PM, Stephen Chrzanowski wrote:
> I personally wish the reverse.  I wish that these interpreted language
> engines would incorporate the SQLite code directly into their own existence
> to avoid having to write wrappers to begin with, except for those wrappers
> where their method name is "DatabaseOpen" and I prefer "OpenDatabase".
>
> SQLite has been around for years, and "R", PHP, Java, Perl, and all these
> other interpreted new and old style languages have never bothered to
> incorporate this public domain database engine within itself.  It isn't
> like the maintainers of these languages don't know it doesn't exist, and if
> they didn't, then my god they gotta get out from under that rock.  Most web
> browsers use SQLite for crying out loud.
>
> For a few years, I've considered taking the entire amalgamation and porting
> it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
> worries about OBJ files, no worries about dependencies, I just include a
> unit and my app is now database aware.  I know 386 assembly, and I can
> always read up on other specifications if I needed to.  My problem is that
> gaming gets in the way.
>
> My 2016 wish list for SQLite is that all developers who write for, or use
> directly or indirectly, any database engine out on the market has a safe
> and happy 2016 and beyond.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

Perhaps we misunderstand each other here.

I interpreted your request as if current systems' error outputs at execute time 
were printing out the problematic SQL statement with placeholder names as 
originally prepared, and you wanted the error outputs to have the placeholders 
substituted with literals for the values passed to them at execute time 
interpolated into them.

And so I was saying that continuing to print out the SQL with placeholders was 
correct, and that one can just list the bound values separately / afterwards 
rather than having to rewrite the SQL to interpolate those values.

If your problem is that the output simply says an error occurred and doesn't 
print out the SQL, then fair enough, I misunderstood you.

If my assessment of what you said matches what you intended, then your analogy 
with C is flawed / apples and oranges with what I was talking about.

-- Darren Duncan

On 2016-01-07 5:55 PM, Warren Young wrote:
> On Jan 7, 2016, at 6:04 PM, Darren Duncan  wrote:
>>
>> On 2016-01-07 4:55 PM, Warren Young wrote:
>>> 2. There is no ?preview? mechanism.
>>
>> The current method of binding is correct.  All we really need is that the 
>> debug logging layer include both the SQL of the prepared statement AND a 
>> list of the bound values when the execute failed
>
> By that logic, it would be okay to design a C compiler that emitted only line 
> numbers as error numbers, and gave those line numbers as cpp(1) output line 
> numbers, not source input line numbers.
>
> That is, if stdio.h is 5kSLOC and you make an error on line 5 of your hello 
> world program, it should complain, ?hello.c:5005: error?.
>
> After all, the programmer has all the information necessary to subtract out 
> the #included files? offsets, and then go look at line 5 in the program to 
> determine what went wrong.
>
> SQLite error handling should improve the same way our C and C++ compilers 
> have.
>
> Given: include (missing ?#"!)
>
> Ancient Unix V7 cc says: hello.c:1: Expression syntax.  Yes, very helpful.  
> (Not!)
>
> pcc on the same box spits out about half a dozen errors for that line, none 
> of which tell you what is wrong.
>
> gcc 4 says:
>
> hello.c:1: error: expected ?=?, ?,?, ?;?, ?asm? or ?__attribute__? before 
> ?
> It?s not as pointlessly noisy as pcc, but it?s still not terribly useful.
>
> clang says:
>
>hello.c:1:1: error: unknown type name 'include'
>include 
>^
>
> The arrow points you right at the error.
>
> Wouldn?t it be nice if SQLite were more like clang in this regard?

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Stephen Chrzanowski]

I personally wish the reverse.  I wish that these interpreted language
engines would incorporate the SQLite code directly into their own existence
to avoid having to write wrappers to begin with, except for those wrappers
where their method name is "DatabaseOpen" and I prefer "OpenDatabase".

SQLite has been around for years, and "R", PHP, Java, Perl, and all these
other interpreted new and old style languages have never bothered to
incorporate this public domain database engine within itself.  It isn't
like the maintainers of these languages don't know it doesn't exist, and if
they didn't, then my god they gotta get out from under that rock.  Most web
browsers use SQLite for crying out loud.

For a few years, I've considered taking the entire amalgamation and porting
it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
worries about OBJ files, no worries about dependencies, I just include a
unit and my app is now database aware.  I know 386 assembly, and I can
always read up on other specifications if I needed to.  My problem is that
gaming gets in the way.

My 2016 wish list for SQLite is that all developers who write for, or use
directly or indirectly, any database engine out on the market has a safe
and happy 2016 and beyond.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

At the command line interface (CLI) in SQLite
(and most SQL implementations) is an interpreted
set at a time language with implicit loops.

Efficient low level languages (such as C) process data
a record at a time and the existing API is appropriate
for them.

Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View
or a Query all at once as a data set.
I would count among the OOIL languages: R, Python, Julia Scala,
MatLab/Octave and APL. In a slightly different category would be Java and
C# which are object oriented and arguably interpreted, but are not intended
to be used interactively at a command line with a Read-Evaluate-Print-Loop
(REPL).

The intent of the higher level API is to improve the reliability of the
interfaces. The existing SQLite APIs are correct, but hard to use in the
sense that creating an interface from an OOIL language is more involved
than just "wrapping" one by one a set of functions. What I am proposing is
a second set of APIs that when trivially wrapped for use in an OOIL
language would result in a function that makes sense to an OOIL programmer
and interprets the SQL statements in a manner consistent with the SQLite
CLI (perhaps it could even borrow code from the CLI).

I believe R has remarkably good interface packages for SQLite, but that is
not necessarily the norm across the other OOIL languages.

I am assuming that the higher level API would be hard to use in C because
its up to the programmer to write the low level code while maintaining a
complex abstraction in their head (because C is better suited for creating
abstractions than using them). Header files (.h) would help some but they
would inflate the size of the code and still be hard for the C programmer
to keep track of. So, that's why I see the need for a second higher API
that might be written in C, but would certainly not be used in C!

I am undecided as to whether the higher level API would be useful in Java
or C#.  Java and C# programmers might not be used to implicit loops and
find them not worth the trouble;
whereas R, Python or Julia programer would expect to get an entire table,
view or query all at once.

The higher level API would have to be optional, since it would not be
desirable for a programmer or organization that needs SQLite to run with
the smallest possible footprint on a phone, tablet or Internet of things
(IOT) device.

Just a wishlist idea. No rush for me because I am happy in R and will
probably be moving from SQLite to client server SQL database before I move
from R to Python, Julia or Java.

Jim Callahan
Orlando, FL



This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 7, 2016, at 6:04 PM, Darren Duncan  wrote:
> 
> On 2016-01-07 4:55 PM, Warren Young wrote:
>> 2. There is no ?preview? mechanism.
> 
> The current method of binding is correct.  All we really need is that the 
> debug logging layer include both the SQL of the prepared statement AND a list 
> of the bound values when the execute failed

By that logic, it would be okay to design a C compiler that emitted only line 
numbers as error numbers, and gave those line numbers as cpp(1) output line 
numbers, not source input line numbers.

That is, if stdio.h is 5kSLOC and you make an error on line 5 of your hello 
world program, it should complain, ?hello.c:5005: error?.

After all, the programmer has all the information necessary to subtract out the 
#included files? offsets, and then go look at line 5 in the program to 
determine what went wrong.

SQLite error handling should improve the same way our C and C++ compilers have.

Given: include (missing ?#"!)

Ancient Unix V7 cc says: hello.c:1: Expression syntax.  Yes, very helpful.  
(Not!)

pcc on the same box spits out about half a dozen errors for that line, none of 
which tell you what is wrong.

gcc 4 says:

   hello.c:1: error: expected ?=?, ?,?, ?;?, ?asm? or ?__attribute__? before 
?
  ^

The arrow points you right at the error.

Wouldn?t it be nice if SQLite were more like clang in this regard?

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 7, 2016, at 5:22 PM, Jim Callahan  
wrote:
> 
> I believe R has remarkably good interface packages for SQLite

That?s the appropriate level: the high-level language's DB access layer should 
map the low-level C record-at-a-time API to an appropriate language-level 
abstraction.

R almost forces you to do this because of things like data.frame.  But, that?s 
no argument for other HLL DBMS API writers not to provide similar affordances.

I?ve been involved with two different C++ DBMS wrapper libraries, and both of 
them provide a way to get a std::vector<> as a result set instead of iterate 
over individual rows.  As with R?s SQLite wrapper, I felt it was my C++ code?s 
responsibility to do this repackaging, not the underlying C DBMS access API.

That?s not to say that the SQLite C API has no warts:

1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses 1-based 
indices.  I can cope with either base, but please pick one!  (And make it the 
correct base for programming, 0.  (Yes, I know I just praised R above.  R?s use 
of 1-based arrays is WRONG.))

2. There is no ?preview? mechanism.  That is, you can?t bind some parameters to 
a prepared query string and then get the resulting SQL because SQLite 
substitutes the values into the query at a layer below the SQL parser.  This 
means that if you have an error in your SQL syntax or your parameters cause a 
constraint violation, your debug logging layer can only log the prepared query 
string, not the parameters that went into it, which makes it unnecessarily 
difficult to determine which code path caused you to get the error when looking 
at logs of a running system.

3. The query finalization code could be less picky.  If I prepare a new query 
without finalizing the previous one, I?d rather that SQLite didn?t punish me by 
throwing errors unless I put it into a ?lint? mode.  Just toss the 
half-finished prior query and move on, please.

4. There are several signs of backwards compatible extensions which make the 
API more complex than if it were designed with the features from the start.  
(e.g. _v2() APIs, the various ways to get error codes, etc.)  Hopefully those 
doing the SQLite4 effort will feel free to break the API, jettisoning this 
historical baggage.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

On 2016-01-07 4:55 PM, Warren Young wrote:
> 2. There is no ?preview? mechanism.  That is, you can?t bind some parameters 
> to a prepared query string and then get the resulting SQL because SQLite 
> substitutes the values into the query at a layer below the SQL parser.  This 
> means that if you have an error in your SQL syntax or your parameters cause a 
> constraint violation, your debug logging layer can only log the prepared 
> query string, not the parameters that went into it, which makes it 
> unnecessarily difficult to determine which code path caused you to get the 
> error when looking at logs of a running system.

The current method of binding is correct.  All we really need is that the debug 
logging layer include both the SQL of the prepared statement AND a list of the 
bound values when the execute failed, and optionally a stack trace.  As for 
problems due to syntax errors, those presumably would be caught at prepare time 
or have nothing to do with the execute time values anyway as syntax errors are 
by definition a SQL syntax problem. -- Darren Duncan