Re: Linked Data with web2py

I think we are talking apples and oranges.

According to Microsoft, who proposed ODBC,

      http://msdn.microsoft.com/en-us/library/aa198030%28SQL.80%29.aspx

"The driver passes SQL statements to SQL Server and returns the  
results of the statements to the application."

The purpose of the web2py DAL is that of generating the SQL statements  
(in the case of RDBS) and it does not care whether the connection is  
established via ODBC or not.

We do not use ODBC on Oracle, for example, but we use cx_Oracle  
instead because, according to this is a quote from the Oracle web page:

    http://www.oracle.com/technology/pub/articles/rubio-python-turbogears.html

"cx_Oracle serves as the core and lowest-level API for bridging a  
Python environment to an Oracle database. cx_Oracle is to Python what  
an Oracle JDBC driver is to Java, enabling an application to execute  
raw SQL queries and manipulate database cursors with a fine level of  
detail."

We could use pyODBC on Oracle as we do with with DB2 but the  
installation process would be more cumbersome.

ODBC does a lot but in the end it just gives a connection and a cursor  
object. It does not give us complete database abstraction which is  
what the web2py DAL does.

One of the most important issue from our prospective is that the  
web2py DAL works with Non Relational Databases too. Currently only  
Google Big Table on the Google App Engine but we are working on  
MongoDB and CouchDB support.  ODBC does not work with non-relational  
databases.

Running on the Google App Engine cloud has some limitations. We can  
only upload pure Python or pure Java programs, no C/C++ modules or pre- 
compiled modules. We can only use the Google API or something that  
they call GQL. There is no SQL on GAE because it is not a relational  
database. Hence, ODBC is not option. Building our system on ODBC  
simply would not cut it. Yet the Google App Engine is the single best  
thing that happened to the world of web development in some time  
(practically free, unlimited scalability, zero maintenance, no messing  
up with server or database administration). We use ODBC when we can  
but we are interested in pushing our technology in places where ODBC  
is not an option.

Least but not last I want to make the case that everybody is doing web  
apps with database connections these days. This is now off-the-shelf  
technology and people should not pay for it. Most ODBC drivers are  
commercial products or require a commercial license for enterprise  
use. Our Database Abstraction Layer is released under the GPL2  
license, only requires third party GPL or BSD drivers, and has been  
tested by more than 1500 registered users and 50 developers.


Massimo


On Dec 24, 2009, at 9:30 AM, Kingsley Idehen wrote:

> Massimo Di Pierro wrote:
>> Hi Kingsley,
>>
>> Internally we use the Python ODBC driver for the connection to DB2  
>> and
>> to MSSQL.
> But how have you come to conclude that ODBC only works with DB2 and
> MSSQL? What about ODBC Drivers that have been developed to work
> consistently across:
>
> Virtuoso, Oracle, SQL Server, DB2, Informix, Ingres, Sybase, Progress,
> MySQL, Firebird, other ODBC Drivers, JDBC Drivers etc? Thus, you could
> have the option ODBC as opposed to: ODBC for DB2 and MS SQL Sever.
>
> What about the fact that you have ODBC Drivers for Virtuoso that  
> enable
> to make one ODBC connection to all the DBMS engines above, and treat
> their tables as though they were part of one DBMS i.e., Virtual DBMS
> based Relational Data Virtualization (or Federation).
>
>> We use more specific drivers for the other databases, as recommended
>> by the official documentation.
> What official documentation? URL please.
>> Anyway, from the Python programming point of view they all expose the
>> same Python API.
>>
>> The fact is ODBC only unifies the data access API and that is a small
>> part of we needed since different database still have different  
>> dialects.
>> Our DAL completely abstracts the database and writes the SQL in the
>> specific dialect of specific backend.
> Are you aware of the fact that via ODBC metadata calls you can
> deductively discern the capabilities and SQL dialiects supported by an
> DBMS. This is all about the ODBC Drivers you are working with, not the
> ODBC spec itself (which is vast and very sophisticated).
>>
>> For example a limit query in SQLite is done with "SELECT ...  FIRST  
>> N"
>> The same query in MSSQL is done with "SELECT .... TOP N", in Oracle  
>> it
>> requires two nested selects "SELECT ... FROM (SELECT w_tmp.*, ROWNUM
>> w_row FROM (SELECT ...) w_tmp WHERE ROWNUM<=N)...;".  In the case of
>> the Google App Engine there is not even SQL so our DAL translates
>> directly into Google API calls.
> Again, you should focus on the functionality you want, and then see if
> the underling ODBC Driver can handle what you want, if it can't you  
> can
> drop back to your work arounds.
>
>>
>> The same query in the web2py DAL reads like, for example:
>>
>>    db=DAL('postgresql://username:password@localhost', pool_size=100)
>>    db.define_table('person',Field('name'))
>>    db.person.insert(name='Ken')
>>    rows = db(db.person.name=='Ken').select(limitby = (0,10))
>>
>> The first line choose the most appropriate connection driver (which
>> may be ODBC). The second line tried to define a table "person". If it
>> does not exist, it is CREATEd. If it exists and has a different
>> stricture, it is ALTERed. The third line inserts a second. The forth
>> line is performs the limit query.
>>
>> As you can see we do not use raw SQL anywhere in our system, although
>> our system may use SQL to talk to the database. Using raw SQL also
>> presents the disadvantage that, if one is not very careful in  
>> escaping
>> variables, one may introduce SQL Injections vulnerabilities. We do  
>> not
>> have to worry about that with the DAL.
> I do understand the abstraction.
> I worked with a product called DAL [1] for Mac OS Classic in the early
> 1990's  (and others pre ODBC, which arrived in 1992 as Windows port of
> what was then the SAG CLI) :-)
>>
>> The RDF tagging is done at the DAL level:
>>
>>    db.person.name.rdf = { .... }
>>
>> Anyway, it is possible there is some feature of ODBC that we have
>> overlooked and we may be able to take  advantage of.
> Yes.
>
> You have ODBC itself. Then you have Virtuoso VDBMS (think scalable  
> high
> performance variant of Microsoft Access JET Engine which major  
> benefits).
>
>
> Kingsley
>>
>> Massimo
>>
>> On Dec 24, 2009, at 6:57 AM, Kingsley Idehen wrote:
>>
>>> Massimo Di Pierro wrote:
>>>> Hello everybody,
>>>>
>>>> I am a new member of this list and first of all I wish everybody  
>>>> Happy
>>>> Holidays.
>>>>
>>>> I also take the occasion to introduce the RDF plugin for web2py.
>>>>
>>>>   http://web2py.com/semantic
>>>>
>>>> web2py is an Open Source web framework for rapid application
>>>> development written in Python and programmable in Python. web2py  
>>>> runs
>>>> almost everywhere and includes everything you need for web  
>>>> development
>>>> in one package including a ssl-enabled web server, a transaction- 
>>>> safe
>>>> relational database, a web-based IDE, a web-based database
>>>> administration tool, and a Database Abstraction Layer that writes  
>>>> SQL
>>>> for you and works transparently on SQLite, MySQL, Postgresql,  
>>>> Oracle,
>>>> MSSQL, FireBird, DB2, Informix, Ingres, and on Google App Engine.
>>>>
>>>> Web2py requires no installation: just download, unzip and click to
>>>> start it.
>>>> You can see an online demo here:
>>>>
>>>>  http://web2py.com/demo_admin
>>>>
>>>> The RDF plugin allows to label (tag) database fields and relations
>>>> with ontologies and web2py automatically exposes the data in the
>>>> database as Linked Data via a RESTful web service. Works with all
>>>> database back-ends listed above.
>>>>
>>>> Any comment and feedback will be appreciated.
>>> Any reason why you don't use ODBC for your data access? Your  
>>> references
>>> above imply you implemented data access APIs on a DBMS by DBMS  
>>> basis.
>>> ODBC is not only superior to all Native DBMS APIs, it is generic  
>>> thereby
>>> shrinking you development and maintenance costs.
>>>
>>> Kingsley
>>>>
>>>> Massimo
>>>>
>>>> -------------------------------------------------------
>>>> Massimo Di Pierro
>>>> Associate Professor
>>>> School of Computing and Digital Media
>>>> DePaul University
>>>> 243 S Wabash Ave
>>>> Chicago IL 60604
>>>> +1-312-375-6536 (phone)
>>>> +1-312-375-6116 (fax)
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>> -- 
>>>
>>>
>>> Regards,
>>>
>>> Kingsley Idehen          Weblog: http://www.openlinksw.com/blog/ 
>>> ~kidehen
>>> President & CEO
>>> OpenLink Software     Web: http://www.openlinksw.com
>>>
>>>
>>>
>>>
>>
>>
>
>
> -- 
>
>
> Regards,
>
> Kingsley Idehen	      Weblog: http://www.openlinksw.com/blog/~kidehen
> President & CEO
> OpenLink Software     Web: http://www.openlinksw.com
>
>
>
>

Received on Thursday, 24 December 2009 16:34:48 UTC