Differences between revisions 65 and 66
Deletions are marked like this. Additions are marked like this.
Line 762: Line 762:
mysql> update ID_TABLE set NEXT_ID = (select max(userId) from Users) + 1 where TABLE_NAME = 'Users';

NOK Configuration for the Linthal 2015 Project

Contents

  1. NOK Configuration for the Linthal 2015 Project
  2. Notes
  3. Terminology
  4. Contact Information
    1. Contact Addresses
      1. Main Office
      2. Axpo Informatik
      3. Construction Site
    2. Contact Names
  5. Discussions and Input Data from NOK
    1. 2009.10.13: Email from GMM
    2. 2009.10.16: AH/GMM/etc. Meeting in Baden to discuss classification system and workflows
    3. 2009.10.23: Emails from GMM for Training
    4. 2009.10.23: Phone call AH/GMM
    5. 2009.10.30: Email from GMM: Configuration Corrections
    6. 2009.11.02: Email from GMM: Corrected !ToDo List
    7. 2009.11.03: Conference Call AH/GMM
    8. 2009.11.03: Email from GMM: Updated Configuration Files
    9. 2009.11.03: Updated NOK ToDo List
    10. 2009.11.19: Phone call AH/GMM
    11. 2009.11.24: Email from GMM and phone call AH/GMM
    12. 2009.12.18: Email from GMM
    13. 2010.01.15: Phone call AH/GMM
    14. 2010.01.18: Email GMM
    15. 2010.01.29: Email GMM
  6. Training Sessions - List of Attendees
    1. Session 1: 2009-11-10, 13:00, Tuesday, Baden
    2. Session 2: 2009-11-11, 13:00, Wednesday, Tierfeld
    3. Session 3: 2009-11-10, 09:00, Friday, Baden
  7. Project to DrawMGT Configuration Mapping
  8. NOK Document Coding System
    1. Drawing and Revision Code Format
  9. Generated SQL Configuration Files
  10. Access to Axpo CentOS System
  11. Using CVS on the Axpo CentOS System
  12. Changes to the default httpd.conf
  13. Installing DrawMGT on drawmgt-pswlimmern.axpo.ch
  14. Switchover lu.softxs.ch --> drawmgt.pswlimmern.axpo.ch Servers
    1. Status of 2009-01-10 17:30
    2. Implementation Steps
  15. Deleting Users
  16. Changing Users and UserRoles

Notes

  1. The official project name is PSW Limmern (Linthal 2015 is a project above, which include PSW Limmern)

  2. NOK is being renamed as Axpo

  3. Axpo Informatic will host our server. They seem to be quite independent of NOK and the Linthal project

  4. 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)
  2. 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

Discussions and Input Data from NOK

2009.10.13: Email from GMM

  1. Bewilligungsauflagen Erfassung DrawMGT v02.xlsx - Example spreadsheet for Bewilligungsauflagen

  2. 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

  2. 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.

  3. New ApprovalStatus: Nicht efforderlich (e.g. N/A - Not Required). Rules:

    • Plan date: optional

    • Complete date: must be empty

    • Initials: optional

  4. Summary of German language approval steps:
    • Offen
    • Erteilt
    • Erteilt mit Kommentar
    • Abgelehnt
  5. 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

  6. Request for improved event history to see exactly what was changed in workflows.

  7. 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. 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

  2. 2009.10.19 DrawMGT Indextyp-Attribute v01.xlsx -- List of document code types and their components

  3. 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.

  4. 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)

    2. Dienstag, 10. November 2009, 13:00 - 16:00, Axpo AG Baden, Teilnehmer: 5 Neue, M. Hase, M. Gmür
    3. Mittwoch, 11. November 2009, 13:00 - 16:00, Bauleitung Tierfehd (Linthal), Teilnehmer: 4 Neue, M. Hase, M. Gmür
    4. 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.

  2. Change workflow step name E (Erhalten/Receive) to B (Bekommen).

  3. 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.

  4. 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. 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

  2. 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

  3. 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. 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. 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. linthal-configuration v02.xls -- File returned from GMM. Only the Users worksheet appears to be updated.

  2. L2015 DrawMGT Rollen v01.xlsx -- List of user roles, and DE names for the roles.

  3. 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. 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

  2. Create new task type Bewilligungsauflagen

  3. Update L2015 DrawMGT Testsystem Korrekturliste v02-AH4.xls

    1. Update with input collected during training sessions
    2. Sent to GMM
  4. Check Auflagen spreadsheet: check fields can fit in the Comments table

    1. (Bewilligungsauflagen Erfassung DrawMGT v02.xlsx) -- see email from 2009.10.13 above)

  5. 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 L2015 DrawMGT Testsystem Korrekturliste v03.xls

  2. 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
  2. Deadline for going live: 2009.01.15 -- All project partners
  3. Final to do items:
    1. DrawMGT Handbook - translate into German
    2. Schritt-für-Schritt Dokumentation - To be implemented in wiki
    3. Set user passwords and send emails to them

    4. Implement configuration for new Bereich LUe TN done (date?)

    5. Update user roles TN done (date?)

  4. Anleitung_Bestellungserfassung_EBanf v1.2.pdf -- Example Schritt-für-Schritt documetation

  5. L2015 DrawMGT PSW Limmern Struktur v05.xls -- Updated Project Structure

  6. L2015-DrawMGT-Rollen-v07.xls -- Updated User Roles

2010.01.15: Phone call AH/GMM

  1. Go live next week!
  2. Four key users / system administrators:
    • Michael Schlegel
    • Adele Grandhi
    • Hans Matter
    • Daniela Kuhn
  3. Set and send passwords to all users on Monday 18-Jan
    1. Can we force users to change password on first login?
  4. Documentation requirements:
    1. Urgently need: CHecklist for login and password change
    2. Urgently need: General DE documentation for Document Transmission
    3. Need checklist for: Sitzung Protocol verschicken
    4. Need checklist for: Metedata upload
  5. Agreed to send:
    1. Link for Wiki's:
      1. Linthal wiki: checklist, FAQ
      2. General DE documentation

    2. Copy of example WebCast

  6. They want a Sicherheits Eklärung
    1. How passwords stored/managed
    2. Possibilities for improving security
      1. Force password change after first-login
      2. Block account after n failed logins
      3. Integration with Axpo/IT secure ID system (LDAP based?)

2010.01.18: Email GMM

  1. Corrected ToDo List in XLSX and PDF formats:

  2. He has set all priorities to 1 & 2, and set completion deadlines for all items.

  3. 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

  • Document Code

    -

    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.

  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.
  2. Still to be defined:
    • System hostname/ip address of CentOS system
    • User name
  3. 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

  4. Typical access: 
    •        ssh root@lu.softxs.ch
       ssh -i /root/axpo-ssh/id_rsa user@centos.axpo.ch

Using CVS on the Axpo CentOS 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
  2. 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 -d linthal@localhost:/home/cvsroot

    2. 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

    3. 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

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

  2. Disable default character settings to allow using of meta tag charset:

  3. Set virtual hosting
  4. 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
>
> <VirtualHost *:80>
>         DocumentRoot /home/linthal/www/shtml
>         ServerName drawmgt-pswlimmern.axpo.ch
>       ServerAlias drawmgt-pswlimmern
>       ServerAlias drawmgt-pswlimmern-localhost
>         Options       -Indexes
> </VirtualHost>
>
> #<VirtualHost *:443>
> #        SSLEngine on
> #        SSLCertificateFile /etc/pki/tls/certs/ca.crt
> #        SSLCertificateKeyFile /etc/pki/tls/private/ca.key
> #        <Directory /home/linthal/www/shtml>
> #        AllowOverride All
> #        </Directory>
> #        DocumentRoot /home/linthal/www/shtml
> #        ServerName drawmgt-pswlimmern.axpo.ch
> #        Options       -Indexes
> #</VirtualHost>

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';
  2. 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
  3. 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.

  2. 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).

  3. 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

  4. 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
select count(*) from Revisions where checkResponsibleId = ...;
select count(*) from Revisions where startResponsibleId = ...;
select count(*) from Revisions where designResponsibleId = ...;
select count(*) from Revisions where drafterResponsibleId = ...;
select count(*) from Revisions where approver1Id = ...;
select count(*) from Revisions where approver2Id = ...;
select count(*) from Revisions where approver3Id = ...;
select count(*) from Revisions where approver4Id = ...;
select count(*) from Revisions where releaserId = ...;
select count(*) from Revisions where requesterUserId = ...;
select count(*) from Revisions where receiverId = ...;
select count(*) from Submittals where submitterId = ...;
select count(*) from Comments where issuerUserId = ...;
select count(*) from Comments where assignedToUserId = ...;
select count(*) from Comments where delegatedToUserId = ...;
select count(*) from SubmittalRecipients where userId = ...;

    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 Users and UserRoles

Axpo follows users and user roles in excel table, and is stored in the Linthal-DrawMGT system as document Mgt-6-01-610-500-00_0003. The procedure described here allows you to insert new users and 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 production system
[linthal@sabdn470 ~]$ mysqldump -uroot -psqladmin linthal113prod ContractRef Groups Roles Users UserRoles > /home/backup/linthal113prod_UserRoles_20100430-1550.sql
#
# copy the export to the development machine
$ scp .... /tmp
#
# check the exports character set, likelyhood the CHARSET=latin1 must be changed to CHARSET=utf8
$ vi /tmp/linthal113prod_UserRoles_20100430-1550.sql
:%s/ENGINE=MyISAM DEFAULT CHARSET=latin1/ENGINE=MyISAM DEFAULT CHARSET=utf8/
:x
#
# load the export into a new database
$ mysql -uroot -psqladmin
set names utf8;
create database linthal113_users;
use linthal113_users;
source /tmp/linthal113prod_UserRoles_20100430-1550.sql;
quit;
#
# create csv files from tables as backup for comparing later
$ mysql -uroot -psqladmin linthal113_users -e 'select * from Users where userId >=100 order by UserId' >  Users_old.csv
$ mysql -uroot -psqladmin linthal113_users -e 'select * from UserRoles where userId >=100 order by userId, contractId, groupId, roleId' >  UserRoles_old.csv
  2. Export Data from Excel Sheet
    • Users
      • Pull down the last line in sheet 'Users' so long, as many lines the sheet 'Benutzer' has.
        • So al line are copied from sheet 'Benutzer' to sheet 'Users'. Old users have userId filled, while new ones not.
      • Fill userId on sheet 'Benutzer' (write the next free userId in the first empty userId field and pull down)
      • Save the excel file
      • Export the sheet 'Users' as TAB separated CSV file with charset UTF-8 to cfg/linthal/site/data/Users.txt

    • UserRoles

      • Export the sheet 'Benutzerrollen' as TAB separated CSV file with charset UTF-8 to cfg/linthal/site/data/L2015-DrawMGT-Rollen.txt

    • Note that Excel (on the Macat least) is unable to save CSV in URF-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: "

    • After saving the file with double quote as a text delimiter, you have to edit the file and remove all the double quote characters.
  3. Create SQL files to update/create Users records 
    $ cd cfg/linthal/site/data
$ ../../../../app/etc/genRefData.pl -t Users -k userId -f Users.txt > Users.sql
#
# The SQL file creates all user records, also the old ones.
$ cp Users.sql Users_new.sql
#
# Delete the records of the old users
$ vi Users_new.sql
:4,NNd
:x
#
# Create the new users
$ mysql -uroot -psqladmin linthal113_users -e 'source Users_new.sql'
#
# Change SQL update records for old users
# Load the records in a temporary record and update the old records
$ cp Users.sql Users_update.sql
$ vi Users_update.sql
:NN,$d              # delete all new records
:s/,$/;/            # change comma to semicolon at the end of the line
:1                  # goto first line
:s/Users/Users_tmp/ # change tablename
Oset names utf8;
create temporary table Users_tmp like Users;   # create temp table in first line
<ESC>
G                   # go to last line
oupdate Users u, Users_tmp t set u.initials=t.initials, u.firstName=t.firstName, u.lastName=t.lastName, u.description=t.description, u.company=t.company, u.eMail1=t.eMail1, u.loginName=t.loginName, u.defaultContractId=t.defaultContractId, u.defaultGroupId=t.defaultGroupId where u.userId = t.userId;
<ESC>
:x
#
# Update old users
$ mysql -uroot -psqladmin linthal113_users -e 'source Users_update.sql'
#
# Create UserRoles.sql
$ ../../../../app/etc/genRoles.pl -2 -l -uroot -psqladmin -dlinthal113_users L2015-DrawMGT-Rollen.txt > UserRoles.sql
#
# Load new UserRoles
$ mysql -uroot -psqladmin linthal113_users -e 'delete from UserRoles where userId >= 100'
$ mysql -uroot -psqladmin linthal113_users -e 'source UserRoles.sql'
  4. Ensure that all changes are correct 
    # Create CSV files again
$ mysql -uroot -psqladmin linthal113_users -e 'select * from Users where userId >=100 order by UserId' >  Users_new.csv
$ mysql -uroot -psqladmin linthal113_users -e 'select * from UserRoles where userId >=100 order by userId, contractId, groupId, roleId' >  UserRoles_new.csv
#
# Compare with the old version
diff Users_old.csv Users_new.csv
diff UserRoles_old.csv UserRoles_new.csv
  5. Run the SQL files on the Axpo system, perform some basic settings on the new users and regenerate caches (remotely) (replace 'NN' with the first new userId) 
    [linthal@sabdn470 ~]$ mysql -uroot -psqladmin linthal113prod
mysql> set names utf8;
mysql> source Users_new.sql
mysql> source Users_update.sql
mysql> update ID_TABLE set NEXT_ID = (select max(userId) from Users) + 1  where TABLE_NAME = 'Users';
mysql> update Users set languageId=2 where userId >= NN;
mysql> update Users set loginEnabled=1, maxLogins=1 where userId >= NN;
mysql> delete from UserRoles where userId >=100;
mysql> source UserRoles.sql;
# from the development machine
$ cd app/etc
$ ./genMeta.pl -n -c https://drawmgt-pswlimmern.axpo.ch/prod/lib/meta

    Note that the genMeta.pl -n option is required because of HTTPS access.

  6. Generate passwords for all new users (replace 'NN' with the first new userId) 
    [linthal@sabdn470 ~]$ mysql -uroot -psqladmin linthal113prod -e 'select userId, loginName from Users where userId>=NN and password is null' > /tmp/users.lst
[linthal@sabdn470 ~]$ vi /tmp/users.lst  # Remove the header
:1
dd:x
[linthal@sabdn470 ~]$ cd app/etc; ./setPasswords.sh /tmp/users.lst > /tmp/setPasswords.sql
[linthal@sabdn470 ~]$ mysql -uroot -psqladmin linthal113prod -e 'source /tmp/setPasswords.sql'
[linthal@sabdn470 ~]$ rm /tmp/users.lst /tmp/setPasswords.sql

NokConfiguration (last edited 2012-01-31 15:51:02 by sleightholm)

Copyright 2008-2014, SoftXS GmbH, Switzerland