digitalmars.D.learn - Why hibernated does not create tables automatically?
- zhmt (27/27) Feb 06 2015 The app compiles fine, but It throw an exception when I try to
The app compiles fine, but It throw an exception when I try to
save data to mysql :
hibernated.type.MappingException ../../../zhmt/.dub/packages/hibernated-0.2.19/source/hibernat
d/metadata.d(3332):
Cannot find entity by class ezsockacount.Dao.Customer
My initialization code is something like:
MySQLDriver driver = new MySQLDriver();
string url = MySQLDriver.generateUrl("10.211.55.10", 3306,
"test");
string[string] params = MySQLDriver.setUserAndPassword("root",
"xxx");
ds = new ConnectionPoolDataSourceImpl(driver, url, params);
EntityMetaData schema = new
SchemaInfoImpl!(Customer,Card,Agent);
Dialect dialect = new MySQLDialect();
factory = new SessionFactoryImpl(schema, dialect, ds);
Connection conn = ds.getConnection();
scope(exit) conn.close();
DBInfo db = factory.getDBMetaData();
db.updateDBSchema(conn, false, true);
I checked the "static EntityInfo [] entities; in SchemaInfoImpl",
the length of entites is 0.
And tables in mysql is not created automatically either.
I found the point of this question, but dont know how to resolve
it?
Will anybody help me?
Thx ahead!!
Feb 06 2015
On Friday, 6 February 2015 at 08:53:12 UTC, zhmt wrote:
The app compiles fine, but It throw an exception when I try to
save data to mysql :
hibernated.type.MappingException ../../../zhmt/.dub/packages/hibernated-0.2.19/source/hibernat
d/metadata.d(3332):
Cannot find entity by class ezsockacount.Dao.Customer
My initialization code is something like:
MySQLDriver driver = new MySQLDriver();
string url = MySQLDriver.generateUrl("10.211.55.10", 3306,
"test");
string[string] params =
MySQLDriver.setUserAndPassword("root", "xxx");
ds = new ConnectionPoolDataSourceImpl(driver, url, params);
EntityMetaData schema = new
SchemaInfoImpl!(Customer,Card,Agent);
Dialect dialect = new MySQLDialect();
factory = new SessionFactoryImpl(schema, dialect, ds);
Connection conn = ds.getConnection();
scope(exit) conn.close();
DBInfo db = factory.getDBMetaData();
db.updateDBSchema(conn, false, true);
I checked the "static EntityInfo [] entities; in
SchemaInfoImpl",
the length of entites is 0.
And tables in mysql is not created automatically either.
I found the point of this question, but dont know how to
resolve it?
Will anybody help me?
Thx ahead!!
IFAIK partial creation does not work. (bug?)
Use db.updateDBSchema( conn, true, true) make sure none of the
entities exist in the database. Otherwise it will fail.
hth.
Feb 06 2015
class Card
{
import hibernated.core;
Id
Generated
long id;
UniqueKey
string pwd;
}
MySQLDriver driver = new MySQLDriver();
string url = MySQLDriver.generateUrl("10.211.55.10", 3306,
"test");
string[string] params = MySQLDriver.setUserAndPassword("root",
"xxx");
auto ds = new ConnectionPoolDataSourceImpl(driver, url, params);
EntityMetaData schema = new SchemaInfoImpl!(Card);
Dialect dialect = new MySQLDialect();
auto factory = new SessionFactoryImpl(schema, dialect, ds);
Card c = new Card;
auto s = factory.openSession();
scope(exit) s.close();
s.save(c);
Here is the simplest example, but it complains the same error.
Feb 06 2015
"zhmt" <zhmtzhmt qq.com> I have submit an issue on github, hope that the author could help me.
Feb 06 2015
On Friday, 6 February 2015 at 09:42:09 UTC, zhmt wrote:
class Card
{
import hibernated.core;
Id
Generated
long id;
UniqueKey
string pwd;
}
MySQLDriver driver = new MySQLDriver();
string url = MySQLDriver.generateUrl("10.211.55.10", 3306,
"test");
string[string] params = MySQLDriver.setUserAndPassword("root",
"xxx");
auto ds = new ConnectionPoolDataSourceImpl(driver, url,
params);
EntityMetaData schema = new SchemaInfoImpl!(Card);
Dialect dialect = new MySQLDialect();
auto factory = new SessionFactoryImpl(schema, dialect, ds);
Card c = new Card;
auto s = factory.openSession();
scope(exit) s.close();
s.save(c);
Here is the simplest example, but it complains the same error.
OK,
Before the call
"auto s = factory.openSession();"
Does de mysql db has a db called 'test' and a table called 'card'
with columns named 'id' and 'pwd'? If not call:
"factory.getDBMetaData().updateDBSchema( conn, true, true );"
verify the table is created in mysql.
After create call:
"factory.openSession();"
...
If things are still failing take a look here:
https://github.com/buggins/hibernated/blob/master/hdtest/source/htestmain.d
This is a simple program testing some basic functions of
hibernated. Try it with sqlite first after that add a mysql
section and try again.
Feb 06 2015
Hi,Arjan,Thx for your replies, I have tried your suggestion,
MySQLDriver driver = new MySQLDriver();
string url = MySQLDriver.generateUrl("10.211.55.10", 3306,
"test");
string[string] params = MySQLDriver.setUserAndPassword("root",
"xxx");
auto ds = new ConnectionPoolDataSourceImpl(driver, url, params);
EntityMetaData schema = new SchemaInfoImpl!(Card);
Dialect dialect = new MySQLDialect();
auto factory = new SessionFactoryImpl(schema, dialect, ds);
auto conn = ds.getConnection();
scope(exit) conn.close();
auto db = factory.getDBMetaData();
db.updateDBSchema(conn, true, true);
Card c = new Card;
auto s = factory.openSession();
scope(exit) s.close();
s.save(c);
The table is not created. The same code works about two weeks
ago, but fails today, it make me confused.
Feb 06 2015
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;
}
}
Feb 12 2015
here is how to use:
UniqueIndex("id",["id"])
class Card
{
NotNull()
Auto()
long id;
string pwd;
long agentId;
bool valid;
long rmb;
long createDate;
long soldDate;
long chargeDate;
}
public Card[] getAllCards(long page,long pageSize)
{
Connection conn = mdb.lockConnection();
scope(exit) conn.close();
string sql = "select * from Card";
Card[] ret = MysqlOrmUtil.exeQueryToObjArr!Card(sql,conn);
return ret;
}
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);
}
}
Feb 12 2015
how to update table schema automatically:
this()
{
mdb = new MysqlDB(connStr);
auto conn = mdb.lockConnection();
scope(exit) conn.close();
MysqlOrmUtil.updateTableSchema!(Customer)(conn);
MysqlOrmUtil.updateTableSchema!(Card)(conn);
MysqlOrmUtil.updateTableSchema!(Agent)(conn);
}
Feb 12 2015