I'm still using flat files on my code base which I plan on keeping, but I would like to insert data into a sqlite database for use elsewhere. I write a lot of database code, but never from C. I have the code below which works but as this is my first time writing to a sqlite db from a C program I wanted a sanity check it in case anybody can see any blatant errors I've made (leaks, poor logic, etc.).

Thanks for the advice. After I wrote this I actually changed this instance to use the area->vnum instead of the name as the key (it's assigned dynamically based on load order but since this would reload game data from scratch everytime any queries would continue to work unless they reference that key value specifically). Normally I'd join on key but query on the name.

Once I started loading the various flag tables in and joining things up I'm quickly find missing pieces.

I think I'm going to split the bits for things like extra_flags out into foreign key tables as that would be the best relational way to do it, but sqlite also supports bitwise searches which means the object could be queried much with a syntax much like IS_SET which is kind of cool.

And of course, extra_flag_types would just be a two column table of extra_flag_id being the bit number, and extra_flag being the text name of it. If you had the bit numbers, you'd then be using extra_flag_id instead. :)

Thanks for the advice. You've got good thoughts, and the more I've plugged away at and queried the data as I've finished pieces the more I've updated the schema to be relational (object table links an object_affect, etc.). I've kept the int values for the flags in the tables but started busting each one out into a foreign key table also.

I think I'm going to leave the sqlite specific SQL if only because there won't be a ton and I assume if I were to ever change platforms that the most cumbersome part would be updating all of the C code that would have to change.

It's working well so far (I like that sqlite is self contained and easily usable from anywhere without a lot of setup for someone).

Of course, to many developers actual standard SQL feels surprisingly limited after developing on any mature RDBMS for a while. For that matter, as far as I know no actual RDBMS fully implements any version of the standard in the first place.

Another common approach, if you want the ability to migrate arbitrarily between RDBMS systems, is to try to shoehorn some kind of suitable abstraction layer (or even an ORM when outside of C/C++) in between the database calls and the main program logic. This gives you the ability to address the syntactic and behavioral incompatibilities between different database systems in a single place instead of in each SQL query sprinkled throughout your code. We could debate the merits of either approach endlessly – neither is one-size-fits-all.

To just scratch the surface, consider that at a very basic level sqlite is dynamically typed, postgres is fairly strictly statically typed, and mysql/mariadb is hovering somewhere in between. Or the syntactic conflict of mysql/mariadb's AUTO_INCREMENT vs sqlite3's AUTOINCREMENT vs postgres SERIAL; you often don't want to work out that logic each time that your code needs to CREATE TABLE. Or even the nasty surprises that can spring up when you migrate your sqlite or postgres project complete with carefully-constructed CHECK constraints over to mysql/mariadb, only to realize later that CHECKs are syntactically accepted but otherwise ignored by those systems and your tables have been silently filling with garbage since you introduced that subtle bug 3 weeks ago.

If I were running all of the game data off of these tables I would be concerned with most of what you brought up because they're good thoughts. Currently, I'm only exporting the game tables and data (areas, objects, help files, etc.) so they can be easily consumed from something like a web interface. I'd like to give someone a leg up on integrating their game data with a modern web site (I didn't see much of this type of snippet available).

If I were writing in C# the data layer could be easily abstracted with interfaces and a middle tier ORM that does most of the leg work of moving between providers. This IMO is complicated by C (and although I <3 C# there isn't enough time available for me to port ROM to it so I'm sticking with the one that brung me). I hate null terminated character arrays but I love ROM. What can I say. Hehe.

You could write a database layer in the language of your choice (perl, python, C#, whatever) and have it listen on a localhost-only socket. Then have your MUD connect to that socket and send requests for data to that script, which would collect the data and send it back using JSON or XML or whatever you can parse easily on the C side.

If you used a proxy like that, you would only change the proxy when you switched databases, and your web clients could also talk to the proxy to get their data.

It would, of course, be more work… but it wouldn't be database handling in C, just the same kind of file I/O, but over a socket instead of a file descriptor.