code sauce

...

User Friendly Selects in Birt

| Comments

a very straightforward post on how to add the chosen plugin to select boxes in birt. first download the chosen plugin from https://harvesthq.github.io/chosen/ and extract the files in webcontent/birt/js. then edit webcontent/birt/pages/layout/FramesetFramgment.jsp and add

1
2
3
4
5
6
<link rel="stylesheet" href="birt/js/chosen.min.css">
<script src="birt/js/jquery-1.9.1.min.js" type="text/javascript"></script>
<script src="birt/js/chosen.jquery.min.js" type="text/javascript"></script>
<script>
var $jQuery = jQuery.noConflict();
</script>

and webcontent/birt/pages/parameter/ComboBoxParameterFragment.jsp and add the following code at the end of the file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<script>
var setInt;
$jQuery("#<%= encodedParameterName + "_selection"%>")
  .chosen({
      disable_search_threshold: 10,
      width: "400px"
  })
  .change(function() {
      birtParameterDialog.__refresh_cascade_select($("<%= encodedParameterName + "_selection"%>"));
      window.setTimeout(function() {
          $jQuery('.birtviewer_parameter_dialog_Select').trigger('chosen:updated');
      }, 100);
});
</script>

Change Charset for Mysql

| Comments

a quick stored procedure to get rid of the infamous :

1
Error Code: 1267. Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’

first of all i had to find those tables and databases, and to do so i used the following sql code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
  table_schema,
  TABLE_NAME,
  column_name,
  character_set_name,
  collation_name
FROM
  information_schema.COLUMNS
WHERE
  collation_name = latin1_swedish_ci
  AND
  table_schema = siis_qsut
ORDER BY
  table_schema,
  TABLE_NAME,
  ordinal_position

reaplce your_db_name with the name od the db you want to change the charset.

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
DELIMITER $$
 CREATE PROCEDURE change_charset_and_collation_for_tables()
     BEGIN
        DECLARE TABLE_NAME VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT ;
        DECLARE num_tables INT DEFAULT ;
        DECLARE cur CURSOR FOR
            SELECT
              t.TABLE_NAME
            FROM
              information_schema.TABLES t
            WHERE
              t.table_schema = your_db_name
              AND
              t.table_type=BASE TABLE;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
         OPEN cur;

         tables_loop: LOOP
             FETCH cur INTO TABLE_NAME;

            IF end_of_tables = 1 THEN
                LEAVE tables_loop;
            END IF;

            SET num_tables = num_tables + 1;

            SET @s = CONCAT(ALTER TABLE  , TABLE_NAME ,  CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci);

            PREPARE stmt FROM @s;
            EXECUTE stmt;
        END LOOP;

        CLOSE cur;
    END $$

Mysql: Updating Multiple Columns With Select

| Comments

sometimes even the simples of the things becomes..hard.
one of those things is updating multimple columns in mysql.
the standard should be something as easy as :

1
2
3
4
5
6
7
8
9
10
11
UPDATE
    table1
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y
FROM
    table1
INNER JOIN
    table2
ON
    table1.CommonColumn = table2.CommonColumn

instead mysql has a quirk that can be used, USING(). so the whole thing becomes something like

1
2
3
4
5
UPDATE
    table1 INNER JOIN table2 USING (CommonColumn)
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y

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 ;

Postgres Change Column Type From Boolean to Integer

| Comments

postgres change column type from boolean to integer

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE table_name ALTER column_name SET DEFAULT null;

ALTER TABLE table_name
ALTER column_name TYPE INTEGER
USING
CASE
  WHEN false THEN 0 ELSE 1
END;

ALTER TABLE table_name ALTER column_name SET DEFAULT 0;
COMMIT;

Fog With Mikrotik as a Dhcp Server

| Comments

having mikrotik 3.x give the correct tftp ip to the client can be a bit of a pain. after some hacking, googling and good luck, finally was able to do it. and it was not what the manual or helps int he forums say. you need to go to the terminal and issue the following commands :

1
/ip dhcp-server network set 0 boot-file-name=undionly.kkpxe next-server=10.10.0.2

and, they are not going to show in the network properties and neither the console..