原本orm使用的hibernated,不知什么原因,一个周后,代码突然不能正常运行,自己查了半天,没解决。到dlang官方论坛,以及hibernated的官方,都没找到解决办法。无奈只能退回到使用mysql的客户端。
但是现在处于开发初期,表结构的改变非常频繁,每次修改类、数据库,sql语句,想想都觉得太麻烦,自动动手,写了一个简单的orm工具;现在只支持从类生成表,修改类定义后自动修改表定义。代码如下,欢迎复制、改进:
支持的功能:
表的自动创建,
列的添加、删除,
索引的创建、修改,
指定某列是否为null
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);
}
public static string toString(CLS)(CLS obj)
{
return MysqlOrmInternalUtil.toString(obj);
}
}
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 string toString(CLS)(CLS obj)
{
if(obj is null)
{
return null;
}
string ret;
foreach (i, type; typeof(CLS.tupleof))
{
enum name = CLS.tupleof[i].stringof;
ret ~= name ~ ":" ~ to!string(__traits(getMember, obj, name)) ~ ",";
}
return ret;
}
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();
if(rs.length==0)
{
return null;
}
CLS ret = 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;
}
static if (is(typeof(__traits(getMember, obj, name)) == bool))
{
long tmp = row[index].get!(long);
__traits(getMember, obj, name) = (tmp!=0);
} else
{
__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;
}
}
用法:
@UniqueIndex("id",["id"])
class Card
{
@NotNull()
@Auto()
long id;
string pwd;
long agentId;
bool valid;
long rmb;
long createDate;
long soldDate;
long chargeDate;
}
string connStr = "host=10.211.55.10;port=3306;user=root;pwd=xxx;db=test";
mdb = new MysqlDB(connStr);
auto conn = mdb.lockConnection();
scope(exit) conn.close();
MysqlOrmUtil.updateTableSchema!(Customer)(conn);
MysqlOrmUtil.updateTableSchema!(Card)(conn);
MysqlOrmUtil.updateTableSchema!(Agent)(conn);
public void createCards(long agentId,long rmb,long count)
{
long curTime = now();
Connection conn = mdb.lockConnection();
scope(exit) conn.close();
for(int i=0; i<count; i++)
{
Card card = new Card();
card.agentId = agentId;
card.createDate = curTime;
card.pwd = createCardPwd();
card.rmb = rmb;
card.valid = true;
string sql = MysqlOrmUtil.genInsertSqlWithoutId("id",card);
MysqlOrmUtil.exeSql(sql,conn);
}
}
public Card getValidCardInfo(long cardId ,string cardPassword)
{
Connection conn = mdb.lockConnection();
scope(exit) conn.close();
string sql = std.string.format("select * from Card where cardId=%s and pwd='%s' and valid=%s",cardId,cardPassword,true);
Card ret = MysqlOrmUtil.exeQueryToObj!(Card)(sql,conn);
return ret;
}