[Kansas-general] virtual tables

[Kirk Haines]

On Fri, 4 Jun 2004 07:52:59 -0600, Berger, Daniel wrote

> This is the part where I admit that I thought that ORM's were nothing
> more than glorified SQL wrappers and I'm not sure I totally 
> understand the need to setup table relationships or keys manually. 
>  I guess I need to read more.

Generally one doesn't need to setup keys manually, at least with Kansas, 
because we can make the database driver tell us what the primary key is on 
the table.

Relationships, though, have to be done manually because there just isn't any 
way for the code to figure that out.

i.e. if we have jukeboxes, albums, and tracks, there is no way for the code 
to just know that jukeboxes have albums and albums have tracks, or that 
tracks belong to albums and albums belong to jukeboxes.  So, one has to 
manually tell the code how things relate.

However, once that is done, we can, for example, query an album record from 
the database, and then with what appears to be a simple method call, get the 
data for the jukebox the album is in, and with another method call get all 
of the albums in the jukebox.

toby_keith_album = ksdbh.select(:Albums) {|a| a.title == 'Unleashed'}
jukebox = toby_keith_album.jukebox
other_albums = jukebox.albums

That's a lot easier than writing all of the SQL to do all of that.

For the virtual tables, though, it's harder, because the idea is to 
synthesize something that doesn't really exist.  The trick is doing that, 
while keeping the easy relationship management and while still knowing what 
pieces of data came from which tables.

Say I have two tables:

teachers
  id int primary key,
  name varchar(80),
  room char(3)

students
  id int primary key,
  name varchar(80)
  room char(3)

I want to construct a virtual table, everyone_in_school, that has teachers 
and students.

If I pretend that everyone is going into a new table with that same 
structure, there will be problems when one tries to insert the student with 
the id of 7 after the teacher with that id has already been inserted.

Perhaps the solution is that we don't worry about it, and leave it up to the 
caller to understand the data?

So if we had the following data:

teachers
  1:Elizabeth Davis:101
  2:Roger Clark:102

students
  1:Jennifer Hodgell:102
  2:Trevor Corr:101
  3:Molly Wilmschen:101
  4:Erich Delcamp:102

then when we iterated over the records returned from selecting everything 
from "everyone_in_school", we'd get:

1,Elizabeth Davis,101
2,Roger Clark,102
1,Jennifer Hodgell,102
2,Trevor Corr,101
3,Molly Wilmschen,101
4,Erich Delcamp,102

I think that might work, and might be what you were thinking of?


Kirk