Sql言語解析器の実現例

8387 ワード
sql パーサー ast 解像度計
最近プロジェクトはもうすぐreleaseになります。手元も忙しくないです。Sql言語解析器の枠組みを3時間かけて書きました。製品のカスタム言語の実現方法にも使えます。本文はinsertのcommandを実現しました。他のコマンドは参照して実現できます。
 
  
 MySQL.java: 
  
 package com.cisco.gendwang;

public class MySQL {
	public static void main(String[] args) 
	{
		MysqlShell shell = MysqlShell.getInstance();
		shell.run();
	}
}
Mysql Shell.java:package com.cisco.gendwang;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class MysqlShell 
{
	private static final MysqlShell instance = new MysqlShell();
	
	private MysqlShell()
	{
	}
	
	public static MysqlShell getInstance()
	{
		return instance;
	}
	
	public void run()
	{
		Context context = new Context();
		SqlParser parser = new SqlParser(context);
		
		BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
		
		while(true)
		{
			System.out.print("mysql> ");
			
			//clear the context and the parser
			context.clear();
			parser.reset();
			
			//read the command from the standard input
			String commandString = null;
			try 
			{
				commandString = input.readLine();
				if(commandString.equalsIgnoreCase("exit"))
				{
					break;
				}
			} 
			catch (IOException e) 
			{
				e.printStackTrace();
				break;
			}
			
			//parse and execute the command
			parser.setCommandString(commandString);
			try 
			{
				Command command = parser.parse();
				command.execute(context);
			} 
			catch (SqlParserException e) 
			{
				System.out.println(e.getMessage());
			}
			catch (SqlExecutionException e)
			{
				System.out.println(e.getMessage());
			}
		}
	}

}
Sql Paser.java:package com.cisco.gendwang;

public class SqlParser 
{
	private static final String SQL_SYNTAX_ERROR = "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1";
	private final Context context;
	private String commandString;
	
	public SqlParser(Context context)
	{
		this.context = context;
	}
	
	public void reset()
	{
		commandString = null;
	}
	
	public void setCommandString(String commandString)
	{
		this.commandString = commandString;
	}
	
	public Command parse() throws SqlParserException
	{
		if(commandString.startsWith("insert"))
		{
			return parseInsertCommand();
		}
		
		return null;
	}
	
	private Command parseInsertCommand() throws SqlParserException
	{
		if(commandString.length() <= "insert".length())
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the insert
		String left = commandString.substring("insert".length());
		left = left.trim();
		
		if(left.length() <= "into".length())
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the into
		left = left.substring("into".length());
		left = left.trim();
		
		String tableName = context.getTableName();
		if(left.length() <= tableName.length())
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the table name
		left = left.substring(tableName.length());
		left = left.trim();
		
		if(left.length() <= "values".length())
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the values
		left = left.substring("values".length());
		left = left.trim();
		
		if(left.length() <= "(".length())
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the (
		left = left.substring("(".length());
		left = left.trim();
		
		if(!(left.endsWith(");")))
		{
			throw new SqlParserException(SQL_SYNTAX_ERROR);
		}
		
		//remove the );
		left = left.substring(0, left.length() - 2);
		
		String[] vals = left.split("\\s*,\\s*");
		
		//build the InsertCommand
		InsertCommand insert = new InsertCommand();
		for(String val: vals)
		{
			insert.addParam(new Constant(val));
		}
		
		return insert;
	}
}
Conttext.java:package com.cisco.gendwang;

import java.util.LinkedList;
import java.util.List;


public class Context 
{
	private final String DATABASE_NAME = "HRSystem";
	private final String TABLE_NAME = "employee";
	private final List<Row> rows = new LinkedList<Row>();
	
	public Context()
	{
	}
	
	public void clear()
	{
		
	}

	public String getDatabaseName() 
	{
		return DATABASE_NAME;
	}

	public String getTableName() 
	{
		return TABLE_NAME;
	}
	
	public List<Row> getRows()
	{
		return rows;
	}
}
Row.java:package com.cisco.gendwang;

public class Row 
{
	private int id;
	private String name;
	private int salary;
	private String email;
	
	private Row(RowBuilder builder)
	{
		id = builder.id;
		name = builder.name;
		salary = builder.salary;
		email = builder.email;
	}
	
	public int getId() 
	{
		return id;
	}

	public void setId(int id) 
	{
		this.id = id;
	}

	public String getName() 
	{
		return name;
	}

	public void setName(String name) 
	{
		this.name = name;
	}

	public int getSalary() 
	{
		return salary;
	}

	public void setSalary(int salary) 
	{
		this.salary = salary;
	}

	public String getEmail() 
	{
		return email;
	}

	public void setEmail(String email) 
	{
		this.email = email;
	}

	@Override
	public boolean equals(Object obj)
	{
		if(!(obj instanceof Row))
		{
			return false;
		}
		
		Row row = (Row)obj;
		
		return (id == row.id);
	}
	
	@Override
	public int hashCode()
	{
		int result = 17;
		
		result = 31 * result + id;
		
		return result;
	}
	
	public static class RowBuilder
	{
		private int id;
		private String name;
		private int salary;
		private String email;
		
		public RowBuilder()
		{
		}
		
		public RowBuilder setId(int id)
		{
			this.id = id;
			return this;
		}
		
		public RowBuilder setName(String name)
		{
			this.name = name;
			return this;
		}
		
		public RowBuilder setSalary(int salary)
		{
			this.salary = salary;
			return this;
		}
		
		public RowBuilder setEmail(String email)
		{
			this.email = email;
			return this;
		}
		
		public Row build()
		{
			//validation can be handled here
			return new Row(this);
		}
	}
}
Command.java:package com.cisco.gendwang;

public abstract class Command 
{
	public abstract Object execute(Context ctx) throws SqlExecutionException;
}
Contstant.java:package com.cisco.gendwang;

public class Constant extends Command 
{
	private Object value = null;
	
	public Constant(String value)
	{
	      char ch = value.charAt(0);
	      
	      if((ch == '"') || (ch == '\'') )
	      {  
	         this.value = value.substring(1, value.length() - 1);
	      }
	      else if(Character.isDigit(ch))
	      {
	         this.value = Integer.parseInt(value);
	      }
	}
	
	@Override
	public Object execute(Context ctx) throws SqlExecutionException
	{
		return value;
	}
}
InsertCommand.java:package com.cisco.gendwang;

import java.util.LinkedList;
import java.util.List;

public class InsertCommand extends Command 
{
	private List<Constant> params = new LinkedList<Constant>();
	
	public InsertCommand()
	{
	}
	
	public void addParam(Constant param)
	{
		params.add(param);
	}
	
	public Object execute(Context ctx) throws SqlExecutionException
	{
		//validation could be put here
		
		//insert into the table
		int id = (int)params.get(0).execute(ctx);
		String name = (String)params.get(1).execute(ctx);
		int salary = (int)params.get(2).execute(ctx);
		String email = (String)params.get(3).execute(ctx);

		Row.RowBuilder builder = new Row.RowBuilder();
		builder.setId(id);
		builder.setName(name);
		builder.setSalary(salary);
		builder.setEmail(email);
		Row row = builder.build();
		
		List<Row> rows = ctx.getRows();
		rows.add(row);
		
		return null;
	}
}
Sql PaserException.java:package com.cisco.gendwang;

public class SqlParserException extends Exception
{
	public SqlParserException(String msg)
	{
		super(msg);
	}
}
Sql Execution Exception.java:package com.cisco.gendwang;

public class SqlExecutionException extends Exception
{
	public SqlExecutionException(String msg)
	{
		super(msg);
	}
}
Oracle接続クエリの理解