Friday, June 22, 2012

Notoriously Databased v0.21

Now that the tables and relationships were mapped out on paper, I then put them into a fresh database. Pretty easy after finding out some autoincrement foibles in the previous testing. 8 tables in all:

Users will have Characters inside each Game. The Characters will have CharacterSubmissions for each turn in what to do with their dungeon, which, once processed, is stored as the DungeonState. The Game will also revolve around a single village, that has VillageStates stored for each Turn. 

(Almost elemental facts, but meh ...)

Generating the App
Next step was to create the new MVC3 C# app. There were a number of steps that I needed memory jogging on, but I finally got the app set up with the database brought in by adding an ADO.NET Entity Data Model class to the Models directory. 

Account Control
The standard template came with ASP users and login hooked into a separate db, and not having to recreate login scripts and password obsfucation seemed like a good thing so I set about attempting to integrate it. Since the final version will be hosted online in AppHarbor using only one database, it seemed logical to bring all the ASP database tables into my own db and possibly link into the User table.

After some research it turns out that placing relationships onto the aspnet_user table is a bad thing in the long run, so I left the original tables as they were and added another field to handle linking to the unique ID. Hopefully I'll be able to link them together using API calls once the whole thing gets going. Creating the aspnet_ tables was relatively easy with aspnet_regsql , and apparently there's a lot more stored procedures that I would have missed if I tried to bring it over from the generated db file.

Once the db's had been merged, the next thing was to get the models to be merged. After a little bit of frustration, it seemed that the AccountModels didn't like accessing the aspnet files under the entity data model, so it was easier in the end to leave the connection string as a separate connection adressing the db with a simple SqlClient. The future problem will be that once I move it all to AppHarbor, it will only migrate one of the connection strings internally, so I'll have to manually update the ConnectionString to permenently point to the online db. Shouldn't be too much of a drama as there's not going to be much separation between production and testing.

Adding Controllers
User accounts complete! Adding controllers for the new tables worked relatively well once I figured out that the Data context class it was after was the complete NotoriousEntities class. Since I had added and removed it a couple of times, I had to restart VS 2010 to get it to pick it up. I'd heard that I should be able to refresh the database if it changed (because dropping the entity model and recreating is a pain) and once you double-click on the edmx file you can 'Update Model from Database..' from the NotoriousModel. Tested and working MUCH better.

Moving to AppHarbor.
I followed the general instructions to set up the new application, but finding the git info still needed a visit to the knowledge base. After previous tests with local git, I'd settled on integrating it into VS 2010.  Right click on the entire project in the project tree enabled me to start up a local git instance, then update it either with the Git Pending Changes addon, or straight to Git Bash. The shell will eventually be more useful, as once the remote git is set up I'll be constantly running the update and push commands. Update is nicer in the addon, but I can't see a push.

AppHarbor with SQL.  
It was easy to pick the free SQL shared server, but finding the details of the server to manually connect once again seemed harder than I remembered. There some new configuation variables that indicated a different method for connecting, but I finally found the options I needed by going to the SQL server. The connection string is going to be the main one to the entity model, and because of that it also needed the resource metadata to complete the connection string rewrite. After looking at the output, I pinched the ConnectionString data for the server itself and placed it over the account model connection. Seems to be working, but getting errors. Doh! no tables!

Once connected using SQL Server Management studio, the next task was to get all of the tables across. From the local database I could run a 'Generate Scripts..' task and point to all the tables. Hopefully the aspnet tables will be fine coming across as I'm pretty sure I won't be able to run aspnet_regsql over on the server.

Databases look good, but running the app looks like there's still issues. No stored procedures. Looks like I can output generation instruction for all stored procedures as a script in itself, and with a bit of massaging I got it to work. Now I need a schema :/.

Looks like I can get all the elements from generating scripts, so back to square one. Generating the complete database brings in too much database specific changes, so I tried picking all the items instead. Some ALTER ROLE scripts were ging to fail, but the rest seemed Ok so I merged them and manually added the role members using the GUI.

Still having issues with the schema, stating that it's an older, incompatible version; a task for another day ...   

EDIT: After a bit of Googling, it seems that the schema was fine, just aspnet requires some data to validate against. A quick merge of aspnet data files and it's all working. Now to build an integrated admin panel for manipulating the game data directly...