Emulation of code-description tables on SQL databases using Enum data type
A method and apparatus for converting code-description table data to an enumerated data type in a schema record referencing the code-description table. A record of a table is identified that references the code-description table, and a portion of a schema describing the record is updated to include the relevant data of the code-description table as an enumerated type of the record schema. The enumerated type schema element includes data elements of the code-description table relevant to the record, each element having its own type. As additional records of the same table that are related to the code-description table are called, the enumerated type schema element may be updated to include additional code-description table data elements.
1. A data storage device, comprising:
one or more memory modules; and
a controller comprising a processor configured to perform a method for data schema detection and migration, the method comprising:
identifying, in a portion of a first file, a reference to a code-description table comprising a plurality of descriptors;
creating a schema for the portion of the first file, the schema comprising a schema element comprised of two or more of the plurality of descriptors;
converting the first file to a table according to the schema;
storing the table and schema in the one or more memory modules;
identifying a mismatch between data of the first file and a second file;
updating a field type to match a mismatched data type by adding a new field to the schema resulting in a new column in the table to allow for a missing field to have a location in the table;
maintaining an exception queue;
determining the exception queue is empty;
stopping identifying mismatches;
determining a last data record has been reached; and
executing a plurality of database operations comprising:
a query on the table;
a record insert on the table;
a record update on the table; and
a record deletion on the table.
2. The data storage device of claim 1 , wherein the schema element is an enumerated data type.
3. The data storage device of claim 2 , wherein converting the first file comprises removing at least a portion of the reference to the code-description table.
4. The data storage device of claim 3 , wherein the method further comprises identifying a mismatched field, comprising a field of the first file that is not matched to the schema element or a second schema element.
5. The data storage device of claim 4 , wherein the mismatched field comprises one of a new field type, a changed field type, and a missing field type.
6. The data storage device of claim 4 , wherein the schema is updated to an updated schema based on the mismatched field type.
7. The data storage device of claim 6 , wherein previously converted portions of the first file are converted based on the updated schema.
8. A controller for a data storage device, comprising:
an I/O to one or more memory devices; and
a processor configured to execute a method for data schema detection and migration, the method comprising:
receiving a portion of a first file comprising a plurality of records;
detecting a reference in one of the records to a code-description table;
defining a schema for the first file based on the data types in at least one record of the plurality of records, comprising at least one schema element comprising at least two descriptors of the code-description table;
storing data from the first file and the schema in the one or more memory devices;
identifying a mismatch between data of the first file and a second file;
updating a field type to match a mismatched data type by adding a new field to the schema resulting in a new column in a data table to allow for a missing field to have a location in the data table;
maintaining an exception queue;
determining the exception queue is empty;
stopping identifying mismatches;
determining a last data record has been reached; and
executing a plurality of database operations comprising:
a query on the data table;
a record insert on the data table;
a record update on the data table; and
a record deletion on the data table.
9. The controller of claim 8 , the method further comprising wherein the reference to the code-description table is removed.
10. The controller of claim 9 , the method further comprising wherein the at least one schema element defined as an enumerated data type.
11. The controller of claim 10 , wherein storing data from the first file comprises defining the data table based on the plurality of records and the schema.
12. The controller of claim 11 , wherein the method further comprises executing one of a query, a record insert, a record update, and a record deletion, on the data table.
13. The controller of claim 11 , wherein the method further comprises detecting a field mismatch comprising one of detecting a new field not present in the schema, a change of data type, or a missing field.
14. The controller of claim 13 , wherein the method further comprises generating a new schema by updating the schema based on the field mismatch, wherein updating the schema comprises one of:
updating the schema to include the new field;
updating the data type; and
updating the schema change a field designation to one of required and optional.
15. The controller of claim 14 , wherein the method further comprises updating the data table based on the new schema.
16. The controller of claim 14 , wherein the method further comprises converting additional data from the file to the data table, based on the new schema.
17. The controller of claim 8 , wherein the method further comprises identifying a type of one of the plurality records of as one of hierarchy, repeated, and optional.
18. A system for storing data, comprising:
one or more memory means; and
an SSD controller means configured to carry out a method for data schema detection and migration, the method comprising:
detecting field hierarchy of a first file and a reference to a code-description table;
defining a schema means based on the field hierarchy, the schema comprising a schema element describing a portion of the code-description table comprising a plurality of descriptors;
defining a data table based on the schema means and the first file;
identifying a mismatch between data of the first file and a second file;
updating a field type to match a mismatched data type by adding a new field to the schema resulting in a new column in the data table to allow for a missing field to have a location in the data table;
maintaining an exception queue;
determining the exception queue is empty
stopping identifying mismatches;
determining a last data record has been reached; and
executing a plurality of database operations comprising:
a query on the data table;
a record insert on the data table;
a record update on the data table; and
a record deletion, on the data table.
19. The system of claim 18 , wherein the schema element comprises a list of at least two of the plurality of descriptors of the code-description table.
20. The system of claim 19 , the method further including typing the schema element as an enumerated data type.