SQL Server doesn't always adhere to its date/time formatting. Here's how to create your own.

This turns a class name (like LineItem) into a nice string (like "line item") line_item = LineItem.new puts line_item.class.name.underscore.humanize.lowcase #spits out "line item"

# Array#to_ranges # Converts an array of values (which must respond to #succ) to an array of ranges. For example, # [3,4,5,1,6,9,8].to_ranges => [1,3..6,8..9] class Array def to_ranges array = self.compact.uniq.sort ranges = [] if !array.empty? # Initialize the left and right endpoints of the range left, right = self.first, nil array.each do |obj| # If the right endpoint is set and obj is not equal to right's successor # then we need to create a range. if right && obj != right.succ ranges << Range.new(left,right) left = obj end right = obj end ranges << Range.new(left,right) end ranges end end

DOM ISO.v.0.3.0.7.bookmarklet.js bookmarklet for selecting and isolating an element on a page. two sections: section 1: Mouseover DOM, setup and handle mouse events and show information about element in informational div. Click to select, Any key to cancel. section 2: Element Isolation with help of XPath. prompt user for XPath expression e.g., //DIV[@id='post-body']. then use XPath to select all elements not(ancestor or descendant or self), then delete those elements. also ignore self-or-descendants of head and title. tools: Ruderman's javascript development environment: https://www.squarefree.com/bookmarklets/webdevel.html#jsenv Mielczarek's js to bookmarklet generator: http://ted.mielczarek.org/code/mozilla/bookmarklet.html (function() { //GLOBALS //globals for classMausWork var gSelectedElement; //currently only one selection var gHoverElement; //whatever element the mouse is over var gHovering=false; //mouse is over something var gObjArrMW=[]; //global array of classMausWork objects. for removing event listeners when done selecting. //extended var infoDiv; //currently just container for InfoDivHover, might add more here var infoDivHover; //container for hoverText text node. var hoverText; //show information about current element that the mouse is over //const EXPERIMENTAL_NEW_CODE=true; //debugging. new features. //START SetupDOMSelection(); //(Section 1) Element Selection function SetupDOMSelection() { { //setup event listeners //var pathx="//div | //span | //table | //td | //tr | //ul | //ol | //li | //p"; var pathx="//div | //span | //table | //th | //td | //tr | //ul | //ol | //li | //p | //iframe"; var selection=$XPathSelect(pathx); for(var element, i=0;element=selection(i);i++) { if(element.tagName.match(/^(div|span|table|td|tr|ul|ol|li|p)$/i)) //redundant check. { var m = new classMausWork(element); gObjArrMW.push(m); attachMouseEventListeners(m); } } document.body.addEventListener('mousedown',MiscEvent,false); document.body.addEventListener('mouseover',MiscEvent,false); document.body.addEventListener('mouseout',MiscEvent,false); document.addEventListener('keypress',MiscEvent,false); } { //setup informational div to show which element the mouse is over. infoDiv=document.createElement('div'); var s=infoDiv.style; s.position='fixed'; s.top='0'; s.right='0'; s.display='block'; s.width='auto'; s.padding='0px'; document.body.appendChild(infoDiv); infoDivHover=document.createElement('div'); s=infoDivHover.style; s.fontWeight='bold'; s.padding='3px'; s.Opacity='0.8'; s.borderWidth='thin'; s.borderStyle='solid'; s.borderColor='white'; s.backgroundColor='black'; s.color='white'; infoDiv.appendChild(infoDivHover); hoverText=document.createTextNode('selecting'); infoDivHover.appendChild(hoverText); } } function CleanupDOMSelection() { for(var m; m=gObjArrMW.pop(); ) { detachMouseEventListeners(m); } ElementRemove(infoDiv); document.body.removeEventListener('mousedown',MiscEvent,false); document.body.removeEventListener('mouseover',MiscEvent,false); document.body.removeEventListener('mouseout',MiscEvent,false); document.removeEventListener('keypress',MiscEvent,false); } function attachMouseEventListeners(c) { //c is object of class classMausWork c.element.addEventListener("mouseover",c.mouse_over,false); c.element.addEventListener("mouseout",c.mouse_out,false); c.element.addEventListener("mousedown",c.mouse_click,false); } function detachMouseEventListeners(c) { //c is object of class classMausWork c.resetElementStyle(); c.element.removeEventListener("mouseover",c.mouse_over,false); c.element.removeEventListener("mouseout",c.mouse_out,false); c.element.removeEventListener("mousedown",c.mouse_click,false); } //mouse event handling class for element, el. function classMausWork(element) { //store information about the element this object is assigned to handle. element, original style, etc. this.element=element; var elementStyle=element.getAttribute('style'); var target; this.mouse_over=function(ev) { if(gHovering)return; var e=element; var s=e.style; s.backgroundColor='yellow'; s.borderWidth='thin'; s.borderColor='lime'; s.borderStyle='solid'; InfoMSG(ElementInfo(e),'yellow','blue','yellow'); gHoverElement=e; gHovering=true; target=ev.target; ev.stopPropagation(); }; this.mouse_out=function(ev) { if(!gHovering)return; if(gHoverElement!=element ||ev.target!=target)return; var e=element; e.setAttribute('style',elementStyle); InfoMSG('-','white','black','white'); gHoverElement=null; gHovering=false; target=null; //ev.stopPropagation(); }; this.mouse_click=function(ev) { if(!gHovering)return; if(gHoverElement!=element ||ev.target!=target)return; var e=element; e.setAttribute('style',elementStyle); ev.stopPropagation(); CleanupDOMSelection(); gHoverElement=null; gHovering=false; target=null; if(ev.button==0) { gSelectedElement=e; ElementSelected(e); //finished selecting, cleanup then move to next part (section 2), element isolation. } }; this.resetElementStyle=function() { element.setAttribute('style',elementStyle); }; } function MiscEvent(ev) //keypress, and mouseover/mouseout/mousedown event on body. cancel selecting. { if(ev.type=='mouseout' && !gHovering) { InfoMSG('-','white','black','white'); } else if(ev.type=='mouseover' && !gHovering) { InfoMSG('cancel','yellow','red','yellow'); } else //keypress on document or mousedown on body, cancel ops. { CleanupDOMSelection(); } } function InfoMSG(text,color,bgcolor,border) { var s=infoDivHover.style; if(color)s.color=color; if(bgcolor)s.backgroundColor=bgcolor; if(border)s.borderColor=border; if(text)hoverText.data=text; } //(Section 2) Element Isolation function ElementSelected(element) //finished selecting element. setup string to prompt user. { PromptUserXpath(ElementInfo(element)); } function PromptUserXpath(defaultpath) //prompt user, isolate element. { var userpath = prompt("XPath of elements to isolate : ", defaultpath); if(userpath && userpath.length>0) { var addPredicate = "[count(./ancestor-or-self::head)=0][count(./ancestor-or-self::title)=0]"; //exclude head & title elements from selection so they aren't removed var addPath = "//script | //form | //object | //embed"; //include these elements in selection for removal var pathx=TransformXPath_NoAncestorDescendentSelf(userpath, addPredicate, addPath); //the xpath selection of all elements to be removed/deleted. try { var element; var elements=$XPathSelect(pathx); for(var i=0;element=elements(i);i++) { if(!element.nodeName.match(/^(head|title)$/i)) //redundant check. { ElementRemove(element); } } } catch(err) { alert("wtf: "+err); } } } //support function $XPathSelect(p, context) { if (!context) context = document; var i, arr = [], xpr = document.evaluate(p, context, null, XPathResult.UNORDERED_NODE_SNAPSHOT_TYPE, null); return function(x) { return xpr.snapshotItem(x); }; //closure. wooot! returns function-type array of elements (usually elements, or something else depending on the xpath expression). } function ElementRemove(e) { if(e)e.parentNode.removeChild(e); } function ElementInfo(element) { var txt=''; if(element) { txt=element.tagName.toLowerCase(); //txt=element.tagName; txt=attrib(txt,element,'id'); txt=attrib(txt,element,'class'); txt='//'+txt; } return txt; function attrib(t,e,a) { if(e.hasAttribute(a)) { t+="[@"+a+"='"+e.getAttribute(a)+"']"; } return t; } } //function to 'invert' the XPath by selecting all elements that are not ancestor and not descendent and not self. function TransformXPath_NoAncestorDescendentSelf(u, includePredicates, includePaths) { //sample input (u): //div[@class='sortbox'] //sample output //*[ not(./descendant-or-self::*=//div[@class='sortbox'])][ not(./ancestor-or-self::*=//div[@class='sortbox'])] //sample output with additional conditions: //*[ not(./descendant-or-self::*=//div[@class='sortbox'])][ not(./ancestor-or-self::*=//div[@class='sortbox'])][count(./ancestor-or-self::head)=0][count(./ancestor-or-self::title)=0] //obsolete method. much faster but can only be used for limited types of (simple) xpath expressions -- unlike the current version, which should be able to convert any xpath. //input: table[@id='topbar'] //output: //*[not(./descendant-or-self::table[@id='topbar']) and not(./ancestor-or-self::table[@id='topbar'])] //output (alternative): //*[count(./descendant-or-self::table[@id='topbar'])=0 and count(./ancestor-or-self::table[@id='topbar'])=0] var o1= './descendant-or-self::*='+gr(u); o1= 'not' + gr(o1); o1= nt(o1); var o2= './ancestor-or-self::*='+gr(u); o2= 'not' + gr(o2); o2= nt(o2); var o= '//*'+o1+o2; if(includePredicates && includePredicates.length>0) o += includePredicates; if(includePaths && includePaths.length>0) o += ' | ' + includePaths; return o; function nt(term){return wrap(term,'[]');} //node test; predicate - enclose with bracket. function gr(term){return wrap(term,'()');} //group - parenthesize. function wrap(term, enclosure){return enclosure.charAt(0)+term+enclosure.charAt(1);} } })();

PHP // Resolves a TinyURL.com encoded URL to its source. // Example: reverse_tinyurl('http://tinyurl.com/2ocfun') => "http://logankoester.com" function reverse_tinyurl($url) { $url = explode('.com/', $url); $url = 'http://preview.tinyurl.com/' . $url[1]; $preview = file_get_contents($url); preg_match('/redirecturl" href="(.*)">/', $preview, $matches); return $matches[1]; }

using System; using System.Collections.Generic; using System.IO.Compression; using System.IO; using System.Collections; namespace Utilities { class Compression { public static byte[] Compress(byte[] data) { MemoryStream ms = new MemoryStream(); DeflateStream ds = new DeflateStream(ms, CompressionMode.Compress); ds.Write(data, 0, data.Length); ds.Flush(); ds.Close(); return ms.ToArray(); } public static byte[] Decompress(byte[] data) { const int BUFFER_SIZE = 256; byte[] tempArray = new byte[BUFFER_SIZE]; List tempList = new List(); int count = 0, length = 0; MemoryStream ms = new MemoryStream(data); DeflateStream ds = new DeflateStream(ms, CompressionMode.Decompress); while ((count = ds.Read(tempArray, 0, BUFFER_SIZE)) > 0) { if (count == BUFFER_SIZE) { tempList.Add(tempArray); tempArray = new byte[BUFFER_SIZE]; } else { byte[] temp = new byte[count]; Array.Copy(tempArray, 0, temp, 0, count); tempList.Add(temp); } length += count; } byte[] retVal = new byte[length]; count = 0; foreach (byte[] temp in tempList) { Array.Copy(temp, 0, retVal, count, temp.Length); count += temp.Length; } return retVal; } } }

SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); Date date = sdf.parse(strDate); Calendar cal = Calendar.getInstance(); cal.setTime(date);

based on wikipedia public interface ISetPixel { void SetPixel(Point point); } public partial class Algorithms2D { public delegate void SetPixel(Point point); public static void Line(Point p0,Point p1,G plot) where G:ISetPixel { int x0=p0.X; int y0=p0.Y; int x1=p1.X; int y1=p1.Y; bool steep=abs(y1-y0)>abs(x1-x0); if (steep) { swap(ref x0,ref y0); swap(ref x1,ref y1); } if (x0>x1) { swap(ref x0,ref x1); swap(ref y0,ref y1); } int deltax=x1-x0; int deltay=abs(y1-y0); int error=-deltax/2; int ystep; int y=y0; if (y00) { y=y+ystep; error=error-deltax; } } } struct CSetPixel:ISetPixel { public CSetPixel(SetPixel setPixel) { this.setPixel=setPixel; } SetPixel setPixel; #region ISetPixel Members public void SetPixel(Point point) { setPixel(point); } #endregion } public static void Line(Point p0,Point p1,SetPixel plot) { Line(p0,p1,new CSetPixel(plot)); } private static int abs(int p) { return Math.Abs(p); } private static void swap(ref T x0,ref T y0) { T z=x0; x0=y0; y0=z; } } unit tests (c# 3.0): [TestFixture] public class Line { [Test] public void LineDiagonal() { List l = new List(); Algorithms2D.Line(new Point(0,0),new Point(3,3),z=>l.Add(z)); Assert.AreEqual(3, l.Count); Assert.AreEqual(new Point(0, 0), l[0]); Assert.AreEqual(new Point(1, 1), l[1]); Assert.AreEqual(new Point(2, 2), l[2]); } [Test] public void Line45() { List l = new List(); Algorithms2D.Line(new Point(0, 0),new Point(6, 3), z => l.Add(z)); Assert.AreEqual(6, l.Count); Assert.AreEqual(new Point(0, 0), l[0]); Assert.AreEqual(new Point(1, 0), l[1]); Assert.AreEqual(new Point(2, 1), l[2]); Assert.AreEqual(new Point(3, 1), l[3]); Assert.AreEqual(new Point(4, 2), l[4]); Assert.AreEqual(new Point(5, 2), l[5]); } }

For more details on how to call this script from php if your server doesn't support python, click http://just-tech.blogspot.com/2007/01/python-html-tables-to-mediawiki.html import HTMLParser, re, sys class html2wiki(HTMLParser.HTMLParser): def __init__(self): HTMLParser.HTMLParser.__init__(self) self.wiki = '' # The Wiki text self.wikirow = '' # The current Wiki row of table being constructed from HTML self.inTD = 0 # Used to track if we are inside or outside a ... tag. self.inTR = 0 # Used to track if we are inside or outside a ... tag. self.re_multiplespaces = re.compile('\s+') # regular expression used to remove spaces in excess self.rowCount = 0 # output row counter. self.rowspan = '' self.colspan = '' self.linebreak = ' ' self.data = '' self.prop = '' def handle_starttag(self, tag, attrs): if tag == 'table': self.start_table() elif tag == 'tr': self.start_tr() elif tag == 'td': self.start_td(attrs) def handle_endtag(self, tag): if tag == 'table': self.end_table(); elif tag == 'tr': self.end_tr() elif tag == 'td': self.end_td() def start_table(self): self.wiki += '{| border=1' + self.linebreak self.wiki += '|-' + self.linebreak def end_table(self): self.wiki += '|}' + self.linebreak def start_tr(self): if self.inTR: self.end_tr() # implies self.inTR = 1 def end_tr(self): if self.inTD: self.end_td() # implies self.inTR = 0 if len(self.wikirow) > 0: self.wiki += self.wikirow self.wiki += '|-' + self.linebreak self.wikirow = '' self.rowCount += 1 def start_td(self, attrs): if not self.inTR: self.start_tr() # implies self.data = '' self.prop = '' self.rowspan = '' self.colspan = '' for key, value in attrs: if key == 'rowspan': self.rowspan = value elif key == 'colspan': self.colspan = value self.inTD = 1 def end_td(self): if self.inTD: self.wikirow += '| ' + self.prop + self.re_multiplespaces.sub(' ',self.data.replace('\t',' ').replace(self.linebreak,'').replace('\r','').replace('"','""'))+ self.linebreak; self.data = '' self.inTD = 0 def handle_data(self, data): if self.inTD: if data.strip() != '': self.prop = '' if self.rowspan != '': self.prop = ' rowspan = '+self.rowspan if self.colspan != '': self.prop += ' colspan = '+self.colspan if self.prop: self.prop += ' | ' self.data += data if __name__ == '__main__': parser = html2wiki() if len(sys.argv) == 2: in_file = open(sys.argv[1],"r") text = in_file.read() parser.feed(text) in_file.close() print parser.wiki else: print 'Argument - filename required'

A simple time based cache build around a map store. import java.util.Map; import java.util.WeakHashMap; /** * Simple time-based cache. */ public class SimpleCache { private long maxAge; private Map store; /** * Instanciate a cache with max age of 1 hour and a WeakHashMap as store. * @see java.util.WeakHashMap */ public SimpleCache() { this.maxAge = 1000 * 60 * 60; this.store = new WeakHashMap(); } /** * @param maxAge maximum age of an entry in milliseconds * @param store map to hold entries */ public SimpleCache(long maxAge, Map store) { this.maxAge = maxAge; this.store = store; } /** * Cache an object. * @param key unique identifier to retrieve object * @param value object to cache */ public void put(Object key, Object value) { store.put(key, new Item(value)); } /** * Fetch an object. * @param key unique identifier to retrieve object * @return an object or null in case it isn't stored or it expired */ public Object get(Object key) { Item item = getItem(key); return item == null ? null : item.payload; } /** * Fetch an object or store and return output of callback. * @param key unique identifier to retrieve object * @param block code executed when object not in cache * @return an object */ public synchronized Object get(Object key, Callback block) { Item item = getItem(key); if (item == null) { Object value = block.execute(); item = new Item(value); store.put(key, item); } return item.payload; } /** * Remove an object from cache. * @param key unique identifier to retrieve object */ public void remove(Object key) { store.remove(key); } /** * Get an item, if it expired remove it from cache and return null. * @param key unique identifier to retrieve object * @return an item or null */ private Item getItem(Object key) { Item item = (Item) store.get(key); if (item == null) { return null; } if (System.currentTimeMillis() - item.birth > maxAge) { store.remove(key); return null; } return item; } /** * Value container. */ private static class Item { long birth; Object payload; Item(Object payload) { this.birth = System.currentTimeMillis(); this.payload = payload; } } /** * A visitor interface. */ public static interface Callback { Object execute(); } } And a couple of junit tests: import java.util.HashMap; import junit.framework.TestCase; public class SimpleCacheTest extends TestCase { public void testPutGet () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); c.put("key1", "value1"); assertEquals("value1", c.get("key1")); c.put("key1", "value1.0"); assertEquals("value1.0", c.get("key1")); c.put("key2", "value2"); assertEquals("value2", c.get("key2")); assertEquals("value1.0", c.get("key1")); } public void testMaxAge () throws InterruptedException { SimpleCache c = new SimpleCache(1000, new HashMap()); c.put("key1", "value1"); assertEquals("value1", c.get("key1")); Thread.sleep(1500); assertNull(c.get("key1")); c.put("key2", "value2"); Thread.sleep(750); c.put("key3", "value3"); Thread.sleep(750); assertNull(c.get("key2")); assertNotNull(c.get("key3")); Thread.sleep(750); assertNull(c.get("key3")); } public void testRemove () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); c.remove("key"); assertNull(c.get("key")); c.put("key", "value"); assertNotNull(c.get("key")); c.remove("key"); assertNull(c.get("key")); } public void testCallBack () { SimpleCache c = new SimpleCache(Long.MAX_VALUE, new HashMap()); assertEquals("value1", c.get("key1", new SimpleCache.Callback() { public Object execute() { return "value1"; } })); assertEquals("value1", c.get("key1")); // again with a new callback (value) c.get("key1", new SimpleCache.Callback() { public Object execute() { return "value2"; } }); assertEquals("value1", c.get("key1")); } }

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )

#!/bin/env ruby # pg_diff - compare two PostgreSQL database schemas # # URL: http://snippets.dzone.com/posts/show/949 # # This is a simple approach to track database schema changes in PostgreSQL. # In some way it is similar to diff program, finding out structure changes # and results in SQL script to upgrade to new schema. # # Differences are tracked on schemas, domains, sequences, views, tables, indices, constraints, rules, functions, triggers. # Two objects with the same name are considered equal if they have the same definitions. # # Missing features: tracking of ownership, user rights, object dependencies, table inheritance, type casts, aggregates, operators. # # Usage: # ./pg_diff dbname=db_v03_dev dbname=db_v04_dev # # Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding (20051127 snapshot). # # This software is released under MIT License # # Copyright (c) 2005 Dmitry Severin # # Permission is hereby granted, free of charge, to any person obtaining a copy # of this software and associated documentation files (the "Software"), to deal # in the Software without restriction, including without limitation the rights # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell # copies of the Software, and to permit persons to whom the Software is # furnished to do so, subject to the following conditions: # # The above copyright notice and this permission notice shall be included in # all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN # THE SOFTWARE. # require 'postgres' module PostgreSqlSchema class Attribute attr_accessor :name, :type_def, :notnull, :default def initialize(name, typedef, notnull, default) @name = name @type_def = typedef @notnull = notnull @default = default end def definition out = [' ', @name, @type_def] out << 'NOT NULL' if @notnull out << 'DEFAULT ' + @default if @default out.join(" ") end def == (other) definition == other.definition end end class Table attr_accessor :table_name, :schema, :attributes, :constraints, :indexes def initialize(conn, schema, table_name) @schema = schema @table_name = table_name @attributes = {} @constraints = {} @indexes = {} @atlist = [] att_query = <<-EOT select attname, format_type(atttypid, atttypmod) as a_type, attnotnull, pg_get_expr(adbin, attrelid) as a_default from pg_attribute left join pg_attrdef on (adrelid = attrelid and adnum = attnum) where attrelid = '#{schema}.#{table_name}'::regclass and not attisdropped and attnum > 0 order by attnum EOT conn.query(att_query).each do |row| attname = row[0] @attributes[attname] = Attribute.new(attname, row[1], row[2], row[3]) @atlist << attname end ind_query = <<-EOT select indexrelid::regclass as indname, pg_get_indexdef(indexrelid) as def from pg_index where indrelid = '#{schema}.#{table_name}'::regclass and not indisprimary EOT conn.query(ind_query).each do |row| @indexes[row[0]] = row[1] end cons_query = <<-EOT select conname, pg_get_constraintdef(oid) from pg_constraint where conrelid = '#{schema}.#{table_name}'::regclass EOT conn.query(cons_query).each do |row| @constraints[row[0]] = row[1] end @constraints.keys.each do |cname| @indexes.delete("#{schema}.#{cname}") if has_index?(cname) end end def has_attribute?(name) @attributes.has_key?(name) end def has_index?(name) @indexes.has_key?(name) || @indexes.has_key?("#{schema}.#{name}") end def has_constraint?(name) @constraints.has_key?(name) end def table_creation out = ["CREATE TABLE #{name} ("] stmt = [] @atlist.each do |attname| stmt << @attributes[attname].definition end out << stmt.join(",\n") out << ");" out.join("\n") end def name "#{schema}.#{table_name}" end def constr_creation out = [] @constraints.each do |n, c| out << "ALTER TABLE #{name} ADD CONSTRAINT #{n} #{c};" end out.join("\n") end def index_creation out = [] @indexes.values.each do |c| out << (c+";") end out.join("\n") end end class Sequence def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" end def definition "CREATE SEQUENCE #{@name} ;" end end class View attr_reader :def, :name def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" view_qery = <<-EOT SELECT pg_catalog.pg_get_viewdef('#{@name}'::regclass, true) EOT @def = conn.query(view_qery)[0][0] end def definition "CREATE VIEW #{@name} AS #{@def}" end end class Database attr_accessor :tables, :views, :sequences, :schemas, :domains, :rules, :functions, :triggers def initialize(conn) cls_query = <<-EOT SELECT n.nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','S','v') AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ORDER BY 1,2; EOT @views = {} @tables = {} @sequences = {} @schemas = {} @domains = {} @functions = {} @rules = {} @triggers = {} conn.query(cls_query).each do |row| schema, relname, relkind = row case relkind when 'r' then @tables["#{schema}.#{relname}"] = Table.new(conn, schema, relname) when 'v' then @views ["#{schema}.#{relname}"] = View.new(conn, schema, relname) when 'S' then @sequences["#{schema}.#{relname}"] = Sequence.new(conn, schema, relname) end end domain_qry = <<-EOT SELECT n.nspname, t.typname, pg_catalog.format_type(t.typbasetype, t.typtypmod) || ' ' || CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null' WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault ELSE '' END FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'd' ORDER BY 1, 2 EOT conn.query(domain_qry).each do |row| @domains["#{row[0]}.#{row[1]}"] = row[2] end schema_qry = <<-EOT select nspname from pg_namespace EOT conn.query(schema_qry).each do |row| @schemas[row[0]]=row[0] end func_query = <<-EOT SELECT proname AS function_name , nspname AS namespace , lanname AS language_name , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment , proargtypes AS function_args , proargnames AS function_arg_names , prosrc AS source_code , proretset AS returns_set , prorettype AS return_type, provolatile, proisstrict, prosecdef FROM pg_catalog.pg_proc JOIN pg_catalog.pg_language ON (pg_language.oid = prolang) JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid) JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid) WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema' AND proname != 'plpgsql_call_handler' AND proname != 'plpgsql_validator' EOT conn.exec(func_query).result.each do |tuple| func = Function.new(conn, tuple) @functions[func.signature] = func end rule_query = <<-EOT select schemaname || '.' || tablename || '.' || rulename as rule_name, schemaname || '.' || tablename as tab_name, rulename, definition from pg_rules where schemaname !~ 'pg_catalog|information_schema' EOT conn.exec(rule_query).result.each do |tuple| @rules[tuple['rule_name']] = Rule.new(tuple['tab_name'], tuple['rulename'], tuple['definition']) end trigger_query = <<-EOT select nspname || '.' || relname as tgtable, tgname, pg_get_triggerdef(t.oid) as tg_def from pg_trigger t join pg_class c ON (tgrelid = c.oid ) JOIN pg_namespace n ON (c.relnamespace = n.oid) where not tgisconstraint and nspname !~ 'pg_catalog|information_schema' EOT conn.exec(trigger_query).result.each do |tuple| @triggers[tuple['tgtable'] + "." + tuple['tgname']] = Trigger.new(tuple['tgtable'], tuple['tgname'], tuple['tg_def']) end end end class Rule attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df end def == (other) other.definition == definition end end class Trigger attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df + ";" end def == (other) other.definition == definition end end class Function def initialize(conn, tuple) @name = tuple['namespace'] + "." + tuple['function_name'] @language = tuple['language_name'] @src = tuple['source_code'] @returns_set = tuple['returns_set'] @return_type = format_type(conn, tuple['return_type']) @tipes = tuple['function_args'].split(" ") if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/ @arnames = $1.split(',') elsif tuple['function_arg_names'].is_a? Array # my version of ruby-postgres @arnames = tuple['function_arg_names'] else @arnames = [""] * @tipes.length end alist = [] @tipes.each_with_index do |typ,idx| alist << (@arnames[idx] +" " + format_type(conn, typ)) end @arglist = alist.join(" , ") @strict = tuple['proisstrict'] ? ' STRICT' : '' @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : '' @volatile = case tuple['provolatile'] when 'i' then ' IMMUTABLE' when 's' then ' STABLE' else '' end end def signature "#{@name}(#{@arglist})" end def definition <<-EOT CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef}; EOT end def == (other) definition == other.definition end def format_type(conn, oid) t_query = <<-EOT SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace) WHERE pg_type.oid = EOT return conn.query(t_query + oid.to_s)[0][0] end end class Diff def initialize(old_db_spec, new_db_spec) @old_conn = PGconn.new(old_db_spec) @new_conn = PGconn.new(new_db_spec) @sections = [ :triggers_drop, :rules_drop, :functions_drop, :indices_drop , :constraints_drop, :views_drop, :sequences_drop , :tables_drop , :domains_drop , :schemas_drop , :schemas_create, :domains_create, :sequences_create, :tables_create , :table_changes , :views_create , :functions_create , :rules_create , :triggers_create , :indices_create, :constraints_create ] @script = {} @sections.each {|s| @script[s] = []} end def run_compare @old_database = Database.new(@old_conn) @new_database = Database.new(@new_conn) compare_schemas compare_domains compare_sequences compare_triggers_drop compare_rules_drop compare_views_drop compare_table_attrs compare_views_create compare_functions compare_rules_create compare_triggers_create compare_table_constraints end def add_script(section, statement) @script[section] << statement end def compare_schemas @old_database.schemas.keys.each do |name| add_script(:schemas_drop , "DROP SCHEMA #{name};") unless @new_database.schemas.has_key?(name) end @new_database.schemas.keys.each do |name| add_script(:schemas_create , "CREATE SCHEMA #{name};") unless @old_database.schemas.has_key?(name) end end def compare_domains @old_database.domains.keys.each do |name| add_script(:domains_drop , "DROP DOMAIN #{name} CASCADE;") unless @new_database.domains.has_key?(name) end @new_database.domains.each do |name, df| add_script(:domains_create , "CREATE DOMAIN #{name} AS #{df};") unless @old_database.domains.has_key?(name) old_domain = @old_database.domains[name] if old_domain && old_domain != df add_script(:domains_drop, "DROP DOMAIN #{name} CASCADE;") add_script(:domains_create, "-- [changed domain] :") add_script(:domains_create, "-- OLD: #{old_domain}") add_script(:domains_create, "CREATE DOMAIN #{name} AS #{df};") end end end def compare_sequences @old_database.sequences.keys.each do |name| add_script(:sequences_drop , "DROP SEQUENCE #{name} CASCADE;") unless @new_database.sequences.has_key?(name) end @new_database.sequences.keys.each do |name| add_script(:sequences_create , "CREATE SEQUENCE #{name};") unless @old_database.sequences.has_key?(name) end end def compare_functions @old_database.functions.keys.each do |name| add_script(:functions_drop , "DROP FUNCTION #{name} CASCADE;") unless @new_database.functions.has_key?(name) end @new_database.functions.each do |name, func| add_script(:functions_create , func.definition) unless @old_database.functions.has_key?(name) old_function = @old_database.functions[name] if old_function && old_function.definition != func.definition add_script(:functions_create , '-- [changed function] :') add_script(:functions_create , '-- OLD :') add_script(:functions_create , old_function.definition.gsub(/^/, "--> ") ) add_script(:functions_create , func.definition) end end end def compare_rules_drop @old_database.rules.each do |name, rule| add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") unless @new_database.rules.has_key?(name) end end def compare_rules_create @new_database.rules.each do |name, rule| add_script(:rules_create , rule.definition) unless @old_database.rules.has_key?(name) old_rule = @old_database.rules[name] if old_rule && old_rule != rule add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") add_script(:rules_create , "-- [changed rule] :") add_script(:rules_create , "-- OLD: #{old_rule.definition}") add_script(:rules_create , rule.definition ) end end end def compare_triggers_drop @old_database.triggers.each do |name, trigger| add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") unless @new_database.triggers.has_key?(name) end end def compare_triggers_create @new_database.triggers.each do |name, trigger| add_script(:triggers_create , trigger.definition) unless @old_database.triggers.has_key?(name) old_trigger = @old_database.triggers[name] if old_trigger && old_trigger != trigger add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") add_script(:triggers_create , "-- [changed trigger] :") add_script(:triggers_create , "-- OLD #{old_trigger.definition}") add_script(:triggers_create , trigger.definition) end end end def compare_views_drop @old_database.views.keys.each do |name| add_script(:views_drop , "DROP VIEW #{name};") unless @new_database.views.has_key?(name) end end def compare_views_create @new_database.views.each do |name, df| add_script(:views_create , df.definition) unless @old_database.views.has_key?(name) old_view = @old_database.views[name] if old_view && df.definition != old_view.definition add_script(:views_drop , "DROP VIEW #{name};") add_script(:views_create , "-- [changed view] :") add_script(:views_create , "-- #{old_view.definition.gsub(/\n/, ' ')}") add_script(:views_create , df.definition) end end end def compare_table_attrs @old_database.tables.each do |name, table| add_script(:tables_drop, "DROP TABLE #{name} CASCADE;") unless @new_database.tables.has_key?(name) end @to_compare = [] @new_database.tables.each do |name, table| unless @old_database.tables.has_key?(name) add_script(:tables_create , table.table_creation) add_script(:indices_create , table.index_creation) unless table.indexes.empty? @to_compare << name else diff_attributes(@old_database.tables[name], table) diff_indexes(@old_database.tables[name], table) @to_compare << name end end end def compare_table_constraints @c_check = [] @c_primary = [] @c_unique = [] @c_foreign = [] @to_compare.each do |name| if @old_database.tables[name] diff_constraints(@old_database.tables[name], @new_database.tables[name]) else @new_database.tables[name].constraints.each do |cname, cdef| add_cnstr(name, cname, cdef) end end end @script[:constraints_create] += @c_check @script[:constraints_create] += @c_primary @script[:constraints_create] += @c_unique @script[:constraints_create] += @c_foreign end def output out = [] @sections.each do |sect| if @script[sect].empty? out << "-- [SKIP SECTION : #{sect.to_s.upcase}] : no changes\n" else out << "-- [START SECTION : #{sect.to_s.upcase}]" out += @script[sect] out << "-- [END SECTION : #{sect.to_s.upcase}]\n" end end out.join("\n") end def diff_attributes(old_table, new_table) dropped = [] added = [] changed = [] old_table.attributes.keys.each do |attname| if new_table.has_attribute?(attname) changed << attname if old_table.attributes[attname] != new_table.attributes[attname] else dropped << attname end end new_table.attributes.keys.each do |attname| added << attname unless old_table.has_attribute?(attname) end add_script(:table_changes , "-- [#{old_table.name}] dropped attributes") unless dropped.empty? dropped.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} DROP COLUMN #{attname} CASCADE;") end add_script(:table_changes , "-- [#{old_table.name}] added attributes") unless added.empty? added.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} ADD COLUMN #{new_table.attributes[attname].definition};") end add_script(:table_changes , "-- [#{old_table.name}] changed attributes") unless changed.empty? changed.each do |attname| old_att = old_table.attributes[attname] new_att = new_table.attributes[attname] add_script(:table_changes , "-- attribute: #{attname}") add_script(:table_changes , "-- OLD : #{old_att.definition}") add_script(:table_changes , "-- NEW : #{new_att.definition}") if old_att.type_def != new_att.type_def add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} TYPE #{new_att.type_def};") end if old_att.default != new_att.default if new_att.default.nil? add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} DROP DEFAULT;") else add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} SET DEFAULT #{new_att.default};") end end if old_att.notnull != new_att.notnull add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} #{new_att.notnull ? 'SET' : 'DROP'} NOT NULL;") end end end def diff_constraints(old_table, new_table) dropped = [] added = [] old_table.constraints.keys.each do |conname| if new_table.has_constraint?(conname) if old_table.constraints[conname] != new_table.constraints[conname] dropped << conname added << conname end else dropped << conname end end new_table.constraints.keys.each do |conname| added << conname unless old_table.has_constraint?(conname) end dropped.each do |name| add_script(:constraints_drop , "ALTER TABLE #{old_table.name} DROP CONSTRAINT #{name};") end added.each do |name| add_cnstr(old_table.name, name, new_table.constraints[name]) end end def add_cnstr(tablename, cnstrname, cnstrdef) c_string = "ALTER TABLE #{tablename} ADD CONSTRAINT #{cnstrname} #{cnstrdef} ;" case cnstrdef when /^CHECK / then @c_check << c_string when /^PRIMARY / then @c_primary << c_string when /^FOREIGN / then @c_foreign << c_string when /^UNIQUE / then @c_unique << c_string end end def diff_indexes(old_table, new_table) dropped = [] added = [] old_table.indexes.keys.each do |name| if new_table.has_index?(name) if old_table.indexes[name] != new_table.indexes[name] dropped << name added << name end else dropped << name end end new_table.indexes.each do |name| added << name unless old_table.has_index?(name) end dropped.each do |name| add_script(:indices_drop , "DROP INDEX #{name};") end added.each do |name| add_script(:indices_create , (new_table.indexes[name] + ";")) if new_table.indexes[name] end end end end def parse_conn_params(str) h = {} str.split(/:/).each{|pair| key, value = pair.split('=', 2); h[key]=value} h end diff = PostgreSqlSchema::Diff.new(parse_conn_params(ARGV[0]), parse_conn_params(ARGV[1]) ) diff.run_compare puts diff.output

/// /// Algorithm for calculating the date of Easter Sunday /// (Meeus/Jones/Butcher Gregorian algorithm) /// http://en.wikipedia.org/wiki/Computus#Meeus.2FJones.2FButcher_Gregorian_algorithm /// /// A valid Gregorian year /// Easter Sunday public static DateTime EasterDate(int year) { int Y = year; int a = Y % 19; int b = Y / 100; int c = Y % 100; int d = b / 4; int e = b % 4; int f = (b + 8) / 25; int g = (b - f + 1) / 3; int h = (19 * a + b - d - g + 15) % 30; int i = c / 4; int k = c % 4; int L = (32 + 2 * e + 2 * i - h - k) % 7; int m = (a + 11 * h + 22 * L) / 451; int month = (h + L - 7 * m + 114) / 31; int day = ((h + L - 7 * m + 114) % 31) + 1; DateTime dt = new DateTime(year, month, day); return dt; } Easter Monday = Easter Sunday + 1 Ascension Day = Easter Sunday + 39 Pentecost Sunday = Easter Sunday + 49 Pentecost Monday = Easter Sunday + 50

class Array def swap!(a,b) self[a], self[b] = self[b], self[a] self end end You can now do stuff like.. [1,2,3,4].swap!(2,3) # = [1,2,4,3] etc.. Many thanks to Sam Stephenson and technoweenie for their suggestions.