[AccessD] Data Export Spec - Rev1

John,

Question #1: You "dive into details" from the very beginning - but it is not
clearly stated in your spec are your custom classes/modules to be developed
planned to be used with DAO only? I mean if you consciously restrict
applicability(/reusability) of the code to be developed - that's OK but it
have to be clearly stated in the specs' abstract to avoid any
misunderstanding/mistreatment of the goals of your project for all involved
parties(/project stakeholders). As far as I see from your code samples you
use DAO. xxx - and from that usage one can make assumption that DAO and
nothing else is planned to be used to work with metadata and the real data
sources - is that a correct assumption?

Shamil

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Thursday, October 20, 2005 6:49 AM
Subject: [AccessD] Data Export Spec - Rev1


> OK this is the first rev to the table specification for the data export
> module.  Everyone is welcomed to try and figure out what we are up to and
> comment on how you would use this and what you think would be useful in
> order to nail down an export specification.
>
> usystblExport holds the top level information about an export, including
the
> name of the export specification, whether the export needs to be fixed
> width, whether to quote the text files, what the delimiter is for this
> specific export of the data, and a fully pathed file spec for where to
place
> the data.
>
> usystblExport
> EX_ID AutoNumber Export ID
> EX_Name Text Export Name
> EX_FixedWidth Yes/No True when Fixed Width
> EX_QuoteText Yes/No True when quotes needed around text
> EX_Delimiter Text The delimiter between fields
> EX_FileSpec Text Fully pathed file name
> EX_HeaderLine Yes/No True when Place the field names in the first line of
> the file
>
> usystblExportDataSource holds the relationship between an Export and a
data
> source.  This is a many to one, i.e. a given export specification can only
> use a single data source, but a single data source can be used by many
> different exports.
>
> usystblExportDataSource
> EXDS_ID AutoNumber DataSourceExport ID
> EXDS_IDEX Number Export ID
> EXDS_IDDS Number Data Source ID
>
> usystblDataSource tells us the name of the table, query or possibly a raw
> SQL statement.  It also gives us a code telling us how to interpret the
> Source field.  The objective is to be able to create a single recordset
and
> export that recordset to multiple destinations, in various formats, but
> still ensure that the data is identical.
>
> usystblDataSource
> DS_ID AutoNumber Data source ID
> DS_Name Text Name of the data set - not required
> DS_Source Memo Data source - can be a sql
> statement, a query name or a table name
> DS_Type Text Source of the data - TBL, QRY or SQL
>
> usystblFld gives us information about each field - the order of the field
in
> the Big String, the source field name in the table, query or SQL
statement,
> the OUTPUT field in the text file, the format string used to coerce the
data
> into the correct format, The width of the field if used in a fixed width
> export, and the beginning position in the string for FIXED WIDTH fields.
> The beginning position can be calculated from the position and the width
of
> all the fields, and is mostly used as a cross check on the specification.
> Fixed width export specifications are very specific and we need to be
> certain that the client receiving the data tells us these numbers
correctly.
>
> usystblFld
> FLD_ID AutoNumber Field ID
> FLD_IDDS Number Data Source ID
> FLD_Order Number The order that the field appears in the
> output string
> FLD_FldName Text Name of the field in the data source
> FLD_OutputFldName Text The name of the field in the output
> file
> FLD_Format Text Format string to use on the data
>
> FLD_BeginPos Number Beginning position in the Big String
> FLD_Width Number The width if this is to be used in a fixed
> width export.
>
> In my current concept of how to implement this set of tables, usystblFld
> would be a class (clsFld) which is responsible for formatting a piece of
> data from a specific field.
>
> usystblDataSource becomes a class (clsDataSource) which is the supervisor
of
> a collection of clsFld.  The first thing that clsDataSource does is to
take
> the DS_ID and use that to pull all of the records in usystblFld with that
> value in FLD_IDDS.  This basically gathers all of the field records that
> will be exported.  Thus a query, table or SQL statement can have more
fields
> than make it into the export, and the fields that make it into the export
> are determined solely by their presence in usystblFld.  As clsDataSource
> reads the records out of usystblFld, it instantiates a clsFld for each
field
> to be exported, and storing the class instances in Field Position order
> (FLD_Order), sort the fields coming out of usystblFlds on FLD_Order.  Thus
> the clsFld instances are stored in the collection in their order in the
> output string.
>
> clsDataSource reads the dataset specified by the table name, query name,
or
> SQL statement stored in DS_Name.  This data set may have N records to be
> exported into the resulting text file. The data set is iterated record by
> record, and each record is then fed to each clsFld in consecutive order in
> the clsFld collection (pseudocode below):
>
> Dim rst as dao.recordset
> Dim db as dao.database
> Dim lclsFld as clsFld
> Dim strBigStr as string
> set db = currentdb
> set rst = db.Open(DS_Source)
> while not rst.eof
>
> 'the stuff above gets the data to be exported
>
> 'The stuff below formats the data using the collection of
> clsFld instances
>
> for each lclsFld in colClsFld
> str = lclsfld.Format(rst)
> strBigStr = strBigStr & SomeDelimiter & str
> next lclsFld
> wend
>
> As you can see, each clsFld instance gets a shot at the recordset, and
uses
> FLD_FldName to determine which piece of the recordset it is supposed to
> format (pseudocode below):
>
> Function Format(rst as dao.recordset) as string
> Dim var as variant
> Dim str as string
> var = rst(FLD_FldName)
> str = format(var,FLD_Format)
> End function
>
> Thus as clsDataSource iterates the recordset of data to export, for each
> record to be exported it passes the recordset off to each clsFld instance,
> getting back formatted data and building up BigString.  When all fields
have
> been formatted and appended to BigString, BigString is written to
> EX_FileSpec.
>
> That is the big picture, starting to discuss implementation logic.  I am
> going to cross post this to my forum out on my web site for the purpose of
> creating a thread where this can be read independently of AccessD chatter.
>
> All comments and suggestions welcomed.  We need to nail down the table
> structure, since the information in the tables drives the construction of
> the classes that hold the data in the usys tables, and the logic of how to
> use this data to create an export file.
>
>
> John W. Colby
> www.ColbyConsulting.com
>
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com