SQL Databases and Prefix Suffix Loot Tables

Muuss · #12 10-06-2004, 07:32 PM

This may be more complete (and incomplete) :

Create a new database, and source that SQL code :

Code:

CREATE TABLE item (
  id int(11) NOT NULL auto_increment,
  name varchar(32) NOT NULL default '',
  factor int(11) NOT NULL default '1',
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) TYPE=MyISAM;


INSERT INTO item VALUES (1, 'chestplate', 40);
INSERT INTO item VALUES (2, 'helm', 25);
INSERT INTO item VALUES (3, 'bracer', 20);

CREATE TABLE prefix (
  id int(11) NOT NULL auto_increment,
  name varchar(32) NOT NULL default '',
  factor int(11) NOT NULL default '1',
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) TYPE=MyISAM;

INSERT INTO prefix VALUES (1, 'Rusty', 10);
INSERT INTO prefix VALUES (2, 'Corroded', 12);
INSERT INTO prefix VALUES (3, 'Torn', 9);
INSERT INTO prefix VALUES (4, 'Crested', 18);

CREATE TABLE suffix (
  id int(11) NOT NULL auto_increment,
  name varchar(32) NOT NULL default '',
  ac int(11) NOT NULL default '0',
  str int(11) NOT NULL default '0',
  sta int(11) NOT NULL default '0',
  dex int(11) NOT NULL default '0',
  int int(11) NOT NULL default '0',
  wis int(11) NOT NULL default '0',
  cha int(11) NOT NULL default '0',
  end int(11) NOT NULL default '0',
  hp int(11) NOT NULL default '0',
  mana int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) TYPE=MyISAM;

INSERT INTO suffix VALUES (1, 'of Vindication', 8, 3, 5, 0, 0, 0, 0, 40, 20, 0);
INSERT INTO suffix VALUES (2, 'of the Firebeetle', 3, 0, 1, 2, 0, 0, 0, 0, 0, 0);

then run that query :

Code:

select concat("INSERT INTO items SET name='",p.name,' ',i.name,' ',s.name,"', ac=",floor(p.factor*i.factor*s.ac/100),', sta=',floor(p.factor*i.factor*s.sta/100),', str=',floor(p.factor*i.factor*s.str/100),', dex=',floor(p.factor*i.factor*s.dex/100),', hp=',floor(p.factor*i.factor*s.hp/100),';') from prefix AS p, item AS i, suffix AS s ORDER BY s.name;

INSERT INTO items SET name='Rusty helm of the Firebeetle',ac=7,sta=2,str=0,dex=5,hp=0;
INSERT INTO items SET name='Corroded helm of the Firebeetle',ac=9,sta=3,str=0,dex=6,hp=0;
INSERT INTO items SET name='Torn helm of the Firebeetle',ac=6,sta=2,str=0,dex=4,hp=0;
INSERT INTO items SET name='Crested helm of the Firebeetle',ac=13,sta=4,str=0,dex=9,hp=0;
INSERT INTO items SET name='Rusty bracer of the Firebeetle',ac=6,sta=2,str=0,dex=4,hp=0;
INSERT INTO items SET name='Corroded bracer of the Firebeetle',ac=7,sta=2,str=0,dex=4,hp=0;
INSERT INTO items SET name='Torn bracer of the Firebeetle',ac=5,sta=1,str=0,dex=3,hp=0;
INSERT INTO items SET name='Crested bracer of the Firebeetle',ac=10,sta=3,str=0,dex=7,hp=0;
...
INSERT INTO items SET name='Corroded bracer of Vindication',ac=19,sta=12,str=7,dex=0,hp=48;
INSERT INTO items SET name='Torn bracer of Vindication',ac=14,sta=9,str=5,dex=0,hp=36;
INSERT INTO items SET name='Crested bracer of Vindication',ac=28,sta=18,str=10,dex=0,hp=72;

The query and the prefix tables are obviously not complete, but adding the missing part shouldn't be to hard.