| SitePoint Sponsor |
I find that the php adapation of the .NET sqlmap was much easier to write (well, it has less features than the java counter part). The implementation is simplier to that of .NET taking advantage of the non-static typing nature of PHP. The experiemental code can be downloaded from http://xlab6.com/sqlmap/sqlmap.tar.gz . It contains unit-tests and 2 pdf documentations (1 manual and 1 tutorial).
Thr: would like to see some of you ideas as well if possible.
Cheers, Wei.
There has been an SqlMaps port in the PHP PetStore demo for quite some time now. I'm not exactly sure which version it is based on though, it looks to be a little bit older Java version (or perhaps the .NET version).
Well, my first point is that the Java/.NET versions of iBATIS doesn't fit to well into the php universe - mainly because of the basic difference between php and java/.net.Originally Posted by wei
I've made a small port now that can do select/insert/update/delete/resultMap/subMaps/parameterMaps(not exactly the same parameter maps as they got/use resultMap="" and select="" in <paramater>-tags in the resultMap to use joins and inheritance between different entities(extends="")
Right now it "works" and I'll post the code tomorrow probably, going to have a go at drawing some more prototype diagrams in Visio/DIA also, anyways, this is the current XML Schema I use: http://pastebin.se/2452 (Pasted external link, don't like what sitepoint does to the code-blocks)
It can handle polymorphism also, so it's quite neat atm.. altho very "hacky" and needs to be refactored a couple of times.
I've also been playing with the thought about drawing a spec for a php equivalent of JavaBeans to maintain a homogeneous interface when working with domain models/objects.
Edit:
Total this is result of about 1½ days work, and I found it very easy and straightforward to implement it, altho a bit hacky yet - it's getting there. I'm not going to do a direct port of iBATIS as I don't see the point porting it 100% to php as some parts aren't needed in php or isn't required in php
Off Topic:
I think we're getting more and more OT here, but on a side note - for reading XML structures(searching for an id) which is fastest - XMLReader or SimpleXML? I haven't had time to do any benchmarks realy.
XmlReader I'd guess, I think SimpleXML loads it all, just like DOM ?
Plus XmlReader does (or will do in near future) support xinclude which I'm unsure SimpleXML does, which maybe nice/useful.
If I remember correctly (Ren?) XmlReader acts much like the SAX Parser, doesn't it? It streams the document, as apposed to the DOM which loads it all...
Yeah, you "pull" the document in with requests for information.
The difference between XmlReader & SAX, is SAX is event based, automatically calling function when something "interesting" happens (start of an element etc), wheras XmlReader you have to inspect nodeType to find out what has just been read.
PHP Code:$reader = new XmlReader()
while ($reader->read())
{
switch ($reader->nodeType)
{
case XmlReader::ELEMENT: ... break;
}
}
I think it doesn't matter how you read the XML, as you want to cache the resulting data structure in production rather than loading the XML on every request. So simple xml does it fine, the simple xpath support can help to find nodes quickly BUT the document must not contain any namespace delcarations.
In my experiemental sqlmap, it was not 1-1 to port of the .net, but much of the design and ideas were taken from that. In actuallity, the php version was much easier to implement.
For the property access, i went with optional getter/setters approach such that you can define property with the existence of getter/setter methods, OR that there exists public instance variables. The same property access can be used on arrays as well.
I would like to know your reasoning for the tagMaps, does it validate the input against the database column type or php type? I think it should be the former.
The the petshop demo and the sqlmap within that was written by J. Ragsdale and I, it was a basic implementation that collects sql into an external file.
There is still a problem of finding a N+1 query solutions for N:M collections, seems that the java version has this solution but absent in .net.
Is this basically Rasmus' ORM except using XML instead of PHP?
Douglas
Hello World
Well, it is a little different I think.
Sqlmap is a data mapper in the sense:
You provide the database and the objects; SQLMap provides the mapping layer that goes between the two.“layer of mappers that moves data between objects and a database while keeping them independent of each other and
the mapper itself.” [Patterns of Enterprise Architecture, ISBN 0-321-12742-0]
A quick example:
Consider the database table
and your Person classCode:Name Type Size PER_ID Long Integer 4 PER_FIRST_NAME Text 40 PER_LAST_NAME Text 40 PER_BIRTH_DATE Date/Time 8 PER_WEIGHT_KG Double 8 PER_HEIGHT_M Double 8
Add a sqlmap.config filePHP Code:class Person
{
public $ID = -1;
public $FirstName;
public $LastName;
public $WeightInKilograms = 0.0;
public $HeightInMeters = 0.0;
private $_birthDate;
//setters and getter for BirthDate
public function getBirthDate()
{
return $this->_birthDate;
}
public function setBirthDate($value)
{
$this->_birthDate = $value;
}
}
And a mapping file to select some data from the database table.HTML Code:<?xml version="1.0" encoding="UTF-8" ?> <sqlMapConfig> <provider class="TAdodbProvider"> <datasource driver="sqlite" host="Data/test.db" /> </provider> <sqlMaps> <sqlMap resource="Data/person.xml"/> </sqlMaps> </sqlMapConfig>
Then, to retrieve a list of Person (using a convenient TMapper that constructs a SQLMap singleton.)HTML Code:<?xml version="1.0" encoding="utf-8" ?> <sqlMap> <select id="SelectAll" resultClass="Person"> SELECT per_id as ID, per_first_name as FirstName, per_last_name as LastName, per_birth_date as BirthDate, per_weight_kg as WeightInKilograms, per_height_m as HeightInMeters FROM person </select> </sqlMap>
Thats the basic idea.PHP Code:require_once('/path/to/SQLMap/TMapper.php');
$persons = TMapper::instance()->queryForList('SelectAll');
foreach($persons as $person)
print_f($person);
Another feature that I found interesting was pagedList, e.g.
PHP Code:$personList = TMapper::instance()->queryForPagedList('SelectAll');
//prints the first 10 person (from 1, ..., 10)
foreach($personList as $person)
print_f($person);
$personList->nextPage();
//prints the next 10 person (eg. from 11,..,20)
foreach($personList as $person)
print_f($person);
The "tagmaps" autocast the input to the right types, so that if you have an integer column you'll get an integer inserted. They're just very early and i debated taking them out.
My first version solved the N+1/1+N problem by adding a distinctOn="id" and groupBy="author" on say the Person + Post resultmaps. I know the Java version did it with only groupBy on the Person(id) but I found no good way fo doing that, again.. it's very very early so.
Casting to db types gets tricky with different databases, best to let the db sort it out themselves i think, but validating the input may be useful (e.g. mysql silently accepts string lengths greater than it defines and truncates), but then the mapper is sort of doing a bit too much.
I still know very little about the N+1 solution in java.
What the tagMaps do is that they check the length of the input argument and also casts it to the right php type. It doesn't try to cast to the db types because, as you said, that get's very tricky.
Small example: http://pastebin.se/2459
May be you understand it better than me, what does groupBy="id" actually do? The doc on this groupBy="xx" is very little.
http://opensource2.atlassian.com/con...elects+problem
is the groupBy="id" the key to rows that say these lists belongs to 1 object?
Wei.
Yes the groupBy="id" tells the application that it should group the statement on persons id(it should've been per_id and not id in my example, as you wanna group on the person tables per_id) and all duplicate persons are removed and thier posts is moved to the real object, kinda smart
Wei, about the dynamic sql xml generation part of iBATIS, I've come up with an alternative way which feels strange but maybe better.. just an idea, something like this:
Basicly you write php code that set the $sql variable, good/bad/stupid/younameit? I feel that the "dynamic" tags in the iBATIS implementations are very well... cumbersome to work with, and I'd say that it's easier to express it in "raw" php as it's much easier to read and much much much faster since it gotta be interpreted on every request.Code:<insert id="person.insert.new" inputClass="Person" dynamic="true"><![CDATA[ if($object instanceof Member): $sql = "insert into Persons (name, parent_id, email, join_date) values(:name, :parent, :email, :date)"; else: $sql = "insert into Persons (name, parent_id, visit_date) values(:name, :parent, :date)"; endif; ]]></insert>
Edit:
Second edit, what about this idead:
<insert id="person.insert.new" inputClass="Person" generator="SQLGenerator::personInsertNew" /> you specify a generator tag in the xml which is a static call to a class, which allows you to do realy advanced logic to create your queries.
Edit:
Third edit, last idea to allow easy selection of differnet queries depending on insertClass:so you don't have to use the generator-attribute for this, if you don't like what sitepoint does to the code and want syntax highlight, check here: http://pastebin.se/2470Code:<insert id="person.insert" inputClass="Person"> <if instanceof="Member" query="person.insert.member"/> <if instanceof="Guest" query="person.insert.guest" /> </insert> <insert id="person.insert.member" inputClass="Person"> insert into Persons (name, email, join_date, parent) values(:name, :email, :date, :parent) </insert> <insert id="person.insert.guest" inputClass="Person"> insert into Persons (name, visit_date, parent) values(:name, :date, :parent) <insert>
Last edited by thr; Mar 13, 2006 at 03:14.
Hmm, not getting why you need to those conditions at all
Presumably you have classes..
And its the job of the mapper to use the most specialised rules for an object?PHP Code:class Person { }
class Guest extends Person { }
class Member extends Person { }
Well, you do need conditions like that to be able to treat object polymorphicly, right? Or to create dynamic search statements, etc.
It doesn't work like that, the mapper has no intelligence at all, it's just damn fast and good at mapping queries to objects. And that would also mean you have to reflect every query to get parent classes, which is very slow.
So member class would use 1st rule, and any other class (person or guest) would use the latter?Code:<insert inputClass="Member"> insert into Persons (name, email, join_date, parent) values(:name, :email, :date, :parent) </insert> <insert inputClass="Person"> insert into Persons (name, visit_date, parent) values(:name, :date, :parent) </insert>
That's not the way it works, the mapper can't autosense which classes belong to which queries.
K, take you word for it, not looked a iBatis at all
It wouldnt have to reflect much, if there wasn't a insert for a subclass defined, it'd have to traverse up the parent classes.
To insert a person you do something like this: $mapper->insert('person.insert', $person); where "person.insert" is the id of the query you wanna execute.K, take you word for it, not looked a iBatis at all
id name postIdYes the groupBy="id" tells the application that it should group the statement on persons id(it should've been per_id and not id in my example, as you wanna group on the person tables per_id) and all duplicate persons are removed and thier posts is moved to the real object, kinda smart
1 Ren 1
1 Ren 2
1 Ren 3
2 Thr 4
It only creates a Person object if it hasn't seen id before. Then attaches the Post to the person with the relevant id.
SQLXML used a different method, which maybe better in some situations, as it reduced the bandwidth coming out of the server.
SELECT id, name, NULL as postId, NULL as userId FROM Persons
UNION SELECT NULL, NULL, postId, userId FROM Posts
And had a method of relating userId = id so could determine which posts belonged to whom.
I think the dynamic sql is more to do with building partial SQL, e.g.
that create a string like (username="xxx" OR username="yyy"), which can be done of course using php code, but having php code in XML will be nasty as well.HTML Code:<iterate prepend=”AND” property=”userNameList” open=”(” close=”)” conjunction=”OR”> username=#userNameList[]# </iterate>
I don't really like the dynamic SQL syntax, dynamic SQL can be useful thought. An alternative is to use the parameter values as part of the SQL, not just parameters, e.g.
where $perferredOrder$ is some SQL string defined as a property in the parameter. This of course can lead to abuse and raise the chance of SQL injection and undesirable effects.HTML Code:<statement id=”getProduct” resultMap=”get-product-result”> select * from PRODUCT order by $preferredOrder$ </statement>
For inserts, i think using different INSERT statements are ok.
Yes, mixing in php code in the xml was a bad idea which is why I decided to scrap it - for now the generator="" attribute is the best I can come up with, while it does separate the more advanced sql parts a bit you can still keep them in one file(your SQLGenerator file).
Me neither, the first thing that popped in my mind when I saw that <dynamic> tag in the docs was "wtf? what where they thinking".
Problem with this is that you get half your sql in xml and the other half in php which might be very confusing hunting down where you put that SQL in your code, etc. I prefer the Generator="" tag over that.
Yup. agreed.
Posting Permissions
Bookmarks