NO

Author Topic: In-memory relational database  (Read 3012 times)

Offline cosh

  • Member
  • *
  • Posts: 33
In-memory relational database
« on: July 18, 2023, 07:37:03 AM »
Hi, there
I recently wrote an in-memory relational database and tested it by using Pelles C.
Here, you could download this project: https://github.com/coshcage/svimrdb
I'll show you guys demo codes here:
Code: [Select]
#include "svimrdb.h"

int main()
{
P_MATRIX pv;
P_TRANS ptrans;
P_TABLE ptbl;

P_ARRAY_Z parrhdr;

parrhdr = strCreateArrayZ(3, sizeof(TBLHDR));
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->ct = CT_INTEGER;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->strname = "number";
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 1))->ct = CT_STRING;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 1))->strname = "name";
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 2))->ct = CT_STRING;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 2))->strname = "Department";

ptrans = siBeginTransaction();

ptbl = siCreateTable(ptrans, "Student", parrhdr);

while (TRUE != siTrylock(ptrans, ptbl, LT_S)) // Share lock.
;

while (TRUE != siTrylock(ptrans, ptbl, LT_X)) // Write lock.
;


siInsertIntoTable(ptrans, ptbl, 2, "Lisa", "CS");
siInsertIntoTable(ptrans, ptbl, 1, "John", "LT");
siInsertIntoTable(ptrans, ptbl, 4, "Amy",  "CS");
siInsertIntoTable(ptrans, ptbl, 3, "Jack", "LT");

siDeleteFromTable(ptrans, ptbl, 3);

siDropTableColumn(ptrans, ptbl, 1);

pv = siCreateViewOfTable(ptbl);

siPrintView(pv);

siDeleteTable(ptrans, ptbl);

// siUnlock(ptrans, ptbl, LT_X);
// siUnlock(ptrans, ptbl, LT_S);

siCommitTransaction(ptrans);

//siRollbackTransaction(&parrtblref, ptrans);

strDeleteMatrix(pv);
strDeleteArrayZ(parrhdr);
siReleaseAllTransaction();

return 0;
}
I haven't finish sql interface now. But you may use relational algebraic functions for searching from the database.
Here is a picture that I was testing the project in Pelles C:

Offline Vortex

  • Member
  • *
  • Posts: 843
    • http://www.vortex.masmcode.com
Re: In-memory relational database
« Reply #1 on: July 18, 2023, 10:51:53 AM »
Hi cosh,

Thanks for your work. Could you upload here your Pelles C example using the library? Thanks.
Code it... That's all...

Offline cosh

  • Member
  • *
  • Posts: 33
Re: In-memory relational database
« Reply #2 on: July 18, 2023, 11:15:38 AM »
OK, I'll package all source files to an attachment.
You may create a new Pelles C ide workspace by your own.
Thank you for your comment! Welcome your feedbacks.
If you want to develop this project with me, please email me. :)

Offline frankie

  • Global Moderator
  • Member
  • *****
  • Posts: 2112
Re: In-memory relational database
« Reply #3 on: July 18, 2023, 11:34:15 AM »
Good.  :)
I'll have a look.  ;)
"It is better to be hated for what you are than to be loved for what you are not." - Andre Gide

Offline MrBcx

  • Global Moderator
  • Member
  • *****
  • Posts: 186
    • Bcx Basic to C/C++ Translator
Re: In-memory relational database
« Reply #4 on: July 18, 2023, 02:44:33 PM »
Not meaning to take anything away from COSH's generous efforts but developers
looking at integrating an in-memory database into their applications should also
be aware of the following capability that's already built-in to SQLITE. 


https://www.sqlite.org/inmemorydb.html


I've tested it in a previously coded BCX app and found it works as advertised. 
I only had to change one line of code in the app.

   rc = sqlite3_open("sq3.db",&dbHandle);     // open a database

to

   rc = sqlite3_open(":memory:",&dbHandle); //  open a database


Bcx Basic to C/C++ Translator
https://www.BcxBasicCoders.com

Offline cosh

  • Member
  • *
  • Posts: 33
Re: In-memory relational database
« Reply #5 on: July 18, 2023, 04:22:05 PM »
Hi, MrBcx
SQLITE is definitely a triumph database system to developers.
While I think svimrdb is a lite version of SQLITE, it is simple to read(but not very simple to program with..).
svimrdb is under developing, if you wanna support it, comment here or give me a thumbs up, I would greatly appreciate you.

By the way, since year 2014, I've introduced BCX compiler to our community. Are you the author of BCX? I would like to thank you.
I was a Visual Basic programmer a long time ago till I found BCX, but I hardly use BCX recently.

Offline MrBcx

  • Global Moderator
  • Member
  • *****
  • Posts: 186
    • Bcx Basic to C/C++ Translator
Re: In-memory relational database
« Reply #6 on: July 18, 2023, 05:56:35 PM »
Hi, MrBcx
SQLITE is definitely a triumph database system to developers.
While I think svimrdb is a lite version of SQLITE, it is simple to read(but not very simple to program with..).
svimrdb is under developing, if you wanna support it, comment here or give me a thumbs up, I would greatly appreciate you.

By the way, since year 2014, I've introduced BCX compiler to our community.
Are you the author of BCX?   I would like to thank you.  I was a Visual Basic programmer
a long time ago till I found BCX, but I hardly use BCX recently.

As I said previously, your generous SV efforts are very much appreciated. 
Here is my thumbs up =^      ;D

My database development has mostly ended since I retired almost 10 years ago but I
understand the many ways that in-memory databases can be useful to programmers.
We all tend to use the tools that we have experience using and that serve our goals.

Re:  BCX ... yes, I am the author and current maintainer.  I am pleased you found BCX
useful in the past and that you promoted it to your community.  BCX continues to receive
regular updates.

Thanks again for your contributions.
Bcx Basic to C/C++ Translator
https://www.BcxBasicCoders.com

Offline cosh

  • Member
  • *
  • Posts: 33
Re: In-memory relational database
« Reply #7 on: May 24, 2024, 08:01:44 PM »
Now svimrdb is dedicated to a high-performance in-memory database.
Compare to sqlite in-memory database svimrdb runs 6 times faster than sqlite.
Here are the codes that I used to test them:
Code: [Select]
#include <stdio.h>

#include "sqlite3.h"


int main() {

    sqlite3 * db;

    char * errMsg = 0;

    int rc;

    //rc = sqlite3_open("test.db", &db);
    rc = sqlite3_open(":memory:", &db);

    if (rc) {

        fprintf(stderr, "%s\n", sqlite3_errmsg(db));

        return 1;

    }

    char * sql = "CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY)";

    rc = sqlite3_exec(db, sql, 0, 0, &errMsg);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "%s\n", errMsg);

        sqlite3_free(errMsg);

        return 2;

    }

    sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, 0);



    for (int i = 1; i <= 10000; i++) {

        char insertSql[100];

        sprintf(insertSql, "INSERT INTO data (id) VALUES (%d)", i);

        rc = sqlite3_exec(db, insertSql, 0, 0, &errMsg);

        if (rc != SQLITE_OK) {

            fprintf(stderr, "%s\n", errMsg);

            sqlite3_free(errMsg);

            sqlite3_exec(db, "ROLLBACK", 0, 0, 0);

            return 3;

        }

    }



    sqlite3_exec(db, "COMMIT", 0, 0, 0);


    sqlite3_close(db);



    printf("done!\n");



    return 0;

}
Code: [Select]
#include "svimrdb.h"
#include <stdio.h>

int main()
{
int i, * p = &i;
P_TRANS ptrans;
P_TABLE ptbl;
P_ARRAY_Z parrhdr;
P_ARRAY_Z parrg;

parrg = strCreateArrayZ(1, sizeof(void *));

parrhdr = strCreateArrayZ(1, sizeof(TBLHDR));
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->ct = CT_INTEGER;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->phsh = NULL;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->cr = CR_PRIMARY_KEY;
((TBLHDR *)strLocateItemArrayZ(parrhdr, sizeof(TBLHDR), 0))->strname = "number";

strInsertItemArrayZ(parrg, &p, sizeof(void *), 0);

ptrans = siBeginTransaction();

ptbl = siCreateTable(ptrans, "Student", parrhdr);

while (TRUE != siTrylock(ptrans, ptbl, LT_S)) // Share lock.
;

while (TRUE != siTrylock(ptrans, ptbl, LT_X)) // Write lock.
;

for (i = 0; i < 10000; ++i)
{
if (TRUE != siInsertIntoTableBase(ptrans, ptbl, NULL, parrg))
{
printf("%s, %d\n", "failed!", i);
siRollbackTransaction(NULL, ptrans);
siReleaseAllTransaction();
return 1;
}
}

siCommitTransaction(ptrans);

siDeleteTable(NULL, ptbl);

siReleaseAllTransaction();

strDeleteArrayZ(parrg);

strDeleteArrayZ(parrhdr);

return 0;
}
svimrdb:
real    0m0.018s
user    0m0.004s
sys     0m0.000s
sqlite:
done!

real    0m0.064s
user    0m0.031s
sys     0m0.000s
 8)
« Last Edit: May 24, 2024, 08:21:00 PM by cosh »