1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
#!/bin/sh
#
# Copyright (C) 2006-2007 Jaroslaw Staniek <js@iidea.pl>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; see the file COPYING. If not, write to
# the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
# Boston, MA 02110-1301, USA.
#
usage {
echo "This script deletes a single table column from a .kexi (SQLite 3)
database file without removing data from the table.
Usage:
$0 database_name table_name column_name
- {database_name}.old backup file is created before proceeding
- database_name, table_name, column_name must exist
- note that queries, forms and other objects referencing
to the altered table can become invalid and have to be
fixed by hand
Example: to delete 'price' column from table 'products', type
$0 db.kexi products price"
}
exit_with_error {
rm -f "$temp_db"
echo $*
echo "Error."
exit 1
}
check {
[ -n "$*" ] && exit_with_error "$*"
}
ksqlite="ksqlite -noheader"
ksqlite_header="ksqlite -header"
if [ $# -lt 3 ] ; then
usage
exit 0
fi
database_name=$1
table_name=$2
column_name=$3
temp_db=`mktemp "$database_name"XXXXXXXX` || exit_with_error
cp "$database_name" "$temp_db" || exit_with_error
# 1. alter the table physically
prepare_new_create_table_statement {
# possible problems: typename ( number , number ) may contain ","
schema=`echo ".schema '$table_name';" | $ksqlite "$database_name" | \
grep "^CREATE TABLE $table_name " | \
sed -e "s/[^(]*(\(.*\));/\1/" || exit_with_error`
IFS=","
for coldef in $schema ; do
col=`echo $coldef | sed "s/^[ ]*\([^ ]*\) .*$/\1/"`
if [ "$col" != "$column_name" ] ; then
echo -n ,$coldef
fi
done | cut -c2-
IFS=" "
}
get_sql_column_names {
names=`$ksqlite_header "$temp_db" "SELECT * FROM '$temp_table_name' LIMIT 1;" | \
head -n 1 || exit_with_error`
IFS="|"
for col in $names ; do
if [ "$col" != "$column_name" ] ; then
echo -n ", $col"
fi
done | cut -c3-
IFS=" "
}
# 1.1. rename the original table to a temp name
temp_table_name=`mktemp "$table_name"XXXXXXXX`
msg=`$ksqlite "$temp_db" "ALTER TABLE '$table_name' RENAME TO '$temp_table_name';"`
check "$msg"
# 1.2. create a new table without the removed column and copy the data
new_create_table_statement=`prepare_new_create_table_statement`
msg=`$ksqlite "$temp_db" "CREATE TABLE '$table_name' ($new_create_table_statement);"`
check "$msg"
sql_column_names=`get_sql_column_names`
msg=`$ksqlite "$temp_db" "INSERT INTO '$table_name' SELECT $sql_column_names FROM '$temp_table_name';"`
check "$msg"
# 1.3. drop the temporary table
msg=`$ksqlite "$temp_db" "DROP TABLE '$temp_table_name';"`
check "$msg"
# 2. alter information in the kexi__fields system table (schema)
# 2.1. Get table's ID
table_id=`$ksqlite "$temp_db" "SELECT o_id FROM kexi__objects WHERE o_type=1 AND o_name='$table_name';" || exit_with_error`
# 2.1. Get column's number
column_order=`$ksqlite "$temp_db" "SELECT f_order FROM kexi__fields WHERE t_id=$table_id AND f_name='$column_name';" || exit_with_error`
$ksqlite "$temp_db" "DELETE FROM kexi__fields WHERE t_id=$table_id AND f_name='$column_name';"
for fname in `$ksqlite "$temp_db" \
"SELECT f_name FROM kexi__fields WHERE t_id=$table_id AND f_order>=$column_order ORDER BY f_order DESC;"` ; do
msg=`$ksqlite "$temp_db" "UPDATE kexi__fields SET f_order=$column_order WHERE t_id=$table_id AND f_name='$fname';"`
check "$msg"
column_order=`expr $column_order + 1`
done
# 3. Copy the original database file to .old file and replace
# the original with the new one
cp "$database_name" "$database_name.old" || exit_with_error
mv "$temp_db" "$database_name" || exit_with_error
exit 1
|
