|
0
|
1 #region
|
|
|
2
|
|
|
3 using System;
|
|
|
4 using System.Collections;
|
|
|
5 using System.Collections.Generic;
|
|
|
6 using System.Linq;
|
|
|
7 using System.Text;
|
|
|
8 using BLToolkit.Aspects;
|
|
|
9 using BLToolkit.Data;
|
|
|
10 using BLToolkit.Data.DataProvider;
|
|
|
11 using BLToolkit.Mapping;
|
|
|
12
|
|
|
13 #endregion
|
|
|
14
|
|
|
15 namespace BLToolkit.DataAccess
|
|
|
16 {
|
|
|
17 public class FullSqlQuery : SqlQuery
|
|
|
18 {
|
|
|
19 private readonly bool _ignoreLazyLoad;
|
|
|
20
|
|
|
21 #region Constructors
|
|
|
22
|
|
|
23 public FullSqlQuery(DbManager dbManager, bool ignoreLazyLoad = false, FactoryType factoryType = FactoryType.LazyLoading)
|
|
|
24 : base(dbManager)
|
|
|
25 {
|
|
|
26 dbManager.MappingSchema = new FullMappingSchema(dbManager, ignoreLazyLoad, dbManager.MappingSchema, factoryType);
|
|
|
27
|
|
|
28 _ignoreLazyLoad = ignoreLazyLoad;
|
|
|
29 }
|
|
|
30
|
|
|
31 #endregion
|
|
|
32
|
|
|
33 #region Overrides
|
|
|
34
|
|
|
35 private readonly Hashtable _actionSqlQueryInfo = new Hashtable();
|
|
|
36
|
|
|
37 [NoInterception]
|
|
|
38 protected override SqlQueryInfo CreateSqlText(DbManager db, Type type, string actionName)
|
|
|
39 {
|
|
|
40 switch (actionName)
|
|
|
41 {
|
|
|
42 case "SelectByKey":
|
|
|
43 return CreateSelectFullByKeySqlText(db, type);
|
|
|
44 case "SelectAll":
|
|
|
45 return CreateSelectAllFullSqlText(db, type);
|
|
|
46 default:
|
|
|
47 return base.CreateSqlText(db, type, actionName);
|
|
|
48 }
|
|
|
49 }
|
|
|
50
|
|
|
51 [NoInterception]
|
|
|
52 public override SqlQueryInfo GetSqlQueryInfo(DbManager db, Type type, string actionName)
|
|
|
53 {
|
|
|
54 var key = type.FullName + "$" + actionName + "$" + db.DataProvider.UniqueName + "$" + GetTableName(type);
|
|
|
55 var query = (SqlQueryInfo) _actionSqlQueryInfo[key];
|
|
|
56
|
|
|
57 if (query == null)
|
|
|
58 {
|
|
|
59 query = CreateSqlText(db, type, actionName);
|
|
|
60 _actionSqlQueryInfo[key] = query;
|
|
|
61 }
|
|
|
62
|
|
|
63 return query;
|
|
|
64 }
|
|
|
65
|
|
|
66 #endregion
|
|
|
67
|
|
|
68 #region Private methods
|
|
|
69
|
|
|
70 private SqlQueryInfo CreateSelectAllFullSqlText(DbManager db, Type type)
|
|
|
71 {
|
|
|
72 var sb = new StringBuilder();
|
|
|
73 var query = new FullSqlQueryInfo();
|
|
|
74
|
|
|
75 sb.Append("SELECT\n");
|
|
|
76
|
|
|
77 var mainMapper = (FullObjectMapper) db.MappingSchema.GetObjectMapper(type);
|
|
|
78
|
|
|
79 BuildSelectSql(mainMapper, sb, db);
|
|
|
80
|
|
|
81 sb.Remove(sb.Length - 2, 1);
|
|
|
82
|
|
|
83 sb.Append("FROM\n\t");
|
|
|
84
|
|
|
85 FullAppendTableName(sb, db, type);
|
|
|
86
|
|
|
87 AppendJoinTableName(mainMapper, sb, db, type);
|
|
|
88
|
|
|
89 query.QueryText = sb.ToString();
|
|
|
90
|
|
|
91 return query;
|
|
|
92 }
|
|
|
93
|
|
|
94 private SqlQueryInfo CreateSelectFullByKeySqlText(DbManager db, Type type)
|
|
|
95 {
|
|
|
96 var sb = new StringBuilder();
|
|
|
97 var query = new FullSqlQueryInfo();
|
|
|
98
|
|
|
99 sb.Append("SELECT\n");
|
|
|
100
|
|
|
101 var mainMapper = (FullObjectMapper) db.MappingSchema.GetObjectMapper(type);
|
|
|
102 BuildSelectSql(mainMapper, sb, db);
|
|
|
103
|
|
|
104 sb.Remove(sb.Length - 2, 1);
|
|
|
105
|
|
|
106 sb.Append("FROM\n\t");
|
|
|
107
|
|
|
108 FullAppendTableName(sb, db, type);
|
|
|
109
|
|
|
110 AppendJoinTableName(mainMapper, sb, db, type);
|
|
|
111
|
|
|
112 AddWherePK(db, query, sb, -1, mainMapper);
|
|
|
113
|
|
|
114 query.QueryText = sb.ToString();
|
|
|
115
|
|
|
116 return query;
|
|
|
117 }
|
|
|
118
|
|
|
119 private void FullAppendTableName(StringBuilder sb, DbManager db, Type type)
|
|
|
120 {
|
|
|
121 var database = GetDatabaseName(type);
|
|
|
122 var owner = GetOwnerName(type);
|
|
|
123 var name = base.GetTableName(type);
|
|
|
124
|
|
|
125 db.DataProvider.CreateSqlProvider().BuildTableName(sb,
|
|
|
126 database == null ? null : db.DataProvider.Convert(database, ConvertType.NameToDatabase).ToString(),
|
|
|
127 owner == null ? null : db.DataProvider.Convert(owner, ConvertType.NameToOwner).ToString(),
|
|
|
128 name == null ? null : db.DataProvider.Convert(name, ConvertType.NameToQueryTable).ToString());
|
|
|
129
|
|
|
130 //TODO Override OracleSqlProvider in order to avoid this mess...
|
|
|
131 string alias = FullGetTableName(type);
|
|
|
132 sb.Append(" " + "T" /*alias*/);
|
|
|
133 sb.AppendLine();
|
|
|
134 }
|
|
|
135
|
|
|
136 private string FullGetTableName(Type type)
|
|
|
137 {
|
|
|
138 //bool isSet;
|
|
|
139 //return MappingSchema.MetadataProvider.GetTableName(type, Extensions, out isSet);
|
|
|
140
|
|
|
141 return type.Name;
|
|
|
142 }
|
|
|
143
|
|
|
144 private void BuildSelectSql(IObjectMapper mapper, StringBuilder sb, DbManager db, string tableName = "T")
|
|
|
145 {
|
|
|
146 int tableNr = 0;
|
|
|
147
|
|
|
148 foreach (IMapper mapField in mapper.PropertiesMapping)
|
|
|
149 {
|
|
|
150 if (mapField is ValueMapper)
|
|
|
151 sb.AppendFormat("\t{0}.{1} {2},\n"
|
|
|
152 , tableName /* (mapper).PropertyType.Name */,
|
|
|
153 db.DataProvider.Convert(((ValueMapper) mapField).ColumnName, ConvertType.NameToQueryField),
|
|
|
154 ((ValueMapper) mapField).ColumnAlias
|
|
|
155 );
|
|
|
156 else if (mapField is IPropertiesMapping)
|
|
|
157 {
|
|
|
158 var propertiesMapping = (IPropertiesMapping) mapField;
|
|
|
159 var cel = propertiesMapping.ParentMapping;
|
|
|
160 while (cel != null)
|
|
|
161 {
|
|
|
162 // To avoid recursion dont take in account types already loaded.
|
|
|
163 if (((IMapper) cel).PropertyType == mapField.PropertyType)
|
|
|
164 continue;
|
|
|
165 cel = cel.ParentMapping;
|
|
|
166 }
|
|
|
167
|
|
|
168 var objectMapper = (IObjectMapper) mapField;
|
|
|
169 if (!objectMapper.IsLazy)
|
|
|
170 BuildSelectSql(objectMapper, sb, db, tableName + tableNr.ToString());
|
|
|
171
|
|
|
172 tableNr++;
|
|
|
173 }
|
|
|
174 else
|
|
|
175 throw new NotImplementedException(mapField.GetType() + " is not yet implemented.");
|
|
|
176 }
|
|
|
177 }
|
|
|
178
|
|
|
179 private void AppendJoinTableName(IPropertiesMapping mapper, StringBuilder sb, DbManager db, Type type, string tableName = "T")
|
|
|
180 {
|
|
|
181 string parentName = FullGetTableName(type);
|
|
|
182 Dictionary<string, ValueMapper> valueMappers = mapper.PropertiesMapping.Where(e => e is ValueMapper).Cast<ValueMapper>().ToDictionary(e => e.PropertyName, e => e);
|
|
|
183
|
|
|
184 int tableNr = 0;
|
|
|
185
|
|
|
186 foreach (IMapper mapField in mapper.PropertiesMapping)
|
|
|
187 {
|
|
|
188 var objectMapper = mapField as IObjectMapper;
|
|
|
189 if (objectMapper != null)
|
|
|
190 {
|
|
|
191 if (!_ignoreLazyLoad)
|
|
|
192 {
|
|
|
193 if (objectMapper.IsLazy)
|
|
|
194 continue;
|
|
|
195 }
|
|
|
196
|
|
|
197 string thisKey = objectMapper.Association.ThisKey[0];
|
|
|
198
|
|
|
199 // TITLE
|
|
|
200 string parentDbField = valueMappers.ContainsKey(thisKey) ? valueMappers[thisKey].ColumnName : thisKey;
|
|
|
201
|
|
|
202 // ARTIST
|
|
|
203 string childDbField = objectMapper.PropertiesMapping.Where(e => e is ValueMapper).Cast<ValueMapper>().First(
|
|
|
204 e => e.PropertyName == objectMapper.Association.OtherKey[0]).ColumnName;
|
|
|
205
|
|
|
206 string childDatabase = GetDatabaseName(mapField.PropertyType);
|
|
|
207 string childOwner = base.GetOwnerName(mapField.PropertyType);
|
|
|
208 string childName = base.GetTableName(mapField.PropertyType);
|
|
|
209 string childAlias = FullGetTableName(mapField.PropertyType);
|
|
|
210
|
|
|
211 StringBuilder childFullName = db.DataProvider.CreateSqlProvider().BuildTableName(
|
|
|
212 new StringBuilder(),
|
|
|
213 childDatabase == null
|
|
|
214 ? null
|
|
|
215 : db.DataProvider.Convert(childDatabase, ConvertType.NameToDatabase).ToString(),
|
|
|
216 childOwner == null
|
|
|
217 ? null
|
|
|
218 : db.DataProvider.Convert(childOwner, ConvertType.NameToOwner).ToString(),
|
|
|
219 childName == null
|
|
|
220 ? null
|
|
|
221 : db.DataProvider.Convert(childName, ConvertType.NameToQueryTable).ToString());
|
|
|
222
|
|
|
223 sb.AppendFormat("\tFULL OUTER JOIN {0} {1} ON {2}.{3}={4}.{5}\n",
|
|
|
224 childFullName,
|
|
|
225 tableName + tableNr.ToString() /*childAlias*/,
|
|
|
226 tableName /*parentName*/,
|
|
|
227 parentDbField,
|
|
|
228 tableName + tableNr.ToString() /*childAlias*/,
|
|
|
229 childDbField
|
|
|
230 );
|
|
|
231
|
|
|
232 AppendJoinTableName((IPropertiesMapping)mapField, sb, db, mapField.PropertyType, tableName + tableNr.ToString());
|
|
|
233
|
|
|
234 tableNr++;
|
|
|
235 }
|
|
|
236 }
|
|
|
237
|
|
|
238 sb.AppendLine();
|
|
|
239
|
|
|
240 //SELECT
|
|
|
241 // ARTIST2.ID_ARTIST,
|
|
|
242 // ARTIST2.ARTIST,
|
|
|
243 // TRACK.ID_TRACK,
|
|
|
244 // TRACK.TRACK,
|
|
|
245 // TRACK.ID_ARTIST,
|
|
|
246 // ARTIST.ID_ARTIST,
|
|
|
247 // ARTIST.ARTIST
|
|
|
248 //FROM
|
|
|
249 // PITAFR01.ARTIST ARTIST2
|
|
|
250 // INNER JOIN PITAFR01.TRACK TRACK ON ARTIST2.ID_ARTIST=TRACK.ID_ARTIST
|
|
|
251 // INNER JOIN PITAFR01.ARTIST ARTIST ON TRACK.ID_ARTIST=ARTIST.ID_ARTIST
|
|
|
252 //WHERE
|
|
|
253 // ARTIST2.ID_ARTIST = 2566
|
|
|
254 }
|
|
|
255
|
|
|
256 private void AddWherePK(DbManager db, SqlQueryInfo query, StringBuilder sb, int nParameter, FullObjectMapper mapper)
|
|
|
257 {
|
|
|
258 sb.Append("WHERE\n");
|
|
|
259
|
|
|
260 foreach (IMapper mm in mapper.PropertiesMapping)
|
|
|
261 {
|
|
|
262 if (mm is ValueMapper && mm.DataReaderIndex == mapper.DataReaderIndex)
|
|
|
263 {
|
|
|
264 var valueMapper = (ValueMapper) mm;
|
|
|
265
|
|
|
266 string tableAlias = mapper.PropertyType.Name;
|
|
|
267
|
|
|
268 //mm.Name = ID_TRACK
|
|
|
269 SqlQueryParameterInfo p = query.AddParameter(
|
|
|
270 db.DataProvider.Convert(valueMapper.ColumnName + "_W", ConvertType.NameToQueryParameter).
|
|
|
271 ToString(),
|
|
|
272 valueMapper.ColumnName);
|
|
|
273
|
|
|
274 sb.AppendFormat("\t{0}.{1} = ", "T" /* tableAlias */,
|
|
|
275 db.DataProvider.Convert(p.FieldName, ConvertType.NameToQueryField));
|
|
|
276
|
|
|
277 if (nParameter < 0)
|
|
|
278 sb.AppendFormat("{0} AND\n", p.ParameterName);
|
|
|
279 else
|
|
|
280 sb.AppendFormat("{{{0}}} AND\n", nParameter++);
|
|
|
281 }
|
|
|
282 }
|
|
|
283
|
|
|
284 sb.Remove(sb.Length - 5, 5);
|
|
|
285 }
|
|
|
286
|
|
|
287 #endregion
|
|
|
288 }
|
|
|
289 } |
