20 August 2012

Online Accounting Software/Systems Comparison

This post shares the results of our research into various web-based accounting systems for small business. We're upgrading from a Spideroak-based (formerly Dropbox-based) Quicken accounting system to an online system. We have invested enough time researching this that we felt we could reduce other people's research time if we share it with the world.

Note that we have very specific requirements (like any company) and we targeted our research to only those systems that meet our basic needs of
  1. Online Access from any machine with a web browser
  2. Support for multiple currencies
  3. Multiple user access with access control
The services we found that meet these three criteria at this time are Kashoo - Small Business Plan, Xero - Large Plan, Outright, WaveAccounting, e-conomic Small Plan, and Ezyaccounts - Medium plan.

For these six services we've created this comparison chart:

Table - Comparison of Online Accounting Software/Services
Services which we looked at but did not meet these our basic needs were FreeAgent, Indinero, Quickbooks Online, LessAccounting, Twinfield, YNAB, and Sage. (Actually, Twinfield might meet these criteria, but we are US-based and Twinfield only serves the EU, so they were out of the running from the start.)

If you want to contribute to our research and expand this comparison chart by adding criteria or filling in the blanks for services that didn't meet our criteria, leave a comment and we'll share the file with you.

Update 1 (October 3, 2012)

Switched from Wave Accounting to Kashoo. We tried Wave Accounting because it appeared to have the features we needed and it's free. But after more than a week of use, it became apparent that two major shortcomings in their system made it impossible for us to work with:

1. There is no way to note transfers between accounts (from a bank account to petty cash, for example). Actually, one can work around this, but it's complicated and confusing and, really, when you come down to it, why should you have to jump through hoops to do something basic like this? I transfer money between accounts all the time, and I just don't need the hassle that Wave demands. (It's like having a keyboard without the "<" key. Yeah, you can live without it and open the default character map every time you want a left bracket, but who really wants to deal with all that?)

2. It doesn't REALLY support multiple currencies. You can't, for example, transfer money from an account in once currency to an account in another currency (again, if one were to withdraw MXN pesos from a US bank acct at an ATM in Mexico). Plus the implementation is confusing for multiple currencies, and it's not always clear which currency is being shown in the total column.

So, we've switched to Kashoo, which looks very loveable at this point. And if it works like we want, it's a bargain even at $100/year.

Update 2 (December 5, 2012)

Multi-currency support. We had to upgrade from the $100/year Kashoo plan to the $200/yr plan in order to have multi-currency support that the free plan doesn't offer. Alas.

Access Restriction to Specific Accounts. Also, we learned that, while Kashoo allows you to restrict access by other users, the restrictions apply to all accounts in your Kashoo plan.

This was potentially a problem, because I want my bookkeeper to be able to see/modify my company accounts but not my personal ones. If I were to create all my accounts within my primary Kashoo plan, I would not be able to restrict access by account. So, what I've done is created a second "business" (in Kashoo parlance) to track my personal bank accounts, and I am able to restrict access so that only I and my accountant can see them....my bookkeeper cannot.

I was afraid that I would have to pay another $200/yr to add my personal accounts "business" so it would also have multi-currency support. But fortunately Kashoo allows you to add a "business" for only $50/yr, so I'm able to have all the features I need for $250/yr.

Excellent.

By the way, Kashoo just announced a simplified pricing plan a few days ago, so our $200/yr plan is now only $192/year!
Simplified Pricing We’ve  also initiated a simplified pricing plan: Free, Monthly, and Annual. All Kashoo customers will have access to both the complete web and iPad applications. All features, such as multi-currency, payroll integration, invoice templating and bank sync, will be available to all customers. Access to the entire Kashoo platform will be forever free to customers processing under 20 transactions (i.e, an income or expense entry) per month. And as your business grows and scales beyond that milestone, Kashoo will be available for as low as $16 a month (when purchased annually). No restricted features. No gimmicks. Just simple pricing for when your business is ready. 
So far, we really like Kashoo, and it looks like we're going to stick with it.

03 August 2012

Winzip filenames default to uppercase

I have a problem with Winzip 15.5 and sent this message to Winzip support:

> For some reason, Winzip has started defaulting to uppercase filenames> when I create new zipx files, which is irritating because I've not> used uppercase filenames in decades!>> I unchecked the Allow Uppercase Filenames setting, but it has had no> effect.>> How do I fix this?

Winzip technical support responded as follows, and the solution works for me:
I would have to assume that you are using the context menu (right click) when creating Zip files.  What I've seen is that if you: 1. Highlight more than one file or folder 2. Right click and choose Add to Zip file the Add dialog that displays will show the 8.3 naming convention in the Add to archive text box. This also affects the file name. Development is working on the fix for this and will include it in the next version. What you can do for now would be to click the "New" button in the Add dialog, type your Zip file name in the New WinZip File dialog that opens, and click Create. This will bring you back to the Add dialog and the path and file name will be in the long name format you would usually expect to see. Please let me know if you have any further questions. --Bill Glenister, WinZip Technical Support

04 June 2012

To display svn diff results side-by-side


Use the following command to suppress unaltered lines of code (optional), change the width of the output (optional), and display the results of an svn diff output side-by-side:

svn --diff-cmd "diff" --extensions "-y --suppress-common-lines --width=240" diff -r M:N path/filename 

Where M=the revision number before changes and N=the last rev number of the changes you wish to see.

28 May 2012

Chrome security bug -- not deleting session cookies

In the most recent version of Google Chrome (19.0.1084.52 m), the function of the "On Startup > Continue where I left off" setting has changed so that it does not log out of accounts when I close my browser. It appears that the Chrome browser no longer clears temporary session cookies when it closes (and according to some posts, the session cookies even remain after restarting the computer!) This is a potentially serious security issue, as I have at least three applications which open in a logged-in status when I start a fresh session of Chrome: Gmail, RememberTheMilk, and Blogger. (As I composed this post I closed my browser a couple times to test setting, and when I reopened it, Blogger was still logged into the "edit post" page for this entry.)

I changed my browser setting to "Open a page or specific set of pages" but it does the same thing: if I'm logged in to these sites when I close my browser, I'll still be logged in when I reopen Chrome again.

Until Google fixes this, I will use the solution I discovered at http://productforums.google.com/forum/#!msg/chrome/Yjw7Urs0fAs/ppNs6qQT_8IJ:


1. Go to Chrome setting then Privacy
2. Click on Content Settings
3. Under Cookies, check : Clear cookies and other site and plugin data when I close my browser.

As the author says, this will clear all your cookies. which I do mind, actually. So this is only a stop-gap solution, and I hope Google fixes this bug soon.


Update 4 Feb 2015

Well, it only took three years, but it appears that someone at Google finally helped me figure out the problem. In brief, the trick is to close Chrome using the Menu > Exit button or Ctrl+Shift+Q, rather than using the red close "X" in the top right corner of the window.

This from the Chromium forum:

#9 mattm@chromium.org
battre: I thought that delete site data on exit had been changed to take precedence over "continue where I left off" (eg  issue 128567  comments 19 and 20). Some tests I did seem to confirm that.

This sounds like it could be the issue where chrome doesn't actually exit (maybe because some chrome app / extension is still running).

curtis: could you try closing chrome using the chrome menu -> "Exit" option instead of clicking the X button? and check the windows task manager to confirm there are no chrome processes still running.

And my happy response:

#10 curtis
Matt: Closing Chrome using the Menu > Exit option did the trick. It killed all Chrome processes and I was prompted for a Gmail password when I restarted Chrome. I tested it again by clicking the red "X" and only a couple of Chrome processes were stopped -- most of them kept running. 

So it appears that THAT is what has changed: the close browser function using the X is not killing processes like it used to. But knowing that I have to use Exit menu item instead is great...once I train myself to do that (an old dog learning a new trick) all will be well. Thanks!

Cool!

19 April 2012

Office Max México sitio -- que estúpido!

Para ver productos en el sitio web de Office Max Mexico (http://www.officemax.com.mx) tienes que crear una cuenta de usuario. Has oído de algo tan ridículo? Y el formulario para registrarse tiene muchos campos requisitos y datos personales. ¡¿Qué?! Para buscar productos de oficina en linea??

Ni modo, es fácil entrar datos falsos y crear una cuenta para tener acceso al sitio, pero requiere tiempo y es ridículo. Es muy buena manera redirigir gente a los sitios de la competencia!

13 April 2012

pantalla azul en mac - kernel panic

Hoy en la mañana tuve un percance con Mac mini.
de impresión me quede pensando que hacer. después de unos segundos me di cuenta que no podía hacer nada.
aparentemente existe un pantallazo azul de Windows en Mac. según dice que es muy poco probable pero me tubo que pasar a mi, así sea el .0001% de posibilidad de que pase, el caso es que puede pasar.
y Mac solo dice "si claro puede pasar, si te pasa haz lo siguiente." son consientes de sus errores.

bueno dejo un poco de información al respecto (chequen el video).


http://www.macuarium.com/foro/index.php?showtopic=273539&st=0&p=2164448&#entry2164448
http://www.forocoches.com/foro/showthread.php?t=1816143
http://support.apple.com/kb/HT3964?viewlocale=es_ES&locale=es_ES
http://www.youtube.com/watch?feature=player_embedded&v=0-22EpQOm8c
http://thexlab.com/faqs/kernelpanics.html

09 April 2012

Tiempo JavaScript setTimeout

Hoy me encontré con el problema de poder manipular tiempo para realizar acciones con funciones JavaScript, para llamar una función pasado un tiempo es con la instrucción setTimeout, el detalle me ocurrió, después de mandar a ejecutar una función con onMouseOver, ya no quería que ejecutara la función , después de un rato recordé de una instrucción que ayuda a eso, la busque y encontré clearTimeout.

la aplicación es sencilla, declaramos una variable global para poder usarla en las funciones que necesitemos, por ejemplo var tiempo;
en la función llamada por onMouseOver iniciamos esta variable con tiempo = setTimeout("función a ejecutar",tiempo_a_esperar);

la detención de esta función la mande a traer con un onMouseOut, y solo falto con colocar clearTimeout(tiempo); para poder detener la función que ya no se necesitaba.

18 March 2012

Code to strip html tags in Notepad++ and leave only text


Select "Regular Expression" in Search Mode. Enter [<].*?> in Find What field and leave the Replace With field empty. Note that you need to have version 5.9 of Notepad++ for the ? operator to work.


For more regex search help in Notepad++, see http://sourceforge.net/apps/mediawiki/notepad-plus/index.php?title=Regular_Expressions

Trojan problems?


Here's what I do when I have an infected Windows machine, and it's always worked for me (so far).

Download and install Spybot Search & Destroy.  Don't click any of the add-ons unless you think you really want them.  Once installed, do an update to make sure you've got the latest files, then do a scan to see what it finds.  

If you've still got problems with your machine after using Spybot, I recommend you download/install/run Malwarebytes

15 March 2012

Working with Interspire Email Marketer Technical Support

We'll be posting here some of our correspondence with a certain company's technical support. Rather than commenting on it, we'll just let it speak for itself.

  1. 7.
    Reply by Andrew Thomas Blake on 8 Mar 2012
    --------------------------------
    Supporting Information
    --------------------------------
    Severity: The application is down or experiencing a major malfunction
    Support Type: I'm having a problem with the application
    Product Version: 6.1.2
    Operating System: Not Applicable
    Web Browser: Not Applicable
    Screen Resolution:
    License Selected: #88827 - http://interspire.true...

    --------------------------------
    Problem Summary
    --------------------------------
    hello hello we need help with our open ticket please

    --------------------------------
    Problem Description
    --------------------------------
    we have an issue that has been open for weeks -- can we please get help with it

    the technical support is down or experiencing a major malfunction 
  2. 6.
    Staff Reply Reply by Stephen Maeder on 8 Mar 2012
    Hi Andrew,

    Thank you for your patience regarding your support ticket.

    Support tickets are answered in the order they are received. Response times may vary depending on the number of pending tickets in the queue. To help eliminate delays resolving your issue, please provide as much detail as possible to recreate the issue.

    Please note that tickets are sorted in our support queue by the last activity date. Any reply by you or our technical support team will reset this date. Please refrain from excess replies to check the status.

    You can check the status of your ticket by logging in to the Interspire Client Area and selecting View Your Tickets. Please note if the ticket status shows 'Active' there is no action required from you.


    Best Regards,

    Stephen Maeder
    Interspire Partner/Affiliate Manager
    http://www.interspire.com

    US: 1-800-939-5570
    International: +1-512-758-7578 
  3. 5.
    Reply by ######@######.###on 8 Mar 2012
    Hello Stephen

    Thanks for your response. However, I have to call into question some of the
    information you have provided: if it is true that tickets are addressed in
    the order that they are received, how come you have answered this one while
    we are still waiting for help with one which is now weeks old?

    Could you please let me know what is the average time your clients wait for
    support tickets to be resolved?

    Thank you

    Andrew Blake



    On Thu, Mar 8, 2012 at 10:44 AM, Interspire Email Marketer <
    emailmarketer@interspire.com> wrote:

    > Hi Andrew,
    >
    > Thank you for your patience regarding your support ticket.
    ...


  4. 4.
    Staff Reply Reply by Scott Smithwick on 8 Mar 2012
    Customer service issues are different than support issues. The customer service team tries to pull out any non-support issue so the technical team can focus on actual support.

    The wait depends on the type and number of issues that were submitted before you.



    Regards,
    Scott Smithwick
    Interspire Customer Service

    Toll-Free: 1 800 939 5570
    US: 1 512 758 7578

    P.S. Have you tried BigCommerce (the hosted version of our shopping cart software) yet? Go here for a 15 day free trial: http://bit.ly/bcfreetrial 
  5. 3.
    Reply by  #####@######.###  on 8 Mar 2012
    Hi Scott

    Thanks for explaining my first uncertainty. That makes sense.

    However, I don't feel that my last question has been answered. Obviously
    resolution times differ; that is the point of taking an average value. I
    would be grateful if you could let me know what it is.

    Or at least -- if you don't have precise figures to hand -- please give me
    a ballpark idea by letting me know which one of the following terms best
    describes it: 'hours', 'days', 'weeks', 'months', 'years'. This is just so
    that I can have a better sense of how patient to be.

    I hope I don't come across as antagonistic, I certainly don't mean to seem
    that way. However, please bear in mind that the time I spend in the
    exchange of these emails is not anyone's free time. Interspire have been
    paid to provide technical support for their product; I myself am also being
    paid to engage in the process, and I feel an obligation to make it as
    efficient as possible.

    At the moment I am wondering if help is on the way at all, which is why I
    am asking for information. I would very much appreciate your help in this.

    Andrew






  6. 2.
    Staff Reply Reply by Scott Smithwick on 8 Mar 2012
    Typically Email Marketer support tickets are answered within 24hrs with either a resolution or additional questions to help troubleshoot the issue. Currently we have an abnormal backlog of support issues (close to double the normal) so things are slower than usual.



    Regards,
    Scott Smithwick
    Interspire Customer Service

    Toll-Free: 1 800 939 5570
    US: 1 512 758 7578

    P.S. Have you tried BigCommerce (the hosted version of our shopping cart software) yet? Go here for a 15 day free trial: http://bit.ly/bcfreetrial 
  7. 1.
    Reply by #####@######.### on 9 Mar 2012
    Hi Scott

    Thanks for the information.

    I'd like to underline that our ticket "XME-769-62473 excessive disk usage
    during mailings!!!!!!!!!!!!!!" has been waiting for a response from your
    technical support team since March 2nd.

    This ticket was originally opened on January 26th.

    Please could you encourage tech support to at least touch base with us
    regarding this issue.

    We have upgraded to the latest version of the software (even though there
    was no indication that the problem we are having was addressed in the new
    version), and we have run a mailing in test mode to see if the excessive
    disk use still occurred (even though it would have been 'unlikely',
    considering test mode does not send any emails).

    John Tuck of Interspire Technical Support has confirmed that 100% disk I/O
    utilization is not the expected behavior when Interspire is sending emails.

    The problem persists.

    We are undergoing a period of server instability that we think might be
    related to these periods of excessive disk use.

    The monitoring software that we are using to help us track down the cause
    of the instability is called New Relic. Every time that Interspire sends
    out a mailing, New Relic sends us an alert warning us that our disk I/O is
    abnormally high.

    Here is a sample:

    Critical problem (1 during this alert) Start time Duration End time
    Disk IO > 90% on vs3 Today, 13:03 16 minutes In-progress


    You will agree that together, these elements constitute valid grounds for
    worry that Interspire is interfering with the operation of our server, and
    I think you would be best advised to look on our Support Request as
    offering Interspire an opportunity to come up with a reason why we should
    not just try a different email marketing software.

    I might suggest that I would be interested to know if any other Email
    Marketer users have problems of excessive disk usage. Are any other
    customers using Email Marketer in conjunction with New Relic? And -- as I
    have asked before, without response -- I would like to know whether the
    rate at which our system sends emails (e.g. 18,843 in four hours) is fast,
    slow or normal.

    Thank you for your attention. You may close this ticket -- and I leave you
    in the hope that our ticket XME-769-62473 will be addressed soon, with
    answers to the questions above.

    Andrew


14 March 2012

modular programming in MySQL stored procedures

MySQL stored procedures are handy when things get complicated.

But one major drawback is that they can't call one another.

Nor can functions return tables.

It would be great if they could, because then you could for example create a subquery that could appear as a function call inside different parent queries -- and if you ever needed to change the subquery, it would change in all the parent queries without you needing to edit all the copies.

At present, if the same subquery needs to appear in several different places, it has to appear literally, and you need to keep all the copies up to date individually.

When you start to use a suite of interlocking functions and procedures, things descend very quickly into chaos.

The solution I've found is this: create a modular structure inside a programming environment (I have used ruby), and then generate the full text of your procedure definitions with a call to the ruby script.

For now I'm just going to post the code of the system and hope that it can speak for itself. Later (especially if anyone shows interest) I'll post an instruction manual and examples of use.


# -*- coding: utf-8 -*-
## Andrew wrote this
## 2011-09-29

=begin

purpose
-------

Make SQL procedures and queries modular.

explanation
-----------

Many queries work by building up nested subqueries.

Sometimes, different queries might need to use the same subqueries.

However, the SQL language (at least in MySQL) has no provision for reusing modules.

So if you are editing a subquery that is used in two or three
different queries, you will find yourself needing to make the same
edits in two or three different places. This is no good

Also, queries with nested subqueries can get hard to read. But if you
can chunk things up, and give each chunk a good name, that should help
with the readability.


=end

#################################################
##
##   CONFIGURATION GLOBALS
##
#################################################

## you can change these to tweak the behaviour slightly

# pretty: whether to give the text a pleasing format or not

# the default is to display prettily if the output is a terminal
# (console) unless you supply 'compact' on the command line and
# compactly if it's being piped somewhere, unless you supply 'pretty'
# on the command line

# the default behaviour should just about do, don't you think?
# what exactly are you after?

$pretty = !ARGV.member?('compact') && (ARGV.member?('pretty') || $stdout.isatty)



# $indent_step: ...why on earth would you want to change this?
$indent_step = '  '

#################################################
##
##   UTILITY GLOBALS
##
#################################################

# don't mess with these

# indent: current indentation level; how many steps to indent when
# pretty
$indent = 0



#################################################
##
##   GLOBAL FUNCTIONS
##
#################################################



def ind
  $pretty ? $indent_step * $indent : ''
end

def nl
  $pretty ? "\n#{ind}" : ' '
end

def format (p = '')
  if $pretty
    indent = ind
    p.gsub(/^/,"#{indent}")
  else
    # get rid of all comment lines, and then excess whitespace
    p.gsub(/^[ \t]*#.*$/,'').gsub(/[ \t\n\r]+/,' ')
  end
end



#################################################
##
##   Abbreviations
##
#################################################

# about sodding time!

$abbr = Hash::new

# These are the default characters for identifying the abbreviations

$n = '$'
$u = '~'

# Don't like em?

def set_abbreviation_marker_characters(namealias, unabbreviate)
  set_namealias_abbreviation_marker_character(namealias)
  set_unabbreviate_abbreviation_marker_character(unabbreviate)
end

def set_namealias_abbreviation_marker_character(namealias)
  $n = namealias
end

def set_unabbreviate_abbreviation_marker_character(unabbreviate)
  $u = unabbreviate
end

def unabbreviation (text)
  output = text.gsub(/[#{$n}]([a-z]+)\b/o) { namealias $1.to_sym }
  output = output.gsub(/[#{$u}]([a-z]+)\b/o) { unabbreviate $1.to_sym }
  output
end




# there are two ways that an abbreviation can be expanded -- either
# simply, or assuming that the abbreviation is a table or column name,
# and placing " as xxx" after the expansion, where xxx is the
# abbreviation itself, so that the abbreviation becomes an alias in
# mysql, and you can (in fact, you have to) use it without expanding it

## this is how you define an abbreviation: the abbreviation comes
## first, as a symbol, and then what it means (the expansion)
def abbreviate (abbr, means)
  $abbr[abbr]=means
end

## this is the simple expansion
def unabbreviate (abbr)
  raise NameError, "Abbreviation #{abbr} not found in abbreviations table" unless $abbr.member?(abbr)
  "#{$abbr[abbr]}"
end

## this is the one that results in an alias being defined in mysql
def namealias (abbr)
  raise NameError, "Abbreviation #{abbr} not found in abbreviations table" unless $abbr.member?(abbr)
  "#{$abbr[abbr]} as #{abbr}"
end



#################################################
##
##   VariableList class: a list of variables
##                    belonging to a query
##
#################################################


class VariableList < Array

  def initialize (*variables)
    "Create a list containing the supplied variables"

    variables.each { |v| self << v }
  end

  def <<(v)
    "Add a variable to the list"

    if v.kind_of? Symbol
      super v
    elsif v.kind_of? Variable or v.kind_of? Cursor
      super v.name
    else
      raise TypeError, "Trying to add #{v.inspect} which is class #{v.class} -- Can't add a non-variable"
    end
  end

  def params(p = '')

    "Output the parameter-list: i.e. all the typed but undefined
    variables, separated by commas"

    self.each { |v|
      a = Variable[v].param
      if a.strip != '' and p.strip != ''
        p += ",#{nl}"
      end
      if a.strip != ''
        p += "#{a}"
      end
    }
    p
  end

  def param_count(c=0)
    self.each { |v|
      c += 1 if Variable[v].is_param
    }
    c
  end

  def undeclare_params!
    self.each { |v| Variable[v].undeclare_param! }
  end
   
  def declarations (p = '')

    "Output the declarations of all typed and defined variables in the
    list"

    self.each { |v|
      p += Variable[v].declare
    }
    p   
  end

  def definitions (p = '')

    "Output the definitions of all defined variables in the
    list"

    self.each { |v|
      p += Variable[v].define
    }
    p
  end  

  def simple?

    "Return true if none of the variables on the list have
    definitions; false otherwise"

    self.each { |v|
      return false if Variable[v].is_defined?
    }
    true
  end

  def check
    "verify that all variables on the list are valid"

    self.each { |v|
      raise NameError, "Error: variable #{v} has not been initialised (check spelling)" if Variable[v].nil?
    }
  end

end

#################################################
##
##   Variable class: has a name,
##                    possibly a type
##                    possibly a definition
##                   might be:
##                    a parameter (type but no definition)
##                    a user-variable (no type, will have '@' prepended to name
##                    or a standard defined variable
##                       which is declared with 'declare'
##
#################################################


class Variable < Object

  ## @@store is a list of all the variables that have been defined; we
  ## use it to access them by name, and to make sure that each one is
  ## unique
  @@store = Hash::new()


  def initialize(name, type, definition = nil)
    "
Create a variable, giving it a name, a type and a definition.

If definition is omitted (but type is present) then the variable is a
parameter, and will appear on the parameter list of the generated
procedure.

If type is set to nil, the variable will be a user-defined variable,
and when used within the definition and the body of the query ti must
be preceded by '@'. If a definition is supplied it will be output;
otherwise, according to the MySQL documentation it will default to
NULL, and a string type;
"



    if @@store[name]
      raise ArgumentError, "Variable \"#{name}\" already exists"
    end

    @name = name
    @type = type
    @definition = definition

    ## @param_declared:

    ## this flag is to indicate whether or not a parameter has already
    ## been placed on the parameter list

    # The reason for it is so that procedures wrapping outer queries
    # can specify in what order they want the parameter list to
    # be. Inner queries then need to know if a particular parameter is
    # already on the parameter list or not.

    # However, whenever a new procedure or function is generated, we
    # must first make sure all parameters are undeclared. That's what
    # the undeclare_params! methods are about.

    @param_declared = false


    @@store[name] = self
  end

  def name
    @name
  end

  def type
    @type
  end

  def param
    "If this variable is a parameter and has not already been declared, declare it"

    if is_param
      @param_declared = true
      "#{name}  #{type}"
    else
      ''
    end
  end

  def is_param
    @definition.nil? and !@type.nil? and !param_declared?
  end

  def param_declared? 

    "Keeping track of whether this is a parameter that has already
    been declared or not"

    @param_declared
  end

  def undeclare_param!
    @param_declared = false
  end
   
  def declare

    "Non-parameters (with type) are declared here, using the 'declare'
    keyword"

    ## @type can be nil, meaning it's a user variable; just define it
    ## (if there's a definition), don't declare it

    if !@definition.nil? and !@type.nil?
      "#{nl}declare #{name}  #{type};"
    else
      ''
    end
  end

  def define
    "Output the definitions of all variables which have them"

    # except make sure not to output a loose ';' if the definition is empty

    if is_defined? and @definition.strip != ''
      "#{nl}#{format(@definition).strip};#{nl}"
    else
      ''
    end
  end

  def is_defined?
    "Does this variable have a definition?"

    !@definition.nil?
  end

  def self.[](s)
    "Provides access to named variables using Variable[:name]"
    @@store[s]
  end

end

#################################################
##
##   QueryList class: a list of subqueries
##                    belonging to a Query
##
#################################################

class QueryList < Array

  def initialize (*queries)
    "Create a list containing the supplied queries"

    queries.each { |q| self << q }
  end

  def <<(q)
    "Add a query to the list"

    if q.kind_of? Symbol
      super q
    elsif q.kind_of? Query
      super q.name
    end
  end

  def params (p = '')
    "Output the parameter list for all the queries in the list"

    self.each { |q|
      p = Query[q].params(p)
    }
    p
  end

  def declarations (p = '')
    "Output the variable declarations for all the queries in the list"

    self.each { |q|
      p = Query[q].declarations(p)
    }
    p   
  end

  def definitions (p = '')
    "Output the variable definitions for all the queries in the list"

    self.each { |q|
      p = Query[q].definitions(p)
    }
    p
  end 

  def simple?
    "Are all the queries on this list simple? (no variables with definitions)"

    self.each { |q|
      return false if !Query[q].simple?
    }
    true
  end

  def param_count(c=0)
    self.each { |q|
      c += Query[q].param_count(c)
    }
    c
  end

  def undeclare_params!
    self.each { |q| Query[q].undeclare_params! }
  end

  def check
    "verify that all subqueries are valid"

    self.each { |q|
      raise NameError, "There is no query called '#{q}', check spelling" if Query[q].nil?
      Query[q].check_variables
    }
  end

end


#################################################
##
##   Query class
##
#################################################



class Query < Object
 
  ## @@store holds all Query instances, to aid in access and to avoid
  ## duplicates
  @@store = Hash::new()

  def initialize(name,comment,body,*variables)
    "Create a query -- it must have a name, comment, a body, and may have variables"
   
    raise ArgumentError, "Query name \"#{name}\" already taken" if @@store[name]

    @name = name
    @body = body

    set_comment(comment)

    ## a note about variables:
    ## we can specify the order of parameters

    ## we might have an outer query that uses param B
    ## and an inner query that uses param A

    ## ordinarily, the procedure that this outer query gives rise to
    ## will have a parameter list that goes (B, A) because its own
    ## parameter is found first

    ## but supposing we want it to be (A, B) ?

    ## the way to do this is to redeclare the existing variable (A) in
    ## the outer query's variable list, before (B)

    @variables = VariableList::new(*variables)
    @subqueries = QueryList::new()

    @handlers = Array::new()
    ## can we handle handlers?

    @deterministic = true
    @readonly = true

    @setting = nil



    @@store[name]=self
  end

  ## setting: This is a little fragment of sql into which the subquery
  ## may be placed. It is not obligatory, and in fact probably will be
  ## seldom used. An example might be:

  ## join
  ##   {{}}
  ## on {}.id=othertable.id

  ## where {{}} gets replaced by the output of "subquery" (or what it
  ## would have been without a setting), and {} gets replaced by the
  ## result of "name"

  ## the need for this is to make subqueries optional, so that they
  ## can be placed in an outer query or not, without their absence
  ## rendering the outer query erroneous in syntax

  ## The only slight wrinkle is that if setting is part of the Query
  ## itself, then one subquery can't find itself in several different
  ## settings. It would be better if setting could be associated with
  ## the query only in the QueryList

  def setting
    @setting
  end

  def set_setting(s)
    @setting = s
  end


  def clone(newname)
    "Create identical query, but with no subqueries"

    Query::new(newname,@comment,@body,*@variables)
  end

  ## clone explanation:

  ## the idea is that when you have two alternative ways of generating
  ## equivalent queries, if you want to be able to implement both
  ## alternatives, then you will need to clone the first subquery that
  ## is identical, and from there branch outwards

  ## -- For example: you have query Q1 which may use either subquery A
  ## or subquery B, the end result is the same. So you can either do

  ##   Query[Q1].add_subquery A -- or --
  ##   Query[Q1].add_subquery B

  ## -- yet obviously not both. However, if you want to have side-by-side
  ## implementations of the two alternatives, then you simply clone Q1
 
  ## Query[Q1].clone Q2

  ## this creates a query called Q2 which is identical to Q1
  ## (including variables), save for any subqueries Q1 might have

  ## then

  ##   Query[Q1].add_subquery A -- and --
  ##   Query[Q2].add_subquery B

  ## are possible, and the corresponding procedures may be generated
  ## and compared

  ## end of clone explanation


  def add_variable (v)
    "Add a variable to this query"

    @variables << v
  end

  def add_variables (*v)
    v.each { |x| add_variable x }

## (we don't understand why "@variables.concat v" causes an error of
## undefined method 'param' for Nil later in the code, when it should
## do the same as the above)
  end

  ## sometimes an outer query (after cloning) is used with a subquery
  ## that takes a different parameter than the original subquery did
  ## -- this means that the original parameter has to be replaced with
  ## a new one, which is what replace_variable does

  def replace_variable(old, new)
    @variables[@variables.index(old)]=new
  end

  def add_subquery (s)
    "Add a subquery to this query"

    if s.kind_of? Symbol
      n = s
    elsif s.kind_of? Query
      n = s.name
    else
      raise TypeError, "Can't add '#{s.inspect}' -- #{s.class} can't be converted to a query"
    end

    ## avoid endless loops in the generation
    if @name == n
      raise NameError, "Can't add self (\"#{@name}\") as subquery"
    end
   
    @subqueries << s
  end

  def add_handler (h)
    @handlers << h
  end

  def set_comment (comment)
    "Make query's comment be this"

    @comment = comment
  end

  def comment
    "Output query name and comment text, every line preceded by '## '"

    return '' if @comment.nil? or !$pretty
    format("\n\n#{name}\n\n#{@comment.strip}\n".gsub(/^(.+)$/, '## \1'))
  end

  def set_not_deterministic
    @deterministic = false
  end

  def deterministic?
    return false if !@deterministic
    @subqueries.each { |s|
      return false if !Query[s].deterministic?
    }
    true
  end

  def set_modifies_sql_data
    @readonly = false
  end

  def readonly?
    return false if !@readonly
    @subqueries.each { |s|
      return false if !Query[s].readonly?
    }
    true
  end


  def name
    @name
  end

  def body
    "Output the entire body of this query, including any subqueries"

    output = format(@body)
    output = output.gsub(/[{]{2}(\d+)[}]{2}/) {
      i = $1.to_i
      @subqueries.count > i ? Query[@subqueries[i]].subquery : ''
    }
    output = output.gsub(/[{](\d+)[}]/) {
      i = $1.to_i
      @subqueries.count > i ? Query[@subqueries[$1.to_i]].name : ''
    }
    output = unabbreviation output
    "#{comment}#{output}"
  end

  def query
    "Output the full body, including subqueries, but not as a subquery itself"
    "#{nl}#{body.strip};#{nl}"
  end

  def subquery
    "Output the full body as a named subquery"

    $indent += 1
    show = "(#{nl}#{body}#{nl}"
    $indent -= 1

    op = "#{show}) as #{name}#{nl} "

    if !@setting.nil?
      op = @setting.gsub(/[{]{2}[}]{2}/, op)
      op = op.gsub(/[{][}]/, name.to_s)
    end

    op
  end

  def function_query
    "Output the full body, including subqueries, in parenthesis and with 'return'"
    "return(#{body.strip});#{nl}"
  end



  def params(p = '')
    "Output the complete parameter list of this query (including subqueries)"

    p = @subqueries.params(@variables.params(p))
  end

  def param_list
    "Output all parameters enclosed in parenthesis, trying to nicely format"
    if !$pretty || param_count == 1
      "( #{params} )"
    else
      "(#{nl}#{params}#{nl})"
    end
  end

  def param_count(c = 0)
    c = @subqueries.param_count(@variables.param_count(c))
  end

  def undeclare_params!
    @subqueries.undeclare_params!
    @variables.undeclare_params!
  end

  def declarations (p = '')
    "Output the complete variable declarations list of this query (including subqueries)"

    @subqueries.declarations(@variables.declarations(p)) 
  end

  def definitions (p = '')
    "Output the complete variable definitions list of this query (including subqueries)"

    @subqueries.definitions(@variables.definitions(p))  
  end 

  def preamble
    p = ''
    if deterministic?
      p += "deterministic\n"
    else
      p += "not deterministic\n"
    end

    if readonly?
      p += "reads sql data\n"
    else
      p += "modifies sql data\n"
    end

    if !simple?
      p += "begin\n"
      $indent +=1
    end

    p
  end

  def put_vars
    "#{declarations}#{definitions}"
  end

  def put_handlers
    output = ''
    @handlers.each { |h|
      output << "#{nl}declare continue handler for not found set #{h} = true;#{nl}"
    }
    output
    end


  def postamble
    return '' if simple?
    $indent -=1
<<output

end//
delimiter ;
output
  end

  def procedure
    undeclare_params!
    <<output
drop procedure if exists #{@name};#{ self.simple? ? '' : "\ndelimiter //" }
create procedure #{@name}#{param_list}
#{preamble}#{put_vars}#{put_handlers}#{query}#{postamble}
output
  end

  def function (type)
    undeclare_params!
    <<output
drop function if exists #{@name};#{ self.simple? ? '' : "\ndelimiter //" }
create function #{@name}#{param_list}
returns #{type}#{nl}#{preamble}#{put_vars}#{put_handlers}#{function_query}#{postamble}
output
  end

  def drop_procedure
    "drop procedure if exists #{name};\n"
  end

  def drop_function
    "drop function if exists #{name};\n"
  end

  def drop
    drop_procedure
    drop_function
  end

  def self.store
    @@store
  end

  def self.[](n)
    @@store[n]
  end

  def self.clean_up

    "Drop any procedures or functions that might have been generated
    from all active queries"

    # cover all bases
    @@store.each { |n,v|
      puts "drop procedure if exists #{n};\ndrop function if exists #{n};\n"
    }
    puts "\n"
  end

  def simple?

    "See if any of this query's variables are defined, or if any of
    the subqueries have defined variables, and if so return false;
    otherwise return true -- meaning that the generated routine won't
    need 'begin' or 'end' or a delimiter switch"

    @variables.simple? and @subqueries.simple?
  end

  def check_variables
    @variables.check
  end

  def check
    check_variables
    @subqueries.check
    self
  end

end

## As explained above, the idea of this class is to associate a
## subquery with a setting, but in such a way that the same query can
## be associated with different settings
class QueryInSetting < Query

  ## ... I guess this is not finished...

end


#################################################
##
##   Cursor class
##
#################################################



class Cursor < Variable

  ## the Cursor is a variable which accepts a Query as its "type"
  ## the actual type becomes "cursor for " and then the body of the query

## strike out the next two lines
  ## it needs to designate an existing Variable as a CONTINUE HANDLER
  ## -- that is what the second argument of the initialize method is for
## end of strike-out

  ## there are elements of this implementation that need research --
  ## for example, how is it going to work for a procedure query to
  ## have more than one cursor? can Cursors share continue handlers?
  ## (example procedures on the web never seem to have more than one
  ## continue handler, even when they have multiple cursors)

  ## if it's true that one procedure can never have multiple
  ## continue-handlers, then we need a way to make sure that when we
  ## add multiple cursors to a procedure, each cursor can know about the
  ## single C-H that the parent procedure is going to use...
  ## it would be a single property added to the VariableList class

  ## Also, we currently add the variables to the cursors -- but this
  ## is not quite right, the parent procedure should handle everything
  ## about the cursor variables.

  def initialize(query, code=nil)
    # A cursor owns its query
    @query = query
    super(@query.name,"cursor for #{nl}#{format @query.body.strip}",'')

    ## @code is the code that should be executed each time the
    ## cursor fetches -- we automatically set up a loop, and this code
    ## goes inside the loop

    @code = code
  end

  ## we need to derive the declare method so that the query's
  ## declarations come above, then the cursor's own declaration, then
  ## the query's definitions

  def declare
   
    "#{@query.declarations}#{super}#{@query.definitions}"
  end

  ## also we need to be able to pull out the query's params
  def param
    # make sure our query outputs its params
    @query.undeclare_params!
    @query.params
  end

  def name
    "#{super}_cursor"
  end

  def basename
    @query.name
  end

  def code
    @code.nil? ? '' : @code
  end

  def loop (variables,
            replacementcode = nil,
            countername = nil,
            handlername = 'cursor_done')
   
    counter = (countername.nil? or countername == '') ? 'counter' : countername

"
set #{handlername} = false;
set #{counter} = 0;
  open #{name};
  #{basename}_loop: loop
  fetch #{name} into #{variables};
  if #{handlername} then leave #{basename}_loop;
  end if;

  set #{counter} = #{counter} + 1;

  #{unabbreviation(format(replacementcode.nil? ? code : replacementcode))}

end loop;

#{countername.nil? ? '' : "select #{counter} as #{basename};"}

close #{name}"
  end

end



## in all cases, the check methods raise an exception if anything is
## not OK (thus not returning); the Query.check method returns self if
## OK

def check (x)
  q = Query[x]
  raise NameError, "There is no query called #{x} (check spelling)" if q.nil?
  q.check
end

## I wonder if this will save typing
def q(x)
  check x
end

def v(x)
  Variable[x]
end

def proc (p)
  "Correct way to generate a procedure"
  puts check(p).procedure
end

def func (f, type)
  "Correct way to generate a function"
  puts check(f).function(type)
end

## you might need to make sure a certain procedure or function is removed

def unproc (p)
  puts check(p).drop_procedure
  puts "\n"
end

def unfunc (f)
  puts check(f).drop_function
  puts "\n"
end

## supposing we quickly would like to generate the loop for a cursor

## we'd need the cursor's name, the list of variables to fetch into,
## the code to execute in the loop, the name of the not-found handler
## (can default to "done"), and perhaps whether to output the counter,
## and what to call it if so