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 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
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 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 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 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
prev sibling next 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
prev sibling next sibling parent "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
prev sibling next sibling parent Russel Winder <russel russel.org.uk> writes:
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=

 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,=

 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.

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 next sibling parent "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
prev sibling next sibling parent "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
prev sibling next sibling parent "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
prev sibling 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