Last active
July 5, 2023 17:31
-
-
Save Soremwar/6a403da3b74025e9ea36bcffe4d55495 to your computer and use it in GitHub Desktop.
This gist showcases the functionality of Oracle data modeler for creating tables programatically
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// How to access Relational models globally and create schemas and tables within it | |
var console = { | |
log: function(msg) { | |
model.getAppView().logMessage(msg); | |
} | |
} | |
var RELATIONAL_MODEL = "test"; | |
var SCHEMA = "test"; | |
var TABLE = "test"; | |
var relational_model = model.design.relationalDesigns.getByName(RELATIONAL_MODEL); | |
if (!relational_model){ | |
relational_model = model.design.addRelationalDesign(); | |
relational_model.setName(RELATIONAL_MODEL); | |
} | |
var schemas = relational_model.getSchemaObjectSet(); | |
var schema = schemas.getByName(SCHEMA); | |
if (!schema) { | |
schema = schemas.createSchemaObject(SCHEMA); | |
schema.setName(SCHEMA); | |
} | |
var table = relational_model.getTableSet().getByName(TABLE); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Old habits die hard | |
var console = { | |
log: function(msg) { | |
model.getAppView().logMessage(msg); | |
} | |
} | |
// Table has to exist for the process to work | |
var TABLE = "TEST"; | |
// [name, type, mandatory, description] | |
var TABLE_DEFINITION = [ | |
['f_decimal', 'DECIMAL(16,2)', true, 'Decimal field'], | |
['f_integer', 'INT(11)', false, 'Integer field'], | |
['f_char', 'CHAR(1)', false, 'Char field'], | |
['f_varchar', 'VARCHAR(10)', false, 'Varchar field'], | |
['f_datetime', 'DATETIME', false, 'Date field'], | |
]; | |
var tables = model.getTableSet().toArray(); | |
var datatypes = model.getDesign().getLogicalDatatypeSet().toArray(); | |
function getDatatype(name) { | |
for (var x in datatypes) { | |
var datatype = datatypes[x]; | |
if (datatype.getName().toUpperCase() === name.toUpperCase()) { | |
return datatype; | |
} | |
} | |
} | |
// Datatypes to be transformed into Oracle datatypes | |
var DATATYPES = { | |
CHAR: getDatatype("CHAR"), | |
DECIMAL: getDatatype("DECIMAL"), | |
DATE: getDatatype("DATE"), | |
DATETIME: getDatatype("DATETIME"), | |
INTEGER: getDatatype("INTEGER"), | |
VARCHAR: getDatatype("VARCHAR"), | |
}; | |
function getMatchingString(str1, str2) { | |
var matches = ""; | |
for (var x = 0; x < str1.length; x++) { | |
if (str1[x] === str2[x]) { | |
matches += str1[x]; | |
} else { | |
break; | |
} | |
} | |
return matches; | |
} | |
function matchDatatype(raw_match) { | |
var clean_match = raw_match.toUpperCase().trim(); | |
var datatype = { | |
type: undefined, | |
precision: undefined, | |
scale: undefined, | |
size: undefined, | |
} | |
if (clean_match.startsWith("CHAR")) { | |
var length = clean_match.match(/\(([^)]+)\)/)[1].trim(); | |
if (!isNaN(length)) { | |
datatype.type = DATATYPES.CHAR; | |
datatype.size = length; | |
} | |
} | |
if (clean_match === "DATE") { | |
datatype.type = DATATYPES.DATE; | |
} | |
if (clean_match === "DATETIME") { | |
datatype.type = DATATYPES.DATETIME; | |
} | |
if (clean_match.startsWith("DECIMAL")) { | |
var precision = clean_match.match(/\((\d+),\s*(\d+)\)/)[1]; | |
var scale = clean_match.match(/\((\d+),\s*(\d+)\)/)[2]; | |
if (!isNaN(Number(precision)) && !isNaN(Number(scale))) { | |
datatype.type = DATATYPES.DECIMAL; | |
datatype.precision = precision; | |
datatype.scale = scale; | |
} | |
} | |
if (clean_match === "INT(11)" || clean_match === "INT (11)") { | |
datatype.type = DATATYPES.INTEGER; | |
} | |
if (clean_match.startsWith("VARCHAR")) { | |
var length = clean_match.match(/\(([^)]+)\)/)[1].trim(); | |
if (!isNaN(length)) { | |
datatype.type = DATATYPES.VARCHAR; | |
datatype.size = length; | |
} | |
} | |
if (datatype.type === undefined) { | |
throw new Error("Could not find match for type " + clean_match); | |
} | |
return datatype; | |
} | |
for (var x = 0; x < tables.length; x++) { | |
var table = tables[x]; | |
if (table.getName() === TABLE) { | |
var columns = table.getElements(); | |
// Delete existing columns | |
for (var y = 0; y < columns.length; y++) { | |
var column = columns[y]; | |
column.remove(); | |
} | |
for (var z in TABLE_DEFINITION) { | |
var column = table.createColumn(); | |
var name = TABLE_DEFINITION[z][0]; | |
var type = TABLE_DEFINITION[z][1]; | |
var mandatory = TABLE_DEFINITION[z][2]; | |
var description = TABLE_DEFINITION[z][3]; | |
var datatype = matchDatatype(type); | |
column.setName(name); | |
// Set type to logical | |
column.setUse(1); | |
column.setLogicalDatatype(datatype.type); | |
column.setDataTypePrecision(datatype.precision); | |
column.setDataTypeScale(datatype.scale); | |
column.setDataTypeSize(datatype.size); | |
column.setMandatory(mandatory); | |
column.setCommentInRDBMS(description); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment