Changeset 32745 for other-projects/the-macronizer/trunk/src/sql-scripts

Timestamp:

2019-02-05T23:03:16+13:00 (5 years ago)

Author:

ak19

Message:

More Western Wilson stuff. 1. Major changes to fix handling of utf8 stuff in db so uniqueness actually works: so finding (selecting) exact matches works and insert unique violations don't happen from code. Inserting is now made lowercase since only macrons matter and case doesn't. 2. The SQL db's MarkedWords table needs to specify the uniqueness of its utf8 marked_word column differently for the utf8-ness to work.

File:

• other-projects/the-macronizer/trunk/src/sql-scripts/MySQL_Code.sql (modified) (1 diff)

other-projects/the-macronizer/trunk/src/sql-scripts/MySQL_Code.sql

@@ -6 +6 @@
 
 
+/*
+Unique constraint on marked_word field needs to be specified specially to deal with utf8
+e.g. to distinguish between āno and anō, rather than treating them both the same breaking uniqueness.
+https://stackoverflow.com/questions/25318479/unique-constraint-violation-with-utf-8-values
+*/
 CREATE TABLE IF NOT EXISTS MarkedWords (
     word_id INT AUTO_INCREMENT,
-    marked_word varchar (255) UNIQUE,
+    marked_word varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE,
     PRIMARY KEY (word_id)
     );