Generic DB support

[Maarc]

Howdy gents! In my paying gig recently, I've had to play with DB stuff across multiple DBMSes. Combined with the existing MySQL thread, I thought I'd take a stab at something generic.

Feature wise, it looks like this:

• * An ODBCManager singleton, to wrap up th connect/disconnect and handles
  * ODBCManager can execute a query and you can iterate over result set (all datareturned as strings)
  * An abstraction of the account class data into DB tables
  * Additional history fields (archival purpose for logins)

Achieved so far

• Loading/saving accounts from DB rather than file

Things left to do

• Retrieve new accounts that are created in DB after load
  Deal with deleted accounts after load
  Add table for dealing with bans
  Expose ODBCManager to JSEngine
  Figure out how to do this from Linux

Built on SQL Server Express 2005, but should work (with slight tweaks, but will fix long term) any other ODBC compliant DBMS.

Here is the DDL:

Code: Select all

CREATE TABLE [dbo].[Account](
	[AccountID] [int] NOT NULL,
	[Username] [char](30) NOT NULL,
	[Password] [char](30) NOT NULL,
	[ContactDetails] [varchar](255) NULL,
	[IsBanned] [bit] NOT NULL,
	[IsSuspended] [bit] NOT NULL,
	[IsPublic] [bit] NOT NULL,
	[IsOnline] [bit] NOT NULL,
	[IsSeer] [bit] NOT NULL,
	[IsCounselor] [bit] NOT NULL,
	[IsGM] [bit] NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[AccountCharacters](
	[AccountID] [int] NOT NULL,
	[Serial] [int] NOT NULL CONSTRAINT [DF_AccountCharacters_Serial]  DEFAULT ((-1))
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[AccountCharacters]  WITH CHECK ADD  CONSTRAINT [FK_AccountCharacters_Account] FOREIGN KEY([AccountID])
REFERENCES [dbo].[Account] ([AccountID])
GO
ALTER TABLE [dbo].[AccountCharacters] CHECK CONSTRAINT [FK_AccountCharacters_Account]

CREATE TABLE [dbo].[AccountLoginHistory](
	[Username] [char](30) NOT NULL,
	[Password] [char](30) NULL,
	[IPAddress4] [char](16) NOT NULL,
	[WhenAttempted] [datetime] NOT NULL,
	[AttemptStatus] [smallint] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AccountLoginHistory]  WITH CHECK ADD  CONSTRAINT [FK_AccountLoginHistory_AttemptInfo] FOREIGN KEY([AttemptStatus])
REFERENCES [dbo].[AttemptInfo] ([AttemptStatus])
GO
ALTER TABLE [dbo].[AccountLoginHistory] CHECK CONSTRAINT [FK_AccountLoginHistory_AttemptInfo]

CREATE TABLE [dbo].[AttemptInfo](
	[AttemptStatus] [smallint] IDENTITY(0,1) NOT NULL,
	[Description] [varchar](32) NOT NULL,
 CONSTRAINT [PK_AttemptInfo] PRIMARY KEY CLUSTERED 
(
	[AttemptStatus] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Once I get a little more done, I'll commit into CVS.

Thoughts and feedback are very welcome.

[Xuri]

Coolness How would this affect the ability to add/modify user accounts? I mean.. how exactly would we do that when the accounts are saved in a database? Just curious, since I have very little experience with databases.

[Maarc]

I'll need to update the account management tool, but basically, its just a row in a table.

I've made it a conditional compile at the moment, so easily able to turn off. Also, unixODBC seems popular/prevalent, which is the same API, so hooray for that!

In theory, it would make it easier to create web based account admin as well.

I do need to abstract some things (eg getdate() vs now() etc), and also look to protect from sql injection attacks (don't want people dropping tables).

[Xuri]

*nod*

More questions: What about auto-creation of accounts (when enabled) - would that still work like normal? And what of the existing method of listing new accounts in the "newaccounts.adm" file and have them added automatically to the database when UOX3 reads the file and finds new entries?

[Maarc]

Well, its not a replacement per se. If the database doesn't connect, it falls back to file read/writes (though I probably should always write, and always check newaccounts.adm too).

Auto accounts via login should still work fine.

I'm working on JS exposure at the moment, but a property is being a bitch, so may just make it a method. But example code will look like:

Code: Select all

	var idxStmt		= ODBC.ExecuteQuery( "SELECT count(*) FROM Account;" );
	if( ODBC.lastOK == true )
	{
		Console.Warning( "ODBC: idxStmt val " + idxStmt );
		ODBC.FetchRow( idxStmt );
		if( ODBC.lastOK == true )
		{
			var colData = ODBC.GetColumn( 0, idxStmt );
			if( ODBC.lastOK == true )
				Console.Warning( "There are " + colData + " accounts in the DB" );
			else 
				Console.Warning( "ODBC GetColumn failure" );
		}
		ODBC.QueryRelease();
	}
	else
	{
		Console.Warning( "ODBC ExecuteQuery failure" );
	}

[stranf]

I downloaded the experimental, thanks Maarc!

But I haven't had a chance to play with it yet.

Does your program automatically port the current accounts into a database, or do I have to transfer that manually?

[Maarc]

The experimental I posted is *specifically* for database testing/feedback, I would not suggest using it on a real production shard. It includes an empty world with 2 accounts.

It will not import your existing accounts (I'll update the Account tool this weekend) and chars with them. You could import them manually, but that will be a real pain.

It really is only for testing database stuff and to get people's interest/feedback, its far from production ready.

BACKUP! If you use it on your world.

[tdzbdao]

Hey there,

long long time ago .. .and - wow, this thing still seems to be alive (at least somehow). I'm the creator of the MySQL-Integration Mod you mentioned earlier in this thread.

However, why don't you take a look at the JS Implementation in my source code? I believe you could easily adapt them to your ODBC attempt.

Btw... Getting ODBC software to build on non MS-Compilers is imho far more complicated than linking MySQL. I just wanted to toss that in, because you were worried about the building process getting to complicated.

A good idea may be to provide this feature as optional using build flags/defines. I guess it would be easier to handle than creating a new code branch for db support.