How to write a simple database engine [closed]

Question

If you're good at reading code, studying SQLite will teach you a whole boatload about database design. It's small, so it's easier to wrap your head around. But it's also professionally written.

http://sqlite.org/

The answer to this question is a huge one. expect a PHD thesis to have it answered 100% ;) but we can think of the problems one by one:

• How to store the data internally: you should have a data file containing your database objects and a caching mechanism to load the data in focus and some data around it into RAM assume you have a table, with some data, we would create a data format to convert this table into a binary file, by agreeing on the definition of a column delimiter and a row delimiter and make sure such pattern of delimiter is never used in your data itself. i.e. if you have selected <*> for example to separate columns, you should validate the data you are placing in this table not to contain this pattern. you could also use a row header and a column header by specifying size of row and some internal indexing number to speed up your search, and at the start of each column to have the length of this column like "Adam", 1, 11.1, "123 ABC Street POBox 456" you can have it like <&RowHeader, 1><&Col1,CHR, 4>Adam<&Col2, num,1,0>1<&Col3, Num,2,1>111<&Col4, CHR, 24>123 ABC Street POBox 456<&RowTrailer>

• How to find items quickly try using hashing and indexing to point at data stored and cached based on different criteria taking same example above, you could sort the value of the first column and store it in a separate object pointing at row id of items sorted alphabetically, and so on

• How to speed insert data I know from Oracle is that they insert data in a temporary place both in RAM and on disk and do housekeeping on periodic basis, the database engine is busy all the time optimizing its structure but in the same time we do not want to lose data in case of power failure of something like that. so try to keep data in this temporary place with no sorting, append your original storage, and later on when system is free resort your indexes and clear the temp area when done

good luck, great project.

There are books on the topic a good place to start would be Database Systems: The Complete Book by Garcia-Molina, Ullman, and Widom

SQLite was mentioned before, but I want to add some thing.

I personally learned a lot by studying SQlite. The interesting thing is, that I did not go to the source code (though I just had a short look). I learned much by reading the technical material and specially looking at the internal commands it generates. It has an own stack based interpreter inside and you can read the P-Code it generates internally just by using explain. Thus you can see how various constructs are translated to the low-level engine (that is surprisingly simple -- but that is also the secret of its stability and efficiency).

Okay, I have found a site which has some information on SQL and implementation - it is a bit hard to link to the page which lists all the tutorials, so I will link them one by one:

• http://c2.com/cgi/wiki?CategoryPattern
• http://c2.com/cgi/wiki?SliceResultVertically
• http://c2.com/cgi/wiki?SqlMyopia
• http://c2.com/cgi/wiki?SqlPattern
• http://c2.com/cgi/wiki?StructuredQueryLanguage
• http://c2.com/cgi/wiki?TemplateTables
• http://c2.com/cgi/wiki?ThinkSqlAsConstraintSatisfaction

I would suggest focusing on www.sqlite.org

It's recent, small (source code 1MB), open source (so you can figure it out for yourself)...

Books have been written about how it is implemented:

http://www.sqlite.org/books.html

It runs on a variety of operating systems for both desktop computers and mobile phones so experimenting is easy and learning about it will be useful right now and in the future.

It even has a decent community here: https://stackoverflow.com/questions/tagged/sqlite

may be you can learn from HSQLDB. I think they offers small and simple database for learning. you can look at the codes since it is open source.