Developing and Testing Data Access of SQLCEPad tool using Self-Shunt and Stub tetsing patterns
Sometimes ago I developed a simple tool for creating and executing SQL statements against SQL CE 3.5 database. Yesterday, I’ve decided to re-develop and improve it in a new version. I’ve started with implementation of data access part of it. The data access is very simple; it retrieves all needed DB schema meta-data that the tool uses. Having in mind all functionality that I’ve needed I’ve started with writing a unit test and thinking of how to implement data access in order to be able to test the logic that builds my DB schema object based on metadata information.
In my old tool implementation, I had one class that looks like this below.
1: public sealed class SqlCEDBSchemeHelper
2: {
3: public static void LoadDbScheme(string connectionString, DbSchema dbScheme)
4: {
5: // implementation omitted
6: }
7:
8: private static SqlCeDataReader GetColumnsMetaData(SqlCeConnection connection)
9: {
10: // implementation omitted
11: }
12:
13: private static SqlCeDataReader GetConstraintsMetaData(SqlCeConnection connection)
14: {
15: // implementation omitted
16: }
17:
18: private static SqlCeDataReader GetReferentialConstraintsMetaData(SqlCeConnection connection)
19: {
20: // implementation omitted
21: }
22: }
What I found here is that it is not possible to test the logic that LoadDbScheme() implements and this way I cannot write a unit test that will ensure that the method works as it is expected. That’s why because it is impossible to mock the methods that retrieve data from DB. It is needed in order to test LoadDbScheme() with a specific data which is independent from real database meta-data. As you may see the methods that retrieve meta-data information are private for the class and this way LoadDbScheme() and these methods are strongly coupled.
Knowing what I need and problems of my first implementation I’ve started to write a unit test. Than I needed some interfaces that will help me to complete first implementation of the test. And I came up with too interfaces as below.
1: public interface IDataRetriever
2: {
3: void Configure(string connectionString);
4: IDataReader GetColumnsMetaData();
5: IDataReader GetConstraintsMetaData();
6: IDataReader GetReferentialConstraintsMetaData();
7: }
8:
9: public interface IDbSchemaBuilder
10: {
11: DbSchema LoadDbSchema(IDataRetriever retriever);
12: }
These two interfaces help me to de-couple DB schema builder object from DB meta-data retrieving logic. With these interfaces I was able to complete the test and to try to compile and run it in green. Here I had two options in my mind how to complete the test. The first one was to use mock object for IDataRetriever using some mock framework and the second one was to use test itself as a mock using self-shunt pattern. I choose the second solution for this specific case. I found it more clear for my test. My test at this moment is below.
1: [TestFixture]
2: public class DataAdapterTest : IDataRetriever
3: {
4: [Test]
5: public void TestLoadDbSchema()
6: {
7:
8: }
9:
10: #region IDataRetriever Members
11:
12: public void Configure(string connectionString) { }
13:
14: public System.Data.IDataReader GetColumnsMetaData() { }
15:
16: public System.Data.IDataReader GetConstraintsMetaData() { }
17:
18: public System.Data.IDataReader GetReferentialConstraintsMetaData() { }
19:
20: #endregion
21: }
And than I ran into another problem. As you can see I use IDataReader instead of DataTable to operate with DB data because a solution using IDataReader with IDbCommand is much faster than a solution that uses DataTable and IDataAdapter. So, IDataReader was the problem because it is impossible to create an instance of it and this way my test implementation of IDataRetriever is unable to implement data readers that contain dummy information for the test. In this case I’ve decided to implement a stub implementaion of IDataReader that will allow me to create and fill in a data reader with required data for the test. Below is how DummyReader looks like.
1: public class DummyReader : IDataReader
2: {
3:
4: private SortedDictionary<int, string> columns;
5: private List<Dictionary<string, object>> rows = new List<Dictionary<string,object>>();
6:
7: private int currentRowIndex = 0;
8: private Dictionary<string, object> currentRow;
9:
10: public void AddColumns(params string[] columnNames)
11: {
12: if (columnNames == null)
13: throw new ArgumentNullException("columnNames");
14:
15: columns = new SortedDictionary<int, string>();
16: for (int i = 0; i < columnNames.Length; i++)
17: {
18: columns.Add(i, columnNames[i]);
19: }
20: }
21:
22: public void AddRow(params object[] values)
23: {
24: if (values == null)
25: throw new ArgumentNullException("values");
26:
27: if (columns.Count != values.Length)
28: throw new ArgumentOutOfRangeException("values");
29:
30: Dictionary<string, object> row = new Dictionary<string, object>(columns.Count);
31:
32: foreach (var item in columns)
33: {
34: row.Add(item.Value, values[item.Key]);
35: }
36: rows.Add(row);
37: }
38:
39: #region IDataReader Members
40:
41: public void Close()
42: {
43:
44: }
45:
46: public int Depth
47: {
48: get { return 0; }
49: }
50:
51: public DataTable GetSchemaTable()
52: {
53: return new DataTable();
54: }
55:
56: public bool IsClosed
57: {
58: get { return false; }
59: }
60:
61: public bool NextResult()
62: {
63: return Read();
64: }
65:
66: public bool Read()
67: {
68: if (currentRowIndex >= rows.Count)
69: {
70: return false;
71: }
72: currentRow = rows[currentRowIndex];
73: currentRowIndex++;
74: return true;
75: }
76:
77: public int RecordsAffected
78: {
79: get { return 0; }
80: }
81:
82: #endregion
83:
84: #region IDisposable Members
85:
86: public void Dispose()
87: {
88:
89: }
90:
91: #endregion
92:
93: #region IDataRecord Members
94:
95: public int FieldCount
96: {
97: get { return columns.Count; }
98: }
99:
100: public bool GetBoolean(int i)
101: {
102: return (bool) currentRow.Values.ElementAt(i);
103: }
104:
105: public byte GetByte(int i)
106: {
107: return (byte)currentRow.Values.ElementAt(i);
108: }
109:
110: public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
111: {
112: throw new NotImplementedException();
113: }
114:
115: public char GetChar(int i)
116: {
117: return (char)currentRow.Values.ElementAt(i); ;
118: }
119:
120: public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
121: {
122: throw new NotImplementedException();
123: }
124:
125: public IDataReader GetData(int i)
126: {
127: throw new NotImplementedException();
128: }
129:
130: public string GetDataTypeName(int i)
131: {
132: throw new NotImplementedException();
133: }
134:
135: public DateTime GetDateTime(int i)
136: {
137: return (DateTime)currentRow.Values.ElementAt(i); ;
138: }
139:
140: public decimal GetDecimal(int i)
141: {
142: return (decimal)currentRow.Values.ElementAt(i); ;
143: }
144:
145: public double GetDouble(int i)
146: {
147: return (double)currentRow.Values.ElementAt(i);
148: }
149:
150: public Type GetFieldType(int i)
151: {
152: return currentRow.Values.ElementAt(i).GetType();
153: }
154:
155: public float GetFloat(int i)
156: {
157: return (float)currentRow.Values.ElementAt(i);
158: }
159:
160: public Guid GetGuid(int i)
161: {
162: return (Guid)currentRow.Values.ElementAt(i);
163: }
164:
165: public short GetInt16(int i)
166: {
167: return (short)currentRow.Values.ElementAt(i);
168: }
169:
170: public int GetInt32(int i)
171: {
172: return (int)currentRow.Values.ElementAt(i);
173: }
174:
175: public long GetInt64(int i)
176: {
177: return (long)currentRow.Values.ElementAt(i);
178: }
179:
180: public string GetName(int i)
181: {
182: return (string)currentRow.Values.ElementAt(i);
183: }
184:
185: public int GetOrdinal(string name)
186: {
187: var item = columns.First(x => x.Value == name);
188: return item.Key;
189: }
190:
191: public string GetString(int i)
192: {
193: return (string)currentRow.Values.ElementAt(i);
194: }
195:
196: public object GetValue(int i)
197: {
198: return currentRow.Values.ElementAt(i);
199: }
200:
201: public int GetValues(object[] values)
202: {
203: throw new NotImplementedException();
204: }
205:
206: public bool IsDBNull(int i)
207: {
208: return currentRow.Values.ElementAt(i) == null;
209: }
210:
211: public object this[string name]
212: {
213: get { return currentRow[name]; }
214: }
215:
216: public object this[int i]
217: {
218: get { return currentRow.Values.ElementAt(i); ; }
219: }
220:
221: #endregion
222: }
Finally, I was able to continue with test implementation. I implemented IDataRetriever interface methods in the test and implement the test. Below is the final test before implementation of IDbSchemaBuilder. This test state is quite important because now I’ll be able to compile and run the test in green (without errors) and this way to verify that it works correctly with test data. Why it is important? Because in the next step when IDbSchemaBuilder will be implemented I’ll be able only to replace mock object of IDbSchemaBuilder with its real implementation and running the test I’ll validate the correctness of the implementation.
1: [TestFixture]
2: public class DataAdapterTest : IDataRetriever
3: {
4: private DbSchema CreateExpectedDbSchema()
5: {
6: // code omitted
7:
8: return schema;
9: }
10:
11: [Test]
12: public void TestLoadDbSchema()
13: {
14: MockRepository mocks = new MockRepository();
15: IDbSchemaBuilder retriever = mocks.StrictMock<IDbSchemaBuilder>();
16: Expect.Call(retriever.LoadDbSchema(this)).Return(CreateExpectedDbSchema());
17: mocks.ReplayAll();
18: DbSchema schema = retriever.LoadDbSchema(this);
19:
20:
21: DbTable employee = schema.Tables.Find(t => t.Name == "employee");
22: Assert.NotNull(employee);
23: DbColumn employeeId = employee.Columns.Find(c => c.Name == "id");
24: Assert.NotNull(employeeId);
25: Assert.AreEqual(0, employeeId.DateTimePrecision);
26: Assert.AreEqual("integer", employeeId.DbType);
27: Assert.AreEqual(null, employeeId.DefaultValue);
28: Assert.AreEqual(false, employeeId.IsForeignKey);
29: Assert.AreEqual(false, employeeId.IsNullable);
30: Assert.AreEqual(true, employeeId.IsPrimaryKey);
31: Assert.AreEqual(0, employeeId.MaxCharacterLength);
32: Assert.AreEqual(0, employeeId.NumericPrecision);
33: Assert.AreEqual(0, employeeId.NumericScale);
34: Assert.AreEqual(0, employeeId.OrdinalPosition);
35: Assert.AreEqual(employee.Name, employeeId.Table.Name);
36:
37: // other assertions omitted
38:
39: mocks.VerifyAll();
40:
41: }
42:
43: #region IDataRetriever Members
44:
45: public string ConnectionString
46: {
47: get { return connectionString; }
48: }
49:
50: public void Configure(string connectionString) { }
51:
52: public System.Data.IDataReader GetColumnsMetaData()
53: {
54: DummyReader reader = new DummyReader();
55: reader.AddColumns("table_name", "column_name", "data_type", "ordinal_position", "column_default",
56: "is_nullable", "character_maximum_length", "numeric_precision", "numeric_scale", "datetime_precision");
57: reader.AddRow("employee", "id", "integer", "0", null, "NO", 0, 0, 0, 0);
58: reader.AddRow("employee", "name", "nvarchar", "0", "", "NO", 0, 0, 0, 0);
59: reader.AddRow("employee", "department_id", "int", "0", null, "YES", 0, 0, 0, 0);
60: reader.AddRow("employee", "description", "nvarchar", "0", "", "YES", 0, 0, 0, 0);
61:
62: reader.AddRow("department", "id", "integer", "0", null, "NO", 0, 0,0, 0);
63: reader.AddRow("department", "name", "nvarchar", "0", "", "NO", 0, 0, 0, 0);
64: reader.AddRow("department", "description", "nvarchar", "0", "", "YES", 0, 0, 0, 0);
65: return reader;
66: }
67:
68: public System.Data.IDataReader GetConstraintsMetaData()
69: {
70: DummyReader reader = new DummyReader();
71: reader.AddColumns("constraint_name", "table_name", "constraint_type", "column_name");
72: reader.AddRow("employee_id_department_id_fk", "employee", "FOREIGN KEY", "department_id");
73: reader.AddRow("employee_id_pk", "employee", "PRIMARY KEY", "id");
74: reader.AddRow("department_id_pk", "department", "PRIMARY KEY", "id");
75: return reader;
76: }
77:
78: public System.Data.IDataReader GetReferentialConstraintsMetaData()
79: {
80: DummyReader reader = new DummyReader();
81: reader.AddColumns("constraint_name", "unique_constraint_name", "match_option", "update_rule",
82: "delete_rule", "description", "constraint_table_name", "unique_constraint_table_name");
83: reader.AddRow("department_id_pk", "employee_id_department_id_fk", "", "none",
84: "none", "", "department", "employee");
85: return reader;
86: }
87:
88: #endregion
89:
90: }
Now, when I’ll have the real implementation of IDbSchemaBuilder I’m able to verify it just replacing few lines of code in the test method as below. Then I can remove the method that has generated expected result and the test is finished.
1: [Test]
2: public void TestLoadDbSchema()
3: {
4: IDbSchemaBuilder retriever = new DbSchemaBuilder();
5: DbSchema schema = retriever.LoadDbSchema(this);
6:
7: // Assertions will take place here
8: }
Conclusion. In this article I’ve tried to share with you my experience of using Unit Testing approach and its principles in order to implement Data Access of SQLCEPad in a new way. Having to think in a different way in order to produce a code that can be tested and writing a test before the real code I was able to produce much more de-coupled code that is well structured. In addition, I have now a test that I can use in any time when I change the code in order to verify my changes. It makes my implementation much more stable for changes and easy for maintenance.
Leave a Reply