Improve reduce functions of SQLite3 request

[Steffen Mutter]

Hi Dennis,

I really appreciate your input :-)

Dennis Lee Bieber wrote:

> 	I'll confess that I've not looked at any such sites -- mainly
> because I wouldn't understand enough about the sport to understand why
> one would do something one way or another.

You better do not. The first one I had to use was horrible in design and
there was only one person doing the programming stuff who stopped
development. So the leagues of the 'Badischer Handball Verband' went to
a commercial solution, done by the 'Württembergischer Handball Verband'.

> I'm mainly responding from
> the point of view of a general software developer with a bit too much
> understanding of relational database theory.

Which is fine, I'll get to that aspect later, you showed me a row where
I have a duplicate field, which is not needed at all.

>> I don't think normalization would really improve the speed on getting
>> the data needed for the user interface, would it?
>>
> 	It may not improve initial speed -- but ideally it should in the end
> simplify the data you have to maintain. Any time you have a table design
> that requires duplicating data from record to record, you run a risk of
> inconsistent data on input.

Okay, let's have a look at the scheme.
Every row represents all the data needed to display a single game fully,
here it is:
tables for each season are named like this: Runde[begin-year][end-year]
so:
CREATE TABLE "Runde20122013" (
"Spiel" INTEGER PRIMARY KEY,
"Staffel" TEXT NOT NULL,
"Datum" TEXT,
"Zeit" TEXT,
"Halle" INTEGER,
"Heim" TEXT,
"Gast" TEXT,
"HeimNr" INTEGER,
"GastNr" INTEGER,
"Termin" TEXT,  <--- OOPS! Thank you for pointing on the design!!!
"HT" INTEGER,
"GT" INTEGER,
"HP" INTEGER,
"GP" INTEGER,
"melderID" INTEGER,
"bemerkung" TEXT,
"verband" TEXT,
"regional" TEXT,
"landes" TEXT,
"kreis" TEXT,
"schiriID" INTEGER,
"editorID" INTEGER,
"lastchange" TEXT );

The 'Spiel' column holds the number of the game, it's the primary key
which could be used to join data later for a game report, where the
players and the scores they did are inserted by their trainers. 
Not implemented yet...

So 'Staffel' = league. There are many similar leagues with the exactly
the same name all over the table, so I need some other rows to get
things sorted correctly.
 Together with 'verband','regional','landes','kreis' you can pick the
correct position of this special league in the command structure of
the german handball federation called 'dhb'.

/dhb/shv/baden/karlsruhe/mJC-KL1 is my team I train.
 
Halle is the ID-Number of
the place where the game is played.

Datum = date, Time = start time of the game. 
And you are right, I have done a double insert of those two rows in a
third row: 'Termin' - I should remove this one, because sorting is
easy done by ORDER BY Datum, Zeit - instead ORDER BY Termin, I actually
use.
 
lastchange represents the last timestamp when the last modification
was done. 

melderID represents the one (man or machine), who inserted
the result of the game. 

editorID the last real personID who changed
someting. 
bemerkung = remarks is for the editor what was edited.
SchiriID is the column which will be changed to TEXT so it can hold
the GnuPG text. This is a required field, depending in how the remark
looks like an icon is displayed to sign the viewer that there is
something he should take care about.

Okay, I see, I have to do some changes:
Remove (Termin) from the table.
Alter schiriID to TEXT the get the GnuPG stuff in there. 
(Besides that I remember SQLite does not really care about that)

> 	Here we get to your "alter and modify". The originator (the person
> using the "public" key of a key pair) will NOT be able to come back
> later and modify the data -- only the owner of the "secret" key can
> decrypt it.

I think I will do it this way:
Let's assume the planner for the referees for all
places in 'karlsruhe' logs in. 
(Meaning all those places with the Halle ID between 23000-23999 in the
league-area 'karlsruhe' will be provided with referees by him - see, a
lot of data needed and stored in extra tables)

So, as soon as he clicks on the button to do
'referee planning' he will be asked for his private-GnuPG to decrypt
the data. 
The data will be displayed with ALL games in those places,
even the games with a higher level he CAN NOT edit - those referee
sets will hold a closed 'lock' as a sign or something like that. 

So, let's say he has a complete blank referee section and starts
planning (it will be about August 2013, just to mention) the system
offers him the matching referees who can be used for this game in a
select box. 
The referee must match these requirements: 
permission to lead he lead this game in hierarchy? 
he's not a member of a club involved in this game 
can lead only a max. number of 3 games a day
So, he picks a referee, the save button appears in the box. Nothing
happens until he klicks  on it to store the data.
When he does, the schiriID TEXT will be encrypted by GnuPG using his AND
the referees public key.

Same thing happens, when a referee logs in and wants to see the games he
was already planned for: 
asking for the unlock phrase, accessing the db,
decrypting the data and displaying it.
I have no idea about the performing issue yet - but I think it's worth a
try :-)
I think it will perform nicely for the bhv (country section) maybe for
shv(region section)  - but if ALL german referees want to get
their individual games plan and all trainers, players come to my place
too and want it as a PDF file generated. Wooooo....
Disk store caching needed...

> 	You've created a "write once" data system. The only operations the
> originator will be able to perform are to delete the record, or replace
> the contents blindly (and anyone with delete/replace privileges could do
> that unless you implement some sort of database privilege scheme that
> doesn't use the PKCS system to lock records to the originator user and
> to the destination [reading] user).

See above. Use more public keys to encrypt the data and more people can
see it. 

> Given those restrictions, you might as well just email the encrypted
> data to the reader and forget about storing it anywhere.	

That would imply the referees have basic knowledge about PGP or GnuPG
and how to use it. I prefer the way to give them a system, anyone can
use as long as they remember their passwords. 

> 	In other words -- you are going to use a single session symmetric
> encryption system just to transmit a passphrase to unlock a PKCS key.
> AND you want the server keyrings to have both public and secret keys for
> each user... If I were paranoid, I sure wouldn't trust that.

That is always the same I run into:
The referee stuff needs to be secure and safe. 
But needs to be accessible by the persons who need access to it. 
Seems to be a classic 'hen and egg' problem - what's first...

The thing is, that I could give the handball.db to anyone, noone could
get the referee data readable without having the matching private keys.

> When PGP
> was originally released, the recommendation was that the key-rings
> should not even be on the computer hard-drive, but on a floppy that the
> user would insert only long enough to perform an encryption/decryption
> operation -- and PGP then went and wiped any memory that the key had
> passed through. GnuPG, as I recall, is an open source replacement based
> on PGP after PGP became a commercial product. Only public keys were ever
> given to anyone else.

That is meant to keep your personal data safe like programs, credit
card, bank balances, pictures noone should see - whatever. 
Not so for my system I am planning: as soon as the game is played
noone would want to pay the referee for extras :-)