Login
User Name:

Password:



Register
Forgot your password?
Vote for Us!
tintin++ ogg sound player script for linux
Author: Robert Smith
Submitted by: Vladaar
6Dragons ogg Soundpack
Author: Vladaar
Submitted by: Vladaar
6Dragons 4.4
Author: Vladaar
Submitted by: Vladaar
LoP 1.46
Author: Remcon
Submitted by: Remcon
LOP 1.45
Author: Remcon
Submitted by: Remcon
Users Online
CommonCrawl, Google, Bing

Members: 0
Guests: 12
Stats
Files
Topics
Posts
Members
Newest Member
481
3,733
19,362
618
Micheal64X
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 #21 Aug 29, 2008, 1:07 pm
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

That's the problem. In a normal database system, if I declare a column to be an integer, any query will ALWAYS return an integer. Any attempt to put anything else in that column will generate an error (and rollback of the transaction).

So, I could write code that asks the database itself for the schema and returns a structure to match (in C++), or a set of void pointers along with a description of the types in some well-known format (in C). OR, I could simply know that when I load a room from the rooms table, it will have X columns, and here are some variables in a room structure to bind, and after the fetch my room structure is filled out.

NOTE: This even works for non-table queries. You can do things like "select mobs.mob_id, mobs.name, items.item_id, items.name from mobs join items using(mobs.wielded_id = items.item_id) where items.name ilike '%sword%'". The ODBC layer will tell you the type of each column in the result set, so you can allocate the right size chunk of memory.

If someone (bad code from elsewhere.... ad hoc query.... some joker at the sql prompt) puts a string into the column that is expected to be an integer (which SQLite allows!), best case scenario is that I get back an integer value 0 since it didn't parse. More likely, the pointer to the string data that was returned will be cast and stuffed into the integer field, resulting in garbage data.

Of course, in MY case it's not an issue since I'm my own customer base. However on a production MUD where you have several folks who have access to the database directly (either via SQL, or by commands in game, or just by being allowed to write snippets that use the database), it is asking for trouble, IMHO.
       
Post is unread #22 Aug 29, 2008, 5:53 pm
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

Quixadhal said:

That's the problem. In a normal database system, if I declare a column to be an integer, any query will ALWAYS return an integer. Any attempt to put anything else in that column will generate an error (and rollback of the transaction).

Well, no, most of the time it will return you a string. It just happens to be a string that you know you can convert at runtime to a valid integer. MySQL returns a MYSQL_ROW, which is 'an array of counted byte strings.' PostGreSQL returns a char* from PQgetvalue. And SQLite executes a callback passing in a char**, ie. an array of strings of your data. Someone somewhere still has know that the data is to be treated as a certain type and to do the run-time coercion accordingly.

If someone (bad code from elsewhere.... ad hoc query.... some joker at the sql prompt) puts a string into the column that is expected to be an integer (which SQLite allows!), best case scenario is that I get back an integer value 0 since it didn't parse. More likely, the pointer to the string data that was returned will be cast and stuffed into the integer field, resulting in garbage data.

It's the first case. SQLite returns each field as text. So you won't get 4 bytes which you would cast to an integer, but a string that you would call atoi() or similar on to get your integer value, meaning you'll get a harmless zero back.

However on a production MUD where you have several folks who have access to the database directly (either via SQL, or by commands in game, or just by being allowed to write snippets that use the database), it is asking for trouble, IMHO.

I accidentally put some numbers into the string field of my SQLite database the other day. The world didn't end. It was just a meaningless value, which I could have caused with a 'real' string that carried the wrong semantic value too. I think people from a C/C++ background sometimes tend to overestimate the benefits of static typing. Just my opinion though.

So, I could write code that asks the database itself for the schema and returns a structure to match (in C++)

Since you can't generate new classes in C++ at run time, this is impossible. In Python or Javascript or something though, sure.
       
Post is unread #23 Aug 29, 2008, 6:16 pm
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

You wouldn't do it at runtime -- you generate the bindings at compile time. Of course this means that if you change the schema, you must recompile to refresh the bindings. This is similar to generating bindings between a host and guest language; you do it at compile time.

Also, I wouldn't be so sure that it's completely impossible to do this at runtime -- you can do some pretty fancy things with templates and so forth to construct an object describing other objects at runtime. Indeed you can't create new classes but you can still create descriptions.
       
Post is unread #24 Aug 30, 2008, 4:16 am
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

Generating bindings at compile time is fine if you only ever want to pull out a row at a time. As soon as you want an ad-hoc query, ie. to use the actual power of SQL, you're back to square one. You could regenerate your db bindings code every time you think of a new query... or you could define them as views and pretend they're tables... but really you're just limiting yourself by making it harder to query the data.

Personally, I wouldn't go down the route of generating database binding code unless you're forced to by your API: we do it at work, in order to be able to pull a row from a db as a complete object. The benefits in safety are tiny and the drawbacks in flexibility significant.
       
Post is unread #25 Aug 30, 2008, 7:43 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

Kylotan said:

I accidentally put some numbers into the string field of my SQLite database the other day. The world didn't end. It was just a meaningless value, which I could have caused with a 'real' string that carried the wrong semantic value too. I think people from a C/C++ background sometimes tend to overestimate the benefits of static typing. Just my opinion though.


That's ok. I think people who haven't worked on systems where errors cost people millions of dollars, or get people killed, tend to handwave things that promote safety because it's inconvenient. I make mistakes, and if the database, compiler, or person on the forums telling me I'm an idiot will help me make fewer of them, I'm all for it! :)

I've worked on both ends of the spectrum.... I spent 5 years working with perl and the DBI, and while tossing data around is much simpler, finding the subtle errors caused by having the wrong type of value end up in the wrong place is not fun. The string "1st" generally gets converted to the integer "1" when things like atoi() are called on it.

I've also worked with ADA, a language which doesn't even allow typecasts unless the types are defined as fully compatible. For example, you can define a type to be the lowercase alphabetic characters (a subtype of character), and you can only cast in one direction (lc -> character), because trying to go the other way isn't defined for every value of the source set. That's also a pain in the butt, but since the language was developed for use in missile guidance systems, I think a certain degree of extra care is probably not a bad thing.

If it's impossible to generate run-time binding types in C++, what's the point of typeof, dynamic_cast, and other things meant to deal with type information at run-time? It seems like the API layer could ask the database for the column type and then use dynamic_cast to properly type the result.

As I said though, I'm not a C++ person so if it can't be done (without resorting to tricks using dlopen), so be it.

I should also mention that while a generic ad-hoc query API is nice, how often are you going to use that from MUD code? I would hope all the places reading and writing to the database are well defined, and I don't think it's a show-stopper if the admins don't have an "sql" toy command to play with at the prompt -- they can always access the database from outside.
       
Post is unread #26 Aug 30, 2008, 11:08 am
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

I think you exaggerated slightly what you can and can't do with compile-time bindings. If you know the type of every column in every schema, then you can actually infer the column types of very many queries. In fact, unless you do things with SQL functions, I think you can pretty much infer everything.

At this point I'm starting to get curious enough to want to actually go look at the SQLite API and see what it looks like, to see what kind of data gets pulled out of it when you make a query...

I don't really want to get into static vs. dynamic typing, I'm just trying to establish what can and can't be done in C++. :smile:
       
Post is unread #27 Aug 31, 2008, 6:35 am   Last edited Aug 31, 2008, 6:41 am by Kylotan
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

Quixadhal said:

That's ok. I think people who haven't worked on systems where errors cost people millions of dollars, or get people killed, tend to handwave things that promote safety because it's inconvenient.

Hmm, I think that's unfair. I've actually worked on embedded systems used by the police and emergency services, for example. You can't assume that anybody who disagrees with your programming opinions is obviously inexperienced!

There is such a thing as a false sense of security, and I believe static typing gives that. It also encourages casts (usually fine, but occasionally completely unsafe) instead of conversions (sometimes fail, but always safe), and in other cases increases code complexity (again contributing to lack of safety).

I think we'll just disagree on this, but that's ok, because we're not on the same project. ;)

If it's impossible to generate run-time binding types in C++, what's the point of typeof, dynamic_cast, and other things meant to deal with type information at run-time? It seems like the API layer could ask the database for the column type and then use dynamic_cast to properly type the result.

The problem is that, at some point, there needs to be code to handle the end data, and C++ code is all statically generated, so you can't throw arbitrary types at it. It's easy enough to detect what type the database column is storing, and you can create a variable of that type if you want, but you can't return it, since C++ expects you to have defined your return type statically. Nor can you pass it to another function, because C++ generates function calls based on static types. So you can either cast it to void*, like the C people do, or use boost::any, or make some fake types all deriving from a common base class (which is what the dynamic_cast stuff is there for, but which doesn't apply to any of the default types you'd get from a RDDBS)... and either way, you have effectively abandoned static typing and moved to dynamic typing because you have to query types at run-time and call different stuff accordingly. My argument would be therefore that it's not worth doing.

I should also mention that while a generic ad-hoc query API is nice, how often are you going to use that from MUD code? I would hope all the places reading and writing to the database are well defined, and I don't think it's a show-stopper if the admins don't have an "sql" toy command to play with at the prompt -- they can always access the database from outside.

I use it all the time from inside the game. Everyone below the top level is barred from doing anything other than SELECTs, but that alone is a great tool. Want to see the last 15 logins from a certain hostname - easy. Find all weapons with an affect that yields a damroll bonus more than 10% of the object's level? Also easy. List the top 20 mobs killed in the last month in areas with level ranges between 40 and 60? Done. Want to know which classes or races are most popular with female characters and which are popular with males? Simple. Many of these stats are really useful to be able to just pull up as soon as you think you might need them. Sure, we lived without this for years, but then we lived without electricity for millenia too. ;) I'd rather have the benefits. But it's a choice we all have to make based on our own requirements.

DavidHaley said:

I think you exaggerated slightly what you can and can't do with compile-time bindings. If you know the type of every column in every schema, then you can actually infer the column types of very many queries. In fact, unless you do things with SQL functions, I think you can pretty much infer everything.

Yeah, you can easily find what a column's type is. The problem, as I hope I made clear earlier in this post, is that you can't really do anything useful with that information because at some stage, you need to funnel the data back through a static interface, which essentially means dynamic typing. So you either have to resign yourself to doing run-time conversions, or to pregenerating type-safe code for your queries beforehand. (Or even better, using a dynamically-typed language in the first place which will do it all for you.)
       
Post is unread #28 Aug 31, 2008, 8:58 am
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

Kylotan said:

You can't assume that anybody who disagrees with your programming opinions is obviously inexperienced!

True, but assuming everyone who doesn't like duck typing just throws type casts around to tell the compiler to be quiet is also unfair.

Kylotan said:

There is such a thing as a false sense of security, and I believe static typing gives that.

Much like the sense of security you get from always having type conversion so you can assume the data involved is correct.

If you're using soft types to make life simpler, that's all well and good. But if you declare something to be a particular type and nothing enforces that declaration, that is a point of failure. If you're importing a few million lines of data from a set of CSV files, and someone made a typo, wouldn't you rather the process halt with a nice rollback, telling you an integer column had a string value in line 34,762? The alternative is to have a data set that now has a 0 in some column that should have been some other number, and a NULL in the last column (which lacked a value).

I've actually caught people doing if(x > 140) where x was declared as a char. As we know, char variables vary between -128 and 127, so the comparison will never be true. Sure, perl wouldn't have the problem, but it would let $x be set to "smog", and while I don't see the value in comparing "smog" > 140, the perl interpreter will happily return false without even a peep.


I agree, we'll just disagree on this one. :)


Kylotan said:

you have effectively abandoned static typing and moved to dynamic typing because you have to query types at run-time and call different stuff accordingly.

Not quite the same. If I ask the database for a column type, and it says "integer", I can allocate an integer-sized chunk of memory, drop the value in it, and hand it off to other people's code AS an integer. Yes, I have to do the type-mucking myself, but the outside world can rely on it being an integer.

If the database LIES -- it says the column type is an integer.... oh but here's a string value, just this once. Now I have to either bear the burden of hand-checking EVERY bit of data that comes back to figure out what it really should be myself -- and I won't always know since I don't know the ultimate use of the data. The database said this was an integer, but it spat "23.5" at me. Do I allocate a float and watch the application crash when it expects an int? Do I truncate it? Do I round it up? If I just hand back the string representation, that pushes the burden to my caller, and the whole point of having an API is to simplify the caller's life, else they'd be down there poking the database themselves.

Kylotan said:

(Or even better, using a dynamically-typed language in the first place which will do it all for you.)

On that, we can agree. Unfortunately, I'm not up for a total rewrite of Smaug in some reasonable language. At least not this week.
       
Post is unread #29 Aug 31, 2008, 10:17 am
Go to the top of the page
Go to the bottom of the page

Kylotan
Fledgling
GroupMembers
Posts37
JoinedNov 28, 2007

Quixadhal said:

True, but assuming everyone who doesn't like duck typing just throws type casts around to tell the compiler to be quiet is also unfair.

No, but it's a fact that a system that pulls data back from the DB like this is going to be performing a cast or a conversion, since the DB just returns a string of bytes. The only question is where that cast or conversion takes place.

If I ask the database for a column type, and it says "integer", I can allocate an integer-sized chunk of memory, drop the value in it, and hand it off to other people's code AS an integer. Yes, I have to do the type-mucking myself, but the outside world can rely on it being an integer.

If the database LIES -- it says the column type is an integer.... oh but here's a string value, just this once. Now I have to either bear the burden of hand-checking EVERY bit of data that comes back to figure out what it really should be myself -- and I won't always know since I don't know the ultimate use of the data.

I don't think not knowing the ultimate use is a big problem. The schema said 'integer', so you run it through your convert-to-integer routine and return what you get. It's true that you need an extra error handling capability there in some situations, but with proper DBs like MySQL or PostGreSQL you are typically going to need special case code in your API for NULL values too. (Sure, if a column is not marked as allowing nulls, then your api calls wouldn't need to accommodate that, but then you'd need a different set of calls or objects for columns that do support nulls, which I doubt is a good thing.) And since your calling code is likely to have other semantic constraints on the data, I don't think it's a big burden to check an isRowValid() flag or some other trivial error-reporting mechanism. It's not ideal, but I think it's a small price to pay to easily allow for dynamic queries and to avoid needing to autogenerate bindings.

Kylotan said:

(Or even better, using a dynamically-typed language in the first place which will do it all for you.)

On that, we can agree. Unfortunately, I'm not up for a total rewrite of Smaug in some reasonable language. At least not this week.

Although this was meant flippantly, I often think about just pushing out much of the complex logic into Python. I already have Python embedded but it's currently not hooked up to much at all. Maybe it's time to change that.
       
Post is unread #30 Aug 31, 2008, 1:05 pm
Go to the top of the page
Go to the bottom of the page

David Haley
Sorcerer
GroupMembers
Posts903
JoinedJan 29, 2007

Kylotan said:

Although this was meant flippantly, I often think about just pushing out much of the complex logic into Python. I already have Python embedded but it's currently not hooked up to much at all. Maybe it's time to change that.

Well, it's what I'm doing with Lua... :wink:

If I were to start it from scratch, though, I might consider Scala. I like typing for several reasons, and while Lua is great for small systems, I've been feeling some pain points on larger systems with lots of complex interactions. Scala's type inference is great to work with, because it'll only get in your way when you actually do something wrong. And the fact that it runs natively on a JVM is a wonderful bonus, because it means writing the host in Java is that much easier.
       
Post is unread #31 Sep 4, 2008, 6:26 pm
Go to the top of the page
Go to the bottom of the page

Quixadhal
Conjurer
GroupMembers
Posts398
JoinedMar 8, 2005

Just as an entertaining read, here's a link to a project called the DTL. It allows you to create STL-like containers that are tied to database queries. They can be static or dynamic (IE: the type of columns isn't known until runtime).

It's a little dry reading, but this is the closest thing I could find to what I was trying to describe.
       
Post is unread #32 Feb 5, 2009, 7:48 am
Go to the top of the page
Go to the bottom of the page

Rojan QDel
Fledgling
GroupMembers
Posts25
JoinedDec 30, 2006

This may be necroposting, but I've always been interested in MUDs using SQL. I've tried MySQL and SQLite at various points to store various pieces of data in my MUD. At some point, i recoded a fair bit of SWRFuss to store/load its data using a MySQL database. I am currently working on rewriting parts of LotJ to use SQLite 3. SWR:SQL was a pretty..nasty task because of the not-so-great C MySQL libraries. I generally used a VERY basic API from mudmagic, that was a wraper for connecting to the DB, and executing queries. All of the data still had to be processed using raw MySQL objects, no wrappers.SQLite has been a bit easier, since its functions are better-designed for use in C.

I've been taking a look at this DTL library, and it looks pretty promising. I'll likely try it out and see what I can get out of it.
       
Pages:<< prev 1, 2 next >>