vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Installation Tables and Columns Help... (https://vborg.vbsupport.ru/showthread.php?t=200575)

Jaxel 01-03-2009 07:43 PM

Installation Tables and Columns Help...
 
Okay, lets say I have a mod... and in version1, I have the following install code... (the code is an example, I know its not functioning, but thats got nothing to do with my problem)

Code:

$db->query_write("CREATE TABLE IF NOT EXISTS table (
  column1 int auto_increment,
  column2 int,
  PRIMARY KEY  (column1)
)");


Then I create a version2, with an extra column... so I made my install code this...

Code:

$db->query_write("CREATE TABLE IF NOT EXISTS table (
  column1 int auto_increment,
  column2 int,
  column3 int,
  PRIMARY KEY  (column1)
)");
$db->query_write("ALTER TABLE table ADD COLUMN column3 int)");


Now I am making a version 3... and there is yet another column to add... I tried the following code, but I got an error when it hit the ADD COLUMN column3 code... because column3 already exists.

Code:

$db->query_write("
CREATE TABLE IF NOT EXISTS table (
  column1 int auto_increment,
  column2 int,
  column3 int,
  column4 int,
  PRIMARY KEY  (column1)
)");
$db->query_write("ALTER TABLE table ADD COLUMN column3 int)");
$db->query_write("ALTER TABLE table ADD COLUMN column4 int)");

I tried looking for a possible IF NOT EXIST condition for altering tables to add columns, but it looks like you cant do it. I know I could just delete the add column3 query, and it would work great for people who are updating from version2 to version3. But that line would still be required if people were upgrading from version1. So is there a proper way for me to handle this problem?

Kirk Y 01-03-2009 08:55 PM

You could run a query to retrieve the columns in "table" and then control the flow of logic from there.

PHP Code:

if (!in_array('column3'$columns))
{
    
// create column 3 as it does not exist yet



Jaxel 01-03-2009 09:52 PM

Hmm... how would that code be written exactly? What would be the code to construct $columns?

Kirk Y 01-03-2009 10:22 PM

PHP Code:

$columns $db->query_read("SHOW COLUMNS FROM " TABLE_PREFIX "table"); 

You'd need to loop through $columns and take out the "field" key.

Jaxel 01-04-2009 03:31 AM

Huh? I'm confused? What would I need to loop? Wouldn't this be enough?

Code:

$columns = $db->query_read("SHOW COLUMNS FROM table");
if (!in_array('column3', $columns))
{
    $db->query_write("ALTER TABLE table ADD COLUMN column3 int)");
}
if (!in_array('column4', $columns))
{
    $db->query_write("ALTER TABLE table ADD COLUMN column4 int)");
}

Thanks for the help BTW.

Kirk Y 01-04-2009 03:45 AM

If you look at what $columns contains, you'll see it is a two-dimensional array. You need to loop through it after your query to extract the relevant information; in this case - the field names.

PHP Code:

$columnquery $db->query_read("SHOW COLUMNS FROM table");
$columns = array();
while (
$column $db->fetch_array($columnquery))
{
    
$columns[] = $column['field'];


Then you can run the in_array check.

Dismounted 01-04-2009 04:02 AM

There is a reason why you are allowed to create multiple install codes in products. In the version 1 install code, use the first query. In the version 2 code, create a query using ALTER and add another column. In the version 3 install code, create another query.

On a new installation of the modification, vBulletin will run the install codes in order of version.

Jaxel 01-04-2009 08:20 AM

So this this shoudl work? the table name is video... and the column is timelength...

Code:

$columns = $db->query_read("SHOW COLUMNS FROM video");
$videocolumns = array();
while ($column = $db->fetch_array($columns))
{
  $videocolumns[] = $column['field'];
}
if (!in_array('timelength', $videocolumns))
{
  echo('<li>Altering Table <strong>' . TABLE_PREFIX . 'video</strong> ... ');
  $db->query_write("ALTER TABLE video ADD timelength int(10) unsigned NOT NULL");
}

I will use the install code stuff in future iterations...

Dismounted 01-04-2009 09:01 AM

Why not use the install code functionality now?

Jaxel 01-04-2009 01:26 PM

Because what if someone is upgrading from Version 1 to Version 3... They wont get the changes in Version 2, because its not in the new install code. If I put the new install code in Version 3; then it gets the redundancy error.

Anyway, the code worked, thanks Kirk!


All times are GMT. The time now is 02:19 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01024 seconds
  • Memory Usage 1,744KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (5)bbcode_code_printable
  • (3)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 

Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete