Fork me on GitHub

module upgrades and tables  Bottom

  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 12, 2009 - 07:15 AM
    im just working on something and its pretty much done but i've run into one issue. I'll show some of my code first so you know what im talking about. (ps its a modification/extention of the FAQ module)
    from pntables

    Code

    // Initialise table array
        $pntable = array();

        // Full table definition
        $pntable['faqanswer'] = DBUtil::getLimitedTablename('faqanswer');
        $pntable['faqanswer_column'] = array ('faqid'         => 'pn_id',
                                              'question'      => 'pn_question',
                                              'urltitle'      => 'pn_urltitle',
                                              'answer'        => 'pn_answer',
                                              'submittedbyid' => 'pn_submittedbyid',
                                              'answeredbyid'  => 'pn_answeredbyid',
                                              'ratingup'        => 'pn_ratingup',           //added this version
                                              'ratingdown'        => 'pn_ratingdown'); //added this version
        $pntable['faqanswer_column_def'] = array('faqid'           => 'I NOTNULL AUTO PRIMARY',
                                                 'question'        => 'X DEFAULT NULL',
                                                 'urltitle'        => "X NOTNULL DEFAULT ''",
                                                 'answer'          => 'X',
                                                 'submittedbyid'   => 'I NOTNULL',
                                                 'answeredbyid'    => 'I NOTNULL',
                                                 'ratingup'          => 'I NOTNULL',
                                                 'ratingdown'          => 'I NOTNULL');


    so everything i need it to do works fine if its a fresh install, however if im doing an upgrade, no dice the module freaks out about not knowing what ratingup and rating down is. in my upgrade section if the pninit i've been trying to work it by using a SQL command ALTER TABLE. however im pretty sure thats a depreciated way of doing it, i assume theres a DBUtil function that takes care of what i need to do, can anyone point me in the right direction. thanks!!!!
  • HalbrookTech
    HalbrookTech
    Developer
    • Rank: Developer
    • Registered: Dec 31, 1969
    • Last visit: Jun 01, 2010
    • Posts: 6859
    Link to this post Posted: Jan 12, 2009 - 07:59 AM
    Hmm look at one of the other modules how they handle adding new fields in an upgrade. I think News may have experienced some DB changes.

    --
    Home Page | Find on Facebook | Follow on Twitter
  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 13, 2009 - 02:49 AM
    no dice, thats what i've been trying to do. copy off other modules. pages doesnt actually do what i want it just migrated some stuff and dropped some columns. i have been having a really hard time getting onto the zikula community site to post tot he forums or check documentation etc.

    after like 2 days of checking every module and reading all the SQL syntax i can what i have come up with (that does not work) is

    Code

    $dbconn =& pnDBGetConn(true);
            $pntable =& pnDBGetTables();
            $FAQanswertable = $pntable['faqanswer'];
            $FAQanswercolumn = &$pntable['faqanswer_column'];
            $sql = "ALTER TABLE $FAQanswertable ADD $FAQanswer_column[ratingup] int(11) default '0' NOT NULL";
            $dbconn->Execute($sql);
            if ($dbconn->ErrorNo() != 0) {
            pnSessionSetVar('errormsg', $dbconn->ErrorMsg());
            return false;
            }


    the error that it returns is

    Quote


    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int(11) default '0' NOT NULL' at line 1



    PS: in the above example i didn't bother putting any code for rating down, i took that part out just trying to get one thing to work



    edited by: kylegio, Jan 13, 2009 - 09:51 AM
  • HalbrookTech
    HalbrookTech
    Developer
    • Rank: Developer
    • Registered: Dec 31, 1969
    • Last visit: Jun 01, 2010
    • Posts: 6859
    Link to this post Posted: Jan 13, 2009 - 11:23 AM
    Are you writing this for PostNuke or Zikula?

    --
    Home Page | Find on Facebook | Follow on Twitter
  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 13, 2009 - 06:27 PM
    writing it for zikula, i am pretty sure that the method i showed above is depreciated from postnuke. its just the only way i have seen it done in other modules.
  • HalbrookTech
    HalbrookTech
    Developer
    • Rank: Developer
    • Registered: Dec 31, 1969
    • Last visit: Jun 01, 2010
    • Posts: 6859
    Link to this post Posted: Jan 13, 2009 - 08:00 PM
    Look at the new version of the FAQ Module in the ValueAddons package. The new method may make it easier to spot the problem.

    --
    Home Page | Find on Facebook | Follow on Twitter
  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 14, 2009 - 12:09 AM
    FIXED, thanks for the help.

    Code

    $sqls   = array();
                $sqls[] = "ALTER TABLE $tables[faqanswer] ADD pn_ratingup INT NOT NULL DEFAULT '0'";
                $sqls[] = "ALTER TABLE $tables[faqanswer] ADD pn_ratingdown INT NOT NULL DEFAULT '0'";
                foreach ($sqls as $sql) {
                    if (!DBUtil::executeSQL($sql)) {
                        return false;
                    }
            }
  • nestormateo
    nestormateo
    Team Member
    • Rank: Team Member
    • Registered: Sep 06, 2006
    • Last visit: May 09, 2010
    • Posts: 2446
    Link to this post Posted: Jan 14, 2009 - 04:23 AM
    DBUtil::changeTable('faqanswer'); does the job.
    Usually it's called at the beggining of the upgrade function, but sometimes it's not because it's not desired to update the table until some fields are renamed or values moved bedore delete them, but that's the way to do in Zikula icon_wink

    --
    - Mateo T. -
    Mis principios... son mis fines
  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 14, 2009 - 09:17 AM
    in case 1.11 (which is the upgrade from version 1.11 which was the original release) the folling:

    Code

    if (!DBUtil::changeTable('faqanswer')) {
                    return false;
                }


    according to the API:
    bool changeTable( tablename $tablename, [SQL $SQL = null], [tabopt $tabopt = null])

    tablename $tablename The tablename key for the PNTables structure
    SQL $SQL ADODB dictionary representation of table (optional) (default=null)
    tabopt $tabopt Table options specific to this table (optional) (default=null)



    so it doesn't need to be specified what to add to the table? will it just figure out from the pntables what needs to be added? if that's the case it isn't working for me. however the ALTER TABLE method does work, but id prefer to do it in the most streamlined/proper way possible


    thanks for all the help BTW
  • nestormateo
    nestormateo
    Team Member
    • Rank: Team Member
    • Registered: Sep 06, 2006
    • Last visit: May 09, 2010
    • Posts: 2446
    Link to this post Posted: Jan 14, 2009 - 11:05 AM
    weird
    yes, the idea is to detect differences between the pntables definition and the table in the database and apply the changes; this doesn't work for column renames, but i understood that adds new columns. Probably a problem with the definition without a default value, is probable that if the ALTER return error it just does nothing and doesn't log an error if the config debugsql var is not set (or something like that).

    Try with "I NOTNULL DEFAULT '0'" and it probably add the fields automatically icon_wink
    keep it up

    --
    - Mateo T. -
    Mis principios... son mis fines
  • kylegio
    kylegio
    Softmore
    • Rank: Softmore
    • Registered: Sep 30, 2008
    • Last visit: May 29, 2010
    • Posts: 201
    Link to this post Posted: Jan 14, 2009 - 11:41 AM
    well, the SQL ALTER command isnt actualy being used in the definition of the tables, i'm only using the ALTER to add the field i need on upgrade because DBUtil isn't doing it. the first bit of code i quoted above (in my first post) is the actual definition for the tables, as seen in pntables. my understanding is that after adding to pntables the last 2 fields, then calling DBUtil::changeTable on that table should add those 2 new fields. the upgrade function doesnt call the changeTable function by default because after version 1.11 a column was renamed, then changeTable is called after the rename, however not many users are upgrading from version 1.11, so i need to call the function again in my newer upgrade. i am calling it exactly as it appears in case for 1.11, however the new fields are not being added still.

    just defining the SQL commands to add this field to the table and then running the dbutil::executesql function gets the job done just fine, but it seems clumsy.

Users on-line

This list is based on users active over the last 60 minutes.