Automatic key population

Dec 12, 2015 at 7:43 PM
I have a application that's currently using UUID's as a primary key for each table. with Dbreeze, you need to specify a (int)key when you insert new records. anyway you could get DBreeze to automatically handle the key creation for you?
Dec 13, 2015 at 11:41 AM
Hi,
In DBreeze,on the low level, key for the table is always byte[]. Its max. size is 65KB. On the upper level we use DBreeze.Utils to transform different .NET types like string, int, long, ulong, GUID etc... into byte[]. We use DBreeze.Utils, because its internal functions make .NET datatypes sortable. After you insert data in DBreeze it becomes automatically sorted by key.

So, actually you can say:
static DBreezeEngine engine=null;

        public static void Main (string[] args)
        {

            if(engine == null)              
                engine = new DBreezeEngine (@"E:\temp\DBreezeTest\DBR1");
            
            using (var tran = engine.GetTransaction ()) {
                tran.Insert<Guid,string> ("t1", Guid.NewGuid (), "x3");
                tran.Insert<Guid,string> ("t1", Guid.NewGuid (), "x4");
                tran.Insert<Guid,string> ("t1", Guid.NewGuid (), "x5");
                tran.Insert<Guid,string> ("t1", Guid.NewGuid (), "x6");
                tran.Commit ();
            }


            //retrieving
            using (var tran = engine.GetTransaction ()) {
                foreach (var row in tran.SelectForward<Guid,string>("t1")) {
                    Console.WriteLine (row.Value);
                }
            }

            Console.ReadLine ();
        }
Of course you can use monotonically grown int,uint, ulong, long types as a key.
Or string can by used as a DBreeze key - search by word using StartsWith.

Please, specify details, if you need something more.
Dec 13, 2015 at 11:32 PM
Thank you. This is exactly what I was looking for.

I was wondering if one could use a generic type to service all structures but using the following code? Is this something that's supported within your library?
        static string dbreezeFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "database");
        static DBreezeEngine _engine = new DBreezeEngine(dbreezeFolder);

        static public bool insert<type>(object _tablerow) where type : new()
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _uniq_id = Guid.NewGuid().ToString().Replace("-", "").GetHashString();

                    string _tablename = typeof(type).ToString();
                    tran.Insert<string, DbMJSON<type>> (_tablename, _uniq_id, (type)_tablerow);
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Global.event_log.WriteEntry(ex.Message, EventLogEntryType.Error);
                    return false;
                }
            }
        }
        static public object retrieve<type>(string _primarykey_value)
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).ToString();
                    return tran.SelectForward<string, DbMJSON<type>>(_tablename).Select(qr => qr.Value.Get).Where(qr => (string)qr.GetType().GetProperty("id").GetValue(qr) == _primarykey_value).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    Global.event_log.WriteEntry(ex.Message, EventLogEntryType.Error);
                    return null;
                }
            }
        }
Dec 13, 2015 at 11:35 PM
Also,

I'm trying to search a "table" and was wondering what the most effective way would be to return a list of objects in a LINQ query.

I have the following code:
        static public List<Row<string, DbMJSON<type>>> search<type>() where type : new()
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).ToString();
                    return tran.SelectForward<string, DbMJSON<type>>(_tablename).ToList();
                }
                catch (Exception ex)
                {
                    Global.event_log.WriteEntry(String.Format("Error locking table for search: {0}", ex.ToString()), EventLogEntryType.Error);
                    return null;
                }
            }
I'm not sure how to work with the values that returned... How do I now serialize the JSON back to the original type?
Dec 14, 2015 at 3:43 AM
This is my complete code for generic type CRUD methods. Let me know if you see any problems with DBreeze library usage...
   static class LocalData
    {
        static string dbreezeFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "database");
        static DBreezeEngine _engine = new DBreezeEngine(dbreezeFolder);

        static public type insert<type>(object _tablerow) where type : new()
        {
            type _returndata = new type();
            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _uniq_id = Guid.NewGuid().ToString().Replace("-", "").GetHashString();

                    string _tablename = typeof(type).Name;
                    _tablerow.GetType().GetProperty("id").SetValue(_tablerow,_uniq_id, null);
                    tran.Insert<string, DbMJSON<type>> (_tablename, _uniq_id, (type)_tablerow);
                    tran.Commit();
                    _returndata = tran.SelectForward<string, DbMJSON<type>>(_tablename).First().Value.Get;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Global.event_log.WriteEntry(String.Format("Error adding record: {0}", ex.ToString()), EventLogEntryType.Error);
                }
            }
            return _returndata;
        }
        static public bool update<type>(object _tablerow) where type : new()
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).Name;
                    tran.Insert<string, DbMJSON<type>>(_tablename, (string)_tablerow.GetType().GetProperty("id").GetValue(_tablerow, null), (type)_tablerow);
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Global.event_log.WriteEntry(String.Format("Error adding record: {0}", ex.ToString()), EventLogEntryType.Error);
                    return false;
                }
            }
        }
        static public type retrieve<type>(string _primarykey_value) where type : new()
        {
            type _returndata = new type();
            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).Name;
                    _returndata = tran.Select<string, DbMJSON<type>>(_tablename, _primarykey_value).Value.Get;
                }
                catch (Exception ex)
                {
                    Global.event_log.WriteEntry(String.Format("Error retrieving record: {0}", ex.ToString()), EventLogEntryType.Error);
                }
            }
            return _returndata;
        }
        static public bool delete<type>(string _primarykey_value) where type : new()
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).Name;
                    tran.RemoveKey(_tablename, _primarykey_value);
                    return true;
                }
                catch (Exception ex)
                {
                    Global.event_log.WriteEntry(String.Format("Error removing record: {0}", ex.ToString()), EventLogEntryType.Error);
                    return false;
                }
            }
        }
        static public List<type> search<type>() where type : new()
        {

            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _tablename = typeof(type).Name;
                    return flatten<type>(tran.SelectForward<string, DbMJSON<type>>(_tablename).ToList());
                }
                catch (Exception ex)
                {
                    Global.event_log.WriteEntry(String.Format("Error locking table for search: {0}", ex.ToString()), EventLogEntryType.Error);
                    return null;
                }
            }
        }
        static public List<type> flatten<type>(List<Row<string, DbMJSON<type>>> _row_list) where type : new()
        {
            List<type> _data = new List<type>();
            foreach(var _datarow in _row_list)
            {
                _data.Add(_datarow.Value.Get);
            }
            return _data;
        }
    }
Dec 14, 2015 at 4:24 PM
I think you have to read documentation very thoroughly first to understand DBreeze abilities and approaches.

But some brief remarks:
  1. try...using(tran)...catch is a preferable construction.
  2. don't use rollback in the end of using - it's done automatically when using(tran) goes out (in case of errors also)
  3. don't use LINQ while searching DBreeze keys, use integrated functions instead (SelectForward..From...To StartsWith etc.).
  4. don't use DbMJSON, today we have protobuf.NET or NetJSON (to use any of them you have to setup custom serializer for DBreeze)
  5. DBreeze automatically converts types for the keys (all basic .NET types) and values (all basic .NET types + custom serializer types).
    So we can say tran.Insert<ulong, float>("t",1,1f) or tran.Insert<ulong, Person>("t",1,new Person())
    and then
    retrieve automatically converts into desirable type tran.Select<ulong, float>("t",1).Value or tran.Select<ulong, Person>("t",2).Value
  6. if you want to make an extension for insert/update/delete, take as a parameter a List of entities, not single one.
  7. while inserting a batch, sort it by key ascending, then execute tran.Insert as many times as necessary then use tran.Commit
  8. remember about ability to insert at once into several tables, they must be synchronized to avoid deadlocks (tran.SynchronizeTables)
and so on...
Dec 14, 2015 at 4:27 PM
So, if you have real life tasks to fulfill, please post a question about best techniques and approaches.
Dec 15, 2015 at 2:38 AM
Question about your insert. You say you should not use DbMJSON, but your documentation says you should. Am I missing something:

https://docs.google.com/document/d/1IFkXoX3Tc2zHNAQN9EmGSXZGbQabMrWmpmVxFsLxLsw/pub
Dec 15, 2015 at 4:26 AM
Also, when I remove the DbMJSON from the insert method I get the following error:
DBreeze.Exceptions.DBreezeException: Unsupported data type "<removed text>"
This is my insert code
        static public type insert<type>(object _tablerow) where type : new()
        {
            type _returndata = new type();
            using (var tran = _engine.GetTransaction())
            {
                try
                {
                    string _uniq_id = Guid.NewGuid().ToString().Replace("-", "").GetHashString();
                    var _typevalue = (type)_tablerow;
                    string _tablename = typeof(type).Name;
                    _tablerow.GetType().GetProperty("id").SetValue(_tablerow,_uniq_id, null);
                    tran.Insert<string, type> (_tablename, _uniq_id, _typevalue);
                    tran.Commit();
                    _returndata = tran.SelectForward<string, type>(_tablename).First().Value;
                }
                catch (Exception ex)
                { 
                    Global.event_log.WriteEntry(String.Format("Error adding record: {0}", ex.ToString()), EventLogEntryType.Error);
                }
            }
            return _returndata;
        }
Dec 15, 2015 at 9:11 AM
Edited Dec 15, 2015 at 8:01 PM
If you didn't setup custom serializer (doc from [20140603] ), then, of course, you must use DbMJSON.
Dec 16, 2015 at 4:44 AM
Thank you. Makes sense now.
Dec 16, 2015 at 8:43 PM
Your welcome!

DBreeze's Keys and Values, on the low level are byte[].
When you tran.insert<Guid, long>, key and value will be converted into byte[] using specific algorithmes to make key sortable.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;

using DBreeze;
using DBreeze.Utils;

namespace testDBreeze
{
    class Program
    {
        static void Main(string[] args)
        {
            DBreezeEngine engine = new DBreezeEngine(@"E:\temp\v");

            using (var tran = engine.GetTransaction())
            {

                tran.Insert<Guid, long>("t1", Guid.NewGuid(), 1);
                tran.Insert<Guid, long>("t1", Guid.NewGuid(), 2);
                tran.Insert<byte[], byte[]>("t1", Guid.NewGuid().ToByteArray(), ((long)2).To_8_bytes_array_BigEndian());

                foreach (var row in tran.SelectForward<byte[], byte[]>("t1"))
                {
                    Debug.WriteLine("Key: {0}; Value: {1}",row.Key.ToBytesString(),row.Value.ToBytesString());
                }
                tran.Commit();
            }

            Console.ReadLine();
        }
    }
}
Result is
Key: 002720378C41FE468EB7C36B92A30E36; Value: 8000000000000001
Key: D8F55D0EBDBD2D40BBE7ABE0C4E599ED; Value: 8000000000000002
Key: FF64F1089C003D4EABD6D2E5D70EB028; Value: 8000000000000002
Dec 16, 2015 at 8:51 PM
Edited Dec 16, 2015 at 8:53 PM
Sorting:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;

using DBreeze;
using DBreeze.Utils;

namespace testDBreeze
{
    class Program
    {
        static void Main(string[] args)
        {
            DBreezeEngine engine = new DBreezeEngine(@"E:\temp\v");

            using (var tran = engine.GetTransaction())
            {

                tran.Insert<DateTime, byte[]>("t1", DateTime.UtcNow, null);
                System.Threading.Thread.Sleep(10);
                tran.Insert<DateTime, string>("t1", DateTime.UtcNow, "jo");
                System.Threading.Thread.Sleep(10);
                tran.Insert<byte[], int>("t1", DateTime.UtcNow.To_8_bytes_array(), 1);

                foreach (var row in tran.SelectForward<byte[], byte[]>("t1"))
                {
                    Debug.WriteLine("Key: {0}; Value: {1}", row.Key.ToBytesString(), row.Value.ToBytesString());
                }
                tran.Commit();
            }

            Console.ReadLine();
        }
    }
}
Result:

Key: 08D3065A12B3414C; Value:
Key: 08D3065A12D6F69D; Value: 6A6F
Key: 08D3065A12D95863; Value: 80000001



or
using (var tran = engine.GetTransaction())
            {
                for (int i = 1; i < 1000000; i+=3)
                {
                    tran.Insert<int, int>("t1", i, i);
                }

                
                foreach (var row in tran.SelectForwardFromTo<int, int>("t1",50000,true,50025,true))
                {
                    Debug.WriteLine("Key: {0}; KeyByte: {2}; Value: {1}", row.Key.ToString(), row.Value.ToString(), row.Key.To_4_bytes_array_BigEndian().ToBytesString());                
                }
                tran.Commit();
            }
Result:
Key: 50002; KeyByte: 8000C352; Value: 50002
Key: 50005; KeyByte: 8000C355; Value: 50005
Key: 50008; KeyByte: 8000C358; Value: 50008
Key: 50011; KeyByte: 8000C35B; Value: 50011
Key: 50014; KeyByte: 8000C35E; Value: 50014
Key: 50017; KeyByte: 8000C361; Value: 50017
Key: 50020; KeyByte: 8000C364; Value: 50020
Key: 50023; KeyByte: 8000C367; Value: 50023
Dec 16, 2015 at 9:01 PM
Edited Dec 16, 2015 at 9:04 PM
Uniqness inside of the group (composite key)
using (var tran = engine.GetTransaction())
            {
                for (int i = 1; i < 4; i+=1)
                {
                    for (int j = 1; j < 6; j+=1)
                    {                  

                        tran.Insert<byte[], int>("t1", i.To_4_bytes_array_BigEndian().Concat(j.To_4_bytes_array_BigEndian()), j);
                    }
                }

                
                foreach (var row in tran.SelectForward<byte[], int>("t1"))
                {
                    Debug.WriteLine("Key: {0}; Value: {1}", row.Key.ToBytesString(), row.Value.ToString());
                    //Debug.WriteLine("Key: {0}; Value: {1}", row.Key.ToBytesString(), row.Value.ToBytesString());
                }
                tran.Commit();
            }
Result:
Key: 8000000180000001; Value: 1
Key: 8000000180000002; Value: 2
Key: 8000000180000003; Value: 3
Key: 8000000180000004; Value: 4
Key: 8000000180000005; Value: 5
Key: 8000000280000001; Value: 1
Key: 8000000280000002; Value: 2
Key: 8000000280000003; Value: 3
Key: 8000000280000004; Value: 4
Key: 8000000280000005; Value: 5
Key: 8000000380000001; Value: 1
Key: 8000000380000002; Value: 2
Key: 8000000380000003; Value: 3
Key: 8000000380000004; Value: 4
Key: 8000000380000005; Value: 5


After insert getting only values of the group 2
using (var tran = engine.GetTransaction())
            {            

                foreach (var row in 
                            tran.SelectForwardFromTo<byte[], int>("t1",
                                    ((int)2).To_4_bytes_array_BigEndian().Concat(int.MinValue.To_4_bytes_array_BigEndian()),true,
                                    ((int)2).To_4_bytes_array_BigEndian().Concat(int.MaxValue.To_4_bytes_array_BigEndian()),true
                            )

                    )
                {
                    Debug.WriteLine("Key: {0}; Value: {1}", row.Key.ToBytesString(), row.Value.ToString());                  
                }                
               
            }
Result:
Key: 8000000280000001; Value: 1
Key: 8000000280000002; Value: 2
Key: 8000000280000003; Value: 3
Key: 8000000280000004; Value: 4
Key: 8000000280000005; Value: 5
Dec 16, 2015 at 9:58 PM
Transaction
using (var tran = engine.GetTransaction())
            {
                tran.SynchronizeTables("t1", "t2");

                tran.Insert<int, int>("t1", 1, 1);
                tran.Insert<int, int>("t2", 1, 1);
                tran.Commit();
            }