ALTE Azure Table Storage Indexed ORM

• Download source code - 33.3 KB
• Download demo test - 10.3 KB

Introduction

An ORM style layer which turns Azure Table Storage into a cheap and scalable indexed noSQL database - without the costs associated with Azure CosmosDB.

• Tiny Embedded .NET NoSQL Database with cloud storage in Azure Table Storage with massive scaleability
• .NET Objects are stored in Azure Table Storage
• Stores one property in one column on Azure for full compatibility and simplicity
• Can store Decimal/Currency type fields not normally supported in Table Storage
• Can store larger byte arrays over multiple columns automatically
• Properties can be indexed for lightning quick retrieval
• Pass in an example object with an index property set to lookup objects via index
• Properties can be full text indexed
• Pass in a search phrase to search the full text index
• Optional additional query on other columns
• Easy to read automatic time sequential IDs - similar to GUIDs but more user friendly and shorter - or use several other ID types or roll your own
• Optimistic concurrency built in
• Simple blob storage utilities built in
• Other utilities included - Randomize lists, Re-indexing, Backup of Table and Blobs
• Includes an ASP.NET session provider - although work is still needed on this to clear old session data, it is fully working
• USES: -- Multi-tenant apps -- Large scale apps
• In use currently on a multi tenant e-commerce platform
• In use currently on large scale classified selling platform

You can download ALTE code and the demo test project from the links at the top of this post.

Background

Azure Table Storage is a cheap, fast and scalable data store - with no frills. Running SQL Server or Cosmos on Azure is great, but expensive. How about somewhere in the middle?

ALTE was developed to bridge this gap. By using technical documents of how to work around Azure Table's limitations published by Microsoft and others, we thought that maybe we could do this work once, and then forget.

The result is ALTE - which provides a flexible, easy to use, scalable and INDEXED table store. Along the way, it also simplifies how to interact with Azure Tables and adds extra support for more types and bigger column sizes.

It doesn't claim to be a relational database, but it is fast, cheap and easy to use. Nothing to stop you from building your own relationships in code. We've tested it with HUGE data sets - and seen fantastic performance, but it's so easy to use, it works just as well for tiny databases, and in these cases, you pretty much get it for free.

How It Works

We've tried to keep Alte as compatible with normal Azure Tables as possible. In that way, you can use Azure Storage Explorer for diagnosis without too much extra work.

• It's POCO based - inheriting from a base class that includes database generated IDs (or your own!)
• Each POCO type is stored in a separate Azure Table - see below for naming conventions
• Each POCO property is stored in a separate Azure Table column. The exception here is Byte arrays which can span columns (automatically) - we plan to do the same with Strings at a later date
• Non supported types - Decimal for example - are converted to other types and back seamlessly
• We don't use the partition key at all - this may sound bad but it allows us to use batch processing
• We store our "primary" record using RowID as PK@RowID
• We store our "secondary" index records, which are a duplicate of the primary record (therefore, with some careful tracking, we only need to load ONE of these records when we retrieve the data) using a RowID generated from the column name and column value
• We store our full text index separately in another Azure Table, with the partition key used as the keyword and the rowkey pointing to the actual ID of the record. We also store other columns along with the index for quick retrieval without going to the parent record.
• Each table is prefixed in Azure Tables with our "tenant prefix" and when backed up is also suffixed by the date and time for example:

  SWEETSHOPoPRODUCTS or for backup copies SWEETSHOPoPRODUCTSo201812011255

  For blob entries, we use similar:

  sweetshop-productimages or for backup copies sweetshop-productimages-201812011255

  The naming convention may seem strange but we are limited by Azure. The "o" characters are the best we can do at a separator and look a bit like a full stop!

Using the Code

A very simple code example is below in VB.NET - using in C# or other .NET languages is no harder.

Imports Alte

Module Module1

    Sub Main()

        Dim ACC As String = GetSetting("AlteTest", "Database", "AzureName", "")
        Dim SEC As String = GetSetting("AlteTest", "Database", "AzureSecret", "")

        Console.WriteLine("Enter Azure Storage Account Name:" + 
                (IIf(ACC = "", "", "Or press return to use " + ACC)))
        Dim ACC2 As String = Console.ReadLine

        If ACC2 <> "" Then
            ACC = ACC2
            SaveSetting("AlteTest", "Database", "AzureName", ACC)

            Console.WriteLine("Enter Azure Storage Account Secret:")
            SEC = Console.ReadLine
            SaveSetting("AlteTest", "Database", "AzureSecret", SEC)
        End If

        Console.WriteLine("Creating Session")
        Dim Session As New AlteSession(ACC, SEC)

        Console.WriteLine("Creating Store")
        Session.CreateStore(Of Company)()
        ' need to do this for any object type we are saving

        Session.CreateBlobContainer("pictures")
        ' for storing bigger binary data

        Dim C As New Company(Session)
        C.SetID(ID_Style.EasyBase30_12)
        ' set our ID to lots of different types - or set your own
        ' if no ID is set by SAVE time - we will create one for you, but if you
        ' have one here you can use in related data straight away

        C.CompanyNumber = "123456"
        C.CompanyName = "Aversa"
        C.AddressLine1 = "The Road"
        C.Town = "Stafford"
        C.Country = "UK"
        C.PostCode = "ST17 000"
        C.Notes = "Some sample text for searching"
        C.Category = "NewCustomer"

        Console.WriteLine("Saving Company")

        C.Save()

        Console.WriteLine("Getting Company By ID")
        Dim C2 = Session.GetObjectByID(Of Company)(C.ID)
        Console.WriteLine("Company name : " + C2.CompanyName)

        Console.WriteLine("Getting Companies By index")
        Dim CS = Session.GetObjectsByIndex(Of Company)
                 (New Company With {.CompanyNumber = "123456"})
        Console.WriteLine("Found " + CS.Count.ToString + " Companies By index")

        Console.WriteLine("Getting Companies By index and extra query")
        Dim CS1 = Session.GetObjectsByIndex(Of Company)_
        (New Company With {.CompanyNumber = "123456"},,, "companyname eq 'Aversa'")
        '(table storage style query using eq etc..., lower case only for property names!)

        Console.WriteLine("Found " + CS.Count.ToString + " Companies By index")

        Console.WriteLine("Getting Companies By Full Text Index")
        Dim FTRs = Session.GetFullTextResults(Of Company)("searching") ' returns a list 
                                                   ' of full text search result objects

        'filter our FTS results client side
        Dim resft = FTRs.FindAll(Function(x) x.Properties("category") = "NewCustomer")

        ' Full text results include the ID of the records and 
        ' any properties saved IN the index
        For Each FTR In FTRs
            Console.WriteLine("Found ID " + FTR.ID)
            Console.WriteLine("Propery Saved With Index (category) is " + _
                    FTR.Properties("category")) ' LOWER CASE PROPERTY NAME HERE
        Next

        ' Now get the actual records - IF WE NEED TO - we may have stored 
        ' what we need in the index..
        Dim CS2 = Session.GetObjectByFullTextResult(Of Company)(FTRs)
        Console.WriteLine("Found " + CS2.Count.ToString + " Companies By full text index")

        'filter our list client side
        Dim res = CS2.FindAll(Function(x) x.CompanyNumber = "123456")

        Console.ReadLine()

    End Sub

End Module

Imports Alte

Public Class Company
    Inherits AlteObject
    ' inherit this to get basics
    Sub New()

    End Sub

    Sub New(AlteSession As AlteSession)
        MyBase.New(AlteSession)
    End Sub

    Property Title As String
    Property FirstName As String
    Property LastName As String

    'standard indexed properties
    <Index>
    Property CompanyName As String
    <Index>
    Property CompanyNumber As String

    Property AddressLine1 As String
    Property AddressLine2 As String
    Property Town As String
    Property Country As String
    Property PostCode As String

    ' a full text index searchable property
    <FTSindex>
    Property Notes As String

    ' a full text index searchable property - and we will store the value 
    ' in the full text index for quick access
    <FTSindex> <FTSstore>
    Property Category As String

    ' See here! We can store decimals in the database - stored internally 
    ' as large integers...
    Property TurnoverGBP As Decimal

End Class

History

• 29^th January, 2019: Initial version

Improvements and bug fixes are always being made - the project is in use in production sites.