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
|
#!/bin/sh
#
# Copyright (C) 2006 Jaroslaw Staniek <[email protected]>
#
# 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 adds a new empty column to a table in a .kexi (SQLite 3)
database file without removing data from the table.
Usage:
$0 database_name table_name new_column_name new_column_type
[new_column_caption]
- {database_name}.old backup file is created before proceeding
- database_name and table_name must exist
- new_column_name must not exist and should be valid idetifier
- new_column_type must be one of:
Byte, ShortInteger, Integer, BigInteger, Boolean, Date, DateTime, Time,
Float, Double, Text, LongText, BLOB (for images)
- new_column_caption can be any text; enclose it in \" \" if you want to use
spaces there
Example: to append a column 'photo' of type BLOB to the table 'cars', type
$0 db.kexi cars photo BLOB Photo"
}
exit_with_error {
rm -f "$temp_db"
echo $*
echo "Error."
exit 1
}
check {
[ -n "$*" ] && exit_with_error "$*"
}
ksqlite="ksqlite -noheader"
if [ $# -lt 4 ] ; then
usage
exit 0
fi
database_name=$1
table_name=$2
new_column_name=$3
new_column_type=$4
new_column_caption=$5
# get numeric value for the data type
case $new_column_type in
Byte) typenum=1;;
ShortInteger) typenum=2;;
Integer) typenum=3;;
BigInteger) typenum=4;;
Boolean) typenum=5;;
Date) typenum=6;;
DateTime) typenum=7;;
Time) typenum=8;;
Float) typenum=9;;
Double) typenum=10;;
Text) typenum=11;;
LongText) typenum=12;;
BLOB) typenum=13;;
*) echo "Unknown type name '$new_column_type'"; exit 1;;
esac
temp_db=`mktemp "$database_name"XXXXXXXX` || exit_with_error
cp "$database_name" "$temp_db" || exit_with_error
msg=`echo "DROP TABLE '$table_name';" | $ksqlite "$temp_db"`
check "$msg"
# 1. Recreate table with new field appended
msg=`echo ".schema '$table_name';" | $ksqlite "$database_name" | grep "^CREATE TABLE $table_name " | \
sed -e "s/);/, $new_column_name $new_column_type);/g" | $ksqlite "$temp_db"`
check "$msg"
# 2.1. Get table's ID
table_id=`echo "SELECT o_id FROM kexi__objects WHERE o_type=1 AND o_name='$table_name';" | \
$ksqlite "$temp_db" || exit_with_error`
# 2.2. Get the new field's order
order=`echo "SELECT MAX(f_order)+1 FROM kexi__fields WHERE t_id=$table_id;" | $ksqlite "$temp_db" || exit_with_error`
# 2.3. Add the new column information to kexi__fields metadata table
msg=`echo "INSERT INTO kexi__fields (t_id, f_type, f_name, f_length, f_precision, f_constraints, \
f_options, f_default, f_order, f_caption, f_help) \
VALUES ($table_id, $typenum, '$new_column_name', \
0, 0, 0, 0, NULL, $order, '$new_column_caption', NULL);" | $ksqlite "$temp_db"`
check "$msg"
# 3. Copy the old data
msg=`echo ".dump '$table_name';" | $ksqlite "$database_name" | grep -v "^CREATE TABLE " | \
sed -e "s/\(^INSERT.*\));$/\\1, NULL);/g" | $ksqlite "$temp_db"`
check "$msg"
# 4. 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
|
