From: gdkhd812 Date: Fri, 30 May 2014 22:00:02 +0000 (+0900) Subject: sequelizeに移行した X-Git-Url: http://git.osdn.jp/view?p=webchat%2FWebChat.git;a=commitdiff_plain;h=f1b33cf06fa5a63db6715de7ac205c143d3499c3 sequelizeに移行した --- diff --git a/init.js b/init.js index 6bf7bd2..cbd80a6 100644 --- a/init.js +++ b/init.js @@ -1,18 +1,14 @@ var util = require("util"); var config = require("./configure.js"); +var profilelist,ipbanlist,rooms; + +module.exports.GetProfileColletion = {}; +module.exports.GetIpBanColletion = {}; +module.exports.GetRoomInfomation = {}; module.exports = function(callback){ var async = require("async"); - - var MySQLPool = new require("./mysql_pool.js"); - var pool = new MySQLPool({ - host : config.db_host, - user : config.db_user, - password : config.db_password, - port : config.db_port, - database : config.db_name, - }); - + if("name_hash" in config.alias) { if(config.alias["name_hash"].type != "unsignednumber") @@ -44,21 +40,19 @@ module.exports = function(callback){ }else{ throw "lastmodifiedが存在しません"; } - + var fs = require("fs"); async.waterfall([ function(next){ - var query = GetDropTableQuery("profilelist"); - pool.query(query,null,next); - }, - function(result,next){ - var query = GetCreateQuery(config.alias,"profilelist"); - pool.query(query,null,next); - }, - function(result,next){ - var query = GetDropTableQuery("ipbanlist"); - pool.query(query,null,next); - }, - function(result,next){ + var Sequelize = require("sequelize"); + var pool = new Sequelize(config.db_name, config.db_user, config.db_password,{ + host:config.db_host, + port:config.db_port + }); + + var query = GetCreateQuery(Sequelize,config.alias,"profilelist"); + var profilelist = pool.define("profilelist",query); + module.exports.GetProfileColletion = profilelist; + var def = { ip:{ type : "text", @@ -71,14 +65,10 @@ module.exports = function(callback){ primary : true, }, }; - var query = GetCreateQuery(def,"ipbanlist"); - pool.query(query,null,next); - }, - function(result,next){ - var query = GetDropTableQuery("rooms"); - pool.query(query,null,next); - }, - function(result,next){ + var query = GetCreateQuery(Sequelize,def,"ipbanlist"); + ipbanlist = pool.define("ipbanlist",query); + module.exports.GetIpBanColletion = ipbanlist; + var def = { number: { @@ -95,88 +85,81 @@ module.exports = function(callback){ type : "bool", }, }; - var query = GetCreateQuery(def,"rooms"); - pool.query(query,null,next); + var query = GetCreateQuery(Sequelize,def,"rooms"); + rooms = pool.define("rooms",query); + module.exports.GetRoomInfomation = rooms; + + fs.exists("inited",function(exists){ + next(null,exists); + }); }, - ],callback); -} + function(exists,next){ + if(exists) + next(null); + else + fs.open("inited","a",function(err,fd){ + fs.closeSync(fd); -function GetDropTableQuery(tablename) -{ - var result = util.format("DROP TABLE IF EXISTS %s;",tablename); + module.exports.GetProfileColletion.drop(); + module.exports.GetProfileColletion.sync(); - console.log(result); + module.exports.GetIpBanColletion.drop(); + module.exports.GetIpBanColletion.sync(); - return result; + module.exports.GetRoomInfomation.drop(); + module.exports.GetRoomInfomation.sync(); + + next(null); + }); + } + ],function(err){ + callback(err); + }); } -function GetCreateQuery(def,tablename) +function GetCreateQuery(Sequelize,def,tablename) { - var result = "CREATE TABLE " + tablename + "("; + var result = {}; for(key in def) { if(typeof(def[key].nodefinetable) != "undefined" && def[key].nodefinetable) continue; + option = {}; switch(def[key].type) { case "text": - result += util.format("%s VARCHAR(%d) ",key,def[key].length); + option["type"] = Sequelize.STRING(def[key].length); break; case "number": - result += util.format("%s %s ",key,GetIntType(def[key].length)); + option["type"] = Sequelize.INTEGER(def[key].length); break; case "unsignednumber": - result += util.format("%s %s UNSIGNED ",key,GetIntType(def[key].length)); + option["type"] = Sequelize.INTEGER(def[key].length).UNSIGNED; break; case "mail": - result += util.format("%s VARCHAR(%d) ",key,def[key].length); + option["type"] = Sequelize.STRING(def[key].length); break; case "password": - result += util.format("%s VARCHAR(%d) ",key,def[key].length); + option["type"] = Sequelize.STRING(def[key].length); break; case "textarea": - result += util.format("%s TEXT ",key); + option["type"] = Sequelize.TEXT; break; case "bool": - result += util.format("%s BOOL ",key); + option["type"] = Sequelize.BOOLEAN; break case "datetime": - result += util.format("%s DATETIME ",key); + option["type"] = Sequelize.DATE; break; default: throw util.format("invaild %s type:%s",key,def[key].type); } if(typeof(def[key].isnotempty) != "undefined" && def[key].isnotempty) - result += " NOT NULL "; - result += ","; - } - - for(key in def) - { - if(typeof(def[key].primary) != "undefined" && def[key].primary) - { - result += util.format("PRIMARY KEY(%s)",key); - break; - } + option["allowNull"] = true; + else if(typeof(def[key].primary) != "undefined" && def[key].primary) + option["primaryKey"] = true; + result[key] = option; } - result += ");"; - - console.log(result); return result; } - -function GetIntType(len) -{ - switch(len) - { - case 1: - return "TINYINT"; - case 2: - return "SMALLINT"; - case 4: - return "INT"; - } - console.log(len); - throw "Invaild Length"; -} diff --git a/ipban.js b/ipban.js index cd71581..2fcad9d 100644 --- a/ipban.js +++ b/ipban.js @@ -2,14 +2,7 @@ module.exports = function() { var config = require("./configure.js"); - var MySQLPool = new require("./mysql_pool.js"); - var pool = new MySQLPool({ - host : config.db_host, - user : config.db_user, - password : config.db_password, - port : config.db_port, - database : config.db_name, - }); + var IpBanModel = require("./init").GetIpBanColletion; var collection = {}; this.IsBaned = function(ip){ return collection[ip] == "r"; @@ -33,7 +26,7 @@ module.exports = function() var async = require("async"); async.waterfall([ function(next){ - pool.query("TRUNCATE TABLE ipbanlist",null,next); + IpBanModel.drop().done(next); }, function(result,next){ var items = new Array(); @@ -50,7 +43,8 @@ module.exports = function() collection[ip] = token[1]; items.push(new Array(ip,collection[ip])); } - pool.query("INSERT INTO ipbanlist VALUES ?",[items],next); + newIpBan = IpBanModel.build(items); + newIpBan.save().done(next); }, ],callfunc); } @@ -59,7 +53,7 @@ module.exports = function() var async = require("async"); async.waterfall([ function(next){ - pool.query("SELECT * FROM ipbanlist",null,next); + IpBanModel.findAll().done(next); }, function(result,next){ for(var i = 0; i < result.length; i++) diff --git a/main.js b/main.js index 88b3c58..9f013d0 100644 --- a/main.js +++ b/main.js @@ -48,24 +48,12 @@ app.configure("production", function(){ }); var async = require("async"); -var fs = require("fs"); async.waterfall([ function(next){ - fs.exists("inited",function(exists){ - next(null,exists); - }); + var init = require("./init"); + init(next); }, - function(exists,next){ - if(exists) - next(null); - else - fs.open("inited","a",function(err,fd){ - fs.closeSync(fd); - var init = require("./init"); - init(next); - }); - } ],function(err){ if(err != null) { diff --git a/mysql_pool.js b/mysql_pool.js deleted file mode 100644 index 071b81e..0000000 --- a/mysql_pool.js +++ /dev/null @@ -1,37 +0,0 @@ -var async = require("async"); - -module.exports = function(param) -{ - this.query = function(query,param,callback) - { - async.waterfall([ - function(next){ - pool.acquire(next); - }, - function(client,next){ - client.query(query,param,function(err,result){ - next(err,result,client); - }); - }, - function(result,client,next){ - pool.release(client); - next(null,result); - } - ],callback); - } - var generic_pool = require("generic-pool"); - var mysql = require("mysql"); - pool = generic_pool.Pool({ - name : "mysql", - max : 10, - create : function(cb){ - var connection = mysql.createConnection(param); - connection.connect(function(err){ - cb(err,connection); - }); - }, - destroy : function(db){ - db.end(); - } - }); -} diff --git a/package.json b/package.json index 6a7ebdb..b7b51ee 100644 --- a/package.json +++ b/package.json @@ -9,8 +9,8 @@ , "express": "3.1.0" , "socket.io": "0.9.13" , "date-utils" : "1.2.12" - , "generic-pool" : "2.0.3" , "mysql" : "2.0.0-alpha7" + , "sequelize" : "1.7.x" , "murmurhash" : "0.0.2" , "lazy" : "1.0.9" } diff --git a/profile.js b/profile.js index 20a069b..9734638 100644 --- a/profile.js +++ b/profile.js @@ -264,22 +264,15 @@ function RenderMessage(res,msg,info) // function ProfileCollection() { - var MySQLPool = new require("./mysql_pool.js"); var murmurhash = require("murmurhash"); - var pool = new MySQLPool({ - host : config.db_host, - user : config.db_user, - password : config.db_password, - port : config.db_port, - database : config.db_name, - }); + var Profile = require("./init").GetProfileColletion; this.AuthAsync = function(name,password,cb){ async.waterfall([ function(next){ - pool.query("SELECT password FROM profilelist WHERE name_hash = ? and name = ?",[murmurhash.v3(name),name],next); + Profile.find({name_hash:murmurhash.v3(name), name:name}).done(next); }, function(result,next){ - if(result[0].password == md5_hex(password)) + if(result.password == md5_hex(password)) next(null,true); else next(null,false); @@ -287,30 +280,38 @@ function ProfileCollection() ],cb); } this.GetAsync = function(name,cb){ - pool.query("SELECT * FROM profilelist WHERE name_hash = ? and name = ?",[murmurhash.v3(name),name],cb); + Profile.find({name_hash:murmurhash.v3(name), name:name}).done(cb); } this.AddAsync = function(data,cb){ - var item = GetItem(data);; - pool.query("INSERT INTO profilelist SET ?",[item],cb); + newProfile = Profile.build(GetItem(data)); + newProfile.save().done(cb); } this.UpdatAsync = function(name,data,cb){ - var item = GetItem(data); - pool.query("UPDATE profilelist SET ? WHERE name_hash = ? and name = ?",[item,murmurhash.v3(name),name],cb); + Profile.update(GetItem(data),{name_hash:murmurhash.v3(name), name:name}).done(cb); } this.ClearAsync = function(cb){ - pool.query("TRUNCATE TABLE profilelist",null,cb); + Profile.drop().done(cb); } this.RemoveRangeAsync = function(names,cb){ - pool.query("DELETE FROM profilelist WHERE name IN (?)",[names],cb); + Profile.destroy({where:{ + in:[names] + } + }).done(cb); } this.RemoveAsync = function(name,cb){ - pool.query("DELETE FROM profilelist WHERE name_hash = ? and name = ?",[murmurhash.v3(name),name],cb); + Profile.destroy({name_hash:murmurhash.v3(name), name:name}).done(cb); } this.FindByNameAsync = function(pattern,start,count,cb){ - pool.query("SELECT * FROM profilelist WHERE name LIKE ? LIMIT ?,?",[pattern+"%",start,count],cb); + Profile.findAll({ + offset:start, + limit:count, + where:{ + like:pattern + } + }).done(cb); } this.ToArrayAsync = function(start,count,cb){ - pool.query("SELECT name,lastmodified FROM profilelist LIMIT ?,?",[start,count],cb); + Profile.findAll({offset:start,limit:count}).done(cb); } var crypto = require("crypto"); diff --git a/public/profile/detail.ejs b/public/profile/detail.ejs index 9d9a551..3917037 100644 --- a/public/profile/detail.ejs +++ b/public/profile/detail.ejs @@ -14,17 +14,17 @@ $(function(){ 詳細画面 -

<%= list[0].name %>の詳細画面

+

<%= list.name %>の詳細画面

<% for(var key in alias) {%> <% if(typeof alias[key] != "undefined" && alias[key].visible) {%>

<%= alias[key].name %>

-

<%- list[0][key] %>

+

<%- list[key] %>

<% } %> <% } %>
- + <% if(!admin){ %> diff --git a/public/profile/edit.ejs b/public/profile/edit.ejs index bcc64d9..6376618 100644 --- a/public/profile/edit.ejs +++ b/public/profile/edit.ejs @@ -16,13 +16,13 @@ <%= alias[key].name %> <% if(alias[key].type == "textarea"){ %> - + <% }else if(alias[key].type == "password"){ %>
<% }else if(typeof alias[key].readonly != "undefined" && alias[key].readonly){ %> - + <% }else{ %> - + <% } %> diff --git a/room.js b/room.js index a5ff81c..950fa36 100644 --- a/room.js +++ b/room.js @@ -3,14 +3,7 @@ module.exports = function() { var _this = this; var config = require("./configure.js"); - var MySQLPool = new require("./mysql_pool.js"); - var pool = new MySQLPool({ - host : config.db_host, - user : config.db_user, - password : config.db_password, - port : config.db_port, - database : config.db_name, - }); + var RoomInfomationModel = require("./init").GetRoomInfomation; var collection = {}; this.Get = function(rno){ return collection[rno]; @@ -45,7 +38,7 @@ module.exports = function() var async = require("async"); async.waterfall([ function(next){ - pool.query("TRUNCATE TABLE rooms",null,next); + RoomInfomationModel.drop().done(next); }, function(result,next){ var util = require("util"); @@ -72,7 +65,8 @@ module.exports = function() Add(rno,password,romonly); items.push(new Array(rno,password,romonly)); } - pool.query("INSERT INTO rooms VALUES ?",[items],callfunc); + newRoom = RoomInfomationModel.build(items); + newRoom.save().done(next); } ],callfunc); } @@ -81,13 +75,12 @@ module.exports = function() var async = require("async"); async.waterfall([ function(next){ - pool.query("SELECT * FROM rooms",null,next); + RoomInfomationModel.findAll().done(next); }, function(result,next){ for(var i = 0; i < result.length; i++) { - //MySQL‚Å‚ÍTINYINT‚ªŽg‚í‚ê‚Ä‚¢‚é - Add(result[i].number,result[i].password,result[i].hiddenlog != 0); + Add(result[i].number,result[i].password,result[i].hiddenlog); } next(null,null); }