Stuffing the gaps in the COLLATIONS table using a stored procedure
To far fetched (pun inteded), some might think.. Below you’ll find a procedure to get a list of MySQL supported character sets and their collations. The output is Python and can be used to build a (big) tuple.
The problem is that character set IDs in MySQL have ‘gaps’. For example hebrew has ID 16, and the next character set in the COLLATIONS-table, tis620, has ID 18. Not a big problem, just a bit annoying. This list is hardcoded in MySQL Connector/Python and I needed something to easily maintain it.
Solutions were using text editing skills, parsing it in Python to produce a list, etc.. But why not a Stored Routine? The following stored procedure is looping over a cursor, calculating the number of rows the gap has, and inserts blanks as needed.
DROP PROCEDURE IF EXISTS python_getcharsets; DELIMITER // CREATE PROCEDURE python_getcharsets() BEGIN DECLARE i,diff,cid,done INT DEFAULT 0; DECLARE chname,coname VARCHAR(32); DECLARE cur CURSOR FOR SELECT ID,CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS ORDER BY ID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; loop_cur: LOOP FETCH cur INTO cid,chname,coname; IF done THEN LEAVE loop_cur; END IF; SET i = i + 1; SET diff = cid - i; WHILE diff DO SELECT "None,"; SET diff = diff - 1; SET i = i + 1; END WHILE; SELECT CONCAT('("',chname,'","',coname,'"), # ',cid); END LOOP loop_cur; CLOSE cur; END// DELIMITER ; CALL python_getcharsets(); DROP PROCEDURE IF EXISTS python_getcharsets;
You would save the above to a file called getcharsets.sql for example, and execute it like this:
shell> mysql -N test < getcharsets.sql .. ("cp1251","cp1251_bulgarian_ci"), # 14 ("latin1","latin1_danish_ci"), # 15 ("hebrew","hebrew_general_ci"), # 16 None, ("tis620","tis620_thai_ci"), # 18 ("euckr","euckr_korean_ci"), # 19 ..
I’ll spare you the complete output, but as you can see from above sample: the gap has been stuffed with a None-Python value. Taking this output, you’ll inserted it your code:
desc = ( None, ("big5","big5_chinese_ci"), # 1 ("latin2","latin2_czech_cs"), # 2 ("dec8","dec8_swedish_ci"), # 3 ("cp850","cp850_general_ci"), # 4 ..
Silly? Definitely basic stuff, but I got my code a bit faster, and cleaner!