31
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Thanks Trabis. I think I figured out another way that might be better but haven't been home to try it out... Not sure that cloning the variable at every step would be the best step.

I have seen references to something that might work. The code looks like this:


$sqlList[] = new sql()->setTable('TableTest1')->setWhere('WhereTest1');
$sqlList[] = new sql()->setTable('TableTest2')->setWhere('WhereTest2');
$sqlList[] = new sql()->setTable('TableTest3')->setWhere('WhereTest3');
$testDB->delete($sqlList);

Will try this in a few minutes..
As long as this works I will have a way to send single or multiple queries to the commands.
I hope to have something in the next couple days to test. This was the last hurdle I had.

32
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

That did not work... But I found some code changes I needed to make which did work.. Now this works and is pretty simple... I had to add a function to the class:

// this is a static method, it doesn't 
    // run on an object, it only runs on a class
    // use it as sql::make()
    
public static function make()
    {
        
// create an instance of class 
        // sql and return it
        
return new static();
    }


So now it works!
So if I want to run a single delete query I can call it like this:
$sql = new sql();
$result=$testDB->delete($sql->setTable('TableTest')->setWhere('WhereTest'));

That way will support being able to use portions of the SQL for a different query. So I could actually do a bunch of different functions without changing the table for instance...

For one time use of a single SQL query I can call the delete like this and it works...
$result=$testDB->delete(sql::make()->setTable('TableTestD')->setWhere('WhereTestD'));

Note no $sql variable so it will destroy the object when it returns.

For a bunch of delete queries this now works:
$sqlList[] = sql::make()->setTable('TableTest1')->setWhere('WhereTest1');
    
$sqlList[] = sql::make()->setTable('TableTest2')->setWhere('WhereTest2');
    
$sqlList[] = sql::make()->setTable('TableTest3')->setWhere('WhereTest3');
        
$testDB->delete($sqlList);


I will likely combine the setter and getter into the same method. Thus instead of setTable($value) and $value=getTable I will just have table. If it contains a value it will set the value. If nothing is included or it is set to null then it will return a value. So the setter would be table($value) and the getter would be $value=table().

However in order to support both single and multiple entries I have the actual command which in this case is deleteIt which is called and run as a single entry. The delete entry then looks at the value passed to it and sees if it is an array or a single object. If it is an array it will step through it and call deleteIt as many times as needed. if it is just a single object it calls deleteIt directly. Works pretty smoothly with little coding.. ;) Now that I have this down I should have a testable PDO class for 2.5.5 this weekend. With one for 2.6 not far behind. The fields such as where and from will be passed to the query as they are at this point.

Note I am mentioning this here for comment since I am still learning with PHP. Once I get this stuff where I understand it there will be less requests and comments and more coding.. ;)


33
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Ok update... This is a little more work then I thought it would be but I am closing in. I created a lot more work for myself but then had a "eureka" moment that simplified much of my work.

I haven't gotten the connector to actually change the database yet with my proposed features but I do have them working to make valid SQL code.

The following SQL format statements are working now with the new syntax:
DELETE table WHERE yes='no'
INSERT INTO table VALUES (1,2,3)
INSERT IGNORE INTO table VALUES (1,2,3)
INSERT INTO table (column1column2column3VALUES (1,2,3)
INSERT INTO table VALUES (1,2,3), (4,5,6), (789)
REPLACE INTO table VALUES (1,2,3)
REPLACE IGNORE INTO table VALUES (1,2,3)
REPLACE INTO table (column1column2column3VALUES (1,2,3)
REPLACE INTO table VALUES (1,2,3), (4,5,6), (789)
UPDATE table SET columns1='value1'column2='value2' WHERE this='that'
UPDATE IGNORE table SET columns1='value1'column2='value2' WHERE this='that'
SELECT FROM table
SELECT column1
column2column3 FROM table WHERE id=23


I am wondering what other commands we really need?
Do we really need the drop table, create table, alter table, show table and those sorts of commands?

I CAN add them but are they really necessary?

ToDo list:
1.Finish up the column and values stuff so they take arrays. Currently doing with strings.
2.Wrestling with the WHERE clause. I think this is a good time to check out the "criteria" feature trabis mentioned above.
3. Sorting out all of the Select variations. Thinking I will learn how to do this while tackling the where clause.
4. setup the actual SQL statements and access the database. Have been just creating the SQL statement its self at this point. Should be real simple to code the actual statements.
5. Comment and error trap the code.

I will likely put what I have in the SVN after I get through with #2 and build from there. I think the hardest things I have to tackle yet are the select statements and the where clause.

34
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Scratch #2, The criteria stuff works well here... We need a tutorial on it though because it is not obvious from the code.

The correct statement mentioned by Trabis with the PDO stuff is below...

$criteria1 = new CriteriaCompo(); 
        
$criteria1->add(new Criteria('field1'3'=')); 
        
$criteria1->add(new Criteria('field2'3'='), 'AND'); 
        
        
$criteria2 = new CriteriaCompo();
        
$criteria2->add(new Criteria('field1'1'=')); 
        
$criteria2->add(new Criteria('field2'1'='), 'AND'); 
        
        
$criteria3 = new CriteriaCompo();
        
$criteria3->add($criteria1);
        
$criteria3->add($criteria2'OR');
        
        
        
$result $testDB->select(sql::make()->table('TableTestC')->columns('ColumnTestC')->where($criteria3->render()));


This produces the following line:
SELECT ColumnTestC FROM TableTestC WHERE ((field1 '3' AND field2 '3') OR (field1 '1' AND field2 '1'))

35
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Update:
The current version in the SVN includes the 2.5 compatible version of the PDO connector. I have tested this with 2.5.5 with no modules installed under MySQL and it works and seems to work fine.

The old MySQL connector returns a status when a database function is called.
PDO throws an interrupt when there is a problem.

I THINK I have covered about 90% of my code with a compatible error catch/logging system but I have not tested it yet.

As is this connector SHOULD work with 2.5.5 with current functionality. Be aware it should work with modules that use 100% xoops API but will likely crap out if a module accesses the database directly.

I have 1 week from today to finish up my semester and baring any further injuries, emergencies or major distractions I should be able to finish the connector no later than May 21st since I am no longer working a second job or going to school during the summer.

Yet todo:

For new proposed low level functionality.
1. Finish up the logic skeleton. All of the CRUD logic is in place and it creates valid SQL code as mentioned above. I have yet to complete the Transaction and Batch skeletons.
2. Add the actual SQL calls to perform the SQL code that is being created. (Will try to use prepared statements)
3. Validate that the error catching/logging system is working correctly.
4. Look into adding the admin type functions mentioned by zyspec and irmtfan.

For full connector:
1. Recheck code for obvious issues or unfinished code.
2. Test error trapping system and check that it is compatible with standard xoops error system.
3. Compare closely with current 2.6.0 MySQL connector and make sure as much has been done as needed to retain compatibility with 2.5.6 but also compliant with the code in 2.6 allowing for minimal code change to make compatible with 2.6.0.

Estimated programming time still required. 16-24 hours for current planned code before adding the admin stuff. Admin stuff likely to only add 4-8 hours due to support system already in place.

Also, I need a list of all "admin" functions we desire to add to the connector so I can add them. I will look at this more next week but feel free to list and give examples of things you want to be able to do that aren't already listed in this thread or in:
https://xoops.org/modules/newbb/viewtopic.php?post_id=353332

Or in:
https://sourceforge.net/p/xoops/feature-requests/402/

36
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

If Trabis or someone else sees this that understands a little better what Trabis meant to chain by using :

return clone $this;


instead of
return $this;


My understanding is that cloning will make a new object each step of the way which will end up leaving an unused object behind.

Not cloning should pass a reference to the main variable which I believe is what I want. Not sure what the advantage to cloning the variable is since all I see it doing is using more memory. Please let me know if I am missing something. I was not aware that you could chain with PHP before trabis mentioned it here.

37
irmtfan
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/5/1 4:21

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Hi
Honestly i dont have enough php knowledge to understand your works completely.
but what Trabis wrote is amazing.
Also zyspec knows better than me.

I just want to mention something.
there should be a rule for module developers to not mentioned their module tables more than once in the whole code.

eg: if my table is mod_MUMODULE_data it just should be mentioned in my class/data.php
So it would be hard-coded anyway if you could do this if 'TableTestC' is your module table:
$result $testDB->select(sql::make()->table('TableTestC')->columns('ColumnTestC')->where($criteria3->render()));

'TableTestC' table is hard-coded.
It is bad for module cloning.
Thats why i suggested in other topic to add facilities to XoopsObejct because then module developer can get the table from handler.

Maybe something like this
[code]
$result $testDB->select(sql::make()->table()->columns('ColumnTestC')->where($criteria3->render()));

I repeat, I review your works but my php knowledge is limited.
Therefor i just reply what i understand !!!

Quote:

redheadedrod wrote:
It looks like it should be easy to add these features but I need to know just what needs to be added. There is not much sense in adding stuff that may only be used for one or two modules but if it is something that will be used by many then it makes sense to do.


You are totally right. I dont want you to add especial connector for this.
I meant i cannot add that "ON DUPLICATE KEY UPDATE" by using current class/criteria.php so it would be totally hard-coded for me.

I think we should enhance class/criteria.php how that it could get any criteria.


38
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

Quote:

irmtfan wrote:

You are totally right. I dont want you to add especial connector for this.
I meant i cannot add that "ON DUPLICATE KEY UPDATE" by using current class/criteria.php so it would be totally hard-coded for me.

I think we should enhance class/criteria.php how that it could get any criteria.



I am learning PHP as I do this.. Have progressed much in the past two years. Especially with my classes in C# dealing with object based programming. Much makes better sense now to me... Still learning new stuff though and I picked up a couple newer "advanced" php books to read this summer.

Anyhow, I am willing to look at the Criteria stuff if there is a need to do so. It took me a little while to wrap my head around it so I am sure it will take me a few more to do so again. It looked to me as intended to work with the "where" clause for SQL and not sure where it makes sense elsewhere. I can spend a little more time on this after I get the PDO stuff done. Since I want to write a full Database tutorial using all of the current stuff it is important for me to understand it anyhow.

And I believe under the 2.6 branch everything is included in the xoops object now. There are no separate objects for users and databases etc if I remember correctly.

39
Mamba
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/5/1 10:12

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


Quote:
Anyhow, I am willing to look at the Criteria stuff if there is a need to do so. It took me a little while to wrap my head around it so I am sure it will take me a few more to do so again. It looked to me as intended to work with the "where" clause for SQL and not sure where it makes sense elsewhere.

I don't know if this would be possible in the current XOOPS architecture, but it would be nice to have Criteria being able to create other type of queries besides SELECT, like INSERT, UPDATE and DELETE.

There are few classes in PHP Classes that do that:

- qdbquery
- php-db-query
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

40
redheadedrod
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6

I will look at it when I am done with the connector.

Like I said, the current criteria configuration looked like it would work well for the where clause but I didn't see where it was useful for anything else.

I will start a new thread for that when I get to it. Since it is more or a less an extension of the database connector I will look at this before I move on to the install script but after I have the main connector coded.

Login

Who's Online

185 user(s) are online (102 user(s) are browsing Support Forums)


Members: 0


Guests: 185


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits