Should work with mysql too just change the driver.
Code: Select all
#########################################
# koreSavetoSQL - written by gl0w 2011
#
# This plugin just saves data to MSSQL
# Right now monsters and items
#
#########################################
package koreSavetoSQL;
use Plugins;
use Globals qw($char %config %timeout);
use Settings;
use FindBin qw($RealBin);
use Log qw(message);
use Misc;
use DBI;
#koreSavetoSQL 1
#koreSavetoSQL_db openkore
#koreSavetoSQL_server localhost
Plugins::register('koreSavetoSQL','Allows your bot to save data to MSSQL.', \&onUnload);
my $hooks = Plugins::addHooks(
['start3', \&onLoad, undef],
['item_gathered', \&onItemFound, undef],
['target_died', \&onKill, undef],
);
sub onLoad {
if($config{koreSavetoSQL} == 1) {
# this is an integrated security connection string
my $dsn = "driver={SQL Server};Server=$config{koreSavetoSQL_server};database=$config{koreSavetoSQL_db};";
our $dbh = DBI->connect("dbi:ODBC:$dsn") or die 'Cannot connect to SQL.\n';
}
}
sub onItemFound {
my @args = @_;
saveItem("$args[1]{item}", "item");
}
sub onKill {
my @args = @_;
saveMonster("$args[1]{name}", "kill");
}
sub saveItem {
my ($msg, $domain) = @_;
my $sth = $dbh->prepare(qq{
INSERT INTO Items (itemName,charName,pickupDate) VALUES (?,?,getdate())
});
$sth->execute("$msg","$char->{name}");
}
sub saveMonster {
my ($msg, $domain) = @_;
my $sth = $dbh->prepare(qq{
INSERT INTO Monsters (monsterName,charName,killDate) VALUES (?,?,getdate())
});
$sth->execute("$msg","$char->{name}");
}
sub onUnload {
Plugins::delHooks($hooks);
$dbh->disconnect;
undef $ucname;
}
1;
Code: Select all
CREATE DATABASE [openkore]
GO
USE [openkore]
CREATE TABLE [dbo].[Items](
[itemName] [nvarchar](max) NOT NULL,
[pickupDate] [datetime] NOT NULL,
[charName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Monsters](
[monsterName] [nvarchar](max) NOT NULL,
[killDate] [datetime] NOT NULL,
[charName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
Code: Select all
select itemName, COUNT(itemName) as NumOccurences from Items --where pickupDate > '2011-01-23 01:54'
group by itemName having (COUNT(itemName) > 1)
Edit: deleted a testline. And i totally forgot that i had to change target_died hook to give me the name of the monster.
Code: Select all
Plugins::callHook("target_died", {name => $monsters_old{$ID}{'name'}});