Three table conditional left join

doing a three table conditional left join in mysql using a subquery

"select * from newsletters
left join
(preferences,
(select * from users where users.id=#{id}) as user)
on (preferences.newsletter_id=newsletters.id
and preferences.user_id=user.id)"


mysql with create_at and modified_at timestamps

Creating a table with two timestamps, one default and one on update, creates an error in mysql.
using default 0, solves this problem, but you need to insert a 0 to get the desired result of a created at timestamp.


create table example(
id int not null auto_increment,
code varchar(250),
created_at TIMESTAMP default 0,
modified_at TIMESTAMP on update current_timestamp,
primary key(id)
) engine innodb;


Rails has some timestamping conventions/methods of its own, but i think this kind of intelligence should reside within the database, not in a web framework.

Gentoo sucks

So im trying to get kissxd installed to be able to connect my pc to my buggy divx player.

It won't install because when i try do do a "emerge -pv --newuse world" it says it needs a newer version of app-text/poppler-bindings, which in turn has been masked....this should be easier, damnit!!

SDO in mvc

I said/thought/learned that SDO could, should fit easily into the mvc pattern, but that's not entirely true.
I think that if you want to keep your Module object clean and agnostic to the das type, you would need the mediator pattern to get the SDO/DAs object.

Still not sure how to save/store it though, while being agnostic to the das.
But that's my own lack of knowledge.

Which is why i'm writing this down int the first place.
Perhaps server, or i, wil come up with ideas for agnostic storing a sdo in a module later on this road.

SDO change summary

In J2EE description of the DTO, they propose to log the changes within a DTO (ctrl +f : change flag ), so you can apply these changes when you get the DTO back to it's source.

PHP implementation seems to do this, for which i'm grateful. In my previous job i got the maintenance of a "ReleaseManager" piece of crap code. There the previous owner of the code actually tried to sync configuration files and managed to really fudge it by comparing 2 different configuration files and syncing the production file with the new configuration file by comparing it based on the line number, white lines etc.

The first thing i did was try to convince him to use xml for this, but he didn't dare to change the configuration on the production machine. So the next best thing was to tell the parser about the changes, instead of trying to guess it based on white space and line numbers.

With this in mind i ended up creating a config parser, template engine for the configuration files.
Not my best code, but good enough to not create erroneous configuration files, which was one of the basic specifications i had (to come up with myself) for this project.

Anyway the moral of this story: don't try to be smart and do not guess when your updating some important data. Let the user, not the program, decide what needs to be removed, inserted etc.

If there is not a design pattern for this, then there should be one. Perhaps i can even describe it myself .... :-)
Actually i could...Template Based Changes...
It would create an object template, read in the data from the data source, remove the old data and apply the new data back to the source.
Ofcourse design patterns are about the creation of objects

SDO not so disconnected

SDO for PHP is sold as a disconnected data graph. This would, imho, imply that switching between different sources for a SDO object should be peanuts. But it's not.

It is in fact just an extra layer/abstraction between a model and it's data source. Which gives it this advantage: a singe interface the model can use on the SDO. SDO is based on the Data Transfer Object design pattern. This pattern, also known as value object pattern(nl), is about transferring data, in an object, within an application.

I think the SDO technology can be enhanced by adding syncing/merging capabilities between two SDO's.
Something like an merge_data and/or sync SDO functionality.

To be honest, for a really disconnected data graph, you would use xml or other string representations of a data object. This is done with the SCA part of this technology with SOA/XML.

Another thing that is bothering me is that for a relational DAS, i need to inform the SDO about it's data model via associative arrays. Most databases have meta data capabilities, but PDO is lacking here.
Perhaps it is possible to "extend" schema (with our own namespace") to describe the model?
This, in combination with the active record pattern should provide us with a way of automating the creation of the models and schema's.

The good news is that converting dbase query output to xml is incredibly easy, i still need 2 das, but the xml version can be an empty document. All you would need to do is fill that document with the sdo content of it's root object and voila, 1 command later (save) and we have a xml string.

Funny news is that the code behind the relational DAS is PHP.

tags:
sdo

converting datagraphs

converting a data graph from a dbase to an xml file should be as easy as

1. opening an sdo-sql-das
2. opening an new sdo-xml-das
3. merge one into the other das
4. apply changes (or write to file)
   

This can only be done if there is a xsd schema file that corresponds with the relative das.
merging is the tricky part, when to crud ...., which sdo has precedence etc...
tags:
limala sdo dope