/PetaPoco.Tests/MultiPocoTests.cs

  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using PetaTest;
  6using PetaPoco;
  7
  8namespace PetaPoco.Tests
  9{
 10	public class MultiPocoTests
 11	{
 12		[TableName("posts")]
 13		[PrimaryKey("id")]
 14		public class post
 15		{
 16			public long id { get; set; }
 17			public string title { get; set; }
 18			public long author { get; set; }
 19
 20			[ResultColumn] public author author_obj { get; set; }
 21		}
 22
 23		[TableName("authors")]
 24		[PrimaryKey("id")]
 25		public class author
 26		{
 27			public long id { get; set; }
 28			public string name { get; set; }
 29
 30			[ResultColumn]
 31			public List<post> posts { get; set; }
 32		}
 33
 34
 35
 36		public MultiPocoTests()
 37		{
 38			_connectionStringName = "mysql";
 39		}
 40
 41		string _connectionStringName;
 42		Database db;
 43
 44		[TestFixtureSetUp]
 45		public void CreateDB()
 46		{
 47			db = new Database(_connectionStringName);
 48			db.Execute(@"
 49
 50DROP TABLE IF EXISTS posts;
 51DROP TABLE IF EXISTS authors;
 52
 53CREATE TABLE posts (
 54	id				bigint AUTO_INCREMENT NOT NULL,
 55	title			varchar(127) NOT NULL,
 56	author			bigint NOT NULL,
 57	PRIMARY KEY (id)
 58) ENGINE=INNODB;
 59
 60CREATE TABLE authors (
 61	id				bigint AUTO_INCREMENT NOT NULL,
 62	name			varchar(127) NOT NULL,
 63	PRIMARY KEY (id)
 64) ENGINE=INNODB;
 65
 66			");
 67
 68
 69			var a1 = new author();
 70			a1.name = "Bill";
 71			db.Insert(a1);
 72
 73			var a2 = new author();
 74			a2.name = "Ted";
 75			db.Insert(a2);
 76
 77			var p = new post();
 78			p.title = "post1";
 79			p.author = a1.id;
 80			db.Insert(p);
 81
 82			p = new post();
 83			p.title = "post2";
 84			p.author = a1.id;
 85			db.Insert(p);
 86
 87			p = new post();
 88			p.title = "post3";
 89			p.author = a2.id;
 90			db.Insert(p);
 91
 92		}
 93
 94		[TestFixtureTearDown]
 95		public void DeleteDB()
 96		{
 97			db.Execute(@"
 98DROP TABLE IF EXISTS posts;
 99DROP TABLE IF EXISTS authors;
100			");
101		}
102
103		[Test]
104		public void Basic()
105		{
106			var posts = db.Fetch<post, author>("SELECT * FROM posts LEFT JOIN authors ON posts.author = authors.id ORDER BY posts.id");
107			Assert.AreEqual(posts.Count, 3);
108
109			Assert.AreEqual(posts[0].id, 1);
110			Assert.AreEqual(posts[0].title, "post1");
111			Assert.AreEqual(posts[0].author, 1);
112			Assert.AreEqual(posts[0].author_obj.name, "Bill");
113			Assert.AreEqual(posts[1].id, 2);
114			Assert.AreEqual(posts[1].title, "post2");
115			Assert.AreEqual(posts[1].author, 1);
116			Assert.AreEqual(posts[1].author_obj.name, "Bill");
117			Assert.AreEqual(posts[2].id, 3);
118			Assert.AreEqual(posts[2].title, "post3");
119			Assert.AreEqual(posts[2].author, 2);
120			Assert.AreEqual(posts[2].author_obj.name, "Ted");
121		}
122
123		[Test]
124		public void CustomRelator()
125		{
126			var posts = db.Fetch<post, author, post>(
127				(p,a)=>
128					{
129						p.author_obj = a;
130						return p;
131					},
132				"SELECT * FROM posts LEFT JOIN authors ON posts.author = authors.id ORDER BY posts.id");
133
134			Assert.AreEqual(posts.Count, 3);
135			Assert.AreNotSame(posts[0].author_obj, posts[1].author_obj);
136			Assert.AreEqual(posts[0].id, 1);
137			Assert.AreEqual(posts[0].title, "post1");
138			Assert.AreEqual(posts[0].author, 1);
139			Assert.AreEqual(posts[0].author_obj.name, "Bill");
140			Assert.AreEqual(posts[1].id, 2);
141			Assert.AreEqual(posts[1].title, "post2");
142			Assert.AreEqual(posts[1].author, 1);
143			Assert.AreEqual(posts[1].author_obj.name, "Bill");
144			Assert.AreEqual(posts[2].id, 3);
145			Assert.AreEqual(posts[2].title, "post3");
146			Assert.AreEqual(posts[2].author, 2);
147			Assert.AreEqual(posts[2].author_obj.name, "Ted");
148		}
149
150		// Relator callback to do many to one relationship mapping
151		class PostAuthorRelator
152		{
153			// A dictionary of known authors
154			Dictionary<long, author> authors = new Dictionary<long, author>();
155
156			public post MapIt(post p, author a)
157			{
158				// Get existing author object, or if not found store this one
159				author aExisting;
160				if (authors.TryGetValue(a.id, out aExisting))
161					a = aExisting;
162				else
163					authors.Add(a.id, a);
164
165				// Wire up objects
166				p.author_obj = a;
167				return p;
168			}
169		}
170
171		[Test]
172		public void ManyToOne()
173		{
174			// This test uses a custom relator callback to connect posts to existing author instances
175			// Note that for each row, an author object is still created - it's just that the duplicates
176			// are discarded
177
178			var posts = db.Fetch<post, author, post>(new PostAuthorRelator().MapIt,
179				"SELECT * FROM posts LEFT JOIN authors ON posts.author = authors.id ORDER BY posts.id"
180				);
181
182
183			Assert.AreEqual(posts.Count, 3);
184			Assert.AreSame(posts[0].author_obj, posts[1].author_obj);
185
186			Assert.AreEqual(posts[0].id, 1);
187			Assert.AreEqual(posts[0].title, "post1");
188			Assert.AreEqual(posts[0].author, 1);
189			Assert.AreEqual(posts[0].author_obj.name, "Bill");
190
191			Assert.AreEqual(posts[1].id, 2);
192			Assert.AreEqual(posts[1].title, "post2");
193			Assert.AreEqual(posts[1].author, 1);
194			Assert.AreEqual(posts[1].author_obj.name, "Bill");
195
196			Assert.AreEqual(posts[2].id, 3);
197			Assert.AreEqual(posts[2].title, "post3");
198			Assert.AreEqual(posts[2].author, 2);
199			Assert.AreEqual(posts[2].author_obj.name, "Ted");
200		}
201
202		class AuthorPostRelator
203		{
204
205			/*
206			 * In order to support OneToMany relationship mapping, we need to be able to 
207			 * delay returning an LHS object until we've processed its many RHS objects
208			 * 
209			 * To support this, PetaPoco allows a relator callback to return null - indicating
210			 * that the object isn't yet fully populated.  
211			 * 
212			 * In order to flush the final object, PetaPoco will call the relator function 
213			 * one final time with all parameters set to null.	It only does this if the callback
214			 * returned null at least once during the processing of the result set (this saves
215			 * simple lamba mapping functions from having to deal with nulls).
216			 * 
217			 */
218			public author current;
219			public author MapIt(author a, post p)
220			{
221				// Terminating call.  Since we can return null from this function
222				// we need to be ready for PetaPoco to callback later with null
223				// parameters
224				if (a == null)
225					return current;
226
227				// Is this the same author as the current one we're processing
228				if (current != null && current.id == a.id)
229				{
230					// Yes, just add this post to the current author's collection of posts
231					current.posts.Add(p);
232
233					// Return null to indicate we're not done with this author yet
234					return null;
235				}
236
237				// This is a different author to the current one, or this is the 
238				// first time through and we don't have an author yet
239
240				// Save the current author
241				var prev = current;
242
243				// Setup the new current author
244				current = a;
245				current.posts = new List<post>();
246				current.posts.Add(p);
247
248				// Return the now populated previous author (or null if first time through)
249				return prev;
250			}
251		}
252
253		[Test]
254		public void OneToMany()
255		{
256			// Example of OneToMany mappings
257
258			var authors = db.Fetch<author, post, author>(new AuthorPostRelator().MapIt,
259				"SELECT * FROM authors LEFT JOIN posts ON posts.author = authors.id ORDER BY posts.id"
260				);
261
262			Assert.AreEqual(authors.Count, 2);
263			Assert.AreEqual(authors[0].name, "Bill");
264			Assert.AreEqual(authors[0].posts.Count, 2);
265			Assert.AreEqual(authors[0].posts[0].title, "post1");
266			Assert.AreEqual(authors[0].posts[1].title, "post2");
267			Assert.AreEqual(authors[1].name, "Ted");
268			Assert.AreEqual(authors[1].posts.Count, 1);
269			Assert.AreEqual(authors[1].posts[0].title, "post3");
270		}
271
272		[Test]
273		public void ManyToOne_Lambda()
274		{
275			// same as ManyToOne test case above, but uses a lambda method as the callback
276			var authors = new Dictionary<long, author>();
277			var posts = db.Fetch<post, author, post>(
278				(p, a) =>
279				{
280					// Get existing author object
281					author aExisting;
282					if (authors.TryGetValue(a.id, out aExisting))
283						a = aExisting;
284					else
285						authors.Add(a.id, a);
286
287					// Wire up objects
288					p.author_obj = a;
289					return p;
290				},
291				"SELECT * FROM posts LEFT JOIN authors ON posts.author = authors.id ORDER BY posts.id"
292				);
293
294
295			Assert.AreEqual(posts.Count, 3);
296			Assert.AreSame(posts[0].author_obj, posts[1].author_obj);
297			Assert.AreEqual(posts[0].id, 1);
298			Assert.AreEqual(posts[0].title, "post1");
299			Assert.AreEqual(posts[0].author, 1);
300			Assert.AreEqual(posts[0].author_obj.name, "Bill");
301			Assert.AreEqual(posts[1].id, 2);
302			Assert.AreEqual(posts[1].title, "post2");
303			Assert.AreEqual(posts[1].author, 1);
304			Assert.AreEqual(posts[1].author_obj.name, "Bill");
305			Assert.AreEqual(posts[2].id, 3);
306			Assert.AreEqual(posts[2].title, "post3");
307			Assert.AreEqual(posts[2].author, 2);
308			Assert.AreEqual(posts[2].author_obj.name, "Ted");
309		}
310
311
312	}
313
314}