code sauce

...

Deploying Code and Db Migration via Phing

| Comments

working on a project hosted on a shared hosting, with limited resources, i had to find a way to update my working copy with the testing and the production server..and after some tinkering this is the result. hope it’s usefull

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<?xml version="1.0"?>
<project name="project_sync" basedir="." default="-init">
  <property name="version" value="4.0"/>
  <!-- Public targets -->
  <!-- ============================================ -->
  <!-- Target: sync:list -->
  <!-- ============================================ -->
  <target name="sync:list" description="Public: List files to be synchronized facade">
    <phingcall target="-sync-execute-task">
      <property name="listonly" value="true"/>
    </phingcall>
  </target>
  <!-- ============================================ -->
  <!-- Target: sync -->
  <!-- ============================================ -->
  <target name="sync" description="Public: Synchronize task fasade">
    <phingcall target="-sync-execute-task">
      <property name="listonly" value="false"/>
    </phingcall>
  </target>
  <!-- ============================================ -->
  <!-- Target: db-update -->
  <!-- ============================================ -->
  <target name="db-update" depends="-list-applied-updates" description="Public: The db update facade ">
    <php expression="eval('$list=glob("${sync.source.projectdir}/sql/*.sql"); natsort ($list); return implode(",", $list);');" returnProperty="db.patchfiles"/>
    <phingcall target="-list-applied-updates"/>
    <echo msg="Searching for updates on : ${sync.source.projectdir}/sql"/>
    <foreach param="filename" target="-apply-changelog">
      <fileset dir="${sync.source.projectdir}/sql">
        <include name="${db.patchfiles}"/>
        <excludesfile name="${sync.source.projectdir}/build/results.txt"/>
      </fileset>
    </foreach>
    <delete file="${sync.source.projectdir}/build/results.txt"/>
  </target>
  <!-- Private targets -->
  <!-- ============================================ -->
  <!-- Target: -init -->
  <!-- ============================================ -->
  <target name="-init" description="Private: Load main settings and display main menu">
    <!-- Main Menu -->
    <echo>Welcome to the site Update System</echo>
    <echo>----------------------------------------</echo>
    <echo>1) Development to Test</echo>
    <echo>2) Test to Production</echo>
    <echo>3) Apply db updates</echo>
    <echo>x) Exit</echo>
    <echo>----------------------------------------</echo>
    <input propertyName="choice" validargs="1,2,3,x">Give the corresponding arg</input>
    <if>
      <equals arg1="${choice}" arg2="1"/>
      <then>
        <property file="test.properties"/>
        <phingcall target="sync"/>
      </then>
      <elseif>
        <equals arg1="${choice}" arg2="2"/>
        <then>
          <property file="production.properties"/>
          <phingcall target="sync"/>
        </then>
      </elseif>
      <elseif>
        <equals arg1="${choice}" arg2="3"/>
        <then>
          <property file="database.properties"/>
          <phingcall target="db-update"/>
        </then>
      </elseif>
      <elseif>
        <equals arg1="${choice}" arg2="x"/>
        <then>
          <echo>bye bye!</echo>
          <php expression="exit();"/>
        </then>
      </elseif>
    </if>
    <tstamp/>
  </target>
  <!-- ============================================ -->
  <!-- Target: -update-changelog -->
  <!-- ============================================ -->
  <target name="-update-changelog" description="Private: Updates the changelog table with the applied updates">
    <echo msg="Applying update : ${filename}"/>
    <pdo url="${sync.database.type}:host=${sync.database.host};dbname=${sync.database.name}" userid="${sync.database.user}" password="${sync.database.password}" onerror="continue">INSERT INTO care_changelog( delta ) VALUES ( '${filename}'.sql );</pdo>
    <echo msg="INSERT INTO care_changelog( delta ) VALUES ( '${filename}' );"/>
  </target>
  <!-- ============================================ -->
  <!-- Target: -apply-changelog -->
  <!-- ============================================ -->
  <target name="-apply-changelog" description="Private: Applies the sql changeset">
    <pdo url="${sync.database.type}:host=${sync.database.host};dbname=${sync.database.name}" userid="${sync.database.user}" password="${sync.database.password}" onerror="abort">
      <transaction src="${sync.source.projectdir}/sql/${filename}"/>
    </pdo>
    <phingcall target="-update-changelog"/>
  </target>
  <!-- ============================================ -->
  <!-- Target: -list-applied-updates -->
  <!-- ============================================ -->
  <target name="-list-applied-updates" description="Private: Creates a list of available updates">
    <pdo url="${sync.database.type}:host=${sync.database.host};dbname=${sync.database.name}" userid="${sync.database.user}" password="${sync.database.password}" onerror="stop">
      <formatter type="plain" usefile="true" showheaders="false" coldelim="" outfile="${sync.source.projectdir}/build/results.txt"/>
         SELECT delta FROM `care_changelog` ORDER BY CAST(replace( substring(delta,7,3) , '.' , '' ) AS UNSIGNED);
      </pdo>
    </target>
    <!-- ============================================ -->
    <!-- Target: -sync-execute-task -->
    <!-- ============================================ -->
    <target name="-sync-execute-task" description="Private: Executes the synchronize task">
      <echo msg="Syncing files"/>
      <!-- <if>
        <not>
          <isset property="sync.verbose" />
        </not>
        <then>
          <property name="sync.verbose" value="true" override="true" />
          <echo message="The value of sync.verbose has been set to true" />
        </then>
      </if> -->
    <property name="sync.remote.auth" value="${sync.remote.user}@${sync.remote.host}"/>
    <taskdef name="sync" classname="phing.tasks.ext.FileSyncTask"/>
    <sync sourcedir="${sync.source.projectdir}" destinationdir="${sync.remote.auth}:${sync.destination.projectdir}" excludefile="${sync.exclude.file}" listonly="${listonly}" verbose="${sync.verbose}"/>
  </target>
</project>

the configuration files are as follows

[database.properties]
1
2
3
4
5
6
7
8
9
# database connection properties
# to be updated on a server basis
sync.source.projectdir=/home/xxxx/www/website/
sync.database.type=mysql
sync.database.name=database_name
sync.database.host=localhost
sync.database.user=db_user
sync.database.password=db_password
production.properties
[production.properties]
1
2
3
4
5
6
7
8
# production server properties
sync.source.projectdir=/var/www/website/
sync.destination.projectdir=/home/client/www/
sync.remote.host=hostname.com
sync.remote.user=remote_user
sync.destination.backupdir=hostname.com/website/backup
sync.exclude.file=/var/www/website/build/sync.exclude
sync.verbose=false 

test.properties is identical to production.properties except for the values.

[sync.exclude]
1
2
3
4
5
6
7
.*/
.buildpath
.project
.hgignore
cache
build
uploads

changesets.sql is the table containing the changesets

[changesets.sql]
1
2
3
4
5
CREATE TABLE IF NOT EXISTS `changelog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `delta` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Comments