21
trabis
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/14 13:43

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Perhaps you could make a wrapper for the JSON. Instead of using this:
$sql 

    query : [ 
        { 
            table: "table" , 
            column: "*", 
                        order: "T_id" 
        } 
    ]  

'

$result $testDB->select($sql);

We could use this
$result $testDB->select()->from('table')->column(*)->order('T_id');


for multiple queries we could have another wrapper
$xoopsSql = new XoopsSql();
$sql[] = $xoopsSql->from('table')->where('id: 2');
$sql[] = $xoopsSql->from('table2')->where('id: 2');
$sql[] = $xoopsSql->from('table3')->where('id: 2');

$result $testDB->delete($sql);


An example of criteria:
$criteria1 = new CriteriaCompo(); 
$criteria1->add(new Criteria('field1'3'=')); 
$criteria1->add(new Criteria('field2'3'='), 'AND'); 

$citeria2 = new CriteriaCompo();
$criteria2->add(new Criteria('field1'1'=')); 
$criteria2->add(new Criteria('field2'1'='), 'AND'); 

$criteria3 = new CriteriaCompo();
$criteria3->addCriteria($criteria1);
$criteria3->addCriteria($criteria2'OR');
$where $criteria3->renderWhere();
//$where should look something like this:
//((field1 = 3 AND field2 = 3) OR (field1 = 1 AND field2 = 1))


Criteria is very useful for WHERE clauses!

Now an example of usage for the criteria above :
$result $testDB->select()->from('table')->column(*)->where($criteria3)->order('T_id')->sort('DESC');

22
Tarik
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/14 14:28

  • Tarik

  • Not too shy to talk

  • Posts: 170

  • Since: 2010/2/3 1


Quote:

for multiple queries we could have another wrapper
$xoopsSql = new XoopsSql();
$sql[] = $xoopsSql->from('table')->where('id: 2');
$sql[] = $xoopsSql->from('table2')->where('id: 2');
$sql[] = $xoopsSql->from('table3')->where('id: 2');

$result $testDB->delete($sql);



this concept is very nice

23
zyspec
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/14 18:48

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


+1 Trabis!

I'd also like the core team to investigate the '10%' that can't use the current API to add a couple of methods to support module install/update. Things like adding/dropping tables, updating columns (ie changing type, size, etc). While I'm at it I'll request a method to handle SELECT 'summaries' (SUM, MIN, MAX, AVG, etc) in a more straight forward method. Currently this has to be done using setGroupby, which to me seems non-intuitive.

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

Thank you for the information Trabis.

That is a much more elegant solution. And probably way faster and much easier.
But what you are showing me is beyond my current experience but I would love to see what the class looks like for that...

I read this on my phone as was thinking through it in class and now re-reading it I am confused. I was not aware you could call something like that.

$sql[] = $xoopsSql->from('table')->where('id: 2');


This is syntax I have not seen before.

The $sql[] is obviously adding a record onto an array.
Are from and where both methods of $xoopsSql and you are chaining them together?

For comparison sake lets say I have these classes:
class selectClass
{
      public 
$table;
      public 
$columns;
      public function 
setTable($t)
      {
          
$this->table $t;
      }
      public function 
setColumn($c)
      {
           
$this->columns $c;
      } 
      public function 
select()
      {
         
//do SQL and return result set in associative array 
      
}
}

class 
dbsql
{
      public 
$sel = new selectClass();
      private 
$sql null;

      public function 
exec()
      {
          
$sql "SELECT " $this->sel->columns " FROM " $this->sel->table;
          
$result $this->sel->select();
      }
      
     
}


Then to use this very basic class how I am used to:

$db = new dbsql();
$db->sel->setTable('table');
$db->sel->setColumn('*');
$result $db->exec();


I am assuming the above code I quoted from you is chaining so I am assuming then that the following code would be the equivalent of the code just posted?

$db = new dbsql();
$result $db->setTable('table')->setColumn('*')->exec();


I am assuming that if indeed this is chaining that you can only chain methods and they have to be from the same class?

MUCH nicer and learn something new.
(Think .NET VBASIC chains commands like that if I remember right but seems like you could only do it with methods. You couldn't do a simple assignment of a value to a variable in the chain. Assuming the same here.)

If I am wrong about this being chaining could you please give a basic class and show me how that is used?

Thanks!

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

Quote:

zyspec wrote:
+1 Trabis!

I'd also like the core team to investigate the '10%' that can't use the current API to add a couple of methods to support module install/update. Things like adding/dropping tables, updating columns (ie changing type, size, etc). While I'm at it I'll request a method to handle SELECT 'summaries' (SUM, MIN, MAX, AVG, etc) in a more straight forward method. Currently this has to be done using setGroupby, which to me seems non-intuitive.


zyspec, since I am looking into developing a new class for this it should be simple to add this sort of thing. Can you give very simple very short examples of what you have to do now and what you would like to do?

I can NOT promise it will end up in the final code but if it is there and the core programmers like it I can't see it not making it. It does make sense and should be very little coding beyond what is already being done.

I can see these as being additional fields in the above mentioned "selectRecord" class. (For the select stuff anyhow)

I can also look into providing other commands as well but it looks like you want more "maintenance" items to update the structure of the database. It does make sense since we are basically building a wrapper to allow use of the same command to a series of databases.


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

@trabis

I looked up chaining and I think I have it down..

So yes, your idea would be much simpler. So away I go...

Rodney

27
irmtfan
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/1/15 7:50

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Yes some functions needed for select, insert, update, dump , ...

eg: i see some functions in publisher module that need to be revised to make it like Trabis wrote.
public function _selectQuery($criteria null)
    {
        
$sql sprintf('SELECT * FROM %s'$this->_db->prefix($this->_dbtable));
        if (isset(
$criteria) && is_subclass_of($criteria'criteriaelement')) {
            
$sql .= ' ' $criteria->renderWhere();
            if (
$criteria->getSort() != '') {
                
$sql .= ' ORDER BY ' $criteria->getSort() . '
                    ' 
$criteria->getOrder();
            }
        }
        return 
$sql;
    }


it means select, from, where should be separated.

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

@trabis
Where can I find the code for criteria and other database API type stuff?

I am trying to work your idea out but it is new to me so I am sorting out the correct method to do so. As it looks now I believe I need to build a new class.

If I put everything into this new class then you can call a single sql query with:

$sql = new XoopSQL();
   
$result $sql->from('table')->column(*)->order('T_id')->select();

This will set the table to "table" the columns to "*", order to "T_ID.

If called in this manner I will clear the contents of the variables after the select is over.

So then this would work fine:
$sql = new XoopSQL();
   
$result $sql->from('table')->column('*')->order('T_id')->select();
   
$result $sql->from('table')->where('id: 2')->delete();


I can also look for a boolean value in the command to NOT clear the variables so then you could call this last query in this fashon. (Lets add one more deletion)

$sql = new XoopSQL();
   
$result $sql->from('table')->column('*')->order('T_id')->select(false);
   
$result $sql->where('id: 2')->delete(false);
   
$result $sql->where('id: 3')->delete();


If I were to try your suggestion for multiple queries I could then have a couple commands that could stack queries as well as perform transactions easily:

stack queries:
$sql = new XoopSQL();
   
$sqlStack[] = $sql->from('table')->column('*')->order('T_id')->cmd('select'false);
   
$sqlStack[] = $sql->where('id: 2')->cmd('delete'false);
   
$sqlStack[] = $sql->where('id: 3')->cmd('delete');
   
$result $testDB->doStack($sqlStack);


transaction
$sql = new XoopSQL();
   
$sqlStack[] = $sql->from('table')->column('*')->order('T_id')->cmd('select'false);
   
$sqlStack[] = $sql->where('id: 2')->cmd('delete'false);
   
$sqlStack[] = $sql->where('id: 3')->cmd('delete');
   
$result $testDB->doTransaction($sqlStack);


The SQL class would be included as part of the database structure and I would extend the SQL class in the database specific file. So the sql class file would perform most of what the database class file does now.

This is the structure I am working on right now.
Note that calling a single query will execute the command and return a response.

Select statements will actually not be allowed in these stack or transaction commands. If you want to use a select in a transaction you will have to do each step like this:

$sql = new XoopSQL();
   
$result $sql->beginTransaction();
   
$result $sql->from('table')->column('*')->order('T_id')->select(false);
   
$result $sql->where('id: 2')->delete(false);
   
$result $sql->where('id: 3')->delete();
   
$result $sql->commit();


But I will be using prepared statements in every case.
I am still tinkering with it but this is the structure that appears to work and I will be coding it up over the next couple days and try to have it ready for testing with 2.5.5 within the next 7 days.

(I have to figure out the preload stuff before I can get it working with 2.6.0. I still am not sure what the heck a preload is...)

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

Ok I am in the process of trying to code this and I have a slight glitch I am hoping someone can help out with..

I am trying out a "delete" command and I currently have it running fine with this format:

$sql = new sql();
$testDB->delete($sql->setTable('TableTest')->setWhere('WhereTest'));

This works well enough I can use it for pretty much every command.

I am trying to duplicate the above mentioned code example to support multiple commands:
$xoopsSql = new XoopsSql();
$sql[] = $xoopsSql->from('table')->where('id: 2');
$sql[] = $xoopsSql->from('table2')->where('id: 2');
$sql[] = $xoopsSql->from('table3')->where('id: 2');
$result $testDB->delete($sql);


But in my case I am using:
$sql = new sql();
    
$sqlList[] = $sql->setTable('TableTest1')->setWhere('WhereTest1');
    
$sqlList[] = $sql->setTable('TableTest2')->setWhere('WhereTest2');
    
$sqlList[] = $sql->setTable('TableTest3')->setWhere('WhereTest3');
    
$testDB->delete($sqlList);


Problem is when I use print_r to view the contents of $sqlList I have:

Array
(
    [
0] => sql Object
        
(
            [
table:sql:private] => TableTest3
            
[where:sql:private] => WhereTest3
        
)

    [
1] => sql Object
        
(
            [
table:sql:private] => TableTest3
            
[where:sql:private] => WhereTest3
        
)

    [
2] => sql Object
        
(
            [
table:sql:private] => TableTest3
            
[where:sql:private] => WhereTest3
        
)

)


So it looks like the Array is storing the reference to the object instead of a copy of the object. So how can I do this and make it work?

I posted a message on stack overflow hoping to get a quick answer but I am hoping someone out here can help me quicker.

30
trabis
Re: Making a PDO "plugin" for 2.5.5 to potentially include in 2.6
  • 2013/2/5 23:51

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


Quote:

redheadedrod wrote:
So it looks like the Array is storing the reference to the object instead of a copy of the object. So how can I do this and make it work?

I posted a message on stack overflow hoping to get a quick answer but I am hoping someone out here can help me quicker.


For chaining, instead of using:
return $this;


Use:
return clone $this;



Login

Who's Online

201 user(s) are online (144 user(s) are browsing Support Forums)


Members: 0


Guests: 201


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