Wednesday, February 9, 2011

Using Eloquera DB as an simple embedded database

Eloquera has a lot of goodness in it. One thing I really like is using it as an embedded database.

All of the code is available at bitbucket here

Heres the official way of doing it.

  • Add references to your project to  Eloquera.Client / Eloquera.Server / Eloquera.Common and Eloquera.config
  • Modify Eloquera.config most commonly the port and DatabasePath as shown:

  • Setup your DB connection to connect to (local)
Now step 2 and 3 aren't that hard. But I wanted to wrap these up a bit. Created a EqUtils project. Just has two functions. You can copy paste them into your own code if you want:


/// 
        /// Ensures two things :
        /// Sets up the database path in the Eloquera.config making sure its valid
        /// Makes sure that dbName points to a valid database.
        /// 
        /// The directory where you want the database file. Recommended Environment.CurrentDirectory
        /// The name of the DBfile without the .eq extension. Also called dbname
        /// Deletes the old database and creates a new one if set to true
        /// complete or relative path to Eloquera.config
        public static void SetupDB(string dbPath,string dbName,string completeConfigPath="Eloquera.config",bool overwrite = false)
        {
            if (!Directory.Exists(dbPath))
            {
                throw new IOException("The dbPath directory does not exist. dbPath requested was : " + dbPath);
            }
            if (!File.Exists(completeConfigPath))
            {
                throw new IOException("Eloquera.config is not present. Make sure this is the path to Eloquera.config: " + completeConfigPath);
            }   
         
            //First setup the DBPath
            FileStream fs = new FileStream(completeConfigPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(fs);
            fs.Close();
            XmlNode server = xmldoc.GetElementsByTagName("Server")[0];
            var currentDatabasePath = server.Attributes["DatabasePath"];
            server.Attributes["DatabasePath"].InnerText = dbPath;
            xmldoc.Save(completeConfigPath);            
            //Now create the database if it does not exist:
            if (File.Exists(Path.Combine(dbPath, dbName+".eq")) && overwrite)
            {
                using (var db = new DB("server=(local);options=none;"))
                {
                    db.DeleteDatabase(dbName, true);
                    db.CreateDatabase(dbName);
                }
            }
            else if (!File.Exists(Path.Combine(dbPath, dbName+".eq")))
            {
                using (var db = new DB("server=(local);options=none;"))
                {                   
                    db.CreateDatabase(dbName);
                }
            }
        }

        /// 
        /// Gets a new DB instance set as local embedded mode.
        /// 
        /// DB instance set as local embedded mode
        public static DB GetLocal()
        {
            return new DB("server=(local);options=none;");
        }


I think the Doc comments pretty much explain all that there is to know.

Using the SetupDB Function you can modify your Eloquera.config with the db path, Make sure that a database is present, Overwrite a database with a new one all in one go.

Using GetLocal you don't need to type in the embedded db string again and again.


Sample
You know what. The great thing about eloquera. You can let the code do the talking:


class Program
    {

        public class TestClass
        {
            [Index]
            public double Indexed { get; set; }
            public double NonIndexed { get; set; }
        }

        public static int ObjectCount = 1000;

        static void Main(string[] args)
        {
            var DbName = "PortableStore";
            EqUtil.SetupDB(Environment.CurrentDirectory, DbName, overwrite: true);

            //Create and store random junk data:         
            using (DB db = EqUtil.GetLocal())
            {
                db.OpenDatabase(DbName);
                for (int i = 0; i < ObjectCount; i++)
                {
                    TestClass item = new TestClass() { Indexed = i, NonIndexed = i };
                    db.Store(item);
                }
                db.Close();
            }           

            //Read all the random junk:
            using(DB db = EqUtil.GetLocal())
            {
                db.OpenDatabase(DbName);
                
                //Query using linq             
                var testClasses = from TestClass testClass in db select testClass;
                foreach (var item in testClasses)
                {
                    //This makes sure all data is retrieved 
                    //A cast will be required as in: (TestClass)item
                }                

                //Query using SQL:
                //For syntax visit http://eloquera.com/help/
                //Note you need to mention the namespace
                
                var sqlTestClasses = db.ExecuteQuery("Select Program.TestClass");
                foreach (var item in sqlTestClasses)
                {
                    //Console.WriteLine(((TestClass)item));
                }

                db.Close();                
            }

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }


Simple isn't it? This simple creates a new database. Overwriting the old one if required:

var DbName = "PortableStore";
EqUtil.SetupDB(Environment.CurrentDirectory, DbName, overwrite: true);


After this will see a newly created PortableStore.eq file in the same folder as your application:


The code simple creates random 1000 items of TestClass and queries them first using Linq and then SQL.
Cool eh? And did I mention ... the reads are REALLY fast. Try it out.

Notes

  • In case you configure your application like this (Environment.CurrentDirectory) note that UAC (Useraccess control on windows Vista and latter) does not allow an application not running as administrator to edit any files in the "Program Files" folder. So, it is best to distribute your application as xcopy deploy OR mention your uses to not install in that folder OR at the very least make the SetupDB step optional / configurable. 
  • It is always good practice to use using:
//Create and store random junk data:         
            using (DB db = EqUtil.GetLocal())
            {
                db.OpenDatabase(DbName);
                for (int i = 0; i < ObjectCount; i++)
                {
                    TestClass item = new TestClass() { Indexed = i, NonIndexed = i };
                    db.Store(item);
                }
                db.Close();
            }   
The general format is Using  { open ; work ; close } 
  • The SetupDB function makes sure that you can call OpenDatabase and there is no need to check if the database file is present. 

Enjoy!