www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - SQLite3 Phobos branch

reply Jesse Phillips <jessekphillips+d gmail.com> writes:
I have a branch of Phobos which includes SQLite 3.7.5 and its bindings 
for D. I have updated the posix.mak to include this when building.

I am posting here at this time to prevent duplicate effort and give a 
heads up that I won't be able to test this for Mac or BSD. I would also 
like to know what I would need to complete to get this ready for a Pull 
Request.

I will work to get this done for Linux and Windows/Wine this weekend.

On another note. The source distribution uses autotools, but it compiles 
fine just by calling GCC. So there might be some interesting flags which 
should be set.

https://github.com/he-the-great/phobos/tree/sqlite3

The binding effort can be attributed to Alexey Khmara, I just added a few 
lines for the update.

https://github.com/bayun/SQLite3-D
Apr 09 2011
next sibling parent Jesse Phillips <jessekphillips+d gmail.com> writes:
Hmm, I'm getting an error compiling dmd for Windows in wine. And 2.053 is 
needed to build druntime/phobos...

make -fwin32.mak C=backend TK=tk ROOT=root OPT= "DEBUG=-D -g -DUNITTEST" 
LFLAGS=-L/ma/co dmd.exe
C:\dm\bin\dmc -c -Ibackend;tk -DMARS -cpp -D -g -DUNITTEST -e -wx -D_DH -
I. backend\cg
	
{ 0,0,0,0,1,1,1,1,1,1,1,0,0,1,1,0,1,0,1,0,0,1,1,1,0,0,1,1,0,0,0,1,1,0,0,
	                                                                      
^
fltables.c(2) : Error: '}' expected
	
{ 0,0,0,0,0,0,0,1,1,0,1,0,0,0,1,0,1,0,0,0,0,1,1,0,0,0,0,1,0,0,0,1,1,0,0,
	                                                                      
^
fltables.c(4) : Error: '}' expected
	
{ -1,-1,-1,1,3,-1,-1,2,2,3,2,1,1,3,2,-1,2,-1,3,-1,-1,2,2,-1,-1,1,-1,2,-1,-1,1,2,2,-1,-1
	                                                                              
      
^
fltables.c(6) : Error: '}' expected
	
{ 0,0,0,1,1,1,1,1,1,1,1,0,0,1,0,0,0,0,0,0,0,0,0,1,0,1,1,1,0,0,0,0,1,0,0,
	                                                                      
^
fltables.c(8) : Error: '}' expected
Apr 09 2011
prev sibling next sibling parent reply Jonathan M Davis <jmdavisProg gmx.com> writes:
 I have a branch of Phobos which includes SQLite 3.7.5 and its bindings
 for D. I have updated the posix.mak to include this when building.
 
 I am posting here at this time to prevent duplicate effort and give a
 heads up that I won't be able to test this for Mac or BSD. I would also
 like to know what I would need to complete to get this ready for a Pull
 Request.
 
 I will work to get this done for Linux and Windows/Wine this weekend.
 
 On another note. The source distribution uses autotools, but it compiles
 fine just by calling GCC. So there might be some interesting flags which
 should be set.
 
 https://github.com/he-the-great/phobos/tree/sqlite3
 
 The binding effort can be attributed to Alexey Khmara, I just added a few
 lines for the update.
 
 https://github.com/bayun/SQLite3-D


If it's just the bindings, then we probably don't need an actual review 
process on the newsgroup - though it'll obviously be reviewed on github before 
being merged in. However, I think that this sort of binding raises a potential 
problem that we need to make sure we're willing to deal with and know how to 
deal with, and that's library versioning.

How stable is SQLite's API? If they put out SQLite 3.7.6, will any of the 
bindings then be invalid? I'm guessing not, since that's likely just a bug fix 
release, but what about 3.8.x? I'd fully expect that SQLite 4 would have major 
changes were it ever released. Does that mean that Phobos then requires the 
user to have a specific version of SQLite installed if they intend to use any 
of the SQLite bindings? If that's the case, then it could make Phobos unusable 
on some systems when SQLite gets upgraded.

If SQLite's API is stable enough (and I think that there's likely a good 
chance that it is), then including bindings for it in Phobos makes good sense, 
and it's a step towards including a D-based database solution in Phobos. But 
if SQLite's API changes much at all, then we could have versioning issues. If, 
on the other hand, SQLite bindings were in a separate library from Phobos 
(even an official one), then we could have different versions of the bindings 
(and different versions of the eventual D database solution) available by 
having different versions of that library, thereby allowing folks to use the 
version of SQLite that's appropriate for their system. But you can't do that 
in Phobos itself.

This is a problem posed by any C bindings that we might want to put into 
Phobos. And as long as the APIs are stable enough, it shouldn't be a problem. 
But any project that's likely to have API changes which would affect the 
bindings could create versioning issues with Phobos' dependencies. So, the 
question is how we want to deal with that and whether SQLite is stable enough 
that such versioning issues are unlikely to be a problem short of SQLite 4.

- Jonathan M Davis
Apr 09 2011
next sibling parent Jesse Phillips <jessekphillips+D gmail.com> writes:
Jonathan M Davis Wrote:


 How stable is SQLite's API? If they put out SQLite 3.7.6, will any of the 
 bindings then be invalid? 


Well, from 3.7.3 to 3.7.5 I added a few enum values and a couple functions. I'd
say SQLite has a fairly stable API.

That said, I did what zlib did and included the source for SQLite as part of
the build for Phobos. And I think this is a great approach because it means
SQLite will always be there and always the supported version.
Apr 09 2011
prev sibling parent reply "Alex Khmara" <alex.khmara gmail.com> writes:
On Sun, 10 Apr 2011 01:19:43 +0300, Jonathan M Davis <jmdavisProg gmx.com>  
wrote:


 If it's just the bindings, then we probably don't need an actual review
 process on the newsgroup - though it'll obviously be reviewed on github  
 before
 being merged in. However, I think that this sort of binding raises a  
 potential
 problem that we need to make sure we're willing to deal with and know  
 how to
 deal with, and that's library versioning.

 How stable is SQLite's API? If they put out SQLite 3.7.6, will any of the
 bindings then be invalid? I'm guessing not, since that's likely just a  
 bug fix
 release, but what about 3.8.x? I'd fully expect that SQLite 4 would have  
 major
 changes were it ever released. Does that mean that Phobos then requires  
 the
 user to have a specific version of SQLite installed if they intend to  
 use any
 of the SQLite bindings? If that's the case, then it could make Phobos  
 unusable
 on some systems when SQLite gets upgraded.

 If SQLite's API is stable enough (and I think that there's likely a good
 chance that it is), then including bindings for it in Phobos makes good  
 sense,
 and it's a step towards including a D-based database solution in Phobos.  
 But
 if SQLite's API changes much at all, then we could have versioning  
 issues. If,
 on the other hand, SQLite bindings were in a separate library from Phobos
 (even an official one), then we could have different versions of the  
 bindings
 (and different versions of the eventual D database solution) available by
 having different versions of that library, thereby allowing folks to use  
 the
 version of SQLite that's appropriate for their system. But you can't do  
 that
 in Phobos itself.

 This is a problem posed by any C bindings that we might want to put into
 Phobos. And as long as the APIs are stable enough, it shouldn't be a  
 problem.
 But any project that's likely to have API changes which would affect the
 bindings could create versioning issues with Phobos' dependencies. So,  
 the
 question is how we want to deal with that and whether SQLite is stable  
 enough
 that such versioning issues are unlikely to be a problem short of SQLite  
 4.

 - Jonathan M Davis


SQLite project pay high attention to compatibility. AFAIK, all changes  
except
first digit are upward compatible - you can take bindings for 3.5.0 and  
link
wih 3.7.6 - all will work as expected, You just will not see new API.

P.S. I updated bindings version to 3.7.6.2 and fixed one bug (see github).
Apr 24 2011
parent reply dsimcha <dsimcha yahoo.com> writes:
On 4/24/2011 3:45 PM, Alex Khmara wrote:

 On Sun, 10 Apr 2011 01:19:43 +0300, Jonathan M Davis
 <jmdavisProg gmx.com> wrote:


 If it's just the bindings, then we probably don't need an actual review
 process on the newsgroup - though it'll obviously be reviewed on
 github before
 being merged in. However, I think that this sort of binding raises a
 potential
 problem that we need to make sure we're willing to deal with and know
 how to
 deal with, and that's library versioning.

 How stable is SQLite's API? If they put out SQLite 3.7.6, will any of the
 bindings then be invalid? I'm guessing not, since that's likely just a
 bug fix
 release, but what about 3.8.x? I'd fully expect that SQLite 4 would
 have major
 changes were it ever released. Does that mean that Phobos then
 requires the
 user to have a specific version of SQLite installed if they intend to
 use any
 of the SQLite bindings? If that's the case, then it could make Phobos
 unusable
 on some systems when SQLite gets upgraded.

 If SQLite's API is stable enough (and I think that there's likely a good
 chance that it is), then including bindings for it in Phobos makes
 good sense,
 and it's a step towards including a D-based database solution in
 Phobos. But
 if SQLite's API changes much at all, then we could have versioning
 issues. If,
 on the other hand, SQLite bindings were in a separate library from Phobos
 (even an official one), then we could have different versions of the
 bindings
 (and different versions of the eventual D database solution) available by
 having different versions of that library, thereby allowing folks to
 use the
 version of SQLite that's appropriate for their system. But you can't
 do that
 in Phobos itself.

 This is a problem posed by any C bindings that we might want to put into
 Phobos. And as long as the APIs are stable enough, it shouldn't be a
 problem.
 But any project that's likely to have API changes which would affect the
 bindings could create versioning issues with Phobos' dependencies. So,
 the
 question is how we want to deal with that and whether SQLite is stable
 enough
 that such versioning issues are unlikely to be a problem short of
 SQLite 4.

 - Jonathan M Davis


 SQLite project pay high attention to compatibility. AFAIK, all changes
 except
 first digit are upward compatible - you can take bindings for 3.5.0 and
 link
 wih 3.7.6 - all will work as expected, You just will not see new API.

 P.S. I updated bindings version to 3.7.6.2 and fixed one bug (see github).


Thank you for your hard work, Alex!

I consider it a no-brainer to include just the bindings and SQLite C 
code in etc right now.  SQLite is arguably the most widely used 
database, is easy to distribute and is public domain.  It would be great 
if this infrastructure was "just there" for whoever wants to build on it.

As far as the wrapper, I would love a simple SQLite wrapper in Phobos, 
since SQLite is the only DB I personally care about.  However, it seems 
others in the community are interested in a more general SQL DB wrapper 
that can be used with a variety of backends.  Now that no GSoC database 
project has been accepted, we need to consider other options for getting 
this done.  I understand that there are a lot of independent attempts, 
but I don't know the status of them or which ones, if any, are targeting 
eventual inclusion in Phobos.
Apr 24 2011
next sibling parent Russel Winder <russel russel.org.uk> writes:
On Sun, 2011-04-24 at 16:40 -0400, dsimcha wrote:
[ . . . ]

 I consider it a no-brainer to include just the bindings and SQLite C=20
 code in etc right now.  SQLite is arguably the most widely used=20
 database, is easy to distribute and is public domain.  It would be great=


=20

 if this infrastructure was "just there" for whoever wants to build on it.
=20
 As far as the wrapper, I would love a simple SQLite wrapper in Phobos,=


=20

 since SQLite is the only DB I personally care about.  However, it seems=


=20

 others in the community are interested in a more general SQL DB wrapper=


=20

 that can be used with a variety of backends.  Now that no GSoC database=


=20

 project has been accepted, we need to consider other options for getting=


=20

 this done.  I understand that there are a lot of independent attempts,=


=20

 but I don't know the status of them or which ones, if any, are targeting=


=20

 eventual inclusion in Phobos.


Is a strong argument that Python has SQLite bindings as standard and is
stronger because of it?

--=20
Russel.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
Dr Russel Winder      t: +44 20 7585 2200   voip: sip:russel.winder ekiga.n=
et
41 Buckmaster Road    m: +44 7770 465 077   xmpp: russel russel.org.uk
London SW11 1EN, UK   w: www.russel.org.uk  skype: russel_winder
Apr 24 2011
prev sibling next sibling parent "Alex Khmara" <alex.khmara gmail.com> writes:
 SQLite project pay high attention to compatibility. AFAIK, all changes
 except
 first digit are upward compatible - you can take bindings for 3.5.0 and
 link
 wih 3.7.6 - all will work as expected, You just will not see new API.

 P.S. I updated bindings version to 3.7.6.2 and fixed one bug (see  
 github).


 Thank you for your hard work, Alex!

 I consider it a no-brainer to include just the bindings and SQLite C  
 code in etc right now.  SQLite is arguably the most widely used  
 database, is easy to distribute and is public domain.  It would be great  
 if this infrastructure was "just there" for whoever wants to build on it.

 As far as the wrapper, I would love a simple SQLite wrapper in Phobos,  
 since SQLite is the only DB I personally care about.  However, it seems  
 others in the community are interested in a more general SQL DB wrapper  
 that can be used with a variety of backends.  Now that no GSoC database  
 project has been accepted, we need to consider other options for getting  
 this done.  I understand that there are a lot of independent attempts,  
 but I don't know the status of them or which ones, if any, are targeting  
 eventual inclusion in Phobos.


I'm familiar with SQLite and MySQL API, but I don't work with another
databases. I can try to make something like "standard interface" for
DB wrappers - at least for simpler cases it will be acceptable, and I
need something like this anyway.

It seems that database and statement objects will be ref-counted
structs (because of need to correctly free resources) - but then we cannot
use interfaces and must go to templates and auto. Does this sound  
reasonable?
I don't like loss of interface's self-documentation, but cannot see  
another way.
Apr 24 2011
prev sibling parent reply Robert Clipsham <robert octarineparrot.com> writes:
On 24/04/2011 21:40, dsimcha wrote:

 However, it seems others in the community are interested in a more
 general SQL DB wrapper that can be used with a variety of backends.
 Now that no GSoC database project has been accepted, we need to
 consider other options for getting this done. I understand that there
 are a lot of independent attempts, but I don't know the status of
 them or which ones, if any, are targeting eventual inclusion in
 Phobos.


I have a general SQL db wrapper (only wrapping SQLite currently), which 
I'd be happy to adapt and submit for phobos, I'm currently working on a 
complete rewrite though, so this may not be a great idea right now.

Example usage:
----
struct Post
{
     int id;
     DateTime time;
     string title;
     string content;
}

auto getPosts(long lim, long offs=0)
{
     with (new SqlQuery)
     {
         select("*").from("blog")
                    .limit(lim)
                    .offset(offs);
         return execute!(Post)();
     }
}
foreach(post; getPosts(10))
{
     // Operate on posts
}
----

The new interface makes the above even simpler, it will look something 
like (rather rough, I'm in the early stages of implementing it):
----
struct Post
{
     int id;
     DateTime time;
     string title;
     string content;
}

auto posts = new SqlitePersister!Post(new SqliteDb("my.db"));
foreach(post; posts[0..10])
{
     // Operate on posts
}
----

Of course, both of these provide (or will provide) an interface to allow 
for SQL statements to be executed directly.

-- 
Robert
http://octarineparrot.com/
Apr 24 2011
parent reply "Alex Khmara" <alex.khmara gmail.com> writes:
On Mon, 25 Apr 2011 01:49:16 +0300, Robert Clipsham  
<robert octarineparrot.com> wrote:


 On 24/04/2011 21:40, dsimcha wrote:

 However, it seems others in the community are interested in a more
 general SQL DB wrapper that can be used with a variety of backends.
 Now that no GSoC database project has been accepted, we need to
 consider other options for getting this done. I understand that there
 are a lot of independent attempts, but I don't know the status of
 them or which ones, if any, are targeting eventual inclusion in
 Phobos.


 I have a general SQL db wrapper (only wrapping SQLite currently), which  
 I'd be happy to adapt and submit for phobos, I'm currently working on a  
 complete rewrite though, so this may not be a great idea right now.

 Example usage:
 ----
 struct Post
 {
      int id;
      DateTime time;
      string title;
      string content;
 }

 auto getPosts(long lim, long offs=0)
 {
      with (new SqlQuery)
      {
          select("*").from("blog")
                     .limit(lim)
                     .offset(offs);
          return execute!(Post)();
      }
 }
 foreach(post; getPosts(10))
 {
      // Operate on posts
 }
 ----

 The new interface makes the above even simpler, it will look something  
 like (rather rough, I'm in the early stages of implementing it):
 ----
 struct Post
 {
      int id;
      DateTime time;
      string title;
      string content;
 }

 auto posts = new SqlitePersister!Post(new SqliteDb("my.db"));
 foreach(post; posts[0..10])
 {
      // Operate on posts
 }
 ----

 Of course, both of these provide (or will provide) an interface to allow  
 for SQL statements to be executed directly.


Is it possible to see code somewhere? It would be interesting to get rid of
raw SQL, but retain access to it's parts - but I'm not sure how many  
constructs
you can handle in this way - for example, what about complex queries with  
joins?
What about subqueries?
Apr 24 2011
parent reply Robert Clipsham <robert octarineparrot.com> writes:
On 25/04/2011 00:19, Alex Khmara wrote:

 Is it possible to see code somewhere?


https://github.com/mrmonday/serenity/blob/master/serenity/SqlQuery.d - 
The code isn't great, it's adapted from D1, and wasn't complete before 
the port.


 It would be interesting to get rid of
 raw SQL, but retain access to it's parts - but I'm not sure how many
 constructs
 you can handle in this way - for example, what about complex queries
 with joins?
 What about subqueries?


I've implemented this kind of thing before with another developer, and 
it's perfectly possible to deal with complex joins and subqueries (he 
implemented that previously). It isn't implemented in the code above 
however, I only did the basics to begin with, and I'm undecided whether 
I'm going to keep the former once the new interface is implemented, I 
haven't thought that far ahead.

-- 
Robert
http://octarineparrot.com/
Apr 24 2011
parent reply "Alex Khmara" <alex.khmara gmail.com> writes:
On Mon, 25 Apr 2011 02:33:44 +0300, Robert Clipsham  
<robert octarineparrot.com> wrote:


 On 25/04/2011 00:19, Alex Khmara wrote:

 Is it possible to see code somewhere?


 https://github.com/mrmonday/serenity/blob/master/serenity/SqlQuery.d -  
 The code isn't great, it's adapted from D1, and wasn't complete before  
 the port.


 It would be interesting to get rid of
 raw SQL, but retain access to it's parts - but I'm not sure how many
 constructs
 you can handle in this way - for example, what about complex queries
 with joins?
 What about subqueries?


 I've implemented this kind of thing before with another developer, and  
 it's perfectly possible to deal with complex joins and subqueries (he  
 implemented that previously). It isn't implemented in the code above  
 however, I only did the basics to begin with, and I'm undecided whether  
 I'm going to keep the former once the new interface is implemented, I  
 haven't thought that far ahead.


Second variant is a kind of ORM, and I think it will be too highlevel for
many cases. But I understand that for web framework it's interesting
direction. As for standard D library, I would like to have something more
generic and traditional. Probably, something modeled from Perl's DBI, but
more D-like.
Apr 24 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Alex Khmara wrote:

 Second variant is a kind of ORM, and I think it will be too highlevel for
 many cases. But I understand that for web framework it's interesting
 direction. As for standard D library, I would like to have something more
 generic and traditional. Probably, something modeled from Perl's DBI, but
 more D-like.


Hi,

Please take a look at https://github.com/pszturmaj/ddb

I also plan to support SQLite3 and MySQL.
Apr 25 2011
parent reply "Alex Khmara" <alex.khmara gmail.com> writes:
On Mon, 25 Apr 2011 12:16:19 +0300, Piotr Szturmaj <bncrbme jadamspam.pl>  
wrote:


 Alex Khmara wrote:

 Second variant is a kind of ORM, and I think it will be too highlevel  
 for
 many cases. But I understand that for web framework it's interesting
 direction. As for standard D library, I would like to have something  
 more
 generic and traditional. Probably, something modeled from Perl's DBI,  
 but
 more D-like.


 Hi,

 Please take a look at https://github.com/pszturmaj/ddb

 I also plan to support SQLite3 and MySQL.


Thank you for posting link to your library.

I think we need to collect different ideas about useful and handy API,
and then try to work out D-style DB interface.

And it's very good that there is somebody who work with Postgres - but
for generic interface we will need to use minimal possibilities set,
present on all databases - or make some "layered" structure, like ranges
(by the way, SELECT result is a forward range by it's nature, so it would
be reasonable to make it conforming to Forward Range specification.

I am sure that there are many good design possibilities, and the more code  
and
ideas we have from different developers, the more chances to work out great
"idiomatic D-style" DB layer. So if anyone else have DB related library in  
D
(apart from those that listed in Wiki and posted in thet thread) - please  
share
your vision.
Apr 25 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Alex Khmara wrote:

 On Mon, 25 Apr 2011 12:16:19 +0300, Piotr Szturmaj
 <bncrbme jadamspam.pl> wrote:


 Alex Khmara wrote:

 Second variant is a kind of ORM, and I think it will be too highlevel
 for
 many cases. But I understand that for web framework it's interesting
 direction. As for standard D library, I would like to have something
 more
 generic and traditional. Probably, something modeled from Perl's DBI,
 but
 more D-like.


 Hi,

 Please take a look at https://github.com/pszturmaj/ddb

 I also plan to support SQLite3 and MySQL.


 Thank you for posting link to your library.

 I think we need to collect different ideas about useful and handy API,
 and then try to work out D-style DB interface.

 And it's very good that there is somebody who work with Postgres - but
 for generic interface we will need to use minimal possibilities set,
 present on all databases - or make some "layered" structure, like ranges
 (by the way, SELECT result is a forward range by it's nature, so it would
 be reasonable to make it conforming to Forward Range specification.


In my Postgres implementation SELECT result is an Input Range, since 
Forward Range implies position saving and that's possible only with cursors.

I think we could loosely follow .NET approach where each client has 
separate class with its own capabilities. On top of that there's a 
wrapper which abstracts all clients to one minimal api set. Actually 
strictly speaking it's under that rather than on top, I mean polymorphic 
DBConnection and its descendands. I think such an approach may be good 
to consider.

Class hierarchy could then look like this:

DBConnection
|
  \
   SqliteConnection
   MySqlConnection
   PostgresConnection
Apr 25 2011
parent "Alex Khmara" <alex.khmara gmail.com> writes:
On Mon, 25 Apr 2011 15:26:44 +0300, Piotr Szturmaj <bncrbme jadamspam.pl>  
wrote:


 In my Postgres implementation SELECT result is an Input Range, since  
 Forward Range implies position saving and that's possible only with  
 cursors.

 I think we could loosely follow .NET approach where each client has  
 separate class with its own capabilities. On top of that there's a  
 wrapper which abstracts all clients to one minimal api set. Actually  
 strictly speaking it's under that rather than on top, I mean polymorphic  
 DBConnection and its descendands. I think such an approach may be good  
 to consider.

 Class hierarchy could then look like this:

 DBConnection
 |
   \
    SqliteConnection
    MySqlConnection
    PostgresConnection


Sorry. Of course, I meant Input Range. My error.
As for hierarchy - I don't know, may be DBConnection will become just
interface - it seems that there is little common code between different  
database
clients hat can be moved into common ancestor.
Apr 25 2011
prev sibling parent Jesse Phillips <jessekphillips+d gmail.com> writes:
On Sat, 09 Apr 2011 18:55:10 +0000, Jesse Phillips wrote:


 ...
 
 https://github.com/he-the-great/phobos/tree/sqlite3
 
 The binding effort can be attributed to Alexey Khmara, I just added a
 few lines for the update.
 
 https://github.com/bayun/SQLite3-D


I've pushed changes to include sqlite3 in the win32 build. However it 
will fail to build due to bug 89 in dmc

http://bugzilla.digitalmars.com/issues/show_bug.cgi?id=89

It also outputs a large number of warnings about some empty for loops.
Apr 10 2011