From 9b2ed52d2607731198d280a735dd8ba287533422 Mon Sep 17 00:00:00 2001 From: simonraven Date: Sun, 11 Mar 2007 21:39:10 +0000 Subject: [PATCH] major re-organisation of the different SQL files; cleanup and fixing of the postgres stuff -- troubled is da man git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk/blootbot@1316 c11ca15a-4712-0410-83d8-924469b57eb5 --- BUGS | 11 ++++++++- ChangeLog | 17 +++++++++++++ INSTALL.pgsql | 46 +++++++++++++++++++----------------- TODO | 7 ++++-- files/sample/blootbot.config | 4 ++-- setup/README | 22 +++++++++++++++++ setup/botmail.sql | 8 ------- setup/connections.sql | 9 ------- setup/factoids.sql | 14 ----------- setup/freshmeat.sql | 8 ------- setup/news.sql | 7 ------ setup/onjoin.sql | 14 ----------- setup/rootwarn.sql | 8 ------- setup/seen.sql | 8 ------- setup/stats.sql | 8 ------- setup/uptime.sql | 6 ----- src/Modules/Factoids.pl | 5 ++-- src/Modules/Topic.pl | 13 ++++++---- src/dbi.pl | 14 +++++------ 19 files changed, 97 insertions(+), 132 deletions(-) create mode 100644 setup/README delete mode 100644 setup/botmail.sql delete mode 100644 setup/connections.sql delete mode 100644 setup/factoids.sql delete mode 100644 setup/freshmeat.sql delete mode 100644 setup/news.sql delete mode 100644 setup/onjoin.sql delete mode 100644 setup/rootwarn.sql delete mode 100644 setup/seen.sql delete mode 100644 setup/stats.sql delete mode 100644 setup/uptime.sql diff --git a/BUGS b/BUGS index 684c8c4..952c297 100644 --- a/BUGS +++ b/BUGS @@ -1 +1,10 @@ -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! diff --git a/ChangeLog b/ChangeLog index 18c95c4..51a6793 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,20 @@ +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 diff --git a/INSTALL.pgsql b/INSTALL.pgsql index 9510400..ecab8bc 100644 --- a/INSTALL.pgsql +++ b/INSTALL.pgsql @@ -2,36 +2,38 @@ Method of installation. ----------------------- - 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 -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 +> createdb --owner= [] -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 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 . 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 diff --git a/TODO b/TODO index b3ddccc..f0bd908 100644 --- a/TODO +++ b/TODO @@ -1,9 +1,12 @@ 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 diff --git a/files/sample/blootbot.config b/files/sample/blootbot.config index 1ac6c8c..d3ccffa 100644 --- a/files/sample/blootbot.config +++ b/files/sample/blootbot.config @@ -26,7 +26,7 @@ set owner OWNER set quitMsg adios amigos # path to a temporary directory which blootbot can use. -set tempDir temp +set tempDir /tmp ##### # Factoid database configuration @@ -37,7 +37,7 @@ set tempDir temp # 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 diff --git a/setup/README b/setup/README new file mode 100644 index 0000000..961d91a --- /dev/null +++ b/setup/README @@ -0,0 +1,22 @@ +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!) diff --git a/setup/botmail.sql b/setup/botmail.sql deleted file mode 100644 index 2789338..0000000 --- a/setup/botmail.sql +++ /dev/null @@ -1,8 +0,0 @@ -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) -); diff --git a/setup/connections.sql b/setup/connections.sql deleted file mode 100644 index 00dbf49..0000000 --- a/setup/connections.sql +++ /dev/null @@ -1,9 +0,0 @@ -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) -); diff --git a/setup/factoids.sql b/setup/factoids.sql deleted file mode 100644 index d5189d0..0000000 --- a/setup/factoids.sql +++ /dev/null @@ -1,14 +0,0 @@ -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) -); diff --git a/setup/freshmeat.sql b/setup/freshmeat.sql deleted file mode 100644 index 4b4f42b..0000000 --- a/setup/freshmeat.sql +++ /dev/null @@ -1,8 +0,0 @@ -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) -); diff --git a/setup/news.sql b/setup/news.sql deleted file mode 100644 index ebfb0e2..0000000 --- a/setup/news.sql +++ /dev/null @@ -1,7 +0,0 @@ -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) -); diff --git a/setup/onjoin.sql b/setup/onjoin.sql deleted file mode 100644 index 994cc54..0000000 --- a/setup/onjoin.sql +++ /dev/null @@ -1,14 +0,0 @@ -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); diff --git a/setup/rootwarn.sql b/setup/rootwarn.sql deleted file mode 100644 index afcee2c..0000000 --- a/setup/rootwarn.sql +++ /dev/null @@ -1,8 +0,0 @@ -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) -); diff --git a/setup/seen.sql b/setup/seen.sql deleted file mode 100644 index d920f79..0000000 --- a/setup/seen.sql +++ /dev/null @@ -1,8 +0,0 @@ -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) -); diff --git a/setup/stats.sql b/setup/stats.sql deleted file mode 100644 index 97f773c..0000000 --- a/setup/stats.sql +++ /dev/null @@ -1,8 +0,0 @@ -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) -); diff --git a/setup/uptime.sql b/setup/uptime.sql deleted file mode 100644 index 373902a..0000000 --- a/setup/uptime.sql +++ /dev/null @@ -1,6 +0,0 @@ -CREATE TABLE uptime ( - uptime INT UNSIGNED DEFAULT '0', # start. - endtime INT UNSIGNED DEFAULT '0', # end. - string VARCHAR(128) NOT NULL, - PRIMARY KEY (uptime) -); diff --git a/src/Modules/Factoids.pl b/src/Modules/Factoids.pl index 9a2a859..91eb3e7 100644 --- a/src/Modules/Factoids.pl +++ b/src/Modules/Factoids.pl @@ -423,7 +423,7 @@ sub CmdFactStats { 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" ); @@ -690,8 +690,7 @@ sub CmdFactStats { # 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); diff --git a/src/Modules/Topic.pl b/src/Modules/Topic.pl index 96b0b3f..10f0f77 100644 --- a/src/Modules/Topic.pl +++ b/src/Modules/Topic.pl @@ -60,10 +60,7 @@ sub topicCipher { 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"); @@ -170,7 +167,13 @@ sub do_add { 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) { diff --git a/src/dbi.pl b/src/dbi.pl index 9a1ca6b..76954e7 100644 --- a/src/dbi.pl +++ b/src/dbi.pl @@ -601,18 +601,20 @@ sub searchTable { } 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 () { chop; + next if $_ =~ /^--/; $data .= $_; } @@ -666,10 +668,8 @@ sub checkTables { # $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'}); @@ -701,7 +701,7 @@ sub checkTables { $cache{create_table}{$_} = 1; - &sqlCreateTable($_); + &sqlCreateTable($_, $param{DBType}); } } -- 2.39.2