Re: [SQLObject] Three Tier apps

[Dave W. <da...@su...>]

Another option for going multi tier is rather than specifically extend 
sql object to create something like http://c-jdbc.objectweb.org/ for 
pythons db api.

This way you can have a whole extra level between sqlObject and the 
database server. Not one to add logic but definitely one to add db 
independence, db scaling and db fault tolerance. But it would be 
something that would work for any db access from python.

Personally I would like to suggest a multi-tier approach based on the 
following

Client --REST--> queue server

Middle Tier --REST--> queue server

Middle Tier --db aggregator--> database server

But included in this will be intra middle tier communication also via 
the queue server to parcel up large tasks into smaller concurrent ones.

Take for example some complex reporting requirement. The client (be it 
web browser or gui) puts a single request in the queue. A middle tier 
server collects the request and does a spread out thus putting many much 
smaller requests into the queue which it will aggregate together to 
create the final report. Each of these smaller requests is picked up by 
a middle tier server (remember you can have as many of these as you 
want) and it is here sqlObject would be used. By connecting the 
sqlObject calls through an aggregator the db queries can be executed in 
parallel on multiple db servers.

Once the original client has submitted the request it simply polls the 
queue server to check on progress.

Now this is quite different architecturally to what you were thinking 
of. However, I would like to suggest that it is also more flexible and 
more likely to succeed. Why?

a) All the components needed (Queue Server, db aggregator) benefit 
multiple projects. So you can get wider support than developing a 
solution for just sqlObject where your total community will always be 
smaller than the sqlObject community.

b) It gives you far more flexibility. Suppose you find that one task 
element that you put in the queue would be much easier to write without 
sqlObject. You don't lose any of the tiers when you need to do this.

c) It gives much better scaling. You can add more middle tier servers, 
more database servers at will. You can also take better advantage of 
multi-processor machines with Python by running multiple python middle 
tier servers on a single multi-processor server. This avoids the GIL 
issues with multi-threaded python code - well it also avoids loads of 
threading issues.

d) You might lose out with sqlObject caches although you can get sql 
caching in the aggregator. But you could also add middle-tier affinity 
to tasks so that certain items in the queue are most likely to be picked 
up by the middle tier server which is most likely to have the most 
useful cache.

e) It also gives the huge flexibility of being able to migrate tools and 
language. Suppose one task is much better suited to Ruby or to some 
other db layer in Python. Now you can have a separate middle tier server 
that will pick up those types of task. You have no dependency issues as 
all that communication is done via REST.

f) This approach also gives you Internet scale. You can distribute your 
middle tier servers anywhere on the Internet. So for example they can be 
local to the database or other resource they need to use. Or to provide 
fault tolerance.

g) Lastly it should, with care, be possible to adopt an architecture 
like this that will scale from a single user, single PC (queue server, 
middle-tier and db all inside a single python process) to almost any scale.

Sorry if this is considered too off subject.

Regards

Dave
-- 
Dave Warnock: http://42.blogs.warnock.me.uk