[Vtigercrm-developers] Receiving data from outside sources

[Jeff Kowalczyk]

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