[Vtigercrm-developers] Receiving data from outside sources

Fathi BOUDRA fboudra at free.fr
Tue Feb 21 16:38:59 PST 2006


> 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.

agree, and already used in my packages.

> 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.

agree.

> 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.

agree. i didn't find how xml file is generated.

> 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.

agree

> 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 ;)

agree

> 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

similar to gforge :)

globaly same point of view.



More information about the vtigercrm-developers mailing list