RFC: Database API for Go

[Peter Froehlich]

Hi all,

I've put together a draft of a database API for Go. The idea is to put
together the "minimum expectations" for any particular database
package (sqlite3, mysql, etc.) in order to get some amount of
compatibility going. Check out

http://github.com/phf/go-sqlite/blob/master/dbapi.go

if you're interested. I know that some people suggested not going with
a string-based API. Sorry to disappoint you. I don't think anyone has
time to re-architect all of SQL into a huge number of sexy interfaces,
so SQL in strings it is, old school. Comments welcome!

Cheers,
Peter
--
Peter H. Froehlich <http://www.cs.jhu.edu/~phf/>
Senior Lecturer | Director, Johns Hopkins Gaming Lab

[Ben Tilly]

On Wed, Nov 18, 2009 at 8:28 PM, Peter Froehlich
<peter.hans...@gmail.com> wrote:
> Hi all,
>
> I've put together a draft of a database API for Go. The idea is to put
> together the "minimum expectations" for any particular database
> package (sqlite3, mysql, etc.) in order to get some amount of
> compatibility going. Check out
>
>  http://github.com/phf/go-sqlite/blob/master/dbapi.go
>
> if you're interested. I know that some people suggested not going with
> a string-based API. Sorry to disappoint you. I don't think anyone has
> time to re-architect all of SQL into a huge number of sexy interfaces,
> so SQL in strings it is, old school. Comments welcome!

I agree that strings make sense.

One thing that I'd suggest making more obvious is when the API
synchronizes acces to the database. The reason for this is that if
multiple threads try to babble to the database at once over one
connection, the database is likely to get unhappy. Therefore you need
to synchronize access, and should make it clear that access is
synchronized.

However an important question. Why are you passing parameters to
prepare? The APIs that I'm used to prepare the query once, then let
you call execute multiple times, with different parameters each time.

Cheers,
Ben

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 1:33 AM, Ben Tilly <bti...@gmail.com> wrote:
> One thing that I'd suggest making more obvious is when the API
> synchronizes acces to the database.  The reason for this is that if
> multiple threads try to babble to the database at once over one
> connection, the database is likely to get unhappy.  Therefore you need
> to synchronize access, and should make it clear that access is
> synchronized.

Yeah, that's a difficult area. I looked at Eden's mysql interface and
he's doing some locking now, but I am not sure he's handling it all
yet. In the sqlite3 case the added complication is that the database
runs "in process". Right now I am thinking that eventually there'll be
one worker "goroutine" that actually calls C functions while the rest
of the interface will just drop off queries to that goroutine. Not
having played with goroutines yet, I don't have a good idea for the
details yet. :-/ But this also shows that the details of how
concurrency is handled are probably DB specific? But maybe I
misunderstand your question and you were pointing out something
different.

> However an important question.  Why are you passing parameters to
> prepare?  The APIs that I'm used to prepare the query once, then let
> you call execute multiple times, with different parameters each time.

Ah, prepare. Yeah. When I started I looked mostly at Eden's interface
that is inspired by Python. There's no prepare there. When I looked at
the details in SQLite, I noticed that they *require* the prepare step,
there's no simple way around it as in the MySQL C interface (well,
there's sqlite3_exec but that requires callbacks). If you look again,
the "current" part of the interface doesn't even talk about prepare,
it's only in the "advanced" part that I didn't promote to "current"
yet that I mention it. I have my doubts that the complexity needed for
this to work portably will be there soon. I'd rather have a simpler,
working interface than a complex, sexy, and broken one. So the idea
would be something like this I guess (to address your concern):

type Connection interface {
...
Prepare(query string) (statement *Statement, os.Error);
Execute(statement *Statement, parameters ...) (cursor *Cursor, os.Error);
...
}

I think I just had the parameters turned around since I didn't
actually implement this part yet. I'll have to take another look.
Especially I need to decide if all I want to do is string substitution
or if I really want to use the underlying (and more complex) binding
API (this is in the case of sqlite3, I have no idea how mysql would do
it).

Thanks for taking the time to write feedback! :-D

[Peter Froehlich]

I just git pushed a more refined version of the interface. More
comments please. :-D

[Michael Hoisie]

Is there already a working sqlite3 library for go?

I think designing these interfaces should be more "bottom up" --
figure out what works, then put structure and theory on top of it.

- Mike

On Nov 18, 11:52 pm, Peter Froehlich <peter.hans.froehl...@gmail.com>
wrote:

[patla073]

I have been working on a pure go mysql connector for a while. I like
the interface proposed so far.

Some comments:

I prefer to use the name "session" instead of "connection", because I
use the name connection for each single database access thread. I also
prefer the name "resultset" instead of "cursor" (but I don't want to
start a discussion about naming, it's not important).

Also it would be nice to have some kind of iterator for the cursor
that just returns true / false, so you can just step through the
cursor and handle eventual errors afterwards, like:

for cursor.hasMore()
{
cursor.next();
// fetch the current row
}

if (cursor.FetchError() != nil)
{
// for some reason the cursor could not fetch all rows, handle this
error...
}

So I basically just want it to be more JDBC-like :)

Why is prepared statments default? In mysql you can just send the
query string to the server, so It would be strange to force the user
to prepare the query first.

Why not just have the functions:

ExecuteQuery(string) // Default, if the statment must be prepared, the
connector could handle this silently or throw an error

ExecutePrepared(statement) // Fancy option for the more advanced user

I think the connection also should have some basic functions like:

ChangeDefaultDB(string);
ChangeUser(string);
etc...

Best Regards
Patrik

[Myron Alexander]

On Nov 19, 11:23 am, Michael Hoisie <hoi...@gmail.com> wrote:
> I think designing these interfaces should be more "bottom up" --
> figure out what works, then put structure and theory on top of it.

I agree. We should wait for a few implementations such as Oracle, DB2,
MySQL, PostgreSQL and then look at all their needs, error handling
issues etc and then build a standard around known quantities,
especially how the DB interfaces work with GO's structure.

Starting with DBAPI as an experiment is not a bad idea but these
things should not be baked into stone until it fits into GO
comfortably.

On a side-note, this dove-tails nicely into a discussion of standards
and how to host them. Python has PEP, Java has JCP, what should GO
have?

- Myron

[Nathan Baum]

Peter Froehlich wrote:
> Hi all,
>
> I've put together a draft of a database API for Go. The idea is to put
> together the "minimum expectations" for any particular database
> package (sqlite3, mysql, etc.) in order to get some amount of
> compatibility going. Check out
>
> http://github.com/phf/go-sqlite/blob/master/dbapi.go
>
> if you're interested. I know that some people suggested not going with
> a string-based API. Sorry to disappoint you. I don't think anyone has
> time to re-architect all of SQL into a huge number of sexy interfaces,
> so SQL in strings it is, old school. Comments welcome!

I wonder about the number of interfaces used.

On the one hand, it seems like there are a lot of interfaces where
Connection would do.

On the other hand, using interfaces to indicate the capabilities of
the database engine seems like a good idea; a much better idea than
depending upon Version. Then when a new database engine is registered,
anyone can use it.

Open() should be simple. You've actually listed the components of a
URI as what will go in the argument array.

Open("mysql://phf:somepassword@localhost/mydb")
Open("sqlite3:my.db?vfs=whatever")

The Execute method seems to belong to Statement, not Connection. I
wouldn't expect to be able to Prepare a statement on an Oracle
connection and then execute it on a SQLite connection, but the API
lets me try to do that.

The FancyConnection doesn't seem very fancy. Any interface can support
direct executions, even though it may be simulated as a Prepare and
Execute. Do all databases meaningfully support prepared statements?

The TransactionalConnection is missing Begin, I think.

You should move the documentation for InformativeCursor.Description to
where the method is defined.

I don't like the name of PythonicCursor!

Perhaps there should be a ReflectiveCursor for convenient ORM usage:

// In my code
type Post struct {
Title string;
Author string;
Body io.Reader; // The API knows how to stream blobs from the
database, if it's possible.
}

// In the API
type ReflectiveCursor interface {
FetchStruct (type reflect.Type) (interface{}, os.Error)
FetchManyStructs (type reflect.Type, count int) ([]interface{},
os.Error)
FetchAllStructs (type reflect.Type) ([]interface{}, os.Error)
}

// Elsewhere
post := cursor.FetchStruct(typeof(Post))

Although is this possible at the moment? I can't see any way of
_writing_ to a struct via reflect.

[Joubin Houshyar]

Hi Peter,

The proposal you have is fairly close, but still too relational
centric. I suggest you review Sun's JCA specification (specially the
Common Client Interface) and follow suite. Its a generalization of
providing connectors to any external system (EIS in their
terminology). (Sun the made the mistake of starting off with a
relational centric API (JDBC) and came to regret it. JCA was their
correction of that mistake.)

JCA is being used by messaging systems, data bases, and all sorts of
proprietary systems. It also also allows for inclusion of
transaction semantics (which would address some of the points raised
in this thread) for systems that require it. And it is general enough
so that simpler systems (such as K/V datastores) can provide adaptors
without jumping through too many hoops or torturing their semantics.

http://java.sun.com/j2ee/connector/

CCI API:
http://java.sun.com/javaee/5/docs/api/javax/resource/cci/package-summary.html

// analog of your Connection - an 'Interaction':
http://java.sun.com/javaee/5/docs/api/javax/resource/cci/Interaction.html

// Semantics of connectivity and interaction are disambiguated: Here
is 'Connection':
http://java.sun.com/javaee/5/docs/api/javax/resource/cci/Connection.html

// can ignore the container centric jargon and components (such as the
naming service, JNDI)
http://java.sun.com/developer/technicalArticles/J2EE/connectorclient/index.html

/R


On Nov 18, 11:28 pm, Peter Froehlich <peter.hans.froehl...@gmail.com>
wrote:

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 4:23 AM, Michael Hoisie <hoi...@gmail.com> wrote:
> Is there already a working sqlite3 library for go?

For a certain definition of "working" yes, the one I started inspired
by Eden Li's MySQL interface.

> I think designing these interfaces should be more "bottom up" --
> figure out what works, then put structure and theory on top of it.

I am doing both. I am working on the SQLite interface while at the
same time looking around at what other APIs are doing and putting that
into the draft of a standard API.

Cheers,
Peter

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 4:33 AM, patla073 <patl...@gmail.com> wrote:
> I have been working on a pure go mysql connector for a while. I like
> the interface proposed so far.

Ah, another one. Do you have yours somewhere? I know of two MySQL
bindings already:

http://github.com/eden/mysqlgo
http://github.com/yone098/go-mysql

BTW, someone finally started a Postgres effort too, just found that one:

http://github.com/oibore/go-pg

> I prefer to use the name "session" instead of "connection", because I
> use the name connection for each single database access thread. I also
> prefer the name "resultset" instead of "cursor" (but I don't want to
> start a discussion about naming, it's not important).

Yeah, I agree 100% about the cursor stuff. In another thread I pointed
out that "cursor" has somewhat different connotations than what the
interface offers. For example SQL "cursors" seem to allow updating
rows as well. I am more torn about connection and session. They are
both hopelessly overloaded, so I am not sure why one should win. But
it's something to think about. :-D

> Also it would be nice to have some kind of iterator for the cursor
> that just returns true / false, so you can just step through the
> cursor and handle eventual errors afterwards, like:
>
> for cursor.hasMore()
> {
> cursor.next();
> // fetch the current row
> }
>
> if (cursor.FetchError() != nil)
> {
> // for some reason the cursor could not fetch all rows, handle this
> error...
> }
>
> So I basically just want it to be more JDBC-like :)

I get it. I didn't want to add that yet because I am hoping to find a
better way of doing iteration using the channel and range stuff in Go.
I just don't understand those pieces well enough yet to make a good
proposal. If anyone here does, please propose an interface using
channels that could work! :-D

> Why is prepared statments default? In mysql you can just send the
> query string to the server, so It would be strange to force the user
> to prepare the query first.
>
> Why not just have the functions:
>
> ExecuteQuery(string) // Default, if the statment must be prepared, the
> connector could handle this silently or throw an error
>
> ExecutePrepared(statement) // Fancy option for the more advanced user

Actually that's rather arbitrary. Apparently there are plenty of
people used to the "prepare/execute" model and plenty of people used
to the "just execute" model. I started with the latter, then saw how
SQLite does it internally. In terms of modelling, it's the "safer"
thing to offer prepare/execute in the basic interface since it's
obvious how to build "execute directly" out of those primitives. The
other way around you lose potential functionality.

> I think the connection also should have some basic functions like:
>
> ChangeDefaultDB(string);
> ChangeUser(string);
> etc...

I think those are pretty MySQL specific. SQLite for example doesn't
care at all about users and hosts and all that. If there are more
database systems out there that need these operations, let's revisit
this. They could conceivably be collected in another interface that
concrete connections can implement if they want but don't have to.

Thanks for your feedback!

Cheers,
Peter

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 4:34 AM, Myron Alexander
<myron.a...@gmail.com> wrote:
> Starting with DBAPI as an experiment is not a bad idea but these
> things should not be baked into stone until it fits into GO
> comfortably.

I am not trying to fix anything, I am doing exactly what you say,
experimenting. The comment at the top of my dbapi.go hopefully makes
that clear? :-D

> On a side-note, this dove-tails nicely into a discussion of standards
> and how to host them. Python has PEP, Java has JCP, what should GO
> have?

That's a good question. I think Go is just too young to have a formal
process for this stuff yet. There is a review process for getting
stuff into the Go tree though. I guess it just means that for now the
Go crew is 100% in charge of what gets in. Fine by me. :-D

Cheers,
Peter

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 7:19 AM, Nathan Baum
<nat...@parenthephobia.org.uk> wrote:
> Open() should be simple. You've actually listed the components of a
> URI as what will go in the argument array.
>
>    Open("mysql://phf:somepassword@localhost/mydb")
>    Open("sqlite3:my.db?vfs=whatever")

I come from Python, that's where I've done the most with databases. At
least there, it seems that ORMs offer the syntax you suggest, while
database interfaces take separate parameters. I am not sure how much
I'd like to parse URIs at this level, seems that I am already pushing
it by requiring each database interface to parse through a map. But I
am flexible. :-D

> The Execute method seems to belong to Statement, not Connection. I
> wouldn't expect to be able to Prepare a statement on an Oracle
> connection and then execute it on a SQLite connection, but the API
> lets me try to do that.

I was thinking about the Execute stuff. The reason it's in Connection
is that ExecuteDirectly is also a Connection-level operation, and it
has to be since there you don't have a statement. So I just put it in
Connection to have all the execute stuff in one place. I agree about
your observation that it would be safer to move Execute into
Statement. Let me implement that for SQLite and then see what's
better. :-D

> The FancyConnection doesn't seem very fancy. Any interface can support
> direct executions, even though it may be simulated as a Prepare and
> Execute. Do all databases meaningfully support prepared statements?

Work in progress. :-D I just wanted to put ExecuteDirectly somewhere
and that name was the first one that came to mind. I am not sure about
all DBs supported prepared statements. I was under that impression.
But I only checked for SQLite and MySQL.

> The TransactionalConnection is missing Begin, I think.

If so then it's missing from the Python API we started from as well. I
think the idea is that all statements you execute on a connection are
potentially part of a transaction, so you really only have to decide
how things "end" and not how they "begin"?

> You should move the documentation for InformativeCursor.Description to
> where the method is defined.

All the comments are in weird places right now. It's on my TODO list.

> I don't like the name of PythonicCursor!

Better name? Another example of where I just needed a name to group a
bunch of signatures under.

> Perhaps there should be a ReflectiveCursor for convenient ORM usage:
>
>  // In my code
>  type Post struct {
>    Title      string;
>    Author  string;
>    Body     io.Reader; // The API knows how to stream blobs from the
> database, if it's possible.
>  }
>
>  // In the API
>  type ReflectiveCursor interface {
>    FetchStruct (type reflect.Type) (interface{}, os.Error)
>    FetchManyStructs (type reflect.Type, count int) ([]interface{},
> os.Error)
>    FetchAllStructs (type reflect.Type) ([]interface{}, os.Error)
>  }
>
>  // Elsewhere
>  post := cursor.FetchStruct(typeof(Post))
>
> Although is this possible at the moment? I can't see any way of
> _writing_ to a struct via reflect.

Hmm, interesting idea. I've been thinking very little abou the ORM
level so far. Also, truth be told, I've not used a Python ORM, I just
talk to the DB directly in my web apps. :-/

Thank you for the feedback, some good stuff here!

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 11:28 AM, Joubin Houshyar <sun...@gmail.com> wrote:
> The proposal you have is fairly close, but still too relational
> centric.  I suggest you review Sun's JCA specification (specially the
> Common Client Interface) and follow suite.   Its a generalization of
> providing connectors to any external system (EIS in their
> terminology).  (Sun the made the mistake of starting off with a
> relational centric API (JDBC) and came to regret it.  JCA was their
> correction of that mistake.)

I've taken a brief (very brief) look and it seems a little
overwhelming in it's complexity. I've put it on my TODO list, but I
think we all may understand this faster if you take what I have right
now, fork it, and change it to fit with JCA. No need to even write
comments mind you, just make the interfaces the way JCA would imply. I
am sure I'll get to it eventually, but I doubt I'll have the time
before next week. :-/

Thank you for pointing it out!

[Ben Tilly]

On Thu, Nov 19, 2009 at 9:14 AM, Peter Froehlich
<peter.hans...@gmail.com> wrote:
[...]

> I am doing both. I am working on the SQLite interface while at the
> same time looking around at what other APIs are doing and putting that
> into the draft of a standard API.

In that case I recommend adding Perl's DBI interface to your list,
http://search.cpan.org/~timb/DBI/DBI.pm.

It has drivers for every kind of database under the sun, from the
"database" of a collection of CSV files to Oracle. Therefore any
feature it implements is quite likely to be compatible with any
database you want. It maintains a pretty good balance between
offering generic access to any database while making it easy to switch
databases, and several other scripting languages have deliberately
modeled their APIs after that one.

Cheers,
Ben

[Carsten Kuckuk]

Hi all,

Please keep the basics (effective database access hidden behind a
layer of interfaces) separate from other concerns, like ORMs, etc.
These other problems can be solved separately. My experience is, that
simple SQL-92 without triggers, stored procedures, BLOBs, etc. is
what's really important. So what is needed is:
- Interface for a DataProvider: CreateConnection(DataSourceUserIDPassword)
- Interface for Connection: BeginTransaction(), CommitTransaction(),
RollbackTransaction(), ExecuteQuery(string)->ResultSet
- Interface for ResultSet:GetNumberOfColumns, GetColumnNameAt,
GetColumnTypeAt,GetColumnValueAt,MoreResultsAvailable,MoveToNextResult
And for extra points a Helper Class DataProviderManager:
RegisterDataProviderByName, GetDataProviderByName

This is the level of abstraction implemented in SQL-CLI, ODBC, JDBC,
ADO.NET, and it has stayed the same now for decade(s). A system of
interfaces that implements this, without any tricks or sugar would be
immediately helpful.

On top of this, ORMs, Linq-to-XXX, or whatever else is currently in
fashion, can easily be implemented.

Just my 2cents.

Carsten

[Joubin Houshyar]

On Nov 19, 12:46 pm, Peter Froehlich <peter.hans.froehl...@gmail.com>
wrote:
> Hi all,

>
> On Thu, Nov 19, 2009 at 11:28 AM, Joubin Houshyar <suno...@gmail.com> wrote:
> > The proposal you have is fairly close, but still too relational
> > centric.  I suggest you review Sun's JCA specification (specially the
> > Common Client Interface) and follow suite.   Its a generalization of
> > providing connectors to any external system (EIS in their
> > terminology).  (Sun the made the mistake of starting off with a
> > relational centric API (JDBC) and came to regret it.  JCA was their
> > correction of that mistake.)
>
> I've taken a brief (very brief) look and it seems a little
> overwhelming in it's complexity. I've put it on my TODO list, but I
> think we all may understand this faster if you take what I have right
> now, fork it, and change it to fit with JCA. No need to even write
> comments mind you, just make the interfaces the way JCA would imply. I
> am sure I'll get to it eventually, but I doubt I'll have the time
> before next week. :-/

It only looks complex :) imo its actually quite notable for its
clarity given the problem that it is addressing. I'm in the process
of finishing go-redis [1]. Once I'm done with that, I'll take a crack
at a Connector pkg and perhaps modify go-redis as a demonstrative
example.

>
> Thank you for pointing it out!

My pleasure.

/R

[1]: http://github.com/alphazero/Go-Redis

[Peter Froehlich]

On Thu, Nov 19, 2009 at 12:14 PM, Peter Froehlich
<peter.hans...@gmail.com> wrote:
> On Thu, Nov 19, 2009 at 4:23 AM, Michael Hoisie <hoi...@gmail.com> wrote:
>> Is there already a working sqlite3 library for go?
>
> For a certain definition of "working" yes, the one I started inspired
> by Eden Li's MySQL interface.

Actually, I just found another SQLite binding that I wasn't aware of
when I started my work:

http://github.com/kuroneko/gosqlite3

Just wanted to point this out for completeness.

[Nathan Baum]

On Nov 19, 5:39 pm, Peter Froehlich <peter.hans.froehl...@gmail.com>
wrote:

> Hi all,
>
> On Thu, Nov 19, 2009 at 7:19 AM, Nathan Baum
>
> <nat...@parenthephobia.org.uk> wrote:
> > Open() should be simple. You've actually listed the components of a
> > URI as what will go in the argument array.
>
> >    Open("mysql://phf:somepassword@localhost/mydb")
> >    Open("sqlite3:my.db?vfs=whatever")
>
> I come from Python, that's where I've done the most with databases. At
> least there, it seems that ORMs offer the syntax you suggest, while
> database interfaces take separate parameters. I am not sure how much
> I'd like to parse URIs at this level, seems that I am already pushing
> it by requiring each database interface to parse through a map. But I
> am flexible. :-D

An advantage of URIs is that they're easy to make configurable. Almost
any configuration format can accept strings, whilst not all can easily
accept an arbitrary map[string] string.

I don't see an obvious benefit to _not_ using URIs, unless there are
esoteric database engines which require a parameter which is an array
or map.

OTOH, since it's obvious how to convert a URI to a map of the sort the
API uses, you could have an OpenURI function. (Using http.URL for
parsing.)

> > The TransactionalConnection is missing Begin, I think.
>
> If so then it's missing from the Python API we started from as well. I
> think the idea is that all statements you execute on a connection are
> potentially part of a transaction, so you really only have to decide
> how things "end" and not how they "begin"?

I'm used to database engines which default to "auto-commit" mode,
where each statement is implicitly followed by a commit. It's useful
if you need to be able to see side effects of database changes in one
program as you make them in another. I have one program at the moment
which communicates with a (proprietary) daemon on another computer by
inserting jobs into a table in a shared database.

I think it might also be useful to require people to explicitly use
Begin() to start a transaction, if that involves a
TransactionalConnection type assertion. It means they are less likely
to be in a situation where their program is designed to use
transactions, is later connected to a non-transactional database, but
doesn't find that out until it tries to rollback the transaction.
Requiring Begin() before the transaction ensures* that if transactions
are not available on the engine, no work will be done that assumes
they are.

* Except in MySQL, where not all table formats are transactional, and
attempting to begin, commit or rollback on those formats doesn't even
result in an error being reported. Great.

> > I don't like the name of PythonicCursor!
>
> Better name? Another example of where I just needed a name to group a
> bunch of signatures under.

You could name them after how they behave.

To me, "cursor" describes something which can go backward and forward,
and used for both reading and writing. This is a result of having used
Microsoft's ADO a long time ago, and having used many text editors
which have things they call cursors.

Your API's cursors aren't like that. You can only read-and-go-forward
(possibly multiple times). They map rather directly to iterators in
Python. Perhaps they should be called ArrayIterator and MapIterator.
Some might consider Generator to be a nicer term.

There are some even simpler kinds of thing which have the behaviour
specified.

Execute could return a function which returns a row for each
invocation. If one chooses whether to use arrays or maps when
executing or preparing the statement, the function need accept no
arguments. (I imagine that one never wants to fetch some rows as
arrays and some as maps.)

Another option is for Execute to return a channel. This has the
advantage that one can use it directly with for range, which looks
nice:

for x := range query.Execute() {
print(x["foobar"])
}

It has the possibly significant downside that you can't conveniently
propagate errors down the same channel, so the API would presumably
also have to provide a way of reporting fetch errors to the program.
(Perhaps via another channel.)

FetchMany and FetchAll can be implemented as generic channel
operations. That has the advantage that chanops.ReceiveMany(<-chan
template{}, int) []template{} and chanops.ReceiveAll(<-chan template
{}) []template{} can also be used with channels from elsewhere, making
them useful to people who aren't even using the DBAPI.

If the cursors remain cursors, channels are still nice. The idiomatic
Go way to produce a channel from a container seems to be with an Iter
method; that could be added to the Cursor interface.

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 1:35 PM, Ben Tilly <bti...@gmail.com> wrote:
> In that case I recommend adding Perl's DBI interface to your list,
> http://search.cpan.org/~timb/DBI/DBI.pm.

I looked at this, and while it's impressive, I think it's going more
into the direction of an ORM and less in that of a plain database
interface. They call what I am trying to do "database driver" and it's
only one piece of their overall architecture. The interface I am
defining is supposed to be the *driver* interface that things like
Perl's DBI can be built on top of.

Thanks for the pointer though, it was an interesting read. :-D

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 5:16 PM, Nathan Baum
<nat...@parenthephobia.org.uk> wrote:
> An advantage of URIs is that they're easy to make configurable. Almost
> any configuration format can accept strings, whilst not all can easily
> accept an arbitrary map[string] string.
>
> I don't see an obvious benefit to _not_ using URIs, unless there are
> esoteric database engines which require a parameter which is an array
> or map.

I hear you. In fact there was already a TODO note in dbapi.go talking
about maybe switching from map[string]interface{} to just
map[string]string with basically the same reasoning: It's unlikely any
database interface needs something that can't be expressed with a
string. However, there *may* be something like a callback that could
be registered as part of Open(). Of course, that could also be done
later as an operation on Connection. So... I am strongly considering
the URI idea now, especially since a halfway decent parser already
exists for those. Just sad that we have to specify more of the format,
with maps I could be pretty lax. Also maps were a little more uniform,
in URIs different parameters use different syntax. But again, I am
thinking about it.

> I think it might also be useful to require people to explicitly use
> Begin() to start a transaction, if that involves a
> TransactionalConnection type assertion. It means they are less likely
> to be in a situation where their program is designed to use
> transactions, is later connected to a non-transactional database, but
> doesn't find that out until it tries to rollback the transaction.
> Requiring Begin() before the transaction ensures* that if transactions
> are not available on the engine, no work will be done that assumes
> they are.

That's actually a very good point. I need to look into the exact
transaction semantics of SQLite for my own version of this, but I
understand where you're going. So each database interface should
specify if it's in auto-commit mode by default or not, and those that
offer an option should export a type implementing
TransactionalConnection with an explicit begin. Sounds reasonable.
Unless someone else with serious DB experience can find a good reason
to *not* have Begin() as an explicit operation.

> * Except in MySQL, where not all table formats are transactional, and
> attempting to begin, commit or rollback on those formats doesn't even
> result in an error being reported. Great.

That doesn't sound good. :-/ Poor Eden will have to deal with that. :-D

> To me, "cursor" describes something which can go backward and forward,
> and used for both reading and writing. This is a result of having used
> Microsoft's ADO a long time ago, and having used many text editors
> which have things they call cursors.

Oh, I've already granted in some other post that "Cursor" is a bad
name for this thing. I thought you specifically didn't like
"PythonicCursor" which would be fine too, I just would need a better
name to use. It's the best one I could come up with. :-D

> Execute could return a function which returns a row for each
> invocation. If one chooses whether to use arrays or maps when
> executing or preparing the statement, the function need accept no
> arguments. (I imagine that one never wants to fetch some rows as
> arrays and some as maps.)

Yep.

> Another option is for Execute to return a channel. This has the
> advantage that one can use it directly with for range, which looks
> nice:
>
>    for x := range query.Execute() {
>        print(x["foobar"])
>    }

It would be nice to be able to get to this point. I really need to
read up on channels more. Or you need to join me in the API project.
:-D

> It has the possibly significant downside that you can't conveniently
> propagate errors down the same channel, so the API would presumably
> also have to provide a way of reporting fetch errors to the program.
> (Perhaps via another channel.)

I don't know, seems like not too many things can go wrong after you
already have a result set. Of course it's still possible, for example
if someone umounts the filesystem. :-D But I see what you're saying.
Would be nice if there was an existing notion of "error result" on top
of channels. I guess you could always just send interface{} across and
then require a cast in the client to decide between error or data?

I like all of this. Thanks! :-D

[Ben Tilly]

On Thu, Nov 19, 2009 at 5:28 PM, Peter Froehlich
<peter.hans...@gmail.com> wrote:
> Hi all,
>
> On Thu, Nov 19, 2009 at 1:35 PM, Ben Tilly <bti...@gmail.com> wrote:
>> In that case I recommend adding Perl's DBI interface to your list,
>> http://search.cpan.org/~timb/DBI/DBI.pm.
>
> I looked at this, and while it's impressive, I think it's going more
> into the direction of an ORM and less in that of a plain database
> interface. They call what I am trying to do "database driver" and it's
> only one piece of their overall architecture. The interface I am
> defining is supposed to be the *driver* interface that things like
> Perl's DBI can be built on top of.

Are you sure about that? In DBI terms DBI is the database independent
layer, you then have a database driver like DBD::Oracle or DBD::pg
that satisfies DBI's interface. And then people build things like
ORMs on top of DBI. (Quite a bit of which is in the DBIx namespace.)

So when you talk about a common layer that different database drivers
can use, it looks to me like you're building an equivalent to DBI
itself.

> Thanks for the pointer though, it was an interesting read. :-D

I'm glad you found it interesting.

Cheers,
Ben

[Peter Froehlich]

Hi all,

On Thu, Nov 19, 2009 at 8:53 PM, Ben Tilly <bti...@gmail.com> wrote:
> Are you sure about that?  In DBI terms DBI is the database independent
> layer, you then have a database driver like DBD::Oracle or DBD::pg
> that satisfies DBI's interface.  And then people build things like
> ORMs on top of DBI. (Quite a bit of which is in the DBIx namespace.)

Ah, wait. Maybe I misread that. :-/ I thought that you can write your
application to DBI and then switch databases simply by passing a
different string to some method. So all database behave exactly the
same behind DBI (at least in theory). Is that not the case?

[Ben Tilly]

On Thu, Nov 19, 2009 at 5:56 PM, Peter Froehlich
<peter.hans...@gmail.com> wrote:
> Hi all,
>
> On Thu, Nov 19, 2009 at 8:53 PM, Ben Tilly <bti...@gmail.com> wrote:
>> Are you sure about that?  In DBI terms DBI is the database independent
>> layer, you then have a database driver like DBD::Oracle or DBD::pg
>> that satisfies DBI's interface.  And then people build things like
>> ORMs on top of DBI. (Quite a bit of which is in the DBIx namespace.)
>
> Ah, wait. Maybe I misread that. :-/ I thought that you can write your
> application to DBI and then switch databases simply by passing a
> different string to some method. So all database behave exactly the
> same behind DBI (at least in theory). Is that not the case?

That is the case. But a given database driver is allowed to add
anything it wants to its API. So it is easy to write a generic
application. But it is also easy to take advantage of database
specific extensions like stored procedures or bulk upload/downloads.

But not that while DBI exposes a seemingly large API, in the
performance critical parts of your code it tends to be a very thin
layer over the database driver. And the various features that DBI
offers tend to be convenience methods which are implemented over a
small core, and methods which may be found supported in a great
variety of databases.

That said, one huge complication that DBI doesn't have to deal with is
typing. If the database has a field with an int type, it makes sense
to have ints within Go. But DBI can just shove it into a Perl scalar,
and the scalar will automagically convert between string and number
depending on how it is used. For ideas on mapping types in the
database to types in Go you'll need to look at other typed languages,
like Java's JDBC.

Cheers,
Ben

[b00m_chef]

Has anyone looked at implementing something higher-level than a simple
ORM?

See example at: http://www.web2py.com/examples/default/dal

On Nov 19, 9:02 pm, Ben Tilly <bti...@gmail.com> wrote:
> On Thu, Nov 19, 2009 at 5:56 PM, Peter Froehlich
>

> <peter.hans.froehl...@gmail.com> wrote:
> > Hi all,
>
> > On Thu, Nov 19, 2009 at 8:53 PM, Ben Tilly <bti...@gmail.com> wrote:
> >> Are you sure about that?  In DBI terms DBI is thedatabaseindependent

> >> layer, you then have adatabasedriver like DBD::Oracle or DBD::pg

> >> that satisfies DBI's interface.  And then people build things like
> >> ORMs on top of DBI. (Quite a bit of which is in the DBIx namespace.)
>
> > Ah, wait. Maybe I misread that. :-/ I thought that you can write your
> > application to DBI and then switch databases simply by passing a

> > different string to some method. So alldatabasebehave exactly the

> > same behind DBI (at least in theory). Is that not the case?
>

> That is the case.  But a givendatabasedriver is allowed to add

> anything it wants to its API.  So it is easy to write a generic
> application.  But it is also easy to take advantage ofdatabase
> specific extensions like stored procedures or bulk upload/downloads.
>
> But not that while DBI exposes a seemingly large API, in the
> performance critical parts of your code it tends to be a very thin

> layer over thedatabasedriver.  And the various features that DBI

> offers tend to be convenience methods which are implemented over a
> small core, and methods which may be found supported in a great
> variety of databases.
>
> That said, one huge complication that DBI doesn't have to deal with is

> typing.  If thedatabasehas a field with an int type, it makes sense

> to have ints within Go.  But DBI can just shove it into a Perl scalar,
> and the scalar will automagically convert between string and number

> depending on how it is used.  For ideas on mapping types in thedatabaseto types in Go you'll need to look at other typed languages,

[Peter Froehlich]

Hi all,

On Mon, Nov 23, 2009 at 12:28 AM, b00m_chef <ro...@devshell.org> wrote:
> Has anyone looked at implementing something higher-level than a simple
> ORM?
> See example at: http://www.web2py.com/examples/default/dal

That's very impressive but slightly above what I am aiming for at the
moment. Please feel free to hack one of those, it would be cool to
have it! On top of db.go maybe? :-D

[Rodrigo Kochenburger]

b00m_chef, I think something like DAL would definitely be interesting
but it makes sense to have a lower level API to built the abstraction
on top of it.

I'm thinking about start working on something similar to Ruby's sequel
(http://sequel.rubyforge.org/), which seems to be similar to DAL, but
defining a common API for database access is essential and I think
this thread is heading towards the right direction.

Once we have a common interface for database drivers, one can create
an ORM or any other abstraction without needing to worry about
creating it's own drivers or drivers for the existing drivers out
there ;) Having a standard API for database drivers would definitely
be a win and something the community should seek.

Anyway, just my two cents.

Cheers

[Rodrigo Kochenburger]

Peter Froehlich, what would you think about renaming your repository
to go-db, or something generic instead of sqlite-centric, so that
other can simply fork your project and adds new drivers for other
databases? This way we can work on different drivers, discuss the
needs as we develop, and push/pull changes from each other.

Cheers

On Nov 23, 3:28 am, b00m_chef <r...@devshell.org> wrote:

[Eden Li]

On Thu, Dec 10, 2009 at 11:52 AM, Rodrigo Kochenburger <div...@gmail.com> wrote:
> Peter Froehlich, what would you think about renaming your repository
> to go-db, or something generic instead of sqlite-centric, so that
> other can simply fork your project and adds new drivers for other
> databases? This way we can work on different drivers, discuss the
> needs as we develop, and push/pull changes from each other.

He's there: http://github.com/phf/go-db although it's not populated yet.

[Peter Froehlich]

Hi all,

On Thu, Dec 10, 2009 at 2:52 PM, Rodrigo Kochenburger <div...@gmail.com> wrote:
> Peter Froehlich, what would you think about renaming your repository
> to go-db, or something generic instead of sqlite-centric, so that
> other can simply fork your project and adds new drivers for other
> databases? This way we can work on different drivers, discuss the
> needs as we develop, and push/pull changes from each other.

I've tried to do this yesterday but I ran into some git problems (I am
new to it). Basically I am trying to "extract" the generic part into a
new repository without losing history. It's a little more complex than
I thought. But that's the plan, move all the generic parts out of the
sqlite repository.

BTW, we are still making some pretty big changes to the API, so if you
track it at this point, please be ready to (a) talk to us frequently
and (b) follow the changes along every day. If you don't want to do
this, I'd recommend waiting a few more weeks. I actually emailed a
whole bunch of Go/DB people a few days ago about input, but sadly I
didn't get much response, so I thought not too many people are
interested. Good to see that this impression was partially wrong. :-D

[SnakE]

2009/12/10 Peter Froehlich <peter.hans...@gmail.com>

Basically I am trying to "extract" the generic part into a
new repository without losing history. It's a little more complex than
I thought. But that's the plan, move all the generic parts out of the
sqlite repository.

I did that several times and will be glad to help.

[Peter Froehlich]

Hi all,

On Thu, Dec 10, 2009 at 7:54 PM, SnakE <snake...@gmail.com> wrote:
> 2009/12/10 Peter Froehlich <peter.hans...@gmail.com>
>>
>> Basically I am trying to "extract" the generic part into a
>> new repository without losing history. It's a little more complex than
>

> I did that several times and will be glad to help.

Thanks, I think I got it. I cloned the old repository locally twice,
then removed the parts from each that shouldn't be there anymore
(including history), and made two new repositories on github instead
of the old one. So please stop tracking phf/go-sqlite (will go away
soon!) and start tracking

phf/go-db
phf/go-sqlite3

instead. I hope to get back to real work now that it seems I mastered
enough git. :-D