MVC: Clarification and Advice

Rich · February 15, 2015, 12:46am

It's been a while since I tried to wrap my head around the MVC pattern,
but I think that this -- or a recommended better alternative -- is needed
for an application I'm developing. I would like clarification of my
understanding of MVC and advice on how to achieve what I need.

There will be two versions of the application: a single-user version with
a SQLite3 back end and a multi-user version with a PostgreSQL back end; the
'Model' in the MVC pattern. Both versions use the same wxPython UI code, the
'View' in the MVC. The Python middleware (the 'Controller') requires two
versions with the same basic structure but one using embedded SQLite3 syntax
and pysqlite, the other using SQLAlchemy and psycopg.

Is this an appropriate way to structure the application? Any and all
suggestions, advice, and thoughts are solicited so feel free to comment.

TIA,

Rich

Karsten_Hilbert · February 15, 2015, 1:10am

There will be two versions of the application: a single-user version with
a SQLite3 back end and a multi-user version with a PostgreSQL back end; the
'Model' in the MVC pattern. Both versions use the same wxPython UI code, the
'View' in the MVC. The Python middleware (the 'Controller') requires two
versions with the same basic structure but one using embedded SQLite3 syntax
and pysqlite, the other using SQLAlchemy and psycopg.

Since SQLAlchemy offers an sqlite backend you shouldn't need to
write separate storage access layers.

Karsten

Rich · February 15, 2015, 1:18am

Karsten,

Ah, I did not realize that. Thanks for pointing me to this because it
looks like the best way to go for both versions.

Much appreciated,

Rich

···

On Sun, 15 Feb 2015, Karsten Hilbert wrote:

Since SQLAlchemy offers an sqlite backend you shouldn't need to write
separate storage access layers.

Werner2 · February 15, 2015, 10:08am

Hi Richard,

It's been a while since I tried to wrap my head around the MVC pattern,
but I think that this -- or a recommended better alternative -- is needed
for an application I'm developing. I would like clarification of my
understanding of MVC and advice on how to achieve what I need.

There will be two versions of the application: a single-user version with
a SQLite3 back end and a multi-user version with a PostgreSQL back end; the
'Model' in the MVC pattern. Both versions use the same wxPython UI code, the
'View' in the MVC. The Python middleware (the 'Controller') requires two
versions with the same basic structure but one using embedded SQLite3 syntax
and pysqlite, the other using SQLAlchemy and psycopg.

Why not use SQLAlchemy for both and just change the engine accordingly? I am in the progress of changing my application to use either Firebird SQL or PostgresSQL and have a config method which looks like this:

def setupDBURL(self):
"""Setup the SQLAlchemy URL to be used.

supported ones are:

'firebird+fdb'
'postgresql+psycopg2'

         """
         self.dbEngineType = None
         if "+fdb" in self.saEngine:
             # TODO: at some point following needs to be stored externaly
             self.dbRuser = "SYSDBA"
             self.dbRpw = "somepassword"
             self.dbEngineType = "fdb"
             self.dburl = saURL(self.saEngine,
                                username=self.dbRuser,
                                password=self.dbRpw,
                                host=self.dataHost,
                                port=self.dataPort,
                                database=self.databaseName)

self.dbEngine = sa.create_engine(self.dburl, encoding='utf8',
echo=self.salog,
connect_args={'charset': 'utf8'})

         elif "+psycopg2":
             self.dbRuser = "postgres"
             self.dbRpw = "somepassword"
             self.dbEngineType = "psycopg2"
             self.dburl = saURL(self.saEngine,
                                username=self.dbRuser,
                                password=self.dbRpw,
                                host=self.dataHost,
                                port=self.dataPort,
                                database=self.databaseName)
             self.dbEngine = sa.create_engine(self.dburl, encoding='utf8',
                                              echo=self.salog)

If the database is created with SQLAlchemy and you use such things as triggers and stored procedures you need to create two versions where needed and create them along these lines, here a simple DOMAIN for a memo field.

     # meta data
     meta_memo = DDL("""CREATE DOMAIN MEMO AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8""")
     meta_memo_PG = DDL("""CREATE DOMAIN MEMO AS TEXT""")
     event.listen(dbCurrent.metadata, 'before_create',
                  meta_memo.execute_if(dialect='firebird'))
     event.listen(dbCurrent.metadata, 'before_create',
                  meta_memo_PG.execute_if(dialect='postgresql'))

# similar for triggers and sp's

and then you call:

db.metadata.create_all(engine)

Werner

···

On 2/15/2015 1:46, Rich Shepard wrote:

Rich · February 15, 2015, 4:09pm

Hello Werner,

This looks like a perfect solution. I'll need to study it carefully to
fully understand how it's working. Certainly more efficient than two
completely separate modules.

FWIW, template databases for both SQLite and PostgrSQL already exist; I
write them as .sql files in emacs then read them into the appropriate dbms.
So each user need only add data.

Thanks very much,

Rich

···

On Sun, 15 Feb 2015, Werner wrote:

Why not use SQLAlchemy for both and just change the engine accordingly? I
am in the progress of changing my application to use either Firebird SQL
or PostgresSQL and have a config method which looks like this:

Werner2 · February 16, 2015, 8:58am

Hi Rich,

Why not use SQLAlchemy for both and just change the engine accordingly? I
am in the progress of changing my application to use either Firebird SQL
or PostgresSQL and have a config method which looks like this:

Hello Werner,

This looks like a perfect solution. I'll need to study it carefully to
fully understand how it's working. Certainly more efficient than two
completely separate modules.

Make sure to review http://sqlalchemy.readthedocs.org/en/rel_0_9/orm/tutorial.html#declare-a-mapping, especially the "Minimal Table Descriptions vs Full Descriptions", although it seems not to apply to you if you just stay with SQLite and PostgresSQL - but it can't hurt:-)

FWIW, template databases for both SQLite and PostgrSQL already exist; I
write them as .sql files in emacs then read them into the appropriate dbms.
So each user need only add data.

Then you can probably just use the 'setupDBURL' method stuff and ignore all the trigger/sp/meta stuff I mentioned.

Werner

···

On 2/15/2015 17:09, Rich Shepard wrote:

On Sun, 15 Feb 2015, Werner wrote:

Rich · February 20, 2015, 8:14pm

I'm using the SQLAlchemy declarative approach to writing the schema.
Would the same data types and syntax work for both SQLite and PostgreSQL?
They use slightly different syntax and data types (e.g., the former stores
all text as type TEXT while the latter distinguishes among CHAR(),
VARCHAR(), and TEXT.

I have colums defined like this one:

username = Column(Unicode(16), Sequence('user_name_seq'), Primary Key =
True)

Would this be valid regardless of backend dbms?

Thanks,

Rich

···

On Sun, 15 Feb 2015, Karsten Hilbert wrote:

Since SQLAlchemy offers an sqlite backend you shouldn't need to write
separate storage access layers.

Werner2 · February 20, 2015, 9:54pm

Hi Rich,

Since SQLAlchemy offers an sqlite backend you shouldn't need to write
separate storage access layers.

I'm using the SQLAlchemy declarative approach to writing the schema.
Would the same data types and syntax work for both SQLite and PostgreSQL?
They use slightly different syntax and data types (e.g., the former stores
all text as type TEXT while the latter distinguishes among CHAR(),
VARCHAR(), and TEXT.

I have colums defined like this one:

username = Column(Unicode(16), Sequence('user_name_seq'), Primary Key =
True)

Would this be valid regardless of backend dbms?

I am pretty sure that works. Haven't found a problem yet between Firebird SQL and PostgreSQL and FB is a pretty strange animal:).

See also:
Minimal Table Descriptions vs. Full Descriptions section on the following page:
http://sqlalchemy.readthedocs.org/en/rel_0_9/orm/tutorial.html#declare-a-mapping

I think you are very save when you stick with the generic types:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#generic-types

There is little specific stuff which is documented here:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#vendor-specific-types

Werner

···

On 2/20/2015 21:14, Rich Shepard wrote:

On Sun, 15 Feb 2015, Karsten Hilbert wrote:

Rich · February 20, 2015, 11:01pm

I am pretty sure that works. Haven't found a problem yet between Firebird
SQL and PostgreSQL and FB is a pretty strange animal:).

Hello Werner,

My web searches for use of unicode as a datatype for postgres and python
turned up nothing relevant so it's good to know that it will work here, too.

See also:
Minimal Table Descriptions vs. Full Descriptions section on the following page:
http://sqlalchemy.readthedocs.org/en/rel_0_9/orm/tutorial.html#declare-a-mapping

I think you are very save when you stick with the generic types:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#generic-types

There is little specific stuff which is documented here:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#vendor-specific-types

Despite several years away from this project my wxPython and Python
knowledge is still sufficient. But, there're so many SQLAlchemy docs to read
(I'm on only page 51 of the 0.9.8 manual now) I'm not sure which to read
first. The UI and backends are written so I need to both learn how to apply
SA to this project and how to fit it all together. For the latter, I'm
reading Mike Driscoll's MediaLocker code that you helped modify and wrapping
my head around how to organize modules.

I know I'll be asking more questions Real Soon Now.

Thanks again ... and carpe weekend,

Rich

···

On Fri, 20 Feb 2015, Werner wrote:

Rich · February 20, 2015, 11:08pm

My web searches for use of unicode as a datatype for postgres and python
turned up nothing relevant so it's good to know that it will work here,
too.

And reading one of the pages you referenced, I see that I found it in the
SA docs:

http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#generic-types

Rich

···

On Fri, 20 Feb 2015, Rich Shepard wrote:

Werner2 · February 21, 2015, 8:08am

I am pretty sure that works. Haven't found a problem yet between Firebird
SQL and PostgreSQL and FB is a pretty strange animal:).

Hello Werner,

My web searches for use of unicode as a datatype for postgres and python
turned up nothing relevant so it's good to know that it will work here, too.

See also:
Minimal Table Descriptions vs. Full Descriptions section on the following page:
http://sqlalchemy.readthedocs.org/en/rel_0_9/orm/tutorial.html#declare-a-mapping

I think you are very save when you stick with the generic types:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#generic-types

There is little specific stuff which is documented here:
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/type_basics.html#vendor-specific-types

Despite several years away from this project my wxPython and Python
knowledge is still sufficient. But, there're so many SQLAlchemy docs to read
(I'm on only page 51 of the 0.9.8 manual now) I'm not sure which to read
first.

I can't read a manual like this from front to back, so I would only read front to back the following:
- Object Relational Tutorial
- Declarative Extension

The UI and backends are written so I need to both learn how to apply
SA to this project and how to fit it all together. For the latter, I'm
reading Mike Driscoll's MediaLocker code that you helped modify and wrapping
my head around how to organize modules.

That hasn't seen much 'love and care' for some time, so I am sure things could be improved. If you do not things which could be improved please let us know.

Have a nice weekend.
Werner

···

On 2/21/2015 0:01, Rich Shepard wrote:

On Fri, 20 Feb 2015, Werner wrote: