[Vtigercrm-developers] Receiving data from outside sources

Tue Feb 21 15:08:43 PST 2006

Can we keep it in adodb XML format so we don't have to have multiple 
copies for the various database backends?

Jeff Kowalczyk wrote:

>Mike Fedyk wrote:
>  
>
>>There is a post on the forum asking how to add data to vtiger from an
>>outside source.  It's a good question and I'd like to know the answer
>>too.  Do we have something for this already?
>>http://forums.vtiger.com/viewtopic.php?t=5065
>>    
>>
>
>This ties in with some things I've been thinking about in regards to our
>use of adodb-schema, .sql files, and sample data:
>
>
>Schema, migration and .SQL files
>--------------------------------
>
>First, it would be helpful to observe how a project I hold in high esteem
>(sql-ledger) does their schema loading and migration. [1, below]
>
>(This is definitely *not* vtigercrm-4.2.4 material:)
>
>.sql files are an efficient way for expert administrators and scripted
>installers to deploy vtigercrm's database. They also work well with
>version control systems for tracking changes.
>
>I propose we use adodb's schema sql-generation features to create
>versioned files in our repository containing sql statements defining only
>tables, indices, etc.
>
>There should be one .sql file for each supported backend (mysql, postgres,
>mssql, oracle, etc), with the filename-corresponding version number
>incremented for every release in which the schema changes.
>
>A person checking in a change to DatabaseSchema.xml must (by convention)
>run a script to regenerate the .sql files, and check it in under the same
>changeset.
>
>See [1] below, and extrapolate the Pg-*.sql files to multiple backends.
>Consider the chart of accounts files like our sample dataset.
>
>We have a generic sample dataset now, but people may be willing to
>contribute industry specific ones, which would have bazooka-level
>marketing effectiveness for those of us trying to make a vtigercrm
>deployment pitch to a customer.
>
>
>
>Sample Data and Bulk Loading of Customer Data
>---------------------------------------------
>
>Second, I love that we have sample dataset. I would like to see it
>maintained as a separate .sql file, containing only bulk-load statements.
>Perhaps even one bulk load file per table.
>
>If we separate out the bulk load statement sql files, and document how
>they are created, enterprising users can easily recreate them with a
>spreadsheet, some cut and paste, and a bit of elbow-grease. They are
>basically TSV files with a few start and end control characters.
>
>Less enterprising users can pay a helpful vtigercrm consultant to do it
>for them ;)
>
>
>
>SQL-Ledger shows us How Its Done
>--------------------------------
>
>[1] sql-ledger (http://sql-ledger.org) is a GLP perl cgi double-entry
>accounting system currently supporting postgresql, oracle and DB2. If we
>were ever to integrate an accounting system, I'd lobby hard for this to be
>the one we use.
>
>Unfortunately there is no browsable repository, so I'll excerpt the file
>listing of the sql-ledger/sql directory as installed:
>
>  $ ls -1 /usr/local/sql-ledger/sql
>  Australia_General_0000-chart.sql
>  Australia_General_00000-chart.sql
>  Austria-chart.sql
>  Austria-gifi.sql
>  Bahasa-Indonesia_Default-chart.sql
>  Belgium-chart.sql
>  Belgium-gifi.sql
>  Brazil_General-chart.sql
>  Canada-English-gifi.sql
>  Canada-English_General-chart.sql
>  Canada-French-gifi.sql
>  Canada-French_General-chart.sql
>  Colombia-PUC-chart.sql
>  Colombia-PUC-gifi.sql
>  Colombia-utf8-PUC-chart.sql
>  Colombia-utf8-PUC-gifi.sql
>  Czech-Republic-chart.sql
>  DB2-create.sql
>  DB2-indices.sql
>  DB2-remove.sql
>  DB2-set.sql
>  DB2-sql-ledger.order
>  DB2-tables.sql
>  Danish_Default-chart.sql
>  Default-chart.sql
>  Dutch_Default-chart.sql
>  Dutch_Standard-chart.sql
>  Egypt-UTF8-chart.sql
>  France-chart.sql
>  German-Sample-chart.sql
>  German-Sample-gifi.sql
>  Germany-DATEV-SKR03-chart.sql
>  Germany-DATEV-SKR03-gifi.sql
>  Germany-SKR03-chart.sql
>  Germany-SKR03-gifi.sql
>  Hungary-chart.sql
>  Hungary-gifi.sql
>  Italy-gifi.sql
>  Italy_General-chart.sql
>  Italy_cc2424-chart.sql
>  Latvia-chart.sql
>  NAICS.sql
>  Norwegian_Default-chart.sql
>  Oracle-indices.sql
>  Oracle-tables.sql
>  Oracle-upgrade-1.8.0-1.8.4.sql
>  Oracle-upgrade-1.8.4-1.8.5.sql
>  Oracle-upgrade-1.8.5-2.0.0.sql
>  Oracle-upgrade-2.0.0-2.0.8.sql
>  Oracle-upgrade-2.0.8-2.2.0.sql
>  Paraguay-chart.sql
>  Paraguay-gifi.sql
>  Pg-functions.sql
>  Pg-indices.sql
>  Pg-tables.sql
>  Pg-upgrade-1.2.6-1.2.7.sql
>  Pg-upgrade-1.2.7-1.4.0.sql
>  Pg-upgrade-1.4.0-1.6.0.sql
>  Pg-upgrade-1.6.0-1.8.0.sql
>  Pg-upgrade-1.8.0-1.8.4.sql
>  Pg-upgrade-1.8.4-1.8.5.sql
>  Pg-upgrade-1.8.5-2.0.0.sql
>  Pg-upgrade-2.0.0-2.0.8.sql
>  Pg-upgrade-2.0.8-2.2.0.sql
>  Pg-upgrade-2.2.0-2.3.0.sql
>  Pg-upgrade-2.3.0-2.3.1.sql
>  Pg-upgrade-2.3.1-2.3.3.sql
>  Pg-upgrade-2.3.3-2.3.4.sql
>  Pg-upgrade-2.3.4-2.3.5.sql
>  Pg-upgrade-2.3.5-2.3.6.sql
>  Pg-upgrade-2.3.6-2.3.7.sql
>  Pg-upgrade-2.3.7-2.3.8.sql
>  Pg-upgrade-2.3.8-2.3.9.sql
>  Pg-upgrade-2.3.9-2.4.2.sql
>  Pg-upgrade-2.4.2-2.4.3.sql
>  Pg-upgrade-2.4.3-2.4.4.sql
>  Pg-upgrade-2.4.4-2.5.0.sql
>  Pg-upgrade-2.5.0-2.5.2.sql
>  Pg-upgrade-2.5.2-2.6.0.sql
>  Pg-upgrade-2.6.0-2.6.1.sql
>  Pg-upgrade-2.6.1-2.6.2.sql
>  Pg-upgrade-2.6.2-2.6.3.sql
>  Pg-upgrade-2.6.3-2.6.4.sql
>  Poland-chart.sql
>  Simplified-Chinese_Default-UTF8-chart.sql
>  Simplified-Chinese_Default-chart.sql
>  Spain-ISO-chart.sql
>  Spain-UTF8-chart.sql
>  Swedish-chart.sql
>  Swedish-gifi.sql
>  Swiss-German-chart.sql
>  Swiss-German-gifi.sql
>  Traditional-Chinese_Default-UTF8-chart.sql
>  Traditional-Chinese_Default-chart.sql UK_General-chart.sql
>  US_General-chart.sql
>  US_Manufacturing-chart.sql
>  US_Service_Company-chart.sql
>  Venezuela_Default-chart.sql
>
>_______________________________________________
>This vtiger.com email is sponsored by: Zoho Writer. Are you still using your desktop word processor for typing documents? Try the AJAX enabled, collaboration-friendly online word processor, Zoho Writer for FREE instead!
>http://zohowriter.com/?vt 
>
>  
>