跳到主要内容
登录

github/tai

公开

Tai · Windows 软件与网站使用时长统计。上游 Planshit/Tai 的源码镜像，非灵栈原创；MIT 许可。来源：https://github.com/Planshit/Tai 。本次为手动同步，暂不提供安装包。

浏览文件
main

分支

标签

SQLiteBuilder.cs

515 行 · 15.9 KB历史下载
1using System;
2using System.Collections.Generic;
3using System.Data.SQLite;
4using System.Diagnostics;
5using System.IO;
6using System.Linq;
7using System.Reflection;
8using System.Text;
9using System.Threading.Tasks;
10
11namespace Core.Librarys.SQLite
12{
13 /// <summary>
14 /// 数据模型信息接口
15 /// </summary>
16 public class IModelInfo
17 {
18 /// <summary>
19 /// 模型名
20 /// </summary>
21 public string Name { get; set; }
22 /// <summary>
23 /// 模型数据库表名
24 /// </summary>
25 public string TableName { get { return Name + "s"; } }
26 /// <summary>
27 /// 获取模型所有属性
28 /// </summary>
29 public List<IModelProperties> Properties { get; set; }
30 }
31 /// <summary>
32 /// 模型属性接口
33 /// </summary>
34 public class IModelProperties
35 {
36 /// <summary>
37 /// 列名
38 /// </summary>
39 public string Name { get; set; }
40 public DbType Type { get; set; }
41 public bool PK { get; set; }
42 //public bool NotNull { get { return !PK; } }
43 }
44
45 public enum DbType
46 {
47 INTEGER,
48 datetime,
49 nvarchar,
50 @int,
51 bit
52 }
53
54 /// <summary>
55 ///handle sqlite code first
56 /// </summary>
57 public class SQLiteBuilder
58 {
59 public static bool IsSelfChecking = true;
60
61 private System.Data.Entity.Infrastructure.DbModel model;
62 /// <summary>
63 /// 连接字符串
64 /// </summary>
65 private string connstr;
66 /// <summary>
67 /// 数据库版本文件
68 /// </summary>
69 private string versionFile;
70
71 /// <summary>
72 /// 数据库文件路径
73 /// </summary>
74 private string dbFile;
75 /// <summary>
76 /// 数据模型
77 /// </summary>
78 private System.Data.Entity.Core.Metadata.Edm.EdmModel storeModel;
79 private System.Data.Entity.Core.Common.DbProviderManifest providerManifest;
80 public SQLiteBuilder(System.Data.Entity.Infrastructure.DbModel model)
81 {
82 this.model = model;
83
84 if (model != null)
85 {
86 connstr = model.ProviderInfo.ProviderManifestToken;
87
88 storeModel = model.StoreModel;
89
90 providerManifest = model.ProviderManifest;
91
92 dbFile = connstr.Split('=')[1];
93
94 versionFile = $"{dbFile}.version";
95
96 }
97 }
98
99 /// <summary>
100 /// 执行数据库自检
101 /// </summary>
102 public bool SelfCheck()
103 {
104 if (IsHandleDb())
105 {
106 bool res = HandleTable();
107
108 IsSelfChecking = false;
109
110 return res;
111 }
112
113 IsSelfChecking = false;
114
115 return true;
116 }
117
118 /// <summary>
119 /// 处理表变化（仅处理新增表、新增表字段、表结构变化）
120 /// </summary>
121 private bool HandleTable()
122 {
123 try
124 {
125 bool isNew = true;
126
127 // 处理前先复制一份数据库文件以防万一
128 string dir = Path.Combine(Path.GetDirectoryName(dbFile), "backup");
129 string backupName = Path.Combine(dir, $"data.handle.backup");
130 if (!Directory.Exists(dir))
131 {
132 Directory.CreateDirectory(dir);
133 }
134 if (File.Exists(backupName))
135 {
136 File.Delete(backupName);
137 }
138
139 if (File.Exists(dbFile))
140 {
141 isNew = false;
142
143 File.Copy(dbFile, backupName);
144 }
145 var modelInfos = GetModelInfos();
146
147 var modelInfosDb = GetModelInfosForDb();
148
149 foreach (var model in modelInfos)
150 {
151 //表名
152 string tableName = model.TableName;
153
154 var modelInfoDb = modelInfosDb.Where(m => m.TableName == tableName);
155 //判断表是否存在
156 if (modelInfoDb.Count() <= 0)
157 {
158 //不存在则重新创建表
159 string csql = GetCreateTableSQL(model);
160 ExecuteNonQuery(csql);
161 }
162 else
163 {
164 //存在则扫描新字段（*暂时不处理字段的属性变化，只处理新增）
165
166 //对应的数据库模型信息
167 var modelDb = modelInfoDb.Single();
168 //查找差异属性
169 foreach (var item in model.Properties)
170 {
171 if (modelDb.Properties.Where(m => m.Name == item.Name).Count() <= 0)
172 {
173 string csql = GetCreateColumnSQL(tableName, item);
174 ExecuteNonQuery(csql);
175 }
176 }
177
178 }
179
180 }
181
182
183 // 处理表字段删除
184 foreach (var dbModel in modelInfosDb)
185 {
186 // 表名
187 string tableName = dbModel.TableName;
188
189 // 查找对应的实体模型
190 var model = modelInfos.Where(m => m.TableName == tableName).FirstOrDefault();
191
192 if (model == null)
193 {
194 // 已删除的模型就从数据库中删除
195 //ExecuteNonQuery($"DROP TABLE {tableName}");// 暂时不处理
196 }
197 else
198 {
199 // 遍历数据库表的属性
200
201 bool isDel = false;
202
203
204
205 foreach (var dbItem in dbModel.Properties)
206 {
207 // 判断实体模型是否有字段，没有的话需要重建表
208 if (!model.Properties.Where(m => m.Name == dbItem.Name).Any())
209 {
210 isDel = true;
211 break;
212 }
213
214
215 }
216 if (isDel)
217 {
218 // 模型字段
219 string field = string.Join(",", model.Properties.Select(m => m.Name).ToArray());
220
221 // 临时表名
222 string tempName = $"{tableName}_temp_{DateTime.Now.ToString("yyyyMMddHHmmss")}";
223
224 // 创建一个新表
225 ExecuteNonQuery(GetCreateTableSQL(model, tempName));
226
227 //// 转移数据
228 ExecuteNonQuery($"INSERT INTO {tempName}({field}) SELECT {field} FROM {tableName}");
229
230
231 // 删除旧表
232 ExecuteNonQuery($"drop table {tableName}");
233
234
235 // 重新命名临时表
236 ExecuteNonQuery($"alter table {tempName} rename to {tableName}");
237 }
238 }
239
240 }
241 return true;
242 }
243 catch (Exception ex)
244 {
245 Logger.Error($"SQLiteBuilder handle fail!Exception Message:{ex.Message}");
246 return false;
247 }
248 }
249
250
251
252 private string SQLiteEscape(string str)
253 {
254 str = str.Replace("/", "//");
255 str = str.Replace("'", "''");
256 str = str.Replace("[", "/[");
257 str = str.Replace("]", "/]");
258 str = str.Replace("%", "/%");
259 str = str.Replace("&", "/&");
260 str = str.Replace("_", "/_");
261 str = str.Replace("(", "/(");
262 str = str.Replace(")", "/)");
263 return str;
264 }
265
266 #region 是否需要处理
267 /// <summary>
268 /// 判断是否需要处理数据，比对core程序集版本号
269 /// </summary>
270 /// <returns></returns>
271 private bool IsHandleDb()
272 {
273 string dir = Path.GetDirectoryName(versionFile);
274 if (!Directory.Exists(dir))
275 {
276 Directory.CreateDirectory(dir);
277 }
278
279 bool f = File.Exists(versionFile);
280
281 // core程序集版本号
282 string coreVersion = Assembly.GetExecutingAssembly().GetName().Version.ToString();
283 if (f)
284 {
285 //存在版本文件对比是否需要更新
286 string dbVersion = File.ReadAllText(versionFile);
287 if (dbVersion != coreVersion)
288 {
289 //版本不一致需要更新
290 File.WriteAllText(versionFile, coreVersion);
291 return true;
292 }
293 //不需要更新
294 return false;
295 }
296
297 File.WriteAllText(versionFile, coreVersion);
298 return true;
299 }
300
301 #endregion
302
303 #region 生成创建列SQL语句
304 /// <summary>
305 /// 生成创建列SQL语句
306 /// </summary>
307 /// <param name="tableName">表名</param>
308 /// <param name="properties">属性</param>
309 /// <returns></returns>
310 private string GetCreateColumnSQL(string tableName, IModelProperties properties)
311 {
312 string typeDefault = !properties.PK ? properties.Type == DbType.@int ? "NULL DEFAULT 0" : "NULL DEFAULT ''" : "";
313
314 if(properties.Type == DbType.bit)
315 {
316 typeDefault = " DEFAULT 0";
317 properties.Type = DbType.INTEGER;
318 }
319
320 if (properties.PK)
321 {
322 properties.Type = DbType.INTEGER;
323 }
324
325 return $"ALTER table {tableName} ADD COLUMN [{properties.Name}] {properties.Type} {typeDefault}";
326 }
327 #endregion
328
329 #region 生成创建表SQL语句
330 /// <summary>
331 /// 生成创建表SQL语句
332 /// </summary>
333 /// <param name="model">模型信息</param>
334 /// <returns></returns>
335 private string GetCreateTableSQL(IModelInfo model, string tableName = null)
336 {
337 tableName = string.IsNullOrEmpty(tableName) ? model.TableName : tableName;
338
339 string sql = $"CREATE TABLE \"{tableName}\" (";
340 foreach (var p in model.Properties)
341 {
342 string typeDefault = p.Type == DbType.@int ? " NULL DEFAULT 0" : " NULL DEFAULT ''";
343
344 if (p.Type == DbType.bit)
345 {
346 typeDefault = " DEFAULT 0";
347 p.Type = DbType.INTEGER;
348 }
349
350 if (p.PK)
351 {
352 typeDefault = "";
353 p.Type = DbType.INTEGER;
354 }
355
356 string add = p.PK ? $"{p.Type} PRIMARY KEY" : p.Type.ToString();
357 string addChar = p == model.Properties[0] ? "" : ",";
358 sql += $"{addChar}[{p.Name}] {add}{typeDefault}";
359 }
360 sql += ")";
361 return sql;
362 }
363 #endregion
364
365 #region 判断表是否存在数据库中
366 /// <summary>
367 /// 判断表是否存在数据库中
368 /// </summary>
369 /// <param name="tableName"></param>
370 /// <returns></returns>
371 private bool IsTableExistDb(string tableName)
372 {
373 using (var con = new SQLiteConnection(@"Data Source=.\data.db"))
374 {
375 con.Open();
376 using (var cmd = new SQLiteCommand(con))
377 {
378 cmd.CommandText = "select count(*) from sqlite_master where type='table' and name = 'StatisticModels3'";
379 var rd = cmd.ExecuteReader();
380 if (rd.Read())
381 {
382 var count = rd.GetInt32(0);
383 return count > 0;
384 }
385 return false;
386 }
387
388 }
389 }
390
391 #endregion
392
393 #region 获取所有数据模型的信息（从代码）
394 /// <summary>
395 /// 获取所有数据模型的信息（从代码）
396 /// </summary>
397 /// <returns></returns>
398 private List<IModelInfo> GetModelInfos()
399 {
400 var result = new List<IModelInfo>();
401
402 foreach (var model in storeModel.EntityTypes)
403 {
404 var i = new IModelInfo();
405 var ps = new List<IModelProperties>();
406
407 i.Name = model.Name;
408 i.Properties = ps;
409 //属性获取
410 foreach (var p in model.Properties)
411 {
412 Enum.TryParse(p.TypeName, out DbType dbType);
413
414 ps.Add(new IModelProperties()
415 {
416 Name = p.Name,
417 PK = p.IsStoreGeneratedIdentity,
418 Type = dbType
419 });
420 }
421 result.Add(i);
422 }
423 return result;
424 }
425 #endregion
426
427 #region 获取所有数据模型的信息（从数据库）
428 /// <summary>
429 /// 获取所有数据模型的信息（从数据库）
430 /// </summary>
431 /// <returns></returns>
432 private List<IModelInfo> GetModelInfosForDb()
433 {
434 var result = new List<IModelInfo>();
435
436 using (var con = new SQLiteConnection(connstr))
437 {
438 con.Open();
439 using (var cmd = new SQLiteCommand(con))
440 {
441 cmd.CommandText = "select name from sqlite_master where type='table' order by name";
442 using (var rd = cmd.ExecuteReader())
443 {
444
445
446 while (rd.Read())
447 {
448 var modelInfo = new IModelInfo();
449 modelInfo.Name = rd["name"].ToString().Substring(0, rd["name"].ToString().Length - 1);
450 modelInfo.Properties = GetModelProperties(modelInfo.TableName);
451 result.Add(modelInfo);
452 }
453 }
454 }
455
456 }
457
458 return result;
459 }
460 #endregion
461
462 #region 获取表的所有属性（从数据库）
463 private List<IModelProperties> GetModelProperties(string tableName)
464 {
465 var result = new List<IModelProperties>();
466
467 using (var con = new SQLiteConnection(connstr))
468 {
469 con.Open();
470 using (var cmd = new SQLiteCommand(con))
471 {
472 cmd.CommandText = $"PRAGMA table_info([{tableName}])";
473 using (var rd = cmd.ExecuteReader())
474 {
475 while (rd.Read())
476 {
477 var mp = new IModelProperties();
478 mp.Name = rd["name"].ToString();
479 Enum.TryParse(rd["type"].ToString(), out DbType dbType);
480 mp.Type = dbType;
481 mp.PK = rd["pk"].ToString() == "1";
482 result.Add(mp);
483 }
484 }
485 }
486 }
487 return result;
488 }
489 #endregion
490
491
492 //sqlite帮助
493 /// <summary>
494 /// 执行一条sql语句，返回影响行数
495 /// </summary>
496 /// <param name="sql"></param>
497 /// <returns></returns>
498 private int ExecuteNonQuery(string sql)
499 {
500 using (var con = new SQLiteConnection(connstr))
501 {
502 con.Open();
503 using (var cmd = new SQLiteCommand(con))
504 {
505 cmd.CommandText = sql;
506 return cmd.ExecuteNonQuery();
507 }
508
509 }
510 }
511
512
513 }
514
515}