[squeak-dev] [ANN] - SqueakDBX first stable version 1.0!!!

Congrats! Looks like you guys have been making tons of headways.

One question: Is there a straightforward set of instructions for how to run SqueakDBX on Windows? The only thing I see referenced says "load it from SqueakSource/Universes" and somehow I don't think that this will get me OpenDBX to begin with ;-) A couple of instructions about how to install SqueakDBX and OpenDBX specifically on Windows would be greatly welcome.

Cheers,
 - Andreas

Mariano Martinez Peck wrote:

Hi folks:

After a continuous work since January 08, we are very glad to announce our first stable version of SqueakDBX.

For those who don't know what this is about, the aim of this project is to build an OpenDBX (http://www.linuxnetworks.de/doc/index.php/OpenDBX/) wrapper which will allow users to perform relational database operations (DDL, DML and SQL) through a truly open source library. Through this feature, the squeak community will hopefully be able to interact with major database engines, such as Oracle and SQL Server, besides those which are open source, like PostgreSQL, MySQL or Sqlite. Moreover, by integrating this with GLORP (http://www.glorp.org/), will allow us to generate a complete and open source solution to relational data base access.


Why did we do squeakDBX?

reeThere are several approaches to persistence in Squeak, some very interesting: OODB like Gemstone and magma, image, CouchDB or TokyoT/C, and so on. All of this options get sense if you can actually decide the way you will persist your objects.

However, this is not something that happens very frequently. FREQUENTLY, the client (the one who pays you for making the software) requires you to use a particular persistence strategy (RDBMS). Not only that, but also a database in particular (like Oracle, MS SQL, and so on). They have lots of reasons: they already have license for it, they have support and companies for it, they know SQL, they want to do selects, legacy systems, and so on. But Squeak only provides drivers for MySQL and PostgreSQL natively, so... what would you do in the rest of the cases? move to another language? OK, we don't. We want to program systems in Squeak.

If you know about SqueakDBX you can just see changelog here: http://wiki.squeak.org/squeak/6109; If you don't, you should continue reading ;)

SqueakDBX features:

-Cross-platform support: Linux, Windows (using MinGW) and Mac. See http://wiki.squeak.org/squeak/6108
-Runs on Squeak and Pharo.
-Mini VM blocks when using FFI (asynchronous queries). See http://wiki.squeak.org/squeak/6060#External%20call%20implementation.
-Own SqueakDBX plugin (experimental). Ability to easily change the external call strategy (FFI or our own plugin).
-Support for: Oracle, PostgreSQL, MySQL, MS SQL Server, ODBC and SQLite3
-Transactional concept: begin transaction, commit and rollback. See http://wiki.squeak.org/squeak/6071
-Mappings from String to specific types in selects. See http://wiki.squeak.org/squeak/6075
-Special OpenDBX options: multistatements, encryption, compression, paged results, MySQL modes, and more. See http://wiki.squeak.org/squeak/6068
-Automated database connection ralease (although manual disconnection is recommended ;-)
-Automated results retrieving in order to do another query, after doing a query and not iterating ALL results
-Lots of unit tests that buck up our project ;-) (85 right now). See http://wiki.squeak.org/squeak/6077.
-Lots of benchmarks and comparison with native drivers (PostgreSQL and MySQL). See http://wiki.squeak.org/squeak/6063.
-Error handling: Not only errors, but levels associated with an error in order to avoid FFI calls (if you get a fatal error, it has no sense to do another query and the resources must be free). See http://wiki.squeak.org/squeak/6076
-Query timeout (this is very useful for web applications) and page size can be set for each query. See http://wiki.squeak.org/squeak/6069
-Very completed documentation in wiki and getting started.
-Code critics and SwaLint were run several times.
-Good design (at least all the major refactors demonstrated that).

Full documentation, installation and getting started instructions can be found at wiki page: http://wiki.squeak.org/squeak/6052 . We spent a lot of time in it. It has all the information you may need and is in continuos development.

Benchmarks: We have a lot of SqueakDBX benchmarks and also some for native squeak drivers (PostgreSQL and MySQL) and SqueakDBX seems to be faster than both of them. You can read more here: http://wiki.squeak.org/squeak/6063

Packages can be installed from Universe (3.10) or SqueakMap. Current version is 1.0. Sources can be download from SqueakSource http://www.squeaksource.com/SqueakDBX (it requires FFI installed).
OpenDBX version: 1.4.

Remember that you can compile OpenDBX by yourself or use precompiled binaries. For more information please read: http://wiki.squeak.org/squeak/6129.

GLORP integration: Actually, this may include two parts:

1) The integration of GLORP with squeak is completely hardcoded with PostgreSQL native driver. Because of this, first we will do a refactor in GLORP in order to enable it to support different drivers. We will create a PostgreSQL driver with the things that GLORP already has. We invited Alan Night to have dinner with us when he came to Argentina. We told him our ideas and discuss for a while till we got a first design of this refactor. We have already started this part.
2) Create a SqueakDBX driver for GLORP just like the one we are planning to do for PostgreSQL.

The last squeak port of GLORP is very old and there is nobody to do it. A friend of us, Diogenes Moreira, has accepted this job, so, thanks to him, we hope to have latest GLORP releases in Squeak.
You can see our Glorp progress here: http://wiki.squeak.org/squeak/6132


Help is always wanted. We would really appreciate if you:

-Give us opinions, comments, ideas, new features, complaints and so on.
-Tell us if you find a bug.
-Tell us if you test SqueakDBX with other RDMBS or OS different from the ones we tested. Just to know if it works or not :)

If you try SqueakDBX and you write something somewhere like a blog, let us know. We have this link: http://wiki.squeak.org/squeak/6131 where we put useful links for all the people.

Special thanks to:

-ESUG, for supporting us through Summer of Talk 08;

-To Norbert (author of openDBX) for his help and to all the people who tested it and help us.

Cheers,

SqueakDBX team

------------------------------------------------------------------------

Hi!

Compliments! FMI, is there any support for stored procedures, stored functions and IN OUT parameter passing with MySQL 5.1?

Thanks
Berto

On Mon, Apr 20, 2009 at 7:25 PM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Compliments! FMI, is there any support for stored procedures, stored functions and IN OUT parameter passing with MySQL 5.1?

Thanks! More or less. At least, it is not tested. However, as you can see in: http://wiki.squeak.org/squeak/6106 we plan to do it in a future.
 
When I asked Norbert (openDBX author) about openDBX store procedure support, he told me "You have to enable the multi-statement option and retrieve all result sets using odbx_result. ". This, in SqueakDBX means: DBXConnection #enableMultiStatements and then do #execute: aSQLString.

I guess in this case aSQLString can be the call string to SP, but I really don't know.

So, perhaps we do support SP but we don't know it. Is just a matter of testing. If you want I can google and see how to create a simple SP and see If I can call it and retrieve the results.

Cheers,

Mariano

Thanks
Berto

Hi!

Multistatement is okay, that's just to make sure the API knows what to do. But beware that retrieving TEXT/BLOB fields as parameters sometimes may be far from easy, due to the fact that you have no idea of the actual lenght. PHP basically cannot do it, so I wonder if openDBX can. Let me know.

Berto

2009/4/21 Mariano Martinez Peck <[hidden email]>

On Mon, Apr 20, 2009 at 7:25 PM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Compliments! FMI, is there any support for stored procedures, stored functions and IN OUT parameter passing with MySQL 5.1?

Thanks! More or less. At least, it is not tested. However, as you can see in: http://wiki.squeak.org/squeak/6106 we plan to do it in a future.
 
When I asked Norbert (openDBX author) about openDBX store procedure support, he told me "You have to enable the multi-statement option and retrieve all result sets using odbx_result. ". This, in SqueakDBX means: DBXConnection #enableMultiStatements and then do #execute: aSQLString.

I guess in this case aSQLString can be the call string to SP, but I really don't know.

So, perhaps we do support SP but we don't know it. Is just a matter of testing. If you want I can google and see how to create a simple SP and see If I can call it and retrieve the results.

Cheers,

Mariano

Thanks
Berto

--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.

Dear SqueakDBX team,

this is indeed great news and I hope that now people turn up and give SqueakDBX a try in their production environment. I recall at least 2 Squeakers who where in need of classical RDBMS support for their existing databases and took the freedom cc'ing them.

Frank, Rob, can you give us your impressions on using SqueakDBX in your shop?

Thanks for the good work SqueakDBXers !

/Klaus

On Mon, 20 Apr 2009 22:17:30 +0200, Mariano Martinez Peck wrote:

Hi folks:

After a continuous work since January 08, we are very glad to announce our
first stable version of SqueakDBX.

For those who don't know what this is about, the aim of this project is to
build an OpenDBX (http://www.linuxnetworks.de/doc/index.php/OpenDBX/)
wrapper which will allow users to perform relational database operations
(DDL, DML and SQL) through a truly open source library. Through this
feature, the squeak community will hopefully be able to interact with major
database engines, such as Oracle and SQL Server, besides those which are
open source, like PostgreSQL, MySQL or Sqlite. Moreover, by integrating this
with GLORP (http://www.glorp.org/), will allow us to generate a complete and
open source solution to relational data base access.


Why did we do squeakDBX?

reeThere are several approaches to persistence in Squeak, some very
interesting: OODB like Gemstone and magma, image, CouchDB or TokyoT/C, and
so on. All of this options get sense if you can actually decide the way you
will persist your objects.

However, this is not something that happens very frequently. FREQUENTLY, the
client (the one who pays you for making the software) requires you to use a
particular persistence strategy (RDBMS). Not only that, but also a database
in particular (like Oracle, MS SQL, and so on). They have lots of reasons:
they already have license for it, they have support and companies for it,
they know SQL, they want to do selects, legacy systems, and so on. But
Squeak only provides drivers for MySQL and PostgreSQL natively, so... what
would you do in the rest of the cases? move to another language? OK, we
don't. We want to program systems in Squeak.

If you know about SqueakDBX you can just see changelog here:
http://wiki.squeak.org/squeak/6109; If you don't, you should continue
reading ;)

SqueakDBX features:

-Cross-platform support: Linux, Windows (using MinGW) and Mac. See
http://wiki.squeak.org/squeak/6108
-Runs on Squeak and Pharo.
-Mini VM blocks when using FFI (asynchronous queries). See
http://wiki.squeak.org/squeak/6060#External%20call%20implementation.
-Own SqueakDBX plugin (experimental). Ability to easily change the external
call strategy (FFI or our own plugin).
-Support for: Oracle, PostgreSQL, MySQL, MS SQL Server, ODBC and SQLite3
-Transactional concept: begin transaction, commit and rollback. See
http://wiki.squeak.org/squeak/6071
-Mappings from String to specific types in selects. See
http://wiki.squeak.org/squeak/6075
-Special OpenDBX options: multistatements, encryption, compression, paged
results, MySQL modes, and more. See http://wiki.squeak.org/squeak/6068
-Automated database connection ralease (although manual disconnection is
recommended ;-)
-Automated results retrieving in order to do another query, after doing a
query and not iterating ALL results
-Lots of unit tests that buck up our project ;-) (85 right now). See
http://wiki.squeak.org/squeak/6077.
-Lots of benchmarks and comparison with native drivers (PostgreSQL and
MySQL). See http://wiki.squeak.org/squeak/6063.
-Error handling: Not only errors, but levels associated with an error in
order to avoid FFI calls (if you get a fatal error, it has no sense to do
another query and the resources must be free). See
http://wiki.squeak.org/squeak/6076
-Query timeout (this is very useful for web applications) and page size can
be set for each query. See http://wiki.squeak.org/squeak/6069
-Very completed documentation in wiki and getting started.
-Code critics and SwaLint were run several times.
-Good design (at least all the major refactors demonstrated that).

Full documentation, installation and getting started instructions can be
found at wiki page: http://wiki.squeak.org/squeak/6052 . We spent a lot of
time in it. It has all the information you may need and is in continuos
development.

Benchmarks: We have a lot of SqueakDBX benchmarks and also some for native
squeak drivers (PostgreSQL and MySQL) and SqueakDBX seems to be faster than
both of them. You can read more here: http://wiki.squeak.org/squeak/6063

Packages can be installed from Universe (3.10) or SqueakMap. Current version
is 1.0. Sources can be download from SqueakSource
http://www.squeaksource.com/SqueakDBX (it requires FFI installed).
OpenDBX version: 1.4.

Remember that you can compile OpenDBX by yourself or use precompiled
binaries. For more information please read:
http://wiki.squeak.org/squeak/6129.

GLORP integration: Actually, this may include two parts:

1) The integration of GLORP with squeak is completely hardcoded with
PostgreSQL native driver. Because of this, first we will do a refactor in
GLORP in order to enable it to support different drivers. We will create a
PostgreSQL driver with the things that GLORP already has. We invited Alan
Night to have dinner with us when he came to Argentina. We told him our
ideas and discuss for a while till we got a first design of this refactor.
We have already started this part.
2) Create a SqueakDBX driver for GLORP just like the one we are planning to
do for PostgreSQL.

The last squeak port of GLORP is very old and there is nobody to do it. A
friend of us, Diogenes Moreira, has accepted this job, so, thanks to him, we
hope to have latest GLORP releases in Squeak.
You can see our Glorp progress here: http://wiki.squeak.org/squeak/6132


Help is always wanted. We would really appreciate if you:

-Give us opinions, comments, ideas, new features, complaints and so on.
-Tell us if you find a bug.
-Tell us if you test SqueakDBX with other RDMBS or OS different from the
ones we tested. Just to know if it works or not :)

If you try SqueakDBX and you write something somewhere like a blog, let us
know. We have this link: http://wiki.squeak.org/squeak/6131 where we put
useful links for all the people.

Special thanks to:

-ESUG, for supporting us through Summer of Talk 08;

-To Norbert (author of openDBX) for his help and to all the people who
tested it and help us.

Cheers,

SqueakDBX team

--
"If at first, the idea is not absurd, then there is no hope for it". Albert Einstein

On Tue, Apr 21, 2009 at 2:04 AM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Multistatement is okay, that's just to make sure the API knows what to do. But beware that retrieving TEXT/BLOB fields as parameters sometimes may be far from easy, due to the fact that you have no idea of the actual lenght. PHP basically cannot do it, so I wonder if openDBX can. Let me know.

As I can figure out from your email, you know far much more than I in SP. However, I think we are mixing two things:

1) SP support: This SHOULD be as I told you: enableMultistatement and just query the resutls. But, it was never tested so we don't know if it works. I don't know how input and oput variables should be managed. I will give it a try.

2) Long fields support: OpenDBX support large objects (TEXT/BLOC...). We (right now) do not. This is also planned. You can see it here: http://wiki.squeak.org/squeak/6106
We have to map from string (openDBX) to objects (squeak). You can read here: http://wiki.squeak.org/squeak/6075 how we do this.

There it says:

"Large objects like CLOB, NCLOB and BLOB are not still supported. However, this is actually not true. OpenDBX has several functions to use a RDBMS. And, it also has another functions to specially manage large objects. But OpenDBX doesn't ALWAYS use those special functions for large objects. It depends on the backend. Some backends need to use those functions, and with others you can just use the standard functions. So, what SqueakDBX actually doesn't support right now are those special functions for large objects. Thus, it doesn't support large objects for the backends that need the special functions. For more information, see http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/1.2.
Example: If you see DBXPlatform #createDataTypesMap you will see that CLOB is not supported. But for Sqlite3 you can use the normal functions, so, actually squeakDBX supports that. DBXSqlitePlatform overrides that method and map a CLOB to a String.
"


Once we get 1) and 2) working, I think we can have the mix of them: SP that use large objects.

What do you think ?


 

Berto

2009/4/21 Mariano Martinez Peck <[hidden email]>

On Mon, Apr 20, 2009 at 7:25 PM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Compliments! FMI, is there any support for stored procedures, stored functions and IN OUT parameter passing with MySQL 5.1?

Thanks! More or less. At least, it is not tested. However, as you can see in: http://wiki.squeak.org/squeak/6106 we plan to do it in a future.
 
When I asked Norbert (openDBX author) about openDBX store procedure support, he told me "You have to enable the multi-statement option and retrieve all result sets using odbx_result. ". This, in SqueakDBX means: DBXConnection #enableMultiStatements and then do #execute: aSQLString.

I guess in this case aSQLString can be the call string to SP, but I really don't know.

So, perhaps we do support SP but we don't know it. Is just a matter of testing. If you want I can google and see how to create a simple SP and see If I can call it and retrieve the results.

Cheers,

Mariano

Thanks
Berto

--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.

Once we get 1) and 2) working, I think we can have the mix of them: SP that use large objects.

What do you think ?

Hi!

The reason I make so many enquiries about long objects (as you probably guessed) is that I need them for a project. I did a number of experiments with more traditional interfaces and they mostly failed to retrieve an IN OUT TEXT parameter from an SP. Functions weren't used because functions do not allow for transaction management (at least in MySQL) and I'm sending the DB a list of abstract XML commands that are to be encapsulated into a single transaction.

I can obviously use tricks (like generating a numeric index for a later SELECT of the value) but this all has a performance fee I'd rather not pay. Most of the bad benchmarks applications get from using RDBMS come from the huge number of interactions they need with the DB, so I'm really trying hard to make it all in one shot. The idea is to send in a potentially huge XML string that contains all the needed operations, parse it and process it with a local stored procedure and return a result in a single go.

To make an insulation layer I eventually resorted to python-twisted, so that GUIs do not even know they are talking to a RDBMS and if tomorrow we want to switch technology we can do it directly from the python daemon. This is a vital need since the daemon doesn't only talk to SmallTalk, but also to legacy systems for repository updates and there are other developing parties who are willing to develop their own non-SmallTalk UIs. If we end up with loads of connection strings all over this structure we are going to be paralyzed pretty soon, let alone the problem of having passwords stored in clear in way too many places.

The problem I usually saw with IN OUT parms is that you simply don't know how long the object is and most retrieval strategies need exactly that to fecth the content. There must be other ways, since internally SPs exchange these parms pretty well, but C based APIs (like PHP) usually fail at this task.

I'm currently porting my system to gentoo, so I'll be fairly busy in the next 3-4 days (first gentoo install! so maybe I'm too optimistic on timing), but after that I'll give OpenDBX a try and report the results.

Berto

2009/4/21 Mariano Martinez Peck <[hidden email]>

On Tue, Apr 21, 2009 at 2:04 AM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Multistatement is okay, that's just to make sure the API knows what to do. But beware that retrieving TEXT/BLOB fields as parameters sometimes may be far from easy, due to the fact that you have no idea of the actual lenght. PHP basically cannot do it, so I wonder if openDBX can. Let me know.

As I can figure out from your email, you know far much more than I in SP. However, I think we are mixing two things:

1) SP support: This SHOULD be as I told you: enableMultistatement and just query the resutls. But, it was never tested so we don't know if it works. I don't know how input and oput variables should be managed. I will give it a try.

2) Long fields support: OpenDBX support large objects (TEXT/BLOC...). We (right now) do not. This is also planned. You can see it here: http://wiki.squeak.org/squeak/6106
We have to map from string (openDBX) to objects (squeak). You can read here: http://wiki.squeak.org/squeak/6075 how we do this.

There it says:

"Large objects like CLOB, NCLOB and BLOB are not still supported. However, this is actually not true. OpenDBX has several functions to use a RDBMS. And, it also has another functions to specially manage large objects. But OpenDBX doesn't ALWAYS use those special functions for large objects. It depends on the backend. Some backends need to use those functions, and with others you can just use the standard functions. So, what SqueakDBX actually doesn't support right now are those special functions for large objects. Thus, it doesn't support large objects for the backends that need the special functions. For more information, see http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/1.2.
Example: If you see DBXPlatform #createDataTypesMap you will see that CLOB is not supported. But for Sqlite3 you can use the normal functions, so, actually squeakDBX supports that. DBXSqlitePlatform overrides that method and map a CLOB to a String.
"


Once we get 1) and 2) working, I think we can have the mix of them: SP that use large objects.

What do you think ?


 

Berto

2009/4/21 Mariano Martinez Peck <[hidden email]>

On Mon, Apr 20, 2009 at 7:25 PM, Bèrto ëd Sèra <[hidden email]> wrote:

Hi!

Compliments! FMI, is there any support for stored procedures, stored functions and IN OUT parameter passing with MySQL 5.1?

Thanks! More or less. At least, it is not tested. However, as you can see in: http://wiki.squeak.org/squeak/6106 we plan to do it in a future.
 
When I asked Norbert (openDBX author) about openDBX store procedure support, he told me "You have to enable the multi-statement option and retrieve all result sets using odbx_result. ". This, in SqueakDBX means: DBXConnection #enableMultiStatements and then do #execute: aSQLString.

I guess in this case aSQLString can be the call string to SP, but I really don't know.

So, perhaps we do support SP but we don't know it. Is just a matter of testing. If you want I can google and see how to create a simple SP and see If I can call it and retrieve the results.

Cheers,

Mariano

Thanks
Berto

--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.

--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.