1
Acer2004
FOREIGN KEY Refernces ..
  • 2005/8/30 12:58

  • Acer2004

  • Just popping in

  • Posts: 10

  • Since: 2005/7/7 1


XOOPs doesnot change reference table names to XOOPS_DB_PREFIX.table_name at foreign key references sql statement ... on the sql file that carry the database structure when installing the module

does any one has an idea

2
Mithrandir
Re: FOREIGN KEY Refernces ..

Yes, I have a code snippet supporting this:

in class/database/sqlutility.php, replace prefixQuery() function with this:
/**
     * add a prefix.'_' to all tablenames in a query
     * 
     * @param   string  $query  valid SQL query string
     * @param   string  $prefix prefix to add to all table names
     * @return  mixed   FALSE on failure
     */
    
function prefixQuery($query$prefix)
    {
        
$pattern "/^(INSERT INTO|CREATE TABLE|ALTER TABLE|UPDATE)(s)+([`]?)([^`s]+)\3(s)+/siU";
        
$pattern2 "/^(DROP TABLE)(s)+([`]?)([^`s]+)\3(s)?$/siU";
        
$pattern3 "/(REFERENCES)(s)+([`]?)([^`s]+)\3/iU";
        if (
preg_match($pattern$query$matches) || preg_match($pattern2$query$matches)) {
            
$replace "\1 ".$prefix."_\4\5";
            
$matches[0] = preg_replace($pattern$replace$query);
            
$matches[0] = preg_replace($pattern3$replace$matches[0]);
            return 
$matches;
        }
        return 
false;
    }
"When you can flatten entire cities at a whim, a tendency towards quiet reflection and seeing-things-from-the-other-fellow's-point-of-view is seldom necessary."

Cusix Software

3
Acer2004
Re: FOREIGN KEY Refernces ..
  • 2005/8/30 13:44

  • Acer2004

  • Just popping in

  • Posts: 10

  • Since: 2005/7/7 1


thank's Mithrandir,
in fact .. i was having no hope to solve this prob .. because most database designer doesnot use REFERENCES..

any way . i'm so happy .. i'm not trying ths solution .. but i think .. it'll work because it's logic

and i think i have just to add the two lines
1. $pattern3, and
2. the second line of $matches[0] .. this is right ?

by the way .. the class i'm designing and need to use REFERENCES is to facilitate reading, checking , and saving data from\to database table that related to other reference_tables using php to draw the input html form with just one line of code ..

4
Acer2004
Re: FOREIGN KEY Refernces ..
  • 2005/8/30 14:01

  • Acer2004

  • Just popping in

  • Posts: 10

  • Since: 2005/7/7 1


thank's again Mithrandir .. it's working right.
do u have any idea where these references stored in the MySQL database ..
i'm trying with 'COMMENT' field from 'show table status like' statement .. but i think there is a simple solution that that ..!!

5
Choozo
Re: FOREIGN KEY Refernces ..
  • 2005/11/14 20:31

  • Choozo

  • Just popping in

  • Posts: 3

  • Since: 2005/11/13


Quote:

Mithrandir wrote:
Yes, I have a code snippet supporting this:

in class/database/sqlutility.php, replace prefixQuery() function with this:
/**
     * add a prefix.'_' to all tablenames in a query
     * 
     * @param   string  $query  valid SQL query string
     * @param   string  $prefix prefix to add to all table names
     * @return  mixed   FALSE on failure
     */
    
function prefixQuery($query$prefix)
    {
        
$pattern "/^(INSERT INTO|CREATE TABLE|ALTER TABLE|UPDATE)(s)+([`]?)([^`s]+)\3(s)+/siU";
        
$pattern2 "/^(DROP TABLE)(s)+([`]?)([^`s]+)\3(s)?$/siU";
        
$pattern3 "/(REFERENCES)(s)+([`]?)([^`s]+)\3/iU";
        if (
preg_match($pattern$query$matches) || preg_match($pattern2$query$matches)) {
            
$replace "\1 ".$prefix."_\4\5";
            
$matches[0] = preg_replace($pattern$replace$query);
            
$matches[0] = preg_replace($pattern3$replace$matches[0]);
            return 
$matches;
        }
        return 
false;
    }

Ahhh, I've been searching around a bit for that info Mithrandir :)
But on that note, how do we make sure uninstall of the same module won't fail due to those very same 'dependancies' in the tables?

I've had to manually drop those tables in one shot as the uninstaller fails to drop single tables that are being referenced:
mysqldrop table xoops_table1,xoops_table2,xoops_table3;


Cheers! :)

6
Choozo
Re: FOREIGN KEY Refernces ..
  • 2005/11/15 17:37

  • Choozo

  • Just popping in

  • Posts: 3

  • Since: 2005/11/13


Oh, never mind. I just found out how to take advantage of the '$modversion['tables'][n] = "tablename"' in the xoops_version.php file!

Just have to be real careful when setting the order of tables there. Tables are dropped according to that order, so make sure you don't drop a table referenced from another before that 'another' table is dropped.

Cheers

Login

Who's Online

307 user(s) are online (255 user(s) are browsing Support Forums)


Members: 0


Guests: 307


more...

Donat-O-Meter

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

Latest GitHub Commits