SQLite – GUI Column management

There are some functions / subroutines in the executable’s System macros Panels which are intended to provide functionality that I have regularly needed, and that does not fit to a single SQLite command. There are also some built-in commands that send queries to the SQLite database.

This page describes some of the built-in commands and embedded functions / subroutines that are available, and that are specifically aimed at the columns of a table.


Removing columns from a table

In SQLite there is no single command to remove a column from a table.

In this executable there are three functions to remove column(s) from a table, they are all in the System macros Panel.

The functional specifications are:

function dbColumnDropbyFilter ( optional schema = "main", tbl, ColumnSpec, optional exactmatch = 1)

Find the first column that fully matches the text in the ColumnSpec value, then delete that column. 

If you want to apply this action to all columns containing the text in the  ColumnSpec value then the fourth parameter exactmatch must be zero 0.

This command is case-insensitive!

——————-

function dbColumnDropTrailing ( optional schema = "main", tbl, ColumnSpec, optional exactmatch = 1)

Find the first column that fully matches the text in the ColumnSpec value. Delete all columns  starting after the found column to the end of the column list.

If you want to apply this action to the first column containing the text in the  ColumnSpec value then the fourth parameter exactmatch must be zero 0.

——————-

function dbColumnDropLeading ( optional schema = "main", tbl, ColumnSpec, optional exactmatch = 0)

Find the first column that fully matches the text in the ColumnSpec value. Delete all columns up to and including the found column.

If you want to apply this action to the first column containing the text in the  ColumnSpec value then the fourth parameter exactmatch must be zero 0.


dbColumnList

Use this to create a string containing the names of all of the columns in a given table, optionally provide a text string to be inserted before each column name, and optionally a text string to be inserted after each column name. The column names will enclosed in a pair of " and any embedded " will be doubled-up.

function dbColumnList ( optional mySchema ="main" , myTable, optional ColumnFilter = "", optional ExactMatch = 1 , optional before = " " , optional after = " ," )
' create a list of the columns in a given table.
' Optionally filter out some column names
' Optional provide a leading and trailing string around the column name
dim query = "pragma " & mySchema & ".table_info(" & sqlString(myTable ) & ")"
If Runon sql ( query ) then usererror ( "Invalid database parameters")
dbColumnList = ""
' WQText(1..6) = cid , name , type , notnull , dflt_value , pk
withquery ( query )
IF ExactMatch and (wqText(2) == ColumnFilter ) THEN
ELSEIF ! ExactMatch and instr ( wqText(2) , columnFilter) then
ELSE
if ! ( dbColumnList == "" ) then dbColumnList = dbColumnList & after
dbColumnList = dbColumnList & before & sqlIdentifier ( WQtext(2) )
END IF
end withquery
end function

So if I execute this function on my standard demo database table family:

message = dbColumnList ( “main” , “family”  )

the result is: