Squeak
  links to this page:    
View this PageEdit this PageUploads to this PageHistory of this PageTop of the SwikiRecent ChangesSearch the SwikiHelp Guide
Database Access
Last updated at 5:57 pm UTC on 31 October 2006
Kamil Kukura: August 05, 2004 When trying to attract web developers from environments such as PHP, ASP or JSP, I'm badly missing a thing to show as how Squeak deals with external databases. Perhaps best way to connect to any DB is to use ODBC which scares me as it relies on FFI (FFI Caveats). .NET people have ADO, Perl/Ruby builds on DBI, Javists rely on JDBC, "C" hard workers stick to ODBC and PHPers go almost native though they also have something: http://adodb.sourceforge.net/

So I'm thinking what would be nice to have to show up with more databases Squeak can natively talk to. Right now I consider doing some work on "SqDBC". At first, it would be a package containing ODBC and ODBCEnh as its default database driver. Then I would like to "grow up" PostgreSQL package in the way it would become SqDBC driver (and upgrade it to protocol 3 as well). Then add some connection pooling to SqDBC. I know it looks quite "javish" as their JDBC but I don't know if is there better "natural" approach.

Yanni Chiu IMHO, Squeak is already well connected to the relational world. It seems to me that you're looking for a portability layer (other than ODBC). Or, maybe you're looking for a web-based CRUD (i.e. Create, Read, Update, Delete) framework...You can issue SQL, and access the result set. Then with Seaside, you can spit out HTML. Can you explain some more about what you expect?

Why is an SqDBC layer needed? If the reason is rdb portability, doesn't ODBC already give you that. IME, you end up building a framework (or use code generation) to access the database anyway; so why shouldn't that framework target the database driver directly, instead of an intermediate layer?

There are three PostgreSQL drivers. Apart from connection pooling and authentication support, the driver that I wrote was never intended to be much more than it already is. It simply sends SQL, and returns a result set. It was expected that some kind of object mapping, code-generation, CRUD package would be built on top. (In fact, I'm working on such a thing).

As for protocol version 3, I looked at it earlier and figured it was not worth doing, just to get perhaps better error recovery and prepared stmt support. If there's interest, I can look at implementing it. Connection pooling would be good. (There's pgpool for PostgreSQL, but it wouldn't be a native Squeak implementation.)

Kamil Kukura: [..Can you explain some more about what you expect?..] Simply put, just growing list of databases we can connect to without disturbing VM with FFI.

[..why shouldn't that framework target the database driver directly, instead of an intermediate layer ] Maybe intermediate layer is not what I meant. It could be rather
abstract class for subclassing a specific RDB driver. When talking about alayer, I would go for some SQL adaptation layer for supporting O/R frameworks (GLORP, ROE) which generate SQL commands.

[There are three PostgreSQL drivers....] I use it and it's quite cool. I like it as a proof that native RDB drivers can be written in Squeak. I think almost all RDBs can be contacted from sockets and I don't see need to go other way.

[..protocol version 3..] Right now version 2 seems OK. For another version I vote for cursor support in resultsets. I'm not sure if PostgreSQL does have support for uni- or bi- directional cursors?
[Marcin Tustin: It does.]

John McIntosh I'm sure I've seen some code that lets you talk to mysql via the socket layer but really you should look at the glorp work. http://www.smalltalkpro.com/squeakextras.html

Beyond the whole issue of read/writing data to the database, you need to make that information into objects over on the smalltalk side. Glorp handles all of this with ease.

Todd Blanchard [re: glorp]Yes, and it works pretty well - but the squeak port works only with Yanni's postgres library. No mysql (not that I care since I have pg)
and no Oracle (which is potentially a biggie).

lex@cc.gatech.edu: FFI seems the proper way to talk to ODBC, and ODBC seems like a direct way to get portable database access. Is fear the only reason not to use FFI, or is there a technical problem as well? Have you tried it? Maybe we just need to smooth the process of getting everything set up?

Avi Bryant In this case there is actually a sound technical reason not to use FFI,
which is that FFI can only support blocking calls, and you really don't want the VM blocking on something as I/O bound as a database query. It means you effectively can't use ODBC in a web setting, or anywhere that you are going to have lots of concurrent queries. It's useful for batch work (massaging/importing/exporting data) but that's about it.

The right thing to do would be to write a plugin that either used an async interface to ODBC (I can't imagine it doesn't provide one), or in the worst case spawns a separate thread for each query.

Ram Krishnan : One reason to implement a Squeak native database connection, i.e. a socket level interface as opposed to a FFI call to a C library, would be to enable multiple threads in the image to execute transactions concurrently. My understanding of the Squeak VM and FFI is that once a Squeak thread descends into a FFI call all other threads in the image will be blocked. This can be undesirable in an environment where the image is servicing multiple client requests, say over the web, that generate independent transactions on the database.

Question :ye juan Does Squeak support connections with PostgreSql or Oracle, or other databases?
Answer Yanni Chiu On SqueakMap there is: PostgreSQL Client for Squeak http://map1.squeakfoundation.org/sm/package/a8d3ca99-f5f4-45e0-9aa7-100a77b64f45 By doing a word search on:http://map1.squeakfoundation.org/sm/packagesbyname I see there's also: MySql Driver, SQLite, ODBC for Squeak. I've never heard of a native Oracle connection, but maybe ODBC will work.

See Databases and Persistence for a master list of Squeak database/persistence options.