SQLite - ALTER TABLE Support

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
User avatar
naderman
Consultant
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Karlsruhe, Germany
Contact:

SQLite - ALTER TABLE Support

Post by naderman »

Hello,

I wonder how phpBB3 is going to deal with SQLite's limited ALTER TABLE support. The DROP column command does not exist which is needed to remove a profile field. Is phpBB going to use temporary tables as a workaround or is there any other plan on how to support profile fields with SQLite? Also are there other ways to work around this problem than creating a temporary table with the column removed, moving all data from the old table, removing the old table and renaming the temorary table to the name of the real one?

naderman

theamazingTWOeyedman
Registered User
Posts: 35
Joined: Sun Jun 08, 2003 3:33 pm

Re: SQLite - ALTER TABLE Support

Post by theamazingTWOeyedman »

I doubt that things like this will actually be replied to but i'll give ya a bump anyway... I'm sure they'll find some way to achieve this though with minimal headache (as always)
Hosting? - mlhosting.net

User avatar
A_Jelly_Doughnut
Registered User
Posts: 1780
Joined: Wed Jun 04, 2003 4:23 pm

Re: SQLite - ALTER TABLE Support

Post by A_Jelly_Doughnut »

You could simply deactivate a field if you're using SQLite.
A_Jelly_Doughnut

bestweb
Registered User
Posts: 16
Joined: Wed Feb 18, 2004 7:31 pm

Re: SQLite - ALTER TABLE Support

Post by bestweb »

Hi,

This is a function I use in the SQLite Layer of my own concurrent developing forum:

(this also happens to be the only way to alter a SQLite table)

Code: Select all

/* Modified and cleaned version of http&#58//code.jenseng.com/db/ */
function alterTable($table, $alterdefs) {
	
	$this->num_queries++;

	if($alterdefs != '') {
		$result								= sqlite_query($this->link, "SELECT sql, name, type FROM sqlite_master WHERE tbl_name = '". $table ."' ORDER BY type DESC");
	
		if(sqlite_num_rows($result) > 0) {
		
			$row							= sqlite_fetch_array($result); //table sql
			$tmpname						= 't'. time();
			$origsql						= trim(preg_replace("/[\s]+/", " ", str_replace(",", ", ", preg_replace("/[\(]/", "( ", $row['sql'], 1))));
			$createtemptableSQL				= 'CREATE TEMPORARY '.substr(trim(preg_replace("'". $table ."'", $tmpname, $origsql, 1)), 6);
			$createindexsql					= array();
			$i								= 0;
			$defs							= preg_split("/[,]+/", $alterdefs, -1, PREG_SPLIT_NO_EMPTY);
			$prevword						= $table;
			
			/* Doesn't work with decimal() columns.. e.g. decimal(5,2) */
			$oldcols						= preg_split("/[,]+/", substr(trim($createtemptableSQL), strpos(trim($createtemptableSQL),'(')+1), -1, PREG_SPLIT_NO_EMPTY);

			$newcols						= array();

			for($i = 0; $i < count($oldcols); $i++ ) {
				$colparts						= preg_split("/[\s]+/", $oldcols[$i], -1, PREG_SPLIT_NO_EMPTY);
				$oldcols[$i]					= $colparts[0];
				$newcols[$colparts[0]]			= $colparts[0];
			}

			$newcolumns = '';
			$oldcolumns = '';

			reset($newcols);

			while(list($key, $val) = each($newcols)) {
				$newcolumns .= iif($newcolumns, ', ', '') . $val;
				$oldcolumns .= iif($oldcolumns, ', ', '') . $key;
			}

			$copytotempsql						= 'INSERT INTO '. $tmpname .'('. $newcolumns .') SELECT '. $oldcolumns .' FROM '. $table;
			$dropoldsql							= 'DROP TABLE '. $table;
			$createtesttableSQL					= $createtemptableSQL;

			foreach($defs as $def) {
				$defparts						= preg_split("/[\s]+/", $def, -1, PREG_SPLIT_NO_EMPTY);
				$action							= strtolower($defparts[0]);

				switch($action) {
					case 'add'&#58 {
						
						if(sizeof($defparts) <= 2) {
							trigger_error('An error occured near "'. $defparts[0] . iif($defparts[1], ' '. $defparts[1], '').'"&#58 syntax error.', E_USER_ERROR);
							return FALSE;
						}
						
						$createtesttableSQL				= substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).',';
						
						for($i = 1; $i < sizeof($defparts); $i++) {
							$createtesttableSQL			.= ' '.$defparts[$i];
						}
						
						$createtesttableSQL				.= ')';

						break;
					}
					case 'change'&#58 {

						if(count($defparts) <= 3) {
							trigger_error('An error occured near "'. $defparts[0] . iif($defparts[1], ' '. $defparts[1], '') . iif($defparts[2], ' '. $defparts[2], '') .'"&#58 syntax error.', E_USER_ERROR);
							return FALSE;
						}
						if($severpos = strpos($createtesttableSQL, ' '. $defparts[1] .' ')) {
							
							if($newcols[$defparts[1]] != $defparts[1]) {
								trigger_error('Unknown column "'. $defparts[1] .'" in "'. $table .'"', E_USER_ERROR);
								return FALSE;
							}
							$newcols[$defparts[1]] = $defparts[2];
							$nextcommapos = strpos($createtesttableSQL, ',', $severpos);
							$insertval = '';
							for($i = 2; $i < count($defparts); $i++) {
								$insertval .= ' '. $defparts[$i];
							}

							if($nextcommapos) {
								$createtesttableSQL = substr($createtesttableSQL, 0, $severpos) . $insertval . substr($createtesttableSQL, $nextcommapos);
							} else {
								$createtesttableSQL = substr($createtesttableSQL, 0, $severpos - iif(strpos($createtesttableSQL,','), 0, 1)) . $insertval .')';
							}
						
						} else {
							trigger_error('Unknown column "'. $defparts[1] .'" in "'. $table .'"', E_USER_ERROR);
							return FALSE;
						}
						break;
					}

					case 'drop'&#58 {
						if(count($defparts) < 2){
							trigger_error('An error occured near "'. $defparts[0] . iif($defparts[1], ' '. $defparts[1], '') .'"&#58 syntax error.', E_USER_ERROR);
							return FALSE;
						}
						if($severpos = strpos($createtesttableSQL,' '. $defparts[1].' ')) {
							
							$nextcommapos			= strpos($createtesttableSQL, ',', $severpos);
							if($nextcommapos) {
								$createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1);
							} else {
								$createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0&#581;) - 1).')';
							}
							unset($newcols[$defparts[1]]);
						} else{
							trigger_error('Unknown column "'. $defparts[1] .'" in "'. $table .'".', E_USER_ERROR);
							return FALSE;
						}
						break;
					}
					default&#58 {
						trigger_error('An error occured near "'. $prevword .'"&#58 syntax error.', E_USER_ERROR);
						return FALSE;
					}
				}

				$prevword = $defparts[count($defparts)-1];
			}


			/**
			 * this block of code generates a test table simply to verify that the columns 
			 * specifed are valid in an sql statement this ensures that no reserved words 
			 * are used as columns, for example.
			 */
			if(!$this->query($createtesttableSQL)){
				trigger_error('The test table could not be created.<br /><br />'. $createtesttable, E_USER_ERROR);
				return FALSE;
			}

			$droptempsql = 'DROP TABLE '. $tmpname;
			sqlite_query($this->link, $droptempsql);
			/* end block */


			$createnewtableSQL	= 'CREATE '.substr(trim(preg_replace("'". $tmpname ."'", $table, $createtesttableSQL, 1)), 17);
			$newcolumns			= '';
			$oldcolumns			= '';

			reset($newcols);

			while(list($key, $val) = each($newcols)) {
				$newcolumns		.= iif($newcolumns, ', ', '') . $val;
				$oldcolumns		.= iif($oldcolumns, ', ', '') . $key;
			}

			$copytonewsql		= 'INSERT INTO '. $table .'('. $newcolumns .') SELECT '. $oldcolumns .' FROM '. $tmpname;
			
			/**
			 * Use a transaction here so that if one query fails, they all fail
			 */
			
			/* Begin the transaction */
			$this->beginTransaction();
			
			/* Create our temporary table */
			$this->executeUpdate($createtemptableSQL);

			/* Copy the data to the temporary table */
			$this->executeUpdate($copytotempsql);

			/* Drop the table that we are modifying */
			$this->executeUpdate($dropoldsql);
			
			/* Recreate that original table with the column added/changed/droped */
			$this->executeUpdate($createnewtableSQL);

			/* Copy the data from our temporary table to our new table */
			$this->executeUpdate($copytonewsql);

			/* Drop our temporary table */
			$this->executeUpdate($droptempsql);
			
			/* Finish the transaction */
			$this->commitTransaction();

		} else {
			trigger_error('Non-existant table&#58 '. $table, E_USER_ERROR);
			return FALSE;
		}

		return true;
	}
}
Implementation:

Code: Select all

$dba->alterTable('tablename', 'ADD newcolumn INTEGER UNSIGNED NOT NULL DEFAULT 0');
You should be able to figure out the the transactions are functions which call SQLite's built in transaction functions, etc.
Fet up with waiting for phpBB3?
k4 Bulletin Board version 2.0-Dev

Post Reply