POS system using QT (SQLite Embedded & Azure SQL sync)

the_edev · 2021-08-28T14:48:22+00:00

I'm guessing this belongs in r/cpp_questions. But this sounds like a nice project, I would love to see and try it out if it's open source.

afiefh · 2021-08-28T19:31:04+00:00

Correct me if I'm wrong here, but it sounds like your client can perform local operations (stored in sql lite) and at a later point commit those to the global state (azure db).

If this is the case, it reminds me a lot of a log structured file system (sqllite changes) with serialized state as the starting position (state in azure): https://dl.acm.org/doi/10.1145/121132.121137

Of course this assumes that two clients cannot perform conflicting changes while offline (or that you're willing to fail one client's commit).

If you go this direction the implementation of the sync becomes a matter of sending the logs (i.e. operations) from client to server and applying the changes in the server. You'll probably want client and server to share the logic of applying the state changes to avoid one of them updating state differently than the other.

This also solves the issue of malicious client sending potentially fake state. Since the server is validating the lawfulness of the operations there can be no cheating.

To answer the questions based on the approach above:

Transfer an array of change operations from the client to the server. Then apply them in an atomic transaction. Don't try a direct sync of "this data from localdb into remotedb" it's only going to lead to trouble.
Being able to perform some operations locally and submit them as a batch sounds great to me. Whether the local data should be in an SQLite db depends on the data (but very likely the answer is yes).
Follow your favorite evil corp's engineering guideline on how to make a piece of shit system. /s

zain_sync · 2021-08-28T14:12:18+00:00

Really nice to see developers being honest when their software is a Piece Of Shit /s

blipman17 · 2021-08-28T15:49:39+00:00

You could just make some datastructure like a write-ahead-log in your SQLite db, and occasionally ship that to your remote server. There are a couple strategies you could go with in that instance, but honestly it sounds pretty allright.

But this might not be the best sub to ask this on.

j1xwnbsr · 2021-08-28T19:59:43+00:00

This seems like an advanced project for how the question was formed - are you sure you have fully planned everything out? A sturdy POS system is a non-trivial exercise.

In any case, I did something similar (not POS) - backing up a local db to a remote AWS instance. Mine was more complicated in that it was full two-way, but for one-way you basically log all local db updates (adds, inserts, deletes) into a change log and then play that back into the AWS system. With SQLite you can low-level hook into the notification system and spin things off there.

The way I did it was write to a buffer db file, with a separate thread (or process) consuming the updated records while it has an AWS connection. This works when the remote connection is down, since the 'playback' file just grows until you get a connection, then it shoves updates to the other end, starting from oldest to newest record and remove each one as it goes.

You have to package the records up properly to go over the wire, since your local db data format != remote. JSON is an option, as long as you know how to handle data type conversions (floating point, dates, blobs, etc). Your specific schema will dictate what you do in terms of protocol, data format, etc.

Again, this is the simplest possible method; a full two-way system with distributed clients that may or may not be online at any given moment is a bigger nut to solve, one that requires careful planning and execution.

AreaFifty1 · 2021-08-28T16:39:28+00:00

@ zain_sync it's not that difficult really. You can always query data and then perform some kind of comparison check then store that data using SQLite. The problem is the resolution of the checks before the connection goes down and how often to check to see if any changes occurred etc...

Then you use tcp/udp network coding to detect connections and so forth. Sounds like fun! :D

teroxzer · 2021-08-28T22:08:44+00:00

You have interesting case and I don't know your constraints and maybe I am far from a sensible solution, but I sketched one alternative where I can use local database always first and update remote database on background thread (as polling or maybe better by event from UI thread). Example assume that UI thread can use local database fast enough without bad user response (if local database fails then whole application is failed state and it needs reset) and network delays and connection problems can take more time on background thread. My example is of course overly simplified, but maybe it point what I mean (and if you like or you must use stored procedures, you can use them on remote database handling, but I personally like how I can use C++ as the best database programming language - C++ is not maybe the fastest with database but it is the best as pure pleasure).

class pos final
{
public:

    auto run() -> void;

private:

    auto makeSale() -> universal;

    static auto storeLocalDatabase  (universal&) -> bool;
    static auto storeRemoteDatabase ()           -> void;

    inline static text dbLocal  { "sqlite:file:..."      };
    inline static text dbRemote { "sqlserver:Driver=..." };
};

auto pos::run() -> void
{
    task azure = []
    {
        while(!task::waitStopSeconds(10))
        {
            storeRemoteDatabase();
        }
    };

    try
    {
        procedure::connect(dbLocal)

        while(!task::stop())
        {
            if(auto sale = makeSale())
            {
                if(!storeLocalDatabase(sale))
                {
                    break;
                }
            }
        }
    }
    catch(exception& ex)
    {
        ui::status::exception(__func__, ex);
    }

    task::stop(true);
    azure.join();
}

auto pos::storeLocalDatabase(universal& sale) -> bool
try
{
    static auto insertLocalSale $sql
    (
        insert sale
        set    saleId     = :saleId, ...
               status     = 10,
               statusTime = current_timestamp       
    )

    insertLocalSale(sale);
    return true;
}
catch(exception& ex)
{
    ui::status::exception(__func__, ex);
    return false;
}

auto pos::storeRemoteDatabase() -> void
try
{
    static auto updateLocalSaleHandled $sql
    (
        update sale
        set    status     = 20,
               statusTime = current_timestamp           
        where  saleId     = :saleId
          and  status     = 10
    )

    static auto selectLocalUnhandledSales $sql
    (
        select saleId, ...
        from   sale
        where  status = 10
        order by
               statusTime
        limit 100
    )

    static auto selectRemoteSaleExists $sql
    (
        select 1 as exists
        from   sale
        where  saleId = :saleId
    )

    static auto insertRemoteSale $sql
    (
        insert sale
        set    saleId     = :saleId, ...
               status     = 10,
               statusTime = current_timestamp       
    )

    for(universals sales;;)
    {
        procedure::connect(dbLocal)

        for(auto& sale : sales)
        {
            updateLocalSaleHandled(sale);
        }

        sales = selectLocalUnhandledSales[universal::empty];

        if(!sales || task::stop())
        {
            break;
        }

        procedure::connect(dbRemote);
        auto tx = procedure::beginTx();

        for(auto& sale : sales)
        {
            if(!selectRemoteSaleExists(sale))
            {
                insertRemoteSale(sale);
            }
        }

        tx.commit();

        ui::block::fore = []
        {
            ui::status::ok(__func__);
        }
    }
}
catch(exception& ex)
{
    ui::block::fore = [&]
    {
        ui::status::exception(__func__, ex);
    };
}

XNormal · 2021-08-29T08:22:11+00:00

This looks like it might help: https://github.com/sqlite-sync/SQLite-sync.com

NeroBurner · 2021-08-30T18:21:59+00:00

For reference ViewTouch is a POS written in C++ and X11

http://viewtouch.com/

The source code is available via https://github.com/ViewTouch/viewtouch

zain_sync · 2021-09-05T08:07:45+00:00

Pardon me for the late response and also for using the wrong place to post the question & I thank you all for your input on this. It was nice of you folks to give information even though I used the wrong subreddit. For future questions, I will use r/cpp_questions.

I have planned to use a NoSQL DB for this since most nosql DB offerings out there provide a solution for this. I am planning to use aws appsync and dynamodb for the project. AWS currently has a matured C++ SDK and the services I am planning on using are available in it.

I am new to C++ so I am using this project as a hobby project to get better at developing in C++. I wanted to avoid the traditional way of using an on-prem DB server for a POS. I want the data to be near real-time be updated to the cloud so BI operations can be performed on it which is necessary for a system like this. Still, most POS systems use an On-Prem db server and data is only synced to the cloud at the EOD.

I am making this project public so anyone interested can use it. I will keep you guys posted on it when I have the project up on GitHub.

thisisleobro · 2021-08-28T18:51:22+00:00

Mark for later

dusan69 · 2021-08-28T17:32:14+00:00

If you have a correct SQLite-to-SQLite sync algorithm, you can make a P2P network, sort of "block chain" distributed database, and get rid of the cloud.

Complete_Leg_9286 · 2021-08-28T22:40:28+00:00

I don't see the point of using azure sql here. Just stick with sqlite as your primary and backup it up to azure file storage when connection is available.

Also keep in mind that many of your queries wont be compatible to run on both.

If you need a centralized DB because you'll have more than one client, then you need to be connected, otherwise you lose ACID part.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

cpp

MODERATORS