• Welcome to the new COTI server. We've moved the Citizens to a new server. Please let us know in the COTI Website issue forum if you find any problems.
  • We, the systems administration staff, apologize for this unexpected outage of the boards. We have resolved the root cause of the problem and there should be no further disruptions.

Using SQLite to track characters

L

Leitz

SOC-14 1K
Admin Award
Baron
Learning LEFT OUTER JOIN. You can use most any programming language with SQLite. If anyone is interested, I can explain more.

The result

Code: 
Jakob|Domici|CbtR|2
Jakob|Domici|Kissing|1
Jakob|Domici|Blade|2
Liv|Ellis|Kissing|2
Liv|Ellis|Lockpicking|2
Liv|Ellis|Stealth|1

The query

Code: 
select 
  p.f_name,
  p.l_name,
  (SELECT sl.skill FROM skill_list sl WHERE psl.skill_idx = sl.idx) as skill_name,
  psl.skill_level
  
from people p left outer join people_skill_level psl on p.idx = psl.people_idx;

The data

Spoiler:

Code: 
DROP TABLE IF EXISTS skill_list;
CREATE TABLE skill_list (
  idx INTEGER PRIMARY KEY,
  skill TEXT
) ;

INSERT INTO skill_list (skill) VALUES ('CbtR');
INSERT INTO skill_list (skill) VALUES ('Kissing');
INSERT INTO skill_list (skill) VALUES ('Blade');
INSERT INTO skill_list (skill) VALUES ('Lockpicking');
INSERT INTO skill_list (skill) VALUES ('Stealth');

DROP TABLE IF EXISTS people;
CREATE TABLE people (
  idx INTEGER PRIMARY KEY,
  f_name TEXT,
  l_name TEXT
);

INSERT INTO people (f_name, l_name) VALUES ('Jakob', 'Domici');
INSERT INTO people (f_name, l_name) VALUES ('Liv', 'Ellis');

DROP TABLE IF EXISTS people_skill_level;
CREATE TABLE people_skill_level (
  idx INTEGER PRIMARY KEY,
  people_idx INTEGER NOT NULL REFERENCES people(idx),
  skill_idx INTEGER NOT NULL REFERENCES skill_list(idx),
  skill_level INTEGER NOT NULL 
);

INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (1, 1, 2);
INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (1, 2, 1);
INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (1, 3, 2);
INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (2, 2, 2);
INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (2, 4, 2);
INSERT INTO people_skill_level (people_idx, skill_idx, skill_level) VALUES (2, 5, 1);
 
Well done. I'm lazy when not at work, and would have not have normalized the tables as well. My first impression of the data inserted was, "He's planning for an 'planetary romance' campaign".
 
Well, Jakob is the PC. He shoots some people and kisses other. Hopefully he remembers which is which...
 
You must log in or register to reply here.
Back
Top