Generating complex SQL queries with shell scripts
It is sometimes necessary to use complex select and insert queries with dozens of variables. Writing such queries by hand is a tedious process and the chance of making a mistake is high. A few lines of shell script code can make this task manageble. The following example uses a MySQL database and Korn shell under Solaris 10 running on a SPARC platform.
First, we need to get into the database and get the list of colums for the particular table we are working with. In this example the database name is “sysinfo” and the table name is “security_tbl”.
mysql -uUSER -pPASSWORD DATABASE mysql> show tables; +-------------------+ | Tables_in_sysinfo | +-------------------+ | applications_tbl | | dbdesigner4 | | dr_reports_tbl | | security_tbl | | support_matrix | | sysinfo_tbl | mysql> describe security_tbl; +---------------------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+------------------+------+-----+---------------------+----------------+ | server_security_id | int(10) unsigned | | PRI | NULL | auto_increment | | server_short_name | varchar(255) | | | | | | server_dns_domain | varchar(255) | | | | | | last_updated_datetime | datetime | YES | | NULL | | | os_name | varchar(45) | | | | | | os_version | varchar(45) | | | | | | syslogd_status | varchar(45) | YES | | NULL | | | etc_utmp | varchar(45) | YES | | NULL | | | etc_utmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | etc_utmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_wtmp | varchar(45) | YES | | NULL | | | var_adm_wtmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_wtmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_btmp | varchar(45) | YES | | NULL | | | var_adm_btmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_btmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_sulog | varchar(45) | YES | | NULL | | | var_adm_sulog_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_sulog_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_syslog_log | varchar(45) | YES | | NULL | | | var_adm_syslog_log_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_syslog_log_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_messages | varchar(45) | YES | | NULL | | | var_adm_messages_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_messages_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_syslog | varchar(45) | YES | | NULL | | | var_log_syslog_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_syslog_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_utmpx | varchar(45) | YES | | NULL | | | var_adm_utmpx_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_utmpx_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_wtmpx | varchar(45) | YES | | NULL | | | var_adm_wtmpx_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_wtmpx_a | datetime | YES | | 0000-00-00 00:00:00 | | | etc_securityfailedlogin | varchar(45) | YES | | NULL | | | etc_securityfailedlogin_m | datetime | YES | | 0000-00-00 00:00:00 | | | etc_securityfailedlogin_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_messages | varchar(45) | YES | | NULL | | | var_log_messages_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_messages_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_run_utmp | varchar(45) | YES | | NULL | | | var_run_utmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_run_utmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_wtmp | varchar(45) | YES | | NULL | | | var_log_wtmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_wtmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_btmp | varchar(45) | YES | | NULL | | | var_log_btmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_btmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_mailog | varchar(45) | YES | | NULL | | | var_log_mailog_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_log_mailog_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_utmp | varchar(45) | YES | | NULL | | | var_adm_utmp_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_utmp_a | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_SYSLOG | varchar(45) | YES | | NULL | | | var_adm_SYSLOG_m | datetime | YES | | 0000-00-00 00:00:00 | | | var_adm_SYSLOG_a | datetime | YES | | 0000-00-00 00:00:00 | | | etc_default_su | varchar(45) | YES | | NULL | | | etc_default_su_m | datetime | YES | | 0000-00-00 00:00:00 | | | etc_default_su_a | datetime | YES | | 0000-00-00 00:00:00 | | | compliance | varchar(45) | YES | | NULL | | +---------------------------+------------------+------+-----+---------------------+----------------+
The list above contains the names of all columns in the security_tbl table. You can redirect this into a file or just highlight, copy, and paste it into a temporary file: /tmp/list1
Let’s format this output so we only have column names and none of the other information:
cat /tmp/list1 | awk '{print $2}' > /tmp/list2
Suppose you are doing an insert query. You have variables with the same names as the column names in your table. This is always a good idea as it saves you time. You query should look something like this:
mysql -u$USER -p$PASS $DBNAME << EOF INSERT INTO security_tbl(server_short_name,server_dns_domain,...) values('$server_short_name','$server_dns_domain',...) EOF
The problem here is the “…” stuff – there’s a lot of it. But with a simple shell script you can generate all of it with no problems:
string="" cat /tmp/list2 | while read line do string=$(echo "${string},${line}") done string2="" cat /tmp/list2 | while read line do string2=$(echo "${string2}','\$${line}") done
Just make sure to edit the beginning and the end of each string to remove unneeded characters.
If you are writing an update query, it would look something like this:
mysql -u$USER -p$PASS $DBNAME << EOF UPDATE security_tbl SET server_short_name='$server_short_name', ... WHERE server_short_name='$server_short_name' AND server_dns_domain='$server_dns_domain'; EOF
Again, the problem is the “…”. If you try to type each line by hand, you are likely to make an error that will drive you crazy later on. A shell script can help you:
cat /tmp/list2 | while read line do echo "${line}='\$${line}'," >> /tmp/list3 done
Now all you have to do is open the /tmp/list3 file, remove the extra comma at the end of the file, and paste everything into your update query. Don’t forget to remove tht extra comma though.
Popularity: 2% [?]
Related posts:
