SitePoint Sponsor

User Tag List

Page 4 of 5 FirstFirst 12345 LastLast
Results 76 to 100 of 121
  1. #76
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  2. #77
    SitePoint Wizard dreamscape's Avatar
    Join Date
    Aug 2005
    Posts
    1,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  3. #78
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wei
    Thr: would like to see some of you ideas as well if possible.
    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.

    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

  4. #79
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #80
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #81
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  7. #82
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston
    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;
    }


  8. #83
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #84
    SitePoint Wizard DougBTX's Avatar
    Join Date
    Nov 2001
    Location
    Bath, UK
    Posts
    2,498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wei
    HTML Code:
    <select id="getAccount" resultClass="Account">
    	select
    	Account_Id as Id,
    	Account_FirstName as FirstName,
    	Account_LastName as LastName,
    	Account_Email as EmailAddress
    	from Accounts
    	where Account_Id = #value#
    </select>
    Is this basically Rasmus' ORM except using XML instead of PHP?

    Douglas
    Hello World

  10. #85
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it is a little different I think.

    Sqlmap is a data mapper in the sense:
    “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]
    You provide the database and the objects; SQLMap provides the mapping layer that goes between the two.

    A quick example:

    Consider the database table
    Code:
    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
    and your Person class
    PHP 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;
        }

    Add a sqlmap.config file
    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>
    And a mapping file to select some data from the database table.
    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>
    Then, to retrieve a list of Person (using a convenient TMapper that constructs a SQLMap singleton.)
    PHP Code:
    require_once('/path/to/SQLMap/TMapper.php');

    $persons TMapper::instance()->queryForList('SelectAll');

    foreach(
    $persons as $person)
        
    print_f($person); 
    Thats the basic idea.

    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); 

  11. #86
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wei
    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 "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.

    Quote Originally Posted by wei
    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.
    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.

  12. #87
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    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.

  13. #88
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wei
    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.
    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.

    Quote Originally Posted by wei
    I still know very little about the N+1 solution in java.
    Small example: http://pastebin.se/2459

  14. #89
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #90
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #91
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    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>
    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.

    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:
    Code:
    <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>
    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/2470
    Last edited by thr; Mar 13, 2006 at 02:14.

  17. #92
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, not getting why you need to those conditions at all

    Presumably you have classes..

    PHP Code:
    class Person { }
    class 
    Guest extends Person { }
    class 
    Member extends Person { } 
    And its the job of the mapper to use the most specialised rules for an object?

  18. #93
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you do need conditions like that to be able to treat object polymorphicly, right? Or to create dynamic search statements, etc.

    Quote Originally Posted by Ren
    And its the job of the mapper to use the most specialised rules for an object?
    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.

  19. #94
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>
    So member class would use 1st rule, and any other class (person or guest) would use the latter?

  20. #95
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren
    [...code...]So member class would use 1st rule, and any other class (person or guest) would use the latter?
    That's not the way it works, the mapper can't autosense which classes belong to which queries.

  21. #96
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    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

    Quote Originally Posted by thr
    And that would also mean you have to reflect every query to get parent classes, which is very slow.
    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.

  22. #97
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren
    K, take you word for it, not looked a iBatis at all
    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.

  23. #98
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    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
    id name postId
    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.

  24. #99
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the dynamic sql is more to do with building partial SQL, e.g.
    HTML Code:
    <iterate prepend=”AND” property=”userNameList”
                   open=”(” close=”)” conjunction=”OR”>
        username=#userNameList[]#
    </iterate>
    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.

    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.
    HTML Code:
    <statement id=”getProduct” resultMap=”get-product-result”>
    select * from PRODUCT order by $preferredOrder$
    </statement>
    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.

    For inserts, i think using different INSERT statements are ok.

  25. #100
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wei
    I think the dynamic sql is more to do with building partial SQL, e.g.
    HTML Code:
    <iterate prepend=”AND” property=”userNameList”
                   open=”(” close=”)” conjunction=”OR”>
        username=#userNameList[]#
    </iterate>
    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.
    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).

    Quote Originally Posted by wei
    I don't really like the dynamic SQL syntax, dynamic SQL can be useful thought.
    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".

    Quote Originally Posted by wei
    An alternative is to use the parameter values as part of the SQL, not just parameters, e.g.
    HTML Code:
    <statement id=”getProduct” resultMap=”get-product-result”>
    select * from PRODUCT order by $preferredOrder$
    </statement>
    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.

    Quote Originally Posted by wei
    For inserts, i think using different INSERT statements are ok.
    Yup. agreed.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •