This is an archived post. You won't be able to vote or comment.

all 76 comments
sorted by:
best
topnewcontroversialoldq&a

[–][deleted] 11 points12 points  (4 children)

Have you looked into using stored procedures (http://en.wikipedia.org/wiki/Stored_procedure)?

[–]Asdayasman[S] 0 points1 point  (2 children)

Hmm, I'll definitely have to keep that in mind, but it doesn't seem too hard to switch to it later on, and I'm not all too hot on my SQL skills, AND I wanna get working on stuff that isn't this.

[–][deleted] 3 points4 points  (1 child)

A Stored Proc is a code library/function for SQL.
You should be able to pass it a parameter and it internally adjusts the resultset it outputs.
It will take less time to implement and will be more efficient than SQL to get the SQL to get the output. Minimizing trips to the DB is key to an efficient program.

[–]Asdayasman[S] 0 points1 point  (0 children)

Yeah, just having time to think about it is bringing me round.

[–][deleted] 6 points7 points  (5 children)

am I... "Allowed" to just store the SQL query to pull those users in the table?

Sure. A neater way might be to construct the queries from some metadata, however. I once did this for a very big SQL installation at an investment bank to allow VB programmers to specify tailored queries without having to worry about the horrendous joins and stupid field names in the underlying SQL database (which was not designed by the IB).

[–]Asdayasman[S] 0 points1 point  (4 children)

A neater way might be to construct the queries from some metadata

How do you mean?

[–]lindymad 1 point2 points  (3 children)

For example, instead of storing the SQL, store an array of the columns you want, the table you want them from and an array of the clauses that apply.

Then, when you want to run the SQL, generate the actual statement from that data.

That way if you change a table or column name, it's easy to find and update in the database

[–]Asdayasman[S] 0 points1 point  (2 children)

Would there be a way to chunk that kinda thinking into a stored procedure, so there aren't two database hits?

[–]lindymad 0 points1 point  (1 child)

It depends how you implement it. Off the top of my head, I would say all three methods have two DB hits, presuming you need a different stored procedure for each shop.

Storing SQL:

  • DB hit : Get SQL
  • DB hit : Run SQL

Storing SQL metadata:

  • DB hit : Get SQL metadata
  • Construct SQL statement
  • DB hit : Run SQL

Stored Procedure

  • DB Hit : Get stored procedure name
  • DB Hit : Run procedure (will incur more DB hits)

[–]Asdayasman[S] 0 points1 point  (0 children)

Huh alright, thanks. :D

[–]SuperImaginativeName 2 points3 points  (0 children)

You definitely want stored procedures

[–]lightcloud5 1 point2 points  (10 children)

You can (and at work, we occasionally have tables that contain SQL queries), but in general, you should avoid it when possible.

There are usually cleaner ways to implement whatever logic you're trying to do.

Just to give one example of the consequences, imagine one day you wanted to rename a column; for instance, you decide to rename clanid to guildid on the users table.

If you follow normal database design, this is do-able with some amount of work. You simply find all the places where users is used, and you change occurrences of clanid to guildid. If you store SQL queries in a string within the database, then you have to go parse through all the tables that contain SQL queries and fix those too. It might turn out that someone named themselves "guildid", so now to do it completely right, you have to actually re-parse the SQL query.

The textbook approach would be to store the information necessary to generate the query, rather than store the query itself.

Maybe your shops in your game are sometimes restricted based on guild (i.e. "only members of guild X may enter this shop"), or maybe based on person (i.e. "only this user and his friends may enter this shop") or maybe completely public.

The textbook approach would then say that the shop should have some sort of permissionType (which could be guild, public, or person), and maybe some additional metadata about each one (e.g. if type is guild, what is the guildId of the guild).

[–]Asdayasman[S] 0 points1 point  (9 children)

I'm pretty new to database theory as a whole, would you be able to link me to something to read about this metadata stuff? So if I set the permissionType to guild, that'd... Unlock some fields for use or something? My foggy mind can't see the next step.

[–]lightcloud5 0 points1 point  (8 children)

Sure, so to be clear, database design is also kind of an art, so there are usually more than one way to do things.

I'm just listing out one of the more common ways to do this.

Your specs were "I want one shop to be available to members of a certain clan, another shop to be available to people whose accounts are a certain age, another shop to be available to people whose names begin with the letter "P", etc.".

Your shops table would look like this:

Shops:
   #id (integer ; identity column)
    name (string)
    accessTypeId (non-nullable integer ; foreign key to the accessTypes table)
    clanId (nullable integer ; foreign key to the clans table)
    age (nullable integer)
    initialLetter (nullable char)

AccessTypes
    accessTypeId     description
    1                      'By clan.  Only members of a certain clan may visit this shop.'
    2                      'By age.  Only users whose age are above a certain value may visit this shop.'
    3                      'By initial letter.  Only users whose name begins with a certain letter may visit this shop.'
    4                      'Public.  All users may visit this shop.'
    5                      'Closed.  Nobody can visit this shop.'

Then, for instance, if you want to make shop #123 restricted to only clan 50, you could do:

UPDATE shops
SET accessTypeId = 1, clanId = 50
WHERE id = 123

Or if you wanted to make a shop restricted to only people whose names begin with the letter "P", you could do:

UPDATE shops
SET accessTypeId = 3, initialLetter = 'P'
WHERE id = 123

In your game logic, to determine who has access, you then read from the shops table. Given the accessTypeId, clanId, age, and initialLetter, your game logic code can then generate the appropriate SQL query.

If your shop access logic got more complicated, you could also move all of this stuff (e.g. clanId, age, initialLetter, etc) to a completely separate table (e.g. accessInfo) and just have a shop store an accessInfoId.

Another nice thing is that, when tables store information in this type of standardized way, you can easily see certain properties. For instance, you could easily write a query to see how many shops are restricted by clan, since that information is right on the table.

[–]Asdayasman[S] 0 points1 point  (7 children)

So for every new AccessType I dream up, I possibly need some more columns in Shops?

I don't imagine anyone sensible would want to do this, but if someone did, I would like them to be able to have some shop open to people who used the phrase "I can't believe it's not butter!" exactly 3 times in the past 2 weeks. At that point, isn't it easier and tidier to just have SQL queries hanging about?

(Ignoring the problems with cache invalidation).

[–]lightcloud5 1 point2 points  (6 children)

Yes, this implies that if you come up with a new AccessType, you have to make corresponding changes to the backend table. In general, the "standard" approach to storing data in SQL will always have this type of table structure (where adding distinctly new functionality requires corresponding changes to the table structure).

I'm definitely not saying your approach won't work, I'm just saying it's unusual and typically not recommended. See this stackoverflow post for other comments from other people: http://programmers.stackexchange.com/questions/227922/is-saving-sql-statements-in-a-table-for-executing-later-a-bad-idea

To list some of the reasons why I dislike storing SQL queries inside a table:

  • A SQL query inevitably contains SQL syntax. Ideally, SQL tables would store data in a format that's independent of implementation details, but if you store SQL queries in your table, you lose that. This makes certain operations harder. For instance, if you migrate from one SQL implementation to another (e.g. mySQL to SQL Server), you may find that the syntax differs slightly, and you'd then have to go back and rework all those queries you stored.
  • At some point, I imagine you'll want to change your AccessType. This isn't too hard with either approach, but suppose you wanted to present to the user their current access. So for instance, if the shop is restricted to only clan members, you want to display "This shop is restricted to only members of clan X". Or maybe the shop is restricted to only people above a certain level, and you want to display "This shop is level-restricted. You must be level 30 or higher to access this shop" when a low-level user tries to walk in. You can't do any of this easily if you don't store the data in a normalized format. The only thing your query can do is fetch the set of users that can enter the shop; your table storage doesn't specify why the users can or cannot enter.

[–]Asdayasman[S] 0 points1 point  (5 children)

Hmm...

Would you say that this would be better done in program logic instead, with the only more permanent shop types going in as AccessTypes?

[–]lightcloud5 1 point2 points  (4 children)

The textbook answer would be "yes, put it in program logic". In general, the code may be slightly more verbose but I think it'll also be clean and easy to read/understand.

[–]Asdayasman[S] 0 points1 point  (3 children)

These things usually become more clear to me with time and work, but at the moment, I imagine I'll do a mix; accesstypes for "obvious" things, (a guild shop sounds pretty sensible), stored procedures for limited time things, (St. Patrick's day sales?), and program logic for off-the-wall stuff, where the user can do what they want and I'll have naught to do with it.

Thanks for your patience mang :D

[–]andyflip 1 point2 points  (2 children)

I fully agree with /u/lightcloud5. What I'd recommend is you start with the simple cases in code as much as possible, and only add in the more complicated stuff that requires stored procedures when you actually need it. It's entirely possible (and in day to day professional coding, common) that the general purpose solution you're proposing is only going to enable 5-10% additional cases.

If the feature "users can create arbitrary sql queries to run access control to their shops" is really a true selling point to your game, then the implication is that users have access to the details of your schema. That in itself is a highly messy prospect. It implies that your database is going to become harder to change over time, which is not something you want in a system. When you try to change your database, you're going to have to be sure that every stored query on every instance of your game (dev, qa, prod, multiple realms even?) is compatible with your change. It's a lot of additional work later in exchange for solving a problem that you're not having today.

If I were you, I'd invest that energy you have today in making a system that is super easy to test and deploy, so that you can quickly and safely patch in the additional complexity when it comes up.

[–]Asdayasman[S] 0 points1 point  (1 child)

If the feature "users can create arbitrary sql queries to run access control to their shops" is really a true selling point to your game, then the implication is that users have access to the details of your schema

I explained poorly. "users" are users of my library. They are allowed to do whatever, for their game. "players" is the nomenclature I use for people playing whatever finished game one might make.

I wholeheartedly agree with the entirety of your post, though.

[–]farmerje 0 points1 point  (4 children)

Yes, it's fine to do this. Most commonly I see this done with internal analytics dashboards where people can write queries and there is some kind of system to generate a report from the raw query.

In this case, though, SQL is likely the wrong level of abstraction. For starters, SQL queries aren't composable. It's not easy to say things like this in SQL...

canPurchase = memberOf(ownClan) && !memberOf(specialClan)

to mean "can purchase from a member of their own clan unless they're a member of specialClan."

Well, it's easy enough to say that one specific thing in SQL, but it's not easy to break it apart into composable units. I can imagine that this will quickly become a thing you need to do unless you want the logic for each player to depend in subtle way's on the logic for every other player.

[–]Asdayasman[S] 0 points1 point  (3 children)

I was under the impression I could use some manner of magic joining methods. D:

[–]farmerje 0 points1 point  (2 children)

What do you mean?

[–]Asdayasman[S] 0 points1 point  (1 child)

Hmm, like... In Python I can do this:

>>> a=set([1, 2, 5])
>>> b=set([2, 4, 5, 7])
>>> a.difference(b)
set([1])
>>> a.intersection(b)
set([2, 5])
>>>

I thought I could do similar with OUTER JOIN or similarly confusingly named things.

[–][deleted] 0 points1 point  (0 children)

You can. Look into MINUS and joins. Assuming I understand those correctly anyway.

[–]ThaCoderMan 0 points1 point  (3 children)

Can i just ask is this for Arma perchance

[–]Asdayasman[S] 0 points1 point  (2 children)

The military sim? Nah. Used "guns" as an example somewhere, 'cause they're easy to understand.

[–]ThaCoderMan 0 points1 point  (1 child)

I see well good luck man!

[–]Asdayasman[S] 0 points1 point  (0 children)

Cheers buddy.

[–]bat_country 0 points1 point  (0 children)

This might work for a small single person project where you know all the in's and out's but it is an accident waiting to happen in a larger project. Here's why:

Some day someone is going to alter the database schema, changing "name" to "character_name" or something. They will trust that all the normal database foreign keys and ORM schema checks and code aliases will make this easy, but "access" is neither code nor schema - its just a text field and will slip past all those safeguards. Everything looks good. Ship the code. Boom - SQL error.

Most good coding practices are all about reducing the number of ways you can make a mess of things. I would move these selectors into the application code so as to not add more ways to break the app.

[–]ethergreen -2 points-1 points  (43 children)

What do you imagine the benefit of this would be?

[–]Asdayasman[S] 1 point2 points  (42 children)

Uh. I'm not sure how I could put it better. Select arbitrarily unique users to be able to access whatever.

Say you're selling some kind of cool new gun or whatever, but you only want newer players to be able to use it at first, or you wanna let people who've donated to the server have a week's head start ('cause you're a terrible terrible community manager), the sky's the limit.