Database search capabilities

[Krewt]

One of the big advantages to having your information in a database, is the posibilities to make custom queries to return the results you are looking for.

Im missing the capability to do custom searches in the tradeskill database. Is there any chance of having that functionality implemented ?

For instance, I find myself looking for:
* Alchemy potions with specific stats.
* A list of alchemy potions including their duration.
* Smithing skillup based on a specific race.
* Smithing items with only storebought items.
* A list of all combines, including trivial and production price.
* A list of all combines, including production and (merchant) sale price.
* Possible combines with items from traders in a specific zone.
* All smithing combines that includes tempers made from items foraged in specific zones.

As you can see from this list of strange search criteria, this is not something that can be made using another prefabricated search.

Im working as a database technician and software developer myself, so I know that each of these search critiria should be possible to perform - However without access to make custom queries, Im finding myself having to browse through all of the entries to find what Im looking for.

[Krazick]

Yes, All of these (and many more) possible search combinations are concievable.

BUT..

and this is a MAJORLY BIG BUT...

WE ARE NOT READY FOR FURTHER ENCHANCMENTS AT THIS TIME!

We are still recovering from our database meltdown. And we have a number of other priorities that come BEFORE any user submitted requests are looked at.

WE WILL ASK when we are ready for updates.

[Ilona]

As regards the last item on your list:

Forage item
Go to EQTraders and type in item name
Hit search
Click on item name
Click on "Find recipes that use this item"
Scroll down til you find the temper, click on the temper name
Click on "Find recipes that use this item"

Yes, it takes a few clicks, but that search is possible as things stand.

Otherwise, see Krazick's response

[Ngreth Thergn]

Re: Database search capabilities

Originally posted by Krewt


For instance, I find myself looking for:
* Alchemy potions with specific stats.
* A list of alchemy potions including their duration.

well we don't have the "spells" that make the stats anywhere in our DB. so not posible with current DB.

* Smithing skillup based on a specific race.

go to your racial page, click on sort by trivial. You have it. Sure you don't have whjat ANYONE can do... Curently I just have a search on the items table, not on the recipes table (like this would require)

* Smithing items with only storebought items.

this is a cross table search I am not sure about allowing...

* A list of all combines, including trivial and production price.
* A list of all combines, including production and (merchant) sale price.

No. I will never allow this for a few reasons.
#1 it allows for too easy stealing of the DB, especiallialy if it generates ONE page. ane even with "continues" it can be botted easily.
#2 if it is one page... it hits the DB too hard. If multiple page with continue... what is the point? search for what you want.
#3 our price data is still way underpopulated.
#4 our price data is WAY WAY too contested.

* Possible combines with items from traders in a specific zone.

nasty cross table search, but this is sorta already in the plans

* All smithing combines that includes tempers made from items foraged in specific zones.

see ilona's post.

As you can see from this list of strange search criteria, this is not something that can be made using another prefabricated search.

Im working as a database technician and software developer myself, so I know that each of these search critiria should be possible to perform - However without access to make custom queries, Im finding myself having to browse through all of the entries to find what Im looking for.

Note. I am NOT a database technician. So all of this coding was done by an amature that tought himself in 5 days, and used help from another expert to get it done.

Now my other issue.

Allowing folks to make custom queries.

Safety

Yes I can make some limits, FORCE a max of 20 hits. Force it to be select only... I still see it as posible for someone to make a search that could bring down the DB server with its complexity. This may be my lack of knowlege, but I see it as a HUGE risk.

Also... what site allows you to make a custom search? I am talking publicly accessable website... not Intranet or private sites, not custom aplications for particular companies (not websites but aplications)

[Krewt]

First of all, I would like to thank you all for posting suggestions as how to find the "complicated" searches I stated as examples.

I would like to address a few of your replies so that you do not think I haven't considered your response:

(Q=Question, A=Answer, R=Response)

Q) All smithing combines that includes tempers made from items foraged in specific zones.
A) Click on "Find recipes that use this item".
R) Returning recipies on one item returns the recipies that uses that item only, not all the items found in a specific zone, and not only smithing combines. If I need to find out where to go forage to improve on my smithing, I will still need to go to the list of smithable items and see what is needed for each recipe.

Q) Alchemy potions with specific stats.
A) Well we don't have the "spells" that make the stats anywhere in our DB.
R) When clicking so see the details for each combine, the effect of the potion is listed there. It is however not listed in the list of components to make the potions.

Q) Smithing skillup based on a specific race.
A) Go to your racial page, click on sort by trivial.
R) I actually didn't know that you could sort based on trivial, on the racial page. Even though it doesn't give me a complete list of everything smithable for my race, it does make it easier to find.

The rest of the responses basically states that you do not know how to make it possible for people to do their custom searches, without overbourdening the server or slacking on security.

I understand your worries about both items. Even though its possible to achieve both goals, I know that we are not all database or security specialists. I realize that you dont make any money from this site and you put a lot of work into the database. I would offer my assistance, but from previous posts, I know that you have too much to do allready and are not accepting any offers to help.

I didn't know that you were concerned about people ripping off your database or listing all the data to make their own sites. This kinda changes my perspective a bit. If you need to protect your data, custom searches would not even be possible, without adding limitations to the functionallity. To me, there is only one good source of tradeskill information and thats what you made. Any other tradeskill sites only have a subset of information and people to make sure that the data is still very much valid and accurate (And wrong information is so much worse than missing information).

Thanks for running a site that makes tradeskilling a lot more manageable, and keep up the good work .

[Ngreth Thergn]

Originally posted by Krewt

Q) Alchemy potions with specific stats.
A) Well we don't have the "spells" that make the stats anywhere in our DB.
R) When clicking so see the details for each combine, the effect of the potion is listed there. It is however not listed in the list of components to make the potions.

Doh forgot that some of the potions are a bit diferent than others heh. If the stat changes are in the spell it is not going to be searchable, if it is in the potion description... then it will be. It is also posible that there are some inconsistancies in the way potions are entered in our database (also a posibllity in inconsistancies in the way that EQ itself makes potions...)

You should still be able to get these to show up in the "advanced search" (which I admit needs more work... but I will get to that eventually) I will poke around to see if it is broken there.

[Verdandi]

The stats granted by potions are a part of the effect and not inherent to the potion item itself. In other words, equipping the potion does not give you the stats. So the stats on potions are entered in the notes field and not in the item data the same way as stats on other items are. I suspect that means they're not searchable.

[Ngreth Thergn]

Yeah. I thouhgt so, which means there are a few inconsistancies in our Database... if you looka t the single charge potions... some of them show stats... not the spell :/

[Verdandi]

I was working on that right before the database rollback, which means it's all back to a jumble again.

[Ngreth Thergn]

I have thought about a few of these a bit more.

* Smithing items with only storebought items.
* Possible combines with items from traders in a specific zone.
* All smithing combines that includes tempers made from items foraged in specific zones.

All of these bring on the question... "How many itterations"

If I only go One layer deep... It will often not get what people want.
If I go MORE than one layer deep, I will often get such a HUGE list it could bring the server to its knees on one search (or at least hit it really hard and cause slowdowns if this happens too often)
Sure the last one defines it fairly well, but STILL could potentially come up short on getting what you want if it only goes one layer deep.

We have to remeber,
#1 this is not a dedicated server
#2 this is not dedicated to ONE user like you could do in a specialized aplication
#3 I am an amature
#4 there are server based web timeouts that a dedicated system could get around.

so realistically... though I could do some of these... they would lead to other issues that could be just as bad as not having them and having to do a little more research yourself.

I already had problems with infinite loops doing iterative searches... so they scare me Plus I am sure I am using alot of memory already keeping the iterative recipee list you see on items pages from going into infinite loops