Rails in the Enterprise: Off the Beaten Track Alex Rothenberg - - PowerPoint PPT Presentation

▶
rails in the enterprise off the beaten track
SMART_READER_LITE
LIVE PREVIEW

Rails in the Enterprise: Off the Beaten Track Alex Rothenberg - - PowerPoint PPT Presentation

Feb 18, 2023 8 likes •561 views

Rails in the Enterprise: Off the Beaten Track Alex Rothenberg http://alexrothenberg.com @alexrothenberg Pat Shaughnessy http://patshaughnessy.net @patshaughnessy2 Thursday, July 22, 2010 Thursday, July 22, 2010 Thursday, July 22, 2010

slide-1
SLIDE 1

Rails in the Enterprise: Off the Beaten Track

Alex Rothenberg http://alexrothenberg.com @alexrothenberg Pat Shaughnessy http://patshaughnessy.net @patshaughnessy2

Thursday, July 22, 2010

slide-2
SLIDE 2

Thursday, July 22, 2010

slide-3
SLIDE 3

Thursday, July 22, 2010

slide-4
SLIDE 4

Thursday, July 22, 2010

slide-5
SLIDE 5

Thursday, July 22, 2010

slide-6
SLIDE 6

Using Rails When ...

sharing a development database there was no documented way to create a new database

  • ur existing database was not built with Rails

in mind the database schema is hard to work with when you find application code in the database

Thursday, July 22, 2010

slide-7
SLIDE 7

Problem 1: Sharing a development database

Thursday, July 22, 2010

slide-8
SLIDE 8

From: ... To: All Developers Cc: ... Date: 07/06/2010 09:47 AM Subject: Dev/QA Databases unavailable. Hi All, This is to inform you that all the databases on Dev/QA servers are down due to issues in UNIX file systems. Please find the following list of servers are impacted

  • oradbdev-ux01
  • oradbdev-ux02
  • oradbdev-ux03
  • oradbqa-ux01
  • oradbqa-ux02

Thursday, July 22, 2010

slide-9
SLIDE 9

Great tutorials for installing Oracle on a Mac:

Mac OS X Leopard:

http://blog.rayapps.com/2009/04/12/how-to-install-oracle- database-10g-on-mac-os-x-intel/

Mac OS X Snow Leopard:

http://blog.rayapps.com/2009/09/14/how-to-install-oracle- database-10g-on-mac-os-x-snow-leopard/

Thursday, July 22, 2010

slide-10
SLIDE 10

Oracle install kits

Oracle 11g:

http://www.oracle.com/technology/software/products/ database/index.html

Oracle XE (for Linux & Windows only)

http://www.oracle.com/technology/software/products/ database/xe/index.html

Thursday, July 22, 2010

slide-11
SLIDE 11

~ pat$ sqlplus dev/dev@orcl SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 14 22:12:06 2010 SQL> exit ~ pat$ sqlplus test/test@orcl SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 14 22:12:06 2010 SQL>

Thursday, July 22, 2010

slide-12
SLIDE 12

Problem 2: There was no documented way to create the database

Thursday, July 22, 2010

slide-13
SLIDE 13

class CreatePeople < ActiveRecord::Migration def self.up create_table :people do |t| t.string :name t.timestamps end end def self.down drop_table :people end end

Thursday, July 22, 2010

slide-14
SLIDE 14

my_app pat$ rake db:migrate (in /Users/pat/my_app) my_app pat$

Thursday, July 22, 2010

slide-15
SLIDE 15

ActiveRecord::Schema.define(:version => ...) do create_table "people", :force => true do |t| t.string "name" t.datetime "created_at" t.datetime "updated_at" end # Lots more Enterprise crap deleted here... end

Thursday, July 22, 2010

slide-16
SLIDE 16

class MigrationZero < ActiveRecord::Migration def self.up end def self.down end end

Thursday, July 22, 2010

slide-17
SLIDE 17

class MigrationZero < ActiveRecord::Migration def self.up create_table "people", :force => true do |t| t.string "name" t.datetime "created_at" t.datetime "updated_at" end # Lots more Enterprise crap pasted here... end

Thursday, July 22, 2010

slide-18
SLIDE 18

my_app pat$ rake db:populate (in /Users/pat/my_app) Deleting existing people... Loading new people... 43 people added. etc...

Thursday, July 22, 2010

slide-19
SLIDE 19

Problem 3: Our existing database was not built with Rails in mind

Thursday, July 22, 2010

slide-20
SLIDE 20

class Person < ActiveRecord::Base end

Thursday, July 22, 2010

slide-21
SLIDE 21

class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personid end

Thursday, July 22, 2010

slide-22
SLIDE 22

class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personid has_many :addresses, :foreign_key => :personid end

Thursday, July 22, 2010

slide-23
SLIDE 23

Thursday, July 22, 2010

slide-24
SLIDE 24

Thursday, July 22, 2010

slide-25
SLIDE 25

Legacy Data Gem

Install:

gem install legacy_data

Source:

http://github.com/alexrothenberg/legacy_data

Thursday, July 22, 2010

slide-26
SLIDE 26

my_app pat$ script/generate models_from_tables analyzing psn_perdiem => PsnPerdiem analyzing psn_person => PsnPerson analyzing psn_personal_info => PsnPersonalInfo etc...

Thursday, July 22, 2010

slide-27
SLIDE 27

class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personid has_many :addresses, :foreign_key => :personid validates_presence_of :first_name validates_uniqueness_of :employee_id end

Thursday, July 22, 2010

slide-28
SLIDE 28
  • User friendly error messages
  • Accessible to most of development team
  • Easier to write tests

Thursday, July 22, 2010

slide-29
SLIDE 29

Problem 4: The database schema was hard to work with

Thursday, July 22, 2010

slide-30
SLIDE 30

SELECT personid, first_name, last_name, ... FROM psn_person

Thursday, July 22, 2010

slide-31
SLIDE 31

SELECT personid, first_name, last_name FROM psn_person INNER JOIN psn_person_extra ON psn_person... INNER JOIN psn_person_career ON psn_person... INNER JOIN psn_person_email ON psn_person... INNER JOIN pik_position ON psn_person... INNER JOIN pik_yes_no ON psn_person... ...and many more...

Thursday, July 22, 2010

slide-32
SLIDE 32

class Person < ActiveRecord::Base has_one :person_extra has_one :person_career has_one :person_email belongs_to :pik_position belongs_to :pik_yes_no # And many other associations too end

Thursday, July 22, 2010

slide-33
SLIDE 33

Thursday, July 22, 2010

slide-34
SLIDE 34

SELECT id, first_name, last_name, ... FROM people SELECT personid, first_name, last_name FROM psn_person INNER JOIN psn_person_extra ON psn_person... INNER JOIN psn_person_career ON psn_person... INNER JOIN psn_person_email ON psn_person... INNER JOIN pik_position ON psn_person... INNER JOIN pik_yes_no ON psn_person...

Thursday, July 22, 2010

slide-35
SLIDE 35

class CreatePeopleView < ActiveRecord::Migration def self.up execute <<-END_SQL CREATE VIEW people AS SELECT personid AS id, ... FROM psn_person INNER JOIN ... ... END_SQL end def self.down execute "DROP VIEW people" end end

Thursday, July 22, 2010

slide-36
SLIDE 36

class Person < ActiveRecord::Base default_scope :readonly=>true end

Thursday, July 22, 2010

slide-37
SLIDE 37

require 'spec_helper' describe Person do it "should ..." do Factory :person, :name => 'hilda' Factory :person, :name => 'fredo' # Do something with people # and assert on result ... end end

Thursday, July 22, 2010

slide-38
SLIDE 38

Thursday, July 22, 2010

slide-39
SLIDE 39

Problem 5: We found legacy code in

  • ur database

Thursday, July 22, 2010

slide-40
SLIDE 40

Thursday, July 22, 2010

slide-41
SLIDE 41

PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId ) IS type tyAddressTable IS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData;

  • PROCEDURE FillTable

( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable ) IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID; BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP; END;

Thursday, July 22, 2010

slide-42
SLIDE 42

PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId ) IS type tyAddressTable IS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData;

  • PROCEDURE FillTable

( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable ) IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID; BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP; END; FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER; BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'|| pDuplicateTable(pRow).AddressNote ;

  • - dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate

personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'|| pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF;

Thursday, July 22, 2010

slide-43
SLIDE 43

PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId ) IS type tyAddressTable IS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData;

  • PROCEDURE FillTable

( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable ) IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID; BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP; END; FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER; BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'|| pDuplicateTable(pRow).AddressNote ;

  • - dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate

personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'|| pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF; IF pDuplicateTable(pRow).systemNote IS NOT NULL THEN pCorrectTable(m).systemNote := pCorrectTable(m).SystemNote||CHR(10)||'Note from Duplicate:'|| pDuplicateTable(pRow).systemNote; shouldModify := pkgGlobal.gTrue; END IF; IF shouldModify = pkgGlobal.gTrue THEN

  • - Clobber added 10/16/03

pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.modify( pCorrectTable(m), pSecure, pCommit );

  • - dbms_output.put_line('Moving columns from pDuplicate PersonId:'||pDuplicateTable(pRow).P

pCorrectTable(m).PersonId||' where data is present in pDuplicate and null in master '); END IF;

  • - Print pDuplicate Record which is to be deleted
  • - dbms_output.put_line('pDuplicate Record which is to be deleted...');
  • - dbms_output.put_line('ADDRESSID :'||to_char(pDuplicateTable(pRow).addressId));
  • - dbms_output.put_line('PERSONID :'||to_char(pDuplicateTable(pRow).personId));
  • - dbms_output.put_line('ADDRESSTYPEID :'||to_char(pDuplicateTable(pRow).AddressTypeId));
  • - dbms_output.put_line('CITY :'||pDuplicateTable(pRow).city);
  • - dbms_output.put_line('STATEPROVINCEID:'||to_char(pDuplicateTable(pRow).stateProvinceId));
  • - dbms_output.put_line('POSTALCODE :'||pDuplicateTable(pRow).PostalCode);
  • - dbms_output.put_line('COUNTRYID :'||to_char(pDuplicateTable(pRow).countryId));
  • - dbms_output.put_line('SYSTEMNOTE :'||substr(pDuplicateTable(pRow).systemNote,1,230));
  • - dbms_output.put_line('ADDRESSNOTE :'||substr(pDuplicateTable(pRow).addressNote,1,230));
  • - Check if Primary Email exists in Master, if no and the pDuplicate is isPrimary, then move the flag to master

BEGIN SELECT COUNT(*) INTO primary_count_for_delete FROM tbAddress WHERE personId = pCorrectPersonId AND IsPrimary = 1; IF primary_count_for_delete = 0 AND pDuplicateTable(pRow).IsPrimary = 1 THEN pCorrectTable(m).IsPrimary := 1; pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.Modify( pCorrectTable(m), pSecure, pCommit ); END IF; EXCEPTION WHEN no_data_found THEN NULL; END; EXIT; ELSE Ret := pkgGlobal.gFalse; END IF; m := pCorrectTable.Next( m ); END LOOP; RETURN Ret; EXCEPTION WHEN OTHERS THEN pkgException.RaiseOther (SQLCODE, sqlerrm); RETURN Ret; NULL; END;

Thursday, July 22, 2010

slide-44
SLIDE 44

PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId ) IS type tyAddressTable IS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData;

  • PROCEDURE FillTable

( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable ) IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID; BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP; END; FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER; BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'|| pDuplicateTable(pRow).AddressNote ;

  • - dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate

personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'|| pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF; IF pDuplicateTable(pRow).systemNote IS NOT NULL THEN pCorrectTable(m).systemNote := pCorrectTable(m).SystemNote||CHR(10)||'Note from Duplicate:'|| pDuplicateTable(pRow).systemNote; shouldModify := pkgGlobal.gTrue; END IF; IF shouldModify = pkgGlobal.gTrue THEN

  • - Clobber added 10/16/03

pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.modify( pCorrectTable(m), pSecure, pCommit );

  • - dbms_output.put_line('Moving columns from pDuplicate PersonId:'||pDuplicateTable(pRow).P

pCorrectTable(m).PersonId||' where data is present in pDuplicate and null in master '); END IF;

  • - Print pDuplicate Record which is to be deleted
  • - dbms_output.put_line('pDuplicate Record which is to be deleted...');
  • - dbms_output.put_line('ADDRESSID :'||to_char(pDuplicateTable(pRow).addressId));
  • - dbms_output.put_line('PERSONID :'||to_char(pDuplicateTable(pRow).personId));
  • - dbms_output.put_line('ADDRESSTYPEID :'||to_char(pDuplicateTable(pRow).AddressTypeId));
  • - dbms_output.put_line('CITY :'||pDuplicateTable(pRow).city);
  • - dbms_output.put_line('STATEPROVINCEID:'||to_char(pDuplicateTable(pRow).stateProvinceId));
  • - dbms_output.put_line('POSTALCODE :'||pDuplicateTable(pRow).PostalCode);
  • - dbms_output.put_line('COUNTRYID :'||to_char(pDuplicateTable(pRow).countryId));
  • - dbms_output.put_line('SYSTEMNOTE :'||substr(pDuplicateTable(pRow).systemNote,1,230));
  • - dbms_output.put_line('ADDRESSNOTE :'||substr(pDuplicateTable(pRow).addressNote,1,230));
  • - Check if Primary Email exists in Master, if no and the pDuplicate is isPrimary, then move the flag to master

BEGIN SELECT COUNT(*) INTO primary_count_for_delete FROM tbAddress WHERE personId = pCorrectPersonId AND IsPrimary = 1; IF primary_count_for_delete = 0 AND pDuplicateTable(pRow).IsPrimary = 1 THEN pCorrectTable(m).IsPrimary := 1; pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.Modify( pCorrectTable(m), pSecure, pCommit ); END IF; EXCEPTION WHEN no_data_found THEN NULL; END; EXIT; ELSE Ret := pkgGlobal.gFalse; END IF; m := pCorrectTable.Next( m ); END LOOP; RETURN Ret; EXCEPTION WHEN OTHERS THEN pkgException.RaiseOther (SQLCODE, sqlerrm); RETURN Ret; NULL; END;

BEGIN ret := upper(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( string1,'!',''),'@',''),'#',''),'$',''), '%',''),'^',''),'&',''),'*',''), '-', ''),'+',''),'',''),'(',''), ')',''),'?',''),',',''),'.',''), '/',''),'=','')); RETURN ret; END;

Thursday, July 22, 2010

slide-45
SLIDE 45

Thursday, July 22, 2010

slide-46
SLIDE 46

Thursday, July 22, 2010

slide-47
SLIDE 47

require 'spec_helper' describe Person do it "should merge two duplicates" do Factory :person, :email => 'alex@alex.com' Factory :person, :email => 'alex@alex.com' Person.merge_duplicates Person.count.should == 1 end end

Thursday, July 22, 2010

slide-48
SLIDE 48

my_app pat$ rake spec (in /Users/pat/my_app) ................................................. .F............................................... ...................................... 1) RuntimeError in 'Should merge two duplicates' OCIError: ORA-04092:cannot ROLLBACK in a trigger ... ORA-01403: no data found ORA-06512: at "HRTEST.PKGAPPBASE", line 1775 ORA-04088: error during execution of trigger UPDATE psn_address SET personid = 10543035 WHERE applicationid = 10594482

Thursday, July 22, 2010

slide-49
SLIDE 49

PROCEDURE updateops # ...lots of PL/SQL... vQryStr := 'SELECT ... WHERE personid = ' || ppersonid; dbms_output.put_line(vQryStr); # ...lots more PL/SQL... END;

Thursday, July 22, 2010

slide-50
SLIDE 50

Oracle Enhanced Adapter

Install:

gem install activerecord-oracle_enhanced-adapter

Source:

http://github.com/rsim/oracle-enhanced

Thursday, July 22, 2010

slide-51
SLIDE 51

6357 lines 0 tests 33% failure 322 lines lots of tests 0.1% failure

Merge Job

Thursday, July 22, 2010

slide-52
SLIDE 52

Using Rails When ...

sharing a development database there was no documented way to create a new database

  • ur existing database was not built with Rails

in mind the database schema is hard to work with when you find application code in the database

Thursday, July 22, 2010

slide-53
SLIDE 53

Use Rails best practices even when confronted with legacy problems

Thursday, July 22, 2010

slide-54
SLIDE 54

Thank you!

Alex Rothenberg http://alexrothenberg.com @alexrothenberg Pat Shaughnessy http://patshaughnessy.net @patshaughnessy2

Thursday, July 22, 2010

slide-55
SLIDE 55

http://media.photobucket.com/image/lipstick%20on%20a%20pig/046664/LipstickPig-C.jpg?o=11 http://www.flickr.com/photos/piccadillywilson/1366479417/

Photo Credits

Thursday, July 22, 2010