SQL/ORM help

[SinNisTeR]

I'm trying to write an object relational mapping tool in Scheme, but I'm having difficulty. Can anyone provide some good documentation for the basics of how the mapping is done? Basically, how is the query string broken down and mapped to the different properties of the related objects?

 

[Markbnj]

You mean mechanically, as in how are the properties enumerated and translated into columns in a SQL statement? Or are you thinking semantically, i.e. what gets translated and how does it map to one or more tables?

The mechanics really depend entirely on the language and what sort of information is available at runtime. In a native compiled language like C or C++ all information about the compile-time names of variables is lost post-compile, or in some cases post-link. Languages like these require some kind of preprocessor-based solution, typically, or they rely on external files in XML or some other format to provide the mapping.

In languages like Java or C# the runtime retains metadata about the types in the program, and this metadata could theoretically be used to enumerate the properties of a class and automatically map them into columns in a table. However, I think it would be tough to do this and always make intelligent choices. For example, which of the properties of a class identifies each instance uniquely, if any? Which represents columns that need indexes? What about variables that are collections of other classes? Do these get mapped into relations automatically? Or are they denormalized into the containing object? How do you tell what the cardinality of a relationship is?

You could have a hybrid system where there is some sensible default translation that can be overridden by an external file. But I don't know how valuable it would be. I personally don't like ORMs, and I don't use them unless I'm forced to. I think databases and runtime metaphors for organizing program code are different worlds with some abstract entities and concepts in common, and as I've said here before, databases will usually outlast applications by years or decades, so they should be designed to be useful as databases, not simply places to stuff instances. Another way of saying it: the database and the runtime classes are both concrete expressions of the abstract solution, with a layer to translate between them.

 

[SinNisTeR]

Thanks for the insight
I agree with what you said about the role of the database. This is just a small fun project I wanted to learn how to do more functionally. This is by no means going to be used by anyone other than me. I think I need more information about both the mechanics and semantics as that would give me the overall knowledge I'd need.

Here is some more implementation information about where I'm currently at. First, I plan on using an XML file to store my mapping information. From there, I'll instantiate objects and store them in some kind of definitions file. In my configurations file I'll have a reference to my objects and XML file. Then I can use standard SQL queries (I'm using SQLite for now) to get the information I need from the database. I now need to know how I can take my XML mapping file and map the returned items from the query to the objects that the user has defined.

Let me give an example.. Say I have two tables, drivers and cars.

drivers
id - int
name - string

cars
id - int
make - string
model - string
driver_id - int

If I do "SELECT * FROM drivers, cars WHERE drivers.id = driver_id

then I get back something that looks like:
id string id make model driver_id

Now the id's I get back are ambiguous. Should I be re-writing the query to something more useful so I can distinguish?

Like: "SELECT drivers.id as driver-id, drivers.name as driver-name, .... etc ..."

When I get the result back, I now know the columns that relate to my objects in some way.

Also, since the query contains "FROM drivers, cars" am I safe to surmise that I'll be using instantiations of both of my objects (drivers, cars)?

These are questions I have about object relational mapping and how this is done. I don't need anything in depth (coalescing, cardinality, etc. this is a very simple project that I'm working on). I appreciate your input about the differences between the mechanics and semantics.

 

[Markbnj]

The really interesting question is when do you know you should do a join? This is exactly where ORMs have to start making assumptions, and they usually default to assuming the most granular role for the objects.

In your example the containing object is car, and it has the ID of a driver. So using a lazy-evaluation approach, the car object might look something like this (in C#):

class Car
{
  private int _driverID = 0;
  private int _id = 0;

  public Driver TheDriver
  {
    get { if ( _driverId == 0 ) throw something; else return dbGetDriver(_driverId);
  }

  public Car( int carId )
  {
    Car tmp = dbGetCar( carId );
    this._id = tmp._id;
    this._driverId = tmp._driverId;
  }
}

Kludgy, but you see the point: you, the programmer, are the only one that knows Cars and Drivers are always retrieved together, and thus can be accessed in the same query using a join. An ORM typically doesn't know unless you tell it, so it will default to creating accessor methods that hit the database for each object. It's more flexible, since you may get a list of drivers from a LicenseSuspended class, and not need Cars for them, or you may get a list of Cars from a DealerLot class and not need Drivers for them. In the above example if you never hit the TheDriver property then you never hit the database to get a driver. But if you always access that property then you'll always hit the database for two round trips when one would do. Tuning the database for context-specific efficiency is not the strong point of an ORM.

 

[JACKDRUID]

Originally posted by: SinNisTeR
Thanks for the insight
I agree with what you said about the role of the database. This is just a small fun project I wanted to learn how to do more functionally. This is by no means going to be used by anyone other than me. I think I need more information about both the mechanics and semantics as that would give me the overall knowledge I'd need.

Here is some more implementation information about where I'm currently at. First, I plan on using an XML file to store my mapping information. From there, I'll instantiate objects and store them in some kind of definitions file. In my configurations file I'll have a reference to my objects and XML file. Then I can use standard SQL queries (I'm using SQLite for now) to get the information I need from the database. I now need to know how I can take my XML mapping file and map the returned items from the query to the objects that the user has defined.

Let me give an example.. Say I have two tables, drivers and cars.

drivers
id - int
name - string

cars
id - int
make - string
model - string
driver_id - int

If I do "SELECT * FROM drivers, cars WHERE drivers.id = driver_id

then I get back something that looks like:
id string id make model driver_id

Now the id's I get back are ambiguous. Should I be re-writing the query to something more useful so I can distinguish?

Like: "SELECT drivers.id as driver-id, drivers.name as driver-name, .... etc ..."

When I get the result back, I now know the columns that relate to my objects in some way.

Also, since the query contains "FROM drivers, cars" am I safe to surmise that I'll be using instantiations of both of my objects (drivers, cars)?

These are questions I have about object relational mapping and how this is done. I don't need anything in depth (coalescing, cardinality, etc. this is a very simple project that I'm working on). I appreciate your input about the differences between the mechanics and semantics.

should be like

select * from drivers join cars on drivers.id = cars.driver_id

 

[SinNisTeR]

JACKDRUID, you're right. In fact, that's the way I normally do things.
I wrote my query that way to better illustrate which tables I needed: Also, since the query contains "FROM drivers, cars" am I safe to surmise that I'll be using instantiations of both of my objects (drivers, cars)?

 

[JACKDRUID]

Originally posted by: SinNisTeR
JACKDRUID, you're right. In fact, that's the way I normally do things.
I wrote my query that way to better illustrate which tables I needed: Also, since the query contains "FROM drivers, cars" am I safe to surmise that I'll be using instantiations of both of my objects (drivers, cars)?

not too sure what you are looking for... are you talking about enforcing referential integrity or using sql database diagram management? which enables you to delete a 'car' object to automatically delete the 'driver" assocated with it... vise versa depends on your configuration..?

 

[SinNisTeR]

I'm looking how to extract information from the SQL query and bind that information to what I will be populating in my objects.

For example, the queries:
SELECT drivers.id, name, cars.id, make, model, driver_id FROM drivers, cars WHERE driver_id = drivers.id;
and
SELECT * FROM drivers JOIN cars WHERE drivers.id = driver_id;

should produce the same result. What I need to know is how I can take the queries and parse them for the right information and generate new queries to populate my objects.

Do I do some kind of matching on select statements? Looking for the tables specified?
For example, "SELECT * FROM drivers JOIN cars WHERE drivers.id = driver_id;" contains drivers and cars, so I change the query to be something like:
"SELECT drivers.id as driver-id, ..., cars.id as car-id, ... FROM drivers JOIN cars WHERE ..."

Then when I get my results back, I know the column names and where they should match to in my objects.

I hope I'm making sense.

 

[Markbnj]

Usually the object is paired with some DB translator class that has intimate knowledge of which columns match to which properties. An ORM may generate this class, or the programmer may hand-code it.

You could use mappings in your XML to map column names (or ordinal position) to property names, and then use reflection to get references to the properties so you can call the set{} accessor.

But as I said in my previous reply: I think you have a more fundamental issue in that join. How do you know you can retrieve a driver and a car from the database in one query? They're separate objects. What if I create a car but don't need a driver?

 

[JACKDRUID]

umm... sorry still not sure.. but.. general syntax to bind it to an object or gridview

dim ds as new dataset
dim gv as new gridview

ds.fill(with data)

gv.datasource = ds.tables(0)

'driverid can be replaced with index minus the double quote
gv.datatextfield = ds.tables(0).columns("drivername").tostring
gv.datavaluefield = ds.tables(0).columns("driverID").tostring

gv.databind()