Ä Netmail2 (2:244/1120) ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ NETMAIL2 Ä
 Msg  : #1428 [101]                         Rcv Pvt
 By : Alex Woick                          2:244/1351      Sam 03 Mar 07 19:22
 To : Ulrich Schroeter                    2:244/1120
 Subj : [FWD] /TMP/NLARCHIVE.ZIP
ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
Hallo Ulrich,


Tschau
Alex

=========================================================================
* Area: ALEX.0NETMAIL (Alex' Netmailfolder)
* Von : Alex Woick, 2:244/1351 (22.Feb.07 21:26)
* An  : Steven Leeman, 2:292/624
* Subj: /TMP/NLARCHIVE.ZIP
=========================================================================
Hallo steven,

here is my database suggestion with an import script. If it somehow don't make 
it as fileattach, you can download the archive from
http://wombaz.dtdns.net/nlarchive.zip

The database contains 4 tables:
nodelist: name and date of a nodelist
nl_node: collection of zone:region:net:hub:node:point:sysop name combinations
nl_line: all data from a nodelist line with references to nl_node. The hash is 
a checksum of the whole line to identify duplicates.
nl_entry: A link table to associate nl_line entries with a nodelist. This is
the big one, but it is not possible to make it smaller.

I collected all 6 identifying numbers for a system, together with the role
(Zone, Region, Host, Node, ...), so you can easily select all nodes for a given
hub, or all nodes of a given region, or all nodes of a given zone.

Some SQL samples:


all your node numbers you ever had:

select keyword, zone, region, net, hub, node, point from nl_node where
sysop='Steven Leeman'


All your different nodelist entries you ever had:

select state, keyword, zone, region, net, hub, node, point, nodename, location,
sysop, phone, baudrate, flags
from nl_line join nl_node using (id_nl_node) where sysop='Steven Leeman'


Look for all nodelists if you were listed and with which node numbers:

select year, day, date, zone, net, node, point, sysop
from nodelist left join (select id_nodelist, zone, net, node, point, sysop
  from nl_line join nl_node using (id_nl_node) join nl_entry using (id_nl_line)
  where sysop='Steven Leeman') as t1 using (id_nodelist)

(ah, you were first listed 02.12.1994 :-)


Here an example to join all 4 tables. Count the size of my hub segment
2:244/1500 and show the development:

select year, day, date, count(id_nl_entry)
from nodelist join nl_entry using (id_nodelist)
  join nl_line using (id_nl_line) join nl_node using (id_nl_node)
where zone=2 and net=244 and hub=1500 and point=0
group by year, day, date


Who were the ZC's for how many nodelists? (To get the entry and exit dates of
each one is a bit more difficult)

select zone, sysop, count(id_nodelist) as num_nodelist
from nl_entry join nl_line using (id_nl_line) join nl_node using (id_nl_node)
where keyword='Zone'
group by zone, sysop
order by zone

All queries should execute immediately or within a few seconds on my system. If
they don't on yours, you have probably dropped some indexes or have given mysql
very limited memory.

Tschau
Alex

======== Ende des Forwards ========