Great House Fliggerty Census and Excise Office
A project....

I decided to make a WIP thread for this. I honestly didn't think that my half-formed half-crazed idea would get the reception it has. Not only was there tremendous feedback in the previous thread about this, but I got a LOT of private communication about it. So I'm quote energized and excited about this now!

The General Idea

Imagine a website where you can view detailed stats about someone's Morrowind character, complete with a screenshot gallery. Now imagine that this website updates while you are playing. That's the general idea.


The Process

So you go to the GHF Census and Excise Office website and register for a new character. You are given a character ID, then you go play Morrowind. The mod setup process will ask you for this character ID, which it will use (obviously) to reference your character's stats online. As you are playing, every few minutes a few scripts will run and harvest a LOT of data about your character and game. This data will be passed through peachykeen's MWSQL into a local database and stored there. Now if you are connected to the internet, this data will be immediately uploaded to our server and displayed on your character's page. If not, you can log in later and manually upload it.



I have really given this a lot of thought over the past few days, and done a lot of research on the use of SQL databases. I have learned a lot; the most important being that the best place to start with such a project is determining which data you will store, and the structure in which it will be stored. All day at work today I was working on arranging the database tables.

I have never created an entire database from scratch before, so there may obviously be better ways to arrange this. I am open to suggestions. I did heavily base much of this on what I learned by thoroughly examining phpBB3 databases and how they correlated data between tables.

My proposed tables, followed by the rows:
(Items with a * next to it is something that the user can manually enter/edit on the site; will be necessary for custom modded things.)

Users //data for the site member
User ID
Username
Password
Email Address
Account Active
Account Level
Number of Characters
Number of Comments
Location
Gender
Website


Character //data for each character
Character ID
Owner's User ID
Is Active //has any data been uploaded yet?
Last Time DB Updated //can determine if the player is currently playing
Number of Screenshots
Number of Comments
Number of Page Views
Name
Avatar Image Path*
Race*
Gender
Class*
Class Description*
Birthsign*
Birthsign Image Path*
Major Skill 1
Major Skill 2
Major Skill 3
Major Skill 4
Major Skill 5
Minor Skill 1
Minor Skill 2
Minor Skill 3
Minor Skill 4
Minor Skill 5
Back Story *
Primary Residence*
Journal File Name //perhaps upload Journal.htm, remove unwanted characters (those around hotlinks) and rename with char ID; will display on a page separate from character screen
Level
Current Health //these will be shown graphically as well as as a fraction on character screen
Max Health
Current Fatigue
Max Fatigue
Current Magicka
Max Magicka
Skills //one row for each one
Attributes //one row for each
Attribute Fortified //one row for each, true/false, can use xGetBasixxx to tell, will determine font color when displaying attribute value
Attribute Damaged //one row for each, same as above
Reputation
Bounty
Current Encumbrance
Max Encumbrance
Current Cell ID
Has Common Disease
Has Blight Disease
Has Corprus Disease
Is/Was Werewolf
Is/Was Vampire
Is Clan Aundae
Is Clan Berne
Is Clan Quarra
Faction //row for each vanilla faction player can join, a dozen or so for custom factions
Faction Reputation //some of this faction stuff can't be acquired through scripting...I will work my genie magic on it, I think I have figured out how to make it work
Faction Rank //again, one row for each faction, for all of this faction stuff
Current Time //GameHour
Day
Month
Current Weather Conditions
Days Passed //game time since player was created
Hours Played //Real time game has been played

//Potentially other things, but this is all I can think of right now; please suggest any more that you may think of.


Inventory
Owner ID (character ID)
Equipped Weapon 1
Equipped Weapon 2 //for arrows, etc
Equipped Clothing //9 rows total
Equipped Armor //8 rows total
Gold Amount
Net Worth //total value of all items in inventory + gold
All Other Inventory Items //total of 1000 rows, one item in each row; string will contain item name and count (ie Silver Broadsword 2)


NPCs
Owner ID (character ID)
Hostile NPCs & Creatures //20 rows possible, not likely that there will be 20 hostiles at one time, game will probably CTD
Follower & Companions //20 rows


Mods //intend to find a way to read current mod list from Morrowind.ini
Owner ID (character ID)
Mod Filename //255 rows possible

//I want to be able to display all of the loaded mods on a separate page from the character screen; when you click on a mod file name, it will automatically use ES Search to find it


Screenshots
Owner ID (character ID)
Picture ID
Filename
Thumbnail Filename
Caption
Number of Views


Comments //members can leave comments on character page
Owner ID (character ID)
Comment ID
Author
Comment




I have been gradually working on building this; I've spent a bit of time working on the registration/login system. We'll move on from there.

I am to understand that peachykeen is working on making MWSQL compatible with MySQL. I believe that will make things a lot easier to come up with a way to upload the data. Dogsbody suggested in the last thread that we can use the local MySQL logs to copy the queries to the central database. That is certainly something I am going to be looking at. It will also be necessary for our program to acquire the mod list from Morrowind.ini and write it to the database as well.

I have set up a headless LAMP server here at my house and made it live. I don't have a static IP, so giving out my IP would be pointless and stupid. But for those of you who are willing to get involved, I will set up a mailing list and keep you updated on how to access the server. I would rather build the whole system on my little dinky server here where I can make regular backups on multiple types of media, and won't risk screwing anything up or building security holes on the server that GHF resides on.

So who's excited for this?

Who wants to help?

One thing that would be nice to have right off the bat (it will help me with the site design overall) would be to have a site header image. Artistic freedom is given, the most appealing that is offered will be used. :hehe:

I understand there has been speculation to use stats realtime, ie download stats from players and use in an arena like scenario or perhaps as companions? Some problems would be what mods the other player would be using.

Also, this sounds like an AWESOME idea already. Me likes!

I think the end goal would be the ability to download the character that you are viewing online. It is something that I am definitely keeping in mind, but I would actually consider that to kind of be the aim of "Version 2.0" of this project. Of course, that is the reason that I want to be able to incorporate ES Search to find the mods in the displayed mod list.

Absolutely fantastic idea! :goodjob: Along with all the rest, of course.


Me!!!


I don't know anything. But I will definitely be following the progress.

Can't wait flig, this will definitely b a standard requirement after it's release.

Your telling me people will know what my character stats are at all times?

Uh-oh, looks were in for some 'mine is bigger than yours' coming up! So I best get cracking on mine then, because mine is more assassiny than yours!

This is the first time my jaw has dropped while reading these forums. I wish there was a way I could contribute, but all I can do right now is say that I'm all over this train. I'll be watching!

If you keep history for a person's data, you could graph the progression of their character, which might be interesting.
I like graphs

This is truly amazing.

Wow, that MySQL program from peachykeen has given you a lot of new ideas and wips huh? This sounds interesting, I'll be anticipating its completion

I can do this! Give me the specifications, such as pixel dimensions, and what you want it to look like and I can get started.

Yay! As you probably know from all my posts in the last thread, I think this idea is ultra cool.

Now I must think of a way I can help out.

Edit: Some Ideas:
- How about having an icon next to everyone's avatar at the GHF Forums, which if they customize their profile it will link to their character(s).
- Some sort of system for "hooks" so that other mods can add references to this list easily, without having to rewrite any scripts.

What's the security on something like this Fliggerty? I suffer Internet paranoia and am always deleting cookies and quite careful about what can access my machine.

Knowing that there was a hacker having a go at the community last year what would you do for a secure connection?

How will it work if you have a custom race?

That's your first database architecture from scratch? You're one hell of a fast-learner.
That's a whole lot better then what I usually see from people who work full-time as programmers.

Looks good.
The number of characters and number of comments are not absolutely needed, as they're duplicate info of the number of rows for a user id. But they might save you some queries on heavily requested tables. You'll have to see though if doing two writes (three for the number of comments) instead of one will be heavier then what you'll save on reads.

I suggest you change IsActive for CreationDate (which I don't see anywhere; might be interesting to keep). When a character is created, you can initialize CreationDate and LastDBUpdate to the same time. Basically, if CreationDate=LastDBUpdate, it means no data has been uploaded yet.

Might be worth storing in a separate table, so update queries from the app and update queries from the web won't be focused on the same table.

You could combine these bunch of true/false in binary strings. Like 100101100 where first number is INT fortified: TRUE, second number is STR fortified: FALSE, third number is PERS fortified: FALSE. Only consider this if you don't plan on allowing users to do a search for characters that have an attribute fortified or damaged, or if you don't mind getting a slight headache.

You can probably combine. If I remember correctly, you can't be from two vampire clans at the same time, and if you're of a clan you're automatically a vampire. But I might be wrong; I don't play much vampires.
If I'm right about this, I'd suggest something like one column where 0 = not a vampire, -1 = was a vampire, 1 = is Aundae, 2 = is Berne, 3 = is Quarra, 4=is modded vampire clan. It's more compact and faster if you want to just check if the character is a vampire, or allow searches for vampire characters only ( (VampireFlag>=1) is faster then (isAundae=1 OR isBerne=1 OR isQuarra=1) )
edit: after sleep and coffee, I realized I made a mistake. It's VampireFlag>=1, not VampireFlag>1

Had to cut my post in two. Apparently it was too long and had to many quotes.

It might be worth putting this in a seperate table since one character can belong to several factions.

I'd suggest combining this as MMDDHH so it's easier to do searches on and quicker to order.

I have no idea, but would it make sense to list these per body slots and have only one row per character? Like characterID, Feets, Legs1, Legs2, LeftHand, RightHand, LeftShoulder, RightShoulder, Chest, Over (robes), Neck, Head, Belt, Amulet, Ring1, Ring2 (I might be missing some)? You wouldn't be able to differentiate between gloves and gauntlets, but I don't know if it's an issue.
edit: I'm missing leg3 (pants+skirt+greaves) and chest2 (shirt+cuirass)

I'd suggest putting it as CharacterID | Creature/NPC | isHostile

That's gonna get very big very fast. If you don't plan on making this info available through search (like search all characters that use this mod), you might want to consider keeping this in a text file, one for each character.

I hope this post will help. Sorry if some of it is unclear; I'm kind of tired right now.
Out of curiosity, your lamp's P is PHP right?

I might be able to give lend you an occasional hand for the server-side stuff. Not much because I work a whole lot these days.
I'm really going to sleep now.

But this means people will have to actually play instead of modding all the time!

But seriously, very nice idea. I too very much like to watch a character develop.

A fast updating home page that loads achievements from players as they happen might also be cool - like a shout out wall or twitter I guess. Gaaark the orc barbarian is entering xxx ancestral tomb, Wilfred the woodelf mage has been named nerevarine, Kaz the argonian scout has found the backbiter bow, Toby the breton thief has reached 15th level... etc etc. Colour coded for world/lore events vs game mechanic things like leveling, or make the choice a toggle to see either or all. Lots of options I guess.

If the characters in the db do end up as NPCs in other peoples games I don't mind the arena idea but I think I'd prefer them to appear MCA like. In towns and out in the wilderness. Perhaps as companions as mentioned.

Look forward to see where this goes, good luck.


:)

-KWM



ps - off topic, any progress on the combat overhaul that was discussed some time ago?

Now this is really interesting. I usually don't care for such "status" things, but this is Morrowind!

God exists and he mods for morrowind

So who's Jesus?

Seriously though, this sounds amazing.

Exquisite handy work of the database, Fliggerty!

I believe that this will turn out wonderful in the end. The only flaw I can discover by goggling is the character table, its feels a bit long. I advise you of, perhaps, changing it by also bernas mention, dividing it into several parts. Makes it easier to scope through the databases for the programmers, and the users should also receive a shorter down-time. As for the structure, wow. You, sure your not a professional programmer? Regardless, exquisitely done!

No objections from my side.

Hm. I think this brings an excellent opportunity to follow the players in-game progress, specially the MQ. The quest-instructions and saga should be able to fit neatly in a text-file. I believe the main quests - thus, the headlines - should all exist in one page. That gives the people over viewing the "players" site, a possibility to see the persons story-based progress. I have more ideas on this subject, but to not confuse or bring a headache to someone, I will stop, for now.

I don't know exactly what this is, but assume it the characters location in the game. Can this be done dynamically? I was thinking a world-map, and adding map-locator's "activators" to the important parts of the game such one as Vivec, Balmora, Suran.. you get the picture. Perhaps even making it co-operate with the original map locating system in Morrowind. It would be fun, time from time, to see where the other persons is, While doing your own adventure. Maybe.. (it's a long-shoot) But, maybe you can add more rows to this column, telling former positions, thus and creating a "tail" where you which way or, in what direction you are heading. Just an idea, thou.
No need for further explaining, as beras did a superb job on how to maintain this.

Looks good, but I missing a column for a player to write a comment of his print. Thats just my theory. But, if the column is created it should contain of its own ID, perhaps linked through the Comment table back here. I surely believe you will figure out a solution.

Nothing, except for the screenshot function, then.

Love your project, enthusiasm and will so-far Fliggerty. Can only say, to you and your co-workers, keep up the good work. :goodjob:
I hope my theories can sprinkle some ideas, and hopefully good ones.


Cheerio.

Very interesting idea.
In my opinion character table has too many rows, so I would use a few child tables for it:
One containing all skills with information on their level and whether they were misc/minor or major, whether they were damaged, drained or fortified.
Similar a table for attributes.
A table consisting of all, the spells, which the character has (you forgot the spells).
Maybe a similar table for the inventories (with added information on whether they were equipped).

I believe that would be a better design, especially since you don't know how many spells and items a player has.

Lol, this way you could see wild spikes in stats.

Fligg, I understand Illuminiel's concerns, will the manual upload be similar to uploading to PES or rapidshare?

I like the idea, as being something similar to Achievements in Microsoft Live - so I can always see where my nephew is up to in Fallout 3 by seeing what achievement points he's got.

So one aim is to have other PCs running around your own MW game ala MCA? Sounds like fun, although I can already feel the equipment envy thanks to my frequent restarting. If -- or should that be when? -- this facet is pulled off, would the NPC version update as the character data did? That would be interesting to see, particularly if you had them as companions at the time.

I'd love to be able to help, but my database-fu is pretty much non-existant. On the other hand, should you need testers I should be good for that. Now that I think on it, this'll be the project that gets me signed up to GHF rather than just lurking on the Better Battles thread. ^_^