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

classic Classic list List threaded Threaded
37 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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


<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_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&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Warren Young-2
On Jan 7, 2016, at 5:22 PM, Jim Callahan <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Stephen Chrzanowski
In reply to this post by Warren Young-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Warren Young-2
In reply to this post by Darren Duncan
On Jan 7, 2016, at 6:04 PM, Darren Duncan <[hidden email]> 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 <stdio.h>    (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 ‘<’ token

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 <stdio.h>
  ^

The arrow points you right at the error.

Wouldn’t it be nice if SQLite were more like clang in this regard?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Jim Callahan

On 8 Jan 2016, at 12:22am, Jim Callahan <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Darren Duncan
In reply to this post by Warren Young-2
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 <[hidden email]> 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 <stdio.h>    (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 ‘<’ token
>
> 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 <stdio.h>
>    ^
>
> The arrow points you right at the error.
>
> Wouldn’t it be nice if SQLite were more like clang in this regard?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Darren Duncan
In reply to this post by Stephen Chrzanowski
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith
In reply to this post by Darren Duncan


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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Stephen Chrzanowski
In reply to this post by Darren Duncan
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 <[hidden email]>
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
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Stephen Chrzanowski
In reply to this post by R Smith
On Fri, Jan 8, 2016 at 10:54 AM, R Smith <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Warren Young-2
In reply to this post by Darren Duncan
On Jan 8, 2016, at 12:39 AM, Darren Duncan <[hidden email]> 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.

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Darren Duncan
In reply to this post by Stephen Chrzanowski
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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Darren Duncan
In reply to this post by Warren Young-2
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

dandl
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: [hidden email] [mailto:[hidden email]] On Behalf Of Darren Duncan
Sent: Saturday, 9 January 2016 9:22 AM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jim Callahan
In reply to this post by Warren Young-2
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 <https://en.wikipedia.org/wiki/C_(programming_language)> and
COBOL <https://en.wikipedia.org/wiki/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 <https://en.wikipedia.org/wiki/Result_set>. Individual
columns <https://en.wikipedia.org/wiki/Column_(database)> 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 <[hidden email]> wrote:

> On Jan 7, 2016, at 5:22 PM, Jim Callahan <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_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&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email]> wrote:

> On Thu, 28 Jan 2016 16:47:40 -0500
> Jim Callahan <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3

On 30 Jan 2016, at 8:13pm, Yannick Duchêne <[hidden email]> 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:

<https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12