CREATE VIEW today AS
SELECT to_char(chanlogs.msg_date, 'MM/DD HH24:MI'::text) AS "time", chanlogs.channel, (chanlogs.speaker || '@'::text) || chanlogs.mud AS speaker, chanlogs.message
FROM chanlogs
WHERE chanlogs.msg_date >= (now() - '1 day'::interval)
ORDER BY chanlogs.msg_date;
CREATE VIEW words AS
SELECT foo.speaker, sum(foo.wordcount) AS words
FROM ( SELECT chanlogs.speaker, fn_wordcount(chanlogs.message) AS wordcount
FROM chanlogs
WHERE NOT chanlogs.is_bot AND chanlogs.msg_date >= (now() - '7 days'::interval)
GROUP BY chanlogs.speaker, chanlogs.message) foo
GROUP BY foo.speaker
ORDER BY sum(foo.wordcount) DESC;
my $text = $_[0];
return undef if !defined $text;
my @words = split /\s+/, $text;
return undef if !defined @words;
return scalar(@words);
[code]
Which in this case is simple whitespace delimited things in the message body… but you could be more fancy if you liked.
The idea is, you can let the database do some of the work for you, and provide a seamless view that makes it look to the code like it's one big table, even if it's actually joining multiple things together.
case CS_PlayerStatsRequest<Zone>.ChartType.ScoreDaily:
{
//Get the top100 stats sorted by points
//Note: This is if a player doesnt use args aka current day stats
var daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);
//Are they requesting a specific date?
DateTime now = DateTime.Now;
if (pkt.options != "")
{
//Player wants to see yesterday's date
if (pkt.options.Equals("-1"))
{
now = now.AddDays(-1);
daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone && dt.date >= now
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);
}
else //Specific date
{
string[] args = pkt.options.Split('-');
string final = string.Join("/", args);
try
{
now = DateTime.Parse(final, System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat);
}
catch (FormatException)
{
//Wrong format, use yesterday as default
now = (now.AddDays(-1));
}
DateTime add = now.AddDays(1);
daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone && dt.date >= now && dt.date < add
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);
}
}
MemoryStream stream = new MemoryStream();
try
{
foreach (Data.DB.statsDaily day in daily)
{
BinaryWriter bw = new BinaryWriter(stream);
bw.Write(day.players[0].alias1.name.ToCharArray());
bw.Write((byte)0);
Data.DB.squad squad = day.players[0].squad1;
string squadname = "";
if (squad != null)
squadname = squad.name;
bw.Write(squadname.ToCharArray());
bw.Write((byte)0);
bw.Write((short)2);
bw.Write(day.vehicleDeaths);
bw.Write(day.vehicleKills);
bw.Write(day.killPoints);
bw.Write(day.deathPoints);
bw.Write(day.assistPoints);
bw.Write(day.bonusPoints);
bw.Write(day.kills);
bw.Write(day.deaths);
bw.Write((int)0);
bw.Write(day.playSeconds);
}
}
catch (Exception e)
{
Log.write(TLog.Warning, "WriteElementDaily " + e);
}
SC_PlayerStatsResponse<Zone> response = new SC_PlayerStatsResponse<Zone>();
response.player = pkt.player;
response.type = CS_PlayerStatsRequest<Zone>.ChartType.ScoreDaily;
response.columns = "Top100 Daily Score,Name,Squad";
response.data = stream.ToArray();
zone._client.sendReliable(response, 1);
}
break;
I would go the route quix described however I would create a duplicate table. Don't have your commands work off the live data that is updated every 5 minutes. Create a duplicate table and create a job to insert a new record by zone at midnight every night then use the timestamps in this new table to grab the recordsets you're looking for. If yearly is your largest denominator then create another duplicate table for the purpose of archiving old data and another job that runs in the middle of the night to move records with timestamps greater than a year to this table. That way you're working off a table that never contains more than a years worth of data with your commands and you're only inserting "Zone * Players" records once a day at the end of every day. If you want players to be able to look at previous years and not just the current year then you can modify your yearly command to work off the archive table when needed.
I use this method when dealing with large amounts of data in tables that will continue to grow larger and larger but for the current purpose you might just want to make the single table that gets inserted into once a night.