[Jason House <jhouse@xxxxxxxxx>]

I have thought about SQL dumps in the past...  I think that a good 
starting point might be to try to consider how to dump a particular 
protocol to a database (via one or more linked tables).  It would also 
be good to consider how to dump a protocol and the protocol it contains 
(and of course somehow have them linked together).

Dumping an individual protocol:

  As is true with everything, there is no easy way to mass process all 
protocols...  The generation of usable SQL tables requires customization 
by protocol.  I am not familiar with most protocols, but I think that 
assuming fields occur 0 or 1 times could produce a good protocol dumping 
utility.

  At a very minimum, each protocol needs to have its own table.  This 
is because every table must have a well defined set of columns. A 
protocol's table can safely include _every_ field as a column that 
occurs 0 or 1 time within the packet.  Items that occur 0 times can have 
a value of NULL.  NULL is a special value (even different from an empty 
string).  Any datatype can have a value of NULL and it simply means not 
present...
  The field names would make good column heading in the 0/1 case. 
Having a fixed number of occurances (>1) would somehow require using 
something other than the field name that ethereal uses.  This is a case 
where it might require custom coding in a dissector/tap...  Anybody have 
any good suggestions on that?
  If there are blocks of fields that are sometimes present, then those 
fields should probably be in a separate table.  When to do this might be 
a judgment call... and thus likely require custom coding in a 
dissector/tap. Of course, protocols such as OSPF must have their LSA's 
in a separate table.
  Variable length lists of items (such as OSPF LSA's) should *not* be 
made into a number of columns corresponding to the maximum number of 
occurances.  This practice almost always leads to greater headache when 
writing queries.  OSPF could indicate an ID to find matches of in a 
table of LSA's.  The table of LSA's would have multiple rows with the 
same ID.  Basically, the parent table would contain an ID column 
representing a list.  That list would then be in another table and have 
one row for each item in the list.  Each item would share the same ID 
and therefore allow a one to many match.


Dumping multiple protocols:
  The combination of protocol tables (ie. to describe a whole packet) 
needs to be based off matching of values in a columns of different 
tables.  In all likelihood, some common pool of identifiers will be 
needed for matching a parent packet with its child packet...  A pool 
similar to that used when ethereal registers its fields (only a new 
value is handed out for any new row in any protocol table)


Do people agree with this?  I suspect that a command line might contain 
which protocols to dump (as well as possibly which subset of the 
protocol fields to dump).  I suspect that a generic solution will have 
to create multiple tables and add extra columns (that the user didn't 
explicitly request) for the purposes of linking the various tables together.

Guy Harris <guy@xxxxxxxxxxxx> wrote:
> On Oct 29, 2003, at 4:07 PM, Jaime Fournier wrote:
> > Is there anyway that we could have the type of
> > functionality in this patch but in a more generic way,
> > as to allow for something like -z
> > "proto,colinfo,var,var" to be used?
>
>
> Probably, if somebody familiar with enough with SQL to
>
> 	1) know what stuff a user would want Ethereal to tell SQL to do;
>
> 	2) come up with a way to have the user tell Ethereal what to do;
>
> would supply that information.
>
> I.e., I assume a user would want Ethereal to tell SQL to create one or 
> more tables, with the values in the columns coming from fields in the 
> packet.
>
> If so, we'd need to have a way to specify what tables are to be 
> created, and what fields are to be used for the columns.  Presumably 
> the tables and columns have names; could we just use Ethereal field 
> names as column names?
>
> Also, that works if you have one table row per packet - but what if 
> there's more than one instance of a field in a packet?