Jump to content
Skysnake

SQLite Alter Table Drop Column (with some AutoIt magic)

Recommended Posts

Skysnake
#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. :)

 

Edited by Skysnake
2016.06.04 Improved

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening everyone
      I am building a management for the company I work with, and I just imported a real amount of rows ( about 29000 ), in my SQLite DB.
      The thing I am not understanding, is the time that the script takes to build this amount of rows in the ListView.
      I didn't measure it, but I think it took 2 minutes or so to create each ListView item...
      It is normal that it takes so much time?
      What can I do to improve the creation of the items?

      Here's the code I am using to query and to create ListView items...
      ; Articles ListView: Global $lvwArticles = GUICtrlCreateListView("ID|Fornitore|Codice|Descrizione|EU|Prezzo|Sconto Applicato|Note", 14, 87, 1507, 660, BitOR($GUI_SS_DEFAULT_LISTVIEW,$LVS_SORTASCENDING,$LVS_SORTDESCENDING), BitOR($WS_EX_CLIENTEDGE,$LVS_EX_GRIDLINES,$LVS_EX_FULLROWSELECT)) ; Query $strQuery = "SELECT * FROM ARTICOLI;" ; Query Execution _SQLite_GetTable2d($objDatabase, $strQuery, $arrResult, $intRows, $intColumns) If @error Then ; Error Handling Else ; Cleaning the ListView _GUICtrlListView_DeleteAllItems($lvwArticles) If @error Then ; Error Handling Else ; No records in the Table If UBound($arrResult) < 2 Then ; Error Handling Else _GUICtrlListView_BeginUpdate($lvwArticles) For $intCounter = 1 To UBound($arrResult) - 1 $strListViewItem = $arrResult[$intCounter][0] & "|" & _ $arrResult[$intCounter][1] & "|" & _ $arrResult[$intCounter][2] & "|" & _ $arrResult[$intCounter][3] & "|" & _ $arrResult[$intCounter][4] & "|" & _ $arrResult[$intCounter][5] & "|" & _ $arrResult[$intCounter][6] & "|" & _ $arrResult[$intCounter][7] $objListViewItem = GUICtrlCreateListViewItem($strListViewItem, $lvwArticles) Next _GUICtrlListView_EndUpdate($lvwArticles) EndIf EndIf EndIf Thanks in advance


      Best Regards.
    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Eminence
      By Eminence
      Hello,
      I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.
      This is my current code:
      Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.
       
      *EDIT
      The date format in the database is by MM/DD/YYYY HH:MM:SS.
    • Seminko
      By Seminko
      In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll
      Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one.
      I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL.
      So I downloaded @jchd's SQLiteExtLoad.au3 as seen here:
      But I'm getting these errors:
      "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires
      If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection.
      I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt.
      Thanks, S.
       
      EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply.
      BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem...
       
      EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly

    • AdamUL
      By AdamUL
      I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples.  The examples that I have tested are throwing errors or not doing anything.  I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script.  I am copying the examples directly from the help file into a test script for testing with no edits.  The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command.  The _SQLite_FastEncode example returns an empty dialog box.  The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console.  Currently, I'm still searching for what is causing this issue.  I'm on Windows 7 Enterprise 64-bit.  Is anyone else having this issue?  
       
      Adam
       
×