= NOK Configuration for the Linthal 2015 Project = <> = Notes = 1. The official project name is '''PSW Limmern''' (Linthal 2015 is a ''project above'', which include PSW Limmern) 1. '''NOK''' is being renamed as '''Axpo''' 1. '''Axpo Informatic''' will host our server. They seem to be quite independent of NOK and the Linthal project 1. This page describes a configuration method for the Linthal project, which could become a model for configuring other project too. = Terminology = 1. Zust. = Approval (Zustimmung) 1. Indextyp = Document code type = Contact Information = == Contact Addresses == === Main Office === * Axpo AG - Hydroenergie, Parkstrasse 23, CH-5401 Baden * +41-56-200-4483 === Axpo Informatik === * Axpo Informatik AG * Bruggerstrasse 68, Postfach, CH-5401 Baden === Construction Site === * Tierfeld, Kanton Glarus * Main Office: 3rd floor of the '''Bauleitung''' office, a 3-level white container office complex * CH coordinates: 717 750 / 193 178 (46.879298N, 8.983394E) == Contact Names == || '''Name''' || '''Description''' || '''Email''' || '''Phone''' || || '''Erich Ermel''' || Main contact from Oct-2011 || Erich.Ermel@axpo.ch || T: 056-200-3779, M: 079-915-6241 || || '''Martin Gmür''' || Main contact ||Martin.Gmuer@axpo.ch || T: 056-200-3872, M: 076-489-0874 || || '''Michael Schlegel''' || Contact for DrawMGT operations. Leiter Planung/Dienste || Michael.Schlegel@axpo.ch || T: 056-200-4425 || || '''Norbet Wohlkinger''' || Commercial contact || Norbert.Wohlkinger@nok.ch || T: 056-933-4483, M: 079-630-3150 || || '''Martin Hase''' || Leiter Baustelle || Martin.Hase@axpo.ch || || || '''Emil Bieri''' || Electro-Mechanical Leiter || Emil.Bieri@axpo.ch || || || '''Rene Käppeli''' || Axpo Informatik || Rene.Kaeppeli@axpo.ch || T: 056-200-4538, M: 079-609-6147 || || '''Adele Grandi''' || DrawMGT user administration || Adele.Grandi@axpo.ch || T: 056-200-3709 || || '''Axpo IT Hotline''' || For Citrix login support || See also [[https://zg-2.softxs.ch/intern/intern/?state=home-1&event=cvw&depth=0&cid=2073|BUG-2703]] || T: 0800-55-05-05 || || '''Mario Robic''' || Linux/Network specialist || Mario.Robic@axpo.ch || T: 056-200-3367, M: 079-630-3558 || || '''--(Jörg Leppin)--''' || --(Unix/Linux Spezialist)-- || --(Joerg.Leppin@axpo.ch)-- || --(T: 056-200-4569, M: 079-403-9429)-- || = Documentation and Notes on System Updates for Axpo = 1. [[AxpoTaskEmailImplementation]] - Notes set to Axpo describing the task email updates requested May 2011 = Discussions and Input Data from NOK = == 2009.10.13: Email from GMM == 1. [[attachment:Bewilligungsauflagen Erfassung DrawMGT v02.xlsx]] - Example spreadsheet for '''Bewilligungsauflagen''' 1. Request for management of milestone dates, that can be assigned to tasks as due dates. This includes the ability change the dates for milestones and have them automatically change the due dates of the tasks. == 2009.10.16: AH/GMM/etc. Meeting in Baden to discuss classification system and workflows == 1. Request for feature (for document/revision security): New flag in Drawings and/or Revision record: * Public to Bereich * Public to entire system 1. Request for feature: New workflow step '''F''' Freigabe, a final approval that is performed after all the other '''Z''' (Zustimmung) approvals, and before the Submit workflow step. To be called '''Release''' in English. 1. New '''!ApprovalStatus''': '''Nicht efforderlich''' (e.g. '''N/A''' - Not Required). Rules: * '''Plan date''': optional * '''Complete date''': must be empty * '''Initials''': optional 1. Summary of German language approval steps: * Offen * Erteilt * Erteilt mit Kommentar * Abgelehnt 1. Implement new workflows: * '''DCAAAAR''' - Design-Check-App1-App2-App3-App4-Release * '''DCAAAARS''' - Design-Check-App1-App2-App3-App4-Release-Submit * '''RCAAAAR''' - Receive-Check-App1-App2-App3-App4-Release * '''RCAAAARS''' - Receive-Check-App1-App2-App3-App4-Release-Submit 1. Request for improved event history to see exactly what was changed in workflows. 1. Idea for new '''Comment''' field for each Approval and Release workflow step * Did not commit to implementing this feature * NOK agreed to use tasks/comments for recording comments associated with approvals New configuration files sent after the meeting from GMM: 1. --([[attachment:2009.10.19 DrawMGT PSW Limmern Struktur v01.xls]])-- -- Master configuration file for classifications * Worksheet Indextyp -- describe drawing code format and file names * Worksheet Attribute PSW Limmern -- list of all the classification and other reference data 1. --([[attachment:2009.10.19 DrawMGT Indextyp-Attribute v01.xlsx]])-- -- List of document code types and their components 1. [[attachment:2009.10.19 DrawMGT Doktypen-Prozesstyp v01.xlsx]] -- List of (project) document types and their corresponding document code types and workflow types. Note that we will implement the project document type as a classification field, and separate from the documentTypeId. 1. [[attachment:2009.10.19 DrawMGT Prozesstypen Tabelle v01.xlsx]] -- List of desired workflows Note that that attribute lists in the file '''2009.10.19 DrawMGT PSW Limmern Struktur v01.xls''' generally have a set of code letters. E.g. '''Bereich''' has code '''aa'''. These same codes also appear in the document code definition and the names of the .txt and .sql files (see below). == 2009.10.23: Emails from GMM for Training == 1. Training schedule: mails: 10/23/09 8:42 AM and 10/23/09 11:37 AM 1. --(Montag, 9. November 2009, 13:00 - 16:00, Axpo AG Baden, Teilnehmer: 6 Neue, E. Bieri, M. Gmür)-- Rescheduled to Dri 13th (see below) 1. Dienstag, 10. November 2009, 13:00 - 16:00, Axpo AG Baden, Teilnehmer: 5 Neue, M. Hase, M. Gmür 1. Mittwoch, 11. November 2009, 13:00 - 16:00, Bauleitung Tierfehd (Linthal), Teilnehmer: 4 Neue, M. Hase, M. Gmür 1. Freitag, 13.11.2009, 9-12 Uhr, Axpo AG Baden, Teilnehmer: 6 Neue, E. Bieri, M. Gmür == 2009.10.23: Phone call AH/GMM == 1. Official project name is '''PSW Limmern'''. 1. Change workflow step name '''E''' (Erhalten/Receive) to '''B''' (Bekommen). 1. The old (original) test system '''test''' is to be kept. * The new update of the data from the meeting or 2009.10.16 should go in the '''beta''' system. 1. OK to delay deployment of production system to the Axpo hosted server to the end of the year == 2009.10.30: Email from GMM: Configuration Corrections == New/Updated files included in the email: 1. [[attachment:L2015 DrawMGT Indextyp-Attribute v02.xlsx]] -- Updated system classifications * Worksheet with document code definition (unchanged) * Worksheet with updates classification lists (updates marked in red) * Some corrections have already been made (Kontakt 571, 572) * Need to suppress the display of the Code for Lage and Tätigkeit * See cfg/linthal/site/config/Settings-!CacheFieldDisplay.php 1. --([[attachment:L2015 DrawMGT PSW Limmern Struktur v02.xls]])-- -- Contract/Group structure * Contract and Group structure * Should map to the ContractRef and Groups worksheets in linthal-configuration.xls 1. --([[attachment:L2015 DrawMGT Testsystem Korrekturliste v01.xlsx]])-- -- List of !ToDo items (See below) * Review items: * State which items easy/difficult to implement * Separate into translation/programming/configuration == 2009.11.02: Email from GMM: Corrected !ToDo List == 1. --([[attachment:L2015 DrawMGT Testsystem Korrekturliste v02.xlsx]])-- -- List of !ToDo items, with priorities added. (See below) Most corrections should be made in the worksheets in cfg/linthal/site/config/linthal-configuration.xls, and then then loaded into the linthal system and described below. == 2009.11.03: Conference Call AH/GMM == 1. --([[attachment:L2015 DrawMGT Testsystem Korrekturliste v02-AH1.xls]])-- -- Updated from conference call with AH/TN remarks Discussion of system configuration !ToDo list (attached above). See column 'AH/TN Bemerkungen', for the list of to do items. We need to: 1. Discuss the PROBLEM (bright-pink) items, and decide how to proceed. 2. Implement the configuration changes, easy fixes and bugs (light-pink) items. 3. Implement the translation (yellow) items. I hope we can implement all of these by the end of the week. == 2009.11.03: Email from GMM: Updated Configuration Files == 1. [[attachment:linthal-configuration v02.xls]] -- File returned from GMM. Only the '''Users''' worksheet appears to be updated. 1. --([[attachment:L2015 DrawMGT Rollen v01.xlsx]])-- -- List of user roles, and DE names for the roles. 1. --([[attachment:L2015 DrawMGT PSW Limmern Struktur v02b.xls]])-- -- Updates to contract & group codes, marked in red. (File renamed to v02b from v02) == 2009.11.03: Updated NOK ToDo List == 1. --([[attachment:L2015 DrawMGT Testsystem Korrekturliste v02-AH3.xls]])-- - Includes new columns AH/TN Remarks & Status == 2009.11.19: Phone call AH/GMM == 1. Update test system with latest CVS updates 1. Create new task type '''Bewilligungsauflagen''' 1. --(Update [[attachment:L2015 DrawMGT Testsystem Korrekturliste v02-AH4.xls]])-- 1. Update with input collected during training sessions 1. Sent to GMM 1. Check Auflagen spreadsheet: check fields can fit in the Comments table 1. ([[attachment:Bewilligungsauflagen Erfassung DrawMGT v02.xlsx]]) -- see email from 2009.10.13 above) 1. Suggestion for allowing task subscribers to download documents attached to a task, regardless of their user roles. E.g. making some a subscriber to a task, would automatically give them viewing rights to all documents attached to the task. == 2009.11.24: Email from GMM and phone call AH/GMM == 1. Corrected !ToDo List --([[attachment:L2015 DrawMGT Testsystem Korrekturliste v03.xls]])-- 1. We should respond with our status and mark items that will not be complete when going live == 2009.12.18: Email from GMM == 1. Deadline for going live: 2009.01.08 -- Axpo personnel only 1. Deadline for going live: 2009.01.15 -- All project partners 1. Final to do items: 1. DrawMGT Handbook - translate into German 1. Schritt-für-Schritt Dokumentation - To be implemented in wiki 1. Set user passwords and send emails to them 1. --(Implement configuration for new Bereich '''LUe''')-- TN done (date?) 1. --(Update user roles)-- TN done (date?) 1. [[attachment:Anleitung_Bestellungserfassung_EBanf v1.2.pdf]] -- Example Schritt-für-Schritt documetation 1. [[attachment:L2015 DrawMGT PSW Limmern Struktur v05.xls]] -- Updated Project Structure 1. [[attachment:L2015-DrawMGT-Rollen-v07.xls]] -- Updated User Roles == 2010.01.15: Phone call AH/GMM == 1. Go live next week! 1. Four key users / system administrators: * Michael Schlegel * Adele Grandhi * Hans Matter * Daniela Kuhn 1. Set and send passwords to all users on Monday 18-Jan 1. Can we force users to change password on first login? 1. Documentation requirements: 1. Urgently need: CHecklist for login and password change 1. Urgently need: General DE documentation for Document Transmission 1. Need checklist for: Sitzung Protocol verschicken 1. Need checklist for: Metedata upload 1. Agreed to send: 1. Link for Wiki's: 1. Linthal wiki: checklist, FAQ 1. General DE documentation 1. Copy of example WebCast 1. They want a Sicherheits Eklärung 1. How passwords stored/managed 1. Possibilities for improving security 1. Force password change after first-login 1. Block account after n failed logins 1. Integration with Axpo/IT secure ID system (LDAP based?) == 2010.01.18: Email GMM == 1. Corrected !ToDo List in XLSX and PDF formats: * [[attachment:L2015 DrawMGT Testsystem Korrekturliste v10-2010-01-18.pdf]] * [[attachment:L2015 DrawMGT Testsystem Korrekturliste v10-2010-01-18.xlsx]] 1. He has set all priorities to 1 & 2, and set completion deadlines for all items. 1. He requests us to tell him ASAP when deadlines can not be met. == 2010.01.29: Email GMM == 1. Request for updating the transmittals template, to include Axpo colors/logos * = Training Sessions - List of Attendees = == Session 1: 2009-11-10, 13:00, Tuesday, Baden == As seated at table, from Martin's left: * Martin Gmür - Stab Projektoberleiter * Alan Hodgkinson - SoftXS MD * Norbert Wohlkinger - Leiter Projektsupport * Rami Molander - SoftXS Marketing/Sales * Michael Schleger - (Asked about sequence number blocks) * Roland Hug - Projektleiter Los A2 (Baustelle?) * Adele Grandi - Projektassistentin Projektplanung * Daniel Loosli - Leiter Projektplanung * Martin Hase - Projektleiter Los A2 (PM Dam) * Philippe(?) Müller - PM Adits == Session 2: 2009-11-11, 13:00, Wednesday, Tierfeld == As seated at table, from Martin's left: * Martin Gmür - Stab Projektoberleiter * Hans-Peter Schmid - Syv. Chefbauleiter * Peter Priske - Bauleiter * Martin Hase - Projektleiter Los A2 (PM Dam) * Thomas Rageth - Leiter Baustellenmanagement * Helmut Roth - Bauleiter * Valeria Frede - Sicherheitsbeauftragte Linthal 2015 * Alan Hodgkinson - SoftXS MD * Nadia Semadeni - Projektleiterin Umwelt (& Bewilligungsauflagen?) * Rami Molander - SoftXS Marketing/Sales == Session 3: 2009-11-10, 09:00, Friday, Baden == As seated at table, from Martin's left: * Martin Gmür - Stab Projektoberleiter * Alan Hodgkinson - SoftXS MD * Martin Husler - Stv. Projektleiter Projektplanung * Heinz Andermatt - Projektleiter HLKSE * Boris Ritter - Projektleiter Los C1, C3 * Rico Senti - Geologe * Emil Bieri - Projektleiter Los C2 = Project to DrawMGT Configuration Mapping = This section describes how the mapping from the NOK spreadsheet '''2009.10.19 DrawMGT PSW Limmern Struktur v01.xls''' and the DrawMGT database tables. || '''Worksheet Name''' || '''NOK Description''' || '''DrawMGT Table''' || '''Notes''' || || Ref-aaa || Bereich || !ContractRef || || || Ref-b || Projektphase || !ProjectPhaseRef || || || Ref-cc || Fachbereich || !CategoryRef || || || Ref-ddd || Objekt || !LocationRef || || || Ref-eee || Kontakt || !WorkTypeRef || Copied also to table Companies s. below || || Ref-ff || Dokumententyp || !SiteClassification1Ref || Keep separate from !DocumentTypeRef || || Lage || Lage || !AlignmentRef || || || Taetigkeit || Tätigkeit || !WorkCategoryRef || || || Archiv || Archiv || !SiteClassification3Ref || || || Veroeffentlichung || Veröffentlichung || !SiteClassification4Ref || || || !WorkflowSteps || - || - || || || !ApprovalStatusRef || - || !ApprovalStatusRef || German translation of workflow steps || || Companies || - || Companies || || || Groups || - || Groups || || || Users || - || Users || || || !UserRoles || - || !UserRoles || || A workaround is applied to fullfill NOK requieremnt 9.1 in L2015 '''DrawMGT Testsystem Korrekturliste v02.xls'''. NOK's Kontakt information will be copied also to table Companies to allow to select the same information in feld '''Requestor'''. = NOK Document Coding System = == Drawing and Revision Code Format == Note: NOK calls the drawing code '''Indextyp''' Drawing Code ||<-17 rowbgcolor="#cddeee"> '''Document Code''' || - ||<-2> '''Revision Code''' || '''File Extension''' || || aaa || - || i || - || b || - || cc || - || ddd || - || eee || - || ff || _ || #### || - || XXX || - || v || ## || .yyy || Component Description || '''Part''' || '''Description''' || '''DrawMGT Mapping''' || || aaa || Bereich || contractId || || i || Intern/Extern Flag || siteClassification2Id || || b || Projektphase || projectPhaseId || || cc || Fachbereich || categoryId || || ddd || Objekt || locationId || || eee || Kontakt || workTypeId || || ff || Dokumenttyp || siteClassification1Id || || #### || Laufnummer (drawingNo) || drawingNo || || XXX || Teilnummer, 1-3 alphanumeric characters, optional || -none- || || - || || -none- || || v || Constant || revisionCode (with revisionNo) || || ## || Versionsnummer (revisionNo) || revisionNo || || .yyy || File name extension || -none- || = Generated SQL Configuration Files = The NOK configuration information lives in the drawmgt-cfg configuration tree: * cfg/linthal/site/data Most of the configuration is generated from an Excel file: * linthal-configuration.xls The initial worksheet called '''Overview''' lists the mappings from the NOK attribute lists to their corresponding DrawMGT classification tables. Note that DrawMGT V11.3 has four extra classification tables SiteClassification1-4. The generation is perform by saving each separate worksheet in the Excel file, in tab-separated format, into a file of the same in the data directory. The saving can be mad manually as described below or automatically with an OpenOffice macro '''export2Csv.bas''' in the same directory. * E.g. Save the '''!ContractRef''' worksheet as !ContractRef.txt * Save the other worksheets too Then run a script to generate SQL files: * generateRefData.sh This script reads the .txt files and outputs .sql files Then load the SQL into the database and run genMeta.pl {{{ cd cfg/linthal/site/data mysql -u... -p... create database linthal113dev; use linthal113dev; source ../../../../app/schema/drawmgt-schema.sql source ../../../../app/schema/id-table-schema.sql source ../../../../app/schema/data/refdata.sql source linthal-configuration.sql source ../../../../app/schema/id-table-setup.sql quit cd ../../../../app/etc ./genMeta.pl }}} = Access to Axpo CentOS System = Contact is Jörg Leppin. See contacts above. == Access to Axpo External CentOS System == 1. Access via private RSA 1024 bit key that we supplied * Public and private key stored at: '''lu.softxs.ch:/root/axpo-ssh''' * id_rsa * id_rsa.pub -- sent to Jörg Leppin, to be installed on their server. 1. Still to be defined: * System hostname/ip address of CentOS system * User name 1. Access will be possible from the following IP addresses: * '''81.221.23.33''' (softxs.ch) * '''80.254.184.33''' (sz.softxs.ch) Joseph Kaelin's home network 1. Typical access: {{{ ssh root@lu.softxs.ch ssh -i /root/axpo-ssh/id_rsa user@centos.axpo.ch }}} == Access to Axpo Internal CentOS System == 1. Access to the internal CentOS system is performed direcly through Citrix Access Gateway (See SUP-2073 in SoftXS Tracking System). 1. Once you logged in successfully through Citrix Access Gateway you can start an ssh tunneling to the external system to allow to use ssh login from the external system and start a small script to generate network traffic to disable timeout on the tunneling: {{{ root@SABDN471 ~]# ssh -R5556:localhost:22 root@sabdn470 [root@sabdn470 ~]# while [ 1 ]; do sleep 60; echo -n "."; done [root@sabdn470 ~]# ssg -p5556 localhost # log in from external to internal in any terminal }}} == Using CVS on the Axpo CentOS External System == 1. The only network connection can be used is an incoming SSH on the Axpo system. Therefore ssh tunneling is used to allow access CVS on honir from the Axpo system. The port 2401 (pserver) will be forwarded to the ssh port on honir. The typical usage: {{{ honir# ssh -R 2401:localhost:22 -i ~/axpo-ssh/id_rsa root@axpo [root@sabdn470 ~]# su - linthal [linthal@sabdn470 ~]$ cd www/shtml/delta/app/etc [linthal@sabdn470 etc]$ cvs -q up }}} 1. The following settings was made to allow to use CVS so simply as above: 1. A '''.cvsrc''' file was created in the home directory of ''linthal'' on the Axpo system with the content below, to allow the access to the CVS repository through ssh on localhost {{{ cvs -z9 -d linthal@localhost:/home/cvsroot }}} 1. A section was made in '''/etc/ssh/ssh_config''' on the Axpo system (above section 'Host *'), to use port 2401 automatically on ssh connection to localhost: {{{ Host localhost Port 2401 }}} 1. An ssh key pair was generated on the Axpo system as user ''linthal'' and the content of the public key was copied to '''.ssh/authorized_keys''' on honir to allow to use cvs without password. {{{ [linthal@sabdn470 etc]$ ssh-keygen -t rsa [linthal@sabdn470 .ssh]$ view ~/.ssh/id_rsa.pub linthal@honir (~) 499$ vi ~/.ssh/authorized_keys }}} == Using CVS on the Axpo CentOS Internal System == 1. If the tunneling mentioned in the section above is up, start an other tunneling from the internal to the external server to use cvs: {{{ [root@SABDN471 ~]# ssh -L2401:localhost:2401 sabdn470 ... [linthal@SABDN471 ~]$ cd www/shtml/beta1 [linthal@SABDN471 beta1]$ cvs -q up app lib }}} 1. The same configuration is set on the internal server as on the external one (.cvsrc, /etc/ssh/ssh_config, ssh key pair) = Changes to the default httpd.conf = Note that ssl authentication is made by the Axpo, we don't get '''htpps''' but '''http''' connection. However - I don't understand (TN) - if '''NameVirtualHost *:443''' is commented out virtual hosting doesn't work. Thw following settings are changed: 1. Set explicit server name 1. Disable default character settings to allow using of meta tag '''charset''': 1. Set virtual hosting 1. All changes: {{{ [root@sabdn470 ~]# diff /etc/httpd/conf/httpd.conf.091229-1738.orig /etc/httpd/conf/httpd.conf 265a266 > ServerName drawmgt.axpo.ch:80 747c748 < AddDefaultCharset UTF-8 --- > #AddDefaultCharset UTF-8 972c973,974 < #NameVirtualHost *:80 --- > NameVirtualHost *:80 > NameVirtualHost *:443 991a994,1013 > > > DocumentRoot /home/linthal/www/shtml > ServerName drawmgt-pswlimmern.axpo.ch > ServerAlias drawmgt-pswlimmern > ServerAlias drawmgt-pswlimmern-localhost > Options -Indexes > > > # > # SSLEngine on > # SSLCertificateFile /etc/pki/tls/certs/ca.crt > # SSLCertificateKeyFile /etc/pki/tls/private/ca.key > # > # AllowOverride All > # > # DocumentRoot /home/linthal/www/shtml > # ServerName drawmgt-pswlimmern.axpo.ch > # Options -Indexes > # }}} = Installing DrawMGT on drawmgt-pswlimmern.axpo.ch = 1. To make the CVS checkout easy, install a modified version of app/lib/etc/checkout.pl from CVS V12.0: {{{ # On honir as root cd /home/met/www/html/prod/app/etc scp -i ~/axpo-ssh/id_rsa checkout.pl root@axpo:/home/linthal/bin # On drawmgt-pswlimmern.axpo.ch as root cd /home/linthal/bin chown linthal:linthal checkout.pl }}} * Modify checkout.pl to use a different cvsroot: {{{ # Change the following line: my $CVS_ROOT = $USER . '@lu.softxs.ch:/home/cvsroot'; # To: my $CVS_ROOT = $USER . '@localhost:/home/cvsroot'; }}} 1. Checkout and setup drawmgt-pswlimmern.axpo.ch-prod {{{ # As user linthal cd ~/bin ./checkout.pl \ -v -q \ -r 113 \ -u linthal \ -d /home/linthal/www/shtml \ -s prod \ -w apache \ linthal \ drawmgt-pswlimmern.axpo.ch-prod # As root: cd /home/linthal/www/shtml/prod/app/install ./setup.sh linthal drawmgt-pswlimmern.axpo.ch-prod apache linthal }}} 1. Checkout and setup drawmgt-pswlimmern.axpo.ch-prod (as user linthal) {{{ # As user linthal cd ~/bin ./checkout.pl \ -v -q \ -r 113 \ -u linthal \ -d /home/linthal/www/shtml \ -s test \ -w apache \ linthal \ drawmgt-pswlimmern.axpo.ch-test # As root: cd /home/linthal/www/shtml/test/app/install ./setup.sh linthal drawmgt-pswlimmern.axpo.ch-test apache linthal }}} = Switchover lu.softxs.ch --> drawmgt.pswlimmern.axpo.ch Servers = == Status of 2009-01-10 17:30 == * prod & test instances installed: CVS, setup.sh & genMeta (via browser) * prod & test databases patched with: {{{ INSERT INTO EventTypeRef VALUES (19, 'PWDCHG', 'Password Change',null,'PwdChange'); }}} * Need to ensure that all new database entries required for the password change events are in the database * Need to rsync the lu var/linthal/.../data directory to the Axpo server (it is rsynced to the delta instance) {{{ rsync -a \ /home/linthal/www/shtml/delta/var/linthal/drawmgt-pswlimmern.axpo.ch-delta/data/ \ /home/linthal/www/shtml/prod/var/linthal/drawmgt-pswlimmern.axpo.ch-prod/data/ rsync -a \ /home/linthal/www/shtml/delta/var/linthal/drawmgt-pswlimmern.axpo.ch-delta/data/ \ /home/linthal/www/shtml/test/var/linthal/drawmgt-pswlimmern.axpo.ch-test/data/ }}} == Implementation Steps == Note: See previous sectino for CVS installation and setup of prod & test instances. 1. '''On lu.softxs.ch''' '''Done''' * Enable redirect pages {{{ cd /home/linthal/www/html/beta mv index-DISABLED.html index.html mv index.php index-DISABLED.php cd /home/linthal/www/html/delta mv index-DISABLED.html index.html mv index.php index-DISABLED.php }}} * Backup delta database. * Copy delta database backup to Axpo server. * Rsync delta system files from lu.softxs.ch server to Axpo server. 1. '''Desired end state lu.softxs.ch server''' * Attempts to access the beta/delta systems on lu.softxs.ch are automatically redirected to the Axpo server, to the main page. (the redirect pages are configured for this). 1. '''On Axpo server: drawmgt-pswlimmern.axpo.ch''' * Backup database * Setup and install new 'prod' instance ('''done''') * cfg/..../InstanceSettings.php exists & is checked-into CVS, but needs checking/testing * Note the email test mode is set to '''false''' * Setup and install new 'test' instance ('''done''') * cfg/..../InstanceSettings.php exists & is checked-into CVS, but needs checking/testing * Note the email test mode is set to '''true''' * Update main page and links to prod and test systems (add note to say that delta and beta systems ''Sind nicht mehr aktuell'' (or something like that). * Setup event daemon jobs for prod and test systems * Setup copy prod --> test job 1. '''Desired end state: Axpo server''' * Initial page http://drawmgt-pswlimmern.axpo.ch has new links to the prod and test systems, and warnings that the beta and delta systems are not to be used. * Data from the lu.softxs.ch delta system loaded in the prod and test systems on the Axpo server. = Deleting Users = 1. Check first, if a User is used anywhere in the system {{{ $ mysql -uroot -psqladmin linthal113prod set @u='userId'; -- Use the actual userId select count(*) from Revisions where checkResponsibleId = @u; select count(*) from Revisions where startResponsibleId = @u; select count(*) from Revisions where designResponsibleId = @u; select count(*) from Revisions where drafterResponsibleId = @u; select count(*) from Revisions where approver1Id = @u; select count(*) from Revisions where approver2Id = @u; select count(*) from Revisions where approver3Id = @u; select count(*) from Revisions where approver4Id = @u; select count(*) from Revisions where releaserId = @u; select count(*) from Revisions where requesterUserId = @u; select count(*) from Revisions where receiverId = @u; select count(*) from Submittals where submitterId = @u; select count(*) from Comments where issuerUserId = @u; select count(*) from Comments where assignedToUserId = @u; select count(*) from Comments where delegatedToUserId = @u; select count(*) from SubmittalRecipients where userId = @u; }}} If any of the select above gives other than 0, than the appropriate Users and UserRoles record can not be deleted. Set the user locked instead. = Changing UserRoles = Axpo follows users and user roles in excel table. The user roles are stored in the Linthal-DrawMGT system as document '''Prj-E-6-01-610-221-00_0014'''. The procedure described here allows you '''reload user roles''' in Axpo server. 1. Create a new database for testing the changes and load tables '''ContractRef''', '''Groups''', '''Roles''', '''Users''' and '''UserRoles''' from the production system {{{ # export tables Users and UserRoles from the internal production system [linthal@sabdn470 ~]$ mysqldump --triggers=false -uroot -psqladmin linthal115prod1 ContractRef Companies Groups Roles Users UserRoles > /home/backup/linthal115prod1_UserRoles_20100430-1550.sql # # copy the export to the development machine $ scp .... /tmp # # load the export into a new database $ mysql -uroot -psqladmin set names utf8; create database linthal115_users; use linthal115_users; source /tmp/linthal115prod1_UserRoles_20100430-1550.sql; quit; }}} 1. Export Data from Excel Sheet * UserRoles * Export the sheet 'UserRoles...' as TAB separated CSV file with charset UTF-8 to cfg/linthal/site/data/L2015-DrawMGT-Rollen.txt * Note that Excel (on the Mac at least) is unable to save CSV in UTF-8 format. Use OpenOffice instead. Select '''File->Save As''' and select the '''Edit filter settings''' checkbox. Then before you save the file a dialog appears where you can select the field and text delimiters. Select the following: * '''Field delimiter: {tab}''' * '''Text delimiter: ''' (empty, go in the field, press BS and TAB to clear it) 1. Create SQL files to update/create UserRoles records {{{ $ cd cfg/linthal/site/data # Create UserRoles.sql $ ../../../../app/etc/genRoles.pl -f 3 -l -uroot -psqladmin -dlinthal115_users L2015-DrawMGT-Rollen.txt > UserRoles.sql }}} 1. Check in the changes {{{ cvs ci -m'Updating User Roles Axpo according to "Prj-E-6-01-610-221-00_0014-v09"' \ L2015-DrawMGT-Rollen.txt UserRoles.sql }}} 1. Run the SQL file on the Axpo internal system The script '''!UpdateUserRoles.sql''' loads the new '''!UserRoles.sql''' into a temporary database and deletes, inserts records into the production database optimizing the number of affected records to minimize mirroring work. CAUTION: in the script '''!UpdateUserRoles.sql''' is the database name ''linthal115prod1'' hard coded. {{{ [linthal@sabdn470 ~]$ cd www/shtml/prod/cfg/linthal/site/data/ [linthal@sabdn470 data]$ cvs -q up [linthal@sabdn470 data]$ mysql -uroot -psqladmin mysql> source UpdateUserRoles.sql }}}