stats/tables/create_tables.php

<?php

require_once("/home/data/httpd/eclipse-php-classes/system/dbconnection_dashboard_rw.class.php");
require_once($_SERVER['DOCUMENT_ROOT'] . "/projects/stats/hostname.php");

$dbc = new DBConnectionDashboard();
$dbh = $dbc->connect();

///////////////////////////////////////////////
//                                           //
// Create the table that will hold all stats //
//                                           //
///////////////////////////////////////////////
$query = "CREATE TABLE ".stats_table()." (project_id varchar(100), stats_date date, liveness smallint, ";

// News stats
$query .= "news_7_answer_average_time int, news_7_unanswered_number smallint, ";
$query .= "news_7_insider_posts float, news_7_number_posts smallint, news_7_average_posts float, "; 
$query .= "news_30_answer_average_time int, news_30_unanswered_number smallint, ";
$query .= "news_30_insider_posts float, news_30_number_posts smallint, news_30_average_posts float, ";

// Dashboard stats
$query .= "bugs_total smallint, bugs_7_delta smallint, bugs_7_percentage tinyint, bugs_7_gainloss smallint, ";
$query .= "bugs_30_delta smallint, bugs_30_percentage tinyint, bugs_30_gainloss smallint, ";
$query .= "bugs_180_delta smallint, bugs_180_percentage tinyint, bugs_180_gainloss smallint, ";

// Mail stats
$query .= "mail_7_number_posts smallint, mail_7_average_posts float, mail_7_average_subscribers float, ";
$query .= "mail_30_number_posts smallint, mail_30_average_posts float, mail_30_average_subscribers float, ";
$query .= "mail_180_number_posts smallint, mail_180_average_posts float, mail_180_average_subscribers float, ";

// Number of articles
$query .= "num_articles int, ";

// Primary key
$query .= "PRIMARY KEY(project_id,stats_date))";

echo $query."\n";

mysql_query($query,$dbh) or die("Could not create table: ".mysql_error());

$dbh = null;
$dbc = null;
?>