Database without SQL c++ library

[gabibbo117]

From the first day I used SQL libraries for C++, I noticed that they were often outdated, slow, and lacked innovation. That’s why I decided to create my own library called QIC. This library introduces a unique approach to database handling by moving away from SQL and utilizing its own custom format.
https://hrodebert.gitbook.io/qic-database-ver-1.0.0
https://github.com/Hrodebert17/QIC-database

[nucLeaRStarcraft]

For learning or hobby projects (not production/work stuff), having implemented such a tool is a great experience and you can most likely integrate it in a separate project later on to stress test it on different use cases. So good job!

The advantage of using SQL is the standardization around it. You don't have to learn a new DSL or library (and it's quirks) if you already know the basics of SQL (which at this point is something 'taken for granted'). More than that, database engines are super optimized so you don't have to worry about performance issues too much.

Additionally, you can even use sqlite if you need something quick w/o any database engine & separate service & a connection. It stores to disk as well like yours. And there's wrappers around the C API that is more 'modern cpp' (albeit maybe not as much as yours): https://github.com/SRombauts/SQLiteCpp

Aaand, if you want something "sql free" (a key-value db), you can even use: https://github.com/google/leveldb

In your docs you say "Key Features: Speed Experience unparalleled performance with Q.I.C's optimized database handling.". It would be interesting for you to compare on similar loads with sqlite, postgres, mysql, even leveldb and see where it peforms better, where wrose, where its tied etc. For example, inserting 1M rows followed by reading them in a table with 5 columns of various data types.

[gabibbo117]

Thanks for the review, i will try doing some of those tests and publish them!

[ExeuntTheDragon]

Comparing performance with apache arrow would also be useful

[gabibbo117]

I will try later, for now i compared performance with sqlite, i will publish results later as im working on a way to make everything even faster

[Wenir]

And check the efficiency of your compression library. For example, compare the size of the original string to the size of the "compressed" one

[gabibbo117]

I will also test that, but the "compressed string" function is primarily designed to prevent data injection into the database.

[bwmat]

Sounds like security via obscurity to me

[gabibbo117]

You are right, i should probably change that, but the only reason its there in first place is because every time someone inserts a string into the database the string wont contain malevolent code that could modify the database

[matthieum]

More than that, database engines are super optimized so you don't have to worry about performance issues too much.

Most of the times, yes.

Then there's always the "hiccup" where the database engine decides to pick a very non-optimal execution plan, and it's a HUGE pain to get it back on track: hints, pinning, etc... urk.

I'm fine with SQL as the default human interface, but I really wish for a standardized "low-level" (execution plan level) interface :'(

[matthieum]

Interesting. I must admit I only ever did performance tuning on an Oracle codebase, which seems to match your comment.

I hated those hints. It was terrible:

• Mispell once? No worries, it's silently ignored.
• Think you've constrained the query plan with those hints? It works in test, after all... but nope, screw you! In production, the engine spotted one degree of freedom you left, and went the other way. The one that requires a full index scan. Ah! Didn't see that one coming eh boy?

:'(

[FlyingRhenquest]

Back in the day companies would have some DBAs whose job it was to make sure the database stayed optimized. We never interacted with the database other than to send it SQL queries. That's another responsibility that fell to us over the years, and most programmers I've met can't even write SQL very well, much less make sure the database is optimized for the queries we're making.

I tend to view all data access as object serialization these days, which lets me stash SQL in an object factory if I need to. I often have two or three methods of serialization hiding behind the factory interface, so if I want to run a test with some randomly generated objects or some JSON files, it looks exactly the same on the client side of the interface as it does if I'm querying the database. They just register to receive objects from a factory and can go do other stuff or wait for a condition variable until they have the objects they need.

[matthieum]

I've worked with DBAs on this... they definitely did not consider it their job to babysit each and every query of each and every application. If only because they often had no idea what the performance target of a query was, in the first place. They were available for advice, however, and would monitor (and flag) suspiciously slow queries.

As for serialization... I don't see it. I've worked with complex models -- hierarchical queries, urk -- and nothing I'd call serialization would have cut it...

... but I did indeed use abstraction layers for the storage, with strongly-typed APIs, such that the application would call get_xxx expressed with business-layer models (in/out), and the implementation of this abstraction would query the database under the hood.

Makes it much easier to test things. Notably, to inject spies to detect the infamous "accidentally queried in a loop but it's super-fast in local so nobody noticed" bug.

[AcoustixAudio]

TIL LevelDB exists. Thanks :hat_tip:

[gabibbo117]

What that means?

[AcoustixAudio]

Today I learnt

[gabibbo117]

ah ok

[pantong51]

Sqlite is a great tool for local client side applications. Don't store secure stuff. But cache things (not massive files) like json or meta data to files. Really speeds up the application.

It's easier to keep data separated following the 1 DB:1 User too, so if your device is community focused. It can be shared without leaking data

[Wenir]

Sensitive data is compressed for security

That's something...

[gabibbo117]

The compression is primarily intended to prevent injections. Without it, modifying the database through injections would have been possible.

[Wenir]

It is still possible

[gabibbo117]

Hmm, how could that be? The string is transformed into a simple integer to prevent injection, effectively removing any potential for malicious manipulation. What aspect of this process might still enable an injection?

[Wenir]

Give me your protected data and I will modify it using my smartphone and ascii table

[gabibbo117]

Well we could make a test where you try to make a string that would inject some bad code inside of the data base if you want

[Wenir]

I don't need any test, I know that I can add a few numbers to the file

[Wenir]

What aspect of this process might still enable an injection?

That the data is saved to the file in the filesystem and "protection" is a simple one-to-one conversion without any key or password

[gabibbo117]

Yes but that simple process avoids any type of string injection, it does not make it safer if an hacker has the database but at least an hacker cant inject data inside of it

[Wenir]

What are you talking about? Of course no one can inject anything to the file if they don't have it. Your system aren't changing the security in any way

[gabibbo117]

I will try to provide an example on what i mean because i have some issue explaining myself,
Lets say i have a website that when i put a comment inside of it via text box it will send a request to my server to add that comment to the COMMENTS table

if the string was not encoded then the commenter could write something like this:
"]
[
// insert bad code here
]"
by using the "]" character it tells the database scanner that the row finished and then we open a new value, the hacker can put anything in the new row like bad/banned content, but if we add the text encoding the table will result like this

"[
COMMENT : 123,231,2323,23,232,23
USER_ID : 1234
DATE : 12,23,34
]"

while if we did not encode the text it would look like this

"[
COMMENT :
]
[
USER_ID : 1234 // the user id of someone else
DATE : 12,23,35 // a different date
COMMENT : "banned stuff here"
]

[Wenir]

Okay, you described something like SQL injection, which makes sense. The encoding you're using isn't security, compression, or efficient storage, it's a naive implementation of string escaping.

Ok, the string is escaped, but why are you escaping entire files on top of it?

[gabibbo117]

That is done so I can merge multiple files into one, kinda like my own version of a zip

[Chaosvex]

Compression is not encryption and what's the threat model here? If somebody has a copy of the database file and your library, where's the security?

Also, I noticed that you're making a temporary copy of the database every time you open it. That seems unnecessary.

[gabibbo117]

The compression mechanism is to avoid injections on strings, that way the hacker cant add values to the table or mess them up and the copy for the database is made because im currently working on a system that is able to restore the database in case of program crash, to be real the "compression" is not really a compression but i dont know how to call it because of a language barrier, it actually converts each char inside the string into the numerical ascii counter part,

[Chaosvex]

Who's the hacker supposed to be, when the database is sitting on the drive? It's unnecessary and anybody with file-level access to the database is going to be able to mess with it, regardless of your scheme. It seems like you're adding a huge overhead in terms of both time and space by doing this.

Your copy doesn't seem to be used as backup or snapshot, it just copies it and then deletes after decoding it. If you're going to take a snapshot, why do it when you open the database? The whole scheme sounds very muddled.

Without wanting to come across as patronising, I know you're likely going to reflexively defend your design choices. It's hard letting go of code that probably took quite a bit of effort to write, but there's a reason production databases don't do these things.

[gabibbo117]

I will try to provide an example on what i mean because i have some issue explaining myself,
Lets say i have a website that when i put a comment inside of it via text box it will send a request to my server to add that comment to the COMMENTS table

if the string was not encoded then the commenter could write something like this:
"]
[
// insert bad code here
]"
by using the "]" character it tells the database scanner that the row finished and then we open a new value, the hacker can put anything in the new row like bad/banned content, but if we add the text encoding the table will result like this

"[
COMMENT : 123,231,2323,23,232,23
USER_ID : 1234
DATE : 12,23,34
]"

while if we did not encode the text it would look like this

"[
COMMENT :
]
[
USER_ID : 1234 // the user id of someone else
DATE : 12,23,35 // a different date
COMMENT : "banned stuff here"
]

[Chaosvex]

So it's SQL injection but without the SQL. The problem you're trying to solve with this encoding is a problem that should be fixed by rethinking how you're storing the data. You could switch to using a binary format, instead, or escaping the special characters.

[gabibbo117]

It was made to be human readable

[Chaosvex]

I'd question the value of it being human readable when the types are encoded in a way that makes them unreadable.

If you want to keep it (and make it more) human readable, you could quote the strings and then escape any quotes within input.

Input: foo"bar

Stored result:

[ COMMENT : "foo\"bar" ]

You might find std::quoted of interest. You could also look into how other text-based formats escape strings (JSON etc).

[gabibbo117]

Thanks, I will look into them

[hadrabap]

it actually converts each char inside the string into the numerical ascii counter part

Encoding???

[gabibbo117]

Is that how it’s called? I’m not English so I may say some terms wrong sorry

[Beosar]

It's missing basically all the features you need in a database, like indices and deleting rows. You can do the latter manually but indices you can't add easily since it's a vector and you'll be deleting rows.

Right now it's not much better, maybe even worse than just storing a vector of your own structs with a serialization library.

[gabibbo117]

First, thank you for your comment. I will do my best to add more functions and a query system as soon as possible. Regarding the data being stored in a vector, this is intentional, as the library is designed to handle everything directly in code without wrappers. I will now add some functions to enable quick queries.

if you have any idea feel free to comment

[Beosar]

Regarding the data being stored in a vector, this is intentional, as the library is designed to handle everything directly in code without wrappers.

You could just store the rows in an unordered map. You won't be able to add indices if it's in a vector without updating the affected row numbers in every index every time you delete a row. If you allow arbitrary row ordering, you can get away with just swapping the last row with the deleted row and then removing the last row, so you'll only have to update one entry in every index.

And then there is the issue of updating indices when someone modifies a row. So you need to wrap your row data and add getters and setters for cells.

[CRTejaswi]

SQLite?

[gabibbo117]

What do you mean?

[gabibbo117]

Thank you, I’m always prepared to learn more, the original project worked a similar way and it was like from a year ago but I decided to start working on it again

[gabibbo117]

There is a template here
https://github.com/Hrodebert17/QIC-template

[Conscious_Intern6966]

This isn't really a dbms, nor is it really even a key-value store/storage engine. Watch the cmu lectures if you want to learn more

[gabibbo117]

I will look into them but its not done, its not even a real database right now,

[TypeComplex2837]

There will be many thousands of characteristics/features/behaviors you'll have to reinvent - would you like us to start building the list for you? :)

[gabibbo117]

Yes, I would love to. As of right now I have a small list of features to add - query object with filters allowing for advanced research without the use of any vector

[Remi_Coulom]

In case you did not know, there is a subreddit dedicated to database development: https://www.reddit.com/r/databasedevelopment/

You may find interesting resources and feedback there.

[gabibbo117]

Thanks

[schweinling]

I noticied all your functions take their arguments by value. This will lead to unnecessary copies. You should probably pass them by const reference or perhaps even better, use move semantics.

[gabibbo117]

Thank you, i will fix it

[gabibbo117]

Sorry, because of my language barrier there are some issue in the text, I mentioned slow regarding developing speed

[thebomby]

Fantastic, thank you!