summaryrefslogtreecommitdiff
path: root/scripts
diff options
context:
space:
mode:
Diffstat (limited to 'scripts')
-rwxr-xr-xscripts/cron/refresh_stats.php55
-rwxr-xr-xscripts/cron/warning.php3
-rw-r--r--scripts/db_migrations/version5.sh294
3 files changed, 325 insertions, 27 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 100644
index 0000000..fa64367
--- /dev/null
+++ b/scripts/db_migrations/version5.sh
@@ -0,0 +1,294 @@
+#!/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
+
+# This particular version migrates from the preversioned state to version 5
+# If you want to reuse it for further migrations you probably should pay special
+# attention because you have to adjust it a bit
+
+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 (i.e. the version before there was versioning)
+ ERROR
+ exit 2
+fi
+
+mysql $mysql_opt <<- 'SQL'
+
+-- Move myISAM to InnoDB bug #1172
+
+ALTER TABLE schema_version ENGINE=INNODB;
+
+echo "table schema_version altered to InnoDB"
+
+
+ALTER TABLE abuserreports ENGINE=INNODB;
+
+echo "table abusereports altered to InnoDB"
+
+
+ALTER TABLE addlang ENGINE=INNODB;
+
+echo "table addlang altered to InnoDB"
+
+
+ALTER TABLE adminlog ENGINE=INNODB;
+
+echo "table adminlog altered to InnoDB"
+
+
+ALTER TABLE advertising ENGINE=INNODB;
+
+echo "table advertising altered to InnoDB"
+
+
+ALTER TABLE alerts ENGINE=INNODB;
+
+echo "table alerts altered to InnoDB"
+
+
+ALTER TABLE baddomains ENGINE=INNODB;
+
+echo "table baddomains altered to InnoDB"
+
+
+ALTER TABLE cats_passed ENGINE=INNODB;
+
+echo "table cats_passed altered to InnoDB"
+
+
+ALTER TABLE cats_type ENGINE=INNODB;
+
+echo "table cats_type altered to InnoDB"
+
+
+ALTER TABLE cats_variant ENGINE=INNODB;
+
+echo "table cats_variant altered to InnoDB"
+
+
+ALTER TABLE countries ENGINE=INNODB;
+
+echo "table countries altered to InnoDB"
+
+
+ALTER TABLE disputedomain ENGINE=INNODB;
+
+echo "table disputedomain altered to InnoDB"
+
+
+ALTER TABLE disputeemail ENGINE=INNODB;
+
+echo "table disputeemail altered to InnoDB"
+
+
+ALTER TABLE gpg ENGINE=INNODB;
+
+echo "table gpg altered to InnoDB"
+
+
+ALTER TABLE localias ENGINE=INNODB;
+
+echo "table localias altered to InnoDB"
+
+
+ALTER TABLE news ENGINE=INNODB;
+
+echo "table news altered to InnoDB"
+
+
+ALTER TABLE otphashes ENGINE=INNODB;
+
+echo "table otphashes altered to InnoDB"
+
+
+ALTER TABLE pinglog ENGINE=INNODB;
+
+echo "table pinglog altered to InnoDB"
+
+
+ALTER TABLE root_certs ENGINE=INNODB;
+
+echo "table root_certs altered to InnoDB"
+
+
+ALTER TABLE stampcache ENGINE=INNODB;
+
+echo "table stampcache altered to InnoDB"
+
+
+ALTER TABLE tickets ENGINE=INNODB;
+
+echo "table tickets altered to InnoDB"
+
+
+ALTER TABLE languages ENGINE=INNODB;
+
+echo "table languages altered to InnoDB"
+
+
+ALTER TABLE locations ENGINE=INNODB;
+
+echo "table locations altered to InnoDB"
+
+
+ALTER TABLE regions ENGINE=INNODB;
+
+echo "table regions altered to InnoDB"
+
+
+ALTER TABLE domaincerts ENGINE=INNODB;
+
+echo "table domainderts altered to InnoDB"
+
+
+ALTER TABLE domains ENGINE=INNODB;
+
+echo "table domains altered to InnoDB"
+
+
+ALTER TABLE domLink ENGINE=INNODB;
+
+echo "table domLink altered to InnoDB"
+
+
+ALTER TABLE emailcerts ENGINE=INNODB;
+
+echo "table emailcerts altered to InnoDB"
+
+
+ALTER TABLE emaillink ENGINE=INNODB;
+
+echo "table emaillink altered to InnoDB"
+
+
+ALTER TABLE email ENGINE=INNODB;
+
+echo "table email altered to InnoDB"
+
+
+ALTER TABLE notary ENGINE=INNODB;
+
+echo "table notary altered to InnoDB"
+
+
+ALTER TABLE tverify ENGINE=INNODB;
+
+echo "table tverify altered to InnoDB"
+
+
+ALTER TABLE tverify-vote ENGINE=INNODB;
+
+echo "table tverify-vote altered to InnoDB"
+
+
+ALTER TABLE userlocations ENGINE=INNODB;
+
+echo "table userlocations altered to InnoDB"
+
+
+ALTER TABLE users ENGINE=INNODB;
+
+echo "table users altered to InnoDB"
+
+
+ALTER TABLE user_agreements ENGINE=INNODB;
+
+echo "table user_agreements altered to InnoDB"
+
+
+ALTER TABLE orgdomaincerts ENGINE=INNODB;
+
+echo "table orgdomaincerts altered to InnoDB"
+
+
+ALTER TABLE orgdomains ENGINE=INNODB;
+
+echo "table orgdomains altered to InnoDB"
+
+
+ALTER TABLE orgdomlink ENGINE=INNODB;
+
+echo "table orgdomlink altered to InnoDB"
+
+
+ALTER TABLE orgemailcerts ENGINE=INNODB;
+
+echo "table orgemailcerts altered to InnoDB"
+
+
+ALTER TABLE orgemaillink ENGINE=INNODB;
+
+echo "table orgemaillink altered to InnoDB"
+
+
+ALTER TABLE orginfo ENGINE=INNODB;
+
+echo "table orginfo altered to InnoDB"
+
+
+ALTER TABLE org ENGINE=INNODB;
+
+echo "table org altered to InnoDB"
+
+
+ALTER TABLE statscache ENGINE=INNODB;
+
+echo "table statscache 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
+