Ziah continues her experiments with MRM or Mini Region Modules. Now it's time to hook up to a database using an external class library... can it be done? Let's find out
In my first two Articles about MRM's ( Mini Region Modules ) i
explored how you can use them to go beyond some of LSL's
restrictions and write scripts in a more elegant way. Reading one
of Adam's Articles once again, I thought it was time to unleash the
real Power of MRM's. ( MRM: Language
Extensions and Libraries )
The ability, not only to write your own Libraries ( a Feature
that every LSL Scripter is missing dreadfully ) but also use
existing ones, opens almost unlimited possibilities to Scripters.
The first thing that slipped into my Mind was direct Database
access. So, let's try it. A look at the OpenSim Sourcecode showed
me, what I would need to access a MySqlDatabase. So let' start
with
//@DEPENDS:MySql.Data.dll
and
using MySql.Data.MySqlClient;
That should give us the necessary Libraries to access a
Database. Now, i only need a crash course in c# with mysql.
Connection to a MySQL Database. My MySql Database is located on the
same Computer as my OpenSim Installation, so the Connection String
should be
string s = "Server=localhost;Port=3306;Database=MRm1;User ID=mrm1;Password=mrm1;Pooling=false;";
Let's quick create a new Database and Table for this Experiment.
Tha Table should my look like this :
--
-- Database: `MRM1`
--
-- --------------------------------------------------------
--
-- Table structure for table `w_objects`
--
CREATE TABLE IF NOT EXISTS `w_objects` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`desc` varchar(30) DEFAULT NULL,
`key` varchar(36) DEFAULT NULL,
`pos` varchar(13) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
And now, Open Sesame! Database..
dbcon = new MySqlConnection(s);
try
{
dbcon.Open();
}
catch(Exception ex)
{
throw new Exception("Can't connect to Database ; " + ex.ToString());
}
Host.Object.Say("Connection to Database established...");
Aha, the Systems kindly calls my Attention to the fact, that i
need to declare what i want to use in c#, and if i want to use the
Class Exceptions, i need :
using System;
but now, let's try to connect to the Database,
12:05:24 - [CLIENT]: Client thread for Ziah Li bdc9ddcd-6b23-4750-b865-bc003d94a80e crashed. Logging them out.
12:05:24 - System.Exception: Can't connect to Database ; MySql.Data.MySqlClient.MySqlException: Access denied for user 'mrm1'@'localhost' to database 'MRm1'
at MySql.Data.MySqlClient.MySqlStream.OpenPacket () [0x00000]
at MySql.Data.MySqlClient.NativeDriver.Authenticate411 () [0x00000]
at MySql.Data.MySqlClient.NativeDriver.Authenticate () [0x00000]
at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000]
at MySql.Data.MySqlClient.Driver.Create (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000]
at MySql.Data.MySqlClient.MySqlConnection.Open () [0x00000]
ouch, i got disconnected from OpenSim, just because the access
to the Database was denied...hmmm...Adam? Ok, what's wrong?
....database 'MRm1'?..oops. Can happen....let's change that to MRM1
and finally :
[3:12] MRM TEST 7: Connection to Database established...
now let's do some stuff.... Just for fun, i would like to store
then Name, Description, UUID and Position of every Object in My Sim
into the Database Table.
foreach(IObject obj in World.Objects)
{
string o_name = obj.Name;
string o_uuid = obj.GlobalID.ToString();
string o_desc = obj.Description;
string o_pos = obj.WorldPosition.ToString();
MySqlCommand cmd = new MySqlCommand("INSERT INTO `MRM1`.`w_objects` (`name`,`desc`,`key`,`pos`) VALUES(?name,?desc,?key,?pos);",dbcon);
cmd.Parameters.AddWithValue("?name",o_name);
cmd.Parameters.AddWithValue("?desc",o_desc);
cmd.Parameters.AddWithValue("?key",o_uuid);
cmd.Parameters.AddWithValue("?pos",o_pos);
cmd.ExecuteNonQuery(); cmd.Dispose();
Host.Object.Say(o_name + " stored.");
}
and close the Connection again
dbcon.Close();
Host.Object.Say("Connection to Database closed...");
save and click....
[10:27] MRM TEST 7: Let's do it!
[10:27] MRM TEST 7: Connection to Database established...
[10:27] MRM TEST 7: Primitive stored.
[10:27] MRM TEST 7: new Improved barrel stored.
[10:27] MRM TEST 7: YoutubeRC stored.
[10:27] MRM TEST 7: MRM Tests DUMMY stored.
[10:27] MRM TEST 7: PingStats stored.
[10:27] MRM TEST 7: PingBox stored.
[10:27] MRM TEST 7: p3 stored.
[10:27] MRM TEST 7: SurfBoard stored.
[10:27] MRM TEST 7: Segment stored.
[10:27] MRM TEST 7: Segment stored.
[10:27] MRM TEST 7: MRM Test 6 stored.
[10:27] MRM TEST 7: Primitive stored.
[10:27] MRM TEST 7: OpenSimInfo stored.
[10:27] MRM TEST 7: Blimp stored.
...
[10:27] MRM TEST 7: MRM TEST 7 stored.
[10:27] MRM TEST 7: Connection to Database closed...
and yes, looking a the Database Table, the Data is there...
I know, the code in this Example is a bit sloppy, but as a proof
of concept, it should be ok. So, if we consider that MRM's also
don't suffer from the limitations and restriction we know from LSL,
this could remove the need for those PHP or Java, Ruby or whatever
Back-ends we need to access with http request from LSL. Even if you
don't want to rewrite existing scripts as MRM's, it should might
well be possible to create a MRM-Back-end for Database-access.
Keep in Mind, that in this Example, i only used one external
Library for the Database access. I'm sure there is a lot more
outside worth to try. Just think about what you were always missing
in LSL, throw it into Google, add ".NET" and maybe "Open Source" as
Keywords, and here you go.
So, have fun and happy scripting..!
Appendix : The complete Script
//MRM:C#
//@DEPENDS:MySql.Data.dll
/*=============================================================
(c) all rights reserved
================================================================*/
using System;
using OpenSim.Region.OptionalModules.Scripting.Minimodule;
using OpenMetaverse;
using MySql.Data.MySqlClient;
namespace OpenSim{
//MRM:C#
//@DEPENDS:MySql.Data.dll
/*=============================================================
(c) all rights reserved
================================================================*/
using System;
using OpenSim.Region.OptionalModules.Scripting.Minimodule;
using OpenMetaverse;
using MySql.Data.MySqlClient;
namespace OpenSim{
class MiniModule:MRMBase
{
private MySqlConnection dbcon;
public override void Start()
{
Host.Object.Say("Starting up a useless script.(MRM)");
Host.Object.OnTouch += OnTouched;
string s = "Server=localhost;Port=3306;Database=MRM1;User ID=mrm1;Password=mrm1;Pooling=false;";
dbcon = new MySqlConnection(s);
dbcon.Close();
}
void OnTouched(IObject sender, TouchEventArgs e)
{
Host.Object.Say("Let's do it!");
try
{
dbcon.Open();
}
catch(Exception ex)
{
throw new Exception("Can't connect to Database ; " + ex.ToString());
}
Host.Object.Say("Connection to Database established...");
foreach(IObject obj in World.Objects)
{
string o_name = obj.Name;
string o_uuid = obj.GlobalID.ToString();
string o_desc = obj.Description;
string o_pos = obj.WorldPosition.ToString();
MySqlCommand cmd = new MySqlCommand("INSERT INTO `MRM1`.`w_objects` (`name`,`desc`,`key`,`pos`) VALUES(?name,?desc,?key,?pos);",dbcon);
cmd.Parameters.AddWithValue("?name",o_name);
cmd.Parameters.AddWithValue("?desc",o_desc);
cmd.Parameters.AddWithValue("?key",o_uuid);
cmd.Parameters.AddWithValue("?pos",o_pos);
cmd.ExecuteNonQuery(); cmd.Dispose();
Host.Object.Say(o_name + " stored.");
}
dbcon.Close();
Host.Object.Say("Connection to Database closed...");
}
public override void Stop()
{
dbcon = null;
}
}
}