Upgrade script

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I want to release my upgrade to my MILPACS module soon to RC2 from RC1. I have done a scipt to add fields to some tables but I have a problem I can't find answers on. My initial table design had some fields that ended up being the wrong datatype (tinyint should have been larger). How can I change the datatype of around 4 or 5 fields in 3 or 4 tables?

For example:

Code:

-- Table structure for table `nuke_milpacs_service_record`


-- 





CREATE TABLE `nuke_milpacs_service_record` (


  `record_id` tinyint(4) NOT NULL auto_increment,


  `uniqueid` tinyint(4) default NULL,


  `record_dt` date default NULL,


  `details` varchar(255) default NULL,


  PRIMARY KEY  (`record_id`)


) TYPE=MyISAM;

should have been

Code:




-- Table structure for table `nuke_milpacs_service_record`


-- 





CREATE TABLE `nuke_milpacs_service_record` (


  `record_id` smallint(4) NOT NULL auto_increment,


  `uniqueid` smallint(4) default NULL,


  `record_dt` date default NULL,


  `details` varchar(255) default NULL,


  PRIMARY KEY  (`record_id`)


) ENGINE=MyISAM DEFAULT CHARSET=latin1;

tinyint ended up being way to small.

Could I SELECT INTO a temp table, DROP TABLE, CREATE TABLE and INSERT back into a new table with the proper datatypes? Is this my only option?

I need this all contained inside a milpacs_upgrade.php

registered · Site Admin Joined: Jun 04, 2004 Posts: 6437

You should be able to change the data type in phpMyAdmin without losing the data. Of course, I'd back up the tables first!

Posted: Thu Jun 15, 2006 3:10 pm

kguske wrote:

You should be able to change the data type in phpMyAdmin without losing the data. Of course, I'd back up the tables first!

True, but this wont help the handful of people that have RC1 and are waiting for me to release RC2. Is there no way to change datatypes thru a PHP script?

Posted: Thu Jun 15, 2006 3:51 pm

When you make the change in phpMyAdmin, it will generate the SQL statements, which you could copy into an update script.

registered · Posted: Fri Jun 16, 2006 12:28 am

What kguske said! Laughing

It should be able to be done with an ALTER and with no data loss as long as the types are compatible and going from "smaller" to "larger". At least that has been my experience anyways.

Posted: Fri Jun 16, 2006 6:05 am

tinyint to smallint should work. That gives me 32,767 I think. I don't know what I was thinking with tinyint.

Posted: Thu Jun 29, 2006 10:47 am

I can't have this script written this way can I?

It seems $sql would be overwritten each time before the query would execute.

Code:

$sql = "ALTER TABLE ".$prefix."_milpacs_members ADD 'nukeusername' TEXT NOT NULL AFTER 'u_name'"; 


$sql = "ALTER TABLE ".$prefix."_milpacs_members CHANGE 'uniqueid' 'uniqueid' INT( 11 ) NOT NULL AUTO_INCREMENT";


$sql = "ALTER TABLE ".$prefix."_milpacs_service_record CHANGE 'record_id' 'record_id' INT( 4 ) NOT NULL AUTO_INCREMENT";


$sql = "ALTER TABLE ".$prefix."_milpacs_members ADD 'ismember' SMALLINT( 1 ) NOT NULL AFTER 'uniqueid'";


$sql = "ALTER TABLE ".$prefix."_milpacs_units DROP INDEX 'unit_motto'";


$sql = "ALTER TABLE ".$prefix."_milpacs_members DROP INDEX 'uniqueid'"; 


$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_name' VARCHAR( 50 ) NOT NULL"; 


$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_tag' VARCHAR( 20 ) NOT NULL"; 


$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_nick' VARCHAR( 20 ) NOT NULL"; 


$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'recruitemail' VARCHAR( 50 ) NOT NULL"; 


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_sotm";


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_points";


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_points_lkup";


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_pass";


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_access";


$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_hof"; 





$result = $db->sql_query($sql);

Posted: Thu Jun 29, 2006 6:18 pm

no but you could through all of it into one statement or variable then apply this code:

$buffer1 = 'START TRANSACTION;' . $buffer . '; COMMIT;';

$query_split = preg_split ("/[;]+/", $buffer2);
foreach ($query_split as $command_line) {
$command_line = trim($command_line);
if ($command_line != '') {

$query_result = mysql_query($command_line);
if ($query_result == 0) {
echo "$command_line";
}

}

}
return $query_result;

$buffer is being used as the variable for ALL the sql insertions, the script breaks it all down at the ; in all statements, now I've seen raven and other use arrays and not buffer it and I'm not sure why, so I'll continue to use this until I learn why they use it instead as perhaps there is a problem or inconvienence.
This will work on any database as long as you set the script to include mainfile and set globals$db
then change line from mysql_query to $db->sql_query

Hope this helps.

Posted: Wed Jul 05, 2006 3:36 pm

I just execute them separately, one-by-one, with my installers. Some mySQL setups will allow more than one statement per call, but it is not an adviseable "setup", so I would expect that most Web Hosts would not have mySQL configured this way. (Opens the door even further for SQL injection issues.)

Posted: Wed Jul 05, 2006 9:04 pm

Are you saying that mysql_query can run more then one statement at once?

I thought in php that mysql_query was only allowed to run one mysql_query at a time.

Thus the reason for my script.

Posted: Fri Jul 21, 2006 1:53 pm

I can't seem to get this last statement to work

This it what it looks like on the screen.

Quote:

Upgrading the MILPACS tables!

--------------------------------------------------------------------------------
Operation Results:
--------------------------------------------------------------------------------
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_service_record succeeded
Alter nuke_milpacs_service_record succeeded
Alter nuke_milpacs_award_lkup succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_units succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Drop nuke_milpacs_sotm succeeded
Drop nuke_milpacs_points succeeded
Drop nuke_milpacs_points_lkup succeeded
Drop nuke_milpacs_pass succeeded
Drop nuke_milpacs_access succeeded
Drop nuke_milpacs_hof succeeded
Information inserted into milpacs_main was Unsuccessful!

--------------------------------------------------------------------------------
Operation Complete!
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
REMOVE THIS SCRIPT WHEN FINISHED!

My milpacs_main just has some information that I moved from a language file, and placed in the database. The table is empty.

My upgrade script actually creates these earlier.

Code:

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_name VARCHAR( 50 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_tag VARCHAR( 20 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

and so on.

But at the end I want to insert data into these fields and it won't work.

Code:

$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");


   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}

Here is my entire upgradeRC1-RC2.php

Code:

<?php


/********************************************************/


/* MILPACS                                              */


/* By: 3rd ID (donovan@3rd-infantry-division.net)       */


/* http://www.3rd-infantry-division.net                 */


/* Copyright © 2006 by Steven Donovan                   */


/********************************************************/





/************************************************************************


* Original Installer Design / Structure


* By: NukeScripts Network (webmaster@nukescripts.net)


* http://www.nukescripts.net


* Copyright © 2000-2005 by NukeScripts Network


************************************************************************/





if ( !defined( 'MILPACS_LOADED' ) ) { die( "Illegal File Access" ); }


//Upgrade script for MILPACS beta 1.0 RC2





require_once("mainfile.php");


global $prefix, $db; 


OpenTable();





echo "Upgrading the MILPACS tables!<br />\n";


echo "<hr>\n";


echo "<b>Operation Results:</b><hr>\n";


$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members ADD nukeusername TEXT NOT NULL AFTER u_name");


if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members CHANGE uniqueid uniqueid INT( 11 ) NOT NULL AUTO_INCREMENT");


if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_service_record CHANGE record_id record_id INT( 4 ) NOT NULL AUTO_INCREMENT");


if (!$result) { echo "Alter ".$prefix."_milpacs_service_record failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_service_record succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_service_record CHANGE uniqueid uniqueid INT( 11 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_service_record failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_service_record succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_award_lkup CHANGE uniqueid uniqueid INT( 11 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_award_lkup failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_award_lkup succeeded<br>\n"; } 





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members ADD ismember SMALLINT( 1 ) AFTER uniqueid");


if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_units DROP INDEX unit_motto");


if (!$result) { echo "Alter ".$prefix."_milpacs_units failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_units succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members DROP INDEX uniqueid");


if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_name VARCHAR( 50 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_tag VARCHAR( 20 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN drillpass");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN u_email");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN name");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN color");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_nick VARCHAR( 20 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD recruitemail VARCHAR( 50 ) NOT NULL");


if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_sotm");


if (!$result) { echo "Drop ".$prefix."_milpacs_sotm failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_sotm succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_points");


if (!$result) { echo "Drop ".$prefix."_milpacs_points failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_points succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_points_lkup");


if (!$result) { echo "Drop ".$prefix."_milpacs_points_lkup failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_points_lkup succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_pass");


if (!$result) { echo "Drop ".$prefix."_milpacs_pass failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_pass succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_access");


if (!$result) { echo "Drop ".$prefix."_milpacs_access failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_access succeeded<br>\n"; }





$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_hof");


if (!$result) { echo "Drop ".$prefix."_milpacs_hof failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_hof succeeded<br>\n"; }





$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");


   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}





echo "<hr><b>Operation Complete!</b><hr>\n";


echo "<hr><b>REMOVE THIS SCRIPT WHEN FINISHED!</b><hr>\n";


CloseTable();


include("footer.php");   


?>

Posted: Fri Jul 21, 2006 3:06 pm

darklord wrote:

Are you saying that mysql_query can run more then one statement at once?

I thought in php that mysql_query was only allowed to run one mysql_query at a time.

Thus the reason for my script.

Yes, it can, but like I said, it is a risky thing to have on as it would allow even more opportunities for SQL injection if you could do many in ONE call. I would bet that this feature is turned OFF in most installations.

Posted: Fri Jul 21, 2006 3:09 pm

Quote:

But at the end I want to insert data into these fields and it won't work.

I cannot see anything wrong with this insert statement structure as you have it, however, could you provide the full table structure for this one table (from phpMyAdmin) as I can only see the new fields that you have added.

Posted: Fri Jul 21, 2006 4:05 pm

Perhaps the commas are causing the database to think there were too many inserts.

Posted: Fri Jul 21, 2006 6:11 pm

montego wrote:

Quote:

But at the end I want to insert data into these fields and it won't work.

I cannot see anything wrong with this insert statement structure as you have it, however, could you provide the full table structure for this one table (from phpMyAdmin) as I can only see the new fields that you have added.

Here is what becomes of my table after the ALTER commands:

motd text
unit_name varchar(50)
unit_tag varchar(20)
unit_nick varchar(20)
recruitemail varchar(50)

The INSERT at the end does not load the values I need.

Code:

$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");


   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}

Posted: Sat Jul 22, 2006 8:15 am

Donovan, I am stumped. Looks fine to me. Try copying and pasting these EXACT values into that table via phpMyAdmin and see if you get any errors.

Is your IF statement right after the insert properly catching that an error occurred?

Also you can try to add this line right in-between these two lines:

print_r ($db->sql_error());

If that gives you an error, than replace it with this:

$sql_error = $db->sql_error();
echo $sql_error['message'];

We need to find some better clues here...