Tcl_script_reindex

Definition

Language:	Tcl
Plugin for language:	ScriptingTcl
How to use:	Create custom SQL function. Type: Scalar. Suggested name: resequence
Function arguments	table, column
Function usage:	SELECT resequence('tableName', 'idColumnName');
Description:	For table column that has sequence of ID numbers, but with gaps (due to deletions, etc), this function re-sequences values, so the numbers are subsequent and do not have gaps. It automatically detects any foreign tables referencing to this updated column and updated foreign column values too. As result returns how many IDs were resequenced and in which tables did it update them.

Code

lassign $argv table column
set currentIds [db eval "SELECT `$column` FROM `$table`"]

set fkTablesToUpdate [list]
set tables [db eval "select name from sqlite_master where type = 'table'"]
foreach tab $tables {
  if {$tab == $table} {
    continue
  }
  set fkRows [db rows "pragma foreign_key_list($tab)"]
  foreach row $fkRows {
    lassign $row id deq fkTable localCol fkCol
    if {$fkTable == $table && $fkCol == $column} {
      lappend fkTablesToUpdate [list $tab $localCol]
    }
  }
}


db eval "PRAGMA foreign_keys = false"

set updated [list $table]
set seq 0
foreach id $currentIds {
  db eval "update `$table` set `$column` = $seq where `$column` = $id"
  foreach fkTabCol $fkTablesToUpdate {
    lassign $fkTabCol fkTab fkCol
    db eval "update `$fkTab` set `$fkCol` = $seq where `$fkCol` = $id"
  }
  incr seq
}

db eval "PRAGMA foreign_keys = true"

foreach fkTabCol $fkTablesToUpdate {
  lassign $fkTabCol fkTab fkCol
  lappend updated $fkTab
}

return "Resequenced $seq IDs in tables: [join $updated {, }]"