Login
User Name:

Password:



Register
Forgot your password?
Vote for Us!
auth_update crash
Dec 23, 2017, 10:15 pm
By Remcon
check_tumble
Dec 18, 2017, 7:21 pm
By Remcon
parse description bug
Dec 15, 2017, 10:08 pm
By Remcon
Couple bugs
Dec 12, 2017, 5:42 pm
By Remcon
Bug in disarm( )
Nov 12, 2017, 6:54 pm
By GatewaySysop
LoP 1.46
Author: Remcon
Submitted by: Remcon
LOP 1.45
Author: Remcon
Submitted by: Remcon
LOP Heroes Edition
Author: Vladaar
Submitted by: Vladaar
Heroes sound extras
Author: Vladaar
Submitted by: Vladaar
6Dragons 4.3
Author: Vladaar
Submitted by: Vladaar
Users Online
CommonCrawl, Yandex, Yahoo!

Members: 0
Guests: 5
Stats
Files
Topics
Posts
Members
Newest Member
478
3,708
19,242
612
Jacki72H
Today's Birthdays
There are no member birthdays today.
Related Links
» SmaugMuds.org » General » Coding » SQL API
Forum Rules | Mark all | Recent Posts

SQL API
< Newer Topic :: Older Topic >

Pages:<< prev 1, 2 next >>
Post is unread #1 Aug 27, 2008, 6:48 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

I've been puttering around with my own hand-coded SQL interfaces for a few years now, and keep thinking of integrating them into SmaugFUSS. However, my patience level with C gets a little lower with each passing year, so I haven't made much progress. :)

So, a while back I sat down and started trying to write some C code to use ODBC, rather than having to choose between PostgreSQL and MySQL. For those who haven't dealt with ODBC, it's a very grumpy creature when used from C... very grumpy indeed.

The other day, it occured to me that I had two other options available. I could use SQLite, which is an embedded database that stores all its data in a single file. The plus side there is you don't need to do any setup to use it, and as long as you have the disk space, it should work on any hosting service (since it's compiled in). The minus side is that the database is not as flexible as a full database engine, and thus it's a little harder to do reports and other things you'd want to do with all your data in SQL.

Option #2 is to find (somewhere) a decent C++ API for ODBC. Since SmaugFUSS compiles using g++, it seems like it wouldn't be a huge stretch to use a C++ API, which would make life a lot simpler than the C API does. Spending a little time with Google yesterday, I found many API's that were undocumented or had only rough auto-generated doxygen excuses for documentation, and several that worked but lacked features I considered important (like being able to bind parameters).

Thus, I figured I'd ask.... does anyone have a C++ ODBC layer they particularly like? It has to be non-commercial, although GPL/BSD type licensing is probably fine (Smaug is more restrictive, but as long as the source is availalbe, GPL/LGPL should still work).

If not, does anyone else out there use SQLite? It seems like a nice elegant solution, and I suspect you could export your data for uploading into a larger RDBMS to do analysis on it, if you felt the need.
       
Post is unread #2 Aug 27, 2008, 6:52 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

I know several people who use SQLite and are happy with it. I'm not sure that a MUD would generate enough data to truly warrant a full DBMS. Out of curiosity what kind of reports are you talking about?

I'm a little surprised that there aren't more libraries that do this kind of thing. Perl has DBI for instance that provides a uniform API to many different databases. Surely there is a fairly major equivalent in C++? Haven't looked myself, though...
       
Post is unread #3 Aug 27, 2008, 7:59 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

The kind of reports I was thinking of are things like:

Show me the top 10 mobs, and the zones they belong to, that were killed by players that were <= mob.level - 5, and whose gold reward was > player.level * 100 or whose xp reward was > mob.level * 100.

Show me the top 10 players who have the maximum time online, but the minimum idle time. (addicts)

Find pairs of player.logout times and player login times where the difference is less than 5 seconds and it happens more than three times in a 24 hour period. (multi-players)


When doing game balance, being able to have a few dozen canned queries like that, and being able to make queries on the fly when you get interesting results, would make the process an order of magnitude simpler than guessing or trying to squint through log files.

Obviously, you still have to put in the code to store the event data in the database, but it's the power to join all those events with the object data and paw through them that makes it worth the trouble, IMHO. :)

Yeah, I worked with perl/postgres for about 5 years, and DBI is a good thing. We actually created a table interface that let you interface to the database tables as objects for select/update/insert, although if you made a query that involved multiple tables or views, you couldn't do write operations.

I was happy with sqlxx until I read far enough to see that it doesn't support parameter binding. There is one called Simple C++ ODBC, but it hasn't been updated since 2006 and lacks support for column types beyond "long int" and "text". Libodbc++ looks promising, but their idea of documentation is to put the doxygen-generated comments on a web site and say "Use the source Luke!"

Having used DBI (and a typeless language), what can I say, I'm spoiled.
       
Post is unread #4 Aug 27, 2008, 8:37 am   Last edited Aug 27, 2008, 8:37 am by David Haley
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

Oh -- you're talking about storing more than just the data, then, right? You're also storing game activity. I didn't realize that in my previous post, so maybe you are generating enough data to warrant a proper DBMS. I agree that those are pretty interesting queries, though!

Sorry to not be more helpful about a C++ API -- the work I've done with databases was all in Perl and similar languages, with only a small bit of MySQL work in C++.
       
Post is unread #5 Aug 27, 2008, 9:28 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

No problem! I'm just fishing in the hopes that someone out there has already found a good C++ API, so I don't have to download and try ALL of them. :)

I like the SQLite concept, the only things that keep me from just using that are the fact that it has squishy typing (you can declare in integer column, but then go ahead and stuff a string into it -- tends to promote subtle runtime errors, especially if your language isn't squishy), and the lack of foreign key constraints.

I have a nice fat (albeit OLD) schema I did for AFK 1.6 that is chock full of FK constraints, as they make error checking so much simpler (and it works for people inserting data outside the game too).
       
Post is unread #6 Aug 27, 2008, 9:48 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

"Squishy" -- I'd never heard it phrased that way, but I like it. :lol: I agree that it sounds a little dangerous to declare as one type and allow other types. If you're going to allow type declaration, you should stick to it...

When you have all this data in a DB, do you cache it in memory at all, i.e. do you treat the DB like the code currently treats a file and load it all into memory -- or do data requests entail DB reads?
       
Post is unread #7 Aug 27, 2008, 10:56 am
Go to the top of the page
Go to the bottom of the page

Samson
Black Hand
GroupAdministrators
Posts3,639
JoinedJan 1, 2002

Quixadhal said:

Thus, I figured I'd ask.... does anyone have a C++ ODBC layer they particularly like? It has to be non-commercial, although GPL/BSD type licensing is probably fine (Smaug is more restrictive, but as long as the source is availalbe, GPL/LGPL should still work).

If not, does anyone else out there use SQLite? It seems like a nice elegant solution, and I suspect you could export your data for uploading into a larger RDBMS to do analysis on it, if you felt the need.


The only layers I've ever run into that looked complete enough to actually use as-is were all made for MySQL specifically. The big problem with 99.9% of them was that they're GPL licensed which means you can't distribute them with a Diku derivative. That meant either finding something that didn't come with GPL's baggage, or writing a layer that worked from scratch. Zarius provided such a layer for AFKMud and it's been in the 2.0 branch since release. Although nothing actually uses it except the helpfile snippet. It's probably not what you're after for ODBC support though :)
       
Post is unread #8 Aug 27, 2008, 11:31 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

Samson said:

which means you can't distribute them with a Diku derivative

Well, you could always not distribute it and list it as a dependency that people have to go get themselves, but yeah, that's not terribly convenient.
       
Post is unread #9 Aug 27, 2008, 11:54 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

Not to open this can of worms again, but I was under the impression that GPL simply required that all code which was derived from GPL'd code be made available, and that no restrictions were placed upon the use of said derived code.

If you use a chunk of GPL'd code in a non-GPL licensed project, it does NOT make the entire project GPL.

I've seen this argument come up many times, in many different forums. The end of the threads almost always has people with legal experience stating that using a GPL library doesn't mean you have to publish your whole project as open source. It's only for derived works that the license becomes viral.

IANAL, so feel free to tell me I'm full of it. :stare:
       
Post is unread #10 Aug 27, 2008, 12:04 pm
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

The derived works are for sure, but my understanding is that as soon as you want to distribute your project along with the GPL code, things change. You don't have to make something open source if you use GPL as long as you're not distributing it. Or something like that. The LGPL is supposed to address that, if memory serves.
       
Post is unread #11 Aug 27, 2008, 12:14 pm
Go to the top of the page
Go to the bottom of the page

Samson
Black Hand
GroupAdministrators
Posts3,639
JoinedJan 1, 2002

It's a small distinction, but yes. My understanding of it after numerous legalese threads and people with "legal training" commenting on it all seemed to agree on the basics:

You can't distribute your code with GPL based code included without the whole thing being under the GPL. The viral thing.
You can distribute your code along with LGPL libraries and only the library is affected by the LGPL license.

So if the ODBC code is LGPL you should be fine. If it's straight GPL, you'll need to do as David suggests and package them for separate download with dependencies in your main package. A pain in the butt, but this is what all the lawyering has accomplished.

Best bet? Just find a package licensed under BSD instead. Less hassle, and no problems with distribution along with Diku licensed code.
       
Post is unread #12 Aug 27, 2008, 12:30 pm
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

/agreed there.

BSD is pretty simple and straightforward.
       
Post is unread #13 Aug 27, 2008, 12:55 pm   Last edited Aug 27, 2008, 12:56 pm by Kayle
Go to the top of the page
Go to the bottom of the page

Kayle
Off the Edge of the Map
GroupAdministrators
Posts1,195
JoinedMar 21, 2006

I don't know about all the different types of Databases, it's honestly not really my cup of tea. But Zarius did give me permission to use his SQL stuff from AFKMud in my base. And I've got to say, that even though like AFKMud I only currently use it for helpfiles, it does it's job in that department. It is just for MySQL as far as I can tell though, So like Samson said, it might not be exactly what you're looking for, but it could be a good start for writing your own, perhaps?

[Edit:] As a side note, my grouping seems to have changed again. :P Yay me? maybe.
       
Post is unread #14 Aug 27, 2008, 2:00 pm
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

I do remember taking a look at that back when it was in AFK, I think even before 2.0.

The only issue I had was that it was coded for MySQL in such a way that most of the database logic remained outside the sql files themselves. IE: Doing a query only returned you one of MySQL's query objects, which you then had to use through the native API.

I wanted to keep all the database specific stuff in sql.c, as much as possible. So, you'd call a routine to get a row of data and get back a structure with that data. That way, moving to some other database would only require changing sql.c and possibly your queries themselves.

I did a PostgreSQL interface, which worked, but while trying to work in MySQL along the same lines I got disgusted and tried to do ODBC. ODBC requires parameter binding (there is no quoting mechanism), and that is very hard to do for a generic interface in C -- because you don't know the types of the data you're being sent, and in C... you have to.

Soooooo, I have a mostly working PgSQL setup with some MySQL hooks. I have a 75% working ODBC module that is just painful to work on (case statements for column types, anyone?). I figured a C++ API would be able to work around many of these issues, since you can identify the types you're being sent and bind columns appropriately. Apparently, it's still a pain though. :)

No worries, it's not like I have a deadline or anything.
       
Post is unread #15 Aug 27, 2008, 3:16 pm
Go to the top of the page
Go to the bottom of the page

Caius
Magician
GroupMembers
Posts132
JoinedJan 29, 2006

I wrote a simple wrapper library about a year ago. It's fairly well encapsulated, so you don't use any of the native API. It's got support for Sqlite2, Sqlite3, MySQL and PostgreSQL, all using the same API. I wrote it for my specific needs which were, and still are, relatively simple. It's in C++. It may not be what you need, but doesn't hurt to take a look. You can find it on SourceForge. It's released under the MIT license, so there's hardly any restrictions on how you use it. Also, it's very simple to use.

I've used it extensively for about a year, and there's only one bug that I'm aware of, and that's easily worked around (You can't reuse a result object, it's not cleaned up properly. I've been too lazy to fix it). I have not done any rigorous performance testing, so I make no guarantees.
       
Post is unread #16 Aug 28, 2008, 4:21 pm
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

I use SQLite on our MUD. I don't do much with it yet, but it seems to do the trick. The first thing I wrote was a generic query handler to return a basic record set. Then I added two immortal commands, one to perform selects (available to all) and one to execute arbitrary SQL (top level admins only). The generic db code for it all is about 30 to 40 lines of C++. Obviously what you choose to do with that recordset is context-specific and requires more code.

The weak typing of the columns isn't that big a deal really - most DB APIs are going to return you strings for your fields anyway unless they use auto-generated code based on the schema or something like that, so you just have to be careful.
       
Post is unread #17 Aug 29, 2008, 9:21 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

Actually, I'm used to using bound variables for both input and output, so type mismatches are a problem in a hard-typed language. Bound variables are required for ODBC (there is no quoting mechanic), and they're safer anyways.

I'm leaning towards using SQLite and a C++ stream wrapper for it. I'm also looking at the OTL project, which appears to be a stream wrapper for ODBC. Both use STL, although I have the feeling OTL may also use BOOST (which is a gigantic!!! library).

On the plus side, they all have friendly licenses... the boost license itself seems the most restrictive, and it sounds much like the LGPL, although I haven't really sat down to read it yet.
       
Post is unread #18 Aug 29, 2008, 9:38 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

Boost is gigantic, but it's also pretty freaking awesome. Of course, if you're the kind of person who really likes some of Boost's features (e.g. functional-like programming) it's debatable how much of your code you should be writing in C++ in the first place! I've become a fan of writing speed-critical pieces in C++, e.g. networking, and trying to fork out as much as possible to a dynamic language, which I find more pleasant to work in for most things.
       
Post is unread #19 Aug 29, 2008, 9:56 am
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

Quixadhal said:

Actually, I'm used to using bound variables for both input and output, so type mismatches are a problem in a hard-typed language. Bound variables are required for ODBC (there is no quoting mechanic), and they're safer anyways.

Well, the sort of point I meant was that since you can't know at compile time what data you're going to get back from a query, any binding to variables you do is necessarily relying on run-time conversions. So the emphasis is still on you to get every single type correct. All the libraries can do is package up the errors more nicely for you. In my case, I mostly prefer just using the sqlite printf style commands compared to doing a load of explicit binding to achieve the same aim. A few overloaded functions to parse the rows can make life easier though.
       
Post is unread #20 Aug 29, 2008, 10:01 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

You can generate code based on database schema that will let you know (from the client perspective) at compile-time what the types are. Of course, if the database itself is truly typeless, then the layer I mentioned will have to do some guesswork and conversion, but from the client's perspective it's mostly transparent.
       
Pages:<< prev 1, 2 next >>