Please Disable Ad-Block To View This Website.

If you block ads, this site can not survive!

Ads are very minimal for registered users. If you don't have an account please register now!

DOWNLOAD
 Full Scripts
 Addons
 Snippets
 DLLs
 MTS Themes
 Tutorials
 Misc.
 File Queue
 Download mIRC
INTERACT
 Screenshots
 Challenge
 Top Downloads
 Submit Form
 Forums

SEARCH
Site Search

FRIENDS
Link to us!
PhotoShelf

Home | Comments:
Average Rating:   9.6   mIRC SQLite v1.3.0 by unclerami
Description:
mIRC SQLite is an implementation of the SQLite library for mIRC. It requires mIRC version 6.2 or higher. It offers a flexible and easy-to-use API (Application Programming Interface) which is strongly inspired by the PHP's SQLite API. With mSQLite you can easily and quickly embed SQL to your mIRC scripts. It doesn't require a seperate process for SQL server nor complex configuration. The library IS the server. You just need to load the script and start coding. :)

Submitted Review Author's Updates
Review:
Documentation
-------------------
This script comes with a .chm file explaining everything you'll possibly need to use the addon. It has in-depth explanations of all of the features included with this library, contact information, how to install the script, examples, howtos and an faq.

Script examples included in the documentation use a nice looking coding font, are contained in a bbcode-type code block and are syntax highlighted.

This is how documentation should be.


Functionality
-------------------
mIRC SQLite is a script/dll combo that gives access to the SQLite API in mIRC. It's a full-featured SQL database engine that has support for things such as hashtables, binary variables, custom functions and much more.

All of the example scripts worked perfectly and show practical uses for this library.

SQLite supports most of the SQL language, but it does have a couple of extra and missing features. You can read about those here: http://www.sqlite.org/lang.html

Overall
-------------------
This is a brilliant addon that can be used to replace mIRC's built-in data storage features. Databases are much more efficient for storing large amounts of data.

Great job ramirez.

Reviewed by hixxy

1.3.0 - 18th August 2009
----------------------------
- Starting from this version mIRC SQLite will open databases and files from the directory specified by $mircdir by default (earlier it used the directory where $mircexe was in). Due to this change in mIRC installations where $mircexe is not in $mircdir, databases needs to be moved to $mircdir if the scripts open databases by using relative paths. The reason for this change was that the DLL wasn't consistent with mIRC's built-in aliases and identifiers, because they used different directory than mIRC SQLite.
- Updated SQLite to version 3.6.17. Upgrading is recommended.
- Updated PCRE to version 7.9.
- Added /sqlite_bind_null.
- Added $sqlite_fetch_num.
- Added $sqlite_fetch_assoc.
- Added $sqlite_fetch_all.
- Added $sqlite_safe_encode.
- Added $sqlite_safe_decode.
- Fixed $sqlite_fetch_bound fetching one byte too little when fetching non-binary data from an unbuffered query result into a binary variable.
- Fixed $sqlite_fetch_bound not returning $null when there's an error.
- Fixed $sqlite_fetch_bound not returning 0 when there's no more rows to fetch.
- Fixed $sqlite_bind_field not returning 1 on success.
- Fixed problems with passing binary data if there was a space in the temporary path used by Windows.
- Fixed "* /bread: invalid parameters" if trying to fetch binary data that is empty.
- Changed $sqlite_fetch_single and $sqlite_fetch_field return maximum string length from 900 bytes to 4096 bytes for mIRC versions 6.32+.

1.2.1 - 1st March 2009
-------------------------
- Updated SQLite to version 3.6.11. Upgrading is recommended.
- Fixed $sqlite_prepare failing to prepare any statements. This critical bug was introduced in 1.2.0 (thanks to Sprak for reporting it).
- Changed SQLite to be compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT flag, which allows user to use ORDER BY and LIMIT clause on UPDATE and DELETE statements. This was already changed in 1.2.0, but there was no note for it in the change log.

1.2.0 - 22nd December 2008
--------------------------------
- Due to the few major additions, changes and bug fixes in this version, this version will be called 1.2.0. Upgrading is strongly recommended.
- Added $sqlite_exec_file for executing a file as SQL.
- Added a .file property to $sqlite_exec for executing a file as SQL.
- Added a .file property to $sqlite_query for executing a file as SQL.
- Added a .file property to $sqlite_unbuffered_query for executing a file as SQL.
- Added a .file property to $sqlite_prepare for executing a file as SQL.
- Added a feature to bind parameters for non-prepared statements using $sqlite_exec, $sqlite_query and $sqlite_unbuffered_query. This will make writing cleaner queries much easier. See Prepared Statements page for example.
- Updated SQLite to version 3.6.7. This version adds new functionality and fixes a lot of important bugs from the previous SQLite version the library used, upgrading is strongly recommended!
- Updated PCRE to version 7.8.
- Fixed $sqlite_open when opening a database from an existing file. It now creates triggers, views, indices and sequence values correctly.
- Fixed a bug with parameter binding for /sqlite_exec and $sqlite_unbuffered_query.
- Fixed a critical bug with the REGEXP operator implementation (thanks to Sir-Loopy for reporting it).
- Fixed a bug with the autocommit feature.
- Changed $sqlite_write_to_file. It now uses a custom written function which is more portable than the previous solution.

1.1.2 - 22nd April 2008
--------------------------
- Updated SQLite to version 3.5.8.
- Fixed a serious bug that caused /sqlite_free to stop working correctly. This bug was introduced in 1.1.0, upgrading is strongly recommended.

1.1.1 - 5th March 2008
--------------------------
- Added /sqlite_help.
- Added /sqlite_autocommit.
- Updated $sqlite_query, /sqlite_exec and $sqlite_unbuffered_query to support binding values to prepared statements.
- Updated SQLite to version 3.5.6.
- Updated PCRE to version 7.6.
- Fixed a serious bug that caused /sqlite_rollback to commit instead of rollback.

1.1.0 - 13th January 2008
-----------------------------
- Due to the major additions, changes and bug fixes in this version, this version will be called 1.1.0. Upgrading is strongly recommended.
- Added support for prepared queries and binding parameters. For information about various binding styles that are supported by mSQLite, see Prepared Statements.
- Added $sqlite_unbuffered_query.
- Added $sqlite_prepare.
- Added $sqlite_bind_field and its alias $sqlite_bind_column.
- Added $sqlite_bind_param.
- Added $sqlite_bind_value.
- Added $sqlite_clear_bindings.
- Added $sqlite_fetch_bound.
- Added $sqlite_current_bound.
- Added $sqlite_is_valid_statement.
- Added $sqlite_begin.
- Added $sqlite_commit.
- Added $sqlite_rollback.
- Added $sqlite_finalize.
- Updated $sqlite_exec and $sqlite_query to be able to execute prepared statements.
- Updated $sqlite_fetch_row to set $SQLITE_NOMOREROWS as an error code when there are no more rows.
- Updated $sqlite_open and $sqlite_open_memory to also copy triggers and views when copying database from another file.
- Updated SQLite to version 3.5.4. There are a lot of important bug fixes inbetween this version and 3.4.1. Upgrading is strongly recommended.
- Updated PCRE to verion 7.4.
- Updated mSQLite to enable the FTS3 module in core by default.
- Fixed a long standing bug in $sqlite_query not returning 0 when the query is a type of query that never returns rows (INSERT/UPDATE/DELETE).
- Fixed a bug where user defined functions that run nested queries removed temporary file created by TMPARG() prematurely.
- Removed FTS1 and FTS2 modules from the core to reduce size since they're obsolete. It's recommended to update your existing code to use FTS3 instead. You can still use the older versions by compiling and loading the modules by yourself.

1.0.15 - 30th July 2007
--------------------------
- Fixed a critical bug that caused mIRC to crash upon loading a database when FTS2 module was enabled. Upgrading is strongly recommended.

1.0.14 - 30th July 2007
--------------------------
- Updated SQLite to version 3.4.1. This release fixes a bug in VACUUM that can lead to database corruption. The bug was introduced in version 3.3.14. Upgrading is recommended for all users.
- Updated PCRE to version 7.2.
- Updated $sqlite_create_aggregate to have distinct properties for step and finalize alias. This allows users to create custom aggregates with only one mIRC alias by checking the $prop identifier's value. See User Defined Aggregates for details and example.
- Fixed a lot of old information on documentation and added some new information on how-to pages for new users.

1.0.13 - 18th June 2007
---------------------------
- Updated SQLite to version 3.4.0. Fixes two seperate bugs that can cause a database corruption and adds explicit limits on the sizes and quantities of things SQLite will handle.
- Updated configuration file to automatically set temporary storage to memory instead of file. Before this was done by modifying the SQLite library source code, which meant that the corresponding edit had to be done for every version which is easy to forget. This ensures that temporary storage will be memory for every version unless you change it yourself.
- Updated the mIRC script to use an alias for the DLL path so people who wish to change where the DLL resides can do so easily by editing the corresponding alias.

1.0.12 - 27th April 2007
---------------------------
- Updated SQLite to version 3.3.17. Performance improvements added in 3.3.14 but mistakenly turned off in 3.3.15 have been reinstated. A bug has been fixed that prevented VACUUM from running if a NULL value was in a UNIQUE column. This version fixes a bug in the forwards-compatibility logic of SQLite that was causing a database to become unreadable when it should have been read-only.
- Updated PCRE to version 7.1.

1.0.11 - 15th April 2007
---------------------------
- Updated SQLite to version 3.3.15. An annoying bug introduced in 3.3.14 has been fixed. There are also many enhancements to the test suite.

1.0.10 - 8th April 2007
-------------------------
- Fixed a bug in custom functions argument encoder.
- Updated SQLite to version 3.3.14. This version focuses on performance improvements, you can see performance improvements up to 35% or more compared to the previois version. This version also adds support for exclusive access mode.
- Updated mSQLite to enable the FTS2 (new version of the Full-text Search) module in the core by default.

1.0.9 - 15th February 2007
------------------------------
- Updated SQLite to version 3.3.13. This version fixes a subtle bug in the ORDER BY optimizer that can occur when using joins. There are also a few minor enhancements. Upgrading is recommended.

1.0.8 - 30th January 2007
-----------------------------
- Fixed $SQLITE_NOTMEMORYDB returning wrong value.
- Fixed $sqlite_fetch_field outputting not intended debug information on certain errors.
- Updated the library to automatically find unique identifier for mSQLite if user doesn't specify one. You're no longer prompted to input an unique id on script load, but you can still force a certain id to be used by specifying it in msqlite.ini.
- Updated SQLite to version 3.3.12. This update fixes several bugs that were introduced in 3.3.10, upgrading is recommended.

1.0.7 - 14th January 2007
-----------------------------
- Fixed some minor memory leaks in $sqlite_query and $sqlite_exec when queries failed.
- Updated SQLite to version 3.3.10. This update fixes several bugs that were introduced in 3.3.9, upgrading is recommended.

1.0.6 - 6th January 2007
----------------------------
- Updated SQLite to version 3.3.9. This update contains important changes and bug fixes, upgrading is recommended.
- Updated PCRE to version 7.0.

1.0.5 - 4th December 2006
------------------------------
- Added $sqlite_set_authorizer.
- Fixed script failed to automatically unload when tried to load on older mIRC and script filename had spaces.
- Fixed various typos and misinformation in documentation.

1.0.4 - 3rd November 2006
------------------------------
- Fixed $sqlite_open not supporting files or directories with spaces on them.
- Fixed mSQLite temporary tables storage using files instead of memory. This was misconfigured in version 1.0.3.
- Updated $sqlite_fetch_field and $sqlite_result to use case-insensitive matching when using field names.

1.0.3 - 11th October 2006
-----------------------------
- Added $sqlite_result.
- Updated SQLite to version 3.3.8, which adds support for full-text search. See this article for details.

1.0.2 - 5th September 2006
--------------------------------
- Added $sqlite_open_memory.
- Added $sqlite_write_to_file.
- Added $sqlite_fetch_field.
- Added $sqlite_is_valid_db.
- Added $sqlite_is_valid_result.
- Added $sqlite_is_memory_db.
- Added /sqlite_reload.
- Added possibility of opening transient databases by using empty file name for $sqlite_open.
- Added possibility of opening memory databases by using :memory: as file name for $sqlite_open.
- Added new configuration options for loading extensions and running pragmas by default. See Installing and Configuring mSQLite for details.
- Fixed a bug opening msqlite.ini from mIRC dir, instead of the script dir.
- Fixed a bug in the ini parser, where it did not correctly remove enclosing quotes around value.
- Fixed $sqlite_rewind not working.
- Fixed various typos and mistakes in documentation.
- Updated to store temporary tables on memory instead of temp files by default. This is much more efficient.
- Updated $sqlite_fetch_single to return size of binary variable, instead of 1, when binvar is specified.
- Updated $sqlite_load_extension to use native SQLite API instead of query for improved efficiency. You can still use load_extension in queries.

1.0.1 - 23rd August 2006
----------------------------
- Added $sqlite_field_metadata.
- Added $sqlite_load_extension.
- Fixed $sqlite_open not setting the busy timeout to the default value specified in msqlite.ini.
- Updated SQLite to version 3.6.7.
- Updated PCRE to version 6.7.
- Updated to allow queries to load dynamic extensions.

1.0.0 - 6th August 2006
---------------------------
- Initial release.
Screenshot:
No
Screenshot
Available

Comments:

  Mode:    Create New Post

JustariusMar 15, 2009 6:17PM
Hmm, maybe there is a more efficient way to do this. I want to loop through the rows, and add something from a single column (not always the first column) to a dialog list.

The way I'm doing it seems horribly inefficient. I just started using this dll + script a few days ago, and I started thinking "wow, this returns data incredibly slow." So I'm wondering if there's a better way to do what I want. The reason I thought this was slow, was because I had experience with using "SQLiteDLL" (another dll but for sqlite2, it can be found here on this website), and I wasn't sure if it was just my imagination, or if it really was slow in comparison to Klops sqlite.dll.

So I decided to export the db, and import it as a sqlite2 db, and then use Klops sqlite.dll to do the exact same procedure. The difference is rather huge. To the point where I think I won't use this DLL if I don't particularly need the special sqlite3 features.


Anyway, here's the results of my trials (note: the mSQLite.dll db had about 10 extra things added to it between the time I imported the data to sqlite2 and did the tests, but that amount doesn't really matter):


(mSQLite.dll) Updated list with 580 rows in 1.201 seconds.
(sqlite.dll) Updated list with 568 rows in 0.171 seconds.


That's a massive difference.

Here's the code I used:

; mSQLite.dll
; 
alias filldialog {
  var %db = $sqlite_open(mydb.sqlite) , %q = select * from flist , %ticks = $ticks
  if (!%db) { echo -a Error opening database. | return }

  var %push = $sqlite_query(%db, %q)

  var %totalRows = $sqlite_num_rows(%push)

  did -rh SpecialDialog 5
  while ($sqlite_fetch_row(%push, row, 3)) { 
    did -a SpecialDialog 5 $hget(row,fname)
  }
  sqlite_free %push

  sqlite_close %db
  did -v SpecialDialog 5

  var %t = $calc($ticks - %ticks)
  echo -s (mSQLite.dll) Updated list with %totalRows in $calc(%t / 1000) seconds.
}


; sqlite.dll
;
alias filldialog2 {
  var %db = $dll(sqlite.dll,open,db SpecialDB.db) , %ticks = $ticks
  if (S_OK* !iswm %db) { echo -a Fail! }

  var %q = select * from flist
  var %pushit = $dll(sqlite.dll,query,db %q)

  did -rh SpecialDialog 5
  if (S_OK* iswm %pushit) {
    var %i = 1 , %z = $gettok($dll(sqlite.dll,Rows,db),2,32)
    while (%i <= %z) {
      did -a SpecialDialog 5 $_fetch(%i)
      inc %i
    }
  }
  noop $dll(sqlite.dll,FreeQuery,db)
  noop $dll(sqlite.dll,close,db)

  did -v SpecialDialog 5

  var %t = $calc($ticks - %ticks)
  echo -s (sqlite.dll) Updated list with %z in $calc(%t / 1000) seconds.
}
alias -l _fetch { return $gettok($dll(sqlite.dll,Fetch,db $1 fname 1),2-,32) }

1 second difference between the two is pretty massive. I'm posting in the hopes of either being given a better way to handle my msqlite.dll routines, or maybe in hopes of having the .dll being worked on in terms of speed. I don't know if it's maybe because you're fetching the entire row, parsing it, and then making several hash table entries for that row or what.

Also, any chance of us getting a standalone DLL option that's closer to the way sqlite.dll works (that way we don't have to rely on a secondary script? (I don't really want to have to make someone install this script just to use a script I made.))

Thanks for your work on this, hope you continue to keep it up to date.

JustariusMar 15, 2009 9:29PM
Alright, I played around a little more with this. Decided to import the same data into a new sqlite3 database file, that way "results returned" would be the same as well. I also changed the my mSQLite code a little bit to see if I could get a speed increase (I did see a speed increase, though it still takes almost 5 times as long as Klops sqlite.dll). I also can hear my computer working a little harder with mSQLite.dll than it does with sqlite.dll.

I changed the $sqlite_open() to $sqlite_open_memory(), and I discovered $sqlite_fetch_field(%result, field).name which *mostly* does what I want. Other than those two changes, code remains mostly the same.

I tested each one five times (well far more than that, but I'll post the first five for each)

(mSQLite.dll) Updated list with 568 in [ 0.92 , 0.936 , 0.936 , 0.92 , 0.921 ] seconds.
(sqlite.dll) Updated list with 568 in [ 0.187 , 0.172 , 0.172 , 0.156 , 0.14 ] seconds.



Switching fetch_field back to fetch_row results in a speed loss ( [ 1.092 , 1.107 , 1.045 , 1.03 , 1.045 ] seconds ).


I'm kind of curious if this could be OS related (I am running Vista x64), so it'd be great if others could test this out as well just in case it really is an OS issue. Other than that, this is a great DLL and script, but I'd be extremely happy if the performance was on par with the original sqlite.dll~

uncleramiMar 16, 2009 5:35AM
How big is your flist table?

I can understand the difference in speed with $sqlite_fetch_row(), because as you suspect, it invokes a bunch of mIRC commands via the DLL to populate the hash table (it doesn't parse anything though), but $sqlite_fetch_field does nothing more than directly return the value from the buffered result-set (it doesn't even invoke any SQLite API functions), so I'm kind of surprised to see that big difference in speed. Well it does a bunch of sanity checks, and checks whether it's dealing with buffered/unbuffered result set, and depending on that does different things, but there's nothing that would explain that big difference in speed, especially because it doesn't invoke any mIRC commands or evaluate any mIRC variables/identifiers.

If you don't mind, can you send your complete script + db to me in e-mail, and I'll look into it, and profile what's the slow part and try to make it as fast as possible.



"Also, any chance of us getting a standalone DLL option that's closer to the way sqlite.dll works (that way we don't have to rely on a secondary script? (I don't really want to have to make someone install this script just to use a script I made.)) "

Well, if you really want to do this, you can check how the DLL API works by reading up msqlite.mrc, but I have no intention of documenting this. The reason is because quite few of the methods do some complex logic to pass all the info needed to the DLL, and it'd compromise usability if users had to do this themselves (although nothing prevents you from doing it if you want to, the code in msqlite.mrc is quite clear if you want to check how it works from it). Another reason is that sometimes I need to change what parameters the MRC aliases pass to the DLL to implement a new feature, and if people called the DLL code directly, it'd break existing scripts, hence to keep things as usable, intuitive and compatible as possible, I don't intend to officially support using the DLL directly. But since you're already including msqlite.dll, I don't see the harm in including the msqlite.mrc as well, and making your script load it when it's loaded as well.

uncleramiMar 16, 2009 5:59AM
Oh and forgot tell, but my e-mail is reko@tiira.net

uncleramiMar 16, 2009 6:10AM
Oh, one more thing you could check. Try doing a performance comparison that doesn't include the call to $sqlite_query, put var %ticks = $ticks after it. I think the performance difference comes from $sqlite_query, not $sqlite_fetch_field. $sqlite_query does a *lot* of things that the other SQLite DLL doesn't support. For example it does query analyzing (because my DLL extends the default SQLite query syntax by adding support to binary variables), as well as parameter binding. However the biggest performance degrade might become from the fact that it buffers the results, the other SQLite DLL might use unbuffered queries by default.

Try changing $sqlite_query() to $sqlite_unbuffered_query(), and see if that's faster for you.

JustariusMar 16, 2009 3:58PM
I'll send you it so you can play with it yourself, however, I think I just found the biggest slow down. The mIRC aliases. I kid you not. Since you were using multiple $gettok() and an identifier to return the DLL location, mIRC had to parse those each and every time, causing *much* of the slow down I was seeing.. Mind you, I've added the unbuffered_query to this, and moved %ticks below the query. (There's 568 rows in the flist btw, which is why it says "Updated (dialog) list with 568 in X seconds").

Fetch Row:
Using: while ($sqlite_fetch_row(%push, row, 3)) {
*** (mSQLite.dll) Updated list with 568 in 1.107 seconds.

Using: while ($dll(msqlite\msqlite.dll, msqlite_fetch_row, %push row 3)) {
*** (mSQLite.dll) Updated list with 568 in 0.375 seconds.


Fetch Field:
Using: while ($sqlite_fetch_field(%push, fname)) {
*** (mSQLite.dll) Updated list with 568 in 0.983 seconds.

Using: while ($dll(msqlite\msqlite.dll, msqlite_fetch_field, %push 1 fname 1)) {
*** (mSQLite.dll) Updated list with 568 in 0.219 seconds.



That's quite a huge speed increase! It'd definitely be more optimal for me to use $dll() over the mIRC aliases. Having mIRC parse too many identifiers in a loop is definitely not good. 0.219s vs 0.187 is MUCH more reasonable. :D

Text edited by author on Mar 16, 2009 @ 5:21PM


uncleramiMar 16, 2009 7:43PM
Well I knew mIRC's parser sucked, but wow, that is pretty severe!

I think it's weird that you have so big difference in speed though. I just tried this little alias:

speed_test {
var %db = $sqlite_open_memory()
sqlite_begin %db
sqlite_exec %db CREATE TABLE flist (fname)
var %i = 1, %sql = INSERT INTO flist (fname) VALUES (?)
while (%i <= 1000) {
noop $sqlite_exec(%db, %sql, %i)
inc %i
}
sqlite_commit %db

var %ticks = $ticks

var %sql = SELECT * FROM flist
var %res = $sqlite_unbuffered_query(%db, %sql)

while ($sqlite_fetch_single(%res)) {
}

echo -a ticks: $calc($ticks - %ticks)

sqlite_free %res
sqlite_close %db
}

Which inserts 1000 rows. This reports ticks to be 185-190ms. I then changed the while loop into:
while ($dll(scripts\msqlite.dll, msqlite_fetch_single, %res)) {

It now runs as 95-100ms. It's a big difference, but nowhere near as big as what you were getting.

(mysql_fetch_single and mysql_fetch_field are almost identical in the back-end code, the mysql_fetch_field just has sanity checks to make sure you aren't trying to fetch an non-existant field).

Anyway, if performance is more important than intuitive API, then you should invoke the $dll directly, although I can't make any promises that the internal $dll calls won't change in the future. :p

uncleramiMar 16, 2009 7:51PM
EDIT. Disregard this!

Text edited by author on Mar 16, 2009 @ 7:59PM


JustariusMar 16, 2009 9:13PM
Hmm that's pretty interesting. Using your alias my results are:

Using your custom aliases >> ticks: 1326
Using a direct DLL call >> ticks: 171


My results vary drastically from yours, hmm. *Tests this on a fresh mIRC install*


Oh this is quite interesting. Fresh install:

Now the results are:
Using your custom aliases >> ticks: 234
Using a direct DLL call >> ticks: 125



....
Alright I found the real culprit behind this case. Wow what a lot of work for almost no reason.

Just on a whim, after testing this in a fresh install, I decided to use File -> Order, and move msqlite.mrc to the very top in my main install, and retry this test. It appears that this was the true problem. An average mIRC user who doesn't write much or use much, would never see this issue. But since I tend to write a ton of aliases, and have a rather massive script collection (pretty much all of it is my own, I tend to make example aliases and scripts for people when I have free time.) Anyway, I digress, the real issue is the script order. mIRC was scanning all the files for an alias that matched "sqlite_fetch_whatever" and since msqlite.mrc was one of the last files, it had to scan all my other files first.

Changing this, and moving it to the very top brought me up to:


Using your custom aliases >> ticks: 250
Using a direct DLL call >> ticks: 156



So back to my other example!
Now with mSQLite.dll, in my own test script (the one I mailed you) I get:
Using $sqlite_fetch_field(): 568 rows in 0.343 seconds. (DOWN from 0.983)
Using $dll(msqlite\msqlite.dll, msqlite_fetch_field, %push 1 fname 1): 568 rows in 0.234 seconds.

109ms difference. Not quite so drastic.

So I guess what we learned here today is, if you plan on using a custom alias within a loop that has several identifiers in it that need to be evaluated.... make sure you move that script to the very top of your script list via "File -> Order"

Thanks for cooperating unclearami~


Edit: I'm curious, and not sure how difficult it'd be to implement but this could possibly yield really fast results. Generally it's a *LOT* better to /filter something into a window or a dialog listbox, so I was wondering if maybe it'd be possible for you to implement a way to export something to a .txt file, such as doing a: $sqlite_fetch_field_intofile(%res, fieldname, tempfile.txt), and have the dll write all the results to a file. Anyway, just an idea. ;x

Text edited by author on Mar 16, 2009 @ 9:47PM


uncleramiMar 17, 2009 4:14AM
That's interesting, I learnt something new too! I had msqlite.mrc at very top in the first place. Another interesting note: You can boost the speed by ~20ms if you completely remove all the comments from the msqlite.mrc file.

Anyway, writing a result directly to a file is not a bad idea, but the real problem is how to make it safe. One of the main reasons why I have a $sqlite_* API instead of directly interacting with the DLL is that this way I can ensure all the data returned safe. I've seen DLLs that do stuff like return rows with fields separated by | characters,or similar, which will break very easily if there's character like | in the actual data. The same problem exists here. What if the actual data contained \n, for instance.

If you have any good ideas how to implement this so that it's logical and generic, I'm listening. I don't want to do anything "hacky" though, because I've gone a long way with this addon to ensure data integrity and validity, and I've tried to avoid making compromises about the interface from the beginning.

EDIT. One possible solution: I could use something like urlencode to encode all the special characters. This would be good because I could ensure that each line holds a single row, and each line has the fields separated by spaces. Another good thing is that if the user knows he doesn't have any special characters (aside from like 0-9 a-z and _), he doesn't need to care about the encoding. The not so good thing is that mIRC provides no method to urldecode by default (though I could include one with mSQLite so it's not a huge issue). It's better than using $encode with mime/uuencode because they will encode *every* character (which would make sorting with /filter harder).

EDIT. When thinking further, it could even be a simplified urlencode encoding. It would only need to encode white space characters (spaces, tags, newlines) as well as % itself (because special characters are encoded as %HEX). What this would mean that unless the user has whitespaces, or percentage sign in the field, he does not need to worry about decoding at all, otherwise he can supply a custom alias to /filter or /play or whatever which would use $sqlite_urldecode as necessary.

Text edited by author on Mar 17, 2009 @ 4:30AM


JustariusMar 17, 2009 4:54AM
That's a good point. I didn't really consider the possibility of a new line being in the data when I mentioned it, so we'd definitely need some form of encoding in that case. But it'd definitely be a great addition for when we know there's not going to be special characters involved (I generally never store any data that'd have a new line in it, for example.). Maybe have an extra switch for encoding.

uncleramiMar 17, 2009 5:29AM
I'll implement this for the next version. It won't be $sqlite_fetch_field_intofile, but more like $sqlite_fetch_into_file (or maybe $sqlite_fetch_all), and it'll fetch all fields to the file (if you want to only fetch one field, you should only specify one field in the SELECT query). Field values will be encoded in an URL-encode like manner, except it only encodes spaces, newlines, percentage characters and the null terminating character (for binary fields).

Text edited by author on Mar 17, 2009 @ 5:36AM


JustariusMar 17, 2009 8:47PM
Hrmn, what's the reason for encoding regular spaces?


This presents a couple problems.

(Note the multiple spaces below.) (I enclosed in code brackets to retain spaces.)

Say you have a variable that equals: "Text     MoreText"
Say you have a file with a single line: "Text     MoreText"

If you attempt to do: /did -a $dname 555 %theVariable,  you will only get "Text MoreText"
However if you do: /filter -fo file.txt $dname 555, you will get "Text     MoreText"

If we are filtering to a dialog/@window, and the info contains spaces (no new lines), we have no choice but to use /filter -k, in order to use the decode on it, which would force us to use either /aline (/echo) or /did, thus losing all double consecutive spaces. Due to the way mIRC drops double consecutive white spaces when tokenizing it's own commands, we'd have to turn to a seperate DLL in order to keep the white spaces intact (spaces.dll (@windows), DCX (dialogs)), which is kind of a shady way to deal with the issue.


Edit: Changed what I posted a bit, for clarity.

Text edited by author on Mar 17, 2009 @ 9:05PM


uncleramiMar 18, 2009 7:16AM
Well I was planning on using space as separator for field values (because /filter has -t for sorting by certain column, the field separator has to be encoded). But I guess I can make it so that you can specify the field separator character yourself.. something like $sqlite_fetch_all(%result, result.txt, 32) where the third parameter is the ASCII value of the separator (32 being space, but you could use whatever you want). This would probably do the trick, as you could use any ascii character as separator, instead of space.

JustariusMar 18, 2009 4:10PM
That'd be awesome. :) (I'm more than familiar with /filter, hehe). The sqlite.dll does the same thing (in terms of specifying the seperator for returning an entire Row as a string and what not), but it doesn't have a "to file" option!

I'm really looking forward to this addition~

uncleramiMar 18, 2009 4:57PM
I'll try to do it asap. I have few other things on my to-do list for next version. One of the biggest things is rewriting a big part of the docs again because mSQLite has matured a lot from its first version back in 2006, and the how-to's and examples are outdated and teach new users ways that have better alternatives in recent versions. Anyway if things go well I might be able to release it this or next week.

uncleramiMar 20, 2009 10:07PM
Ok update. The new fetching mode is done, but I am not going to release a new version for a while (still got a bunch of stuff on my to-do list for next version), but if you want a pre-release version, I can send you one.

JustariusMar 21, 2009 11:25PM
woot, awesome!#

Also, yeah, I'd like to play around with the pre-release. Can send it to the email I emailed you with (preferably), or to "scriptin" at "gmail" dot "com".

uncleramiMar 23, 2009 5:12AM
I will e-mail it when I get back from work.

uncleramiDec 21, 2008 8:27PM
It took a bit longer than intended, but finally got some time to finish the new version. Hopefully you'll like the new features. :)

one4twoDec 31, 2008 7:43PM
Great - will soon update :)

Maybe you can tell me whats wrong with :)

alias spamcount {

var %Search = $2-
if (!%Search) {
msg $chan No Input
return
}

; We want to search for text even if it's in middle of the quote
%Search = $replace($+(*,%Search,*), $chr(32), *)

; Replace wildcard * with % and escape greet
var %safe_Search = $replace($sqlite_escape_string(%Search),*,%)

; Find text from greets

var %sql = SELECT COUNT(*) FROM greets WHERE Search LIKE ' $+ %safe_Search $+ '

var %request = $sqlite_query(%greet_db, %sql)

var %spam = $sqlite_fetch_single(%request)

msg $chan I have %spam Entries

; sqlite_free %request
}

The Problem with this Code is, when im trying to find something like: _TEST- it also counts results like _thisTEST- (why is in this example also the word this integrated ) ? I just want to count exactly with _TEST- and not with _abcTEST-

uncleramiJan 1, 2009 12:38PM
_ matches any single character in LIKE (which is why it'd match TEST preceded by anything). If you want to use the literal _, you gotta escape it, like this:

var %safe_Search = $replace($sqlite_escape_string(%Search),\,\\,_,\_,%,\%,*,%)
var %sql = SELECT COUNT(*) FROM greets WHERE Search LIKE ' $+ %safe_Search $+ ' ESCAPE '\'

Now you can use \ to escape % and _. By the way, in the new version you can write the above like this instead:

var %sql = SELECT COUNT(*) FROM greets WHERE Search LIKE ? ESCAPE '\'
var %request = $sqlite_query(%greet_db, %sql, $replace($sqlite_escape_string(%Search),\,\\,_,\_,%,\%,*,%))

Basically, you can bind parameters even without preparing statements, just pass the bound parameters to $sqlite_query directly. No need to $sqlite_escape_string() anymore, which means cleaner and safer code. :)

one4twoJan 1, 2009 1:48PM
Mannnnny thanks - works perfect !!

one4twoJan 5, 2009 5:10PM
Is there something comparable to CHAR_LENGTH ?

I need a query to check, if there are entry's less than eg 12 Chars

uncleramiJan 5, 2009 7:16PM
You can use LENGTH()

List of core functions in SQLite can be seen here: http://www.sqlite.org/lang_corefunc.html

one4twoJan 6, 2009 3:28AM
oh , but why cant i use it like

var %sql = SELECT * FROM greets WHERE (length(Data) == 14) LIKE ' $+ %safe_Data $+ ' ESCAPE '\' ORDER BY rowid DESC LIMIT 50

i only want to have results, where the Text data displays exactly 14 Chars or < 14, whatever ^^

uncleramiJan 7, 2009 2:45AM
You need two separate conditions in the WHERE, you (length(Data) == 14) returns a boolean true/false, you can't compare it with LIKE. To do what you want (to get data with length 14 or less, and that matches the LIKE):

var %sql = SELECT * FROM greets WHERE length(Data) <= 14 AND Data LIKE ' $+ %safe_Data $+ ' ESCAPE '\' ORDER BY rowid DESC LIMIT 50

one4twoJan 7, 2009 1:09PM
works perfect - many thanks !

one4twoJan 19, 2009 12:59PM
Ive got a new question :>

Im having a row, named Unix, where i store $ctime

Now i wanna get some extra information like, how many entries have been added the last day, last week, or the last month, ...

I am starting with the last day:

var %sql = SELECT Unix FROM greets WHERE strftime('2009-19-01')

but like expected, that didnt work :/

uncleramiJan 19, 2009 3:05PM
To get number of entries for today:
var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix) = DATE('now')

To get number of entries for the current month:
var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix, 'start of month') = DATE('now', 'start of month')

et cetera

one4twoJan 19, 2009 4:10PM
Many thx!! , but wether im to ... whats wrong with this

alias daystats {
var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix) = DATE('now')
var %request = $sqlite_query(%greet_db, %sql)
var %db-total = $sqlite_num_rows(%request)
msg $chan Today added: %db-total
}

i always receive 1 as Resulst, but thats for 100% wrong ^^

uncleramiJan 19, 2009 8:30PM
That's because that query only fetches one row. It's bad practice to get a count by fetching all the rows (because that requires a lot of unneeded memory), instead you use COUNT(*), which only selects the number of found rows as a regular value, and doesn't actually buffer those rows unnecessarily into memory:

alias daystats {
var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix) = DATE('now')
var %request = $sqlite_query(%greet_db, %sql)
var %db-total = $sqlite_fetch_single(%request)
sqlite_free %request
msg $chan Today added: %db-total
}

Also don't forget to sqlite_free your results.

one4twoJan 20, 2009 12:52AM
Im very sorry to say, but nothing has changed. Well thats not exactly correct. I receive Zero as Result.
Maybe its because the Date/Time in my Unix row is like in $ctime ?

Text edited by author on Jan 20, 2009 @ 1:41AM


uncleramiJan 20, 2009 3:11AM
Sorry there was a tiny error in the query, should be this:

var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix, 'unixepoch') = DATE('now')

one4twoJan 20, 2009 4:00AM
yeah !!! great :) THANK YOU !

so lets go on ^^

I have this query:
var %sql = SELECT * FROM greets WHERE Data LIKE ' $+ %safe_Datal $+ ' ESCAPE '\' ORDER BY rowid DESC LIMIT 50

Now i want a second one, that counts the Results, BUT as a above, with a limit of 50

if there are eg 20 founds. It should be displayed, like: im having 20 .... in DB

Im not able so set a llimit in the count - ive tried like:

var %sql2 = SELECT COUNT(*) FROM greets WHERE Data LIKE ' $+ %safe_Data $+ ' ESCAPE '\' ORDER BY count(*) DESC LIMIT 50

but that does count all matches ^^

edit: the found Result is wrong, if there are more than 50 founds.

The Results in the first query, that i get query, should match the displayed number of the second query

Text edited by author on Jan 20, 2009 @ 4:18AM


uncleramiJan 20, 2009 11:56AM
var %sql2 = SELECT COUNT(*) FROM greets WHERE Data LIKE ' $+ %safe_Data $+ ' ESCAPE '\' ORDER BY count(*) DESC LIMIT 50

You're misunderstading how selecting count works. When you do SELECT COUNT(*) in this simple context, it _always_ returns a single row, thus ORDER BY or LIMIT makes no sense. If you simply want to limit the number of the count to 50, then it's just a simple check if count > 50 then count = 50.

one4twoJan 23, 2009 6:09AM
sometimes its to simple ^^

currently im trying to get the stats of the last 24hours. Incredible, im to ... :/ ive tried so many .. but didnt catch the right one.

PS: var %sql = SELECT COUNT(*) FROM greets WHERE DATE(Unix, 'unixepoch') = DATE('now')

Im not sure, if this is correctly working. Ive done the cmd at 00:01 am and its says > 1000 added , but there have been less than 10

edit: Just recognized; date with date Now starts counting with 1:00 am

Text edited by author on Jan 25, 2009 @ 6:56AM


uncleramiOct 4, 2008 5:49PM
Sorry for the lack of updates!

I'm working on a new version that updates SQLite to the latest version and has ability to execute scripts directly from files (great for initialization queries and such). Expect it this or next week.

one4twoOct 19, 2008 1:16AM
Well - great to read !

one4twoOct 19, 2008 12:57PM
Can you tell me, whats wrong with this:

on *:START:{
set %greet_db $sqlite_open(greet.db)
if (!%greet_db) {
echo 4 -a Error: %sqlite_errstr
return
}


sqlite_exec %greet_db CREATE TABLE IF NOT EXISTS greets (.....)

so far so good

Now i want to attach a second database. Im trying it like:

sqlite_exec %greet_db ATTACH DATABASE greet2.db AS greets

... and nothing is happening

Both DB's are completey the same, colums,... only the Filenames are different.

Can u plz help ? :)

Text edited by author on Oct 19, 2008 @ 12:58PM


uncleramiOct 24, 2008 6:54AM
What do you mean nothing is happening? Does it give an error? ATTACH DATABASE isn't supposed to return any data; It just attaches the database to your connection so you can use it in queries after you've attached it.

one4twoOct 24, 2008 7:20AM
Is the syntax i'm using correct ? There is no error output

I mean nothing is happening, that when i do a search, there is no result of the attached database. Do i have to rewrite my queries or something else ?

Text edited by author on Oct 24, 2008 @ 7:24AM


uncleramiOct 24, 2008 7:55AM
The syntax is fine, but you can't just do

SELECT * FROM greets

To get everything from the greet.db table as well as greet2.db. To get greets from greet.db, you do that, but to get them from greet2.db you need to do:

SELECT * FROM greets.greets

Basically when you have this:

sqlite_exec %greet_db ATTACH DATABASE greet2.db AS greets

The "AS greets" part means, that whenever you want to access a table from greet2.db, you need to use greets.TABLE_NAME, without the "greets." prefix before the table, you're just accessing the table of the originally opened database. If they have exactly same columns, you can use UNION JOIN to select both tables in same query, and search from both:

SELECT * FROM greets UNION SELECT * FROM greets.greets

Just know that each of your SELECT statements in above query needs their own WHERE clauses et cetera (but only one ORDER BY). You might want to use one database file to start with, and not use two separate databases, if possible.

one4twoOct 24, 2008 8:19AM
Thank you very very much, especially for the detailed explanation!!! As soon as i'm @home, im gonna try this :)

one4twoOct 24, 2008 10:37AM
I'm sorry, but i need further help :(

if I m using it like:

var %sql = SELECT * FROM greets.greets WHERE Data LIKE ' $+ %safe_Data $+ ' ORDER BY rowid ASC LIMIT 1

the attach function does work, but i only have access to the attached (greet2.db)Database .. not to the greet.db

Thats the first thing and if i'm trying:

var %sql = SELECT * FROM greets UNION SELECT * FROM greets.greets WHERE Data LIKE ' $+ %safe_Data $+ ' ORDER BY rowid DESC LIMIT 1

nothing does happen. No error message and no output. Both DB's do have exactly the same columns. In both examples i am using the attach command.

Maybe i am missunderstanding something. Especially i am interested in the UNION Selection, to search in both DB's. Or is there any other way, to ADD a second Database ?


PS: Attaching only works when using: ' around the db.name '
sqlite_exec %greet_db ATTACH DATABASE 'greet2.db' AS greets

Text edited by author on Oct 25, 2008 @ 1:16PM


uncleramiOct 26, 2008 12:16PM
Like I said, you need to have own WHERE clauses for both SELECTs in the UNION. Change this:

var %sql = SELECT * FROM greets UNION SELECT * FROM greets.greets WHERE Data LIKE ' $+ %safe_Data $+ ' ORDER BY rowid DESC LIMIT 1

To:

var %sql = SELECT * FROM greets AS g1 WHERE g1.Data LIKE ' $+ %safe_Data $+ ' UNION SELECT * FROM greets.greets g2 WHERE g2.Data LIKE ' $+ %safe_Data $+ ' ORDER BY rowid DESC LIMIT 1

And see if it helps.

one4twoOct 26, 2008 12:53PM
I'm sorry to say, but nothing has changed. No access to both Databases; no error message. Maybe there's something on the request, that need to be changed ?

uncleramiOct 27, 2008 2:53AM
If there's no error message, only thing I can say without testing myself is that it doesn't find anything. I can try later myself, when I get home from work.

one4twoOct 27, 2008 9:42AM
I appreciate your Help very much ! Thx. For me it seems, that there's no connection to the Databases. The thing i'm trying to find is for sure in both Databases.

snkAug 23, 2008 4:15PM
Is it possible to retrieve the primary key for a newly added row, like @@IDENTITY in Mssql server?

Would be smart if $sqlite_exec returned primary key for the new row when using INSERT, and "affected rows count" when using DELETE or UPDATE

uncleramiSep 20, 2008 8:20AM
To retrieve it within mIRC script, you can use the $sqlite_last_insert_rowid() identifier. To retrieve it in a query, you can use the last_insert_rowid() SQL function.

I thought of making $sqlite_exec() return the affected rows or newly created key, but decided against it because I wanted to keep it consistent. It's easy enough to write a wrapper alias to do that if you want it.

mcm2Aug 7, 2008 1:54PM
Rating:     10Okay, first I would like to thank you for this more than AMAZING thing you've just did there :)

Second, I have some problems... Everything works, except inserts... I've been using SQL for a long time but as always: I can do errors as well :P

I did a little function to use for inserts here it is: (and the way to use it)

alias sqlInsert {
  if (!$1) {
    echo 2 sqlInsert: Missing argument.
    halt
  }

  %sqlite_query = INSERT INTO %sqlite_db ( $+ %sqlite_rows $+ ) values( $+ %sqlite_values $+ )
  return $sqlite_exec($1, %sqlite_query)
}

alias test {
;%DefaultDB is set when mIRC launches.

  %sqlite_db = database_name
  %sqlite_rows = 'row1', 'row2'
  %sqlite_values = 'value number one', 'value number two'
  sqlInsert %DefaultDB
}

For any unknown reasons, it does not insert any data when I try it...

Woud be nice if you could help me on that :) I'm using mIRC 6.32

Thanks a lot!

uncleramiAug 9, 2008 2:24PM
The problem might be this:

%sqlite_rows = 'row1', 'row2'

Columns aren't strings, so you shouldn't use single quotes. Just

%sqlite_rows = row1, row2

Should work.

If that doesn't work, try printing %sqlite_errstr, it'll tell you what the error is (everytime a query fails, mIRC SQLite will set the cause of the error in the variable %sqlite_errstr).

mcm2Aug 9, 2008 3:22PM
THANKS a lot!

I wasnt aware of sqlite_errstr. Really helpful.

one4twoMay 26, 2008 11:33AM
Hello - hopefully someone can help me out of this

i'm adding like:

var %safe_Date = $sqlite_escape_string(%Date), %safe_Time = $sqlite_escape_string(%time), %safe_Rel = $sqlite_escape_string(%Name)
sqlite_exec %greet_db INSERT INTO greets (Date, Time, Name) VALUES (' $+ %safe_Date $+ ', ' $+ %safe_Time $+ ', ' $+ %safe_Name $+ ')

and searching:

var %x = 1
while ($sqlite_fetch_row(%request, row) && (%x <= 2)) {
msg $chan [DB] $hget(row, Date) $hget(row, Time) $hget(row, Name)
inc %x

no my problem on multiple results, because i want the latest eg 2 entries and not the oldest one. Anyone can help me plz ?

Text edited by author on May 26, 2008 @ 11:36AM


uncleramiMay 28, 2008 5:58AM
You just need an ORDER BY for your SELECT query. If your table has an auto incrementing id column, you can order by it, but even if it doesn't SQLite stores it internally and you can refer to it with the rowid name. Depending on which format your date/time is in, you can also use them to sort the resultset. Here's an example of what you can add at the end of your SELECT query:

SELECT ... ORDER BY rowid DESC LIMIT 2

This also shows you that you should limit the results to two in the query level, NOT in your while loop in mIRC scripting (because if you don't limit it in the query, SQLite will buffer results that you don't even need to, thus decreasing performance of your script).

one4twoMay 28, 2008 1:51PM
Great !!! I appreciate your help - Thank you. One more further question plz:

Im adding Data like: previous text something like this on channel:

Add - K12 - Data-some-Data -

ON *:TEXT:*:#:{
if ($1 == Add) {
/adding $date $time $3- }
}

alias adding {


; Make sure we are connected to db
if (!%greet_db) {
echo 4 -a Error: Not connected to greet.db
return
}

; Add greet
var %Date = $date, %Time = $time, %Data = $5, %K = $3, %P = $gettok($5,-1,45)
if (!%Date || !%Time || !%Data || !%K || !%P) {
echo -a 6 Error: Invalid arguments!
return
}

; Escape nick and greet and then execute query
var %safe_Date = $sqlite_escape_string(%Date), %safe_Time = $sqlite_escape_string(%time), %safe_Data = $sqlite_escape_string(%Data), %safe_K = $sqlite_escape_string(%K), %safe_P = $sqlite_escape_string(%P)
sqlite_exec %greet_db REPLACE INTO greets (Date, Time, Data, K, P) VALUES (' $+ %safe_Date $+ ', ' $+ %safe_Time $+ ', ' $+ %safe_Data $+ ', ' $+ %safe_K $+ ', ' $+ %safe_P $+ ')
echo -a Added %Date %Time %Data %K %P
}

The Data Field is UNIQUE

There is one more colum named: N - If the following happens, i would like to have, that the information is stored in an existing-matching line, but only added to the colum N

ON *:TEXT:*:#:{
if ($1 == change) {
/updating $1- }
}

event on chan:
Change - Data-some-Data - nothing.changed.today

alias updating {
; find/check(if existing) matching line and add to Colum N - text
???

; Add greet
var %Data = $3, %N = $5
if (!%Data || !%N) {
echo -a 6 Error: Fivalid arguments!
return
}

I'm sorry, but i've no idea how to go on :(

Hopefully you can help ? :)

uncleramiMay 29, 2008 1:25PM
UPDATE greets SET N = ' $+ %Safe_N $+ ' WHERE Data = ' $+ %Safe_Data $+ '

one4twoMay 30, 2008 10:27AM
Perfect !!!!! Many many thanks!!!

last but not least - one further question plz

eg:

Data row N row

Data1 nothing changed
Data2
Data3 changed to 4

i want something like this:

Total in Data row counted matching: *Data* and Changed: 2 Entries

I dont know how to combine the counting for 2 OR more rows. May i ask you one more time for ur help ? :)

uncleramiMay 30, 2008 2:48PM
I'm not sure I understand, but from what I gather you want to get the number of rows that were changed by the UPDATE statement. If so, you can use:
$sqlite_changes(%greet_db)

This will return the number of rows that were changed by the UPDATE statement that was last issued.

one4twoMay 30, 2008 3:30PM
*deleted* i got it on my own :)

sqlite_exec %greet_db UPDATE greets SET N = ' $+ %Safe_N $+ ' WHERE Data = ' $+ %Safe_Data $+ '

works perfect!

but when N is updated, an entry in for eg: U should be deleted. Can i combine the functions or is there another way to do so ?

I've tried to set a $null in U with another update, but when i count , the $null is also counted and thats not what i want. When there is an UPDATE in N, the field U should be completely deleted.

What can i do ? Sorry for asking so many times for your help

Text edited by author on May 31, 2008 @ 12:44PM


uncleramiJun 1, 2008 4:45AM
What do you mean with completely deleted? You an of course do this:
sqlite_exec %greet_db UPDATE greets SET N = ' $+ %Safe_N $+ ', U = NULL WHERE Data = ' $+ %Safe_Data $+ '

Which would set U to NULL ($null is for mIRC, it doesn't work in SQLite).

one4twoJun 2, 2008 7:31AM
mannnnnny thanks !

one4twoJun 5, 2008 3:57AM
How can i make a backup or copy of the DB, without getting error code 26 ?

I've copied the file and tried to open with another mirc, but that doesn't work .

uncleramiJun 5, 2008 12:28PM
Copying the file should be fine.

one4twoJun 17, 2008 8:40AM
Just don't know, what i've done wrong.. Thx

Hopefully this is my absolute last question ^^

How can i UPDATE / REPLACE everything matching: whatever .. in just ONE row ? :)

for eg: Row: test

entries: test1, test1 and so on

Now i want to replace the word test1 with another. Damn, but im to stupid to find out on my own :(

I've tried:

sqlite_exec %greet_db UPDATE INTO greets SET test = ' $+ %Safe_test $+ ', test = my_text

but that's not working. Maybe you can help me out one mor time? :)

HAPMMay 3, 2008 8:18PM
One suggestion for the next release. I got this idea because it takes very long time to add much data. Because i think it's mIRC's lazy dll-layer, it would be nice to execute multible (long) querys with only one dll call. I thought about something like writing the querys with mIRC into a sql-script file and giving the path to the file to the dll. I would wonder if there is no sqlite function to execute sql-files. And if there is none, would it be hard to write one?

Another benefit would be that u can create setup sql-scripts, so u can have something like this in your code:

var %id
if (!$isfile(dbfile)) {
%id = $sqlite_open(dbfile)
sqlite_exec_script %id scriptfile
}

and other simple task like creating temptables on startup etc...

I think u get the idea.


Would be nice if you could implement that. And thanks for this great dll, i forgot that on my last post 2 years ago hehe.

Text edited by author on May 3, 2008 @ 8:20PM


uncleramiMay 4, 2008 3:27AM
This is a great idea and will surely be added. :)

PS. If you run a lot of queries that insert data in row, wrap them inside a transaction (see /sqlite_begin and /sqlite_commit in help file), it'll speed up the process a lot.

HAPMMay 28, 2008 11:56AM
Tried _begin and _commit and your right. Speed is increased by about 90% o0 when executing about 20 INSERT-querys. Didn't know that transactions have so much effect in sqlite. So i think my idea is mostly helpful for database creation or initialisation. Thx for the tip.

mointJan 26, 2008 7:10PM
What seems to be wrong in here? I can't get results from it ...

set %desi_db $sqlite_open(test.db3)
if (!%desi_db) {
echo 4 -a Database Error: %sqlite_errstr
return
}

else {
var %sql = SELECT * FROM badnick
var %res = $sqlite_query(%desi_db, %sql)
if (!%res) {
echo 4 -a Query Error: %sqlite_errstr
return
}
echo -a Total Nicks Found: $sqlite_num_rows(%res)
while ($sqlite_fetch_row(%res, rows)) {
echo $hget(rows,nick)
}
sqlite_free %res
}

The total nicks found does give an output, but the $hget is not giving any rows back. Thanks.
(using mIRC ver 6.31 and 3.5.4 for SQLite)

tontitoFeb 1, 2008 3:47AM
Hello,

Please replace while ($sqlite_fetch_row(%res, rows)) { by

while ($sqlite_fetch_row(%res, rows, $SQLITE_ASSOC)) {


cheers

uncleramiFeb 1, 2008 1:07PM
I can figure out three possible reasons for this:

1) There's no column "nick" in the badnicks table.
2) The line is too long (I doubt this since it's unlikely the nicks are ~900 chars :p)
3) The echo is messing up. Try with -a and possibly with a static prefix to prevent mess ups with empty fields: echo -a nick: $hget(rows, nick)

tontitoNov 16, 2007 11:25AM
Hi there,

Just to be sure, can we bind variables with this?
I have been looking at sqlite page and your documentation and it seems that it isn't yet supported.

Any plans to had this, if you haven't add it yet?

kind regards

uncleramiNov 16, 2007 2:09PM
The chances are that it will never have bind support. There are couple of benefits with binding arguments: They prevent SQL injections and they can speed up queries if a query is run multiple times with different arguments (since it only needs to be prepared once). Since mIRC is not really the platform for developing speed critical applications, the second benefit doesn't really concern mIRC as much (because the speed difference isn't that big in your average queries that you'll be running in mIRC scripts). As for preventing SQL injections, for now I just have to trust that the users of this script are either knowledgeable about SQL, or read the documentation.

Binding has some added complexity because the user has to understand and learn to execute queries in steps, and I trust that if someone understands prepared queries and bindings, they also know how to escape data to prevent SQL injections. There's nothing you can't achieve with the functions that are in the library already, that you could with binding. However I do acknowledge that binding would be a nice addition for those who want to use it, so I might implement it in the future, but not for a while likely.

tontitoNov 16, 2007 5:00PM
Well i subscribe as wanting it, whenever you can provide it.

I would love to see how it behaves in mirc for my 2k queries and inserts running at once.

Kind regards.

uncleramiDec 6, 2007 6:39AM
Hey,

Sorry for the lack of updates, I've had a hectic 2-3 weeks. I'm working on a new mSQLite version, that updates SQLite to latest version and has a bind support. I'm hoping to release a new version during this week.

tontitoDec 9, 2007 4:21PM
Great news m8, thanks :D

tontitoDec 16, 2007 11:48AM
Where is the precious m8? :D

uncleramiJan 7, 2008 7:45PM
Sorry I kind of forgot about this, been so busy with other stuff.
I'm planning on release the new version today or tomorrow (it'll be kind of big release, will be called 1.1.0).

uncleramiJan 8, 2008 6:08PM
Update. I didn't get everything done yet (all of the code for new version is written and ready, but I haven't updated the documentation yet as I have to write a couple of new How-To's). I'll leave for army tomorrow, but the update will be posted this weekend. Here's the current changelog, so you know there's something to wait for (I still might add some stuff, but it's pretty final). :)

* Due to the major additions, changes and bug fixes in this version, this version will be called 1.1.0. Upgrading is strongly recommended.
* Added support for prepared queries and binding parameters. For information about various binding styles that are supported by mSQLite, see Binding Parameters.
* Added $sqlite_unbuffered_query. For information about differences between buffered and unbuffered queries, see Unbuffered Queries.
* Added $sqlite_prepare.
* Added $sqlite_reset.
* Added $sqlite_bind_field and its alias $sqlite_bind_column.
* Added $sqlite_fetch_bound.
* Added $sqlite_current_bound.
* Added $sqlite_bind_param.
* Added $sqlite_bind_value.
* Added $sqlite_clear_bindings.
* Added $sqlite_is_valid_statement.
* Updated $sqlite_exec and $sqlite_query to be able to execute prepared statements.
* Updated $sqlite_fetch_row to set $SQLITE_NOMOREROWS as an error code when there are no more rows.
* Updated $sqlite_open and $sqlite_open_memory to also copy triggers and views when copying database from another file.
* Updated SQLite to version 3.5.4. There are a lot of important bug fixes inbetween this version and 3.4.1. Upgrading is strongly recommended.
* Updated PCRE to verion 7.4.
* Updated mSQLite to enable the FTS3 module in core by default.
* Fixed a long standing bug in $sqlite_query not returning 0 when the query is a type of query that never returns rows (INSERT/UPDATE/DELETE).
* Fixed a bug where user defined functions that run nested queries removed temporary file created by TMPARG() prematurely.
* Removed FTS1 and FTS2 modules from the core to reduce size since they're obsolete. It's recommended to update your existing code to use FTS3 instead. You can still use the older versions by compiling and loading the modules by yourself.

tontitoJan 11, 2008 5:59PM
Great job m8.

I think i have to say that you rule :D

uncleramiJan 12, 2008 8:24PM
Released. :)

tontitoJan 13, 2008 7:36AM
Great work m8, as always!

uncleramiJan 13, 2008 11:55AM
It's great to know that the some people find my efforts useful. Thanks! :)

uncleramiOct 11, 2007 1:17PM
SQLite 3.5.1 has been released sometime ago and I'm working on an update for mIRC SQLite. It's taking a bit longer than usually because of the new virtual filesystem that SQLite 3.5 uses, which requires me to rewrite some stuff (and I don't have very much time due to being in army). But expect an update shortly.

tontitoAug 9, 2007 12:17PM
Hi there,

With this latest version it seems that some sort of memory leak was added.
Aldo i can't see any used memory in mirc itself, not even selecting other memory parameters in task manager, i find that my available physical memory decreases a lot and mirc becomes much more slow.
If i close mirc i can recover up to 400MB of memory.
Something that may be influencing this is my 460MB database file... and no, the database isn't in memory.

Anyone else can confirm this?
I rollback to v1.0.13 and all seems normal.

Thanks for your time.

uncleramiAug 10, 2007 1:44PM
Hey,

I haven't added any new features in the mSQLite that could cause that, so it might be a bug in the SQLite library. If someone can confirm this, I could try to debug and report it though.

tontitoAug 10, 2007 6:33PM
Hi again,

I ran some tests by replacing some parts (mrc file and dll).

With 1.0.15 mrc file and dll i notice the leak
With 1.0.15 mrc file and 1.0.13 dll i notice the leak
With all 1.0.13 i don't notice it.

My be a different call type made in the mrc file?

uncleramiAug 11, 2007 5:51AM
I highly doubt that, but I'll look into it. There's only one little change in the mrc file, and that change only makes it possible for the script to send one more parameter when creating a custom aggregate. Unless you're using custom aggregates, the mrc file should be pretty much identical with 1.0.13 and 1.0.15.

tontitoAug 11, 2007 6:04PM
Hi,

No o am not using that, i am just executing queries.

I have noticed that selects don't increase the used memory

tontitoAug 12, 2007 6:02PM
Hi,

i have noticed that version 1.0.13 also has something similar.

after i unload the i managed to free 100MB of memory in the available physical memory section in task manager.

The current size of the Db is 57MB.

Kind regards and thanks for your feedback

uncleramiAug 19, 2007 1:56AM
I can't seem to reproduce the bug. What version of mIRC are you using? Does upgrading to 6.3 help? If you still experience the bug, e-mail the script to me, so I can debug it (my e-mail is in the help file).

tontitoSep 1, 2007 6:47AM
Did my email helped you trace the source of the problem m8?

cheers

uncleramiSep 2, 2007 2:08AM
I haven't received an email from you. It's very possible that my email client filtered it as spam, if you didn't tag the subject with "sqlite". Try to resend it.

tontitoSep 26, 2007 1:25PM
Hi again,

lets wait for next mirc version.

http://trout.snt.utwente.nl/ubbthreads/ubbthreads.php?ubb=showflat&Number=186361&page=1#Post186361

Thanks
:)

uncleramiSep 27, 2007 12:59PM
Ah, good thing that the bug was found. :)

snkAug 21, 2007 9:52AM
Rating:     8Hey,

First I want to say that this is really a nice addon.
I was wondering, can I select 2 columns from the DB at the same time?

I've tried the following, but i only get the first value:

var %p_time $sqlite_result(%request, PTime).name
var %p_date $sqlite_result(%request, PDate).name

Or is there a way to have both date and time in the same column in the DB?

uncleramiAug 25, 2007 1:34PM
If you select them both, you can also fetch them both:

var %request = SELECT PTime, PDate FROM sometable
var %p_time = $sqlite_result(%request, PTime).name
var %p_date = $sqlite_result(%request, PDate).name

You might want to fetch the fields to a hash table with sqlite_fetch_row though, as it's a bit more pretty way of doing things.

snkAug 29, 2007 1:42PM
I've tried to fetch them both (via the sql), but only the first variable is containing data.
Maybe i should try the hashtable-thing :)

Anyway, is there any support for .db3 format?

uncleramiSep 1, 2007 5:37AM
Paste the whole snippet of code that isn't working and I'll take a look at it.

And yes, .db3 format is supported. The .db3 is just an extension, it doesn't matter if it's .sql, .db, .db3, etc. All SQLite3 databases are supported.

SpraksterJul 30, 2007 3:47AM
Rating:     9Great job with this. I didn't even know of sqlite's existence before trying this. It adds a lot to mirc's abilities.

However, with v1.0.14/3.4.1 it causes my mirc to freeze when opening a connection, whether it's a new or existing file. For the info: win98 (still), the command-line 3.4.1 sqlite3.exe opens databases fine, and reverting to the previous 1.0.13/3.4.0 dll version stopped the freezing.

uncleramiJul 30, 2007 5:05AM
Yeah, I received some reports from other people as well. Apparently this is a problem caused by some changes in the FTS2 module. I'm working on a fix right now, and I'll probably release it today, or at very last the next weekend (since I won't be here during the weekdays).

EDIT. Unfortunately mircscripts.org doesn't let me submit an update for a week, but you can download the fixed version here: http://www.whahay.net/ramirez/msqlite.zip

Text edited by author on Jul 30, 2007 @ 6:11AM


tontitoJul 30, 2007 7:38AM
5 stars :)

SpraksterJul 30, 2007 8:49AM
It's fixed. Thank you :)

uncleramiJun 26, 2007 2:52AM
Btw I thought I'd mention that in a couple of weeks I'm leaving for military service, so I won't have much time to work on this. I'll still update the SQLite to a new version everytime one gets released and I'm home, but in case you think this addon is missing some nice feature you'd want or something now's the time to say it (I'll probably release a new version couple of days before I leave).

HAPMMay 27, 2007 4:22PM
Rating:     10Very nice,

ilove to work with SQL. It allows very complex data structures and fast access. I will use this dll for our current script project. There is only one little thing what would nice to be changed. Can you use an alias as a constants for the dll path? For example:

alias SQLITE_DLL return $qt($+($scriptdir,msqlite.dll);

That would help me much, because i don't have to replace the path at any dll call in the next release.

Good job!!!!!

uncleramiMay 28, 2007 5:43PM
Thanks. :)

I'll do what you suggested for the next version.

tontitoApr 27, 2007 6:21PM
What can i say...

I like to be updated, good job :)

uncleramiApr 28, 2007 3:01PM
Hehe. In case you're wondering about the frequency of the updates, it's because the authors of SQLite like to release versions every 7 days sometimes (usually they were released every 2 months or so). I've decided to wait for a week or two from now on, before releasing a new mIRC SQLite version after they release a new version, in case they're going to release an urgent bug fix version few days later. I'd make it possible for the users to update SQLite themselves without having to touch mSQLite, but I don't want to for few reasons.

Text edited by author on Apr 28, 2007 @ 6:34PM


tontitoApr 28, 2007 3:12PM
Hi,

I think it is great the way it is.

:)

GSFFeb 5, 2007 1:39PM
Rating:     10i just wanted to say thank you a long time now, im using this dll with geoip country database so i can check any time where a user is from just with a custom identifier $mygeoip(ip) (i was thinking about posting the script here but it should be more fun for everyone to write it themselves), and with a specific protection that i have implemented for my network, in a case of a proxy flood, it only allows connections from Greece,or Cyprus.. everything else gets z-lined.. for a while at least:) so it really helps :))

uncleramiFeb 7, 2007 8:58AM
Glad to hear that. :)

Did you construct the sqlite geoip database from some other sort of database yourself, or is there a premade sqlite database available somewhere?

GSFFeb 7, 2007 7:58PM
the database is available at www.maxmind.com in CSV type... so i just used tksqlite (found while googling) to convert it to a sqlite database... and it worked preety flawlesly :) its size is about 4,5mb.. at first i was using the database without having it loaded to memory and it used about 17% cpu... loaded in memory it only requires about 3% which makes it preety light... i also used the geoip city database which is not really accurate but there was a large cpu usage with that because of the enormous size....and there wasnt any point actually in that:) im now also working on a network/server statistic script.. mostly to see how the random dns of my network is working...
p.s sorry for bad english :D

Text edited by author on Feb 7, 2007 @ 8:02PM


uncleramiFeb 8, 2007 9:11AM
17% of CPU? It shouldn't really use any CPU at all when it's not being used, or is it constantly being used?

GSFFeb 8, 2007 10:17AM
no i meant only when its being used... but normally there are lots of connections on the network... so it could be a problem opening the database every 5-8 seconds... but as i said, loaded in memory... its perfect :)

uncleramiFeb 8, 2007 4:50PM
You could keep the database open at all times, even if it's only queried once in a while. I just made my own implementation of the geoip database, and it seems to run pretty fast.

DaedelusFeb 15, 2007 9:02AM
Any chance of getting a copy of that script?

uncleramiFeb 15, 2007 1:52PM
I can give you the one I made, it is probably almost identical to GSF's instead it uses file db instead of memory db.
You need this db: http://www.whahay.net/ramirez/geoip.db
The script [ usage: $geoip.country(ip) ]:

alias geoip.country {
  ; Assign the ip to a variable
  var %ip = $1

  ; Make sure we have a valid ip, if not return $null
  if (!$regex(%ip, /\d+\.\d+\.\d+\.\d+/)) {
    return
  }

  ; Make sure DB is open, if it isn't open it
  if (!$sqlite_is_valid_conn(%geoip_db)) {
    set %geoip_db $sqlite_open(geoip.db)
    if (!%geoip_db) {
      echo 4 -a Error: Couldn't open database ( $+ %sqlite_errstr $+ )
      return
    }
  }

  ; Find the country
  var %sql = SELECT name FROM ip_to_country WHERE $longip(%ip) BETWEEN begin_num AND end_num
  var %res = $sqlite_query(%geoip_db, %sql)
  if (!%res) {
    echo 4 -a Error: Query failed ( $+ %sqlite_errstr $+ )
    return
  }

  ; Check if there was a result
  if ($sqlite_num_rows(%res) > 0) {
    var %country = $sqlite_fetch_single(%res)
  }
  else {
    var %country = $null
  }

  ; Free result
  sqlite_free %res

  ; Return country ($null if one wasn't found)
  return %country
}


GSFFeb 16, 2007 2:13AM
yes, you should use this one... its well written :) its almost identical of course except i didn't realize $longip was what we are looking for.. so i did the alias myself as described at maxminds website.. (lol) .... isn't there a better regexp for ip check??
im now working on a chatterbot which is going to work using large greeklish sqlite databases (a combination of greek and english used in IRC) .. so once again thanks for the updates :)

uncleramiFeb 16, 2007 6:28AM
Yeah you could use a better regexp too. If you want something that also checks that the numbers are on range, this would do:

var %r = /[1-2]?\d{1,2}\.[1-2]?\d{1,2}\.[1-2]?\d{1,2}\.[1-2]?\d{1,2}/
if ($regex(%ip, %r)) {

But I just did a simple check to ensure that the ip is in a dotted, numeric format. If the ranges are invalid $longip just returns 0, and it's ok to pass 0 the query, which is why I used that simple regexp.

tontitoDec 5, 2006 10:17AM
Rating:     10Hi unclerami,

Good job with this dll :)