monster table
- name (text, primary key)
- skill (integer)
- stamina (integer)
- treasure (integer)
equip table
- monname (text, primary key)
- equipname (text, primary key)
- equiptype (text)
- lootable (integer)
- adjust (integer)
paragraph table
- module (text, primary key)
- pararef (text, primary key)
- paratext (text)
- monster (text)
- looseitem (text)
options table
- module (text, primary key)
- startpararef (text, primary key)
- endpararef (text, primary key)
- optiontext (text)
- conditions (text)
I won't go into a detailed explanation of each column, suffice to say there are a mix of text and integers, and some fields are primary keys.
For those who are not familiar with primary keys, these are columns that are used to single out and identify each record. If a table has a single column as its primary key, each record in the table must have a unique value for that column. So for example, in the monster table there is one column as the primary key, name. Each entry must have a unique name value. If a table has 2 or more columns as primary keys, then the combination of those columns must be unique. For example, an entry in the paragraph table must have a unique combination of module column and pararef column.
To add new records, delete unwanted records and edit existing records in all four tables I have created two different programs using two different approaches. The first one took quite an abstract approach, treating the tables as unrelated and not really taking into account the individual needs of each table or each column. The first program uses the PRAGMA command in SQLite to retrieve information about each table, namely which columns are primary keys and also which are text or numbers. This means that the program won't try to input text values into columns for integers. It should also catch duplicate primary keys. The good news for this approach is that it is very adaptable, and if I change the tables or even use it on a different database it should still work. The program itself is 263 lines of code & comments
The second one is much more customised and takes into account the relationships between tables.
Although in Python a variable can represent a list, tuple or dictionary (a series of different values, called an iterable), in SQLite they don't use lists or other iterable datatypes - you create more tables and elements that would be in the list become records in the new table.
So if a monster has several items of equipment associated with it, rather than having a column called equip, there is a separate table. One column (here monname) says what monster record each item in the table is associated with. Here the value in monname should match up with a value in the name column of the monster table. Another column (here equipname) identifies each item associated with that monster. It is no coincidence that monname and equipname are the two columns that form the primary key for this table - the combination of monname and equipname should be unique and identify each entry in the equip table.
This is a similar situation to paragraphs and options - each paragraph typically has several options associated with it, though each option has its own characteristics associated with it.
So when entering a new paragraph, the program will ask whether the user also wants to enter options associated with that paragraph. Similarly, when deleting an unwanted paragraph, the program will offer to delete options associated with that paragraph (where startpararef value in options matches pararef value in paragraph). Also if the primary key (pararef) value of a paragraph record is changed, the program offers to update the startpararef value of options associated with the paragraph. These functions are also adapted to the situation with monsters and associated equipment.
This customised version is a lot longer at 665 lines - as well as writing functions that can cope with any table, I have also written functions to deal with specific tables. Is it better? When I am using it to add data to the database, yes I believe so. But I have to hope that I won't be changing the SQLite table structure because the second version assumes the database is set up in a particular way.
Incidentally, both versions can present the contents of each table in HTML. The first one can do so in a straightforward, no-frills way.
Current Contents of paragraph
| module | pararef | paratext | monster | looseitem |
| haunted | 001 | The storm is raging, and you are pelted by rain and hail. You take shelter in the porch of an old creaking house. Perhaps you can find food and a bed for the night? You knock on the door, but it is not locked and swings open. You consider going in or looking around the outside of the house. | ||
| haunted | 001a | You find yourself at the front of the house. The front door is still ajar. There are stretches of garden either side to the north and south. | ||
| haunted | 002 | You walk around to the north side of the house. The windows on the ground floor are all boarded up and you cannot see in. Through the pelting rain you can see what may be a vegetable garden further to the north. The garden continues along the side of the house. | ||
| haunted | 003 | Walking around the south side of the house you come across a large pond. In the middle is a statue of what may be a satyr or a goatish demon. The water in the pond looks murky with pondweed. The south side of the house continues ahead. | ||
| haunted | 003a | As you step into the cold murky water the mud covers your ankles. You move in, towards the mysterious statue. You suddenly see from the corner of your eye something moving in the water towards you. Something dark green, slimy and with long limbs lurches at you from under the water. You must defend yourself against the giant frog! | Giant frog | |
| haunted | 005 | You approach the vegetable patch, and notice some figure shuffling around behind the canes supporting runner beans. Covered in rags and wielding a pitchfork it lurches towards you. As you see its rotting face, you realize the gardener is an undead zombie and you must fight it! | Zombie | |
| haunted | 005a | You approach the vegetable patch. The remains of a defeated zombie are scattered over the cabbages and broccoli. Just to the west you can see a surprisingly large garden shed. |
The second one has the option of including the options associated with each paragraph:
Current Contents of Paragraph & Options table
| module | pararef | paratext | monster | looseitem | options |
| haunted | 001 | The storm is raging, and you are pelted by rain and hail. You take shelter in the porch of an old creaking house. Perhaps you can find food and a bed for the night? You knock on the door, but it is not locked and swings open. You consider going in or looking around the outside of the house. | ('Do you go left around the north side of the house?', '', '002') ('Do you go right around the south outside of the house?', '', '003') ('Do you step into the dark house?', '', '004') | ||
| haunted | 001a | You find yourself at the front of the house. The front door is still ajar. There are stretches of garden either side to the north and south. | ('Head left to the north side of the house?', '', '002') ('Head right to the south side of the house?', '', '003') ('Push open the door and go into the house?', '', '004') | ||
| haunted | 002 | You walk around to the north side of the house. The windows on the ground floor are all boarded up and you cannot see in. Through the pelting rain you can see what may be a vegetable garden further to the north. The garden continues along the side of the house. | ('Go over to the gardening figure in the vegetable patch?', 'Alive:Zombie', '005') ('Carry on along the north side of the house?', '', '006') ('Head back east to the front door?', '', '001a') ('Head over to the vegetable patch to the north?', 'Dead:Zombie', '005a') | ||
| haunted | 003 | Walking around the south side of the house you come across a large pond. In the middle is a statue of what may be a satyr or a goatish demon. The water in the pond looks murky with pondweed. The south side of the house continues ahead. | ('Wade into the pond to investigate the statue?', 'Alive:Killer frog', '003a') ('Wade into the pond to investigate the statue?', 'Dead:Killer frog', '003b') ('Head back east round to the front door?', '', '001a') | ||
| haunted | 003a | As you step into the cold murky water the mud covers your ankles. You move in, towards the mysterious statue. You suddenly see from the corner of your eye something moving in the water towards you. Something dark green, slimy and with long limbs lurches at you from under the water. You must defend yourself against the giant frog! | Giant frog | ||
| haunted | 005 | You approach the vegetable patch, and notice some figure shuffling around behind the canes supporting runner beans. Covered in rags and wielding a pitchfork it lurches towards you. As you see its rotting face, you realize the gardener is an undead zombie and you must fight it! | Zombie | ||
| haunted | 005a | You approach the vegetable patch. The remains of a defeated zombie are scattered over the cabbages and broccoli. Just to the west you can see a surprisingly large garden shed. |
