Address casemapping issues in sqlite/mysql

3 files changed, 57 insertions, 1 deletions

diff --git a/docs/CMakeLists.txt b/docs/CMakeLists.txt
index fd93e81c0..7e135d207 100644
--- a/docs/CMakeLists.txt
+++ b/docs/CMakeLists.txt
@@ -7,7 +7,7 @@ if(WIN32)
     # Add README.txt to list of files for CPack to ignore
     add_to_cpack_ignored_files("README.txt$" TRUE)
   endif()
-  set(DOCS Changes Changes.conf DEFCON FAQ INSTALL LANGUAGE MODULES NEWS ${CMAKE_CURRENT_BINARY_DIR}/README.txt WIN32.txt)
+  set(DOCS Changes Changes.conf DEFCON FAQ INSTALL LANGUAGE MODULES MySQL.md NEWS ${CMAKE_CURRENT_BINARY_DIR}/README.txt SQLite.md WIN32.txt)
   install(FILES ${DOCS}
     DESTINATION ${DOC_DIR}
   )
diff --git a/docs/MySQL.md b/docs/MySQL.md
new file mode 100644
index 000000000..c463c1f00
--- /dev/null
+++ b/docs/MySQL.md
@@ -0,0 +1,24 @@
+# How to install rfc1459 character set into MySQL:
+
+Copy data/mysql/rfc1459.xml to your character_sets_dir, identified by
+
+```
+SHOW VARIABLES LIKE 'character_sets_dir';
+```
+
+Add the following charset to Index.xml in the character_sets_dir directory:
+
+```
+<charset name="rfc1459">
+  <collation name="rfc1459_ci" id="800"/>
+  <collation name="rfc1459_inspircd_ci" id="801" flag="primary"/>
+</charset>
+```
+
+Restart MySQL.
+
+Now create the database using the appropriate character set:
+
+```
+CREATE DATABASE anope DEFAULT CHARACTER SET rfc1459 DEFAULT COLLATE rfc1459_inspircd_ci;
+```
diff --git a/docs/SQLite.md b/docs/SQLite.md
new file mode 100644
index 000000000..2d5b1c7b8
--- /dev/null
+++ b/docs/SQLite.md
@@ -0,0 +1,32 @@
+# How to modify Anope sqlite databases
+
+Anope uses a custom SQLiite function (anope_canonicalize) for canonicalizing strings for case insensitivity comparisons.
+It does this by using SQLites support for having indexes on expressions https://www.sqlite.org/expridx.html.
+
+For example the account table could look like:
+
+```
+CREATE TABLE `anope_db_account` (
+	`display`,
+	`pass`,
+	`email`,
+	`language`,
+	`id` NOT NULL PRIMARY KEY,
+	FOREIGN KEY (id) REFERENCES anope_db_objects(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+);
+CREATE INDEX idx_display ON `anope_db_account` (anope_canonicalize(display));
+```
+
+So, to do a SELECT which utilizes the indicies, Anope does something like:
+
+```
+SELECT id FROM `anope_db_account` WHERE anope_canonicalize(display) = anope_canonicalize('Adam');
+```
+
+If you are using your own SQLite instance, like the sqlite command line interface, the anope_canonicalize function
+will be missing. To fix this load one of libanope_sqlite_ascii.so, libanope_sqlite_rfc1459_inspircd.so,
+or libanope_sqlite_rfc1459.so into SQLite, depending on your casemap configuration.
+
+```
+sqlite> .load lib/libanope_sqlite_ascii.so
+```