diff options
Diffstat (limited to 'scripts')
| -rwxr-xr-x | scripts/cron/refresh_stats.php | 55 | ||||
| -rwxr-xr-x | scripts/cron/warning.php | 3 | ||||
| -rwxr-xr-x | scripts/db_migrations/version5.sh | 249 | ||||
| -rw-r--r-- | scripts/gpgfillmissingemail.php | 4 |
4 files changed, 282 insertions, 29 deletions
diff --git a/scripts/cron/refresh_stats.php b/scripts/cron/refresh_stats.php index 2a3d2b5..3b446ba 100755 --- a/scripts/cron/refresh_stats.php +++ b/scripts/cron/refresh_stats.php @@ -23,7 +23,7 @@ require_once(dirname(__FILE__).'/../../includes/mysql.php'); /** * Wrapper around mysql_query() to provide some error handling. Prints an error * message and dies if query fails - * + * * @param string $sql * the SQL statement to execute * @return resource|boolean @@ -35,7 +35,7 @@ function sql_query($sql) { fwrite(STDERR, "MySQL query failed:\n\"$sql\"\n".mysql_error()); die(1); } - + return $res; } @@ -54,7 +54,7 @@ function updateCache($stats) { $sql = "insert into `statscache` (`timestamp`, `cache`) values ('$timestamp', '".mysql_real_escape_string(serialize($stats))."')"; sql_query($sql); - + // Make sure the new statistic was inserted successfully $res = sql_query( "select 1 from `statscache` where `timestamp` = '$timestamp'"); @@ -62,7 +62,7 @@ function updateCache($stats) { fwrite(STDERR, "Error on inserting the new statistic"); return false; } - + sql_query("delete from `statscache` where `timestamp` != '$timestamp'"); return true; } @@ -74,22 +74,22 @@ function updateCache($stats) { */ function getDataFromLive() { echo "Calculating current statistics\n"; - + $stats = array(); $stats['verified_users'] = number_format(tc( "select count(*) as `count` from `users` where `verified` = 1 and `deleted` = 0 and `locked` = 0")); - + $stats['verified_emails'] = number_format(tc( "select count(*) as `count` from `email` where `hash` = '' and `deleted` = 0")); - + $stats['verified_domains'] = number_format(tc( "select count(*) as `count` from `domains` where `hash` = '' and `deleted` = 0")); - + $certs = tc("select count(*) as `count` from `domaincerts` where `expire` != 0"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -101,7 +101,7 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `expire` != 0"); $stats['verified_certificates'] = number_format($certs); - + $certs = tc("select count(*) as `count` from `domaincerts` where `revoked` = 0 and `expire` > NOW()"); $certs += tc("select count(*) as `count` from `emailcerts` @@ -113,11 +113,12 @@ function getDataFromLive() { $certs += tc("select count(*) as `count` from `orgemailcerts` where `revoked` = 0 and `expire` > NOW()"); $stats['valid_certificates'] = number_format($certs); - + $stats['assurances_made'] = number_format(tc( "select count(*) as `count` from `notary` - where `method` = '' or `method` = 'Face to Face Meeting'")); - + where (`method` = '' or `method` = 'Face to Face Meeting') + and `deleted` = 0")); + $stats['users_1to49'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -125,7 +126,7 @@ function getDataFromLive() { group by `to` having sum(`points`) > 0 and sum(`points`) < 50 ) as `low_points`")); - + $stats['users_50to99'] = number_format(tc( "select count(*) as `count` from ( select 1 from `notary` @@ -133,7 +134,7 @@ function getDataFromLive() { group by `to` having sum(`points`) >= 50 and sum(`points`) < 100 ) as `high_points`")); - + $stats['assurer_candidates'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -148,7 +149,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['aussurers_with_test'] = number_format(tc( "select count(*) as `count` from `users` where ( @@ -163,7 +164,7 @@ function getDataFromLive() { and `cv`.`type_id`=1 )" )); - + $stats['points_issued'] = number_format(tc( "select sum(greatest(`points`, `awarded`)) as `count` from `notary` where `deleted` = 0 @@ -177,16 +178,16 @@ function getDataFromLive() { $next_month_ts = mktime(0, 0, 0, date("m") - $i + 1, 1, date("Y")); $first = date("Y-m-d", $first_ts); $next_month = date("Y-m-d", $next_month_ts); - + echo "Calculating statistics for month $first\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_month' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -195,7 +196,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_month' @@ -240,16 +241,16 @@ function getDataFromLive() { $next_year_ts = mktime(0, 0, 0, 1, 1, $i + 1); $first = date("Y-m-d", $first_ts); $next_year = date("Y-m-d", $next_year_ts); - + echo "Calculating statistics for year $i\n"; - + $totalusers += $users = tc( - "select count(*) as `count` from `users` + "select count(*) as `count` from `users` where `created` >= '$first' and `created` < '$next_year' and `verified` = 1 and `deleted` = 0 and `locked` = 0"); - + $totassurers += $assurers = tc( "select count(*) as `count` from ( select 1 from `notary` @@ -258,7 +259,7 @@ function getDataFromLive() { and `deleted` = 0 group by `to` having sum(`points`) >= 100 ) as `assurer_candidates`"); - + $certs = tc( "select count(*) as `count` from `domaincerts` where `created` >= '$first' and `created` < '$next_year' @@ -286,7 +287,7 @@ function getDataFromLive() { $tmp_arr['new_users'] = number_format($users); $tmp_arr['new_assurers'] = number_format($assurers); $tmp_arr['new_certificates'] = number_format($certs); - + $stats['growth_last_years'][] = $tmp_arr; } $stats['growth_last_years_total'] = array( diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index 0c97ba2..0cffc02 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -18,6 +18,7 @@ */ require_once(dirname(__FILE__).'/../../includes/mysql.php'); + require_once(dirname(__FILE__).'/../../includes/lib/l10n.php'); $days = array("1" => "3", "15" => "2", "30" => "1", "45" => "0"); @@ -34,6 +35,7 @@ $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { + L10n::set_recipient_language(intval($row['id'])); if($row['subject'] == "") { $row['crt_name'] = str_replace("../", "www/", $row['crt_name']); @@ -106,6 +108,7 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { + L10n::set_recipient_language(intval($row['memid'])); if($row['subject'] == "") $row['subject'] = $row['CN']; diff --git a/scripts/db_migrations/version5.sh b/scripts/db_migrations/version5.sh new file mode 100755 index 0000000..f18f8c6 --- /dev/null +++ b/scripts/db_migrations/version5.sh @@ -0,0 +1,249 @@ +#!/bin/sh +# LibreSSL - CAcert web application +# Copyright (C) 2004-2011 CAcert Inc. +# +# 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; version 2 of the License. +# +# 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; if not, write to the Free Software +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA + + + +# script to do database migrations + +set -e # script fails if any command fails + +STDIN=0 +STDOUT=1 +STDERR=2 + +if [ "$1" = "--help" ]; then + cat >&$STDERR <<- USAGE + Usage: $0 [MYSQL_OPTIONS] + You have to specify all options needed by "mysql" as if you had started + the MySQL command line client directly (including the name of the + database to operate on). The MySQL user used has to have enough + privileges to do all necessary operations (among others CREATE, ALTER, + DROP, UPDATE, INSERT, DELETE). + You might need to enter the mysql password multiple times if you + specify the -p option. + USAGE + exit 1 +fi + +mysql_opt=" --batch --skip-column-names $@" + +schema_version=$( mysql $mysql_opt <<- 'SQL' + + SELECT MAX(`version`) FROM `schema_version`; +SQL +) +if [ $schema_version != 4 ]; then + cat >&$STDERR <<- ERROR + Error: database schema is not in the right version to do the migration! + Expected version: 4 + ERROR + exit 2 +fi + +mysql $mysql_opt <<- 'SQL' + +-- Move myISAM to InnoDB bug #1172 + +ALTER TABLE `abusereports` ENGINE=INNODB; +system echo "table abusereports altered to InnoDB" + + +ALTER TABLE `addlang` ENGINE=INNODB; +system echo "table addlang altered to InnoDB" + + +ALTER TABLE `adminlog` ENGINE=INNODB; +system echo "table adminlog altered to InnoDB" + + +ALTER TABLE `advertising` ENGINE=INNODB; +system echo "table advertising altered to InnoDB" + + +ALTER TABLE `alerts` ENGINE=INNODB; +system echo "table alerts altered to InnoDB" + + +ALTER TABLE `baddomains` ENGINE=INNODB; +system echo "table baddomains altered to InnoDB" + + +ALTER TABLE `cats_passed` ENGINE=INNODB; +system echo "table cats_passed altered to InnoDB" + + +ALTER TABLE `cats_type` ENGINE=INNODB; +system echo "table cats_type altered to InnoDB" + + +ALTER TABLE `cats_variant` ENGINE=INNODB; +system echo "table cats_variant altered to InnoDB" + + +ALTER TABLE `countries` ENGINE=INNODB; +system echo "table countries altered to InnoDB" + + +ALTER TABLE `disputedomain` ENGINE=INNODB; +system echo "table disputedomain altered to InnoDB" + + +ALTER TABLE `disputeemail` ENGINE=INNODB; +system echo "table disputeemail altered to InnoDB" + +ALTER TABLE `domaincerts` ENGINE=INNODB; +system echo "table domainderts altered to InnoDB" + + +ALTER TABLE `domains` ENGINE=INNODB; +system echo "table domains altered to InnoDB" + + +ALTER TABLE `domlink` ENGINE=INNODB; +system echo "table domlink altered to InnoDB" + + +ALTER TABLE `email` ENGINE=INNODB; +system echo "table email altered to InnoDB" + + +ALTER TABLE `emailcerts` ENGINE=INNODB; +system echo "table emailcerts altered to InnoDB" + + +ALTER TABLE `emaillink` ENGINE=INNODB; +system echo "table emaillink altered to InnoDB" + + +ALTER TABLE `gpg` ENGINE=INNODB; +system echo "table gpg altered to InnoDB" + + +ALTER TABLE `languages` ENGINE=INNODB; +system echo "table languages altered to InnoDB" + + +ALTER TABLE `localias` ENGINE=INNODB; +system echo "table localias altered to InnoDB" + + +ALTER TABLE `locations` ENGINE=INNODB; +system echo "table locations altered to InnoDB" + + +ALTER TABLE `news` ENGINE=INNODB; +system echo "table news altered to InnoDB" + + +ALTER TABLE `notary` ENGINE=INNODB; +system echo "table notary altered to InnoDB" + + +ALTER TABLE `org` ENGINE=INNODB; +system echo "table org altered to InnoDB" + + +ALTER TABLE `orgadminlog` ENGINE=INNODB; +system echo "table orgadminlog altered to InnoDB" + + +ALTER TABLE `orgdomaincerts` ENGINE=INNODB; +system echo "table orgdomaincerts altered to InnoDB" + + +ALTER TABLE `orgdomains` ENGINE=INNODB; +system echo "table orgdomains altered to InnoDB" + + +ALTER TABLE `orgdomlink` ENGINE=INNODB; +system echo "table orgdomlink altered to InnoDB" + + +ALTER TABLE `orgemailcerts` ENGINE=INNODB; +system echo "table orgemailcerts altered to InnoDB" + + +ALTER TABLE `orgemaillink` ENGINE=INNODB; +system echo "table orgemaillink altered to InnoDB" + + +ALTER TABLE `orginfo` ENGINE=INNODB; +system echo "table orginfo altered to InnoDB" + + +ALTER TABLE `otphashes` ENGINE=INNODB; +system echo "table otphashes altered to InnoDB" + + +ALTER TABLE `pinglog` ENGINE=INNODB; +system echo "table pinglog altered to InnoDB" + + +ALTER TABLE `regions` ENGINE=INNODB; +system echo "table regions altered to InnoDB" + + +ALTER TABLE `root_certs` ENGINE=INNODB; +system echo "table root_certs altered to InnoDB" + + +ALTER TABLE `schema_version` ENGINE=INNODB; +system echo "table schema_version altered to InnoDB" + + +ALTER TABLE `stampcache` ENGINE=INNODB; +system echo "table stampcache altered to InnoDB" + + +ALTER TABLE `statscache` ENGINE=INNODB; +system echo "table statscache altered to InnoDB" + + +ALTER TABLE `tickets` ENGINE=INNODB; +system echo "table tickets altered to InnoDB" + + +ALTER TABLE `tverify` ENGINE=INNODB; +system echo "table tverify altered to InnoDB" + + +ALTER TABLE `tverify-vote` ENGINE=INNODB; +system echo "table tverify-vote altered to InnoDB" + + +ALTER TABLE `user_agreements` ENGINE=INNODB; +system echo "table user_agreements altered to InnoDB" + + +ALTER TABLE `userlocations` ENGINE=INNODB; +system echo "table userlocations altered to InnoDB" + + +ALTER TABLE `users` ENGINE=INNODB; +system echo "table users altered to InnoDB" + + + -- Update schema version number + INSERT INTO `schema_version` + (`version`, `when`) VALUES + ('5' , NOW() ); +SQL + + +echo "Database successfully migrated to version 5" +exit 0 + diff --git a/scripts/gpgfillmissingemail.php b/scripts/gpgfillmissingemail.php index f328876..39f9d8f 100644 --- a/scripts/gpgfillmissingemail.php +++ b/scripts/gpgfillmissingemail.php @@ -18,7 +18,7 @@ require_once("../includes/mysql.php"); //general.php"); //include "../includes/general.php"; -function hex2bin($data) +function gpg_hex2bin($data) { while(strstr($data, "\\x")) { @@ -69,7 +69,7 @@ echo "Found:\n"; if (preg_match("/<([\w.-]*\@[\w.-]*)>/", $bits[9],$match)) { //echo "Found: ".$match[1]; - $mail = trim(hex2bin($match[1])); + $mail = trim(gpg_hex2bin($match[1])); echo "EMail: *$mail**\n"; |