Unique fields in Access 2007

MySQL has an awesome uniqueness setup with these statements:

... on duplicate key update id=last_insert_id()...

... field2 char(100) not null unique...

insert into tbl (col1,col2,col3) values (
  (select id from tbl2 where f2='x'),
  (select id from tbl3 where f3='y'),
  (select id from tbl4 where f4='z'));

Using AutoIt, the Access statements would be

... field2 char(100) constraint field2u unique...

$query = "select id from tbl2 where f2='x';"
$rs.Open($query, $conn)
$values = $values & "," & $rs.Fields("id").Value
; repeat for other two select statements
; insert into...

For small apps, it’s not bad.


Using Spy++ and AutoIt to automate GUIs

I successfully figured out how to replace mouse movements and prayer with resolution-independent window handles: use Spy++ to survey the target window handles, and use AutoIt’s WinAPI.au3 lib to effect the automation.

Before we go into the feedback cycle, we’ll do some preliminary steps:

  1. Launch your target GUI program.
  2. Launch Spy++ and scroll among the harvest.
  3. Make note of the window handles.

The feedback loop is

  1. Write AutoIt functions using WinAPI.au3
  2. Try it out, sprinkling plenty of ConsoleWrite($hwnd & @CRLF) statements
  3. Refresh Spy++, Tree->Expand All, and scroll down to the relevant handles
  4. Repeat from 1.

Your first goal is to get the handle. Nothing else matters before this. Windows originate from a root Desktop window. It reminds me of a DOM tree. When you hear “tree,” think “traverse.” How do you travel among the nodes? Grab a nearby, easy parent:

$hwnd = WinActivate("Easy Title")
ConsoleWrite($hwnd & @CRLF) ; compare handle in Spy++

Now alternate:

$hwnd = _WinAPI_GetWindow($hwnd, $GW_CHILD)
ConsoleWrite($hwnd & @CRLF)
$hwnd = _WinAPI_GetWindow($hwnd, $GW_HWNDLAST)
ConsoleWrite($hwnd & @CLRF)
$hwnd = _WinAPI_GetWindow($hwnd, $GW_CHILD)
ConsoleWrite($hwnd & @CRLF)
$hwnd = _WinAPI_GetWindow($hwnd, $GW_HWNDLAST) ; are we there yet ?

Once you have the handle, you can try a couple strategies:

  • Use _SendMessageA() and emulate sniffed event messages from Spy++
  • Use _WinAPI_GetWindowRect(), unpack the coords, and use a targeted MouseClick – maybe a few pixels to the right and down for a stubborn combo box, for example.

Automating GUIs is street-fighting: there are no rules. At the very least, Spy++ and AutoIt will let you be systematic with your throat capture. Resolution independence is possible; free your users to use your app with zero coordinate fiddling!


Spyxx.exe is available in the Visual Studio 2013 Community Edition, but I have not tried it.

Teasing out an MVC

Brackeen’s KeyTest.java gives a taste of having a faux model: a LinkedList to hold messages when keys are pressed or released. The inheriting subclass from GameCore consults the list in draw(), which is called in GameCore’s gameLoop() when KeyTest is instantiated and run() in main.

ScreenManager as the view is updated in the game loop away from the KeyTest subclass.

This is all hunky-dory until a couple chapters later, when we hunker down and combine these concepts to make a 2d game. Brackeen refactors his code to several managing classes, so that’s still my TODO.

I really hope OpenBSD 5.4 running on a old laptop can run the game in fullscreen flawlessly. I am worried about ThreadPool; maybe I will have to tune it. The original .jar dropped frames and the music stopped midway. It also didn’t run full screen, so maybe the window compositing contributed to performance issues.

Division II

Earlier I mentioned division as finding the length of ticks given a side of land and the number of divisions upon it:

barHeight = screen.getHeight() / numBars;

Another division is to find the number of ticks instead:

numberOfMessages = screen.getHeight() / FONT_SIZE;

This lets you determine if there are more messages than you can draw on the screen:

if (messages.size() > screen.getHeight() / FONT_SIZE)

Where messages is a java.util.LinkedList.

The intuition of the first kind of division, in terms of multiplication, the product of barHeight and numBars to equal the screen’s height, becomes barHeight added to itself once plus (numBars – 1) times, as if you were filling the height of the space with individual bars of fractional height, totaling then to the full.

The second kind of division is more confusing: messages.size() speaks to a magnitude than a discrete collection – it feels strange to add numberOfMessages plus numberOfMessages (FONT_SIZE – 1) times. In this case, FONT_SIZE is the “barHeight,” and we sum it like this: FONT_SIZE + FONT_SIZE * (numberOfMessages – 1) to equal the screen’s height.

Pressing with this line, does it make sense that

numBars = screen.getHeight() / barHeight;

This should feel similar to the second example.

In fact, it seems there’s only one kind of division and two ways of looking at the term order.

Code from Brackeen.

Event traffic goodies underneath rectangles

Sniffing GUI event traffic in Spy++ reminds me of Fiddler2 and HTTP traffic: you’re sniffing data streams in the tempest beneath, a fisherman in storm, hauling a risky catch for hefty reward. As always, AutoIt masterfully slots itself as the go-to language+lib: DllCall(), WinAPI.au3, and a forums that surpasses StackOverflow in generosity, expertise and sheer volume of examples.

But AutoIt Info wasn’t enough; Spy++ showed me the tree nesting of windows – literally, windows upon windows, with Desktop the root claimant, and briefly I thought how Windows could actually be cool to learn and intellectually profitable. I’m excited to try out AutoIt’s _WinAPI_xxx series of functions; hopefully I can traverse down to the child windows that I hope to target.

I was recently motivated because my work was about to be junked for relying on configuration: the user had to fiddle around with coordinates to get the mouse movement perfect – and it had to be, because we only had the binary. We had to go with a “trainer,” app-as-MMO, with not a ban to await but a good job going. With this method of targeting windows, my app could be resolution independent – a true boon, for it would work on any machine without (x,y) frivolity.

How to start a new project

Open your favorite text editor and start typing a CSV file.

If your project is to learn a new language, you’ve already got the basic roadmap:

  • read from the file
  • populate to an array
  • write back to the file
  • refactor into functions
  • create a GUI
  • convert data to database
  • figure out the ODBC driver string

During the process, you can use shell scripts as glue code; initially, you let the script parse it:

setlocal enabledelayedexpansion

for /f "delims=, tokens=1,2,3,4,5" %%i in (csv-file.txt) do ( 
  java PrintArgs %%i %%j %%k %%l %%m

goto :eof

Then you let Java parse it:

setlocal enabledelayedexpansion

for /f "tokens=*" %%i in (csv-file.txt) do ( 
  java PrintArgs %%i

goto :eof

And then you make it monolithic:

java PrintArgs csv-file.txt

Bonus points for piping an object-oriented program in your daily work:

java PrintArgs csv-file.txt | findstr "RATFOR"

There’s an architecture here somewhere

If I think of a controller as a message sender, and the view and model as message receivers, MVC seems simpler than the last post. So GameCore is the controller because it sends getGraphics() and update() messages to ScreenManager, and some model receives an elapsed time in update() with which to update its sprites and animations. Since it’s example code, the model logic is not separated into another class.

By the same line of reasoning, GameCore’s draw() method is like dividing up processing so that no method gets too long: we would consult a model and call draw() on the Graphics2D context passed in.

Therefore, GameCore acts as a controller, ScreenManager is the view, and the model is a local Sprite[] array, LinkedList, etc.

Why did I want to shoehorn MVC into game programming? Sometimes, it feels like the Wild West: methods called seemingly arbitrarily, objects instantiated without pomp. Putting an MVC structure above it lends sanity to the cause.

By the way, in AutoIt I compile the GUI.au3 file. All its widgets and functions delegate to a Controller.au3 file, but the GUI – the View – calls functions in Controller.au3. So the GUI is a “View-Controller” and Controller.au3 is a manager, and the DAO.au3 source file is the model.

Internally, AutoIt is driven by Win32 libs, right? I guess there is a AWT-equivalent thread that intercepts events? AutoIt is single-threaded and I have to Sleep() in a running while loop, so I guess not.

I suppose this explanation will suffice until my next paradigm shift.

Not theft, but a loan

I was having a lot of difficulty reconciling a core object that does this:

Graphics2D g = screen.getGraphicsContext();

The core object gets the graphics context from another object and draws on it; does that make it a View? I thought it was the Controller. To me, it felt like GameCore was a thief and he was exploiting ScreenManager’s graphics context.

But actually there’s this line in gameLoop() method:


So GameCore is just borrowing the reference, drawing stuff on it, and disposing it; ScreenManager then updates with its buffering strategy. At that moment, the user sees something. That puts ScreenManager as the View. GameCore as a cooperating object “consults the controller” in a “view context” – acting as a View, GameCore-as-View – with the ScreenManager-as-Controller, employing draw() as a populating method and the graphics context as the “controller’s data fetched from a model.”

In AutoIt, it would be View.au3 iterating over a global array in Controller.au3 to populate an invisible ListView. In a sense, the graphics context is ScreenManager’s “global array.” The graphics context is the “global array” of ScreenManager-Controller with a (graphics device abstraction) model.

\#include <Model.au3>

Dim $gArrayInController = GetDataFromModel()

Func GetDataFromModel()
  Local $arr[1] = [ 0 ]
  DAOGetData($arr)  ; model function
  Return $arr

\#include <Controller.au3>
For $i = 1 To $gArrayInController[0]
  Dim $aSplit = StringSplit($gArrayInController[$i], ",")

  ; AutoIt idiom for ListView: populate first field of row,
  ; then the rest of the row
  PopulateFirstCol($listView, $i, $aSplit[1])  ; convenience function
  PopulateRestOfRow($listView, $i, $aSplit)    ; index 2..$aSplit[0]

| View ^
messages | Controller | data
v Model |
GameCore says to ScreenManager, “I know you’re the one who will display the screen; may I borrow your graphics context so that I can act as a View?”

Of especial relevance to this post is Dijkstra: http://lambda-the-ultimate.org/node/264

Brackeen is a godsend to game programming. A lot of folks would be thankful to him if they checked out his book.

Don’t worry – I don’t know what I wrote here, either.

Complexity coma

Do you fall asleep when concepts get gnarly? I’ll doze off when the logic gets knotted, when the mental trace is too hard to follow. (Write better debugging tools!) If things aren’t self-evident by thin-sliced intuition, when line-romp becomes less dungeon and more line-by-line dragon, of my own volition I nod my head and sleep.

This sucks. I’m trying to find ways to stave it off. Fight it with a waving stick like a torch against the dark, in a forest of slumber. Figuratively, the lamp is the fire and it’s cold: cross mine arms and rest –

Rewriting is supposedly the most important part of writing. So rewriting one’s mental knowledge should be right next to cleanliness and godliness. This may only help with new code. For bugs, my constant method has been dozing off and then waking with a sudden clarity of the solution. Or, a realization of the refactoring complexity… Zzz…

I’d be a real hoot at hackathons, I’m sure. A real night owl for Ludum Dare.

Refuel with mental calories

Reading some of _why’s “Poignant Guide to Ruby,” I imagined declaring variable names out of dream stuff:

private int height_of_cloud_nine;

Dim one_girl_in_all_the_world

var salary;

When I have nothing to program but the bareness of numbers, and with no number theory to go by, maybe I can get away with making up a world – a fashionable one, bright and clear and of light and morning, a thousand words behind a declare.

The methods tell a story; the objects abstract abstractions. Explanations absent from comments, but mechanics rightly expounded. A stack for airy candies manufactured between operative widgets, children programming with every box.

I abandoned myths because they meant nothing; I returned to them to feel something.