Skip to content

Instantly share code, notes, and snippets.

@Soremwar
Last active July 5, 2023 17:31
Show Gist options
  • Save Soremwar/6a403da3b74025e9ea36bcffe4d55495 to your computer and use it in GitHub Desktop.
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
// 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);
// 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