Need beta testers for new database library

[Andre Garzia]

Bob,

Let me mix comments below.

On Wed, Aug 8, 2012 at 1:09 PM, Bob Sneidar <bobs at twft.com> wrote:
> I can answer as I purchased sqlYoga and have tested dbLib.
>
> The former allows multiple database connections. It allows you to save those connections to a button (puts info into custom prop) and then load them as you need, or you can simply work with them in memory. You can create other objects as well. There are query objects, table objects, etc. and working with them is simply a matter of calling functions and commands that set or get the parameters of those objects.
>
> dbLib works with one connection at a time. While you can save all the set conditions and restore them later, it is really designed to work with one database connection at a time, and one set of query parameters at a time.

dbLib can work with multiple connections. It has a default connection
that it uses if you don't specify a explicit connection id as a
parameter. Its in the docs! =)

So if you use something like dbGet("contacts") to get your contacts,
you can use dbGet("contacts", myConnectionID) to use a connection that
is not the default one.

So YES dbLib supports multiple connections but it makes it easy to
work with a default one.



>
> When you set conditions on an sqlYoga object, the conditions are persistent throughout multiple queries. dbLib however resets  the conditions after each query. Frankly I find that approach a little odd, but it is workable, since you can save your conditions to an array.
>

The conditions that are reset but the connection is maintained. So if
you do something like:

dbWhere "country", "Brazil"
put dbGet("contacts") into myBrazilianRecordsA
put dbGet("contacts") into AllRecordsA

Then the first dbGet call will return all brazilians because of the
preceding dbWhere but the second dbGet call will return everyone
because the where conditions are reset after each dbGet.

The basic premise is that it makes it real easy to work with simple
queries. For example:

dbWhere "country", "Brazil"
put dbGet("contacts") into myBrazilianRecordsA
dbWhere "country", "USA"
put dbGet("contacts") into myAmericanRecordsA

The code above works because the conditions are reset at each dbGet.
If they were persistent, you would need to keep reseting it yourself.
There is no query object like SQLYoga.


> sqlYoga also has a lot of utility functions. For instance you can get the structure of a table with a simple function call, or get a list of tables in the database without knowing any SQL or the particular syntax for that flavor of SQL. Both do a fairly good job at insulating the developer from having to write his own queries, but complex queries like compound conditionals with mixed and's and or's however will have to be coded and both provide a way to pass complex queries.
>

As you said SQLYoga has more features and I don't aim to write
functions to create really complex queries full of booleans and joins.
Those that know they need such queries can simply write their own SQL
code and pass to the library. For complex queries it is better to
write them yourself than to waste hours trying to debug why the
complex query is generated wrong by the library.

> dbLib is much simpler to get started with. It doesn't bother about the connection itself. It expects you to handle that part. Once you do that, you pass the id to a function which stores it, and all calls after that are made with that id. sqlYoga requires that you create a database object, and then a connection object in memory at least before you can start working with your tables.
>

As I said, dbLib can work with multiple connections. Opening a
connection with revOpenDatabase is pretty simple, I saw no need to
reinvent the wheel there. On the other hand SQLYoga has tons of
features and it makes sense for it to use such objects and minimize
the boilerplate that you need to build.


> sqlYoga has a bit of a learning curve to understand how to work with the custom "objects" that Trevor came up with. Once you get past that you begin to see the advantages of doing things that way. I have a method I use for accessing my database that is used throughout most of the app I am working on, but from time to time I find I need a quick query, and I don't want to have to reset the primary object's parameters and then restore them again. Having the ability to create and optionally save multiple named database objects with sqlYoga is really handy in those situations.
>

In dbLib a method for accessing some records that you want to use over
and over again without the risk of polluting the "query space" is done
in the following way:

function getBrazilianAdults
  put dbPreserveQueryParameters() into whateverIsSetAtTheMomentArray
  dbReset
  dbWhere "country", "Brazil"
  dbWhere "age >", "21"
  put dbGet("contacts") into myReturnValueArray
  dbRestoreQueryParameters whateverIsSetAtTheMomentArray
  return myReturnValueArray
end getBrazilianAdults

With that in place, you can call getBrazilianAdults() anywhere in your
code without the risk of polluting other queries that are being set.
This is a different mindset from SQLYoga and its objects. It is
simpler.

Why you need those preserve/restore/reset calls because without it,
you can have bugs like:

  dbLimit "10"
  put getBrazilianAdults() into myAdults

Without those preserve/restore calls, the dbLimit above the function
call would be "active" during the getBrazilianAdults() execution time
and then only 10 records would be returned from the inner dbGet inside
the function. With those preserve/restore calls,  you basically save
the current query state, reset it, do your stuff and then restore it.

This is much like preserving the defaultfolder. Some libraries need to
change the defaultfolder to work but your stack may expect it not to
be touched so these libraries will save the old defaultfolder to a
variable, set it to the needed folder, work their magic and then
restore the defaultfolder to the previous location. These calls
provide you with the same thing. Instead of hunting for hidden bugs,
you can simply preserve what was set before, do your stuff and restore
it to its earlier state.

An advantage of this approach is how easy it is to make filters.
Suppose you have an interface with checkboxes to filter your datagrid,
like those online shopping catalogs where when buying a laptop you can
filter by CPU, brand, color, etc. With dbLib, each checkbox logic is
pretty easy, its like:

  dbWhere "CPU", "core2duo"

or

  dbWhere "Brand", "Apple"

Those calls will all be active when the eventual refresh is done using dbGet().

Well, this is me just spilling the beans about the new library. It
will be available shortly. I just need to solve some PayPal issues.
(damn PayPal support)

cheers
andre

-- 
http://www.andregarzia.com -- All We Do Is Code.
http://fon.nu -- minimalist url shortening service.