Improve reduce functions of SQLite3 request

[Steffen Mutter]

Hi Dennis,

Dennis Lee Bieber wrote:

> 	If this is meant to be a web-based system, you probably should be
> looking into using some sort of framework: Django, Zope/Plone, etc.

I tried both frameworks but I always see, that these frameworks
improve the progress to get things done but also limit what you can do
and how you can do it. 
I really want to code all by myself:
I want a browser user interface based on HTML5, jQuery and jQueryUI, CSS
on the client side, JSON to submit the data and all stuff to display,
alter and modify is almost completely done on the client. 
So the server itself just waits for something to do (en-/decrypt the dat
a for the referees for example)
I've seen those commercial handball sites use something like TYPO3,
they can't even get the easiest things to work I've been asking for,
even their sites are not SSL secured.
The userinterface I offer to my users is specially designed for the
needs of handball play. 
There are still a lot of features missing (my competitors don't even
think about) so if you return as a user to the page using HTML5s
session- or localstorage - it would improve the usage of the site
again, so the user sees automatically the club, team and league he's
interested in, stores his GnuPG password in his browser...

> 	Most of these frameworks use some form of object-relational mapper
> (ORM) to go between Python code objects (each table is a class, and each
> record is an object instance, to put it is very general terms) and the
> database itself.

I am not very good in object oriented programming yet, I'm just a
hobbyist. I bet you would losse patience if you'd see my coding...

> 	Problem with going that route is that you really need to normalize
> the database (as soon as you said you just dump everything into one
> table I shuddered), since a lot of the capability is tied to using
> foreign key linkages to filter data.

I don't think normalization would really improve the speed on getting
the data needed for the user interface, would it?

> 	You shouldn't need the public key cryptography system since the
> application itself should be able to control visibility of the data
> using some privilege table and a log-in identifier.

No it should not. Not even I as the 'master' of the system shall not be
able to see the referee stuff - see, if the club got teams in higher
leagues some guys would certainly like to pay referees to lead the game
in a certain direction... 
That's why I thought about using GnuPG...
It's quite easy to use with a plugin, available as a tarball.
http://packages.python.org/python-gnupg/ 
I got very exited when I did some tests, but found out that I need a lot
of entropy to work properly. So I summoned a haveged deamon on my
systems (available as Debian Squeeze backport)

> Using PKCS to
> encrypt data would mean the creator of that data would be unable to edit
> it, as they'd need the private key to decrypt it for editing.

That's indeed my idea:
The GnuPG software on the server handles the keyrings
and all the decryption for the users. 
I will force them to use a password for their private
key, when they initially set up their pub/sec rings so the server can
decrypt it for them:

They will be asked for their private key when needed the session is SSL
secured (I use CAcert as CA)  so no externals can spy their password.

Okay, I could do some server side scripting to create a save-passwords
tool, but that would break user privacy and make my efforts useless.
It thought about doing it this way: The keyring will reside in a
directory. Every user gets his own directory inside of it for
the keys, named as their userID. So is easy to pick the right key
for the logged in user, isn't it?

> Not to
> mention you'd need to have a way of transmitting the keys to the
> appropriate people. 

Users can get download their individual pub/sec-keyring via https.

> If you kept the keys on the database, you'd then
> need a secure way to ensure the proper key for the user was picked --
> anything that secure could be used to control the direct access to the
> data itself without encrypting it.

Er - but the data in the database would be readable, wouldn't it? 
That's exactly not what I want...

Maybe my thinking is absolute nonsense:
If you have an idea, how to get it done in an easier way, there are
only 2 things to get done: 
- data in the database needs to be kept save
- even I can't see it (without breaking spying out the users) 
- only the users who need it for gameplay can see it

I am listening
:-)