Frequently Asked Questions

mIRC SQLite

Frequently Asked Questions
This FAQ also contains a number of technical questions and answers that might not interest people who don't develop mIRC libraries themselves. This is why I'll try to arrange the FAQ so that technical questions come after more general questions. If your question is not answered here or in the Official SQLite FAQ, feel free to contact me with your question, I also gladly answer technical questions.
IMPORTANT. When you send me e-mail make sure to include the word "mIRC" or "SQLite" (without quotes) in the subject, otherwise my mail filter won't catch it.
Q: I don't know SQL. Where can I learn it?
A: There a bunch of tutorials and documentation for SQL and SQLite on the Internet. Just use your favorite search engine and you'll know SQL in no time! The SQLite docs for SQL can be found here: http://www.sqlite.org/lang.html
Q: Should I use single or double quotes for string literals?
A: Single quotes. Double quotes are a dangerous thing in SQLite, because if the quoted string is an identifier, it will be treated as an identifier, otherwise as a string literal. This means that SQLite might interpret a string literal as an identifier when you mean to use it as a string literal. Not only that, but if you want to use double quotes to denote an identifier, and the identifier doesn't exist it will be automatically treated as a string literal. This means that SQLite might not report an error if you, for example, make a typo in your query. It's best to avoid double quotes completely.
Q: How do I tell SQLite that something is an identifier, such as a table or a column name?
A: You can usually leave it unquoted. If however the identifier is one of the reserved keywords that must be quoted to be used, use either square brackets or backticks. The list of keywords that must be quoted in order to be used as an identifier can be seen here: http://www.sqlite.org/lang_keywords.html.
Q: How can I make LIKE case sensitive? I tried 'a' LIKE BINARY 'A' but it doesn't work.
A: You can make LIKE case sensitive with the case_sensitive_like pragma. You can set it on with the following query: PRAGMA case_sensitive_like=ON
Another option would be to use the GLOB function instead, which is always case sensitive. Please note that GLOB uses the Unix file globbing syntax for its wildcards, that is, it uses * instead of % and ? instead of _
Q: How can I make REGEXP case insensitive?
A: Use the feature to change matching options on fly. To make it use case insensitive matching, use (?i), example: 'a' REGEXP '(?i)A'
You can reset the matching to act in a case sensitive manner by doing (?-i)
Q: Why do the row indexes start from 1 and not 0?
A: The reason is consistency. I wanted this library to be as consistent with mIRC as possible. And because in mIRC pretty much everything starts from 1 instead of 0 when it comes to indexing, I decided to do the same for mSQLite.
Q: Is unicode supported?
A: Yes. mSQLite uses UTF-8 encoding for a lot of things, such as for user defined function names or regular expressions. You can still insert text that isn't encoded in UTF-8 to SQLite databases. When using REGEXP to do a regular expression match, mIRC SQLite will first check the UTF-8 validity of the pattern and the subject. If they are valid, they are treated as unicode, otherwise they are not.
Q: I want to take advantage of unicode in regular expressions, how do I ensure that my text is valid UTF-8?
A: mIRC has a few useful identifiers for dealing with UTF-8. Namely you can use $utfencode and $utfdecode to encode and decode text respectively. $isutf can be used to check for UTF-8 validity of text, it returns 0 if text isn't valid UTF-8, 1 if it is valid UTF-8 and only consists regular ASCII characters or 2 if it is valid UTF-8 and consists multibyte characters.
Q: If I open a file using relative path instead of absolute path, is the current directory mIRC directory or the script directory?
A: The mIRC directory. If you want to ensure the database is opened where the script is installed, and not in mIRC's root directory, use the $scriptdir alias, that returns absolute path to the script directory.
Q: Since every database is stored in its own file, can't I use the same connection to query data from more than one database?
A: You can! Use the ATTACH DATABASE statement for this.
Q: How can I get a list of tables in a database?
A: Every SQLite database has a special table named sqlite_master that defines the database schema. You can use the following query to get a list of all tables in a database: SELECT name FROM sqlite_master WHERE type='table'
Q: Are nested queries allowed?
A: If you mean SQL subqueries, the answer is yes. If you mean a query in an user defined function, the answer is still yes. However you need to be careful in this case, if your user defined function executes a query that calls the same user defined function, you might end up having an infinite loop. This can cause a crash.
Q: How does mSQLite find out whether the data passed to TMPRES is text or binary?
A: If the passed data contains NUL (0x0) character(s) or starts with SOH (0x1) it is considered as binary data, otherwise text.
Q: Your code is syntax highlighted, did you do all of that manually?
A: Hell no. All of the syntax highlighted code is generated automatically with a mIRC syntax highlighter script by Tye Shavik. The script is available here: http://www.mirc.net/tye/mirc_script.phps. Thanks a ton Tye!
Q: I can see that your mIRC code uses XML docs. Where can I get the tool to generate HTML docs from the XML and who made it?
A: The XML docs generator is a tool I created to make writing docs for this library less tedious. It's not available publicly as it's very unpolished, but I might release it some day.
Q: I appreciate your hard work, but there already is a SQLite wrapper DLL for mIRC. Didn't you know that?
A: As a matter of fact, I did. There are many reasons why I decided to make my own SQLite library for mIRC, even though knowing one existed already. First and foremost, the SQLiteDll wrapper is terribly outdated, mSQLite brings you the features of the latest SQLite. Second of all I wanted to make the library more versatile and feature rich. Here are some features that mSQLite adds to those of SQLiteDll: A more user friendly API which exposes more functionality to the user, SQL regular expressions with the REGEXP keyword, handling binary data, user defined functions, fetching rows to hash tables instead of using an unsafe and limited seperator mechanism and more.
Q: Where is sqlite_unbuffered query?
A: As of version 1.1.0 unbuffered queries are supported.
Q: Why isn't there a way to open a persistent connection in mSQLite, as you can do with sqlite_popen in PHP?
A: In mSQLite it's not necessary. In PHP the advantage of using a persistent connection means that when a connection is established to an already open database, the schema data needs not to be re-read. mSQLite uses a shared cache for databases and schema data for connections to the same database. This means that mSQLite implements persistent connections implicitly, and even improves the PHP's persistent connection mechanism by implementing reference counting; In other words, a call to sqlite_close on an open persistent connection doesn't close the connection to the database if there are other active connections.
Q: Why are you required to have an unique id for every instance of mIRC and mSQLite?
A: The reason for this is because mSQLite uses mIRC's SendMessage API to intercommunicate with mIRC. mIRC implements intercommunication with SendMessage by using named mapped files. If every instance of mIRC used the same mapped file, there could be problems with two instances of mIRC trying to access the same mapped file at same time. This would result into undefined behavior. To prevent such cases, one would need to implement synchronization to the mapped file, but this would have its own share of problems, one being inefficiency. While there are other ways to intercommunicate with mIRC, such as using DDE, it is much more inefficient and has its own share of limitations of use. By using an uniquely named mapped file, mSQLite can have an exclulsive access to the mapped file, which is the safest and most efficient way to intercommunicate with mIRC. This is also the reason why mSQLite wasn't released until now. I was trying to find a decent way to make mSQLite safe even when multiple instances of mIRC are running or other scripts require access to the mapped file. Then mIRC 6.2 was released and with some modifications I accomplised the goal in the most efficient way I could've hoped.
Q: I understand why you're required to have an unique id now. But why don't you just generate one instead of forcing the user to give one?
A: As of version 1.0.8 this is exactly what mSQLite does! You can still give the id manually if you wish though, by editing the configuration file.
Q: What implementation of regular expressions does mSQLite use?
A: mSQLite uses PCRE (Perl Compatible Regular Expressions) for regular expressions implementation.
Q: Can I have the source code?
A: I have no intention of releasing the source code for as long as I personally work on the project.