Difference between revisions of "User:Jtraulle/DiscourseMigration"
m |
Tag: 2017 source edit |
||
| Line 328: | Line 328: | ||
A step by step guide is also available [https://meta.discourse.org/t/importing-from-kunena-3/43776?u=jtraulle on the Discourse forum]. | A step by step guide is also available [https://meta.discourse.org/t/importing-from-kunena-3/43776?u=jtraulle on the Discourse forum]. | ||
| − | + | Old Kunena releases (2.x and 3.x) can be downloaded from the [https://joomlacode.org/gf/project/kunena/frs/?action=index&br_pkgrlssort_by=package_name&br_pkgrlssort_order=asc JoomlaCode forge]. | |
| − | |||
| − | + | ===Customized SQL query to get all Kunena users=== | |
| + | |||
| + | Some old Kunena user that does not have a Joomla! account was not retrieved by the import script. | ||
| + | |||
| + | This SQL query use UNION to get the missing users directly from the <code>kunena_messages</code> table. | ||
| + | |||
| + | |||
| + | For use in https://github.com/discourse/discourse/blob/master/script/import_scripts/kunena3.rb script. | ||
| + | |||
| + | <source lang="SQL"> | ||
| + | --- Main Joomla! users table (original query in script) | ||
| + | SELECT id, username, email, registerDate FROM gvrsi_users | ||
| + | UNION | ||
| + | --- Query to fetch users not present in main Joomla! table that have an email address defined | ||
| + | --- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00) | ||
| + | SELECT DISTINCT | ||
| + | userid AS id, | ||
| + | name AS username, | ||
| + | email, | ||
| + | STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate | ||
| + | FROM gvrsi_kunena_messages | ||
| + | WHERE userid NOT IN(SELECT id FROM gvrsi_users) | ||
| + | AND email IS NOT NULL AND email <> '' | ||
| + | GROUP BY id, username | ||
| + | UNION | ||
| + | --- Query to fetch users not present in main Joomla! table that does not have an email address defined | ||
| + | --- Email address is set to no_email to allow Discourse account creation | ||
| + | --- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00) | ||
| + | SELECT DISTINCT | ||
| + | userid AS id, | ||
| + | name AS username, | ||
| + | 'no_email' AS email, | ||
| + | STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate | ||
| + | FROM gvrsi_kunena_messages | ||
| + | WHERE userid NOT IN(SELECT id FROM gvrsi_users) | ||
| + | AND (email IS NULL OR email = '') | ||
| + | AND userid NOT IN( | ||
| + | SELECT DISTINCT userid AS id | ||
| + | FROM gvrsi_kunena_messages | ||
| + | WHERE userid NOT IN(SELECT id FROM gvrsi_users) | ||
| + | AND email IS NOT NULL AND email <> '' | ||
| + | GROUP BY id | ||
| + | ) | ||
| + | GROUP BY id, username | ||
| + | ORDER BY id; | ||
| + | </source> | ||
| + | |||
| + | |||
| + | ===PostgreSQL query to fix quote blocks=== | ||
| + | |||
| + | After migrating, some quote blocks does not show properly. | ||
| + | |||
| + | That is because we must have a blank line after the [quote] BBCode tag. | ||
| + | |||
| + | <source lang="bash"> | ||
| + | ./launcher enter app | ||
| + | sudo -u postgres psql discourse | ||
| + | </source> | ||
| + | <source lang="SQL"> | ||
| + | UPDATE posts | ||
| + | SET raw = regexp_replace(posts.raw, '\[(/)?quote([^]]*)]', E'\n[\\1quote\\2]\n', 'g') | ||
| + | FROM posts_with_bbcode_quotes | ||
| + | WHERE posts.id IN (posts_with_bbcode_quotes.targeted_posts) | ||
| + | RETURNING raw; | ||
| + | |||
| + | UPDATE posts set raw = regexp_replace(raw, '(\[quote=".*").*(])', '\1\2', 'g')'; | ||
| + | </source> | ||
| + | |||
| + | We use the <code>rebake</code> command to rebake HTML formatted texts from '''raw''' column into '''cooked''' column. | ||
| + | |||
| + | <source lang="bash"> | ||
| + | rake posts:rebake | ||
| + | </source> | ||
| + | |||
| + | |||
| + | ===PostgreSQL query to fix custom smileys=== | ||
| + | ''To come soon'' | ||
==Tentative design== | ==Tentative design== | ||
Revision as of 20:49, 2 August 2019
This page gathers info about Discourse migration.
Proposed skeleton
Each newly created thread MUST be placed into a category.
Categories in bold are main categories.
Categories preceded of a bullet point are sub categories belonging to a main category.
| CollapseCategories proposal |
|---|
| Annonces & actualités (verrouillé)
Vous trouverez dans cette catégorie des annonces et actualités en lien avec le projet Dolibarr : nouvelles versions, évènements, mise à jour des outils communautaire du projet, etc. |
| Installer mon Dolibarr
Obtenir de l’aide sur l’installation de Dolibarr quelque soit votre système (GNU/Linux, macOS, Windows, système NAS), votre méthode d’installation (depuis les sources, DoliWamp, DoliDeb, DoliRpm) et votre environnement (serveur local, mutualisé, dédié, cloud). |
| Utiliser mon Dolibarr
Obtenir de l’aide sur l’utilisation des modules fournis avec Dolibarr, suggérer des nouvelles fonctionnalités ou signaler des anomalies.
|
| Maintenir mon Dolibarr
Obtenir de l’aide sur la mise à jour de Dolibarr vers une version plus récente, la migration d’une instance Dolibarr vers un nouveau serveur, les opérations de sauvegarde et de restauration (fichiers et base de données) et plus généralement, tout ce qui touche à la configuration et à la maintenance système de la machine hébergeant votre instance Dolibarr (cron, permissions, etc.). |
| Développer pour Dolibarr
Obtenir de l’aide concernant le développement de modules spécifiques ou thèmes pour Dolibarr mais également sur la façon d’utiliser les modules API REST et SOAP pour interconnecter Dolibarr avec une application externe. |
| Discuter entre Dolibarriens
Cet espace de discussion vous permet d’échanger avec les autres utilisateurs de Dolibarr sur des sujets sans lien avec Dolibarr. |
Each newly created thread can also have tags regardless of its category.
| Tags type | Tags proposal |
|---|---|
| Type of
thread |
Question |
| Suggestion | |
| Anomalie | |
| Code branch
or Version |
v10.x |
| v9.x | |
| v8.x | |
| v7.x | |
| v6.x | |
| v5.x | |
| v4.x | |
| v3.x |
An user will also have the ability to mark an answer of the thread as the solution.
This explains why there is no Résolu tag in the proposal.
| Current categories or sub-categories | New proposed categories and sub-categories |
|---|---|
| Annonces et fonctionnement du forum | Annonces & actualités (verrouillé) |
| Installation | Installer mon Dolibarr |
| Installation sous Mac | |
| Installation sous Gnu/Linux | |
| Installation sous Windows | |
| Installation Qnap Synology Divers | |
| Installation Hebergeurs | |
| Mise à Jour | Maintenir mon Dolibarr |
| Howto / Aide | Utiliser mon Dolibarr |
| Bugs sur la version CVS ou demo | |
| Bugs sur la version stable courante | |
| Bugs avec PostgreSQL | |
| Bugs sur la version ARCHIVE | |
| Bugs Dolibarr V6 | |
| Bugs Dolibarr V7 | |
| Bugs Dolibarr V3 | |
| Bugs Dolibarr V4 | |
| Bugs Dolibarr V5 | |
| Retours d'expériences utilisateurs/intégrateurs | Retours d'expérience |
| Suggestions/Nouvelles fonctionnalités | Utiliser mon Dolibarr |
| Spécifiques à un Pays | |
| France | |
| Belgique | |
| Canada | |
| Suisse | |
| Autre | |
| Le module que j'ai téléchargé et deployé n'apparait pas | Modules externes du DoliStore |
| Création d'un nouveau module | Développer pour Dolibarr |
| Hooks/Triggers/Classes Objets | |
| API | |
| Git & Github | |
| Adhérents/Association | Modules GRC/GRF |
| E-Commerce | Modules externes du DoliStore |
| Agenda | Autres modules natifs |
| WebMails | Modules externes du DoliStore |
| Autres modules | |
| Modules InfraS | |
| Modules ATM | |
| Module gestion de projets | Autres modules natifs |
| Extrafields | |
| Modeles ODT | |
| Modules Patas Monkeys | Modules externes du DoliStore |
| Gestion des Ressources Humaines (GRH) | Modules GRH |
| Module utilisateurs | |
| Demandes de congés | |
| Notes de frais | |
| GRH | |
| Protection des Données | |
| Gestion de la relation client (GRC) | Modules GRC/GRF |
| Module Tiers | |
| Modules Contrats/Abonnements | |
| Module Interventions | |
| Propositions commerciales | |
| Expéditions | |
| Contrats/Abonnements | |
| Gestionnaire de tickets(Expérimental) | |
| Commandes clients | |
| Gestion de la relation fournisseur (GRF) | |
| Fournisseurs | |
| Propositions commerciales fournisseurs | |
| Incoterm | |
| Modules financiers (Compta/trésorerie) | Modules financiers |
| Comptabilité /Comptabilité (avancée) | |
| Prélèvements | |
| Banques et caisses | |
| Dons | |
| Règlement salaires | |
| Module Marges | |
| Factures et avoirs clients/ factures fournisseurs | |
| Gestion des emprunts | |
| Gestion des Produits/Services (PM) | Modules PM |
| Variantes de produits | |
| Numéros de Lot/Série | |
| Stock | |
| Gestion des services | |
| Gestion des produits | |
| Gestion électronique de documents (GED) | Autres modules natifs |
| Modules Themes | Utiliser mon Dolibarr |
| Theme amarok | |
| Theme Eldy | |
| Outils multi-modules | Autres modules natifs |
| Libellés/Catégories | |
| Éditeur WYSIWYG | |
| Mass Emailing | |
| Flux RSS | |
| Exports / Imports de données | Utiliser mon Dolibarr |
| Module Multidevise | Autres modules natifs |
| Codes-barres | |
| Sites web et autres applications frontales | Utiliser mon Dolibarr |
| Touch Screen POS | Modules financiers |
| Module Sondage ou Vote | Autres modules natifs |
| Module Caisse | Modules financiers |
| Système | Maintenir mon Dolibarr |
| Travaux planifiés | |
| Interfaces avec des systèmes externes | Développer pour Dolibarr |
| Paypal | Modules financiers |
| Impressions Directe | Autres modules natifs |
| Projets/Travail collaboratif | |
| Module Ressources | |
| Projets/Opportunités/Affaires |
Migration path
Migrating from Kunena to Discourse can be handled using the Discourse official import script.
A step by step guide is also available on the Discourse forum.
Old Kunena releases (2.x and 3.x) can be downloaded from the JoomlaCode forge.
Customized SQL query to get all Kunena users
Some old Kunena user that does not have a Joomla! account was not retrieved by the import script.
This SQL query use UNION to get the missing users directly from the kunena_messages table.
For use in https://github.com/discourse/discourse/blob/master/script/import_scripts/kunena3.rb script.
--- Main Joomla! users table (original query in script)
SELECT id, username, email, registerDate FROM gvrsi_users
UNION
--- Query to fetch users not present in main Joomla! table that have an email address defined
--- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00)
SELECT DISTINCT
userid AS id,
name AS username,
email,
STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate
FROM gvrsi_kunena_messages
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
AND email IS NOT NULL AND email <> ''
GROUP BY id, username
UNION
--- Query to fetch users not present in main Joomla! table that does not have an email address defined
--- Email address is set to no_email to allow Discourse account creation
--- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00)
SELECT DISTINCT
userid AS id,
name AS username,
'no_email' AS email,
STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate
FROM gvrsi_kunena_messages
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
AND (email IS NULL OR email = '')
AND userid NOT IN(
SELECT DISTINCT userid AS id
FROM gvrsi_kunena_messages
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
AND email IS NOT NULL AND email <> ''
GROUP BY id
)
GROUP BY id, username
ORDER BY id;
PostgreSQL query to fix quote blocks
After migrating, some quote blocks does not show properly.
That is because we must have a blank line after the [quote] BBCode tag.
./launcher enter app
sudo -u postgres psql discourse
UPDATE posts
SET raw = regexp_replace(posts.raw, '\[(/)?quote([^]]*)]', E'\n[\\1quote\\2]\n', 'g')
FROM posts_with_bbcode_quotes
WHERE posts.id IN (posts_with_bbcode_quotes.targeted_posts)
RETURNING raw;
UPDATE posts set raw = regexp_replace(raw, '(\[quote=".*").*(])', '\1\2', 'g')';
We use the rebake command to rebake HTML formatted texts from raw column into cooked column.
rake posts:rebake
PostgreSQL query to fix custom smileys
To come soon
Tentative design
Custom logos
Go to Admin → Customize → Settings → Branding
- Based on Dolibarr main color #3D5F93
- Blippo font
Main forum logo
.png)
Logo to use in email notifications and mobile view
.png)
Custom color palette
Go to Admin → Customize → Colors
- tertiary : #004488
- quaternary : #3D5F93
- danger : #B43314
Custom layout
Discourse allow to easily include a custom header and footer ; as well as custom styles.
This is a tentative custom Layout
Tentative design desktop (proposal)
Tentative design mobile view (proposal)
Custom Header to add
Go to Admin → Customize → Themes → Light → Edit CSS/HTML → Common → Header and paste :
| Expand |
|---|
Go to Admin → Customize → Themes → Light → Edit CSS/HTML → Common → Footer and paste :
<div id="footer">
<span itemprop="name">Dolibarr ERP & CRM</span> © 2003-2017 - Gestion d'entreprise ou d'association
</div>
Custom CSS to add
Go to Admin → Customize → Themes → Light → Edit CSS/HTML → Common → CSS and paste :
| Expand |
|---|
Requirements
Hardware requirements
To the extent possible, a VPS machine that will be dedicated to running the Discourse forum software only is the way to go.
- modern single core CPU, dual core recommended
- 1 GB RAM minimum (with swap)
- 64 bit Linux compatible with Docker (I recommand 18.04 LTS Ubuntu Server to be aligned with the wiki.dolibarr.org OS).
- 10 GB disk space minimum (SSD is a plus for the speed but not a requirement)
Refer to Hardware requirement (github.com)
Domain requirements
Discourse requires a FQDN (Fully Qualified Domain Name) : for example forum.dolibarr.fr and cannot be installed in a subdirectory.
Email requirements
Discourse requires connection to a valid mail server :
- using SMTP protocol
- accessible on a FQDN (Fully Qualified Domain Name) : for example
forum.dolibarr.fr - with authentication (username and password)
| In house mail server like Postfix | External email service like Mailgun |
|---|---|
|
|
|
Tips and tricks
Docker tips and tricks
The main and recommended way to Install Discourse is using Docker containers system (it contains everything required to run Discourse : Ruby, Sidekiq, Redis, PostgreSQL with the appropriate version and does not mess with the host data).
The quick start guide is great for installation.
Data that should be preserved when the containers die (PostgreSQL database data files, users uploads, etc.), are persisted on the host.
The default mount point for all persisted data related to Discourse is through
/var/discourse/shared/standalone
PostgreSQL tips and tricks
Allow PostgreSQL to be accessed from an external application
Useful to be able to access the database from a graphical client (like pgAdmin 4, TablePlus or Postico for example).
1. Expose the 5432 TCP port from the container to the host by adding this line to the expose: section of containers/app.yml :
- "5432:5432"
2. Rebuild the container to take into account modifications to app.yml :
./launcher rebuild app
3. Enter into the container, login as the default postgresql user, add a new user and grant it all perms to the database
./launcher enter app
sudo -u postgres psql discourse
CREATE USER my_username WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_username;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO my_username;
GRANT ALL PRIVILEGES ON DATABASE discourse to my_username;
\q
Refer to PostgreSQL - How to grant access to users? for more info.
Dumping database
Using pg_dump to dump the database
Below syntax assume that a custom pgsql account has been created prior to running the command and that port 5432 of PostgreSQL is correctly forwarded from the container to the host (following #Allow PostgreSQL to be accessed from an external application section instructions).
The below command is executed from the host.
pg_dump -h 127.0.0.1 -d discourse -U my_username -W > discourse-backup-20190801.sql
Using discourse internal backup tool
It is also possible to use Discourse internal backup tool to grab a backup (database + users uploads) of the Discourse instance.
Just go to Admin → Backup