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
|
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using System.Diagnostics;
using System.IO;
using System.Collections;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Annotations;
namespace SuiviCollab.Dal.Configuration
{
class SqliteContextInitializer<T> : IDatabaseInitializer<T>
where T : DbContext
{
bool _dbExists;
DbModelBuilder _modelBuilder;
public SqliteContextInitializer(string dbPath,DbModelBuilder modelBuilder)
{
_dbExists = File.Exists(dbPath);
_modelBuilder = modelBuilder;
}
public void InitializeDatabase(T context)
{
if (_dbExists)
return;
var model = _modelBuilder.Build(context.Database.Connection);
using (var xact = context.Database.BeginTransaction())
{
try
{
CreateDatabase(context.Database, model);
xact.Commit();
}
catch (Exception)
{
xact.Rollback();
throw;
}
}
}
class Index
{
public string Name { get; set; }
public string Table { get; set; }
public List<string> Columns { get; set; }
}
private void CreateDatabase(Database db, DbModel model)
{
const string tableTmpl = "CREATE TABLE [{0}] (\n{1}\n);";
const string columnTmpl = " [{0}] {1} {2}"; // name, type, decl
const string primaryKeyTmpl = " PRIMARY KEY ({0})";
const string foreignKeyTmpl = " FOREIGN KEY ({0}) REFERENCES {1} ({2})";
const string indexTmpl = "CREATE INDEX {0} ON {1} ({2});";
var indicies = new Dictionary<string, Index>();
foreach (var type in model.StoreModel.EntityTypes)
{
var defs = new List<string>();
// columns
foreach (var p in type.Properties)
{
var decls = new HashSet<string>();
if (!p.Nullable)
decls.Add("NOT NULL");
var annotations = p.MetadataProperties
.Select(x => x.Value)
.OfType<IndexAnnotation>();
foreach (var annotation in annotations)
{
foreach (var attr in annotation.Indexes)
{
if (attr.IsUnique)
decls.Add("UNIQUE");
if (string.IsNullOrEmpty(attr.Name))
continue;
Index index;
if (!indicies.TryGetValue(attr.Name, out index))
{
index = new Index
{
Name = attr.Name,
Table = type.Name,
Columns = new List<string>(),
};
indicies.Add(index.Name, index);
}
index.Columns.Add(p.Name);
}
}
defs.Add(string.Format(columnTmpl, p.Name, p.TypeName, string.Join(" ", decls)));
}
// primary keys
if (type.KeyProperties.Any())
{
var keys = type.KeyProperties.Select(x => x.Name);
defs.Add(string.Format(primaryKeyTmpl, string.Join(", ", keys)));
}
// foreign keys
foreach (var assoc in model.StoreModel.AssociationTypes)
{
if (assoc.Constraint.ToRole.Name == type.Name)
{
var thisKeys = assoc.Constraint.ToProperties.Select(x => x.Name);
var thatKeys = assoc.Constraint.FromProperties.Select(x => x.Name);
defs.Add(string.Format(foreignKeyTmpl,
string.Join(", ", thisKeys),
assoc.Constraint.FromRole.Name,
string.Join(", ", thatKeys)));
}
}
// create table
var sql = string.Format(tableTmpl, type.Name, string.Join(",\n", defs));
db.ExecuteSqlCommand(sql);
}
// create index
foreach (var index in indicies.Values)
{
var columns = string.Join(", ", index.Columns);
var sql = string.Format(indexTmpl, index.Name, index.Table, columns);
db.ExecuteSqlCommand(sql);
}
}
}
} |
Créer un base de donnée SQLite avec Entity Framework
Répondre avec citation
Envoyé par RSS13
Partager