1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
using System.Text;
using Dapper;
using Microsoft.Data.Sqlite;
namespace CrupestApi.Commons.Crud;
public class TableInfo
{
private readonly Lazy<List<string>> _lazyColumnNameList;
// For custom name.
public TableInfo(Type entityType)
: this(entityType.Name, entityType)
{
}
public TableInfo(string tableName, Type entityType)
{
TableName = tableName;
EntityType = entityType;
var properties = entityType.GetProperties();
var columnInfos = new List<ColumnInfo>();
bool hasPrimaryKey = false;
bool hasId = false;
foreach (var property in properties)
{
var columnInfo = new ColumnInfo(entityType, property.Name);
columnInfos.Add(columnInfo);
if (columnInfo.IsPrimaryKey)
hasPrimaryKey = true;
if (columnInfo.SqlColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
{
hasId = true;
}
}
if (!hasPrimaryKey)
{
if (hasId) throw new Exception("A column named id already exists but is not primary key.");
var columnInfo = new ColumnInfo(entityType, "id", true, true, ColumnTypeInfoRegistry.Singleton.GetRequiredByDataType(typeof(int)));
columnInfos.Add(columnInfo);
}
ColumnInfos = columnInfos;
CheckValidity();
_lazyColumnNameList = new Lazy<List<string>>(() => ColumnInfos.Select(c => c.SqlColumnName).ToList());
}
public Type EntityType { get; }
public string TableName { get; }
public IReadOnlyList<ColumnInfo> ColumnInfos { get; }
public IReadOnlyList<string> ColumnNameList => _lazyColumnNameList.Value;
public void CheckValidity()
{
// Check if there is only one primary key.
bool hasPrimaryKey = false;
foreach (var column in ColumnInfos)
{
if (column.IsPrimaryKey)
{
if (hasPrimaryKey) throw new Exception("Two columns are primary key.");
hasPrimaryKey = true;
}
}
if (!hasPrimaryKey) throw new Exception("No column is primary key.");
// Check two columns have the same sql name.
HashSet<string> sqlNameSet = new HashSet<string>();
foreach (var column in ColumnInfos)
{
if (sqlNameSet.Contains(column.SqlColumnName))
throw new Exception($"Two columns have the same sql name '{column.SqlColumnName}'.");
sqlNameSet.Add(column.SqlColumnName);
}
}
public string GenerateCreateIndexSql()
{
var sb = new StringBuilder();
foreach (var column in ColumnInfos)
{
if (column.IndexType == ColumnIndexType.None) continue;
sb.Append($"CREATE {(column.IndexType == ColumnIndexType.Unique ? "UNIQUE" : "")} INDEX {TableName}_{column.SqlColumnName}_index ON {TableName} ({column.SqlColumnName});\n");
}
return sb.ToString();
}
public string GenerateCreateTableSql(bool createIndex = true)
{
var tableName = TableName;
var columnSql = string.Join(",\n", ColumnInfos.Select(c => c.GenerateCreateTableColumnString()));
var sql = $@"
CREATE TABLE {tableName}(
{columnSql}
);
";
if (createIndex)
{
sql += GenerateCreateIndexSql();
}
return sql;
}
public async Task<bool> CheckExistence(SqliteConnection connection)
{
var tableName = TableName;
var count = (await connection.QueryAsync<int>(
@"SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
new { TableName = tableName })).Single();
if (count == 0)
{
return false;
}
else if (count > 1)
{
throw new DatabaseInternalException($"More than 1 table has name {tableName}. What happened?");
}
else
{
return true;
}
}
public string GenerateSelectSql(WhereClause? whereClause, OrderByClause? orderByClause, int? skip, int? limit, out DynamicParameters parameters)
{
if (whereClause is not null)
{
var relatedFields = ((IWhereClause)whereClause).GetRelatedColumns();
if (relatedFields is not null)
{
foreach (var field in relatedFields)
{
if (!ColumnNameList.Contains(field))
{
throw new ArgumentException($"Field {field} is not in the table.");
}
}
}
}
parameters = new DynamicParameters();
StringBuilder result = new StringBuilder()
.Append("SELECT * FROM ")
.Append(TableName);
if (whereClause is not null)
{
result.Append(' ');
result.Append(whereClause.GenerateSql(parameters));
}
if (orderByClause is not null)
{
result.Append(' ');
result.Append(orderByClause.GenerateSql());
}
if (limit is not null)
{
result.Append(" LIMIT @Limit");
parameters.Add("Limit", limit.Value);
}
if (skip is not null)
{
result.Append(" OFFSET @Skip");
parameters.Add("Skip", skip.Value);
}
result.Append(';');
return result.ToString();
}
}
|
