Search the Community

#cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.0 Author: Skysnake Date: 2016.05.17 Script Function: Alter table drop column. By way of example the original table has these three columns: name,surname,nickname to be replaced with name,surname,number "We cannot drop a specific column in SQLite 3. See the FAQ. It is not possible to rename a column, remove a column, or add or remove constraints from a table. While you can always create a new table and then drop the older one.Dec 9, 2011" http://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite The only ALTER TABLE functions that SQLite supports are those that can be accomplished without having to rewrite the entire table. Or, to put it another way, the only ALTER TABLE functions supported are those that can be done in O(1) time. ALTER TABLE DROP COLUMN requires writing every row of the table. D. Richard Hipp http://sqlite.1065341.n5.nabble.com/Why-can-t-SQLite-drop-columns-td64687.html Notes: Required original array size of table to be altered (zero count +1) Required the name of the column to drop Required full "create table ..." syntax for new column eg: , number text not null default 'Contact Number') #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here ; SQLite.dll version must match #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Local Const $iMax = 6 ; this is the current table column count Local $query, $aResult, $iRows, $iColumns, $ColName, $sMsg, $mySQLdb, $aLessColmns[$iMax][1] ; will require only one column Local $sNewColNames Local $sLocalSQLiteDll = "sqlite3_win32-x86-3120100.dll" ; Local $sSQliteDll = _SQLite_Startup($sLocalSQLiteDll, False, 1) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _ "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir or from www.autoitscript.com") Exit -1 EndIf ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $mySQLdb = _SQLite_Open(@ScriptDir & '\mydb.sql3') ; Without $sCallback it's a resultless statement ; ..................................................... create table tORiginal for System Info $query = "CREATE TABLE if not exists tORiginal ( " _ & "id integer primary key unique not null " _ & ", name text not null default 'First Name' " _ & ", surname text not null default 'Surname' " _ & ", nickname text not null default 'Nickname' " _ & ", RegDate text NOT NULL DEFAULT current_timestamp " _ & " ) ; " If Not _SQLite_Exec($mySQLdb, $query) = $SQLITE_OK Then _ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg() & " * " & @ScriptLineNumber) ConsoleWrite("SQL created tORiginal " & @CRLF) ; Without $sCallback it's a resultless statement ; ..................................................... create table tORiginal for System Info $query = "Insert into tORiginal (name,surname,nickname) values ('John','Jones','Johnny') " _ & " ; " If Not _SQLite_Exec($mySQLdb, $query) = $SQLITE_OK Then _ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg() & " * " & @ScriptLineNumber) ConsoleWrite("SQL data inserted into tORiginal " & @CRLF) $query = " SELECT * FROM tORiginal " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "CREATEd and INSERTed" ) ; show the results of insert into SQL table ; https://www.sqlite.org/pragma.html#pragma_table_info $query = " pragma table_info(tORiginal) " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Pragma Result: Original Table") ; show the results of pragma ConsoleWrite("SQL PRAGMA to find all columns " & @CRLF) ; this is the AutoIt magic ; SQLite does not allow manipulation of the pragma result, but ; AutoIt can process this result as normal query result ; delete column %id% from result #cs Row|Col 0 |Col 1 |Col 2 |Col 3 |Col 4 |Col 5 [0]|cid |name |type |notnull|dflt_value |pk [1]|0 |id |INTEGER|0 | |1 [2]|1 |Name | |0 | |0 [3]|2 |Surname | |0 | |0 [4]|3 |Nickname | |0 | |0 ; >>>>>>>>> column to remove #CE ;https://www.autoitscript.com/wiki/Arrays#Comparing_Arrays Local $transfound = UBound($aResult, 1) - 1 For $i = 1 To $transfound Step +1 ConsoleWrite($aResult[$i][1] & @CRLF) ; Check that the array is big enough If UBound($aLessColmns) = $i Then ; Resize the array when $i is equal to the element count in the array to prevent subscript error ReDim $aLessColmns[$aLessColmns[0] + $iMax] EndIf If StringRegExp($aResult[$i][1], "nick") Then ;>>>>>>>>> remove the column you dont want ; it may be dangerous to use a Regex here :) ; do nothing, do not add this column name Else $aLessColmns[$i][0] = $aResult[$i][1] ; safely add data to new index element $aLessColmns[0][0] = $i ; update the index count for future reference EndIf Next ; for good measure ; Move backwards through the array deleting the blank lines For $i = UBound($aLessColmns) - 1 To 0 Step -1 If $aLessColmns[$i][0] = "" Then _ArrayDelete($aLessColmns, $i) EndIf Next $aLessColmns[0][0] = UBound($aLessColmns) - 1 ; update the index count for future reference _ArrayDisplay($aLessColmns, "Column names from tORiginal less one") ; show the new array $sNewColNames = _ArrayToString($aLessColmns, "|", 1, -1, ",") ; entire array MsgBox(0, "$sNewColNames", $sNewColNames) ConsoleWrite("New Column names " & $sNewColNames & @CRLF) ; now create a transient table, insert values, and rename _SQLite_Exec($mySQLdb, "Create table tNewColumns (" & $sNewColNames & ", number text not null default 'Contact Number') ; " & _ "Insert into tNewColumns (" & $sNewColNames & ") select " & $sNewColNames & " from tORiginal " & _ " ; ") $query = " SELECT * FROM tNewColumns " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & @ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Results of new table") ; show the results of insert into SQL table ; https://www.sqlite.org/pragma.html#pragma_table_info $query = " pragma table_info(tORiginal) " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Pragma Result: altered Original Table") ; show the results of pragma ConsoleWrite("SQL PRAGMA to find all columns " & @CRLF) ; now drop tORiginal ; https://www.sqlite.org/lang_altertable.html ; ALTER TABLE tNewColumns RENAME TO tORiginal ConsoleWrite("_SQLite_Shutdown " & @CRLF) _SQLite_Shutdown() FileDelete($mySQLdb) I believe it is suitable to post on this Forum, as it serves no purpose anywhere else. I have searched, and except for some ancient threads referring to this, could find nothing similar. I used SQLite 3.12.1 which can be downloaded here Precompiled Binaries for Windows. I see they are up to 3.12.2 now. Painfully obvious is of course that one must know the name of the offending column.