ELENESSKI GAMES
  • Home
  • Games
    • Worzzler
    • The Lost Tribes
  • Unity
    • Facade Generator for Archimatix
    • Elenesski Object Database
    • Generic Move Camera
    • Unity C# Library
  • SQLINQ
    • Change Log
    • Concepts >
      • Overview
      • The Patterns
      • Database & Factories
      • Working with Tables
      • Custom Queries
      • Lazy Loading
      • Units of Work
    • Video Tutorials
    • Getting Started
    • Code Examples >
      • Implementing Behavior
      • Finding Objects
      • Custom Searches
      • Converters and Transformers
      • Object Association Management
      • New Objects
      • Creating Database
      • Associating Objects
      • Deleting Objects
      • Validating Objects
      • Saving
    • Sample Model >
      • Tutorial Model
      • Table Classes Code
      • Domain Classes Code
      • Factories Code
    • OSX Developers
    • Bridge Building
    • External Websites
    • Class Documentation
  • Presentations
    • 3D Ship Design
  • Music
    • Jamendo (Main)
    • Blend
    • Sound Cloud
    • Mixcloud (DJ)
    • DJ Demos
  • YouTube

Summary

SQLINQ is a Unity 3D library that implements several data and object design patterns for further abstracting your based on a open source library that provides generics, lamda expressions and LINQ to interact with a SQLite database and is not dependent on the System.Data library. 

SQLINQ is available for purchase for CAD$1499.99.  Pre-requisites for this software are at least a Professional copy of Sparx Enterprise Architect.  SQLINQ is embedded into the EA Query Tool, which provides the ability to quickly analyze a local EA repository for model documentation quality.  SQLINQ v2019.2 is current as of August 2019.

Essentially it allows you to work with data purely from an abstract level almost never needing to deal with SQL to fetch, insert, update or delete data out of a SQLite database.

The following parts of the database describe what the code is doing without the generator.   You can use SQLINQ without the generator for maximum flexibility.

Overview

The first APEX generate was written in 2003, to implement 100K website using mySQL and ISS/.Net.  APEX has matured with 4 major versions, version 4 saw support for SQLite (for Xamerin/iOS), so SQLINQ is the 5th version of the APEX platform.

With this wrapper you can work with your data at a object level without ever having to write SQL statements, and take advantage of the power LINQ language to pull data out of the database for your use.

The wrapper also implements the following Fowler Enterprise Architecture and Gang of Four Patterns:
  • Identity Field (http://www.martinfowler.com/eaaCatalog/identityField.html)
  • Identity Map (http://www.martinfowler.com/eaaCatalog/identityMap.html)
  • Lazy Loading (http://www.martinfowler.com/eaaCatalog/lazyLoad.html)
  • Factory Method (http://en.wikipedia.org/wiki/Factory_method_pattern)
  • Bridge Pattern (http://en.wikipedia.org/wiki/Bridge_pattern)

And provides Support for these patterns.  Additional coding is required by you to implement these features, but all is described below:
  • Unit of Work (http://www.martinfowler.com/eaaCatalog/unitOfWork.html)
  • Single Table Inheritance (http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html)

Note: You can implement a combination of Single Table Inheritance or Concrete Table Inheritance with SQL LINQ.  Really the only differentiation between the two is how you construct your tables and objects.

What does sqlinq really provide?

When working with data, there is a tendency to work with the data inside of our software according to the physical structure of the data.  Imagine trying to represent a solar system in space game where you have planets and moons.
Picture
To access the moons for a particular planet, what are the steps.  If you only have the planet's name, you first scan the Planet Table looking for a matching name, then scan the Moon Table and filtering by the Planet ID.

​
In SQLINQ, navivation between Planet and Moon is done through the navigation of the connectors.  If I have a Planet object, called "Earth", and our moon is called Luna. 
  • I can access all it's moons using "Earth.Moons" which gives me a list of all moons, but Earth.Moons[0] would be Luna.
  • If I had the Luna object, I could access the planet using "Luna.ThePlanet".

key differences

If you have the "Earth" object with no other objects loaded into the repository memory from the phyiscal database, to get the moons, you only need to code "Earth.Moons".  In the background, Moons is a Lazy Loaded Child.  The software will realize the Moons reference has not been loaded, and automatically query the database to pull these objects into memory.  Essentially it does the same process above, but in a more convenient way from a coding speed perspective.

One of the cool feature of the Lazy Loading process is the Identity Map.  This is a Repositiory pattern, and it's job is to guarantee that there is only ever one instance of a given Type and ID in the repository memory at any time.  So it's job is to sit in the datapath, and for every object coming in from the database is tossed, and instead, the existing memory version is returned instead.  This simple but critically important feature is to eliminate dual object data contamination bugs, that are among the most difficult to isolate and reolve.

The bug occurs when two copies of the same table row objects have conflicting changes, so that when these changes are persisted, one copy overwrites the other, resulting in data loss or corruption.  I've spent weeks hunting down bugs like these in large corporate database, it's very rewarding finding them, but head scratchers and expensive to resolve.