Persisting data when data config/validation in contained in an xml file
posted 6 months ago
We have a webapp that validates and then uploads data (mostly in the form of spreadsheets). Each project has their own individual xml file that specifies what data is valid for the project. This xml file contains information such as required columns, recommended columns, acceptable values for a column, etc. We are currently a triple store to store the data and a rdbms to store the metadata (project information, individual data upload metadata, etc), however a customer wants to use mysql to store the data.
1. The data will need to be queryable. Therefore, I think the best option would be to create a db column for every "column" specified in the config xml. When uploading, a user will be inserting/uploading multiple rows, so I think that doing a batch csv load to mysql will be best. From testing, this was much faster then writing insert statements from the uploaded spreadsheet. Does this seem like the best option?
2. The xml files are mostly static, however there could be a column added or removed. Because of this, we need to verify that the database matches the config file whenever someone uploads data and throw an error if the database and config file don't match. How would you recommend doing this? Is fetching the column_name from INFORMATION_SCHEMA.COLUMNS and comparing them to the config file the best option? We would be using jdbc for the csv load, but jpa entities to represent the table. The other option would be to compare the Entity object properties to the config file (using reflection?).