Why hibernated does not create tables automatically?

[zhmt via Digitalmars-d-learn]

finally ,I write a orm tool to replace hibernated, it is simple, 
and it is easy to use, and every thing in control.

It is free to copy ,improve.


module mysqlormutil;

import vibe.d;
import std.stdio;

import mysql.connection;
import mysql.db;


struct NotNull
{
}

struct Auto
{
}

struct Index
{
	string name;
	string [] cols;
}

struct UniqueIndex
{
	string name;
	string[] cols;
}

class TableIndexInfo
{
	public string indexName;
	public string[] cols;
	public bool isUnique;
}

class MysqlOrmUtil
{
	//更新表结构
	public static void updateTableSchema(CLS)(Connection conn)
	{
		MysqlOrmInternalUtil.updateTableSchema!(CLS)(conn);
	}
	
	//生成replace语句，保存整个对象
	public static string genSaveAllSql(CLS)(ref CLS obj)
	{
		return MysqlOrmInternalUtil.genSaveAllSql!(CLS)(obj);
	}
	
	public static string genInsertSqlWithoutId(CLS)(string 
idColName,ref CLS obj)
	{
		return 
MysqlOrmInternalUtil.genInsertSqlWithoutId!(CLS)(idColName,obj);
	}

	public static ulong exeSql(string sql,Connection conn)
	{
		return MysqlOrmInternalUtil.exeSql(sql,conn);
	}

	public static CLS exeQueryToObj(CLS)(string sql,Connection conn)
	{
		return MysqlOrmInternalUtil.exeQueryToObj!(CLS)(sql,conn);
	}
	
	public static CLS exeQueryToStruct(CLS)(string sql,Connection 
conn)
	{
		return MysqlOrmInternalUtil.exeQueryToStruct!(CLS)(sql,conn);
	}

	public static CLS[] exeQueryToObjArr(CLS)(string sql,Connection 
conn)
	{
		return MysqlOrmInternalUtil.exeQueryToObjArr!(CLS)(sql,conn);
	}
	
	public static CLS[] exeQueryToStructArr(CLS)(string 
sql,Connection conn)
	{
		return MysqlOrmInternalUtil.exeQueryToStructArr!(CLS)(sql,conn);
	}
}

class MysqlOrmInternalUtil
{
	__gshared static string[string] dToMysql ;
	shared static this()
	{
		dToMysql["int"] = "int";
		dToMysql["long"] = "bigint(20)";
		dToMysql["string"] = "varchar(128)";
		dToMysql["bool"] = "tinyint(1)";
	}
	
	public static string getMysqlType(string dtype)
	{
		return dToMysql[dtype];
	}

	public static CLS[] exeQueryToObjArr(CLS)(string sql,Connection 
conn)
	{
		Command cmd =  Command(conn);
		cmd.sql = sql;
		ResultSet  rs = cmd.execSQLResult();
		CLS[] ret = resultSetToObjArray!(CLS)(rs);
		return ret;
	}
	
	public static CLS[] exeQueryToStructArr(CLS)(string 
sql,Connection conn)
	{
		Command cmd =  Command(conn);
		cmd.sql = sql;
		ResultSet  rs = cmd.execSQLResult();
		CLS[] ret = resultSetToStructArray(rs);
		return ret;
	}

	public static CLS exeQueryToObj(CLS)(string sql,Connection conn)
	{
		Command cmd =  Command(conn);
		cmd.sql = sql;
		ResultSet  rs = cmd.execSQLResult();
		CLS ret = new CLS;
		resultSetToObj!(CLS)(rs);
		return ret;
	}

	public static CLS exeQueryToStruct(CLS)(string sql,Connection 
conn)
	{
		Command cmd = new Command(conn);
		cmd.sql = sql;
		ResultSet  rs = cmd.execSQLResult();
		CLS ret;
		resultSetToStruct!(CLS)(rs);
		return ret;
	}

	public static ulong exeSql(string sql,Connection conn)
	{
		Command cmd =  Command(conn);
		cmd.sql = sql;
		ulong ret;
		cmd.execSQL(ret);
		return ret;
	}
	
	public static CLS[] resultSetToObjArray(CLS)(ref ResultSet rs)
	{
		CLS[] arr;
		foreach(Row row ; rs)
		{
			CLS obj = new CLS;
			rowToObjOrStruct!(CLS)(rs,row,obj);
			arr ~= obj;
		}
		return arr;
	}
	
	public static CLS resultSetToObj(CLS)(ref ResultSet rs)
	{
		if(rs.length == 0)
		{
			return null;
		}
		
		CLS ret = new CLS;
		resultSetToObjOrStruct!(CLS)(rs,ret);
		return ret;
	}
	
	public static CLS[] resultSetToStructArray(CLS)(ref ResultSet rs)
	{
		CLS[] arr;
		foreach(Row row ; rs)
		{
			CLS obj;
			rowToObjOrStruct(row,obj);
			arr ~= obj;
		}
		return arr;
	}
	
	public static CLS resultSetToStruct(CLS)(ref ResultSet rs)
	{
		if(rs is null || rs.length ==0)
		{
			return null;
		}
		
		CLS ret;
		resultSetToObjOrStruct(rs,obj);
		return ret;
	}
	
	public  static void resultSetToObjOrStruct(CLS)(ref ResultSet 
rs,ref CLS obj)
	{
		foreach(Row row ; rs)
		{
			rowToObjOrStruct!(CLS)(rs,row,obj);
			break;
		}
	}
	
	public static void rowToObjOrStruct(CLS)(ref ResultSet rs,Row 
row ,ref CLS obj)
	{
		ulong overflow = 1000000;
		foreach (i, type; typeof(CLS.tupleof))
		{
			enum name =  CLS.tupleof[i].stringof;
			ulong index = rs.colNameIndicies.get(name,overflow);
			if(index == overflow)
			{
				continue;
			}
			__traits(getMember, obj, name) =  row[index].get!(type);
		}
	}
	
	//更新表结构
	public static void updateTableSchema(CLS)(Connection conn)
	{
		string tableName = CLS.stringof;
		string[string] old =getTableTypes(conn,tableName);
		string[string] newsch = getClsMysqlTypes!(CLS)();
		string[] sqls = genTableFieldsUpdateSql(tableName,old,newsch);
		auto cmd = Command(conn);
		ulong rowsAffected;
		//更新表的列
		foreach(s ; sqls)
		{
			cmd.sql = s;
			writeln(s);
			cmd.execSQL(rowsAffected);
			writeln(std.string.format("affected : %s",rowsAffected));
		}
		
		//更新索引
		TableIndexInfo[string] newindices = getClsIndexInfo!(CLS)();
		TableIndexInfo[string] oldindices = 
getTableIndexInfo!(CLS)(conn);
		sqls = genIndexUpdateSql!(CLS)(conn,newindices,oldindices);
		foreach(s ; sqls)
		{
			cmd.sql = s;
			writeln(s);
			try{
				cmd.execSQL(rowsAffected);
				writeln(std.string.format("affected : %s",rowsAffected));
			}catch(Exception e)
			{
				writeln(e.msg);
			}
		}
		//更新列的额外限制
		sqls = genFieldConstraintSql!(CLS)(conn);
		foreach(s ; sqls)
		{
			cmd.sql = s;
			writeln(s);
			try{
				cmd.execSQL(rowsAffected);
				writeln(std.string.format("affected : %s",rowsAffected));
			}catch(Exception e)
			{
				writeln(e);
			}
		}
	}
	
	public static string genSaveAllSql(CLS)(ref CLS obj)
	{
		string[] arr;
		foreach (i, type; typeof(CLS.tupleof))
		{
			enum name =  CLS.tupleof[i].stringof;
			auto v = __traits(getMember,obj,name);
			if(typeid(type)==typeid(string))
			{
				arr ~= std.string.format("%s='%s'",name,v);
			}else
			{
				arr ~= std.string.format("%s=%s",name,v);
			}
		}
		string sql = std.string.format("replace %s set 
%s",CLS.stringof,arr.join(","));
		return sql;
	}
	
	public static string genInsertSqlWithoutId(CLS)(string 
idColName,ref CLS obj)
	{
		string[] arr;
		foreach (i, type; typeof(CLS.tupleof))
		{
			enum name =  CLS.tupleof[i].stringof;
			if(idColName == name)
			{
				continue;
			}
			auto v = __traits(getMember,obj,name);
			if(typeid(type)==typeid(string))
			{
				arr ~= std.string.format("%s='%s'",name,v);
			}else
			{
				arr ~= std.string.format("%s=%s",name,v);
			}
		}
		string sql = std.string.format("insert into %s set 
%s",CLS.stringof,arr.join(","));
		return sql;
	}
	
	//拼接索引sql语句
	public static string[] genIndexUpdateSql(CLS)(Connection 
conn,TableIndexInfo[string] newinfo,TableIndexInfo[string] old)
	{
		string[] arr;
		
		//find indices to be deleted.
		foreach(TableIndexInfo v; old.values)
		{
			if(newinfo.get(v.indexName,null) is null)
			{
				string sql =  std.string.format( "alter table %s drop INDEX 
%s",CLS.stringof,v.indexName);
				arr ~= sql;
			}
		}
		
		//find indices to be modified.
		foreach(TableIndexInfo v; newinfo.values)
		{
			TableIndexInfo oldOne = old.get(v.indexName,null);
			bool createNew = false;
			bool dropOld = false;
			if(oldOne is null)
			{
				createNew = true;
			}else if(oldOne.isUnique==v.isUnique && oldOne.cols==v.cols){
				//same;
			} else {
				createNew = true;
				dropOld = true;
			}
			
			if(dropOld)
			{
				string sql =  std.string.format( "alter table %s drop INDEX 
%s",CLS.stringof,v.indexName);
				arr ~= sql;
			}
			
			if(createNew)
			{
				if(v.isUnique)
				{
					string sql = std.string.format( "alter table %s add UNIQUE 
%s (%s)",CLS.stringof,v.indexName,v.cols.join(","));
					arr ~= sql;
				}else
				{
					string sql = std.string.format( "alter table %s add INDEX %s 
(%s)",CLS.stringof,v.indexName,v.cols.join(","));
					arr ~= sql;
				}
			}
		}
		
		
		return arr;
	}
	
	//获取类索引信息
	public static TableIndexInfo[string] getClsIndexInfo(CLS)()
	{
		TableIndexInfo[string] arr;
		
		auto all = __traits(getAttributes, CLS);
		foreach(one ; all)
		{
			if(typeid(one)==typeid(Index))
			{
				Index index = cast(Index)(one);
				TableIndexInfo info = new TableIndexInfo;
				info.indexName = index.name;
				info.cols = index.cols;
				info.isUnique = false;
				
				arr[info.indexName] = info;
			}else if(typeid(one)==typeid(UniqueIndex))
			{
				UniqueIndex index = cast(UniqueIndex)(one);
				
				TableIndexInfo info = new TableIndexInfo;
				info.indexName = index.name;
				info.cols = index.cols;
				info.isUnique = true;
				
				arr[info.indexName] = info;
			}
		}
		return arr;
	}
	
	
	
	//获取索引信息
	public static TableIndexInfo[string] 
getTableIndexInfo(CLS)(Connection conn)
	{
		TableIndexInfo[string] arr;
		auto cmd = new Command(conn);
		cmd.sql = std.string.format("show index from %s",CLS.stringof);
		
		ResultSet rs = cmd.execSQLResult();
		if(rs.length==0)
		{
			return arr;
		}
		
		foreach(Row row ; rs)
		{
			string keyName = 
row[rs.colNameIndicies["Key_name"]]._toString();
			string colName = 
row[rs.colNameIndicies["Column_name"]]._toString();
			long isUnique = 
row[rs.colNameIndicies["Non_unique"]].get!(long);
			
			TableIndexInfo info = arr.get(keyName,null);
			if(info is null)
			{
				info = new TableIndexInfo;
				arr[keyName] = info;
			}
			
			info.indexName = keyName;
			info.cols ~= colName;
			info.isUnique = (isUnique==0);
		}
		return arr;
	}
	
	//生成列限制sql
	public static string[] genFieldConstraintSql(CLS)(Connection 
conn)
	{
		MetaData md = MetaData(conn);
		
		ColumnInfo[] ca = md.columns(CLS.stringof);
		bool[string] nullmap;
		
		foreach( one ; ca)
		{
			nullmap[one.name] = one.nullable;
		}
		
		string[] arr;
		foreach (i, type; typeof(CLS.tupleof))
		{
			enum name =  CLS.tupleof[i].stringof;
			//string typestring = type.stringof;
			auto all = __traits(getAttributes, CLS.tupleof[i]);
			string[] cons;
			bool nullable = true;
			foreach(att ; all)
			{
				if(typeid(att) == typeid(Auto))
				{
					cons ~= "AUTO_INCREMENT";
				} else if(typeid(att) == typeid(NotNull))
				{
					nullable = false;
				}
			}
			
			if(cons.length>0)
			{
				//如果修改别的属性，null属性必须带
				cons ~= nullable?"NULL" : "NOT NULL";
			}else
			{
				//如果不修改别的属性，检测null属性是否需要修改
				if(nullable && !nullmap[name])
				{
					cons ~= "NULL";
				}else if(!nullable && nullmap[name])
				{
					cons ~= "NOT NULL";
				}
			}
			
			if(cons.length>0)
			{
				string sql = std.string.format("alter table %s change %s %s 
%s 
%s",CLS.stringof,name,name,getMysqlType(type.stringof),cons.join(" 
"));
				arr ~= sql;
			}
		}
		return arr;
	}
	
	//获取类的所有字段对应的mysql类型
	public static string[string] getClsMysqlTypes(CLS)()
	{
		string[string] arr;
		foreach (i, type; typeof(CLS.tupleof))
		{
			enum name =  CLS.tupleof[i].stringof;
			string typestring = type.stringof;
			arr[name] = getMysqlType(typestring);
		}
		return arr;
	}
	
	//从数据库中获取表字段的类型
	public static string[string] getTableTypes(Connection 
conn,string table)
	{
		string[string] arr;
		MetaData md = MetaData(conn);
		
		ColumnInfo[] ca = md.columns(table);
		foreach(info ; ca)
		{
			arr[info.name] = info.colType;
		}
		
		return arr;
	}
	
	//生成字段修改sql
	public static string[] genTableFieldsUpdateSql(string 
tableName,string[string] old,string[string] newtab)
	{
		string[] arr;
		if(old is null || old.length==0)
		{
			arr ~= genTableCreateSql(tableName,newtab);
			return arr;
		}
		
		//check column updates
		foreach(name;newtab.keys())
		{
			string oldType = old.get(name,null);
			string newType = newtab[name];
			
			if(oldType is null)
			{
				//add column;
				string sql = std.string.format("alter table %s add %s 
%s",tableName,name,newType);
				arr ~= sql;
			}else if(oldType == newType)
			{
				// same definition
			}else
			{
				//alter table
				string sql = std.string.format("alter table %s change %s %s 
%s",tableName,name,name,newType);
				arr ~= sql;
			}
		}
		
		//check columns to delete
		foreach(name; old.keys())
		{
			string newtype = newtab.get(name,null);
			if(newtype is null)
			{
				string sql = std.string.format("alter table %s drop 
%s",tableName,name);
				arr ~= sql;
			}
		}
		
		return arr;
	}
	
	//生成创建表的sql
	public static string genTableCreateSql(string 
tableName,string[string] types)
	{
		string[] arr;
		foreach (name;types.keys()) {
			string typestring = types[name];
			arr ~= name~" "~typestring;
		}
		string sql = "create table " ~ tableName ~ "(";
		sql ~= arr.join(",");
		sql ~= ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
		return sql;
	}
}