[storm] using odbc as a backend

[Vernon Cole]

I have added several people to this e-mail in order to pull several threads
of thought together so that everyone will have the same information. This
will be something less than an announcement and more than a dream. Perhaps a
confession?...

For the benefit of those who have not been following this thread on the
STORM group , it was started March 6th when Martin DeMello inquired about
connecting to a database using pyodbc and STORM<https://storm.canonical.com/>.
The conversation has discussed adding new database "schemes" to storm in
general, and the two efforts to provide Microsoft SQL Server support in
particular.
My epistle will follow the quotations...
--
VC

On Fri, Mar 6, 2009 at 5:53 PM, James Henstridge <james at jamesh.id.au> wrote:

> On Fri, Mar 6, 2009 at 5:06 PM, Vernon Cole <vernondcole at gmail.com> wrote:
> > Indeed, this is, IMHO, the biggest sticky problem with ADO and/or odbc
> > integration.  Taking care of the database specific stuff is exactly what
> > odbc DOES NOT do. The code we are working on now is specific to using
> odbc
> > or ADO to access a Microsoft SQL database.  If some poor unfortunate user
> > were to try using the same code to get to (for example) an ACCESS
> database,
> > things would mysteriously start to break.
>
> No argument there.  Sounds like a reason to clearly badge the backend
> as MS SQL server support rather than ODBC or ADO support.
>
> >   And what happens if we DO open a postgress or MySQL database using ADO?
> > The code which already knows how to handle those database will not be
> > called!
> >   The storm.database.register_scheme(scheme, factory) design is simply
> not
> > capable of handeling the complexities involved in a user (or programmer)
> > friendly way.  The idea that each scheme uses a single specific api
> > implementation to get to a single specific database server engine will
> not
> > hold up in the real world. For example, if I access a MS-SQL database
> using
> > adodbapi, I use qmark parameter substitution. To get to the same database
> > using the Linux odbc driver, I must switch to the %s variety of
> parameters.
> > Storm would have to know this.
> >   Am I supposed to (in my python application program) ask which operating
> > system I am running on in order to use a different scheme? I think we can
> do
> > better, but I am not sure how. Perhaps we will need to implement some
> sort
> > of environment layer -- sort of like sys.path() -- in order to smooth
> this
> > whole mess out.
>
> While supporting every adapter under the sun for each supported
> database might seem nice, it has a cost.  If there is one Python
> adapter that is clearly the best for a database, does it actually make
> sense not to depend on it for access to the DB?
>
> In the case of proprietary adapters, or if the primary adapter for the
> database isn't portable, it might make sense to support multiple
> adapters but I wouldn't say that should be the default.  What concrete
> benefit do you get from not using psycopg2 or MySQLdb or pysqlite?
>
> James.


James, and group:
  This is indeed the case here, twice.  The adapters used by the most mature
branch of MS-SQL storm are proprietary,
and the other adapter, my own adodbapi, is not portable to Linux, except
perhaps using IronPython on Mono, perhaps, someday.
  So, while I agree that concentrating on the "clearly best" adapter is a
Wise Thing to do, still, I would love to get storm<->adodbapi<->MySQL and
storm<->adodbapi<->progress (alongside of storm<->adodbapi<->MSSQL) working
for a couple of reasons:
  First: Versatility. Adodbapi also works in IronPython and Python 3.0.  It
can be used as a porting tool to get storm working in both of those
environments. ADO is also useful for data mining environments, where a user
may be accessing more than one database engine simultaneously.
  Second: --
.. Errr, Ummm, this will take a bit of time to explain ...

Take a break, go get a cup of coffee, when you come back and have your feet
up, continue reading...

An eternity ago, around 1982, I was a member of a small group who wrote a
fourth generation language system using a non-SQL relational database. SQL
did not work in 60 kbytes of memory on a pdp-11. It was (or is) called RDM.
You've never heard of it. Our goal was to make a system good enough so that
it would be possible to implement the four big accounting applications
(General Ledger, AP, AR, and payroll) without using any 3GL code.

In 1991, my sister asked me about porting an accounting system which she had
been selling and supporting for some years, onto an MS-DOS platform. Data
General Business Basic was getting hard to support. I told her that it could
be done in RDM (which had been ported to MS-DOS by that time) and started
work as her partner. By 1993, working alone, I had completed a marketable
system. Yes, RDM really is that good.

  I made heavy use of a feature which was built into the Digital Equipment
operating systems, but had to be implemented inside of RDM on the MS-DOS
platform: virtual device names. Control tables for the General Accounting
system were in device GA:. Data specific to a single company was in DA:.
Data shared between related companies, such as the table of mailing
addresses, was in AD:.  Medical data transfers to and from our hospital
system using Health Level 7 integration is in HL7:. You get the idea. When
we converted a customer's hospital from MS-DOS on a Novell 3 server, to
Windows 95 on a Microsoft NT-4 server, we simply changed the logical device
mapping table and the entire application went back to work. The hospital
system was down for only a few hours to make the conversion.

In 2001, for reasons unimportant here, our company got into financial
difficulty. I went to work elsewhere. RDM is slowly fading out. Customers
want pretty GUI screens, and the GUI version of RDM leaves a bit to be
desired, such as being a commercial product of a fading company.

Fast forward to today.  I am back at the old stand and want to port the
financial system to a modern platform and make it open source.  Python is
the language of choice, of course. SQL is a necessity. I will have to find
and/or develop similar 4GL facilities to those of RDM, such as python_gui,
for example. STORM seems to be the access method which will make this a
possibility, but I need some kind of logical name facility.  Ergo, I want
one to be added to STORM, so that I will not have to always maintain logical
naming in my new 4GL.

Selfish of me, isn't it?

A logical name facility would also make it much easier to specify which
database is in use under what engine, so that we could implement an ADO
engine for multiple databases. This is called killing two birds with one
stone.

Am I hoping for too much?
--
Vernon Cole
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/storm/attachments/20090308/dcb3c4be/attachment.htm