EQEmulator Forums - SQL Databases and Prefix Suffix Loot Tables

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Archive::General Discussion (https://www.eqemulator.org/forums/forumdisplay.php?f=613)

- - SQL Databases and Prefix Suffix Loot Tables (https://www.eqemulator.org/forums/showthread.php?t=16185)

SQL Databases and Prefix Suffix Loot Tables

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.

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 !"

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

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

:roll:

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."

Quote:

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 :?:

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.

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

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.

Simple analogy follows:

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

Code:

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:

Code:

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...

thank you for taking your time to explane this.

Edit:
Much Obliged!

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.