dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 7861
committer: Bob Jolliffe <bobjolliffe@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2012-08-05 17:48:50 +0100
message:
  Useful miscellanea for working with DHIS uids and user passwords in postgres sql
added:
  resources/sql/pltcl.sql


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

=== added file 'resources/sql/pltcl.sql'
--- resources/sql/pltcl.sql	1970-01-01 00:00:00 +0000
+++ resources/sql/pltcl.sql	2012-08-05 16:48:50 +0000
@@ -0,0 +1,34 @@
+CREATE OR REPLACE LANGUAGE pltcl;
+
+-- Generates a valid dhis2 uid identifier
+CREATE OR REPLACE FUNCTION dhis_uid() RETURNS varchar(11) AS $$
+  set map "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
+  #-- first character must be a letter
+  set uid [string index $map [expr int(rand()*52)]]
+  #-- 10 more characters
+  for {set x 0} { $x<10 } {incr x} {
+    set uid $uid[string index $map [expr int(rand()*62)]]
+  }	
+  return $uid
+$$ LANGUAGE pltcl;
+
+-- Internal function reimplements Java String.hashCode() method and merges
+-- username with password
+CREATE OR REPLACE FUNCTION dhis_password_mash(varchar, varchar) RETURNS varchar AS $$
+  set userhash 0
+  set userlength [string length $1]
+  for {set x 0} {$x<$userlength} {incr x} {
+    scan [string index $1 $x] %c ch 
+    set userhash [expr 31*$userhash + $ch]
+  }
+  set merged $2\{$userhash\}
+  return $merged
+$$ LANGUAGE pltcl;
+
+-- Returns a dhis2 hash code for a username, password combination
+CREATE  OR REPLACE FUNCTION dhis_password_hash(varchar, varchar) 
+  RETURNS varchar AS  'select md5(dhis_password_mash($1, $2));'
+LANGUAGE SQL;
+
+-- testing
+select dhis_uid(), dhis_password_hash('admin','district');