SQL Databases and Prefix Suffix Loot Tables

[maveren]

I was wondering if EQEmu using an SQL Database would be able to produce a loot structure that contains Prefix/Model/Sufix entities. If it is possible I would like to create a item database that consist of p=500, m=#, s=500. Millions and millions of different unique items.

[Muuss]

You mean, something like :

table prefix

A
The
()

table model

sword
dagger
breastplate

table suffix

of vindication
of rage
of Muuss

and combine it to produce :

A dagger of rage
Breastplate of vindication
The sword of Muuss
dagger of vindication
The dagger of vindication
etc...

This is not possible atm with eqemu. The server isnt able to produce random gear.
To do it statically, IE adding it yourself to the database, you would have to write a full set of scripts, with these tables, adding restrictions and more combinations, with their limits....
After that, the question of stats would appear, how to combine them ? how to produce something homogene enough ?

Muuss' Breastplate ?
Breastplate of Muuss ?

If all this was working, it may produce some datas to source to the items table and... now what ? mobs would have to drop them, so you would starting to add them to the loot tables. You could also replace the actual items by the new ones, expecting to avoid to give a 70 AC BP to a firebeetle

So for your question, as i understand it, the answer is "no, not natively, but you can still do it by yourself !"

[Edgar1898]

Not really, you could do this with a couple of sql commands.

[sotonin]

But why would you want too..... this isn't diablo....

[maveren]

Prefix:
Corroded
Fabled
Prestine
Moldy
Torn
Worn
Dull
Eradicating

Model:
Breastplate
Earing
Sword
Staff
Tunic Dagger

Sufix
of Vindication
of the Unliving
of the Lion
ext.

The sufixes would be based on the mob your killing...
the prefixes would be random and many, up to 500 different prefixes... So in the end you could Kill Vindi and get a "Rusty~Breastplate~of Vindication" or the more valuable "Crested~Breastplate~of Vindication" or one of the other 498~Breastplates~ of Vindication... You get the picture.

I would have to add the sufix to the acuall mob table, and then have it pick the prefix which is the standard loot table. Acually this wouldnt be any harder to assemble in the long run, and takes out alot of the work involved in creating new items. adding 10 prefixes to the database would essencially add over 1000 items to the game, or more. Much like S.O.E. did during there Fabled Events.

The reason that I would want to do this:
Host a smaller world, characters/Goals would be unique, twinking would be obsolite, and if you have played Diablo, you know running inst fun, but getting upgrades everytime you play the game is. With a high population server, thie would be an ideal way for characters to further customize ther characters by chance, not by just going to a zone and expecting the exact item everybody else in the guild or group has.

This also makes the game more realistic, how many identical and i mean compleatly identical down to the defects/flaws/blemishes items exist? None, and the possibility of you getting the very best (Statistical Enhancement) on the item your camping, which could have multiple priority stats, being the most mana on an earing, to the most AC/HPs on the same prefix. Id see alot more loot rotting on the ground, and thats never a bad thing.

Also keep in mind the casual players, they have the ability to get the better gear, with less a chance of striking gold. "Seems very ideal to me."

[Muuss]

Originally Posted by Edgar1898

Not really, you could do this with a couple of sql commands.

LE, would you write , even if they're incomplete, those sql commands please ?. Unless i m wrong, you need to add the items to the items table and have the table reloaded by the server to have them available ingame, right ? how to do this dynamically ? I m kinda interested in the process too, i like the idea.
I know how to randomly modify the yet available gear by changing its name or/and stats, this isn't really hard, but i have no idea of how to do it at run time :

[sotonin]

Ya but why would you want 500 different bp's off a single mob. The mobs in EQ are usually set up with a lot of difficulty. If i got a rusty bp off vindi i would be extremely pissed. You go after him for the good shit.

[Muuss]

That's why i wonder how, in a few sql queries, you can obtain something adequate.

[maveren]

when your working with a server population of about 100 members max, and you only have one mob that drops the ideal breastplate for tanking classes. everybody would have that same breastplate within a couple weeks. As soon as one group can farm them.
Also when people can solo mobs for experance and get 12 different versions of the drop to choose from.
I think most people set there standards to high and expect the best items, but it the standard happens to be the rusty version of the item for a level 20 character, and htey happen to by chance get somthing rare, or in the uper 20% of the prefix table, it could be well worth the fight.
Think of the Vidi BP now, and the work it took to get it, yeah it wouldnt be worth the fight and setup as you expect the standard version of the bp, but to a new server, where there is no standard, id settle for any upgrades, and its nice to know the next time we do him he could drop somthing extreamly uber.

[Edgar1898]

Simple analogy follows:

create a simple table and fill it with our test data like so:

CREATE TABLE `item_names` (`prefix` VARCHAR (32));
ALTER TABLE `item_names` ADD `model` VARCHAR(32);
ALTER TABLE `item_names` ADD `suffix` VARCHAR(32);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Corroded', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Fabled', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Prestine', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Moldy', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Torn', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Worn', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Dull', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES ('Eradicating', NULL, NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, 'Breastplate', NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, 'Earing', NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, 'Sword', NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, 'Staff ', NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, 'Tunic Dagger', NULL);
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, NULL, 'of Vindication');
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, NULL, 'of the Unliving');
INSERT INTO item_names (prefix, model, suffix) VALUES (NULL, NULL, 'of the Lion');

then run the select query to get all the item names:

select concat(a.prefix,' ',b.model, ' ', c.suffix) from item_names a, item_names b, item_names c where length(concat(a.prefix,' ',b.model, ' ', c.suffix))>1 group by a.prefix,b.model,c.suffix;

The 120 results of the above command are:

• Corroded Breastplate of the Lion
  Corroded Breastplate of the Unliving
  Corroded Breastplate of Vindication
  Corroded Earing of the Lion
  Corroded Earing of the Unliving
  Corroded Earing of Vindication
  Corroded Staff of the Lion
  Corroded Staff of the Unliving
  Corroded Staff of Vindication
  Corroded Sword of the Lion
  Corroded Sword of the Unliving
  Corroded Sword of Vindication
  Corroded Tunic Dagger of the Lion
  Corroded Tunic Dagger of the Unliving
  Corroded Tunic Dagger of Vindication
  Dull Breastplate of the Lion
  Dull Breastplate of the Unliving
  Dull Breastplate of Vindication
  Dull Earing of the Lion
  Dull Earing of the Unliving
  Dull Earing of Vindication
  Dull Staff of the Lion
  Dull Staff of the Unliving
  Dull Staff of Vindication
  Dull Sword of the Lion
  Dull Sword of the Unliving
  Dull Sword of Vindication
  Dull Tunic Dagger of the Lion
  Dull Tunic Dagger of the Unliving
  Dull Tunic Dagger of Vindication
  Eradicating Breastplate of the Lion
  Eradicating Breastplate of the Unliving
  Eradicating Breastplate of Vindication
  Eradicating Earing of the Lion
  Eradicating Earing of the Unliving
  Eradicating Earing of Vindication
  Eradicating Staff of the Lion
  Eradicating Staff of the Unliving
  Eradicating Staff of Vindication
  Eradicating Sword of the Lion
  Eradicating Sword of the Unliving
  Eradicating Sword of Vindication
  Eradicating Tunic Dagger of the Lion
  Eradicating Tunic Dagger of the Unliving
  Eradicating Tunic Dagger of Vindication
  Fabled Breastplate of the Lion
  Fabled Breastplate of the Unliving
  Fabled Breastplate of Vindication
  Fabled Earing of the Lion
  Fabled Earing of the Unliving
  Fabled Earing of Vindication
  Fabled Staff of the Lion
  Fabled Staff of the Unliving
  Fabled Staff of Vindication
  Fabled Sword of the Lion
  Fabled Sword of the Unliving
  Fabled Sword of Vindication
  Fabled Tunic Dagger of the Lion
  Fabled Tunic Dagger of the Unliving
  Fabled Tunic Dagger of Vindication
  Moldy Breastplate of the Lion
  Moldy Breastplate of the Unliving
  Moldy Breastplate of Vindication
  Moldy Earing of the Lion
  Moldy Earing of the Unliving
  Moldy Earing of Vindication
  Moldy Staff of the Lion
  Moldy Staff of the Unliving
  Moldy Staff of Vindication
  Moldy Sword of the Lion
  Moldy Sword of the Unliving
  Moldy Sword of Vindication
  Moldy Tunic Dagger of the Lion
  Moldy Tunic Dagger of the Unliving
  Moldy Tunic Dagger of Vindication
  Prestine Breastplate of the Lion
  Prestine Breastplate of the Unliving
  Prestine Breastplate of Vindication
  Prestine Earing of the Lion
  Prestine Earing of the Unliving
  Prestine Earing of Vindication
  Prestine Staff of the Lion
  Prestine Staff of the Unliving
  Prestine Staff of Vindication
  Prestine Sword of the Lion
  Prestine Sword of the Unliving
  Prestine Sword of Vindication
  Prestine Tunic Dagger of the Lion
  Prestine Tunic Dagger of the Unliving
  Prestine Tunic Dagger of Vindication
  Torn Breastplate of the Lion
  Torn Breastplate of the Unliving
  Torn Breastplate of Vindication
  Torn Earing of the Lion
  Torn Earing of the Unliving
  Torn Earing of Vindication
  Torn Staff of the Lion
  Torn Staff of the Unliving
  Torn Staff of Vindication
  Torn Sword of the Lion
  Torn Sword of the Unliving
  Torn Sword of Vindication
  Torn Tunic Dagger of the Lion
  Torn Tunic Dagger of the Unliving
  Torn Tunic Dagger of Vindication
  Worn Breastplate of the Lion
  Worn Breastplate of the Unliving
  Worn Breastplate of Vindication
  Worn Earing of the Lion
  Worn Earing of the Unliving
  Worn Earing of Vindication
  Worn Staff of the Lion
  Worn Staff of the Unliving
  Worn Staff of Vindication
  Worn Sword of the Lion
  Worn Sword of the Unliving
  Worn Sword of Vindication
  Worn Tunic Dagger of the Lion
  Worn Tunic Dagger of the Unliving
  Worn Tunic Dagger of Vindication

Note that this is just a simple example, but if you use your imagination, you could create items using this process. It would take a lot of work because you have a lot more fields to work with but it would work...

[maveren]

thank you for taking your time to explane this.


Edit:
Much Obliged!

[Muuss]

This may be more complete (and incomplete) :

Create a new database, and source that SQL 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 :

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.