static DnsRecordsSimple Row2DnsRecords(dnsrecords dr) { DnsRecordsSimple d = new DnsRecordsSimple(); d.rid = long.Parse(dr.id.ToString()); d.domain = dr.zone.ToString().ToLower() + "."; d.name = dr.host.ToString().ToLower(); d.type = dr.type.ToString(); if (d.type == "MX") { d.rdata = dr.mx_priority.ToString() + " " + dr.data.ToString().ToLower(); } else if (d.type == "TXT") { d.rdata = dr.data.ToString().Replace("\"", string.Empty); } else { d.rdata = dr.data.ToString(); } d.ttl = Convert.ToInt32(dr.ttl); d.view = dr.view.ToString(); d.userid = Convert.ToInt32(dr.userid); d.is_stop = dr.active == "Y"?"N":"Y"; return(d); }
static void MongoInsertFromDnsrecords() { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start();//开始计时 // //DataSet ds = MySQLHelper.Query("select zone,host,type,data,ttl,view,mx_priority from dnsrecordsregular where zone in (SELECT distinct Zone from zones WHERE NSState=1 ORDER BY Zone LIMIT 0,10000)"); //DataSet ds = MySQLHelper.Query("select a.zone,a.host,a.type,a.data,a.ttl,a.view,a.mx_priority,a.userid from (SELECT DISTINCT zone from zonestemp where nsstate=1 and isload=1 order BY zone LIMIT 0,500)as t inner join dnsrecordsregular as a on t.zone=a.Zone"); //指定数量 //DataSet ds = MySQLHelper.Query("select zone,host,type,data,ttl,view,mx_priority,userid from dnsrecordstemp where zoneid in(SELECT id from zonestemp where IsLoad=1 and( (id<860948 and DomainLevel=0) or (DomainLevel>1 and id<1460556 ))) and Host not like %*%"); DataSet ds = MySQLHelper.Query("select zone,host,type,data,ttl,view,mx_priority,userid from dnsrecordstemp where zoneid in(1782993)"); long q = watch.ElapsedMilliseconds; DataTable dt = ds.Tables[0]; long count = dt.Rows.Count; Console.WriteLine("query use time:" + q.ToString() + ";" + count + " rows data"); List <DnsRecordsSimple>[] dla = new List <DnsRecordsSimple>[16] { new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>() }; //for (int i = 0; i < 16; i++) //{ // string a = i.ToString("x"); // Console.WriteLine(a + " " + Int32.Parse(a, System.Globalization.NumberStyles.HexNumber)); //} foreach (DataRow dr in ds.Tables[0].Rows) { DnsRecordsSimple d = Row2DnsRecords(dr); string collectionname = StringHelper.CalculateMD5Hash(d.domain).ToLower().Substring(0, 1); int idx = Int32.Parse(collectionname, System.Globalization.NumberStyles.HexNumber); dla[idx].Add(d); } Console.WriteLine("List count" + dla.Length); try { //using (Mongo mongo = new Mongo(MongoHelper.connectionString)) //{ // mongo.Connect(); // IMongoDatabase mongodatabase = mongo.GetDatabase(MongoHelper.database); // for (int i = 0; i < 16; i++) // { // IMongoCollection<DnsRecordsSimple> collection = mongodatabase.GetCollection<DnsRecordsSimple>(i.ToString("x")); // if(dla[i].Count>0) // collection.Insert(dla[i], true); // Console.WriteLine("List " + i.ToString("x") + " obj count " + dla[i].Count); // } // watch.Stop();//停止计时 // mongo.Disconnect(); //} } catch (Exception ex) { string re = ex.ToString(); } }
static DnsRecordsSimple Row2DnsRecords(DataRow dr) { DnsRecordsSimple d = new DnsRecordsSimple(); d.domain = dr[0].ToString().ToLower() + "."; d.name = dr[1].ToString().ToLower(); d.type = dr[2].ToString(); if (d.type == "MX") { d.rdata = dr[6].ToString() + " " + dr[3].ToString().ToLower(); } else if (d.type == "TXT") { d.rdata = dr[3].ToString().Replace("\"", string.Empty); } else { d.rdata = dr[3].ToString(); } d.ttl = Convert.ToInt32(dr[4]); d.view = dr[5].ToString(); d.userid = Convert.ToInt32(dr[7]); return(d); }
static void RefreshNewColumn() { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start();//开始计时 List <ZonesSimple> dl = new List <ZonesSimple>(); List <AuthoritiesSimple>[] ala = new List <AuthoritiesSimple>[16] { new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>() }; List <DnsRecordsSimple>[] dla = new List <DnsRecordsSimple>[16] { new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>() }; var client = DriverConfiguration.Client; var db = client.GetDatabase(DriverConfiguration.DatabaseNamespace.DatabaseName); IMongoCollection <ZonesSimple> cz = db.GetCollection <ZonesSimple>("zones"); DataTable dt = MySQLHelper.Query("SELECT z.id,z.zone,z.userid,CASE when d.maxfensheng<1 then 0 else 1 end as level,z.nsstate,z.active,z.forcestop from zones as z left join domainlevel as d on z.DomainLevel=d.levelvalue where z.userid<>348672 and (z.Active='N' or z.ForceStop='Y' ) ").Tables[0]; DataTable dta = MySQLHelper.Query("select a.id,a.zone,host,data,type,ttl,mbox,serial,refresh,retry,expire,minimum ,t.userid,a.zoneid from authorities as a left join zones as t on a.ZoneID=t.id where t.userid<>348672 and (t.Active='N' or t.ForceStop='Y')").Tables[0]; DataTable dtd = MySQLHelper.Query("select d.id,d.zoneid,d.zone,d.host,d.type,d.data,d.ttl,d.view,d.mx_priority,d.userid,d.active from dnsrecords as d left join zones as t on d.ZoneID=t.id where t.userid<>348672 and (t.Active='N' or t.ForceStop='Y')").Tables[0]; Console.WriteLine("zones total count " + dt.Rows.Count); Console.WriteLine("authorities total count " + dta.Rows.Count); Console.WriteLine("dnsrecords total count " + dtd.Rows.Count); List <zones> zonesList = DtToList <zones> .ConvertToModel(dt); int deleteCount = 0; IList <string> zoneArry = new List <string>(); foreach (zones z in zonesList) { zoneArry.Add(z.zone + "."); ZonesSimple zs = Row2ZoneSimple(z); dl.Add(zs); if ((deleteCount > 0 && deleteCount % 200 == 0) || deleteCount == zonesList.Count) { cz.DeleteMany(Builders <ZonesSimple> .Filter.In("domain", zoneArry)); string rrcol = StringHelper.CalculateMD5Hash(z.zone + ".").Substring(0, 1); IMongoCollection <DnsRecordsSimple> cd = db.GetCollection <DnsRecordsSimple>(rrcol); cd.DeleteMany(Builders <DnsRecordsSimple> .Filter.In("domain", zoneArry)); Console.WriteLine("delete count " + deleteCount + " use time " + watch.ElapsedMilliseconds); if (dl.Count > 0) { cz.InsertMany(dl); Console.WriteLine("insert zones use time " + watch.ElapsedMilliseconds); } zoneArry.Clear(); dl.Clear(); } deleteCount++; } Console.WriteLine("delete complete use time " + watch.ElapsedMilliseconds); List <authorities> aList = DtToList <authorities> .ConvertToModel(dta); List <authorities> drl = new List <authorities>(); string domain = ""; for (int idx = 0; idx < aList.Count;) { domain = aList[idx].zone.ToString().ToLower() + "."; string collectionname = StringHelper.CalculateMD5Hash(domain).ToLower().Substring(0, 1); drl.Add(aList[idx]); while (idx < (aList.Count - 1) && aList[idx].zone == aList[idx + 1].zone) { drl.Add(aList[idx + 1]); idx++; } if (drl.Count > 1) { foreach (AuthoritiesSimple a in Row2Authorities(drl)) { ala[Int32.Parse(collectionname, System.Globalization.NumberStyles.HexNumber)].Add(a); } } drl.Clear(); domain = ""; idx++; } List <dnsrecords> rList = DtToList <dnsrecords> .ConvertToModel(dtd); List <dnsrecords> unList = new List <dnsrecords>(); foreach (dnsrecords dr in rList) { if (CheckRecordHost(dr.host, dr.type) && CheckRecordData(dr.data, dr.type, dr.view, dr.host)) { DnsRecordsSimple d = Row2DnsRecords(dr); string collectionname = StringHelper.CalculateMD5Hash(d.domain).ToLower().Substring(0, 1); int idx = Int32.Parse(collectionname, System.Globalization.NumberStyles.HexNumber); dla[idx].Add(d); } else { unList.Add(dr); } } Console.WriteLine("start insert use time " + watch.ElapsedMilliseconds); for (int i = 0; i < 16; i++) { IMongoCollection <DnsRecordsSimple> collection = db.GetCollection <DnsRecordsSimple>(i.ToString("x")); if (ala[i].Count > 0) { collection.InsertMany(ala[i]); } if (dla[i].Count > 0) { collection.InsertMany(dla[i]); } } if (unList.Count > 0) { Console.WriteLine("uninsert dnsrecords " + unList.Count); } Console.WriteLine("mission complete use time " + watch.ElapsedMilliseconds); }
static void MongoInsertFromDnsrecords() { try { List <TempZoneID> temp = DtToList <TempZoneID> .ConvertToModel(MySQLHelper.Query("select id as zoneid from Temp").Tables[0]); DataTable dtid = MySQLHelper.Query("select min(id),max(id) from zones").Tables[0]; long min = Convert.ToInt32(dtid.Rows[0][0]); long max = Convert.ToInt32(dtid.Rows[0][1]); System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start();//开始计时 long index = min; do { DataTable dt = MySQLHelper.Query("select d.id,d.zoneid,d.zone,d.host,d.type,d.data,d.ttl,d.view,d.mx_priority,d.userid,d.active from dnsrecords as d left join zones as t on d.ZoneID=t.id where d.Active='Y' and d.type<>'PTR' and d.ZoneID BETWEEN " + index + " and " + (index + 20000) + " and t.userid<>348672 and t.id is not NULL and t.Active='Y' and t.ForceStop='N' order by d.zone").Tables[0]; Console.WriteLine("GetDataTabel from mysql Use Time={0};", watch.ElapsedMilliseconds); List <dnsrecords> rListtemp = DtToList <dnsrecords> .ConvertToModel(dt); Console.WriteLine("DataTable Convert to ModelList; Use time={0};", watch.ElapsedMilliseconds); List <dnsrecords> rList = new List <dnsrecords>(); foreach (dnsrecords d in rListtemp) { if (temp.FindAll(tz => tz.zoneid == d.zoneid).Count == 0) { rList.Add(d); } } List <dnsrecords> unList = new List <dnsrecords>(); List <DnsRecordsSimple>[] dla = new List <DnsRecordsSimple>[16] { new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>() }; foreach (dnsrecords dr in rList) { if (CheckRecordHost(dr.host, dr.type) && CheckRecordData(dr.data, dr.type, dr.view, dr.host)) { DnsRecordsSimple d = Row2DnsRecords(dr); string collectionname = StringHelper.CalculateMD5Hash(d.domain).ToLower().Substring(0, 1); int idx = Int32.Parse(collectionname, System.Globalization.NumberStyles.HexNumber); dla[idx].Add(d); } else { unList.Add(dr); } } Console.WriteLine("ModelList Convert and Filter; Use time={0};", watch.ElapsedMilliseconds); var client = DriverConfiguration.Client; var db = client.GetDatabase(DriverConfiguration.DatabaseNamespace.DatabaseName); for (int i = 0; i < 16; i++) { IMongoCollection <DnsRecordsSimple> collection = db.GetCollection <DnsRecordsSimple>(i.ToString("x")); if (dla[i].Count > 0) { collection.InsertMany(dla[i]); } } Console.WriteLine("MongoDB Inserted; Use time={0};", watch.ElapsedMilliseconds); if (unList.Count > 0) { IMongoCollection <dnsrecords> collection2 = db.GetCollection <dnsrecords>("dnsrecordswrong"); collection2.InsertMany(unList); } Console.WriteLine("MongoDB Inserted Wrong Data; Use time={0};", watch.ElapsedMilliseconds); index = index + 20001; Console.WriteLine("min={0};max={1};index={2};use time {3}", min, max, index, watch.ElapsedMilliseconds); Console.WriteLine("=============================================="); } while (index < max); Console.WriteLine("End min={0};max={1};index={2};use time {3}", min, max, index, watch.ElapsedMilliseconds); watch.Stop();//停止计时 } catch (Exception ex) { Console.WriteLine(ex.Message); } }