Click here to Skip to main content
14,237,722 members

ALTE Azure Table Storage Indexed ORM

Rate this:
2.80 (3 votes)
Please Sign up or sign in to vote.
2.80 (3 votes)
29 Jan 2019CPOL
An ORM style layer which turns Azure Table Storage into a cheap and scalable indexed noSQL database - without the costs associated with Azure CosmosDB.

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 retreival
  • 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 utilites included - Randomise 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 tennant e-commerce platforrm
  • In use currently on large scale classified selling platform

You can download ALTE code and the demo test project at

https://www.codeproject.com/script/Articles/ViewDownloads.aspx?aid=1274571

 

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 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 retreive 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 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 simply 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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

nickdenker
Web Developer Aversa
United Kingdom United Kingdom
Nick is a highly experienced .NET web and windows app developer, SQL database engineer, and team leader, and his skills go back to legacy environments in VB3 upwards.
He now works using the latest technology of .NET, HTML5, Xamarin and Microsoft’s cloud, Azure.

Nick has an eye for fine technical detail, yet will always start a project from the end user's perspective. He believes any application or web site should be as intuitive as possible with minimal learning curve.

Whilst at Box.co.uk, Nick worked developing the entire software infrastructure to run the company. This included stock control, customer and supplier management, e-commerce and accounts - as well as the integrations required to other services such as payment gateways, suppliers and logistics companies. Due to his skills, along with the sales skills of his former business partner, this enabled Box to grow to the multi-million pound giant it is today.

Comments and Discussions

 
QuestionNice but .. Pin
GerhardKreuzer18-Jan-19 11:06
memberGerhardKreuzer18-Jan-19 11:06 
AnswerRe: Nice but .. Pin
OriginalGriff18-Jan-19 11:08
protectorOriginalGriff18-Jan-19 11:08 
GeneralRe: Nice but .. Pin
GerhardKreuzer18-Jan-19 11:11
memberGerhardKreuzer18-Jan-19 11:11 
GeneralRe: Nice but .. Pin
GerhardKreuzer18-Jan-19 12:13
memberGerhardKreuzer18-Jan-19 12:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 17 Jan 2019

Stats

4.1K views
44 downloads
3 bookmarked