1
irmtfan
add some needed facilities for module developers in working with xoops database.
  • 2013/4/30 11:31

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Currently i see many developers have to write many codes for some tasks like "show" "change" "drop" "add" in queries on "table", "table field" and "table index" in their modules. thru enhancing userlog module i decided to add these functions to XoopsObject class. so i want to share them with you and you save your precious time:
public function changeField($field null$structure null) {
    
$sql "ALTER TABLE {$this->table} CHANGE {$field} {$field} {$structure}";
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}

public function 
showFields($field null) {
    
$sql "SHOW FIELDS FROM {$this->table}";
    if (isset(
$field)) {
        
$sql .= " LIKE '{$field}'";
    }
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    
$ret = array();
    while (
$myrow $this->db->fetchArray($result)) {
        
$ret[$myrow["Field"]] = $myrow;
    }
    return 
$ret;
}

public function 
addField($field null$structure null) {
    if (empty(
$field) || empty($structure)) {
        return 
false;
    }
    if (
$this->showFields($field)) {
        return 
false;
    } 
// field is exist
    
$sql "ALTER TABLE {$this->table} ADD {$field} {$structure}";
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}

public function 
dropField($field null) {
    if (empty(
$field)) {
        return 
false;
    }
    if (!
$this->showFields($field)) {
        return 
false;
    } 
// field is not exist
    
$sql "ALTER TABLE {$this->table} DROP {$field}";
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}

public function 
showIndex($index null) {
    
$sql "SHOW INDEX FROM {$this->table}";
    if (isset(
$index)) {
        
$sql .= " WHERE KEY_NAME = '{$index}'";
    }
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    
$ret = array();
    while (
$myrow $this->db->fetchArray($result)) {
        
$ret[] = $myrow;
    }
    return 
$ret;
}

public function 
addIndex($index null$fields = array()) {
    if (empty(
$index) || empty($fields)) {
        return 
false;
    }
    if (
$this->showIndex($index)) {
        return 
false;
    } 
// index is exist
    
$fields is_array($fields) ? implode(","$fields) : $fields;
    
$sql    "ALTER TABLE {$this->table} ADD INDEX {$index} ( {$fields} )";
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}

public function 
dropIndex($index null) {
    if (empty(
$index)) {
        return 
false;
    }
    if (!
$this->showIndex($index)) {
        return 
false;
    } 
// index is not exist
    
$sql "ALTER TABLE {$this->table} DROP INDEX {$index}";
    if (!
$result $this->db->queryF($sql)) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}

/**
 * Show if the object table or any other table is exist in database
 *
 * @access public
 * @param string $table or $db->prefix("{$table}") eg: $db->prefix("bb_forums") or "bb_forums" will return same result
 * @param bool   $found
 */
public function showTable($table null) {
    if (empty(
$table)) {
        
$table $this->table;
    } 
// the table for this object
    // check if database prefix is not added yet and then add it!!!
    
if (strpos($table$this->db->prefix "_") !== 0) {
        
$table $this->db->prefix("{$table}");
    }
    
$result $this->db->queryF("SHOW TABLES LIKE '{$table}'");
    
$found  $this->db->getRowsNum($result);
    return empty(
$found) ? false true;
}

/**
 * Rename an old table to the current object table in database
 *
 * @access public
 * @param string $oldTable or $db->prefix("{$oldTable}") eg: $db->prefix("bb_forums") or "bb_forums" will return same result
 * @param        bool
 */
public function renameTable($oldTable) {
    if (
$this->showTable() || !$this->showTable($oldTable)) {
        return 
false;
    } 
// table is current || oldTable is not exist
    // check if database prefix is not added yet and then add it!!!
    
if (strpos($oldTable$this->db->prefix "_") !== 0) {
        
$oldTable $this->db->prefix("{$oldTable}");
    }
    if (!
$result $this->db->queryF("ALTER TABLE {$oldTable} RENAME {$this->table}")) {
        
xoops_error($this->db->error() . '<br />' $sql);
        return 
false;
    }
    return 
true;
}
currently you can use them by copying them to your ModuleObject class which extend the XoopsObject so the usage is like this:
$MYMODULEHandler =& xoops_getmodulehandler('handler_name''MODULE_NAME');

$MYMODULEHandler->changeField("MY_FIELD""TEXT NOT null");

if(
$MYMODULEHandler->showTable()) print_r($MYMODULEHandler->table "is exist");

if(
$MYMODULEHandler->renameTable("MY_OLD_TABLE")) print_r(" upgrade to the new version correctly done")
but we can add them to XoopsObject or maybe to XoopsDatabaseFactory

2
redheadedrod
Re: add some needed facilities for module developers in working with xoops database.

I am getting back to the PDO connector after I finish up my classes this and next week.. Lets talk.. This would be the time to do this...

I hope to have a full new connector that will be included with Alpha 3. I should be able to complete this with 2 weeks of finishing my semester since most of the work has already been done. I will leave my email to you in a PM.

Rodney

3
zyspec
Re: add some needed facilities for module developers in working with xoops database.
  • 2013/4/30 18:55

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


+1
irmtfan - I had put in a similar request to the core team back in Sept 2012. Seehttps://sourceforge.net/p/xoops/feature-requests/402/

4
Mamba
Re: add some needed facilities for module developers in working with xoops database.
  • 2013/4/30 21:40

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


+1
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

5
redheadedrod
Re: add some needed facilities for module developers in working with xoops database.

It looks like I take my last test for this semester on May 7th and I will get back to the PDO connector. With no further additions I expect between 16-24 hours more of work to be done with this code before I feel comfortable to present it.

While I am developing this for 2.5 if you look at the code I am also trying to bring it up to the level that the database connector is in 2.6.0 and I believe if my initial look at it is correct I may only need to change one line to make it compatible with 2.6.0.

The thread discussing this connector is::

https://xoops.org/modules/newbb/viewtopic.php?post_id=351598

I will look further into adding these request into that code since my understanding is that it is intended to be refactored if necessary by a core developer and added for Alpha 3 assuming I get it done in time. (I hope to be done with this within a week after my classes are complete baring any more emergencies, injuries or distractions. Maybe two while adding these requests.)

Since the support logic is already in place it should not take me much longer to add the code to support the items listed in the threads mentioned here and in the PDO thread.







6
irmtfan
Re: add some needed facilities for module developers in working with xoops database.
  • 2013/5/1 1:46

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Yes they are all needed.
we need many things.
Currently im stuck in another query because xoops is not capable to do it.
http://dev.mysql.com/doc/refman/4.1/en/insert.html
INSERT [LOW_PRIORITY DELAYED HIGH_PRIORITY] [IGNORE]
    [
INTOtbl_name [(col_name,...)]
    {
VALUES VALUE} ({expr | DEFAULT},...),(...),...
    [ 
ON DUPLICATE KEY UPDATE
      col_name
=expr
        
[, col_name=expr] ... ]

the ON DUPLICATE KEY UPDATE is very needed for a logger module. because i want to increment hits and i want to do it with efficiency.
Now we have to get all and when we find nothing then we insert otherwise increment.

So we need many facilities.

@redheadedrod :
Your work is really needed and i will lokking forward to be able to use it specially in xoops 2.6
try to implement any possible query.

@zyspec :
Yes i saw that request some months ago. please link this topic to that request too.

IMO there is a bug in core in class/database/database.php
function prefix($tablename '')
    {
        if (
$tablename != '') {
            return 
$this->prefix '_' $tablename;
        } else {
            return 
$this->prefix;
        }
    }
}


It will add the prefix more than one time.
to correct it i suggest this:
function prefix($tablename '')
    {
        if (
$tablename != '') {
    
// check if database prefix is not added yet and then add it!!!
    
if (strpos($tablename$this->prefix "_") !== 0) {
        
$tablename$this->prefix '_' $tablename;
    }

            return 
$tablename;
        } else {
            return 
$this->prefix;
        }
    }
}

Can we commit this to SVN?


7
redheadedrod
Re: add some needed facilities for module developers in working with xoops database.

I still need a list of the desired options to add to the database connector or try to figure it out myself...

I am not touching anything outside of the database connector.

And as to the prefix statement I would think the bug is in the program that calls that routine more than once... I have done this myself but not sure it is a "bug" in the core.

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.

8
irmtfan
Re: add some needed facilities for module developers in working with xoops database.
  • 2013/5/1 4:32

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Quote:
And as to the prefix statement I would think the bug is in the program that calls that routine more than once... I have done this myself but not sure it is a "bug" in the core.
yes you are totally right. but core should have the best code how that it could prevent bad coding by module developers as much as possible. because a module developer may write this more than once in his code: $db->prefix($table); So if a current core function allow adding prefix more than once without checking, I will consider it as a core bug. it can be fixed by a simple checking. Edit: @zyspec: I review your alter class more. Now i think we need these: 1- one class for alter model in class/model/alter.php 2- one class for show model in class/model/show.php 3- add required code to XoopsObject class to work with the above models. Also about your code i think: - we should decide about using FIELDS or COLUMNS - it is better to return more information in the SHOW see:
public function showFields($field null)
    {
        
$sql "SHOW FIELDS FROM {$this->table}";
        if (isset(
$field)) {
            
$sql .= " LIKE '{$field}'";
        }
        if (!
$result $this->db->queryF($sql)) {
            
xoops_error($this->db->error().'<br />'.$sql);
            return 
false;
        }
        
$ret = array();
        while (
$myrow $this->db->fetchArray($result)) {
            
$ret[$myrow["Field"]] = $myrow;
        }
        return 
$ret;
    }
- better to not use ` ` ? it will avoid mistypes. - why 2 parameters for ADD $column_type and $params. one is enough. the developer can add anything he like. eg: for add a field
MYFIEKD TINYINT100 UNSIGNED DEFAULT '0' NOT null AFTER OTHERFIELD
Your opinion?

9
redheadedrod
Re: add some needed facilities for module developers in working with xoops database.

We have to be careful in requesting making such additions to core such as a check for someone calling prefix more than once. This is a simple bug to fix and I don't believe it is something that should be in the core. Any extraneous code added to the core will slow down the execution of the code. 1 line here and another there may not seem like much but if you add enough to a large system it can reduce the over all performance considerably.

IMO programming bugs like that should be left as bugs in the module unless it can affect the security of the site. We are trying to streamline the core and remove any unnecessary functions. Adding a couple lines to code that is executed every time a table is addressed seems a bit much to prevent a programming bug from showing. Personally I would rather the bug show its self instead of being masked like this so I can fix it. And I HAVE had this happen in the past.

I will however make sure I incorporate the prefix into the code I am building so it is automatically added and will be transparent to the code when using the new proposed functions.

10
irmtfan
Re: add some needed facilities for module developers in working with xoops database.
  • 2013/5/2 3:47

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


@redheadedrod:
You are right but please pay attention to my reasons for naming it a core bug and for requesting to add that line:
1- There are some functions in the core that you should add the prefix before using them like this:
$out func1($db->prefix("mytable"));

but there are others again in the core that you should not add the prefix before using them like this:
$out func2("mytable");

because the func2 is like this:
function func2($tablename)
{
$tablename $db->prefix($tablename);
}

So we can not blame module developers too much the core is not consistent too and the module developer have to be familiar with all of them to write correct codes

2- In many cases the module developer will received some kind of php/mysql error after adding extra prefix and can find that bad coding. but in very rare cases like this query for showing the existence of a table:

"SHOW TABLES LIKE '{$table}'"

there is not any php/mysql error. even the core logger will show you this:
SHOW TABLES LIKE my_table

because the xoops core logger will automatically sanitized the query log and will removed db prefix even if there were more than one prefix, all of them will be removed.
so again it is a bad job by the core and you can not blame the poor module developer because he didnt received any error It would be really hard for him to find that why SHOW TABLES LIKE my_table result is empty

3- While Core 2.6.0 is rather clean, core 2.5.5 and 2.5.6 is full of hard-codes and unneeded extra codes and unneeded checking. So IMO adding one line which have many benefits will not be too much for that.

4- we can add some kind of logger errors after that line to warn the module developer that he added prefix more than once
function prefix($tablename '')
    {
        if (
$tablename != '') {
    
// check if database prefix is not added yet and then add it!!!
    
if (strpos($tablename$this->prefix "_") !== 0) {
        
$tablename$this->prefix '_' $tablename;
        
$this->setLogger("DB Prefix is added");
    } else {
        
$this->setLogger("You add prefix more than once but core ignore it. Please correct your codes");
    }

            return 
$tablename;
        } else {
            return 
$this->prefix;
        }
    }
}

Login

Who's Online

193 user(s) are online (92 user(s) are browsing Support Forums)


Members: 0


Guests: 193


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