Not sure if this is the right place to post this but I'm looking for programming help

Not sure if this is the right place to post this but I'm looking for programming help.

Does anyone know of a simple to use, bare minimum IRC-bot? What's the recommend programming language for this task?

The only thing I'm looking to add to it is a search module.

For example I'm looking to do the following:

-Users can type for example "!search type=cyberware name=cyberears" into the channel and it will return information on "cyberears" using the category "cyberware"
-A general "!search" function for search a huge catalog via "name"
-Would work off a database (not sure what type of database is recommended)

It would be greatly appreciated if anyone could help as I've tried countless times to research this but wasn't able to find any templates specific for this task.

P.S. In case anyone is interested this is for a Shadowrun 5e database.

Other urls found in this thread:

github.com/sopel-irc/sopel/wiki/Sopel-tutorial,-Part-1
twitter.com/NSFWRedditGif

since you dont seem to know any programming language you probably wont get very far. i dont think you really want to write one yourself. maybe use a open source one instead? google says sopel is gud.
github.com/sopel-irc/sopel/wiki/Sopel-tutorial,-Part-1

I have average knowledge of languages like c++, C#, VB (lol), and I don't find it hard learning a new language if I have a template to work off of.

I've downloaded Sopel but I really need help wrapping my head around designing a database search module. I just don't know how to take the text the Users enters and parse it for use searching a database and returning information.

well sopel is using python. you can use python to access any SQL database, theres plenty tutorials out there. everything in-between is covered in the tutorial i linked.

>(not sure what type of database is recommended)
MySQL probably would do fine if you wouldn't create crazy queries.

Just do something like this

A table with categories (ID, Name)
A table with results (ID, ID_Category, Result data)

SELECT `Result_data` FROM `results` LEFT JOIN `categories` ON `results`.`ID_Category` = `categories`.`ID` WHERE `categories`.`Name` LIKE %(escaped category name, normalized)% AND `results`.`Result_data` LIKE %(escaped, normalized query)% LIMIT 50

Though you'd be better off with anoter table for tags + tag-result marks, but you get the idea.
If you need any more help ask, I'll lurk a bit

Thanks for the help. Are you able to link me to a tutorial regarding the module I'm trying to implement and how to create it?

Cool, I have a basic understanding of SQL. Would you mind giving me a run down of what that command does? I understand some of it but I'm a little rusty.

It pulls max of 50 results from the result table by looking at the category id (you really don't want to work with strings too much) + possible matches in the result table
so let's say you'd ask for category "foo" with query "bar"

The query would first LEFT JOIN the category table on ID and look for the category (basically match ONLY results that have that category ID, it's a simple filter), then it'd search the actual data for the string.

So if you had the following tables:
category:
ID 1
Name foo

And results:
ID 1
ID_Category 1
Result_data bar

And ask for category foo with query bar

It'd pull Result_data (in this case it's simply bar)
From every result
Where
Category name is similar (has string in) to "foo"
So only category 1
(LEFT JOIN ---- ON tells the server that ID_Category IS ID from the category table, so you don't need to explicitly include that instruction)
And where Result_data is similar to "bar"
So result with ID 1
So it'd return "bar"

If you're wondering how exactly useful would that be, you'd need to build a simple tag system, which I can explain if you wish.

whoops, I forgot one thing
In the where clausule it also needs
`results`.`ID_Category` = `categories`.`ID`
so it actually selects the category you want

It's late and I'm a bit tired, but you get the point

Sweet, I have plenty of time if you're willing to explain the use of a tag system.

btw do you know anything about using an IRC BOT and implementing a search function like what I mentioned above?

Not really an IRC guy, sorry.

Well basically you'd want somekind of interface to automatically add results into the system.

In that case, you'd need really ANY of the following:
A programming language that can interpret and process SQL queries (basically any SQL connection library + array support)
Somekind of interface to supply it with actions (your !search function would call one of those, another is "add result", "remove result" etc.)
Normalization methods (best if written on the programming language side of things)
Escape methods (you don't want people to do nasty shit to your database)

For example, all I've mentioned is pretty much built-into php.

The whole search system would work as following:

4 tables in db

Result:
ID (My ID)
Category_ID (ID of my category)
Data (Your info, can of course be more than 1 column)

Category:
ID (My ID)
Name (Category Name)

Tag:
ID (My ID)
Tag_String (My assigned string)

Tag_Assignments:
ID (My ID)
Tag_ID (Tag's ID)
Result_ID (Result's ID)

To quickly go over the two unexplained methods:

Add result:

1. Select / Make category
2. Get Category ID
3. Type in the result data, let's refer here to it as "Result Data"

The Result Data has to be first escaped (removing SQL stuff from the string) and then normalized (to lower characters, no special characters [unless you want them]) and then split into words

So if you'd input the Result Data as "A wild wolf"
The finished data from this would be an array, 3 strings "a", "wild" and "wolf"

After that is done, you insert your new Result and get it's ID

Let's say you get ID 42 on insert, after that you save it to a variable and then set up tags.
First off you have to check every single tag and see if it was created already.

So you do a call like
SELECT `ID` FROM `Tag` WHERE `Tag_String` = '---your word---'
If it returns a number - you add the tag assignment, so you call INSERT to `Tag_Assignments` with your Result ID (42) and Tag's ID. 1/2

Ah cool, I'm not looking to edit the database from with IRC. I just want them to be able to send search queries. Removing SQL stuff from the string would be essential though.

If it doesn't return a number, you create a new tag, get it's ID and do the assignment as pictured above.

You want to repeat this process for every single word for that Result Data array you got.

Once that is complete, you can begin your searches.

Searches would work as following:

1. Get Category name and query (from IRC bot string)
2. See if category exists (is valid)
SELECT `ID` FROM `Category` WHERE `Name` = '---your category name from query---'
If the query doesn't return a number - the category doesn't exist
Else:
3. Scan tags:
SELECT `ID` WHERE `Tag_String` LIKE '---your query, segmented into words as previously---'
Repeat for each word OR chain together with LIKE %word1% OR LIKE %word2% etc.
4. Once you finish, check the results - you can have more than 1 ID from those queries
If empty - nothing matches your search
Else:
5. Get all result assignments for tags:
SELECT `Result_ID` WHERE `Tag_ID` IN (--your tag ID's from previous query--)
5. Get your actual results:
SELECT `Data` FROM `Result` WHERE `Category_ID` = '--your previously selected category ID---' AND `ID` IN (---your result ID from previous query---) LIMIT 50 //or whatever

You've successfully selected all the data you wanted. Congrats.

well you could just create a simple interface/website for removing/adding new results. It'd be rather simple, really.

I could literally code all this in PHP in the time it took me to write this explanation out for you

What's the tag system basically for? Couldn't you just work the search with the categories table?

What's the results table for? Somewhere to store the search query?

Results table is for holding the data you'll display when someone "gets" the data he/she wants to find.

So if you say !search category=fruit query=apple

It'll respond with
"Red apples are healthy"
"Green apples are not so healthy"

Where both strings will be separate entries.

The tag system exists to allow you to search for a few points more efficiently.
Otherwise the database will not recognize something like this:

Take for example a Result with this text:
"Red apples are not that good"

If you query with "apple"
And you'd skip the tag system, you'd still get the result you'd expect

However, if you queried with "blue apples gravy meat"
You wouldn't be able to get the result, as the query you'd run would be

SELECT `Data` FROM `Result` WHERE `Data` LIKE '%blue apples gravy meat%'

Where as with tags, all those words would create an essentially separate query (categorized and cleaned up ofc)

So you'd essentially get something like
SELECT `Data` FROM `Result` WHERE `Data` LIKE '%blue%' OR `Data` LIKE '%apples%' OR `Data` LIKE '%gravy$' OR `Data` LIKE '%meat%'

What's the benefit of using a Tag system over, say, doing it all on the programming side before you start the SQL query?

Lots of LIKE will be slow. If you work with a small number of Results it'd be fine for the most part, however when working with bigger numbers you'd notice significant slowdowns (SQL is bad when it comes to strings)
You could glue the statement together with OR '%like%' tags and such when it comes to the Tag ID search (, step 3)
As to speed it up (only run 1 query to see if tags even exist)

Bigger web apps use this kind of system, like Prestashop for example (I worked with it for a while, hooking into the database directly)

So generally you want to keep a healthy mix. Do 1, 2 (max) SQL queries with strings.
Work wth IDs for the rest. Those are much more efficient.

Generally the way I descibed it, you only need to use strings twice.
Once to get the category and then in step 3, to get the tag IDs
Everything after that is working ONLY with numbers, which databases are much better with.

I just counted it and the whole complete search is 4 separate queries.
In the end, you could just simply connect them together and create one single big query with sub-queries, which could further speed things up.
But then, optimization is boring.

Awesome, thanks for the help. I only wish I could archive this so I could reference it later. I think there is a website for that if IIRC.

No idea. You can just save the webpage if you want to preverve the styles and whatnot, else I'd just suggest copying it to a text file.

well either way I'm going to sleep, it's late.

Ok, once again, thanks for your help.