BeBot - An Anarchy Online and Age Of Conan chat automaton
General => Feedback and Suggestions => Topic started by: TigerDragon on February 19, 2008, 02:55:45 am
-
I'm considering diving into the source to add support for using PostgreSQL as an alternative database for those who prefer using it (like myself.)
This would require a new module similar to the MySQL.php file and code that allows for one or the other to be loaded (so the bot doesn't die if MySQL isn't there, but PostgreSQL most certainly is.)
-
A couple of thoughts here.
conf/Bot.conf should have a variable
$preferred_database = 'mysql'; //or 'postgre'
and Sources/Bot.php should read that variable and check if the server for $preferred_database is availible. It's debatable if it should die if the preferred database isn't running but the other is. For me that's a coin toss.
If all variables are present in conf/MySQL.php that should be renamed to conf/SQL.php. If the variables needed for either differ greatly creating a conf/PostgreSQL.php would be in order.
Table creation and schema handling would probably need abstracting if the syntax differ. In any case either ought to be abstracted to insure a uniform handling.
There's probably a host of other issues that need adressing but we'll take them as they come.
Welcome to BeBot and thank you for wanting to contribute to the development.
-
Okay, I've taken a cursory glance at the source for several .php files and I think your suggestion on IRC to encapsulate the "create table" calls in a separate function from "query" is probably best. The reason I say this is... there are enough differences between MySQL and PostgreSQL to warrant it. The easiest example to use is "auto_increment." MySQL uses auto_increment to define a variable that automatically increases in value each time an insert is applied to the table. PostgreSQL uses sequences instead.
I also want to encapsulate things like MYSQL_ASSOC into a variable, because the pgsql equivalent function uses PGSQL_ASSOC instead.
It's going to take some work to try to make the database functions transparent, but I think it will be worth it in the end (because it will make it easier for the next person to add support for their favorite SQL server!)
-
I also want to encapsulate things like MYSQL_ASSOC into a variable, because the pgsql equivalent function uses PGSQL_ASSOC instead.
I vote we return MYSQL_BOTH and PGSQL_BOTH at all times (provided that's an option for PGSQL). The only drawback I can see is if the array becomes too big a memory hog.
The alternative is to always return an associative array. That would be "a better way"(tm) imo.
-
The default return type for postgres is PGSQL_BOTH. I haven't looked at the php functions for mysql to see what the default is for it, but I assume it's the same.
I don't like assuming default behavior, because it can change at any time in future updates to php. This would potentially cause problems. Returning BOTH doubles the size of the return array, but not every mysql query is calling ASSOC. I haven't looked to see if only the "create table" calls are not doing this, but I think (from memory) that some of the regular select queries were not calling ASSOC, and thus there is some mixed behavior in the bot already. I could be wrong, though.
Either changing it to be consistently ASSOC or changing it to use a wrapper variable as part of the encapsulating "SQL" object would work.
-
Always using ASSOC would be the cleaner (and less error-prone) way.
I guess we are just too lazy to adapt every single case of the normal arrays - which are the default (I think...) due to historic reasons.
-
Then I'll look at making ASSOC be the "default" for the query function in the generic SQL wrapper class when I add the create() function for creating tables.
-
...the normal arrays - which are the default (I think...) due to historic reasons.
I think that was one of my first contributions making MYSQL_ASSOC optional in the select()-function.
I really do not see any good reason to keep using numeric indexes from the queries.
-
I really do not see any good reason to keep using numeric indexes from the queries.
It would require a thorough rewrite of every function using the numeric version...
I guess this is the main reason why it isn't changed.
-
I expect to be going a thorough re-write of a lot of functions. Do you all mind if I clean up some of the comments, too? Fix spelling errors, make all the files use the same formatting (some use spaces, others use tabs, for example) and other janitorial work while I'm doing this project?
-
Feel free, if you create a patch file I'll most likely commit the changes to the SVN.
Changes to the trunk may create some conflicts, as I'm trying to get some work on the bot done the next days.
-
IMHO MYSQL_ASSOC should be default. There are cases where the numeric array makes more sense though. When I know a query is only going to return one result (or if it returns more than one result something is wrong!) I stick with the numeric index.
I vote for keeping the option to switch between numeric, associative, or both, but change the default to associative.
I don't thing MYSQL_BOTH would be a good idea. Too much possibility for errors and issues with modules iterating through the both the numeric index and associative indexes....
-
I'll hold off on this until a consensus is met. Whatever the case, the "BOTH" or "ASSOC" needs encapsulation, because "MYSQL_BOTH" and "MYSQL_ASSOC" are for the mysql functions only. The postgresql functions have their own "PGSQL_BOTH" and "PGSQL_ASSOC" and that is what should be used when the database is postgres instead of mysql.
-
You could make an abstract base class for database access, and then make MySQL and PostgreSQL and perhaps any further dababase we use.
Then define abstract functions in the database class like get_assoc(), get_both(), get_number(), and the implementations return the valid MYSQL_ASSOC or PGSQL_ASSOC values as the database needs.
-
You could make an abstract base class for database access, and then make MySQL and PostgreSQL and perhaps any further dababase we use.
This is what I've meant when I've said "encapsulated."
Then define abstract functions in the database class like get_assoc(), get_both(), get_number(), and the implementations return the valid MYSQL_ASSOC or PGSQL_ASSOC values as the database needs.
Yep... pretty much what I was planning.
-
Yep... pretty much what I was planning.
Ignore my comments then ;)
-
For supporting multiple databases, should we be looking at something like PDO, PEAR MDB2, or some abstraction layer?
Looks like PEAR MDB2 supports fbsql, ibase, mssql, mysql, mysqli, oci8, pgsql, querysim, and sqlite.
-
Something like PDO may help - though that would require a complete rewrite of all DB access too.
-
Something like PDO may help - though that would require a complete rewrite of all DB access too.
PDO doesn't look like a complete solution...
Right now our SQL is written for MySQL and only MySQL. It might be generic enough for PGSQL or SQLite, or it may not be. I know in some of my modules I have queries that will only work in MySQL as I've specified the MySQL storage engine to use.
PEAR MDB2 does some sort of abstraction/translation so that the SQL queries will work on any database supported by PEAR MDB2...
I've heard a number of arguments on why PostgreSQL is superior to MySQL, and those arguments just don't apply for what we're doing with BeBot and MySQL. Referential integrity, foreign keys, full transaction logging, and what have you just aren't a concern for BeBot.
If we were to officially support a database other than MySQL my vote would be for SQLite first as it doesn't require running of a database server, thus lowering the requirements for running BeBot significantly. BeBot + SQLite would be something IGN users (poor souls) looking for an upgrade path would go for.
-
BeBot + SQLite would be something IGN users (poor souls) looking for an upgrade path would go for.
Whistling into a modem would be an upgrade for IGN users. </joke>
-
Right now our SQL is written for MySQL and only MySQL. It might be generic enough for PGSQL or SQLite, or it may not be. I know in some of my modules I have queries that will only work in MySQL as I've specified the MySQL storage engine to use.
I think some of the datatypes are pretty MySQL specific. Maybe even a couple of the MySQL queries I used, I haven't checked what is supported by which sql server.
I've heard a number of arguments on why PostgreSQL is superior to MySQL, and those arguments just don't apply for what we're doing with BeBot and MySQL. Referential integrity, foreign keys, full transaction logging, and what have you just aren't a concern for BeBot.
I think all of that could be done with MySQL and InnoDB tables too. Not that we need it, true.
If we were to officially support a database other than MySQL my vote would be for SQLite first as it doesn't require running of a database server, thus lowering the requirements for running BeBot significantly. BeBot + SQLite would be something IGN users (poor souls) looking for an upgrade path would go for.
Would be nice, but I think there would be datatype problems again.
And Blue, thats no joke :P
-
Just to throw in $.02 from the "I don't do DB work" side of the fence ... anything that would let SQLite work would be very welcome. I wouldn't use it for orgbots or raidbots, but I'd definitely use it for a personal bot. I travel alot and don't like to have to run XAMPP from a USB stick all the time (so I just don't bother). But if it was running SQLite and I could just run the one thing to have it I would definitely use it.
It may be -way- outside the usual scope of BeBot but it would sure be useful.