-
Notifications
You must be signed in to change notification settings - Fork 0
/
csv.sh
executable file
·89 lines (71 loc) · 6.24 KB
/
csv.sh
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
#!/bin/bash
# Update these variables with your MySQL credentials
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_HOST="localhost"
DATABASE_NAME="sbtest"
#@echo "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ', GROUP_CONCAT('MODIFY ', COLUMN_NAME, ' ', COLUMN_TYPE, ' NOT NULL DEFAULT ', COALESCE(QUOTE(COLUMN_DEFAULT), CASE DATA_TYPE >"
DEFAULT_DATE="2010-01-01 00:00:00"
# Run the SQL query
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SELECT CONCAT('UPDATE \`', TABLE_NAME, '\` SET \`', COLUMN_NAME, '\` = ''${DEFAULT_DATE}'' WHERE \`', COLUMN_NAME, '\` IS NULL;') AS update_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES'" \
| while read -r update_query; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$update_query"
done
DEFAULT_DATE="0"
# Run the SQL query
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SELECT CONCAT('UPDATE \`', TABLE_NAME, '\` SET \`', COLUMN_NAME, '\` = ''${DEFAULT_DATE}'' WHERE \`', COLUMN_NAME, '\` IS NULL;') AS update_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES'" \
| while read -r update_query; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$update_query"
done
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SHOW TABLES" | while read -r table_name; do
# Drop foreign keys
#mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
#"SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND TABLE_NAME = '${table_name}' AND REFERENCED_TABLE_NAME IS N>"
#| while read -r fk_name; do
# mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "ALTER TABLE ${table_name} DROP FOREIGN KEY ${fk_name};"
#done
# Drop foreign keys
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND TABLE_NAME = '${table_name}' AND REFERENCED_TABLE_NAME IS NOT NULL;" \
| while read -r fk_name; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "ALTER TABLE ${table_name} DROP FOREIGN KEY ${fk_name};"
done
# Drop primary keys
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"ALTER TABLE ${table_name} DROP PRIMARY KEY;" 2>/dev/null
# Drop foreign keys
#mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
#"SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND TABLE_NAME = '${table_name}' AND REFERENCED_TABLE_NAME IS NOT NULL;" \
#| while read -r fk_name; do
# mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "ALTER TABLE ${table_name} DROP FOREIGN KEY ${fk_name};"
#done
# Drop index keys
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SHOW INDEX FROM ${table_name} WHERE Key_name != 'PRIMARY';" | awk '{print $3}' | sort -u \
| while read -r index_name; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "ALTER TABLE ${table_name} DROP INDEX ${index_name};"
done
done
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` MODIFY \`', COLUMN_NAME, '\` VARCHAR(15000) CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME, ';') AS alter_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND DATA_TYPE = 'text'" \
| while read -r alter_query; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$alter_query"
done
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
"SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ', GROUP_CONCAT('MODIFY ', COLUMN_NAME, ' ', COLUMN_TYPE, ' NOT NULL DEFAULT ', COALESCE(COLUMN_DEFAULT, CASE DATA_TYPE WHEN 'varchar' THEN '\"\"' WHEN 'text' THEN '\"\"' WHEN 'char' THEN '\"\"' WHEN 'int' THEN '0' WHEN 'bigint' THEN '0' WHEN 'tinyint' THEN '0' WHEN 'smallint' THEN '0' WHEN 'decimal' THEN '0' WHEN 'float' THEN '0.0' WHEN 'date' THEN '\"2010-01-01\"' WHEN 'time' THEN '\"00:00:00\"' WHEN 'datetime' THEN '\"2010-01-01 00:00:00\"' WHEN 'timestamp' THEN '\"2010-01-01 00:00:01\"' ELSE '\"\"' END) SEPARATOR ', '), ', ENGINE = CSV;') AS alter_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND IS_NULLABLE = 'YES' GROUP BY TABLE_NAME" \
| while read -r alter_query; do
echo $alter_query
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$alter_query"
done
# Run the SQL query
# mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
# "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ', GROUP_CONCAT('MODIFY ', COLUMN_NAME, ' ', COLUMN_TYPE, ' NOT NULL DEFAULT ', IF(COLUMN_DEFAULT IS NULL, COALESCE(QUOTE(COLUMN_DEFAULT), '\"\"'), '\"\"') SEPARATOR ', '), ', ENGINE = CSV;') AS alter_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND IS_NULLABLE = 'YES' GROUP BY TABLE_NAME" \
#| while read -r alter_query; do
# mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$alter_query"
#mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -Bse \
#"SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ', GROUP_CONCAT('MODIFY ', COLUMN_NAME, ' ', COLUMN_TYPE, ' NOT NULL DEFAULT ', COALESCE(QUOTE(COLUMN_DEFAULT), CASE DATA_TYPE WHEN 'date' THEN '1000-01-01' WHEN 'time' THEN '00:00:00' WHEN 'datetime' THEN '1000-01-01 00:00:00' WHEN 'timestamp' THEN '1970-01-01 00:00:01' ELSE '\"\"' END) SEPARATOR ', '), ', ENGINE = CSV;') AS alter_query FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '${DATABASE_NAME}' AND IS_NULLABLE = 'YES' GROUP BY TABLE_NAME" \
#| while read -r alter_query; do
# mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DATABASE_NAME" -e "$alter_query"