[xsrcg-devel] SQL Dump Part 1

[Adam B. <co...@di...>]

Pasted below is the first draft of the SQLite dump of the rpgcharacter 
portion of the XML data. After going through I know we're going to need 
to clean it up a bit, and I'd probably suggest that instead of having 
the seperate attribute table we bring it in with the main character 
table, even though that'll make the main character table pretty large 
by itself since it'll need to have a ton of instances of the attribute 
table, but it might be better to do it that way.

Anyways, if you look through here and see anything jump out at you or 
whatever let me know. I haven't bothered to test it with SQLite yet, so 
it may not even work properly at this stage. It's more a proof of 
concept.

Once I get the rest of the stuff added I'll dump into SQLite and see 
how it works. I'll also probably work up a graphical chart showing all 
the relations to make grasping everything easier.

So, without further ado...


-- SQLite dump
--
-- This is an SQLite dump for XSRCG
--
--

-- 
-- Table structure for table 'realworld'
-- corresponds to 'realworld' XML
--
CREATE TABLE realworld (id INTEGER PRIMARY KEY,
     system_id INT,
     character_id INT,
     generation TEXT,
     creationdate TEXT,
     lastmoddate TEXT,
     fileid TEXT,
     player_id INT,
     concept TEXT,
     notes TEXT);

--
-- Table structure for table 'player'
-- subset of 'realworld' XML
--
CREATE TABLE player (id INTEGER PRIMARY KEY,
     playername TEXT,
     playeremail TEXT,
     playerurl TEXT);

--
-- Table structure for table 'system'
-- corresponds to 'system' XML
--
CREATE TABLE system (id INTEGER PRIMARY KEY,
     name TEXT,
     edition TEXT,
     variation TEXT,
     reference TEXT);

--
-- Table structure for table 'character'
-- corresponds to 'bio' XML
--
CREATE TABLE character (id INTEGER PRIMARY KEY,
     realworld_id INTEGER,
     gender TEXT,
     birthplace TEXT,
     birthdate TEXT,
     height TEXT,
     weight TEXT,
     eyecolor TEXT,
     haircolor TEXT,
     skincolor TEXT,
     buildtotal INT,
     earnedtotal INT,
     earnedremaining INT,
     archetype TEXT,
     nature TEXT,
     demeanor TEXT,
     illustration TEXT,
     history TEXT,
     note TEXT);


--
-- Table structure for table 'identity'
-- corresponds to 'identity' XML
--
CREATE TABLE identity (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     name TEXT,
     alias TEXT,
     level TEXT,
     sin TEXT,
     note TEXT);

--
-- Table structure for table 'race'
--
--
CREATE TABLE race (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     name TEXT,
     variant TEXT,
     ethnicity TEXT,
     initialcost TEXT);

--
-- Table structure for table 'asset'
-- corresponds to 'asset' XML
--
CREATE TABLE asset (id INTEGER PRIMARY KEY,
     character_id INT,
     name TEXT,
     type TEXT,
     variation TEXT,
     quantity NUMERIC,
     unitvalue NUMERIC,
     level INT,
     note TEXT);
     notes TEXT);

--
-- Table structure for table 'attrubute'
-- corresponds to 'atttribute' XML
--
CREATE TABLE attribute (id INTEGER PRIMARY KEY,
     character_id INT,
     name TEXT,
     value INT,
     initial INT,
     improvement INT,
     racialmod INT,
     natural INT,
     max INT,
     initialcost INT,
     additionalcost INT,
     note TEXT);

--
-- Table structure for table 'skill'
-- corresponds to 'skill' XML
--
CREATE TABLE skill (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     name TEXT,
     value INTEGER,
     initial INTEGER,
     initialcost INTEGER,
     additionalcost INTEGER,
     variation_id INTEGER,
     concentration_id INTEGER);

--
-- Table structure for table 'property'
-- corresponds to 'property' XML
--
CREATE TABLE property (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     name TEXT,
     value INTEGER,
     type TEXT,
     initial INTEGER,
     initialcost INTEGER,
     additionalcost INTEGER,
     note TEXT,
     details TEXT);

--
-- Table structure for table 'augmentation'
-- corresponds to 'augemntation' XML
--
CREATE TABLE augmentation (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     location TEXT,
     quality TEXT,
     condition TEXT,
     cost NUMERIC,
     impact NUMERIC,
     note TEXT);

--
-- Table structure for table 'advantage'
-- corresponds to 'reference' XML
--
CREATE TABLE advantage (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     name TEXT,
     initialcost INTEGER,
     additionalcost INTEGER,
     value INTEGER);

--
-- Table structure for table 'power'
-- corresponds to 'power' XML
--
CREATE TABLE power (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     name TEXT,
     value INTEGER,
     initial INTEGER,
     initialcost INTEGER,
     additionalcost INTEGER,
     variation text,
     note TEXT);

--
-- Table structure for table 'spell'
-- corresponds to 'spell' XML
--
CREATE TABLE spell (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     name TEXT,
     value INTEGER,
     initial INTEGER,
     initialcost INTEGER,
     additionalcost INTEGER,
     variation TEXT,
     note TEXT);

--
-- Table structure for table 'equipment'
-- corresponds to 'equipment' XML
--
CREATE TABLE equipment (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     count NUMERIC,
     units TEXT,
     carried TEXT,
     equipped TEXT,
     location TEXT,
     quality TEXT,
     condition TEXT,
     cost NUMERIC,
     name TEXT,
     note TEXT
     unique TEXT,
     contains TEXT);

--
-- Table structure for table 'transport'
-- corresponds to 'transport' XML
--
CREATE TABLE transport (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     refid INTEGER,
     condition TEXT,
     cost NUMERIC,
     name TEXT,
     note TEXT,
     unique TEXT,
     contains TEXT);

--
-- Table structure for table 'contact'
-- corresponds to 'contact' XML
--
CREATE TABLE contact (id INTEGER PRIMARY KEY,
     character_id INTEGER,
     name TEXT,
     relation TEXT,
     location TEXT,
     level INTEGER,
     initialcost INTEGER,
     additionalcost INTEGER,
     note TEXT);

--
Adam Brown
co...@di...