Difference between revisions of "Store filenames with emoticons"

From LogicalDOC Community Wiki
Jump to navigationJump to search
 
(28 intermediate revisions by the same user not shown)
Line 1: Line 1:
You must have a MySQL 8/MariaDB database with utf8md4 settings
+
You must have a MySQL 5.7-8/MariaDB database with utf8md4 settings
  
 
Since this is not a risk-free operation it is necessary to back up the database before proceeding.
 
Since this is not a risk-free operation it is necessary to back up the database before proceeding.
  
 +
=== Update the configuration of LogicalDOC ===
 +
First you have to add a couple of parameters to the jdbc connection url of LogicalDOC
 +
# Shutdown LogicalDOC system service/daemon
 +
# Locate the file '''context.properties''' in '''<LOGICALDOC_INSTALLATION_DIR>/conf'''
 +
# Edit the file context.properties by adding a couple of parameters to the value of key jdbc.url
 +
# Locate the key '''jdbc.url''' and add the parameters '''characterEncoding=utf8&allowPublicKeyRetrieval=true''' <br/><pre>e.g.: jdbc.url=jdbc:mysql://localhost:3306/logicaldoc?useSSL=false&characterEncoding=utf8&allowPublicKeyRetrieval=true</pre>
 +
# Save the file and restart LogicalDOC system service/daemon
 +
 +
=== Update the database schema ===
 
Connect to the database using mysql client
 
Connect to the database using mysql client
 
<pre>
 
<pre>
Line 8: Line 17:
 
</pre>
 
</pre>
  
Check the status of the charset and collation settings are OK with the query below
+
Check the status of the charset settings are OK with the query below
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
  SHOW VARIABLES LIKE 'char%';
 
  SHOW VARIABLES LIKE 'char%';
 
</syntaxhighlight>
 
</syntaxhighlight>
  
[[File:Mysql8-charset-for-emoticons.png|frame|left|caption]]
+
[[File:Mysql8-charset-for-emoticons.png|360px|frame|center|MySQL queries to check charset and collation to store emoticons in LogicalDOC filenames]]
<br/>
+
 
[[File:Mysql8-charset-for-emoticons.png|345px|none|ddddd]]
+
 
[[File:Mysql8-charset-for-emoticons.png|400px|frameless|center]]
+
Here you should '''check that character_set_server is set to utf8mb4'''<br>
 +
If it is not set to utf8mb4 you must backup the database schema of logicaldoc and change that<br>
 +
For more information [https://stackoverflow.com/questions/44591895/utf8mb4-in-mysql-workbench-and-jdbc utf8mb4 in MySQL Workbench and JDBC]
  
 
Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history
 
Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history
Line 33: Line 44:
 
     CHARACTER SET utf8mb4
 
     CHARACTER SET utf8mb4
 
     COLLATE utf8mb4_unicode_ci;
 
     COLLATE utf8mb4_unicode_ci;
 
  
 
ALTER TABLE
 
ALTER TABLE
Line 42: Line 52:
 
     COLLATE utf8mb4_unicode_ci;
 
     COLLATE utf8mb4_unicode_ci;
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
=== Add some files with emoticons in LogicalDOC ===
 +
Now you can upload some files with emoticons from your desktop or just change the filename of a stored document adding an emoticon at the start of the File name
 +
[[File:Document-browser-files-with-emoticons.png|thumb|600px|center|The document explorer in LogicalDOC DMS 8.3.3 showing some files with emoticons]]
 +
 +
=== Additional information ===
 +
 +
* [https://makandracards.com/makandra/2529-show-and-change-mysql-default-character-set Show and change MySQL default character set]
 +
* [https://makandracards.com/makandra/2531-show-the-character-set-and-the-collation-of-your-mysql-tables Show the character set and the collation of your MySQL tables]
 +
* [https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is How do I see what character set a MySQL database / table / column is?]
 +
* [https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-mysql-database How to store Emoji Character in MySQL Database]
 +
* [https://stackoverflow.com/questions/44591895/utf8mb4-in-mysql-workbench-and-jdbc utf8mb4 in MySQL Workbench and JDBC]

Latest revision as of 08:27, 19 September 2019

You must have a MySQL 5.7-8/MariaDB database with utf8md4 settings

Since this is not a risk-free operation it is necessary to back up the database before proceeding.

Update the configuration of LogicalDOC

First you have to add a couple of parameters to the jdbc connection url of LogicalDOC

  1. Shutdown LogicalDOC system service/daemon
  2. Locate the file context.properties in <LOGICALDOC_INSTALLATION_DIR>/conf
  3. Edit the file context.properties by adding a couple of parameters to the value of key jdbc.url
  4. Locate the key jdbc.url and add the parameters characterEncoding=utf8&allowPublicKeyRetrieval=true
    e.g.: jdbc.url=jdbc:mysql://localhost:3306/logicaldoc?useSSL=false&characterEncoding=utf8&allowPublicKeyRetrieval=true
  5. Save the file and restart LogicalDOC system service/daemon

Update the database schema

Connect to the database using mysql client

mysql -u root -p logicaldoc

Check the status of the charset settings are OK with the query below

 SHOW VARIABLES LIKE 'char%';
MySQL queries to check charset and collation to store emoticons in LogicalDOC filenames


Here you should check that character_set_server is set to utf8mb4
If it is not set to utf8mb4 you must backup the database schema of logicaldoc and change that
For more information utf8mb4 in MySQL Workbench and JDBC

Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history

ALTER TABLE
    ld_document
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    ld_version
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    ld_history
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Add some files with emoticons in LogicalDOC

Now you can upload some files with emoticons from your desktop or just change the filename of a stored document adding an emoticon at the start of the File name

The document explorer in LogicalDOC DMS 8.3.3 showing some files with emoticons

Additional information