' return a delimited string from a list
private Sub Join(Delimiter, lst As List) As String
Dim lStr As StringBuilder
lStr.Initialize
For Each strValue As String In lst
lStr.Append(strValue).Append(Delimiter)
Next
Return RemDelim(Delimiter,lStr.tostring)
End Sub
'remove a delimiter from a string
private Sub RemDelim(delimiter As String, value As String) As String
If value.EndsWith(delimiter) = True Then
Dim delimLen As Int = delimiter.length
Dim sb As StringBuilder
sb.Initialize
sb.Append(value)
sb.Remove(sb.Length-delimLen,sb.Length)
Return sb.tostring
Else
Return value
End If
End Sub
'get all column names from a table
private Sub GetTableColumnNames(sql As SQL, tblName As String) As List
Dim strFld As String
Dim curFields As List
Dim cur As ResultSet
curFields.Initialize
cur = sql.ExecQuery("PRAGMA table_info ('" & tblName & "')")
Do While cur.NextRow
strFld = cur.GetString("name")
curFields.Add(strFld)
Loop
cur.close
Return curFields
End Sub
'gets the existance of a column from a table
private Sub ColumnExists(sql As SQL, tblName As String, colName As String) As Boolean
Dim lst As List = GetTableColumnNames(sql,tblName)
If lst.IndexOf(colName) = -1 Then
Return False
Else
Return True
End If
End Sub
'remove unwanted characters from sql command
Private Sub CleanSQL(sValue As String) As String
Dim sb As StringBuilder
Dim tCnt As Int= 0
Dim tTot As Int = sValue.length - 1
Dim sIt As String
Dim sTo As String = "01234567890)[abcdefghijklmnopqrstuvwxyz_,]("
sb.Initialize
For tCnt = 0 To tTot
sIt = sValue.SubString2(tCnt,tCnt+1)
Select Case sIt
Case " "
sb.Append(sIt)
Case Else
If sTo.IndexOf(sIt.ToLowerCase) >= 0 Then
sb.Append(sIt)
End If
End Select
Next
Return sb.tostring
End Sub
'remove a column from sqlite table, the fldname to remove is case sensitive
Sub RemoveColumn(sql As SQL, TableName As String, FldName As String) As Boolean
Dim isremoved As Boolean = False
sql.BeginTransaction
Try
Dim newFields As List
'get the current table columns
Dim curFields As List = GetTableColumnNames(sql,TableName)
'remove the column to be removed from the list and define new column names
Dim newFields As List
newFields.Initialize
For Each strColumn As String In curFields
If strColumn.EqualsIgnoreCase(FldName) = False Then newFields.Add(strColumn)
Next
' define the new fields to use in new table
Dim newFieldsS As String = Join(",", newFields)
'get the sql that was used to create the original table
Dim sqlS As String = sql.ExecQuerySingleResult("SELECT sql from sqlite_master where name = '" & TableName & "'")
'clean the sql command to create the table
sqlS = CleanSQL(sqlS)
'establish splitting locations
sqlS = sqlS.Replace(",",",~")
'ensure the tabs are cleaned out
sqlS = sqlS.Replace(TAB,"~")
sqlS = sqlS.Replace(CRLF,"~")
Dim spCode() As String = Regex.Split("~",sqlS)
Dim sb As StringBuilder
sb.Initialize
For Each strLine As String In spCode
' see if line starts with column not needed
If strLine.StartsWith("[" & FldName & "] ") = True Then
' do nothing
else if strLine.StartsWith(FldName & " ") = True Then
' do nothing, this is precaution
else If strLine.StartsWith(" [" & FldName & "] ") = True Then
' do nothing
Else
' this should be used on a new table
sb.Append(strLine)
End If
Next
'now rename the original table, we will copy records across
sql.ExecNonQuery("ALTER TABLE " & TableName & " RENAME TO " & TableName & "_old")
'create a new table with updated fields
Dim sCommand As String = sb.ToString.trim
If sCommand.EndsWith(",") = True Then
sCommand = RemDelim(",",sCommand)
sCommand = sCommand & ")"
End If
sql.ExecNonQuery(sCommand)
'copy records to new table from renamed table
sql.ExecNonQuery("INSERT INTO " & TableName & "(" & newFieldsS & ") SELECT " & newFieldsS & " FROM " & TableName & "_old")
' drop the temporal table created
sql.ExecNonQuery("DROP TABLE " & TableName & "_old")
isremoved = Not(ColumnExists(sql, TableName, FldName))
sql.TransactionSuccessful
Catch
sql.Rollback
Log(LastException)
End Try
Return isremoved
End Sub
