Skip to content

Instantly share code, notes, and snippets.

@krisrice

krisrice/tree.js

Created August 30, 2017 20:35
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save krisrice/4eaaf1058685a9adcd7e35ce63d82a57 to your computer and use it in GitHub Desktop.
Save krisrice/4eaaf1058685a9adcd7e35ce63d82a57 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
tree.js
var tree = (function(){
var tab= ' ';
function ddlnode( x, inputLine, parent ) {
this.x = x;
this.y = function() {
if( this.children.length == 0 )
return this.x+1;
else
return this.children[this.children.length-1].y();
};
this.parent = parent;
this.children = [];
if( parent != null )
parent.children.push(this);
this.fks = null;
this.descendants = function() {
var ret = [];
ret.push(this);
for( var i = 0; i < this.children.length; i++ )
ret = ret.concat(this.children[i].descendants());
return ret;
}
this.maxChildNameLen = function() {
var maxLen = 2;
var tmp = this.trimmedContent().toUpperCase();
if( ddl.options["Audit Columns"].value == 'Yes' || 0 < tmp.indexOf('/AUDITCOLS') )
maxLen = 'ROW_UPDATES_CNT'.length;
if( this.fks != null )
for( var col in this.fks ) {
//var parent = this.fks[col];
var len = col.length;
if( maxLen < len )
maxLen = len;
}
for( var i = 0; i < this.children.length; i++ ) {
if( 0 < this.children[i].children.length )
continue;
var len = this.children[i].parseName().length;
if( maxLen < len )
maxLen = len;
}
return maxLen;
}
this.content = inputLine;
this.comment;
this.trimmedContent = function() {
var ret = this.content.trim();
var start = ret.indexOf('[');
var end = ret.indexOf(']');
if( this.comment == null && 0 < start )
this.comment = ret.substr(start+1, end-start-1);
if( 0 < start ) {
ret = ret.substr(0,start) + ret.substr(end+2);
}
var start = ret.indexOf('--');
if( this.comment == null && 0 < start )
this.comment = ret.substr(start+2);
if( 0 < start ) {
ret = ret.substr(0,start);
}
return ret.trim();
}
this.parseName = function( prefix ) {
function replaceTrailing ( ret, postfix ) {
if( 0 < ret.indexOf(postfix) && ret.indexOf(postfix) == ret.length-postfix.length )
return ret.substring(0, ret.length-postfix.length);
return ret;
};
var ret = this.trimmedContent();
var qtBegin = ret.indexOf('"');
var qtEnd = ret.indexOf('"', qtBegin+1);
if( 0 <= qtBegin && qtBegin < qtEnd )
return ret.substring(qtBegin, qtEnd+1);
ret = ret.toUpperCase();
if( 0 == ret.indexOf('VIEW ') ) {
var chunks = ret.split(' ');
return ddl.objPrefix() + chunks[1];
}
ret = replaceTrailing(ret,' D');
var pos = ret.indexOf('/');
if( 0 < pos )
ret = ret.substring(0,pos);
ret = ret.trim();
ret = replaceTrailing(ret,' INTEGER');
ret = replaceTrailing(ret,' INT');
ret = replaceTrailing(ret,' NUM');
ret = replaceTrailing(ret,' CLOB');
ret = replaceTrailing(ret,' BLOB');
ret = replaceTrailing(ret,' DATE');
ret = replaceTrailing(ret,' TSWTZ');
ret = replaceTrailing(ret,' TSWLTZ');
ret = replaceTrailing(ret,' TS');
ret = ret.replace(/ VC\d+/g,'');
ret = ret.replace(/ /g,'_');
if( prefix == undefined )
if( 0 == this.children.length ) { // switched to this comparison stile because accidental typo this.children.length = 0 is disastrous!
if( this.parent != undefined && this.parent.colprefix != undefined )
ret = this.parent.colprefix.toUpperCase()+'_' + ret;
} else {
ret = ddl.objPrefix() + ret;
}
var c = ret.substr(0,1);
if (c >= '0' && c <= '9') {
ret = 'x'+ret;
}
return ret;
}
this.parseType = function() {
var tmp = this.trimmedContent().toUpperCase();
if( 0 == tmp.indexOf('--') )
return "COMMENT";
if( 0 == tmp.indexOf('VIEW ') )
return "VIEW";
var ret = "VARCHAR2(4000)";
if( 0 < tmp.indexOf(' NUM') || 0 < tmp.indexOf('/PK') )
ret = "NUMBER";
if( 0 < tmp.indexOf(' INT') )
ret = "INTEGER";
if( 0 == tmp.indexOf('DATE ') || 0 < tmp.indexOf(' DATE')
|| 0 < tmp.indexOf(' D') && tmp.indexOf(' D') == tmp.length-' D'.length
)
ret = ddl.options["Date Data Type"].value.toUpperCase();
if( 0 < tmp.indexOf(' CLOB') )
ret = "CLOB";
if( 0 < tmp.indexOf(' BLOB') )
ret = "BLOB";
if( 0 < tmp.indexOf(' TS') )
ret = "TIMESTAMP";
if( 0 < tmp.indexOf(' TSWTZ') )
ret = "TIMESTAMP WITH TIME ZONE";
if( 0 < tmp.indexOf(' TSWLTZ') )
ret = "TIMESTAMP WITH LOCAL TIME ZONE";
if( 0 <= tmp.indexOf('/NN') || 0 <= tmp.indexOf('/NOT NULL') )
ret = ret + ' not null';
if( 0 <= tmp.indexOf('/HIDDEN') || 0 <= tmp.indexOf('/INVISIBLE') )
ret = ret + ' invisible';
var optQuote = "'";
if( ret.startsWith('INTEGER') || ret.startsWith('NUMBER') || ret.startsWith('DATE') )
optQuote = "";
if( 0 <= tmp.indexOf('/CHECK') ) {
var start = tmp.indexOf('/CHECK');
var end = tmp.lastIndexOf('/');
if( end == start)
end = tmp.length;
var values = tmp.substr(start+'/CHECK'.length, end-start-'/CHECK'.length).trim();
values = values.replace(/, /g,optQuote+","+optQuote).toUpperCase();
values = values.replace(/ /g,optQuote+","+optQuote).toUpperCase();
ret = ret +' constraint '+parent.parseName('noprefix=>true')+'_'+this.parseName('noprefix=>true')+'_CC\n';
ret = ret +" check ("+this.parseName('noprefix=>true')+" in ("+optQuote+values+optQuote+"))";
}
if( 0 <= tmp.indexOf('/BETWEEN') ) {
var start = tmp.indexOf('/BETWEEN');
var end = tmp.lastIndexOf('/');
if( end == start)
end = tmp.length;
var values = tmp.substr(start+'/BETWEEN'.length, end-start-'/BETWEEN'.length).trim();
ret = ret +' constraint '+parent.parseName('noprefix=>true')+'_'+this.parseName('noprefix=>true')+'_BET\n';
ret = ret +" check ("+this.parseName('noprefix=>true')+" between "+values+")";
}
if( 0 <= tmp.indexOf('/DEFAULT') ) {
var start = tmp.indexOf('/DEFAULT');
var end = tmp.lastIndexOf('/');
if( end == start)
end = tmp.length;
var value = tmp.substr(start+'/DEFAULT'.length, end-start-'/DEFAULT'.length).trim();
ret = ret +' default on null ' + optQuote+value+optQuote ;
}
if( 0 < tmp.indexOf('/PK') ) {
ret = ret + ' GENERATED BY DEFAULT ON NULL AS IDENTITY\n';
ret = ret + tab + tab+' '.repeat(parent.maxChildNameLen()) +'constraint '+parent.parseName('noprefix=>true')+'_'+this.parseName('noprefix=>true')+'_pk primary key';
}
if( 0 < tmp.indexOf('/UNIQUE') ) {
ret = ret + '\n';
ret = ret + tab + tab+' '.repeat(parent.maxChildNameLen()) +'constraint '+parent.parseName('noprefix=>true')+'_'+this.parseName('noprefix=>true')+'_unq unique';
}
return ret;
}
this.isExplicitPk = function() {
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
var tmp = child.trimmedContent().toUpperCase();
if( 0 < tmp.indexOf('/PK') )
return true;
}
return false;
}
this.refId = function() {
var tmp = this.trimmedContent().toUpperCase();
var pos = tmp.indexOf(' ID ');
if( 0 < pos )
return tmp.substr(0,pos)+'S';
pos = tmp.indexOf(' ID');
if( pos == tmp.length-' ID'.length )
return tmp.substr(0,pos)+'S';
pos = tmp.indexOf('/FK');
if( 0 < pos )
return tmp.substr(pos+'/FK'.length).trim().replace(' ','_');
pos = tmp.indexOf('/REFERENCE');
if( 0 < pos )
return tmp.substr(pos+'/REFERENCE '.length).trim().replace(' ','_');
return null;
}
this.parseValues = function() {
var tmp = this.trimmedContent().toUpperCase();
if( 0 <= tmp.indexOf('/CHECK') || 0 <= tmp.indexOf('/VALUES') ) {
var substr = '/CHECK';
var start = tmp.indexOf(substr);
if( start < 0 ) {
substr = '/VALUES';
start = tmp.indexOf(substr);
}
var end = tmp.lastIndexOf('/');
if( end == start)
end = tmp.length;
var values = tmp.substr(start+substr.length, end-start-substr.length).trim();
if( 0 < values.indexOf(',') ) {
values = values.replace(/ /g,"");
return values.split(',');
} else
return values.split(' ');
}
if( 0 <= tmp.indexOf('/BETWEEN') ) {
var start = tmp.indexOf('/BETWEEN');
var end = tmp.lastIndexOf('/');
if( end == start)
end = tmp.length;
var values = tmp.substr(start+'/BETWEEN'.length, end-start-'/BETWEEN'.length).trim();
values = values.replace(" AND "," ");
var ret = [];
for( var i = parseInt(values.split(' ')[0]); i <= parseInt(values.split(' ')[1]) ; i++ )
ret.push(i);
return ret;
}
return null;
}
this.apparentDepth = function() {
var chunks = this.content.split(/ |\t/);
var offset = 0;
for( var j = 0; j < chunks.length; j++ ) {
var chunk = chunks[j]/*.intern()*/;
//if( "\t" == chunk )
//TODO;
if( "" == chunk ) {
offset++;
continue;
}
if( !/[^.a-zA-Z0-9_"]/.test(chunk) )
return offset;
}
throw 'No alphanumerics in the node content';
}
this.depth = function() {
if( this.parent == null )
return 0;
return this.parent.depth()+1;
}
this.location = function() {
return "["+x+","+this.y()+")";
}
this.offset = function( padding ) {
var s = "";
for( var i = 0; i < this.depth() ; i++ )
s = s + padding;
return s;
}
this.toString = function( padding ) {
return this.offset(padding)
//+ nbrsp;
+ this.location()
+ padding
+ this.content
;
}
this.toDDL = function( ) {
if( this.parseType() == "VIEW" ) {
var tmp = this.trimmedContent().toUpperCase();
var chunks = tmp.split(' ');
var ret = 'create or replace view ' +this.parseName()+ " as\n";
ret = ret + 'select\n';
var maxLen = 0;
for( var i = 2; i < chunks.length; i++ ) {
var tbl = ddl.find(chunks[i]);
var len = (chunks[i]+'.ID').length;
if( maxLen < len )
maxLen = len;
for( var j = 0; j < tbl.children.length; j++ ) {
var child = tbl.children[j];
len = (chunks[i]+'.'+child.parseName()).length;
if( maxLen < len )
maxLen = len;
}
}
for( var i = 2; i < chunks.length; i++ ) {
var tbl = ddl.find(chunks[i]);
var pad = ' '.repeat(maxLen - (chunks[i]+'.ID').length);
ret = ret + tab + chunks[i]+'.ID'+tab+pad+chunks[i]+'_ID,\n';
for( var j = 0; j < tbl.children.length; j++ ) {
var child = tbl.children[j];
if( 0 == child.children.length ) {
pad = ' '.repeat(maxLen - (chunks[i]+'.'+child.parseName()).length);
ret = ret + tab + chunks[i]+'.'+child.parseName()+tab+pad+chunks[i]+'_'+child.parseName()+',\n';
}
}
}
if( ret.lastIndexOf(',\n') == ret.length-2 )
ret = ret.substr(0,ret.length-2)+'\n';
ret = ret + 'from\n';
for( var i = 2; i < chunks.length; i++ ) {
pad = ' '.repeat(maxLen - chunks[i].length);
ret = ret + tab + ddl.objPrefix()+chunks[i] + pad + chunks[i] + ',\n';
}
if( ret.lastIndexOf(',\n') == ret.length-2 )
ret = ret.substr(0,ret.length-2)+'\n';
ret = ret + 'where\n';
for( var i = 2; i < chunks.length; i++ )
for( var j = 2; j < chunks.length; j++ ) {
if( j == i )
continue;
var nameA = chunks[i];
var nameB = chunks[j];
var nodeA = ddl.find(nameA);
var nodeB = ddl.find(nameB);
for( var k in nodeA.fks ) {
var parent = nodeA.fks[k];
if( parent == nameB) {
var ref = parent;
if( parent.endsWith('S') )
ref = ref.substr(0,ref.length-1);
ret = ret + tab + nameA+'.'+ref+'_ID = ' +nameB+'.ID and\n';
}
}
}
if( ret.lastIndexOf(' and\n') == ret.length-' and\n'.length )
ret = ret.substr(0,ret.length-' and\n'.length)+'\n';
ret = ret + ';\n';
return ret;
}
if( this.parseType() == "COMMENT" )
return this.content;
if( this.children.length == 0 ) {
var pad = tab+' '.repeat(this.parent.maxChildNameLen() - this.parseName().length);
return this.parseName()+pad+this.parseType();
}
if( this.fks == null ) {
this.fks = [];
if( this.parent != null )
this.fks[this.parent.parseName('noprefix=>true')+'_ID']=this.parent.parseName('noprefix=>true');
for( var i = 0; i < this.children.length; i++ )
if( this.children[i].refId() != null ) {
this.fks[this.children[i].parseName()]=this.children[i].refId();
}
}
var postponed = [];
var indexedColumns = [];
var ret = "";
if( ddl.options["Include Drops"].value == 'Yes' )
ret = ret + 'drop table '+this.parseName()+" cascade constraints;\n\n";
ret = ret + 'create table '+this.parseName()+" (\n";
var pad = tab+' '.repeat(this.maxChildNameLen() - 'ID'.length);
if( !this.isExplicitPk() ) {
ret = ret + tab + 'ID' + pad + 'NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY\n';
ret = ret + tab + tab+' '.repeat(this.maxChildNameLen()) +'constraint '+this.parseName('noprefix=>true')+'_ID_PK primary key,\n';
}
for( var fk in this.fks ) {
var parent = this.fks[fk];
var ref = parent;
if( parent.endsWith('S') )
ref = ref.substr(0,ref.length-1);
// fk = ref + '_ID';
pad = tab+' '.repeat(this.maxChildNameLen() - fk.length);
ret = ret + tab + fk + pad + 'NUMBER\n';
ret = ret + tab + tab+' '.repeat(this.maxChildNameLen()) + 'constraint '+this.parseName('noprefix=>true')+'_'+fk+'_FK\n';
var onDelete = ' on delete '+ddl.options["On Delete"].value.toLowerCase();
if( ddl.options["On Delete"].value == 'Restrict')
onDelete = '';
ret = ret + tab + tab+' '.repeat(this.maxChildNameLen()) + 'references '+ddl.objPrefix()+parent+onDelete+',\n';
}
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
if( 0 < child.children.length ) {
postponed.push(child);
continue;
}
if( child.refId() == null )
ret = ret + tab + child.toDDL([]) +',\n';
}
var tmp = this.trimmedContent().toUpperCase();
if( ddl.options["Audit Columns"].value == 'Yes' || 0 < tmp.indexOf('/AUDITCOLS') ) {
var pad = tab+' '.repeat(this.maxChildNameLen() - 'CREATED'.length);
ret = ret + tab + 'CREATED' + pad + ddl.options["Date Data Type"].value.toUpperCase() + ' not null,\n';
pad = tab+' '.repeat(this.maxChildNameLen() - 'CREATED_BY'.length);
ret = ret + tab + 'CREATED_BY' + pad + 'VARCHAR2(255) not null,\n';
pad = tab+' '.repeat(this.maxChildNameLen() - 'UPDATED'.length);
ret = ret + tab + 'UPDATED' + pad + ddl.options["Date Data Type"].value.toUpperCase() + ' not null,\n';
pad = tab+' '.repeat(this.maxChildNameLen() - 'UPDATED_BY'.length);
ret = ret + tab + 'UPDATED_BY' + pad + 'VARCHAR2(255) not null,\n';
pad = tab+' '.repeat(this.maxChildNameLen() - 'ROW_UPDATES_CNT'.length);
ret = ret + tab + 'ROW_UPDATES_CNT' + pad + 'INTEGER not null,\n';
}
if( ret.lastIndexOf(',\n') == ret.length-2 )
ret = ret.substr(0,ret.length-2)+'\n';
ret = ret + ')'+(ddl.options["Compression"].value == 'Yes' || 0 < tmp.indexOf('/COMPRESS')?' compress':'')+';\n\n';
for( var fk in this.fks ) {
var parent = this.fks[fk];
var ref = parent;
if( parent.endsWith('S') )
ref = ref.substr(0,ref.length-1);
ret = ret + 'create index '+this.parseName()+'_i1 on '+this.parseName()+' ('+ref+'_ID);\n\n';
}
ret = ret + this.generateTrigger();
var j = 1;
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
var tmp = child.trimmedContent().toUpperCase();
if( 0 <= tmp.indexOf('/IDX') || 0 <= tmp.indexOf('/INDEX') )
ret = ret + 'create index '+this.parseName()+'_i'+(j++)+' on '+this.parseName()+' ('+child.parseName()+');\n';
}
if( this.comment != null )
ret = ret + "comment on table "+this.parseName()+" is '"+this.comment+"';\n";
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
if( child.comment != null && child.children.length == 0 )
ret = ret + "comment on column "+this.parseName()+'.'+child.parseName()+" is '"+child.comment+"';\n";
}
ret = ret + '\n';
for( var i = 0; i < postponed.length; i++ ) {
ret = ret + postponed[i].toDDL();
}
return ret;
}
this.generateTrigger = function() {
var ret = 'create or replace trigger '+ this.parseName() +'_BIU\n';
ret = ret + ' before insert or update\n';
ret = ret + ' on '+ this.parseName() +'\n';
ret = ret + ' for each row\n';
ret = ret + 'begin\n';
var OK = false;
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
method = null;
if( 0 < child.content.indexOf('/lower') )
method = 'LOWER';
else if( 0 < child.content.indexOf('/upper') )
method = 'UPPER';
if( method == null )
continue;
ret = ret + ' :new.'+child.parseName()+' := '+method+'(:new.'+child.parseName()+');\n';
OK = true;
}
var tmp = this.trimmedContent().toUpperCase();
if( ddl.options["Audit Columns"].value == 'Yes' || 0 < tmp.indexOf('/AUDITCOLS') ) {
ret = ret + ' if inserting then\n';
ret = ret + ' :new.created := SYSDATE;\n';
ret = ret + ' :new.created_by := user;\n';
ret = ret + ' end if;\n';
ret = ret + ' :new.updated := SYSDATE;\n';
ret = ret + ' :new.updated_by := user;\n';
ret = ret + ' :new.row_updates_cnt := NVL(:old.row_updates_cnt, 0) + 1;\n';
OK = true;
}
if( !OK )
return '';
ret = ret + 'end;\n/\n\n';
return ret;
}
this.rows = 0;
this.generateData = function() {
var ret = '';
var tmp = this.trimmedContent();
var start = tmp.indexOf('/insert ');
if( 0 < start ) {
tmp = tmp.substr(start+'/insert '.length);
var tmps = tmp.split(' ');
this.rows = parseInt(tmps[0]);
if( 300 < this.rows )
this.rows = 300;
for( var i = 0; i < this.rows; i++ ) {
ret = ret + 'insert into '+this.parseName()+' (\n';
for( var fk in this.fks ) {
var parent = this.fks[fk];
var ref = parent;
if( parent.endsWith('S') )
ref = ref.substr(0,ref.length-1);
ret = ret + ' '+ref+'_ID,\n';
}
for( var j = 0; j < this.children.length; j++ ) {
var child = this.children[j];
if( 0 == child.children.length )
ret = ret + ' '+child.parseName()+',\n';
}
if( ret.lastIndexOf(',\n') == ret.length-2 )
ret = ret.substr(0,ret.length-2)+'\n';
ret = ret + ') values (\n';
for( var fk in this.fks ) {
var parent = this.fks[fk];
var ref = parent;
if( parent.endsWith('S') )
ref = ref.substr(0,ref.length-1);
var refNode = ddl.find(parent);
var values = [];
for( var k = 1; k <= refNode.rows ; k++ )
values.push(k);
ret = ret + ' '+sample(this.parseName(),ref+'_ID', 'INTEGER', values)+',\n';
}
for( var j = 0; j < this.children.length; j++ ) {
var child = this.children[j];
if( 0 == child.children.length )
ret = ret + ' '+sample(this.parseName(),child.parseName(), child.parseType(), child.parseValues())+',\n';
}
if( ret.lastIndexOf(',\n') == ret.length-2 )
ret = ret.substr(0,ret.length-2)+'\n';
ret = ret + ');\n';
}
ret = ret + '\n';
}
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
if( 0 < child.children.length )
ret = ret + child.generateData();
}
return ret;
}
this.render = function( table, depth ) {
var row = table.insertRow(-1);
var cell = row.insertCell(0);
var nbrsp = String.fromCharCode(160);
var nbrspX3 = nbrsp+nbrsp+nbrsp;
//cell.innerHTML = this.toString(apparentDepth, nbrspX3); //<---- no sugarcoating
cell.innerHTML = "<font face=\"Lucida Console\" size=-1>"
+this.offset(nbrspX3)
+ "<font color=blue face=\"Lucida Console\" size=-1>"
+ this.location()
+ nbrsp
+ "</font><font color=DarkMagenta face=\"Lucida Console\" size=-1>"
+ this.content.trim()
;
for( var i = 0; i < this.children.length; i++ ) {
var child = this.children[i];
child.render(table, depth+1);
}
}
}
function recognize( input ) {
var ret = [];
var path = [];
var lines = input.split("\n");
var lineNo = -1;
for( var i = 0; i < lines.length; i++ ) {
var line = lines[i]/*.intern()*/;
if( "\n" == line )
continue;
lineNo++;
line = line.replace(/\r/g,'');
var nc = line.replace(/\r/g,'').replace(/ /g,'');
if( /[^a-zA-Z0-9="{}\/.,_\-\[\]]/.test(nc) )
continue;
if( "" == nc )
continue;
var node = new ddlnode(lineNo,line,null); // node not attached to anything
var matched = false;
for( var j = 0; j < path.length; j++ ) {
var cmp = path[j];
if( node.apparentDepth() <= cmp.apparentDepth() ) {
if( 0 < j ) {
var parent = path[j-1];
node = new ddlnode(lineNo,line,parent); // attach node to parent
path[j] = node;
path = path.slice(0, j+1);
matched = true;
break;
} else {
path[0] = node;
path = path.slice(0, 1);
ret.push(node);
matched = true;
}
}
}
if( !matched ) {
if( 0 < path.length ) {
var parent = path[path.length-1];
node = new ddlnode(lineNo,line,parent);
}
path.push(node);
if( node.apparentDepth() == 0 )
ret.push(node);
}
}
return ret;
}
return recognize;
}());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment