Wednesday, 26 March 2014

Do the SQL Shuffle

A little puzzle arose when I was looking an anonymising client data. At one point I wondered if simply shuffling around some fairly innocuous text fields would be enough to separate some items from other parts of the data. The answer was no, but I still spent some time in the evening figuring out how I'd do it if I needed to - remind me to Get A Life at some point! :)

How to shuffle?


Suppose you have a simple table with an ID field, and a text description. You want to shuffle the 'des' data around so that they all still exist, but in different rows to where they started.

ID

DES
11

line 11
12

line 12
13

line 13
14

line 14
15

line 15
16

line 16
17

line 17
21

line 21
23

line 23

You can play with this using a temporary table (don't forget to DROP it later) :


-- create temp test data
CREATE TABLE #DataTable (
ID int,
Des varchar(10) );

WITH DATA AS (
   SELECT 11 AS ID, 'line 11' AS dez UNION ALL
   SELECT 12, 'line 12' UNION ALL
   SELECT 13, 'line 13' UNION ALL
   SELECT 14, 'line 14' UNION ALL
   SELECT 15, 'line 15' UNION ALL
   SELECT 16, 'line 16' UNION ALL
   SELECT 17, 'line 17' UNION ALL
   SELECT 21, 'line 21' UNION ALL
   SELECT 23, 'line 23' )
INSERT INTO #DataTable (ID, Des)
SELECT * FROM DATA


--  Insert Shuffle code here :)

SELECT * FROM #DataTable;
DROP TABLE #DataTable;


After finding that Rand() only executes once per SQL statement and is therefore useless for a random value per row..... eventually I found that  Abs(CheckSum(NewID())) %90 is a handy way to get the kind of random values I was after. Deciding to try a multi-stage CTE approach, I got this far :


With d1 AS
(
SELECT  -- shove a random value in
  ID,
  Des,
  Abs(CheckSum(NewID())) % 90 as Randm
FROM #DataTable
),
d2 as
(
SELECT  -- now get what we need with order sequences
  ID as ID,
  Row_number() OVER (ORDER BY ID) AS OrigRow,
  Row_number() OVER (ORDER BY Randm) as NewRow,
  Des as ChangedDes
FROM d1
)
SELECT * FROM d2 ORDER BY OrigRow



This gave me the following, and you can play with it at
http://sqlfiddle.com/#!3/fcba5/4/0

ID ORIGROW NEWROW CHANGEDDES
11 1 8 line 11
12 2 1 line 12
13 3 7 line 13
14 4 2 line 14
15 5 5 line 15
16 6 6 line 16
17 7 9 line 17
21 8 3 line 21
23 9 4 line 23

After some deep thought (very deep for that time of night) before retiring to my chamber, I came up with the idea of joining one row to another from the CTEs, from old order to new random order, and linking that to the original row being updated. :


-- Shuffle
With d1 AS
(
SELECT
  ID, Des, Abs(CheckSum(NewID())) % 90 as Randm
FROM #DataTable
),
d2 as
(
SELECT
  ID as ID,
  Row_number() OVER (ORDER BY ID) AS OrigRow,
  Row_number() OVER (ORDER BY Randm) as NewRow,
  Des as ChangedDes
FROM d1
)
UPDATE #DataTable
SET Des =
 (SELECT swap.ChangedDes
  FROM d2 swap
   INNER JOIN d2 old ON
     swap.NewRow = old.OrigRow
  WHERE old.ID = #DataTable.ID
 )
FROM #DataTable



That does the job nicely, and runs quickly enough too. See it in action at
http://sqlfiddle.com/#!3/fcba5/6/0

If you have any better ways to do it, I'm all ears. Comments welcomed, as always!

Monday, 24 March 2014

Anonymising Client Data

(I'm back working again after many long months unemployed, so I should be able to post little odds and ends here again)

If you want to demo your application using real data that you've brought back to the office from a client site, it's rather important to remove real names and addresses, etc., from the data before letting other parties set eyes on it. It won't inspire much confidence to show a potential client another client's data!

If you're using MS SQL Server then you may have the AdventureWorks database to hand, chock full of random names that can be used to overwrite the names and addresses that you wish to remove. Getting a random number to work in a simple UPDATE statement is a bit tricky because the RAND() is only evaluated once - so you'll get a different result each time but all the rows will be set the same :(

So I called upon my favourite CTE know-how and cooked up the following. It's a cascading multi-step CTE affair. First it retrieves the Max(ContactID)from the Contact table, so that the 0-to-1 random number can be scaled up to the full range of the table from which we'll be grabbing random names.

Next, we create a "ChangeData1" temporary table of IDs for the table we wish to change, which will also contain a random row ID from the Contacts table (a starting point to look one up) thanks to getting a random number from the checksum of a NewID(), modulus the maximum row number.

At this point you can then use the next stage of the CTEs to grab a real row number (using TOP 1 where the row ID > the random number) and JOIN to that row, and you'll have full access to the AdventureWorks row to change a number of fields in one UPDATE.

Or you can have this simpler example, where ChangeData2 becomes a list of IDs and one new field from the randomly selected AdventureWorks row.

USE [MyDatabase];

With AW_Names as  -- Get Maximum ID for range of random numbers
( SELECT Max(ContactID) as MaxID
  FROM [AdventureWorks].[Person].[Contact]
),
ChangeData1 as  -- Get ID numbers for table we want to change, and a random num for each row
(
 SELECT
   ID,
   ABS(CHECKSUM(NewId())) % (SELECT MaxID FROM AW_Names) as Rando
 FROM myTable
),
ChangeData2 as  -- Get ID numbers for table we want to change, and a name from AdventureWorks for each random field
(
 SELECT
   ID AS ChangeID,
   ( SELECT TOP 1 LastName FROM [AdventureWorks].[Person].[Contact]
     WHERE ContactID > Rando
   ) As NewField
 FROM ChangeData1
)
UPDATE myTable
SET
  Lastname = ChangeData2.NewField
FROM ChangeData2 WHERE ChangeData2.ChangeID=ID





It seems to run acceptably fast, even a more complex version that updates numerous fields at once, and randomises a Date of Birth column too. Thousands of records safely cleansed in the blink of an eye. This has become my starting point for a long script that will cleanse a number of tables.

Alternatively, you could take a look at Anonimatron, which was my first port of call before I decided to go it alone...

Anonimatron

This free software (a Java .jar) can whizz through tables and fields that you specify in an XML config file, using a variety of new data types (crazy ancient Roman names for example!) and can remember how it did it so that future runs will change the same fields to the same new values if you wish to keep certain parts of your data familiar.

See the author's blog for a guide : http://rolfje.wordpress.com/2013/11/03/anonimatron-quick-start/

Having updated my Java installation (so that .jar programs.can run), I then downloaded Anonimatron from http://sourceforge.net/projects/anonimatron/

I used the folder within the zip download to unzip into a folder :
C:\Program Files\Anonimatron\anonimatron-1.7\
I took a look at the batch file supplied, and converted it to Windows line feeds, and ran it. It complained about memory, not being able to "reserve enough space for object heap". Not a great intro to the joys of Java, but an opportunity to learn...

I eventually figured out what the -Xmx2G parameter was doing, so I tried -Xmx1600m instead, and got that much working. Keeping the original batch safe, I was now using a copy called ano.bat.

Realising that I wanted another batch to play with (to call the main one) I set up ano2.bat to call the first batch as required :
ano.bat -config config.xml -synonyms synonyms.xml

This was enough to do the trick, after I got it connecting to MS SQL, which called for the installation of a free 'JDBC' driver download and install.
Grab Microsoft JDBC Driver 4.0 for SQL Server  from http://www.microsoft.com/en-us/download/details.aspx?id=11774
and then place a copy of sqljdbc4.jar from that installation into
C:\Program Files\Anonimatron\anonimatron-1.7\jdbcdrivers
alongside the MySql and PostgreSQL ones.
If you wish to use integratedSecurity=true in the connection string, you'll need to copy sqljdbc_auth.dll to the main C:\Program Files\Anonimatron\anonimatron-1.7\  folder (or sort out the library path issues!)

Example

If this command line works :
sqlcmd -S myServer\test -d myDatabase -Q "SELECT ID,LastName FROM myTable"
then this XML file config.xml should work - in this case changing a name column to wacky Life of Brian names like Augusnus, Dibocus, Ebodabogubinus etc.!


<?xml version="1.0" encoding="UTF-8"?>
<configuration jdbcurl="jdbc:sqlserver://myServer;instanceName=test;DatabaseName=myDatabase;integratedSecurity=true;">

  <table name="myTable">
    <column name="LastName" type="ROMAN_NAME" />
  </table>

</configuration>


The command parameter -configexample is used to display help, running for me via the batch file:
ano.bat -configexample

This reveals the following possibilities for anonymising your data fields (and you can also write you own 'anonymizers' too, to bolt in)  :

DEFAULT_TYPES
java.lang.String
java.util.Date

CUSTOM_TYPES
RANDOMCHARACTERS
DUTCHBANKACCOUNT
UUID
ELVEN_NAME
ROMAN_NAME
PREFETCHCHARACTERS
RANDOMDIGITS
EMAIL_ADDRESS
STRING
BURGERSERVICENUMMER

The author is responsive to requests for assistance on his blog, or via the dev website, so it's worth considering Anonimatron if it seems to suit your needs. I'm going down the route of rolling my own, any other suggestions are welcomed if you'd like to comment. Thanks.

Friday, 7 June 2013

HTA : HTML-defined GUIs for your PowerShell Scripts

In my last posting http://advancedcase.blogspot.com/2013/05/hta-html-defined-guis-for-your-scripts.html I showed an HTA containing VBScript, to be called from a batch file or Python script.

This time it's PowerShell's turn, and I've converted to JavaScript - because I've been looking at Node.js and how it has made excellent use of JS's functions being first class objects, closures, etc. Before I carry on studying all that fun, I thought I'd finish up this HTA malarkey first.

Time to run a new script .....
C:\>powershell  c:\testps.ps1


The PowerShell Script : testps.ps1 :

This uses Invoke-Expression twice - first to launch the HTA and wait, and secondly to run a piece of PS code that the HTA will have written to the temp folder (using a cmd line echo!) to set the variables.

A line of JavaScript commands is passed into the HTA call, with | vertical bars taking the place of " quotes. This allows you to run whatever code you like within the HTA.



$htacall = 'cmd /c start /wait mshta.exe "C:\your-path\testps.hta" "^ num1=3.14 ; str1=|String Param| ; alert(|Wow!|)"'
Invoke-Expression -command $htacall

$htacall = 'cmd /c type %TEMP%\testhta.ps'
$outp = Invoke-Expression -command $htacall
Invoke-Expression $outp

"Result: " + $ZZ_NUM1 + " " + $ZZ_STR1


 

The HTA with JavaScript : testps.hta :

The script in here is explained in the previous blog posting - well, the VBScript version anyway :)

This time (compared to the VBScript version) the variables are initialised in case the HTA is called directly. The BASIC version didn't complain about undeclared variables, but JS will. And I've removed the batch_path stuff, as it's not used here. As always, any comments are welcomed.

<head> <title>My HTA Test</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize to quarter of screen area, centered window.resizeTo(screen.availWidth/2,screen.availHeight/2); window.moveTo(screen.availWidth/4,screen.availHeight/4); // Initialise vars in case the HTA is called without the expected code num1=0; str1=''; // Passed-in code - will execute to set variables var arrParams = oHTA.commandLine.split('^'); for (i = 1; i < arrParams.length; ++i) { // next 2 lines needed for PowerShell method of calling the HTA arrParams[i]=arrParams[i].replace(/"/g,''); // delete any " arrParams[i]=arrParams[i].replace(/\|/g,'"'); // | become " // alert(arrParams[i]); eval(arrParams[i]); } // SCRIPT-specific code : set controls from executed code txt_num1.value=num1; txt_str1.value=str1; }; function Set_Env_Vars(){ // Write PowerShell code (for exec later) to testhta.ps in %TEMP% folder var oWSS = new ActiveXObject("wscript.shell"); strBat='Echo $ZZ_NUM1=' + txt_num1.value + '; $ZZ_STR1="' + txt_str1.value + '" > "%TEMP%"\\testhta.ps'; oWSS.run('cmd /C ' + strBat ,0,true); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#ccccFF" > <!-- onmousedown = "DisplayMessage" --> <h1>Hello!</h1> Just a little test.... <P> Number &nbsp; <input type="text" value="" name="txt_num1""> <p> String &nbsp; <input type="text" value="" name="txt_str1""> <p> <input type="button" value="Return these to the batch" name="run_button" onClick="Set_Env_Vars()"><p> </BODY>

Wednesday, 29 May 2013

HTA - HTML-defined GUIs for your scripts

Back when I was assessing whether I needed Python in my life, I was trying to find a straightforward way to build a GUI. All the tools listed at python.org seemed to be total overkill for basic needs or not licensed in a way that I found acceptable for the commercial use I had in mind.

Some time later, I was passing the time on StackOverflow seeing if I could rack up some more points for being helpful with some problem solving, and up came a question about GUIs for batch files. HTA was a solution - quite a good one in fact. But it's funny that it seems that MS haven't been too hot about spreading the word about HTA - I have to say that even with all the geek sites that I surf daily it's not something I recall ever seeing getting much of a mention.

An HTA is simply HTML, displayed by the guts of Window's IE engine via mshta.exe - allowing you to display anything a browser can normally show, collect input, and run code that can't normally be run within HTML scripts. For example, write to local files. This means an HTA can be a script with a GUI, or it can be used to create a GUI for a separate script - command batch or Python, etc ...

Good ol' Wikipedia will explain more about HTA (HTML Applications), and MS gives you the basics - so all I can do here is to present a full little example showing how simple it would be to add some spice to a script (Command .bat Batch, Python or other) that needs some user input. Here we can see how simple it is to display the HTML GUI from the script, passing in any required parameters if required, and retrieve the user's input afterwards.

Example HTA

The HTML below is all you need to produce a basic example GUI like this :



<head>
  <title>My HTA Test</title>
  <HTA:APPLICATION ID="oHTA"
       APPLICATIONNAME="HTA Test"
       SCROLL="no"
       SINGLEINSTANCE="yes"
       WINDOWSTATE="maximize"
  >
</head>

<SCRIPT LANGUAGE="VBScript">

    Sub NoContextMenu()
      window.event.CancelBubble= True
      window.event.ReturnValue= False
      ' Alert("Right-click is not allowed, sorry!")
    End Sub

    Sub Window_onLoad
        ' use the following line to disable right-click menu and prevent View Source
        Set document.onContextMenu=GetRef("NoContextMenu") 

        ' resize to quarter of screen area, centered
        window.resizeTo screen.availWidth/2,screen.availHeight/2
        window.moveTo screen.availWidth/4,screen.availHeight/4
        'Msgbox oHTA.commandLine

        ' Passed-in code - will execute to set variables
        arrParams = Split(oHTA.commandLine, "`")
        For i = 1 to (Ubound(arrParams))
          ' Msgbox arrParams(i)
          execute(arrParams(i))
        Next

        ' SCRIPT-specific code : set controls from executed code
        txt_bp.value=batch_path
        txt_num1.value=num1
        txt_str1.value=str1
    End Sub 

    Sub Set_Env_Vars
      ' msgbox "writing " & txt_str1.value
      Set oWSS = CreateObject("WScript.Shell") 
          ' FYI: msgbox oWSS.ExpandEnvironmentStrings("%WinDir%")
      strBat="Echo @SET ""ZZ_NUM1=" & txt_num1.value _
          & """ ^& @SET ""ZZ_STR1=" & txt_str1.value & """ > ""%TEMP%""\testhta-set.bat"
      strBat=strBat & " & Echo @SET ZZ_NUM1=^& @SET ZZ_STR1=^&> ""%TEMP%""\testhta-clr.bat"
      oWSS.run "cmd /C " & strBat ,0,true
      Set oWSS = Nothing 
      self.close
    End Sub 
</SCRIPT>

<BODY bgcolor="#ccccFF">
  <h1>Hello!</h1>
  Just a little test....
  <P>
  Batch Path &nbsp; <input type="text" value="" name="txt_bp"">
  <p> 
  Number &nbsp; <input type="text" value="" name="txt_num1"">
  <p> 
  String &nbsp; <input type="text" value="" name="txt_str1"">
  <p> 
  <input type="button" value="Return these to the batch"
   name="run_button"  onClick="Set_Env_Vars"> 
</BODY>


The <Head> section contains the HTA:APPLICATION tag to specify a number of options about the HTA.

In the <Script> section we have :

*  Sub NoContextMenu()
(along with the first line of Window_onLoad)
Prevents the user from seeing the source code by disabling any right-click pop-up menu which would have had a View Source option. The user won't have any clues to reveal that it's an HTA.

* Sub Window_onLoad
Sets the window size and location, runs the code passed in from the batch, and then uses those newly set variables to set the control values.

* Sub Set_Env_Vars
This is called as the click action for the button, and uses a WScript.Shell object to run a single line of Command Line code (two or more commands can be executed in one line with a & between them) in order to create a temporary batch file in the %TEMP% folder. This - containing environment variable assignments - will be called from the main batch file after the HTA has closed, providing a mechanism to get the values available to the batch file.
Sure, you could write the file the hard way in the VBScript, but why not call a simple piece of familiar command line code instead - with all the %TEMP% decoding for free?

In the <Body> section, we have normal HTML with <Input> controls, and whatever ever else you would need to make things look beautiful.

If you'd rather use JavaScript, yes, that's just as capable. Here's the part that resizes and centers the HTA, and sets the function to run automatically when loading/refreshing :


<SCRIPT LANGUAGE="javascript">
    function Window_onLoad(){  // resize to quarter of screen area, centered
       window.resizeTo(screen.availWidth/2,screen.availHeight/2);
       window.moveTo(screen.availWidth/4,screen.availHeight/4);
       //alert("running");
    }
   window.onload=Window_onLoad;
</SCRIPT>



Example Batch File

You could, of course, call the HTA directly and rely upon it doing whatever it needs to do itself, or arrange for it to call hidden batch files to do any heavy lifting. But this blog post is about calling an HTA from an already-running batch file for user interaction.

This testhta.bat calls the above HTA, passing in some code that gets executed within the HTA - setting some variables. Everything after the backtick ` is interpreted as code by the HTA. Several lines of code can be compressed into one line by using a : between each command.

In this example the %~dp0 trick is passed in (shown in the GUI but not passed back to the batch) to show how you could pass in the batch's path in case you decide to try building in functions within the batch file (chunks of code that get bypassed in normal use but can be used by calling the batch with suitable parameter to enable a goto), to be called from within the HTA - another can of worms of complexity! (with the advantage of containing all your code in just two files, the .bat and .hta)

When control returns to the batch, it calls the temporary batch file that the HTA created, and in this way the cmd environment variables are set and will then be available to use within the batch. Here's an example of the "%TEMP%\testhta-set.bat" contents:
@SET "ZZ_NUM1=5.678" & @SET "ZZ_STR1=Oh, I see." 


@echo off
start /wait mshta.exe "C:\somepath\test.hta" ` batch_path="%~dp0" : num1=3.14 : str1="String Param" : msgbox("Wow!")
if exist "%TEMP%\testhta-set.bat" call "%TEMP%\testhta-set.bat"
echo Result: %ZZ_STR1%

 rem Tidy up and remove Env Vars & temp batches
 if exist "%TEMP%\testhta-clr.bat" call "%TEMP%\testhta-clr.bat"
 if exist "%TEMP%\testhta-clr.bat"  del "%TEMP%\testhta-clr.bat"
 if exist "%TEMP%\testhta-set.bat"  del "%TEMP%\testhta-set.bat"


Yes, I realise that including runable code - in the call to the HTA file - is something that would raise an eyebrow or two amongst purists, but this is just one simple way to pass in data to be used by the HTA - there are other ways. This is for personal use, or where security issues aren't a problem. Just showing the easiest way to get you started!

Python version

Some subtle differences here in testhta.py :


#Python v3.3
print("Hello, World!")

# First, call the HTA - using | in place of any "  - and wait for it to close
from subprocess import Popen    # now we can reference Popen
from subprocess import PIPE
new_process_obj = Popen(['mshta.exe', r'C:\your-path\testpy.hta', r'` batch_path=|no %~dp0 available| : num1=3.14 : str1=|String Param| : msgbox(|Wow!|)'])
new_process_obj.wait()

# retrieve a line of code to exec, from py file in temp folder
process = Popen(['cmd.exe','/c type %TEMP%\\testhta.py & echo '], shell=False, stdin=PIPE,stdout=PIPE,stderr=None)  
output = process.stdout.readline()  
estr= output.decode("utf-8")[:-3]  # delete some trailing "\r\n garbage from the & echo
exec(estr)

# now we have the variables set
print(ZZ_NUM1)
print(ZZ_STR1) 

# pause in case run from Win Explorer double-click
input('Press <ENTER> to continue')


When the HTA is launched, the VBScript code is passed in with the | character used in place of ", for Pythonesque reasons. Also, when the string is retrieved within the HTA it will have an unwanted trailing " - which calls for two replaces :


        For i = 1 to (Ubound(arrParams))
            ' next 2 lines needed for Python method of calling the HTA
          arrParams(i)=Replace(arrParams(i),Chr(34),"") ' delete any "
          arrParams(i)=Replace(arrParams(i),"|",Chr(34)) ' any | becomes "
          execute(arrParams(i))
        Next


One final difference is the method used to pass the values back to the Python script. This time I've used a testhta.py file in %TEMP% to be exec-uted. Here's an example, one line with two commands separated by a semi-colon  :
ZZ_NUM1=3.14; ZZ_STR1="String Param"

This is created by this modified part of the orginal HTA (in Sub Set_Env_Vars)  :

      strBat="Echo ZZ_NUM1=" & txt_num1.value _
          & "; ZZ_STR1=" & chr(34) & txt_str1.value & chr(34) & " > ""%TEMP%""\testhta.py"


Of course, as both the HTA and Python have access to the Clipboard, that could be used for passing data back or forth instead, as one of many alternatives.

PowerShell

I don't want to duplicate someone else's fine work, so have a good read here :
http://9to5it.com/using-html-applications-as-a-powershell-gui/
In this case the author is starting from the HTA and launching the PowerShell from there, instead of the other way around. Whatever seems logical to you!

I have rewritten the HTA in JavaScript and I provide a PS script in my follow-up posting
http://advancedcase.blogspot.com/2013/06/hta-html-defined-guis-for-your.html

Monday, 27 May 2013

21st Century Pascal

I worked with Pascal for a number of years, so I'll always have an affection for the language of Begins and Ends. It was created in the late 1960s as a very readable and structured general purpose language, and was obviously so suitable for the task that it caught on widely for teaching purposes. As the years passed, MS VB managed to capture the mindset of the masses, then we saw the competing platforms of Java and .NET. Pascal languished on the sidelines, with most of its fans using Delphi, but along the way the procedural language gained full OOP abilities that brought it up to date and able to hold its own.

Pascal finds itself still incorrectly maligned as an 'obsolete training language' (very far from the case!) while the world assumes that C-like languages of curly braces { } are the One True Way - as if a little change in syntax flavouring really makes any difference! I do prefer more verbose languages given the choice, begins and ends just stand out more than tiny { and }s. You'll find that 'muscle memory' makes typing begin as quick as entering a password, and a modern Pascal code editor will autocomplete the matching end for you.

Here's some reading material comparing Pascal and C : http://en.wikipedia.org/wiki/Comparison_of_Pascal_and_C
and assorted opinions are offered here : http://programmers.stackexchange.com/questions/114846/why-has-c-prevailed-over-pascal

In the end, we can easily conclude that by the time you 'outgrow' Pascal (if ever?) then you'll be experienced enough to switch to the more terse C (or descendants) without any great trouble - so it's still a great way to start programming.

Free Pascal and Lazarus

And here's a great reason to get going with Pascal. It's Free! The Free Pascal project has brought us an Open Source compiler, and the Lazarus IDE is also an impressive piece of work for development. In one easily installed all-in-one IDE application, it's a snap to produce GUI programs with standard controls and much much more. It talks to all the major databases and makes Pascal a credible alternative to other languages.

Your Lazarus/FPC program will compile to a standard .exe that requires no runtime framework, is as fast as other compiled languages (leaving interpreted scripting languages in its dust), and all this for no cost and no vendor lock-in. Free! What are the alternatives? Java is still at the mercy of Oracle, and .NET has Mono, but .... isn't Totally Free quite appealing? Scripting languages like Python and Ruby have their fans, but after studying them to my satisfaction I found I simply didn't need them (yet?) and creating GUIs seems to be a nightmare compared to the Lazarus drag-and-drop simplicity.

Installation

Hundreds of users are downloading every day, from Source Forge. I downloaded the 110MB file lazarus-1.0.8-fpc-2.6.2-win32.exe, and it was an easy install with no tricky questions to confuse anyone :



 When you fire it up, you'll find that the various parts of the IDE user interface are separate windows :


but this is nothing to worry about, even if you have previous experience with Visual Studio it's no biggie - you get used to it (so easily that they've never been motivated to change this).

If you don't like the rather nasty default font, it can be changed easily via Tools.. Options.. Editor..Display so you can make it look as smart and contemporary as you like :

... and I was also able to change the Default Text Background colour to some something slightly darker than bright white glaring into my eyeballs.

Almost too easy!

In moments you can add a button to a form, and display a mesage with showmessage :

So why not give it a try? There's a helpful Wiki and the forums are friendly. It must be straightforward because most of the forum questions are of a quite advanced technical nature - nobody seems to need to ask about the basics and run of the mill queries.

I believe Lazarus is unmatched in its niche, so have a go!

Thursday, 23 May 2013

AutoIt or AutoHotkey?

It can be very useful indeed to be able to run simple scripts to set up macro hotkeys to fire off a whole load of commands, even controlling other running apps. Even better if you can use it for more interactive tasks, as a general purpose programming tool, allowing neat and functional GUIs with ease.

For example, for my radio use I developed an AHK script that can be drag-n-dropped onto a standalone interpreter .exe (portable - no install required) that fires up several small copies of WinAmp audio player, each configured to a different soundcard and routed to its own channel on the mixing desk. It gives me the ability to select a random track at the press of a button, from the library of hundreds of tracks - ensuring variety by removing any tendency to stick to my same old favourites.

My script can stop/start a WinAmp instance by referencing its name (WinAm1/WinAm2/WinAm3 etc), mute the audio or bring it up to normal again, seek to 30s into the track (lots of tracks take time to get going, so it's quicker to audition them some way in, when they're well under way), pause, restart, etc. Another window shows me a list of jingles, and double-clicking one will automatically load it into the usual 'cart' ready to play, and tick a box so that I can see that I've already used it in the show. The small main GUI window has an Exit button that closes everything it opened, tidying away everything in one fell swoop.

The script is over 400 lines long, and it helps that Notepad++ colours the syntax for me (Language.. A.. Autoit) but I'm glad I commented as I went (back in 2011) because a couple of years down the line there are parts that are now bafflingly odd without my comments to explain it!

So... AutoIt or AutoHotkey?

In the beginning there was AutoIt and it was good. But lacking. Lacking enough for Chris Mallett to break away and develop AutoHotkey as an alternative, because his proposal to integrate hotkey support into AutoIt v2 failed to generate response from the AutoIt community.

Chris Mallett : "The AutoHotkey project began about two years ago because I loved AutoIt v2 but wanted faster, better, and built-in hotkeys. Although AutoHotkey does not use any AutoIt v2 source (I didn't even know its source had been released), it does use AutoIt v3 source for about 34 commands, such as Process and FileCopy. In addition, AutoHotkey includes ahk2exe, which is a modified version of Jon's script compiler. In addition, it includes AutoScriptWriter (the macro recorder) and Window Spy, both of which were written by Larry Keys. So thanks to Jon, Larry, and the other AutoIt developers for all their innovation, hard work, and generosity."

So that explains why AHK exists. Me, I had already gotten used to AHK's odd syntax and was well underway to achieving what I needed before I heard about AutoIt, so I wasn't sufficiently impressed to switch even if I had reason to. I found the AHK forums and documentation were friendly and helpful, so there wasn't any point in changing just for slightly clearer more BASIC-like syntax in places. I can adapt to anything, verbose BEGIN/END, C/Java/etc's {} or Python's Significant Whitespace - it makes no real difference to me. Sometimes AHK drives me nuts and it seems like Every. Single. Line. Of. Code. needs to be researched as I go, but it's worth the pain in the end. There is no simpler way to intercept hot keys and control other programs already running. How much code would it take in a conventional programming language to do the following?

 ; simple example
F12::
msgbox You pressed F12
return

That's an AHK script in its entirety - and one of these four lines is a comment! Even though - as a software dev - I find it painful that the message isn't in quotes (to be fair, you can do that if you want - msgbox % "You pressed F12" - but that doesn't make the scripts all over the www more readable), it's still packing a remarkable amount of functionality into those few lines, tapping into the very heart of Windows to intercept the key handling routines.

A quick bit of more history, to help you to decide between the two competing camps :
As AHK progressed, tension arose between AHK and AutoIt - "AutoIT is no longer open source. Before AutoHotKey existed, AutoIT v3 was open source. According to the AHK help file, AutoHotKey adapted source code from AutoIT v3 such as Window Spy and the script compiler. When Autohotkey launched, disagreement happened between the creators of AutoIt and Autohotkey because Autohotkey placed advertisements on Google that directly targeted AutoIt v3 searches and displayed Autohotkey as a "sponsored link" ad for Autohotkey app. This included posts on the AutoIt forum that the Autohotkey creator posted with link to his software. Shortly afterwards, a new version of AutoIt was released as closed source." - http://www.wikivs.com/wiki/Autohotkey_vs_AutoIt_v3

.. so the upshot is : no matter what the capabilities of either program, if you prefer to select your software based upon how Open it is, then only AutoHotkey fits the bill; AutoIt may be Freeware but it is Closed Source. With Closed Source I always wonder why - what are they hiding and for what good reason? With Open Source it feels like the project will live for as long as I could possibly want and I won't be wasting my time learning it.

Want to decide based upon more technical merits? http://stackoverflow.com/questions/1686975/choosing-a-windows-automation-script-language-autoit-vs-autohotkey is a good start. Or Google for : autoit vs autohotkey

Wednesday, 22 May 2013

Turbo Pascal 3 revisited

For some reason thoughts popped into my head of an old program I wrote to produce a Lissajous figure on the screen, in Turbo Pascal 3 back in the 1980s. This is done by plotting X and Y coordinates from two independently running sine waves.


At the time it ran very smoothly and impressively fast (I thought!) on my old DOS box - a gloriously primitive twin-floppy box with a mono CRT and IIRC it didn't even have a full 640k of RAM - and sat there rotating gracefully like a trace on an oscilloscope.

Looking at the file date it looks like I played with it again in 1995, and I vaguely remember it had sped up a bit on a more advanced Win 95 box, hehe.

Here's the code, FWIW - note the repeat.. until 1=2 will require Ctrl-C to break out :)

program liss;
VAR px, py : Real;  { phase in rad X,y-axis }
VAR ox, oy : Real;  { phase in deg X,y-axis }
    stepy : real;
CONST  ScXs = 38;  { Scr X Size }
       ScYs = 10;  { Scr X Size }
       StepX = 0.1;
begin
  StepY:=StepX/2.06;
  textColor(15);
  ClrScr;
  Px:=0.0;
  py:=0.0;
  ox:=-(StepX)*130;
  oy:=-(StepY)*130;
  Repeat
    ox:=ox+StepX;
    oy:=oy+StepY;
    If ox>6.2831 THEN ox:=ox-6.2831;
    If oy>6.2831 THEN oy:=oy-6.2831;
    GotoXy(Trunc(41-(SIN(oy)*ScXs)), Trunc(13-(SIN(ox)*ScYs)));
    Write( ' ');
    px:=px+StepX;
    py:=py+StepY;
    If px>6.2831 THEN px:=px-6.2831;
    If py>6.2831 THEN py:=py-6.2831;
    GotoXy( Trunc(41-(SIN(py)*Scxs)), Trunc(13-(SIN(Px)*Scys)));
    Write( 'þ');  { use block character obtained by
                    holding ALT and typing 2 5 4 on the numeric keypad }
  until 1=2;
end.
Needless to say, on modern kit it runs like lubed lightning, comparatively - even without XP's ALT+ENTER full screen textmode. I even tried it in DOSBox for a laugh - rather more slow there.
To install and play with TP v3, it can be downloaded (ZIP) free of charge from the copyright holders (seeing as it's an antique!) from here : https://downloads.embarcadero.com/free/turbopascal

I found a PDF manual at bitsavers.informatik.uni-stuttgart.de

I used 7zip (FOSS) to unzip the download into C:\DATA\TP\ and had a blast from the past. I can still remember Crtl-KB and Ctrl-KK to mark text, Ctrl-KC to copy, Ctrl-KD to exit :)

In fact, I still used turbo.com as a text editor for years, even when we moved on up to Metaware's Pro Pascal. It was handy for general text file use, until the 64k file size limit started to get in the way, and then I moved on to Win32Pad as it was easier than NotePad to retain my familiar DOS-like white-on-black colour scheme. From there I migrated to Notepad++ for coloured syntax.

TURBO runs in B&W mode at first, and you will need to set the TINST.MSG and TURBO.COM files to ReadWrite (if the unzip process sets them ReadOnly) before you'll have any luck setting the colour mode to 80x25 colour using the installer TINTST.COM

In this day and age I suspect that Pascal newcomers tend to go straight to FreePascal and the Lazarus IDE, but this old clunker would certainly be one way to start from the BEGINning :)

Workflow Automation


Another little program of mine was a working environment like this :


This was back in days of 8.3 filenames, and even with Win95 I still worked at the command line. So I rigged up this code to present pages of directory contents in alpha order, with the arrow keys moving the tab-card shaped surround around the files. I could tab between two different folders and selected files. At the push of a button I could do whatever I needed with the selected file - edit, launch all the usual batch files to compile, link, check in/out from version control, etc.

It was launched with two quick almost-adjacent keys because I found I could call a batch file '.bat - single quotes are valid in a DOS filename. The batch called my turbo pascal .com program, and that in turn rewrote the same batch file that had called it.. while it was still running! Odd, perhaps, but it worked well so long as the folder and filenames within the batch were padded with trailing exclamation mark characters so that the batch was always the same size with everything in the same place - otherwise poor old DOS got a bit confused by the mid-run rewrite :)

My turbo .com program launched further processes by adding the command to the end of the '.bat and then exiting - one batch file chained into another (no 'call' command with two batches in memory at once) so as much memory was available for compiling as if I'd typed the command at the prompt directly. This was vital when memory was at a premium and sometimes I had to be really careful to optimise my code to get things to compile! It all worked like a charm and made life a lot easier. I do enjoy harnessing the power of lazyness to make life easier.

Those were the days! Not sure I'd want to go back though, to be honest :)