SQLite Database

-=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- (c) WidthPadding Industries 1987 0\|322\|0 -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=-
Socoder -> Off Topic -> SQLite Database
Fri, 29 Mar 2013, 16:27
Cower	I think I’ve decided to go with using SQLite databases as the main format for most of the stuff my engine loads. Easier that way, especially since I can merge them all together into one giant file later.
Fri, 29 Mar 2013, 16:27
Cower	Yep, going with SQLite. Wrote a nice interface to work with it in C++11 and it's quite a bit easier to load data in thanks to it.
Fri, 29 Mar 2013, 16:41
Jayenkai	What!? You're not using PNGs to store data!!? What kind of craziness is this!?!! -=-=- ''Load, Next List!''
Sat, 30 Mar 2013, 02:42
Cower	I'm using PNGs/other images and my own model format for data like that, but for more flexible data that would be a pain to read in/out in a binary format, DBs are admittedly a lot better. All I really have to do is open a DB, pass it to something to read in data, and then go on using the DB for whatever I want. No need to worry about file position or alignment or endianness (of non-blobs, anyway), since SQLite handles it all. Basically, it's a matter of avoiding what would be a huge pain. I had four options, more or less, for a lot of data: JSON, Sparse (my own format), some proprietary binary format I haven't created yet, or databases. Turns out databases are smaller than JSON, easier to parse than both JSON and Sparse (though Sparse is very fast to parse), and the binary format doesn't really win anything because I can't just read it in due to alignment issues, endianness, etc., again. Also, for an example, this is a screenshot of a font loaded in from an sqlite database. Linked because it's enormous. The loading code itself is only about 80 lines, not counting some variable declarations and checking to make sure the database is open.
Sat, 30 Mar 2013, 06:26
Afr0	You're going with SQLite as the... main format for most of the stuff your engine loads? Care to explain? o.O Are you going to store all your data uncompressed and then use *.sqls to map them? Or are you going to load stuff into a SQLite DB in memory? That makes sense, but how do you store stuff on disk? ... thanks for creating this topic. It's the most interesting topic we've had for ages, as Socoder seems to have debased into some kind of Learning With Monkey/NSFW/Technological News hybrid. Hardly any coding topics anymore, certainly not any good/challenging ones... -=-=- Afr0 Games Project Dollhouse on Github - Please fork!
Sat, 30 Mar 2013, 12:19
Cower	The databases are kept as a series of database files. It's not stored as an SQL file or kept in memory, though I can create temporary in-memory DBs. The in-memory DBs aren't really useful except for probably some odd cases, like cvars (since SQLite columns are dynamically typed, it makes it easy to just keep a single cvar table of string keys and arbitrary values). The advantage is that database files can be arbitrarily merged, so I can reasonably sort things as needed. Since all resources are accessed over read-only DB "connections" (term loosely used since a connection is just disk access to the DB file), it's feasible to create DB connections as needed and pass those off as needed for reading data. For an example of a DB file, this is a font database I've used in Snow for testing: dl.dropbox.com/u/31892/snow/fonts.db You can open it using sqlite3, etc. The comparable JSON files are 1.1MB when minified, the database is only 569kb. A single ASCII JSON font file will be about 6kb less than the database. A single JSON file of a variety of Unicode characters is much larger, mainly due to the kernings map. The same fact about kernings also applies to the database, though it obviously scales better. In all practicality, I don't intend to support more than a few ASCII fonts (zero support for non-English character sets, really, since I don't need them - if I did, I'd load non-English glyphs on demand), so this ends up working out fairly well for the few fonts I do need. For an idea of how loading something from one of the DBs works, this is the font loading code: rfont_t::rfont_t(database_t &db, const string &name) : name_(name) { if (!db.is_open()) { throw std::invalid_argument("Database is not open"); } // To save on error handling code, let the DB throw if something bad happens bool throw_reset = db.throw_on_error(); db.set_throw_on_error(true); try { int font_id = INT_MIN; // Font row ID in the database vec2f_t page_scale = vec2f_t::one; // Look for font info for the given name, throw exception if none found { dbstatement_t info_query = db.prepare(font_info_query_string); info_query.bind_text_static(1, name); info_query.execute([&](dbresult_t &fir) { font_id = fir.column_int(KFONT_ID); line_height_ = fir.column_float(KLINE_HEIGHT); leading_ = fir.column_float(KLEADING); ascent_ = fir.column_float(KASCENT); descent_ = fir.column_float(KDESCENT); bbox_min_ = { fir.column_float(KBBOX_MIN_X), fir.column_float(KBBOX_MIN_Y) }; bbox_max_ = { fir.column_float(KBBOX_MAX_X), fir.column_float(KBBOX_MAX_Y) }; pages_.resize(fir.column_int(KPAGES)); page_size_ = { fir.column_int(KPAGE_WIDTH), fir.column_int(KPAGE_HEIGHT) }; page_scale = page_size_.inverse(); }); } // Crap pants if no font found. if (font_id == INT_MIN) { throw std::invalid_argument("No font with given name found"); } // Load glyphs { dbstatement_t glyph_query = db.prepare(font_glyph_query_string); glyph_query.bind_int(1, font_id); glyph_query.execute([&](dbresult_t &fgr) { rfont_t::glyph_t glyph = { fgr.column_int(KPAGE), { fgr.column_float(KFRAME_X), fgr.column_float(KFRAME_Y) }, vec2f_t::zero, { fgr.column_float(KFRAME_WIDTH), fgr.column_float(KFRAME_HEIGHT) }, { fgr.column_float(KADVANCE_X), fgr.column_float(KADVANCE_Y) }, { fgr.column_float(KOFFSET_X), fgr.column_float(KOFFSET_Y) } }; // Round the glyph frame to its pixel edges glyph.uv_max = glyph.uv_min + glyph.size; glyph.uv_min.x = std::floor(glyph.uv_min.x); glyph.uv_min.y = std::floor(glyph.uv_min.y); glyph.uv_max.x = std::ceil(glyph.uv_max.x); glyph.uv_max.y = std::ceil(glyph.uv_max.y); glyph.size = glyph.uv_max - glyph.uv_min; glyph.uv_min = page_scale; glyph.uv_max = page_scale; glyph.uv_min.y = 1.0f - glyph.uv_min.y; glyph.uv_max.y = 1.0f - glyph.uv_max.y; std::swap(glyph.uv_min.y, glyph.uv_max.y); glyphs_.insert({ fgr.column_int(KCODE), glyph }); }); } // Load kernings { dbstatement_t kern_query = db.prepare(font_kern_query_string); kern_query.bind_int(1, font_id); kern_query.execute([&](dbresult_t &fkr) { kerns_.insert({ // First/second glyph pair { fkr.column_int(KFIRST_CODE), fkr.column_int(KSECOND_CODE) }, // Kerning for glyph pair fkr.column_float(KAMOUNT) }); }); } db.set_throw_on_error(throw_reset); // end try } catch (std::exception &ex) { // Restore previous throw_on_error state for the DB then continue the throw db.set_throw_on_error(throw_reset); throw; } } // rfont_t::rfont_t() --v
Sat, 30 Mar 2013, 18:16
Afr0	The databases are kept as a series of database files. It's not stored as an SQL file What, what? You designed your own format for use with SQLite? -=-=- Afr0 Games Project Dollhouse on Github - Please fork!
Sat, 30 Mar 2013, 18:21
Cower	SQLite's format is databases, not SQL files.
Sun, 31 Mar 2013, 05:59
Afr0	SQL files are databases...
Sun, 31 Mar 2013, 10:52
Cower	No, they aren't. An SQL file is a series of SQL statements in text.
Mon, 01 Apr 2013, 02:03
Afr0	I only ever worked with SQLite in memory, that's probably why. -=-=- Afr0 Games Project Dollhouse on Github - Please fork!