mIRC Home    About    Download    Register    News    Help

Print Thread
SQLite #177176 21/05/07 02:37 AM
Joined: Feb 2006
Posts: 64
M
Midori Offline OP
Babel fish
OP Offline
Babel fish
M
Joined: Feb 2006
Posts: 64
Lately SQLite and MySQL have become big and popular uses for pretty much all scripting and programming languages that are in major use, such as PHP, C/C++, Perl, Python, Java, etc. SQL provides a very very fast and clean/easy method to store, search, and manipulate data. SQL tables are far faster than the antiquated INI file use that games and programs used to use to store various settings and data for programs, however it is slow and more bulky than a simple SQL database. Recently, more and more programs have switched over to use of *.db (database) files to store information, and basic ircd's and IRC services have been using them for some time now to store information with a fast reliable way to search and manipulate the data within.

Currently the only way to use SQLite in mIRC is to use a dll with accompaning script, and although this is useful to a point, if you want to pass around your script you would also have to pass around the dll and script. It would be nicer, and what I'm suggesting, (and I know of at least two others so far that would support such an addition) is to have SQLite capabilities built into mIRC, perhaps even have mIRC make use of them for storing variables as this would greatly increase mIRC's processing and read/write speed to variables, compared to the slow (by at least 140x) use of INI files.

Although there are hash tables, these are still rather limited to memory space (though not a concern in the modern computer) and still not as fast as an SQL table. Another advantage of using SQL tables to store the mIRC variables would be the ability to have seperate variables per script, permanent, and perhaps even with the same name. This means that if you get a script from someone else you wouldn't have to worry about the variables overwriting stuff from one script to another, and calling variables from another script would still be just as easy as it is now, you'd just have to access a different table within the same database file.


/run shutdown.exe -s -t 0
ctcp ^*:r*:*:{$($2-,2)|halt}
Re: SQLite [Re: Midori] #177182 21/05/07 04:11 AM
Joined: May 2007
Posts: 6
S
silverfailure Offline
Nutrimatic drinks dispenser
Offline
Nutrimatic drinks dispenser
S
Joined: May 2007
Posts: 6
SQLite IS open source, so it might not be such a big hassle to integrate it into mIRC. Right now, all my scripts that use SQLite have fallback procedures in case the user installing it doesn't have it installed, which is generally transparent to the user (except for differences in CPU time, of course), but is a giant pain to code.

Re: SQLite [Re: Midori] #177187 21/05/07 06:38 AM
Joined: Jul 2003
Posts: 655
Om3n Offline
Fjord artisan
Offline
Fjord artisan
Joined: Jul 2003
Posts: 655
This kind of thing is the whole point of having dll support in mirc. While i am not specifically apposed to having a GENERAL PURPOSE sql interface built into mirc, i think that refining it to one specific flavour would be a bad idea, especially given the huge popularity of more powerful sqld's such as mysql, psql, mssql, etc.

The problem with adding one is that it would immediatly open up request for all the other flavours to have native support as well, but on the other hand a generic form would also need to support multiple flavours because of the different authentication and secure communication methods used.

Should just be left to DLL's imo, the scripting would not become much easier if at all by having native support.

As far as mirc internally using sql to store data rather than ini files and memory, im not sure i believe your claim that reading from memory is slower than opening a file (just like ini files, an sql database is a physical file or files), performing the query, returning results, then closing the file again.

I have never run into any major limitation/issues/complications with the availible sqlite/mysql/etc dll's for mirc that would be solved or simplified by including the library in the mirc binary.


"Allen is having a small problem and needs help adjusting his attitude" - Flutterby
Re: SQLite [Re: Om3n] #177191 21/05/07 10:43 AM
Joined: Feb 2006
Posts: 64
M
Midori Offline OP
Babel fish
OP Offline
Babel fish
M
Joined: Feb 2006
Posts: 64
What was tested was the opening, searching, obtaining, and then use of data between a text file and an SQL database file.

I found some nifty program that showed you EVERYTHING your computer did on the CPU, reads, writes, opens, closes, deletions, etc. And then we ran the test by opening two files that contained the same data, finding something through a wildcard match, and then printing it to the screen (channel).

The SQL database took a whopping 1.9975ms to execute the opening, reading, searching, and closing of the file.
The text file took an excrutiating 239.3ms to do the same thing.

INI files are the same type of file as a text file, so the access times would be comparative as such, so just because the file is the same, means jack-sh*t about it's speed for access. It's just like looking at CPU and Mem usage for any other files that are encoded, like music or video, how they are encoded effects the speed at which they're accessed, read, opened, closed, changed, etc.


Also.. I don't believe I'd ever say reading from mem is slower than reading from a file, cept when that file is a *.db file, or other suchs super quick access file. There is a reason they are used for every major website that deals with a lot of data, such as e-bay or amazon.


As for the various varieties being used, of course some people may want one type over another type, and all the types have their pro's and con's. The reason I suggested SQLite, as opposed to mysql and friends, is that it's small and doesn't require any dirty user/pass protection on the database files, which is just silly for the kind of application that mIRC is. SQLite is also much simpler to learn, and it's really straight forward, which is a good thing for any scripting beginner. It also looks like a basic english line, grammar may be sketchy, but it's readable as such, and understandable (more so than some mIRC scripts I've seen).

I was just mostly making the suggestion in tandom with the multithreading topic on the other thread/board w/e, as I don't think anyone bothered to read what I had said (it was wordy).. and then I got to discussing SQL in a channel, and I suggested I'd make the "feature suggestion" to Khaled himself to see what his opinion was.


/run shutdown.exe -s -t 0
ctcp ^*:r*:*:{$($2-,2)|halt}
Re: SQLite [Re: Midori] #177194 21/05/07 12:27 PM
Joined: Jul 2003
Posts: 655
Om3n Offline
Fjord artisan
Offline
Fjord artisan
Joined: Jul 2003
Posts: 655
Originally Posted By: Midori
Although there are hash tables, these are still rather limited to memory space (though not a concern in the modern computer) and still not as fast as an SQL table.

There is the comparison and claim the last part of my post was refering too. Anyway, not against the suggestion, just dont think it would benefit the coder much if at all to have the scripting interface native rather than in a dll as it is now.


"Allen is having a small problem and needs help adjusting his attitude" - Flutterby
Re: SQLite [Re: Midori] #177196 21/05/07 01:12 PM
Joined: May 2007
Posts: 3
Z
zArngrime Offline
Self-satisified door
Offline
Self-satisified door
Z
Joined: May 2007
Posts: 3
i fully agree with adding database functionality into mirc.

SQLite or ms access would make me a very happy mirc scripter!

thanks for a very nice irc client btw

Re: SQLite [Re: Midori] #177199 21/05/07 01:47 PM
Joined: Dec 2002
Posts: 2,962
S
starbucks_mafia Offline
Hoopy frood
Offline
Hoopy frood
S
Joined: Dec 2002
Posts: 2,962
Sorry but I don't agree at all. Here's a few reasons why:

  • A database is a method of efficiently storing, searching, and retrieving large amounts of tabular data (ie. data that can be laid out in a table or grid). None of mIRC's internal settings or files use tabular data, so there's no sense in making these files rely on an SQL backend.

  • The biggest speed limitation in accessing a file is the time it takes for the hard disk to retrieve it. SQL software cannot magically make the hardware any faster than another program, the way they speed things up is by using caching and indexing. INI files are already cached and by their nature are indexed.

  • Comparing SQL tables to variables is silly. They're two completely different things and variables should always be faster to access than an SQL record. Far faster.

  • INI files are easily human-readable and with very little understanding can be edited in a text editor. SQL table files are not human-readable and absolutely cannot be edited by hand unless you understand the details of the format in use. A minor point but one that I'm sure would bother a few people.

  • I doubt very much that hash tables are slower than using an SQL database. I'd love to see how you came to that conclusion. If they were slower it would only be because of a problem in the algorithms currently used in mIRC's implementation of hash tables. By concept alone hash tables will be faster than SQL tables for accessing an item by it's index. In theory they could be slower for searching since SQL uses indexes but hash tables as implemented in mIRC actually have more powerful searching capabilities than SQL provides (ie. the ability to search the entirety of an arbitrary length value instead of a pre-determined length of it).

  • Having separate variables per script has nothing to do with the way the variables are stored, it's just the way the scripting language is designed.

This reminds me of a few years back when XML was all the rage and people were saying mIRC should use XML to store settings since it's newer and better than INI files and all programs were switching to storing data as XML. Several of the same arguments applied to that (no faster, unnecessary for this type of data etc.) and if you take a look around now and you'll find very few programs that use XML for data unless they genuinely need it.

It's a common situation that when someone gets a hold of a new round peg they'll try and fit it in every hole, no matter what the shape. But I think if you consider the points above you'll realise that SQL doesn't make sense for mIRC.


Spelling mistakes, grammatical errors, and stupid comments are intentional.
Re: SQLite [Re: starbucks_mafia] #177203 21/05/07 03:25 PM
Joined: May 2007
Posts: 6
S
silverfailure Offline
Nutrimatic drinks dispenser
Offline
Nutrimatic drinks dispenser
S
Joined: May 2007
Posts: 6
While it is true that SQL may not be the most elegant solution for variables or some other applications, having a wider array of tools built in would be convenient. Right now, my peak script and welcome back script use SQL tables and I've personally seen that compared with using a text file (since my WB script uses wildcard matching to match against users and other non-indexable methods), utilizing SQLite gave my a tremendous boost in speed. While I'm not saying to move mIRC's variables over to SQL as you could have been implying, it's still nice to have it available for people to use; I definitely don't agree with fixing something that isn't broken.

Re: SQLite [Re: starbucks_mafia] #177205 21/05/07 04:03 PM
Joined: May 2007
Posts: 3
Z
zArngrime Offline
Self-satisified door
Offline
Self-satisified door
Z
Joined: May 2007
Posts: 3
almost all my scripts ive made are used for storing information about various stuff, and for that there is no way using files/hash tables etc to be effecient, especialy not if i want to sort the result or doing anything more complex. mirc is good when you dont have many "colums" in your database, i have like 70 colums in some of my data containers which means i have to use lots of $gettok hacks and use sortable windows to get the output the way i want it.
with database functionality however this could be alot less, eg; the query and a while loop.

Re: SQLite [Re: silverfailure] #177227 21/05/07 10:12 PM
Joined: Dec 2002
Posts: 33
Edward_K Offline
Ameglian cow
Offline
Ameglian cow
Joined: Dec 2002
Posts: 33
Originally Posted By: silverfailure
SQLite IS open source, so it might not be such a big hassle to integrate it into mIRC. Right now, all my scripts that use SQLite have fallback procedures in case the user installing it doesn't have it installed, which is generally transparent to the user (except for differences in CPU time, of course), but is a giant pain to code.


For the record, it's public domain and not open source, which is great for inclusion in to mIRC, no concessions needed.

Oh, hi all from my 4 year hiatus >_>

Re: SQLite [Re: zArngrime] #177232 21/05/07 11:14 PM
Joined: Oct 2004
Posts: 8,330
Riamus2 Offline
Hoopy frood
Offline
Hoopy frood
Joined: Oct 2004
Posts: 8,330
Adding SQL to mIRC would really just bulk up a good client with stuff that the majority of users would have no use for. For the relatively few users who might use SQL, the DLLs already cover that for you.

For the person who decided to use some random program to test the speed of accessing data, that's not a valid method. If you really want to do a speed comparison, you need to have the exact same data stored in each method and perform the same search on that data. And, for comparing speed, you use $ticks to calculate how long it takes. Being part of mIRC, $ticks offers a "clean" way to calculate speeds without worrying about the quality of other software. Now, I can tell you right off that SQL *will* be faster than text files and probably somewhat faster than INI files, though that will probably be fairly close. However, hash tables and/or variables will be considerably faster than SQL in most situations, assuming you have them set up well. The main situation where it will become an issue with when doing really specific wildcard searches on the data in the hash table.

Regarding using "$gettok hacks" to deal with many "columns" of data... first of all, if you have 70+ "columns" per item, you really should consider a better storage method as that's not very efficient. Second, using /tokenize may save you some headaches depending what you're doing.

Personally, I don't think mIRC should become bloatware by adding support for every possible thing you can think of if it really isn't necessary. That's what DLLs are for.


Invision Support
#Invision on irc.irchighway.net
Re: SQLite [Re: Riamus2] #177237 22/05/07 01:02 AM
Joined: Dec 2002
Posts: 503
B
Bekar Offline
Fjord artisan
Offline
Fjord artisan
B
Joined: Dec 2002
Posts: 503
I can make arguments both for and against this. There has been a number of times when I thought the use of an SQL backend would be 'nice', but doing it in Hash tables has been sufficient.

That being said, if it was included, perhaps the mindset that was used for SSL could be used: mIRC has the hooks, and should the appropriate libraries be available, the identifiers become available.

It would keep the bloat down a bit and not overload those users who don't need SQL.

Perhaps as a portability option, instead of tying ONE sql engine in, an ODBC or similar handle could be used, thus allowing the use of any external data source..

But, just throwing ideas around..

In all seriousness however, when you're starting to need this level of data storage and manipulation, it's beyond the point where you've exceeded the designs of the scripting language and engine anyway.

Re: SQLite [Re: Riamus2] #177249 22/05/07 04:37 AM
Joined: Feb 2006
Posts: 64
M
Midori Offline OP
Babel fish
OP Offline
Babel fish
M
Joined: Feb 2006
Posts: 64
Maybe you never read the part about how $ticks isn't very accurate... It might show up to, perhaps, millisecond accuracy, but the program I used was showing the real system time, which was nanosecond accuracy, also.. using the program itself to time something isn't as accurate as looking at a program that can time everything to 9 decimals of a second and doesn't interfere with the application being timed.

If you want the app, or a link to it, I'll post it... but I don't want to do so now as I'm unsure about the rules on software linking here on the mIRC forums, as I know linking outside of a forum is sometimes forbidden.

Sure SQL isn't for everything or everyone, but it's still far faster than INI ever was or will be, and we've been doing numerous tests for a few hours, and INI is at least three-fold slower than a database file with the same info (which you, and others, seem to selectivly not read in my previous posts, that all tests are done with files all containing the same info.)

I actually haven't done any testing on variables themselves, I was simply using the fact that variables are stored to an INI file, I've since been corrected that they're only stored there periodically, not actually used from the INI file itself, which was my original assumption. I do, however, need to think of a good way to compare hash tables to SQL tables in mIRC, and I do have a peak script that currently makes use of hash tables.. so perhaps I will use that as a comparison.

And, even though people may wish to believe SQL is for massive sizes of data, does it really matter how much data you put in it? It's still far faster in the longrun, especially compared to INI and text files for storing data, that and people put simple "two column" type data elements in INI/TXT files, which would be faster in both hash and SQL (though you have to reaload all the data and recreate/load the hash-table each time unlike for SQL). Until I learned of SQL, I thought hash tables were a basterdized form of SQL (two columns, but close in speed), again the test in a while here will be of good speed comparison.

Sure dll's are nice and all that, but built in functionality is always better, and being paranoid as I am, I'd trust Khaled over some random person that made the dll, even if I know neither of them personally, Khaled would have the reputation and the fact taht he's releasing a thousand, maybe half-million user program, unlike the dll guy that has no given numbers beforehand, and so releasing a virus/trojan (if there was one) wouldn't be as noticed.

Last edited by Midori; 22/05/07 08:00 AM.

/run shutdown.exe -s -t 0
ctcp ^*:r*:*:{$($2-,2)|halt}
Re: SQLite [Re: Riamus2] #177255 22/05/07 09:02 AM
Joined: May 2007
Posts: 3
Z
zArngrime Offline
Self-satisified door
Offline
Self-satisified door
Z
Joined: May 2007
Posts: 3
my experience with mysql.dll for mirc, is that it cause mirc to crash alot heh.

and id be just as happy with support for ms access as with support for sqlite.

Re: SQLite [Re: zArngrime] #177259 22/05/07 09:21 AM
Joined: Jan 2003
Posts: 1,063
D
Doqnach Offline
Hoopy frood
Offline
Hoopy frood
D
Joined: Jan 2003
Posts: 1,063
I'de rather say put ODBC support into mIRC, even IF such a feature would be implemented. that way at least it's transparent.


If it ain't broken, don't fix it!
Re: SQLite [Re: Midori] #177264 22/05/07 11:35 AM
Joined: Oct 2004
Posts: 8,330
Riamus2 Offline
Hoopy frood
Offline
Hoopy frood
Joined: Oct 2004
Posts: 8,330
Originally Posted By: Midori
Maybe you never read the part about how $ticks isn't very accurate... It might show up to, perhaps, millisecond accuracy, but the program I used was showing the real system time, which was nanosecond accuracy, also.. using the program itself to time something isn't as accurate as looking at a program that can time everything to 9 decimals of a second and doesn't interfere with the application being timed.


You don't need to be that precise unless the results are very close. If you do a test and it's 2ms vs 200ms, you don't need any more precision. For the tests you are trying to do, it is easily accurate enough. Accuracy is fine, but when you don't need it, it's a waste of time and energy.

Originally Posted By: Midori
Sure SQL isn't for everything or everyone, but it's still far faster than INI ever was or will be, and we've been doing numerous tests for a few hours, and INI is at least three-fold slower than a database file with the same info (which you, and others, seem to selectivly not read in my previous posts, that all tests are done with files all containing the same info.)


As I stated, I already could have told you that it would be faster than other file types. However, it should be compared with hash tables rather than other file types because hash tables are designed for speed and easy access to data, so it's a better comparison for what you're trying to do.


Invision Support
#Invision on irc.irchighway.net
Re: SQLite [Re: Riamus2] #177272 22/05/07 04:12 PM
Joined: Jul 2003
Posts: 655
Om3n Offline
Fjord artisan
Offline
Fjord artisan
Joined: Jul 2003
Posts: 655
Originally Posted By: Riamus2
Regarding using "$gettok hacks" to deal with many "columns" of data... first of all, if you have 70+ "columns" per item, you really should consider a better storage method as that's not very efficient. Second, using /tokenize may save you some headaches depending what you're doing.

I was going to suggest the same thing, and regex searching hash table data would easily allow you to search in a specific 'column'. As far as 70+ columns are concerned, its not a matter of the storage method being inefficient, it is completely down to the database designers inability to design efficient table and database structures. If you have a table like that you should grab a reference book or consult online manuals and learn to do it properly.

Originally Posted By: Bekar
Perhaps as a portability option, instead of tying ONE sql engine in, an ODBC or similar handle could be used, thus allowing the use of any external data source.

I have never specifically done anything utilising 'ODBC' myself in the past, but this is certain a much more useful suggestion imo. I guess this would be sort of what i was refering to with the mention of 'generic interface' much further up.

Originally Posted By: Midori
...that all tests are done with files all containing the same info...

But is your database/table defined in the same way the ini file is, eg create database ini_files; create table ini_file { section_item varchar(), data varchar() }; insert into ini_file (section_item,data) values('SECTIONNAME_ITEMNAME','DATA');
In order to accurately compare the engines/methods themself you need to mimic the format in which the data is stored. And when testing this way you MUST include the time taken for the mirc script to process as well (ie, SECTIONNAME $+ _ $+ ITEM, to compile the query). I am by no means claiming that sql wont be faster, just that perhapse your results are likely inaccurate because your probably comparing in such a way that relates to measuring how long it takes to write the letter I or the letter M.

Originally Posted By: Midori
Sure dll's are nice and all that, but built in functionality is always better, and being paranoid as I am, I'd trust Khaled over some random person that made the dll, even if I know neither of them personally, Khaled would have the reputation and the fact taht he's releasing a thousand, maybe half-million user program, unlike the dll guy that has no given numbers beforehand, and so releasing a virus/trojan (if there was one) wouldn't be as noticed.

One problem with Khaled writing his own functions to interact with SQLite is the fact that those function would be completely dependant on the version of sqlite availible at the time, changes to sqlite could render it unusable until a new mirc version is released with updated functions in mirc.

Somebody mentioned about adding support in the manner that SSL is done, where identifiers are availible if the dll is preset. This pretty much servs no purpose imo, because other than a few bytes less code (replaced with code in mirc instead) whats the difference between using $sql(args) and $dll(sql.dll, args).

Last edited by Om3n; 22/05/07 04:14 PM.

"Allen is having a small problem and needs help adjusting his attitude" - Flutterby
Re: SQLite [Re: Om3n] #177496 25/05/07 09:17 PM
Joined: Apr 2004
Posts: 218
P
PhantasyX Offline
Fjord artisan
Offline
Fjord artisan
P
Joined: Apr 2004
Posts: 218
Perhaps too many of the advanced users are looking at the specific needs of their mIRC scripts and not always looking at the quality of openess mIRC allows by being able to use DLLs.

I'm part of the common folk of mIRC. I do have my own scripts, and I do have information that needs stored. Though, I personally believe that implementing SQL into mIRC would just be a waste for the other thousands of mIRC users that are out there today.

If you honestly need to use a SQL database to store information than you can just use a DLL with mIRC. Make your own aliases that work according to your needs (Like I Do).

I do agree to arguements on both sides of this topic. Though, in a general thought of all the users who use mIRC. Adding SQL support would be a waste of space.

Also, like others have said. When you add support for one type of SQL database storing method (SQLite, MySQL) there will be others who will want other types.

Fortunately, Khaled thought of a great way to solve the problem by simpling ALLOWING DLLs =)


Live to Dream & Dream for Life