|
Size: 506
Comment:
|
Size: 2031
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| The membership data is stored in multiple tables within the database "ffii" on genba (postgresql): | The membership data is stored in multiple tables within the database "ffii" on vic (postgresql): |
| Line 13: | Line 13: |
| ??, no permissions |
|
| Line 15: | Line 17: |
| * uid, character varying(8) * mailref, ?? * mailuser, part of email before @ * mailhost, part of email after @ |
|
| Line 16: | Line 23: |
| * uid, character varying(8) * adrref, character varying(8), ?? * land, character varying(2), 2 letter code for country (belgium = be, netherlands = nl) * plz, text, zip code * urb, text, city * str, text, street name and number |
|
| Line 18: | Line 31: |
| * uid | character varying(8) * pntyp | character varying(2) | ?? * pnom, full name |
|
| Line 20: | Line 36: |
| * uid | character varying(8) | * telref | character varying(8) | ?? * tel | text | telephone number * telrem | text | ?? |
|
| Line 22: | Line 42: |
| * uid | character varying(8) | * pass | character varying(16) | hash (what kind of?) of password == Some useful queries == Get member info from country BE: psql ffii ffii=# \o /home/dietvu/ffii.csv; ffii=# select distinct * from mail left outer join teln on (teln.uid = mail.uid) inner join pnom on (pnom.uid = mail.uid) inner join padr on (padr.uid = mail.uid) left outer join asoc on ( asoc.uid = mail.uid) where "land" = 'be'; email only: ffii=# select distinct mailuser, mailhost from mail left outer join teln on (teln.uid = mail.uid) inner join pnom on (pnom.uid = mail.uid) inner join padr on (padr.uid = mail.uid) left outer join asoc on ( asoc.uid = mail.uid) where "land" = 'be'; == Observations during manual correction == A lot of double records, only difference is organisation (nothing/ffii/ael) or norm/subs status for mailing lists. |
The membership data is stored in multiple tables within the database "ffii" on vic (postgresql):
asoc
- uid, character varying(8): aktiv id
- org, character varying(8): organisation, default 'ffii'
- asolvl, numeric(1,0): membership status, -1 (unsubscribed) to 3 (active member), not necessarily correct
- asorem , text, ???
- asolack, character varying(16), acknowledgement code of membership
asolack
??, no permissions
- uid, character varying(8)
- mailref, ??
- mailuser, part of email before @
- mailhost, part of email after @
padr
- uid, character varying(8)
- adrref, character varying(8), ??
- land, character varying(2), 2 letter code for country (belgium = be, netherlands = nl)
- plz, text, zip code
- urb, text, city
- str, text, street name and number
pnom
- uid | character varying(8)
- pntyp | character varying(2) | ??
- pnom, full name
teln
- uid | character varying(8) |
- telref | character varying(8) | ??
- tel | text | telephone number
- telrem | text | ??
pass
- uid | character varying(8) |
- pass | character varying(16) | hash (what kind of?) of password
Some useful queries
Get member info from country BE:
psql ffii
ffii=# \o /home/dietvu/ffii.csv;
ffii=# select distinct * from mail left outer join teln on (teln.uid = mail.uid) inner join pnom on (pnom.uid = mail.uid) inner join padr on (padr.uid = mail.uid) left outer join asoc on ( asoc.uid = mail.uid) where "land" = 'be';
email only: ffii=# select distinct mailuser, mailhost from mail left outer join teln on (teln.uid = mail.uid) inner join pnom on (pnom.uid = mail.uid) inner join padr on (padr.uid = mail.uid) left outer join asoc on ( asoc.uid = mail.uid) where "land" = 'be';
Observations during manual correction
A lot of double records, only difference is organisation (nothing/ffii/ael) or norm/subs status for mailing lists.