SQL Setting Up SqLite Tables

[Recoil]

I'm checking things out and trying to get a feel for setting this up.  My knowledge of databases is a bit rusty, and my experience was limited to working with Access DB files in Visual Studio...like 8-9 years ago.

My biggest concern is the foreign key fields.  Currently when I launch OTT I add/launch a campaign.  Adding creates a new folder in the application directory, with an options file specific to that campaign, along with a folder that houses the map files.  Maps are specific to a campaign.

When the campaign is launched and on a map, I can add unique tokens to that map.  So, tokens created on Map1 are not accessible on Map2 and must be recreated.  They are unique to each map, and are not like separate NPC's.

Campaign Table:

CREATE TABLE `CAMPAIGNS` (
`CAMPAIGN_ID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`CAMPAIGN_NAME`	TEXT NOT NULL DEFAULT 'New Campaign',
`SETTINGS_LAST_MAP`	INTEGER NOT NULL DEFAULT 1,
`SETTINGS_AUTO_UPDATE_PLAYERS`	INTEGER NOT NULL DEFAULT 0,
`SETTINGS_MAX_MAP_X`	INTEGER NOT NULL DEFAULT 27,
`SETTINGS_MAX_MAP_Y`	INTEGER NOT NULL DEFAULT 15,
`SETTINGS_RESOLUTION`	INTEGER NOT NULL DEFAULT 0,
`TIME_LAST_HOUR`	INTEGER NOT NULL DEFAULT 9,
`TIME_LAST_MINUTE`	INTEGER NOT NULL DEFAULT 0,
`LEVELS_DAWN`	INTEGER NOT NULL DEFAULT 180,
`LEVELS_MIDDAY`	INTEGER NOT NULL DEFAULT 0,
`LEVELS_DUSK`	INTEGER NOT NULL DEFAULT 180,
`LEVELS_NIGHT`	INTEGER NOT NULL DEFAULT 200,
`LEVELS_CAVE`	INTEGER NOT NULL DEFAULT 220,
`GRID_SHOW_GRID_LINES`	INTEGER NOT NULL DEFAULT 1,
`GRID_RED_VALUE`	INTEGER NOT NULL DEFAULT 255,
`GRID_GREEN_VALUE`	INTEGER NOT NULL DEFAULT 255,
`GRID_BLUE_VALUE`	INTEGER NOT NULL DEFAULT 255,
`GRID_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_TILES_AUTO_SHADOW`	INTEGER NOT NULL DEFAULT 1,
`LAYERS_GROUND_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_CLUTTER_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_WALLS_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_SHADOWS_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_OVERHEAD_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`LAYERS_FOW_OPACITY`	INTEGER NOT NULL DEFAULT 255,
`TOKENS_SHOW_TOKENS`	INTEGER NOT NULL DEFAULT 1,
`TOKENS_TOKEN_OPACITY`	INTEGER NOT NULL DEFAULT 255
)

 

In my Maps Table, I have an extra field for the Campaign_ID that a map is tied to [ ForeignKey: `CAMPAIGNS`(`CAMPAIGN_ID`) ].  My understanding is that when launched into the campaign it will only be able to display maps specific to that campaign:

Maps Table:

CREATE TABLE "MAPS" (
`MAP_ID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`MAP_CAMPAIGN_ID`	INTEGER NOT NULL,
`MAP_NAME`	TEXT NOT NULL DEFAULT 'Empty',
`MAP_REVISION`	INTEGER NOT NULL DEFAULT 0,
`MAP_TILESET`	INTEGER NOT NULL DEFAULT 0,
`MAP_TYPE`	INTEGER NOT NULL DEFAULT 0,
`MAP_NORTH`	INTEGER NOT NULL DEFAULT 0,
`MAP_SOUTH`	INTEGER NOT NULL DEFAULT 0,
`MAP_WEST`	INTEGER NOT NULL DEFAULT 0,
`MAP_EAST`	INTEGER NOT NULL DEFAULT 0,
`MAP_MAX_X`	INTEGER NOT NULL DEFAULT 27,
`MAP_MAX_Y`	INTEGER NOT NULL DEFAULT 15,
`MAP_TILES`	BLOB,
FOREIGN KEY(`MAP_CAMPAIGN_ID`) REFERENCES CAMPAIGNS(CAMPAIGN_ID)
)

 

Likewise, in my Tokens Table, there is an extra field for the MAP_ID that this token is tied to [ ForeignKey: `MAPS`(`MAP_ID`) ].  When on the map it will only select the Tokens specific to that map:

Tokens Table

CREATE TABLE "TOKENS" (
`TOKEN_ID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`TOKEN_MAP_ID`	INTEGER NOT NULL,
`TOKEN_NAME`	TEXT NOT NULL DEFAULT 'Token',
`TOKEN_SPRITE`	INTEGER NOT NULL DEFAULT 0,
`TOKEN_X`	INTEGER NOT NULL DEFAULT 0,
`TOKEN_Y`	INTEGER NOT NULL DEFAULT 0,
`TOKEN_DIR`	INTEGER NOT NULL DEFAULT 0,
`TOKEN_SCALE`	INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY(`TOKEN_MAP_ID`) REFERENCES MAPS(MAP_ID)
)

 

In my experience, databases is not something you can really "wing-it" on, and work so much better when they are mapped out properly ahead of time.  So...I would like some advice on what I currently have setup.  Also, I am still unsure how I am going to go about the layers and tiles when saving and loading the maps.  I have that set to a BLOB data field [ `MAP_TILES` BLOB, ], but how am I supposed to compile the list of stuff that will go in there to save it, as well as read it back out when I am loading the map?

[Damian666]

just wing it man

that's what I am doing xD

[Python Panda]

I'd help but im not going to scroll line by line in the spoilers, as they don't show fully and you must scroll pretty much line by line. Either put the SQLite in quote blocks or just take out the spoilers and put them in a code block that is defined.

EDIT:

JC fixed it. I'll give it a look see and let you know.