"Converting" SQLite to DAL?

[Alec Taylor]

I finally completed a successful conversion of my Microsoft Access
database to SQLite 3; after trying numerous scripts on a couple of
platforms.

I used the `.dump` command to create a *.sql file with the `CREATE
TABLE` and associated statements.

Then using the "extract_sqlite_models.py" from the "scripts" folder I
generated some code; but found that what was generated was legacy
database accesses; it didn't generate the modern syntax with Field()
and whatnot.

How do I automate the conversion of the SQLite 3 database to web2py
DAL's syntax?

(furthermore would like to access the current .db from web2py; as
there is data there)

Thanks for all suggestions,

Alec Taylor

[Cliff Kachinske]

Pardon me if I'm telling you things you already know.  For the short version, skip to the bottom two paragraphs.

You can think of a db driven application as having several layers.  The bottom layer is the database engine and the rdbms.  For this simplicity I'm discussing them as one unit, though they are not quite.  This layer stores the data and performs the CRUD operations.  It knows how to do these things.

For what you are trying to do, that would be SQLite.

On top of the rdbms there is a layer that tells the rdbms what to do.  The language it generally uses for this is SQL.  There are statements like INSERT, DELETE, UPDATE.  In the day-to-day operations, those are the most important ones.  This middle layer also transmits data between the rdbms and the top layer.  Some people call this the business rules layer.

The top layer interacts with the (most of the time) human user.  Sometimes this is called the presentation layer.

What Microsoft did with Access is blur the natural and easy distinctions between these layers.  The result is Access, a confusingly mixed bag of rdbms, business rules layer and presentation layer.  They also threw in an IDE.  That all seems really convenient and I guess it's OK if one can solve all one's computer app needs with Access.  But if one ever need to grow beyond it, Access has not taught anything about the Natural Order Of Things.   

So I'm pretty sure your db dump is a collection of SQL commands to create tables in SQLite and populate those tables with the data that was in the Access tables.  

All you have right now is the bottom layer of your application.

I hope you're still with me because the news is actually quite good.  You can use SQLFORM.grid and SQLFORM.smartgrid to manage the data in these tables.  The great thing about smartgrid especially is that it knows about table joins so you still don't have to dig into DAL syntax.

[Alec Taylor]

Hi Cliff,

I no longer have a Micrsoft Access database; I now have a simple
SQLite database.

Will rename the tables so that they work with the conversion script.

> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to web2py+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>