D2 Loot Maker - feature update

[velotek]

Wow that was fast - didn't think I would see the mod that quick so I started just to write the db scripts (see below example) from scratch. I decided to base my loot drops off existing loot drop entries of other npc's in the same level range. The queries certainly work pretty fast, but the number of entries per npc certainly is huge. I am in the middle of rewriting the scripts into a stored proc, where I would execute a loop for each npc, within it would execute the below query to return only random 10 at a time. There is some flaws with this approach but certainly a start in the right direction.

Going to download your updates, as that is much easier - Thanks for the hard work.

example:

/* Add Loot Drop*/
insert into lootdrop (name) values ('custom_lvl_10_20_weapons');

/* Clean Up Drop Entries */
delete from lootdrop_entries where lootdrop_id = (select id from lootdrop where name = 'custom_lvl_10_20_weapons');

/* Attach items to loot drops */
/*insert into lootdrop_entries (lootdrop_id, item_id, item_charges, equip_item, chance)*/
SELECT distinct (select id from lootdrop where name = 'custom_lvl_10_20_weapons'),
       t.id, 1,1,15
from
(
SELECT i.id, i.name itemname, i.slots, i.itemtype, n.name npcname, n.level
FROM items i,
     lootdrop_entries lde,
     lootdrop ld,
     loottable_entries lte,
     loottable lt,
     npc_types n
where i.id = lde.item_id and
      lde.lootdrop_id = ld.id and
      ld.id = lte.lootdrop_id and
      lte.loottable_id = lt.id and
      lt.id = n.loottable_id and
      n.level >= 10 and n.level <=20 and
      i.itemtype in (0,1,2,3,4,35,45) and /* weapons only */
      slots in (8192,16384,24576,2097152,2048) and /* primary, secondary, pri/sec, ammo, range */
      i.name not like 'Rusty%'
) t;

/* Clean Up Loot Table Entries */
delete from loottable_entries where lootdrop_id = (select id from lootdrop where name = 'custom_lvl_10_20_weapons');

/* create loottable_entries for npc's in lvl range*/
insert into loottable_entries
SELECT distinct t.loottable_id,
       (select id from lootdrop where name = 'custom_lvl_10_20_weapons'),
       1,75
from
(
SELECT i.id, i.name itemname, i.slots, i.itemtype, n.name npcname, n.level, n.loottable_id
FROM items i,
     lootdrop_entries lde,
     lootdrop ld,
     loottable_entries lte,
     loottable lt,
     npc_types n
where i.id = lde.item_id and
      lde.lootdrop_id = ld.id and
      ld.id = lte.lootdrop_id and
      lte.loottable_id = lt.id and
      lt.id = n.loottable_id and
      n.level >= 10 and n.level <=20
) t
order by t.id;