-allows adding a "cmd:foo (.*)" factoid but not removing it
+Known bugs that should be dealt with soon as possible:
+
+ * allows adding a "cmd:foo (.*)" factoid but not removing it (not sure if this still applies)
+ * Older CMD: foo's cannot be used or removed. Must be removed manually from the database with SQL
+ * !+topic list gives and incorrect error Failed. "You (#botpark) are not in #botpark, hey?"
+ * Bot tries to ask chanserv for OP's on any channel. Should be a chanset setting on a per channel basis
+ * News is currently stored in a file rather than the SQL table created for it
+ * bot doesnt seem to keep track of stat counters for "heh :) ...etc". Might be settings though and not a bug
+ * !help has size issues. Add's extra lines with only 1 or 2 help commands instead of one maximum size IRC msg
+ * FIXME: !WARN! ircCheck: we have a NULL chan in hash channels? removing!
+2007-03-08 00:00 troubled
+
+ * files/sample/blootbot.config: set tempDir changed to /tmp and
+ changed pgsql to SUPPORTED!
+ * src/dbi.pl: Fixed SQL comments (--) bug that prevented tables
+ from being created during startup.
+ * src/Modules/Factoids.pl: Typo for "factstats locked in selColHash
+ was preventing it from finding the func. Also addressed a !WARN!
+ issue about sorting a scalar.
+ * src/dbi.pl: Altered checkTables() to pass $dbtype when creating
+ tables. Table sql now called from setup/<$dbtype>/$table.sql
+ * src/Modules/Topic.pl: Fixed topicAuthor when no topic existed
+ and cleaned up original if statement to remove redundancy
+ * Reorganized the setup/ dir. Put schema SQL for each db type
+ into its own dir to allow customization. See the README file
+ * src/dbi.pl: Simplified the pgsql "SHOW TABLES" SQL
+
2007-03-07 14:06 troubled
* setup/pgsql/: Added pgsql specific dir. To be completed later
-----------------------
- Debian: (apt-get install postgresql)
-- Debian: (apt-get install libpgperl)
+- Debian: (apt-get install libpg-perl)
----
-OLD: SUPPORT FOR PGSQL IS CURRENTLY BROKEN! You'll have to use one of the other
-databases instead.
----
-Actually, I have implemented pgsql support. It works just fine, but it assumes
-that you have precreated the tables for now. To help with this, I have
-included a sql file under setup/pgsql/pgsql-schema.sql. Simply psql <dbname>
-and the type:
+As of now, blootbot has full pgsql support. It seems to be working 100%, but it
+assumes that you have precreated the database and user for now. As long as you
+already created the database and user and stored this info in the blootbot.config,
+then the tables will automatically be created on startup. Until I get setup.pl
+fixed, run the following commands as root (or postgres if root doesnt have
+permission to create users/db's):
-dbname#=> BEGIN;
-dbname#=> \i path/to/setup/pgsql/pgsql-schema.sql
-.......
-dbname#=> COMMIT;
+> createuser --no-adduser --no-createdb --pwprompt --encrypted <user>
+> createdb --owner=<user> <dbname> [<description>]
-If everything went fine, you should have working Pgsql tables needed for blootbot.
-Type "\d" to check if they were created.
+Dont forget to replace <user> and so forth with actual values you intend to use,
+and dont include the <>'s ;) If you run these commands, you should get a user
+with an encrypted password that cannot create new db's or user's (as it should be!),
+and the user will own the newly created database <dbname>. Congrats!
-In the future I will try to get things working a little smoother. But for now
-this should be considered "near production" quality. :)
+If everything went fine, you should have everything blootbot needs to use pgsql.
+Next simply cd to the base directory you installed the bot to and type:
-TODO:
------
- - Auto create tables if they dont exist
- - Modify setup.pl to do pgsql work
- - Pgsql db conversions?
+./blootbot
+Thats it! Everything the bot needs should be automatically created when it loads
+for the first time.
+
+In the future I will try to get around to editing the setup.pl file to ask the
+same questions it does for mysql (your root password etc) so that you can skip
+manually creating the database/user. But for now, this should be just fine for
+most of you techies out there.
+
----
troubled@freenode
TODO:
- - let's get a release out!
+ - Add PostgreSQL setup scripts instead of including a schema.sql
+ - Normalize the SQL tables a little better to reduce size and increase speed
+ - Keep the Changelog, TODO and BUGS files up to date. Clean things up a bit
+ - DONE: let's get a release out!
- rename ^[+-] commands
- remind - like this and others: http://jibble.org/reminderbot/
- kill SHM and and move to a pipe
- - add CIA like support - http://cia.navi.cx/
+ - DONE: add CIA like support - http://cia.navi.cx/
- add pastebot like support - http://sial.org/pbot/
- move nicks/server into sql table
- make channel flags be server/channel flags
set quitMsg adios amigos
# path to a temporary directory which blootbot can use.
-set tempDir temp
+set tempDir /tmp
#####
# Factoid database configuration
# mysql -- ...
# SQLite -- SQLite (libdbd-sqlite-perl) (might be version 2 or 3)
# SQLite2 -- SQLite (libdbd-sqlite-perl) (force version 2)
-# pgsql -- postgresql (NOT SUPPORTED)
+# pgsql -- postgresql (SUPPORTED and TESTED!!!)
### REQUIRED by factoids,freshmeat,karma,seen,...
set DBType mysql
--- /dev/null
+Welcome,
+
+This directory has changed slightly. The new format allows for
+each type of database to have its own schema. The following
+directories are included:
+
+ mysql/ -- Schema for the popular MySQL
+ sqlite/ -- Schema for v2 or v3 of SQLite
+ sqlite2/ -- Schema for specifically v2 of SQLite
+ pgsql/ -- Schema for PostgreSQL
+
+Also, the included setup.pl has been modified to work with
+all of the above types of databases. (FIXME: actually, only
+MySQL until I actually change it)
+
+To automate the setup of your database and user, type:
+
+ cd ~/blootbotdir
+ ./setup/setup.pl
+
+(NOTE: The setup will ask for an account capable of administrating
+the database server!)
+++ /dev/null
-CREATE TABLE botmail (
- srcwho VARCHAR(20) NOT NULL,
- dstwho VARCHAR(20) NOT NULL,
- srcuh VARCHAR(80) NOT NULL,
- time INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()',
- msg TEXT NOT NULL,
- PRIMARY KEY (srcwho,dstwho)
-);
+++ /dev/null
-CREATE TABLE connections (
- server VARCHAR(30) NOT NULL,
- port INT NOT NULL DEFAULT '6667',
- nick VARCHAR(20) NOT NULL,
- nickservpass VARCHAR(8) NOT NULL,
- ircname VARCHAR (20) NOT NULL DEFAULT 'blootbot experimental bot',
- timeadded INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()',
- PRIMARY KEY (server,port,nick)
-);
+++ /dev/null
-CREATE TABLE factoids (
- factoid_key VARCHAR(64) NOT NULL,
- requested_by VARCHAR(64) NOT NULL DEFAULT 'nobody',
- requested_time INT NOT NULL DEFAULT '0',
- requested_count SMALLINT UNSIGNED NOT NULL DEFAULT '0',
- created_by VARCHAR(64),
- created_time INT NOT NULL DEFAULT '0',
- modified_by VARCHAR(192),
- modified_time INT NOT NULL DEFAULT '0',
- locked_by VARCHAR(64),
- locked_time INT NOT NULL DEFAULT '0',
- factoid_value TEXT NOT NULL,
- PRIMARY KEY (factoid_key)
-);
+++ /dev/null
-CREATE TABLE freshmeat (
- projectname_short VARCHAR(64) NOT NULL,
- latest_version VARCHAR(32) DEFAULT 'none' NOT NULL,
- license VARCHAR(32),
- url_homepage VARCHAR(128),
- desc_short VARCHAR(96) NOT NULL,
- PRIMARY KEY (projectname_short,latest_version)
-);
+++ /dev/null
-CREATE TABLE news (
- channel VARCHAR(16) NOT NULL,
- id INT UNSIGNED DEFAULT '0',
- key VARCHAR(16) NOT NULL,
- value TEXT NOT NULL, # limit to ~450 or so.
- PRIMARY KEY (channel,id,key)
-);
+++ /dev/null
-CREATE TABLE onjoin (
- nick VARCHAR(20) NOT NULL,
- channel VARCHAR(16) NOT NULL,
- message VARCHAR(255) NOT NULL,
- modified_by VARCHAR(20) NOT NULL DEFAULT 'nobody',
- modified_time INT NOT NULL DEFAULT '0',
- PRIMARY KEY (nick, channel)
-);
-
--- v.2 -> v.3
--- ALTER TABLE onjoin ADD COLUMN modified_by VARCHAR(20) NOT NULL DEFAULT 'nobody';
--- ALTER TABLE onjoin ADD COLUMN modified_time INT NOT NULL DEFAULT '0';
--- ** the following doesn't work for sqlite **
--- ALTER TABLE onjoin ADD PRIMARY KEY (nick, channel);
+++ /dev/null
-CREATE TABLE rootwarn (
- nick VARCHAR(20) NOT NULL,
- attempt SMALLINT UNSIGNED,
- time INT NOT NULL,
- host VARCHAR(80) NOT NULL,
- channel VARCHAR(20) NOT NULL,
- PRIMARY KEY (nick)
-);
+++ /dev/null
-CREATE TABLE seen (
- nick VARCHAR(20) NOT NULL,
- time INT NOT NULL,
- channel VARCHAR(20) NOT NULL,
- host VARCHAR(80) NOT NULL,
- message TINYTEXT NOT NULL,
- PRIMARY KEY (nick,channel)
-);
+++ /dev/null
-CREATE TABLE stats (
- nick VARCHAR(20) NOT NULL,
- type VARCHAR(8) NOT NULL,
- channel VARCHAR(16) NOT NULL DEFAULT "PRIVATE",
- time INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()',
- counter SMALLINT UNSIGNED DEFAULT '0',
- PRIMARY KEY (nick,type,channel)
-);
+++ /dev/null
-CREATE TABLE uptime (
- uptime INT UNSIGNED DEFAULT '0', # start.
- endtime INT UNSIGNED DEFAULT '0', # end.
- string VARCHAR(128) NOT NULL,
- PRIMARY KEY (uptime)
-);
return &formListReply(1, $prefix, @list);
} elsif ($type =~ /^locked$/i) {
- my %hash = &sqlSelectColhash("factoids",
+ my %hash = &sqlSelectColHash("factoids",
"factoid_key,locked_by", undef,
"WHERE locked_by IS NOT NULL"
);
# parse the results.
&msg($who, "Fixed $fixed factoids.");
- &msg($who, "Self looped factoids removed: ".
- sort(keys %loop) ) if (scalar keys %loop);
+ &msg($who, "Self looped factoids removed: ". keys %loop ) if (scalar keys %loop);
my $prefix = "Loose link (dead) redirections in factoids ";
return &formListReply(1, $prefix, @newlist);
foreach (@_) {
my ($subtopic, $setby) = split /\|\|/;
- if ($setby =~ /^(unknown|)$/i) {
- push(@topic, $subtopic);
- # If topicAuthor is on then show it in topic, otherwise just topic -- troubled
- } elsif ($param{'topicAuthor'} eq "1") {
+ if ($param{'topicAuthor'} eq "1" and (!$setby =~ /^(unknown|)$/i)) {
push(@topic, "$subtopic ($setby)");
} else {
push(@topic, "$subtopic");
return if ($channels{$chan}{t} and !&hasFlag("T"));
my @prev = &topicDecipher($chan);
- my $new = "$args ($orig{who})";
+ my $new;
+ # If bot new to chan and topic is blank, it still got a (owner). This is fix
+ if ($param{'topicAuthor'} eq "1") {
+ $new = "$args ($orig{who})";
+ } else {
+ $new = "$args";
+ }
$topic{$chan}{'What'} = "Added '$args'.";
if (scalar @prev) {
}
sub sqlCreateTable {
- my($table) = @_;
+ my($table, $dbtype) = @_;
my(@path) = ($bot_data_dir, ".","..","../..");
my $found = 0;
my $data;
+ $dbtype = lc $dbtype;
foreach (@path) {
- my $file = "$_/setup/$table.sql";
+ my $file = "$_/setup/$dbtype/$table.sql";
next unless ( -f $file );
open(IN, $file);
while (<IN>) {
chop;
+ next if $_ =~ /^--/;
$data .= $_;
}
# $sql_showTBL = SQL to select all tables for the current connection
my $sql_showDB = "SELECT datname FROM pg_database";
- my $sql_showTBL = "SELECT c.relname FROM pg_catalog.pg_class c \
- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
- WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog','pg_toast') and
- pg_catalog.pg_table_is_visible(c.oid)";
+ my $sql_showTBL = "SELECT tablename FROM pg_tables \
+ WHERE schemaname = 'public'";
foreach ( &sqlRawReturn($sql_showDB) ) {
$database_exists++ if ($_ eq $param{'DBName'});
$cache{create_table}{$_} = 1;
- &sqlCreateTable($_);
+ &sqlCreateTable($_, $param{DBType});
}
}
projects / infobot.git / commitdiff