Details

Applies to
MySQL.py.parser
Purpose
Extend table creation abilities
Description
This patch adds the ability to create tables by massaging SQL queries. For example this query finds customer addresses from a CRM, formats them into a table and makes one column a link to google maps highlighting the physical location of the customer. 

DBDATA localhost,davecrm,crmread,DatabasePassword
QUERY select concat(concat("http://maps.google.com/maps?f=q&hl=en&q=", replace(shipping_address_street, "\n", ", "), ", ", shipping_address_city, ", New Zealand"), "|", concat("Map to ", name,  ", ", Shipping_address_street)) from accounts where NOT shipping_address_street = "" and NOT shipping_address_city = "" and NOT deleted = "1" order by name;
WMLCOLUMN 1

returns a table of links that looks like this

 http://maps.google.com/maps?f=q&hl=en&q=320%20Manchester%20St,%20Christchurch,%20New%20Zealand   "Map to Total Team, 320 Manchester Street" 

Patch

   1 --- MySQL.py.orig	2007-03-27 15:26:00.000000000 +1200
   2 +++ MySQL.py	2007-03-27 15:51:40.000000000 +1200
   3 @@ -16,6 +16,7 @@
   4  """
   5  
   6  import sys
   7 +import string,re
   8  from MoinMoin import wikiutil, config
   9  import MySQLdb
  10  import re
  11 @@ -27,6 +28,7 @@
  12  _re_evenattr=re.compile("EVENATTR (?P<EVENATTR>.*)")
  13  _re_oddattr=re.compile("ODDATTR (?P<ODDATTR>.*)")
  14  _re_wikicol=re.compile("WIKICOLUMN (?P<WIKICOLUMN>.*)")
  15 +_re_wmlcol=re.compile("WMLCOLUMN (?P<WMLCOLUMN>.*)")
  16  _re_htmlcol=re.compile("HTMLCOLUMN (?P<HTMLCOLUMN>.*)")
  17  _re_mailcol=re.compile("MAILCOLUMN (?P<MAILCOLUMN>.*)")
  18  _re_hidden=re.compile("HIDDENCOLUMN (?P<HIDDENCOLUMN>.*)")
  19 @@ -36,9 +38,10 @@
  20  <img src="/wiki/rightsidebar/img/moin-www.png"
  21  alt="[www]" width="11" height="11">%s</a>'''
  22  _format_wikiref=' <a href="%s">%s</a>'
  23 -_format_mailto='<a href="mailto:%s">%s</a>'
  24 +_format_wmlref=' <a href="%s">%s</a>'
  25 +_format_mailto=' <a href="mailto:%s">%s</a>'
  26  
  27  class Preference:
  28      def __init__(self):
  29 @@ -51,6 +54,7 @@
  30          self.header=''
  31          self.html=''
  32          self.wiki=''
  33 +        self.wml=''
  34          self.mail=''
  35          self.hidden=''
  36  
  37 @@ -91,6 +95,8 @@
  38  	output.append(params.linebreak)
  39          output.append('[WIKICOLUMN columnnumber1,columnnumber2,...,...]')
  40  	output.append(params.linebreak)
  41 +        output.append('[WMLCOLUMN columnnumber1,columnnumber2,...,...] use | to separate two columns, aa|bb will become a link to aa labelled as bb')
  42 +	output.append(params.linebreak)
  43          output.append('[MAILCOLUMN columnnumber1,columnnumber2,...,...]')
  44  	output.append(params.linebreak)
  45          output.append('[HIDDENCOLUMN columnnumber1,columnnumber2,...,...]')
  46 @@ -148,6 +154,10 @@
  47          if match and match.groupdict().has_key('WIKICOLUMN'):
  48              params.wiki=match.group('WIKICOLUMN').split(',')
  49          
  50 +        match=_re_wmlcol.search(parameterstring)
  51 +        if match and match.groupdict().has_key('WMLCOLUMN'):
  52 +            params.wml=match.group('WMLCOLUMN').split(',')
  53 +
  54          match=_re_htmlcol.search(parameterstring)
  55          if match and match.groupdict().has_key('HTMLCOLUMN'):
  56              params.html=match.group('HTMLCOLUMN').split(',')
  57 @@ -210,13 +220,17 @@
  58              aColumnNumber=aColumnNumber+1 
  59              element=unicode(str(aCell) , config.charset)
  60              if str(aColumnNumber) in params.hidden: 
  61 -                    conitnue
  62 +                    continue
  63              if str(aColumnNumber) in params.html: 
  64                      isHTML=1 
  65                      element=_format_href % (str(aCell), str(aCell) )
  66              if str(aColumnNumber) in params.wiki: 
  67                      isHTML=1
  68                      element=_format_wikiref % (str(aCell), str(aCell) )
  69 +            if str(aColumnNumber) in params.wml:
  70 +                    isHTML=1
  71 +		    wml=str(aCell).split("|")
  72 +                    element=_format_wmlref % (str(wml[0]), str(wml[1]) )
  73              if str(aColumnNumber) in params.mail: 
  74                      isHTML=1
  75                      element=_format_mailto % (str(aCell),str(aCell))
MySQL.py.parser.diff

Discussion

Created by cfalconer@totalteam.co.nz The purpose was to allow more flexible generation of tables from SQL queries, so that a URL could be created from a name field and an address field. See the example above for one way to use WML.

And WML stands for Wiki Mark-up Language... for no reason at all.

Plan

MoinMoin: MoinMoinPatch/WikiMarkupLanguage (last edited 2007-10-29 19:06:49 by localhost)