Everyone knows that a database is the proper place to save data. There are SOME occasions where a text file would be better suited, but this is rarely the case. One of the main reasons people use a textfile over a database is because of simplicity. It is very easy to read and write to a file, much easier than it is to create a database, tables and columns.
At a glance, it might seem that a textfile is better than a database because of the above reasons. The problem happens when you need to manipulate or interpret the data. Things like this are overly complex when you are dealing with a flat file, but a database can handle complex tasks like this with its eyes closed. You also get the ability to do some pretty amazing things with a database that are simply not possible using a text file (such as indexes, constraints, foreign keys, type safety, and data normalization).
This plugin attempts to bridge the gap for scripts and database. It provides a data access object (called DatabaseManager) and can read database schema information (tables/columns) from an XML file. It also has a framework to store your queries (with parameters) and give you the ability to execute these queries with very little effort.
This plugin serves 3 key purposes:
1) This plugin exposes a data access layer that is easy and intuitive. There are currently 2 methods that are available to plugins.
RunQuery(queryText) - runs a query and returns the result as a recordset.
RunPQuery(queryText, params) - runs a parameterized query and returns the result as a recordset.
The RunPQuery method is used in the dynamic database classes that are generated from the XML schema.... read on
2) This plugin allows you to define the database tables and columns inside an XML file. It will automagically create the table if it doesnt exist and add the columns. It will ALSO validate the schema on bot load, adding any NEW fields as necessary. All of this is logged inside the SchemaAudit table so a user (or a crafty developer) can use this information to help troubleshoot problem. This setup is ideal for people that need to change the schema when it comes time to release a new bot version.
It is also possible to add a column to an EXISTING table, even if its part of a different plugin! The DatabaseManager plugin will already have several tables and will maintain the data. For example, it has a Player table that stores the PlayerID, Username, and ClanID. Whenever someone joins the channel, the plugin manager will ensure that they exist in the database. This allows other plugins to use this information without repeating the same logic over and over. Other plugins can also add columns to the Player table, such as AdvancedGreet for the AGP plugin.
3) This plugin will generate a VBS class that contains public functions for calling all of the queries that are defined in the XML. These queries are executes using ADO's parameterized queries and is safe from SQL injection. The query can define a return type (RecordSet, Value, None) for the function as well. This gives the developer a single location for every query. This is important because it makes it very simple to change the data layer without hunting through hundreds of lines of VBS code.
Now for the best part..... ANY plugin can create an instance of this dynamic class for ANY OTHER plugin!! Lets wait a second for that to sink in..... ..... ..... .......................... Yep, pretty incredible. So you want to write a plugin that will increase someone's trivia score? No problem, just get the trivia's database object and call the AddPoints() method. Pass in the PlayerID (from the DatabaseManager's object) and the amount of points. This would take 3 lines of code.....
CODESet dbDatabase = DatabaseManager.GetDatabaseClass("utDatabase")
Set dbTrivia = DatabaseManager.GetDatabaseClass("trivia")
Call dbTrivia.AddPoints(dbDatabase.GetPlayer("FiftyToo")("PlayerID"), 5000)
Inline documentation for the queries. This would allow developers to add text to a query that would help other people integrate with it. This plugin will come with an XSLT that will be used to transform the XML file into nicely formatted HTML. We could then add a command for the bot that will open an IE window with the HTML whenever someone types /databasehelp trivia.
Integrated query browser and designer. I would like to create a form that would allow developers to type in a query and see the results in a datagrid. It will list all of the dynamic objects and their methods and allow them to execute the functions and view the results.
Database backup and restore points. Since every plugin has access to the database, every plugin can potentially PWN your data. The only thing worse then losing the database for a plugin is losing the database for EVERY plugin. Yah, we need to look into this for sure
Uninstalling plugins. Since all schema changes are recorded in the audit table, it is possible to figure out what a plugin did to the database, and then reverse it (ie drop the table/columns). We could even take this one step further and create a NEW database from the all of the current XML files, copy over the data from the OLD database into the NEW database, then replace the OLD database with the NEW database. This will ensure that there is no stale data muckin up our database.
Better error handling. Nuff said....
Support for views and indexes. There is already code to create a primary key on AutoIncrement columns, but I would like to allow people to create an index on any column they wany.
Support for constraints. This would allow developers to create constraints on a column. A constraint is a little snippet of SQL code that will get processed whenever a row is added or modified. These come in handy when you are dealing with data integrity. You could create a constraint on the Birthday column that will disallow values that are greater than the current date. You could make a constraint for the points column that disallows negative values. Constraints are generally used to ensure that the data in the database is always correct.
Questions and comments are welcome and appreciated
It is now possible to control whether or not the code generation should create functions based off table definition alone. So far I have 2 functions, Add<TableName> and List<TableName>.... For example
<Column Name="AlertID" Type="Integer" AutoIncrement="True" />
<Column Name="PlayerID" Type="Integer" />
<Column Name="AlertDate" Type="Date" />
<Column Name="Message" Type="VarWChar" Size="200" />
.... will yield ...
Public Function ListAlert(whereClause)
If whereClause = "" Then
Set ListAlert = DatabaseManager.RunQuery("SELECT * FROM Alert")
Set ListAlert = DatabaseManager.RunQuery("SELECT * FROM Alert WHERE " & whereClause & "")
Public Function AddAlert(f_PlayerID,f_AlertDate,f_Message)
Dim params, retval
params = Array(DatabaseManager.NewParameter("@PlayerID", f_PlayerID, ADOVBS.adInteger, 4, ADOVBS.adParamInput),DatabaseManager.NewParameter("@AlertDate", f_AlertDate, ADOVBS.adDate, 8, ADOVBS.adParamInput),DatabaseManager.NewParameter("@Message", f_Message, ADOVBS.adVarWChar, 200, ADOVBS.adParamInput))
DatabaseManager.Verbose "PlayerID: " & f_PlayerID
DatabaseManager.Verbose "AlertDate: " & f_AlertDate
DatabaseManager.Verbose "Message: " & f_Message
Call DatabaseManager.RunPQuery("INSERT INTO Alert (PlayerID,AlertDate,Message) VALUES (@PlayerID,@AlertDate,@Message)", params)
I will add Delete<TableName>, <TableName>Exists, and Update<TableName>. For simple databases, these auto-generated functions will do everything, totally eliminating the need for the <Queries>