Python Database Apps

[Ed Leafe]

On Sep 11, 2007, at 2:56 PM, darien.watkins at gmail.com wrote:

> It's gonna be a desktop app.  The
> database engine is going to be the critical component.  I like sqlite
> so that I don't need a database server on the client side.  It would
> help though if there is a way to sync between multiple clients to a
> central server database.  That's the tough part.  To give you a better
> idea of what i'm trying to do, I am trying to write an app where
> multiple technicians have an issue tracker that is available both
> offline and online.  I was looking to use sqlite as the local side
> that would sync new and updated issues to a central server when
> online.  Any technician may work on any issue.  Generally they will
> not be working on the same issue at the same time.  The technicians
> are geographically diverse (anywhere in the southeast US, not in an
> office.)

	As far as the server goes, you can't go wrong with PostgreSQL, as  
others have mentioned. I have more experience with MySQL, but their  
recent licensing changes have simply made it easier to go with  
Postgres, as I don't have to worry about which clause of which  
document I might be violating.

	For the local data store, SQLite is far and away the best choice,  
since you don't have to use two types of data access, as you would if  
you went with SQL on the server and, say, pickling on the local.

	If you create both data stores with the same structure, you can use  
UUIDs as your keys, along with a timestamp flag for records that are  
added or modified when disconnected so that you can re-sync later.  
You will have to handle conflicts (i.e., someone changed a record  
that another also changed while disconnected) on your own,  
implementing your own business logic to determine who "wins", and how  
the conflicted data is handled.

	I'll close with a plug for our product: Dabo. It is a desktop  
application framework with support for SQLite, PostgreSQL, MySQL,  
Firebird and Microsoft SQL Server databases. For disconnected data,  
you would simply define a local connection (to SQLite) and a remote  
connection (to your server database), and switch between the two  
depending on whether you are disconnected or not. The framework will  
handle the rest, allowing you to focus on the stuff that is unique to  
your app, such as the conflict resolution and business logic.

-- Ed Leafe
-- http://leafe.com
-- http://dabodev.com