「SqlParser」の編集履歴（バックアップ）一覧はこちら

「SqlParser」(2008/04/14 (月) 07:12:06) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

var ll = new Array();

var Sql = ReadFile('','XXX');

var ro = new RegExpOperator();
ro.AddKey( {Name:'Select'         , RegExp:/Select/i} );
ro.AddKey( {Name:'From'           , RegExp:/From/i} );
ro.AddKey( {Name:'Left Outer Join', RegExp:/Left +Outer +Join/i} );
ro.AddKey( {Name:'Inner Join'     , RegExp:/Inner +Join/i} );
ro.AddKey( {Name:'On'             , RegExp:/On/i} );
ro.AddKey( {Name:'Where'          , RegExp:/Where/i} );
ro.AddKey( {Name:'Order By'       , RegExp:/Order +By/i} );
ro.AddKey( {Name:'Group By'       , RegExp:/Group +By/i} );
ro.AddKey( {Name:'Bracket.From'   , RegExp:/\(/i} );
ro.AddKey( {Name:'Bracket.To'     , RegExp:/\)/i} );

//JsAlert( Sql.match(/Group +BYp|From/i) );
//JsAlert( Serialize( ro.Search(Sql) ) );
//JsQuit();

var sn = new SqlNode( null );
sn.Read( Sql );

//WriteFile( ll.join('\r\n') );
WriteFile( DumpSqlNode( sn ) );

//WriteFile(ja.Sto.Tree.GetXmlRep());

function DumpSqlNode( Sn, indent ){
	var indent = IsGiven(indent) ? indent+'	' : '';
	var yy = new Array();
	
	var ee = Sn.Elements;
	for( var i=0; i<ee.length; i++ ){
		yy.push( toStringElement(  ee[i], indent ) );
	}
	
	if( Sn.Type=='Bracket' ){
		yy.unshift(indent + '(');
		yy.push   (indent + ')');
	}
	else if( Sn.Type=='Select' ){
		var yy = new Array( indent + yy.join(', ').Rmv(indent) );
		yy.unshift( indent + Sn.Type );
		return yy.join('\r\n');
	}
	else if( Sn.Type!=null ){
		yy.unshift( indent + Sn.Type );
	}
	if( !Sn.HasSqlNode() ){
		return indent+yy.join(' ').Rmv(indent);
	}
	return yy.join('\r\n');
	
	function toStringElement(e, indent){
		if( e instanceof SqlNode ){
			return DumpSqlNode( e, indent );
		}else{
			return indent + TrimLeft(e) ;
		}
	}
}
function SqlNode(Type_, Parent_){
	//Select, From, Where, OrderBy, GroupBy, Bracket
	this.Type     = Type_;
	this.Parent   = null;
	this.Elements = [];
	
	function Read( Sql ){
		
		Sql = Sql.replace(/  +/g, ' ');
		
		var r  = ro.Search(Sql);
		var rn = r.Name;
		
		if( rn!=null ){
			
			// --------sqlPrev--------r.From xxx r.To--------sqlNext--------
			var t       = this.Type;
			var sn      = new SqlNode( rn.split('.')[0] );
			var sqlPrev = Sql.substring( 0, r.From );
			var sqlNext = Sql.substring( r.To );
			
			this.AddElement( sqlPrev );
			
			if( rn=='Bracket.From' ){
				this.AddElement( sn );
			}
			else if( rn=='Bracket.To' ){
				sn = this.GetUpperContext();
			}
			else{
				if( t!=null && t!='Bracket' ){
					this.Parent.AddElement( sn );
				}else{
					this.AddElement( sn ); 
				}
			}
			sn.Read( sqlNext );
		}else{
			this.AddElement( Sql );
		}
	}
	function AddElement( e ){
		if( e!='' && (e+'').Rmv(' ')!='' ){
			if( e instanceof SqlNode ){
				e.Parent = this;
			}
			this.Elements.push( e );
			//ll.push( this.GetPath() +': '+ e.Type );
		}
	}
	function GetPath(){
		if( this.Parent!=null ){ return this.Parent.GetPath() +'.'+ this.Type; }
		return this.Type;
	}
	function GetUpperContext(){
		var p = this.Parent;
		return (this.Type=='Bracket') ? p : p.GetUpperContext();
	}
	function HasSqlNode(){
		for( var i=0; i<this.Elements.length; i++ ){
			if( this.Elements[i] instanceof SqlNode ){
				return true;
			}
		}
		return false;
	}
SqlNode.prototype.GetUpperContext = GetUpperContext;
SqlNode.prototype.GetPath    = GetPath;
SqlNode.prototype.Read       = Read;
SqlNode.prototype.AddElement = AddElement;
SqlNode.prototype.HasSqlNode = HasSqlNode;
}

function TrimLeft(xxx){
	if( xxx.charAt(0)==' ' ){
		return TrimLeft(xxx.substring(1));
	}
	return xxx;
}

function RegExpOperator(){
	
	this.RegExps = [];//{Name,RegExp}
	
	function AddKey( key ){
		this.RegExps.push(key);
	}
	function Search(text){
		var rn     = null;//Name
		var rpFrom = -1;//Position
		var rpTo   = -1;//Position
		
		for( var i=0; i<this.RegExps.length; i++ ){
			var r = this.RegExps[i]
			var p = text.search( r.RegExp );
			if( 0<=p ){
				if( rpFrom==-1 || p<rpFrom ){
					rn     = r.Name;
					rpFrom = RegExp.index;
					rpTo   = RegExp.lastIndex;
				}
			}
		}
		return { Name:rn, From:rpFrom, To:rpTo };
	}
RegExpOperator.prototype.AddKey = AddKey;
RegExpOperator.prototype.Search = Search;
}

var sn = new SqlNode();
sn.Read( Sql );

//WriteFile( ll.join('\r\n') );
WriteFile( DumpSqlNode( sn ) );

function DumpSqlNode( Sn, indent ){
	var indent = IsGiven(indent) ? indent+'	' : '';
	
	if( Sn.Type=='Bracket' ){
		return DumpBracket( Sn, indent );
	}
	else{
		return DumpElement( Sn, indent );
	}
	
	function toStringElement(e, indent){
		if( e instanceof SqlNode ){
			return DumpSqlNode( e, indent );
		}else{
			return indent + TrimLeft(e) ;
		}
	}
	function DumpElement( Sn, indent ){
		var yy = [];
		var ee = Sn.Elements;
		
		if( Sn.Type!=null ){
			yy.push( indent + Sn.Type );
		}
		
		if( !Sn.HasSqlNode() ){
			var zz = [];
			for( var i=0; i<ee.length; i++ ){
				zz.push( toStringElement(  ee[i], '' ) );
			}
			yy.push( zz.join(' ') );
		}else{
			for( var i=0; i<ee.length; i++ ){
				yy.push( toStringElement(  ee[i], indent ) );
			}
		}
		return yy.join( !Sn.HasSqlNode() ? ' ' : '\r\n' );
	}
	function DumpBracket( Sn, indent ){
		var yy = [];
		var ee = Sn.Elements;
		
		if( !Sn.HasSqlNode() ){
			yy.push( '(' );
			yy.push( ee.join(' ') );
			yy.push( ')' );
		}else{
			yy.push(indent + '(');
			for( var i=0; i<ee.length; i++ ){
				yy.push( toStringElement(  ee[i], indent ) );
			}
			yy.push(indent + ')');
		}
		return yy.join( !Sn.HasSqlNode() ? ' ' : '\r\n' );
	}
}
function SqlNode(Type_, Parent_){
	//Select, From, Where, OrderBy, GroupBy, Bracket
	this.Type     = Type_;
	this.Parent   = null;
	this.Elements = [];
	
	var ro;
	if(ro==null){
		ro = new RegExpOperator();
		ro.AddKey( {Name:'Select'         , RegExp:/Select /i} );
		ro.AddKey( {Name:'From'           , RegExp:/ From /i} );
		ro.AddKey( {Name:'Left Outer Join', RegExp:/Left +Outer +Join/i} );
		ro.AddKey( {Name:'Inner Join'     , RegExp:/Inner +Join/i} );
		ro.AddKey( {Name:'On'             , RegExp:/ On /i} );
		ro.AddKey( {Name:'Where'          , RegExp:/ Where /i} );
		ro.AddKey( {Name:'Order By'       , RegExp:/Order +By/i} );
		ro.AddKey( {Name:'Group By'       , RegExp:/Group +By/i} );
		ro.AddKey( {Name:'Bracket.From'   , RegExp:/\(/i} );
		ro.AddKey( {Name:'Bracket.To'     , RegExp:/\)/i} );
	}
	
	function Read( Sql ){
		
		Sql = Sql.replace(/  +/g, ' ');
		
		var r  = ro.Search(Sql);
		var rn = r.Name;
		
		if( rn!=null ){
			
			// --------sqlPrev--------r.From xxx r.To--------sqlNext--------
			var t       = this.Type;
			var sn      = new SqlNode( rn.split('.')[0] );
			var sqlPrev = Sql.substring( 0, r.From );
			var sqlNext = Sql.substring( r.To );
			
			this.AddElement( sqlPrev );
			
			if( rn=='Bracket.From' ){
				this.AddElement( sn );
			}
			else if( rn=='Bracket.To' ){
				sn = this.GetUpperContext();
			}
			else{
				if( t!=null && t!='Bracket' ){
					this.Parent.AddElement( sn );
				}else{
					this.AddElement( sn ); 
				}
			}
			sn.Read( sqlNext );
		}else{
			this.AddElement( Sql );
		}
	}
	function AddElement( e ){
		if( e!='' && (e+'').Rmv(' ')!='' ){
			if( e instanceof SqlNode ){
				e.Parent = this;
			}
			this.Elements.push( e );
			//ll.push( this.GetPath() +': '+ e.Type );
		}
	}
	function GetPath(){
		if( this.Parent!=null ){ return this.Parent.GetPath() +'.'+ this.Type; }
		return this.Type;
	}
	function GetUpperContext(){
		var p = this.Parent;
		return (this.Type=='Bracket') ? p : p.GetUpperContext();
	}
	function HasSqlNode(){
		var b = false;
		for( var i=0; i<this.Elements.length; i++ ){
			var e = this.Elements[i];
			if( e instanceof SqlNode ){
				b = b || ( e.Type=='Bracket' ? e.HasSqlNode() : true );
			}
		}
		//ll.push( this.GetPath() +': '+ b );
		return b;
	}
SqlNode.prototype.GetUpperContext = GetUpperContext;
SqlNode.prototype.GetPath    = GetPath;
SqlNode.prototype.Read       = Read;
SqlNode.prototype.AddElement = AddElement;
SqlNode.prototype.HasSqlNode = HasSqlNode;
}

function TrimLeft(xxx){
	if( xxx.charAt(0)==' ' ){
		return TrimLeft(xxx.substring(1));
	}
	return xxx;
}

function RegExpOperator(){
	
	this.RegExps = [];//{Name,RegExp}
	
	function AddKey( key ){
		this.RegExps.push(key);
	}
	function Search(text){
		var rn     = null;//Name
		var rpFrom = -1;//Position
		var rpTo   = -1;//Position
		
		for( var i=0; i<this.RegExps.length; i++ ){
			var r = this.RegExps[i]
			var p = text.search( r.RegExp );
			if( 0<=p ){
				if( rpFrom==-1 || p<rpFrom ){
					rn     = r.Name;
					rpFrom = RegExp.index;
					rpTo   = RegExp.lastIndex;
				}
			}
		}
		return { Name:rn, From:rpFrom, To:rpTo };
	}
RegExpOperator.prototype.AddKey = AddKey;
RegExpOperator.prototype.Search = Search;
}