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;
}
}
