11 Sep, 2011, balakoth wrote in the 1st comment:
Votes: 0
For some reason Im blanking on a good approach here.

Basically Im looking to create "defintion" tables for DB Tables so I can mimic functionality I have in my PHP MVC application regarding SQL statement creation. PHP is lovely because of non type restricted arrays, but Im stumping myself on how to go about this in an efficent way in C.

Basically, id like to build a "defintion" table for example, the player struct: (Psuedo code)

The struct would hold
int varType, char fieldName, PointerToMemberInStruct

You would then have a table that had:
INT32 (CONST define), "name", &player->name


Which I could then access, and depending on the player pointer pull the data of player name when referencing "name" in the table.

I know there are various other ways to go about this, I just wanted to try and find a fluid way that allowed me to add and remove fields at ease, and ease the creation of automatically built SQL queries through a function, given a passed table definition struct.

Ive worked on a few high end MMOs, a commercial aspect and only one has slightly approached it in this method, but still not in the way I like. Any suggestions would be great.
11 Sep, 2011, plamzi wrote in the 2nd comment:
Votes: 0
When I was researching the same topic, I came across this link.

Basically, to look up a result field by name in C, you have to iterate through all the returned fields in the result object and do a string compare. It struck me that, in C, it would be a lot more straightforward (and even a bit more efficient) to do a "verbose" query for "name, level, class" and then just grab row[0], row[1], row[2], etc. It's not pretty nor modular, but it has the advantage of being apparent.

As for adding and removing fields dynamically, I guess you can write your own code using field name lookups, but I decided against worrying about that for two reasons:

1. Creating and removing fields is a relatively rare occurrence, so it can easily be done in a SQL client as part of the prep work for a new feature.

2. Very few structs correspond 1:1 to a table. In fact, given that I'm generating my own unique id's for each table, no table corresponds 1:1 to a struct.

For dynamic query composition, you can look at the deltamud implementation (should be in this repository or google-able). My recollection is it's not terribly inspired, but it may help generate some ideas.
12 Sep, 2011, plamzi wrote in the 3rd comment:
Votes: 0
As for the question in the title, I think you can declare the pointer to a function with an argument. and that function can return the pointer you need (e. g. player->name) at the time you need it.
12 Sep, 2011, David Haley wrote in the 4th comment:
Votes: 0
You can refer to a specific struct instance's field, like what Plamzi said, but you cannot refer to an uninstantiated structure field. So you can't have a PlayerStruct, and say "give me the field for this instance referred to by the name 'name'". You'd have to implement that yourself, one way or another.
12 Sep, 2011, Runter wrote in the 5th comment:
Votes: 0
A few ORMs use "automigration" to take metadata and turn it into the correct database structure. This makes turn around fast for development and wireframes, on the other hand..

Most ORMs use database versioning to solve this. Either with a DSL, database language, or something in between.

class AddSystemSettings < ActiveRecord::Migration
def up
create_table :system_settings do |t|
t.string :name
t.string :label
t.text :value
t.string :type
t.integer :position
end

SystemSetting.create :name => "notice",
:label => "Use notice?",
:value => 1
end

def down
drop_table :system_settings
end
end


Each time any structure to the database is changed you'd simply add a new migration file. Never removing files, only adding them. This ensures you can build the database structure, or rewind it to any point in time. In fact, you can rewind the database to a specific backup based on a timestamp or some other data.

There's other reasons why migrations like this turn out to be rather important. Mostly because it's a pain in the neck having to write a script to convert every record from String to Text, or Integer to String. or some other unreconcilable database types. This makes it more natural to do as well.

Just hand rolling every database change you make is of course possible, but it's as unwise IMO as writing code without using any type of revision control system. It's just asking for headaches.
12 Sep, 2011, balakoth wrote in the 6th comment:
Votes: 0
Yeah at one point I was thinking about writting functions to return said information, but then you are writting a function for every field you wish to access via that table. Seems a bit overkill to just simplify the process of migrating and expanding data.

Still trying to find the code I saw years ago that handled their db tables with C struct tables. I miss the ability that Ruby and PHP have to do typless arrays and such for exactly the reason I was stating. I guess back to the drawing board.


Even if i had a function pointer to reference the field I needed, theres no way to pass that initialized struct I need to a static table unfortunately, so I guess I already knew my answer, was just hoping someone had a better idea.

I got to spoiled for years developing my own MVC framework for PHP and had developed a great system for accessing, writting, and updating SQL data dynamically without having to modify Strings of SQL queries, simply having the functions build them.


Alas, I have not given up and am determined to find a way to do this the way I envision!
12 Sep, 2011, David Haley wrote in the 7th comment:
Votes: 0
No, you don't need a function for every field. You just need a generic list of "field objects" associated with each object that can have fields. You already had the skeleton for such a structure.

I'm not sure what exactly you're trying to accomplish so it's hard to give more detailed suggestions. But basically, you need to discard the literal layer of struct fields, and construct a layer on top of it.

Why are you doing this in C?
12 Sep, 2011, balakoth wrote in the 8th comment:
Votes: 0
plamzi said:
When I was researching the same topic, I came across this link.

Basically, to look up a result field by name in C, you have to iterate through all the returned fields in the result object and do a string compare. It struck me that, in C, it would be a lot more straightforward (and even a bit more efficient) to do a "verbose" query for "name, level, class" and then just grab row[0], row[1], row[2], etc. It's not pretty nor modular, but it has the advantage of being apparent.

As for adding and removing fields dynamically, I guess you can write your own code using field name lookups, but I decided against worrying about that for two reasons:

1. Creating and removing fields is a relatively rare occurrence, so it can easily be done in a SQL client as part of the prep work for a new feature.

2. Very few structs correspond 1:1 to a table. In fact, given that I'm generating my own unique id's for each table, no table corresponds 1:1 to a struct.

For dynamic query composition, you can look at the deltamud implementation (should be in this repository or google-able). My recollection is it's not terribly inspired, but it may help generate some ideas.


I get what you are saying. I think perhaps i explained it a little diferently than I intended. While no table will 1:1 to a struct. The idea is that I would be creating a C struct constant table, that mirrors the SQL DB table, but along with the field names, references a pointer to the particular variable in a struct (say playerdata for example) Since You arent normally saving every variable in the playerdata struct to your DB, it would be easy to simply add a line to your table defintion (C struct in source), add the field in the db, and beyond that the query building function would do the rest..

MOstly this was to save from rewritting update and insert queries, saving from syntax issues, and allowing you to pass custom arrays to the build function for custom updates and select statements, without needing to manually reference the playerdata variables (as they would be pointed to in a C struct constant table) My idea is far fetched, but oh well haha.
12 Sep, 2011, balakoth wrote in the 9th comment:
Votes: 0
David Haley said:
No, you don't need a function for every field. You just need a generic list of "field objects" associated with each object that can have fields. You already had the skeleton for such a structure.

I'm not sure what exactly you're trying to accomplish so it's hard to give more detailed suggestions. But basically, you need to discard the literal layer of struct fields, and construct a layer on top of it.

Why are you doing this in C?


Expanding on a already Circle based implementation of SQL. Ive done things like this in other languages for various commercial projects. Just never attempted with a C based server. Only reason Im doing it in C, it is what I am limited to (I am helping someone else with their MUD)
12 Sep, 2011, balakoth wrote in the 10th comment:
Votes: 0
Vey psuedo code lol

struct SQLDefintion *playerdefintion
{
INT32, "id", &playerdata->id;
VAR, "name", &playerdata->name;
BIGINT, "location", &playerdata->vnum;
}


char * buildSQLQuery(SQLDefintion *definition, playerdata)
{
Loop defintion table via valid playerdata struct,
build update query (eg. "update table ")
sprintf(defintion->fieldname = defintion->pointer);
close query
return query;
}
12 Sep, 2011, David Haley wrote in the 11th comment:
Votes: 0
Nope. That's not possible, as we've said already.

You have to implement the fields on the player object as a series of name/value pairs.
12 Sep, 2011, balakoth wrote in the 12th comment:
Votes: 0
David Haley said:
Nope. That's not possible, as we've said already.

You have to implement the fields on the player object as a series of name/value pairs.


I got that part ;) Just figured id show some code to give an idea of what I was trying to do unless it wasnt clear. And adding that layer over an existing system seems more trouble than benefit i would gain. From scratch maybe, in a different language, but.. Yeah. Thanks for the input
12 Sep, 2011, Tyche wrote in the 13th comment:
Votes: 0
Not sure exactly what you are trying to do.
You can certainly store the offsets of a struct for later processing
against an instance of that struct…
struct fields {
char * name;
int type;
ptr_t location;
};

struct player {
int id;
char * name;
long vnum;
};

struct fields table = {
{"id", INT32, offsetof(struct player, id)},
{"name", VAR, offsetof(struct player,name)},
{"location", BIGINT, offsetof(struct player, vnum)}
};

struct player p;

foo = table_lookup("id");
*(p+foo) = something;
bar = table_lookup("name");
strcpy(p+bar, something);


??
12 Sep, 2011, balakoth wrote in the 14th comment:
Votes: 0
That may just be what im looking for Tyche.

Essentially, I want to build tables that hold references to the variables in a particular struct in the game (Our examples are using player data) that also reference the field names that are associated with the field they will be saved in the DB Table. That way I can loop through a list to build an SQL query (update, insert), on the fly without typing out the "update player name = 'name', location = 'vnum' where id = 'id'" in a char buf.

Later, if I so choose to add, say "LastName" to the table, I would simply add LastName to the FieldTable, and LastName to the Database table, and the query would be built without any modification to the query string manually.
12 Sep, 2011, Tyche wrote in the 15th comment:
Votes: 0
Runter said:
A few ORMs use "automigration" to take metadata and turn it into the correct database structure. This makes turn around fast for development and wireframes, on the other hand..

Most ORMs use database versioning to solve this. Either with a DSL, database language, or something in between.


It certainly would be an interesting C project. I'd probably implement it like CORBA (using a pre-compiler).
12 Sep, 2011, balakoth wrote in the 16th comment:
Votes: 0
No formatting, didnt really define much, just a psuedo.. I guess essentially this is what I would be trying to do.. Yes not right I know, but trying to answer the "not sure what you are trying to do exactly" comment :)

Thanks again everyone for the input

struct fields {
char * name;
int type;
ptr_t location;
};

struct player {
int id;
char * name;
long vnum;
};

struct fields table[] = {
{"id", INT32, offsetof(struct player, id)},
{"name", VAR, offsetof(struct player,name)},
{"location", BIGINT, offsetof(struct player, vnum)}
};

<EDIT>

void buildQuery(player *p, char * where_clause)
{
sprintf(query, "UPDATE player_data");
for(;;)
{
if(table[i].type == INT32)
sprintf(query_field, " %s = '%d'", table[i].name, p+table[i].location);
else if (table[i].type == BIGINT)
sprintf(query_field, " %s = '%l'", table[i].name, p+table[i].location)
else
sprintf(query_field, " %s = '%s'", table[i].name, p+table[i].location)

strcat(query, query_field);
i++;
}

p_where = table_lookup(where_clause);
sprintf(query_end, " WHERE %s = '%d'", where_clause, p+p_where);
strcat(query, query_end)


}
12 Sep, 2011, Tyche wrote in the 17th comment:
Votes: 0
balakoth said:
That may just be what im looking for Tyche.

Essentially, I want to build tables that hold references to the variables in a particular struct in the game (Our examples are using player data) that also reference the field names that are associated with the field they will be saved in the DB Table. That way I can loop through a list to build an SQL query (update, insert), on the fly without typing out the "update player name = 'name', location = 'vnum' where id = 'id'" in a char buf.

Later, if I so choose to add, say "LastName" to the table, I would simply add LastName to the FieldTable, and LastName to the Database table, and the query would be built without any modification to the query string manually.


I'd write a program that reads a structural DSL, then generates the actual headers for the structs, populates the lookup tables with the offsets, and validates/alters the database schema automagically.
Like I mentioned… sort of like some CORBA implementations do.
That way you can build your application just like one does with an ORM.

make migrate
make myapp

Then of course, you'd have to define an API that implements the queries, inserts, deletes, joins to use that information.
0.0/17