Changeset 4990
- Timestamp:
- May 31, 2017, 12:13:05 PM (4 years ago)
- Location:
- lliurex-analytics-server/trunk/fuentes
- Files:
-
- 5 edited
-
debian/changelog (modified) (1 diff)
-
debian/postinst (modified) (5 diffs)
-
lliurex-analytics-server/usr/lib/analytics-server/analytics-0_1_3-to-0_1_4.sql (modified) (5 diffs)
-
lliurex-analytics-server/usr/lib/analytics-server/analytics/config.php (modified) (2 diffs)
-
lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
lliurex-analytics-server/trunk/fuentes/debian/changelog
r4868 r4990 1 lliurex-analytics-server (0.1.6) xenial; urgency=medium 2 3 * Sql optimizations 4 * Better postinstallation script 5 6 -- M.Angel Juan <m.angel.juan@gmail.com> Tue, 30 May 2017 18:40:42 +0200 7 1 8 lliurex-analytics-server (0.1.5) xenial; urgency=medium 2 9 -
lliurex-analytics-server/trunk/fuentes/debian/postinst
r4859 r4990 52 52 PASS=$(cat /etc/lliurex-analytics-server/config_db|perl -ne '/pass=(.*)/i && print $1') 53 53 if [ -n "${USER}" -a -n "${PASS}" ]; then 54 RES=$( echo 'show tables;'|mysql -u${USER} -p${PASS} -s analytics2>/dev/null || true)54 RES=$(mysql -u${USER} -p${PASS} -N -s -D analytics -e "show tables" 2>/dev/null || true) 55 55 if [ -n "${RES}" ]; then 56 56 user="${USER}" … … 92 92 pass=$input 93 93 fi 94 do_dialog 'Database configuration' 'Password root de mysql?' 95 rootpass=$input 94 ask_root="yes" 95 if [ -f /etc/mysql/debian.cnf ]; then 96 root_test=$(mysql -u root -s -N -e "show databases" 2>/dev/null || true) 97 if [ -z "$root_test" ]; then 98 rootuser=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/user[ \t]*=[ \t]*(.*)/i && print $1') 99 rootpass=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/password[ \t]*=[ \t]*(.*)/i && print $1') 100 if [ -n "$root_user" -a -n "$root_pass" ]; then 101 root_test=$(mysql -u $rootuser -p$rootpass -s -N -e "show databases" 2>/dev/null || true) 102 if [ -z "$root_test" ]; then 103 ask_root="no" 104 rootuser="-u$rootuser" 105 rootpass="-p$rootpass" 106 fi 107 fi 108 else 109 ask_root="no" 110 rootuser="-uroot" 111 rootpass="" 112 fi 113 fi 114 if [ "x$ask_root" = "xyes" ]; then 115 do_dialog 'Database configuration' 'Mysql root user? (empty is root)' 116 rootuser=$input 117 do_dialog 'Database configuration' 'Password root de mysql?' 118 rootpass=$input 119 root_test=$(mysql -u $rootuser -p$rootpass -s -N -e "show databases" 2>/dev/null || true) 120 if [ -z "$root_test" ]; then 121 echo "Wrong password, leaving all unconfigured and aborting...." 122 exit 0 123 else 124 if [ -z "$rootuser" ]; then 125 rootuser="root" 126 fi 127 rootuser="-u$rootuser" 128 if [ -z "$rootpass" ];then 129 rootpass="" 130 else 131 rootpass="-p$rootpass" 132 fi 133 fi 134 fi 96 135 else 97 136 print_info="yes" … … 106 145 107 146 # UPDATE DB FROM OLDER VERSIONS 147 tables=$(mysql $rootuser $rootpass -s -N -D analytics -e "show tables" 2>/dev/null|grep historico_clients) 148 keys=$(mysqldump $rootuser $rootpass analytics|egrep '^[[:space:]]*KEY'|wc -l) 108 149 dumpfile="/usr/lib/analytics-server/analytics-0_1_3-to-0_1_4.sql" 109 150 … … 115 156 need_update_to_0_1_4=no 116 157 dpkg --compare-versions "$older_version" lt "$target_update" && need_update_to_0_1_4=yes 158 if [ -z "$tables" -o $keys -ne 12 ]; then 159 need_update_to_0_1_4=yes 160 echo "failed database test for table historico_clients or keys" 161 fi 117 162 if [ "x$need_update_to_0_1_4" = "xyes" ]; then 118 echo Updating to database version 0.1.4 119 mysql < $dumpfile 163 echo Updating to database version upper than 0.1.4 164 mysqldump $rootuser $rootpass --no-create-info analytics > /tmp/__analytics_tmp__ 165 mysql $rootuser $rootpass -s -N -e "drop database analytics" 166 mysql $rootuser $rootpass < $dumpfile 167 mysql $rootuser $rootpass analytics < /tmp/__analytics_tmp__ 168 else 169 echo "Seems that database is newer than 0.1.4, skipping database migration" 120 170 fi 121 171 … … 124 174 125 175 if [ "x$create_db" = "xyes" ]; then 126 if [ "x$rootpass" = "x" ]; then127 mysqlroot="mysql -uroot"128 mysqluser="mysql -u$user -p$pass"129 else130 mysqlroot="mysql -uroot -p$rootpass"131 mysqluser="mysql -u$user -p$pass"132 fi133 176 if [ "x$create_user" = "xyes" ]; then 134 echo "create user '$user'@'%' identified by '$pass';" | $mysqlroot135 echo "grant all privileges on analytics.* to '$user'@'%';" | $mysqlroot136 echo "flush privileges;" | $mysqlroot177 mysql $rootuser $rootpass -s -N -e "create user '$user'@'%' identified by '$pass';" 178 mysql $rootuser $rootpass -s -N -e "grant all privileges on analytics.* to '$user'@'%';" 179 mysql $rootuser $rootpass -s -N -e "flush privileges;" 137 180 echo "user=$user" > /etc/lliurex-analytics-server/config_db 138 181 echo "pass=$pass" >> /etc/lliurex-analytics-server/config_db 139 182 chmod 750 /etc/lliurex-analytics-server/config_db 140 183 fi 141 $mysqluser< $dumpfile184 mysql $rootuser $rootpass < $dumpfile 142 185 else 143 186 if [ "x$create_user" != "xyes" -a "${ask_user}" != "no" ]; then -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics-0_1_3-to-0_1_4.sql
r4854 r4990 1 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 1 2 SET AUTOCOMMIT = 0; 2 3 START TRANSACTION; … … 6 7 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 7 8 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 8 /*!40101 SET NAMES utf8 mb4*/;9 /*!40101 SET NAMES utf8 */; 9 10 10 11 CREATE DATABASE IF NOT EXISTS `analytics` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; … … 34 35 `version` char(20) NOT NULL, 35 36 `sabor` char(50) NOT NULL, 36 PRIMARY KEY (`id`) 37 PRIMARY KEY (`id`), 38 KEY `fecha` (`fecha`), 39 KEY `version_sabor` (`version`,`sabor`), 40 KEY `app` (`app`), 41 KEY `sabor` (`sabor`), 42 KEY `version` (`version`), 43 KEY `app_count` (`app`,`count`) 37 44 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 38 45 … … 44 51 `nclients` int(11) NOT NULL, 45 52 PRIMARY KEY (`id`) 46 ) ENGINE=InnoDB 53 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 47 54 48 55 CREATE TABLE IF NOT EXISTS `packages` ( … … 53 60 `sabor` char(50) NOT NULL, 54 61 PRIMARY KEY (`id`), 55 UNIQUE KEY `app` (`app`,`version`,`sabor`) 62 KEY `app` (`app`), 63 KEY `version_sabor` (`version`,`sabor`), 64 KEY `sabor` (`sabor`), 65 KEY `version` (`version`), 66 KEY `app_count` (`app`,`count`), 67 KEY `app_version_sabor` (`app`,`version`,`sabor`) 56 68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 57 69 COMMIT; -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/config.php
r4854 r4990 14 14 { 15 15 "name": "15", 16 "like": "LIKE \' %15.05%\'",16 "like": "LIKE \'15.05%\'", 17 17 "sabor": [ 18 18 { … … 32 32 { 33 33 "name": "16", 34 "like": "LIKE \' %16%\'",34 "like": "LIKE \'16%\'", 35 35 "sabor": [ 36 36 { -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php
r4860 r4990 102 102 if ($thismonth > $lastmonth){ 103 103 $date_to_history=$this->dates['last_one_month_ago']; 104 $sql="insert into historico(app,count,fecha,version,sabor) (select app,count,'$date_to_history' as fecha,version,sabor from packages );";104 $sql="insert into historico(app,count,fecha,version,sabor) (select app,count,'$date_to_history' as fecha,version,sabor from packages order by count desc limit 100);"; 105 105 $this->dbconn->query($sql); 106 106 $sql="truncate packages;"; 107 $this->dbconn->query($sql); 108 $delete_before=$this->dates['first_two_month_ago']; 109 $sql="delete from historico where app != 'dummy' and fecha < '$delete_before'"; 107 110 $this->dbconn->query($sql); 108 111
Note: See TracChangeset
for help on using the changeset viewer.
