Use DDL / sql .files instead of Excel

[nheeren]

Related to issue https://github.com/IndEcol/IE_data_commons/issues/4, I would like to propose to use actual DDL / .sql scripts instead of .xlsx files in https://github.com/IndEcol/IE_data_commons/tree/master/mySQL_Create An example for provenance.xlsx / provenance.sql:

CREATE TABLE `provenance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `reserve1` varchar(255) DEFAULT NULL,
  `reserve2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (id)
) 

This would be clearer and more portable. I think @Mahadi21 needs to create these commands anyway and he could simply store them in a text file with the .sql ending. These can then be executed by different tools.

[nheeren]

Another big benefit, of course, would be version control, i.e. quickly seeing the changes that were made / proposed

[Mahadi21]

Initially we thought about keeping both. The excel files are in the repository already and I am supposed to upload the .sql files also. Somehow I missed it. Will do it asap. If you want, feel free to do it also.

If we want to keep both, its better to create two sub-folders (one for .xlsx files and other one is for .sql files).

[nheeren]

Hmm. That adds reduncdancy, which is maybe unnecessary. Say, I want to add a constraint on a particular column, will I need to make changes to both files? What is the benefit of having xlsx files?

[stefanpauliuk]

I am definitely in favour of dropping the xlsx in places where there is not much formatting to be done, like table definitions. For more complicated files, we can keep Excel-compatible csvs (with ; as separator, have them already now in the IEDC_Classficition_fill folder) that are both csv and thus version-manageable and Excel-editable, which clearly is a great advantage.

[nheeren]

Ok, so we agree to move to .sql files in mySQL_Create and close this issue? Will @Mahadi21 be able to do that?

[Mahadi21]

Yes, sure.. I can do that.

[nheeren]

thanks!