Files
@ 7b0aafc6b7ca
Branch filter:
Location: kallithea/scripts/docs-headings.py - annotation
7b0aafc6b7ca
2.6 KiB
text/x-python
mysql: create database with explicit UTF-8 character set and collation
A spin-off from Issue #378.
In MySQL, the character sets for server, database, tables, and connection are
set independently. Ideally, they should all use UTF-8, but systems tend to use
latin1 as default encoding, for example:
character_set_server = latin1
collation_server = latin1_swedish_ci
Databases would thus by default be created as:
character_set_database = latin1
collation_database = latin1_swedish_ci
To make things work consistently anyway, we have so far specified the utf8mb4
charset explicitly when creating tables, but there is no corresponding simple
option for specifying the collation for tables. We need a better solution.
If necessary and possible, the system charset and collation should be set to
UTF-8. Some systems already have these defaults default - see
https://mariadb.com/kb/en/differences-in-mariadb-in-debian-and-ubuntu/ .
The defaults can be changed as described on
https://mariadb.com/kb/en/setting-character-sets-and-collations/#example-changing-the-default-character-set-to-utf-8
to give something like:
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
Databases will then by default be created as:
character_set_database = utf8mb4
collation_database = utf8mb4_unicode_ci
and there is thus no longer any need for specifying the charset when creating
tables.
To be reasonably resilient across all systems without relying on system
defaults, we will now start specifying the charset and collation when creating
the database, but drop the specification of charset when creating tables.
For existing databases, it is recommended to change encoding (and collation) by
altering the database and each of the tables inside it as described on
https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 .
Note the use of utf8mb4_unicode_ci instead of utf8mb4_general_ci - see
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci .
For investigation of these issues, consider the output from:
show variables like '%char%';
show variables like '%collation%';
show create database `KALLITHEA_DB_NAME`;
SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "KALLITHEA_DB_NAME";
SELECT * FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "KALLITHEA_DB_NAME";
A spin-off from Issue #378.
In MySQL, the character sets for server, database, tables, and connection are
set independently. Ideally, they should all use UTF-8, but systems tend to use
latin1 as default encoding, for example:
character_set_server = latin1
collation_server = latin1_swedish_ci
Databases would thus by default be created as:
character_set_database = latin1
collation_database = latin1_swedish_ci
To make things work consistently anyway, we have so far specified the utf8mb4
charset explicitly when creating tables, but there is no corresponding simple
option for specifying the collation for tables. We need a better solution.
If necessary and possible, the system charset and collation should be set to
UTF-8. Some systems already have these defaults default - see
https://mariadb.com/kb/en/differences-in-mariadb-in-debian-and-ubuntu/ .
The defaults can be changed as described on
https://mariadb.com/kb/en/setting-character-sets-and-collations/#example-changing-the-default-character-set-to-utf-8
to give something like:
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
Databases will then by default be created as:
character_set_database = utf8mb4
collation_database = utf8mb4_unicode_ci
and there is thus no longer any need for specifying the charset when creating
tables.
To be reasonably resilient across all systems without relying on system
defaults, we will now start specifying the charset and collation when creating
the database, but drop the specification of charset when creating tables.
For existing databases, it is recommended to change encoding (and collation) by
altering the database and each of the tables inside it as described on
https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 .
Note the use of utf8mb4_unicode_ci instead of utf8mb4_general_ci - see
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci .
For investigation of these issues, consider the output from:
show variables like '%char%';
show variables like '%collation%';
show create database `KALLITHEA_DB_NAME`;
SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "KALLITHEA_DB_NAME";
SELECT * FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "KALLITHEA_DB_NAME";
aa6f17a53b49 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 0a277465fddf f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 a188803df37e a188803df37e 01aca0a4f876 a8e6bb9ee9ea 665dfa112f2c f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 a8e6bb9ee9ea f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 a8e6bb9ee9ea f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 ed2fb6e84a02 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 665dfa112f2c a188803df37e a8e6bb9ee9ea f38b50f8a6a6 f38b50f8a6a6 f38b50f8a6a6 | #!/usr/bin/env python3
"""
Consistent formatting of rst section titles
"""
import re
import subprocess
spaces = [
(0, 1), # we assume this is a over-and-underlined header
(2, 1),
(1, 1),
(1, 0),
(1, 0),
]
# http://sphinx-doc.org/rest.html :
# for the Python documentation, this convention is used which you may follow:
# # with overline, for parts
# * with overline, for chapters
# =, for sections
# -, for subsections
# ^, for subsubsections
# ", for paragraphs
pystyles = ['#', '*', '=', '-', '^', '"']
# match on a header line underlined with one of the valid characters
headermatch = re.compile(r'''\n*(.+)\n([][!"#$%&'()*+,./:;<=>?@\\^_`{|}~-])\2{2,}\n+''', flags=re.MULTILINE)
def main():
filenames = subprocess.check_output(['hg', 'loc', 'set:**.rst+kallithea/i18n/how_to']).splitlines()
for fn in filenames:
fn = fn.decode()
print('processing %s' % fn)
s = open(fn).read()
# find levels and their styles
lastpos = 0
styles = []
for markup in headermatch.findall(s):
style = markup[1]
if style in styles:
stylepos = styles.index(style)
if stylepos > lastpos + 1:
print('bad style %r with level %s - was at %s' % (style, stylepos, lastpos))
else:
stylepos = len(styles)
if stylepos > lastpos + 1:
print('bad new style %r - expected %r' % (style, styles[lastpos + 1]))
else:
styles.append(style)
lastpos = stylepos
# remove superfluous spacing (may however be restored by header spacing)
s = re.sub(r'''(\n\n)\n*''', r'\1', s, flags=re.MULTILINE)
if styles:
newstyles = pystyles[pystyles.index(styles[0]):]
def subf(m):
title, style = m.groups()
level = styles.index(style)
before, after = spaces[level]
newstyle = newstyles[level]
return '\n' * (before + 1) + title + '\n' + newstyle * len(title) + '\n' * (after + 1)
s = headermatch.sub(subf, s)
# remove superfluous spacing when headers are adjacent
s = re.sub(r'''(\n.+\n([][!"#$%&'()*+,./:;<=>?@\\^_`{|}~-])\2{2,}\n\n\n)\n*''', r'\1', s, flags=re.MULTILINE)
# fix trailing space and spacing before link sections
s = s.strip() + '\n'
s = re.sub(r'''\n+((?:\.\. _[^\n]*\n)+)$''', r'\n\n\n\1', s)
open(fn, 'w').write(s)
print(subprocess.check_output(['hg', 'diff'] + filenames))
if __name__ == '__main__':
main()
|