Node-SQLite-NoDep : SQLite for node.js without NPM

Seeing as how Mozilla is slowly phasing out anything that’s not part of Firefox (sorry Mozilla, I think that’s the wrong call… we don’t need YACC… yet another Chrome clone), including XUL, XULRunner, JSShell and so on I’m slowly trying to replace these technologies on our servers. JSShell in particular has been invaluable in automating simple tasks in a sane language.

The obvious replacement is node.js… but as great as node.js is, it has a few shortcomings. In particular, it relies heavily on NPM to provide even basic functionality and as any admin knows, something that can break will eventually break if its too complex. An admin wants a solution that’s as complex as necessary, but still as simple as possible. So, installing node.js along with npm on a machine is a liability. Luckily node.js itself is portable, but since its library interface is unstable, depending on anything from npm is a big no-no.

One thing I frequently need is a little database. Simple text files work too, but eventually you’ll end up duplicating what any database already does. SQLite is an obvious choice for applications with very few users, for example reftest results. But connecting SQLite to node.js without anything from NPM is a pretty ugly task. Luckily, there’s also a commandline version of SQLite and while it may not be as fast as a binary interface, it can get the job done… with a little help.

Node-SQLite-NoDep does exactly that. It launches sqlite3.exe and sends any data back and forth between your node.js application and the child_process , converting the INSERT statements produced by sqlite3.exe into buffers and providing basic bind parameter support. The documentation is not entirely complete yet, but you can find a quick introduction here along with the jsdoc documentation available here.

Basically, all you have to do is grab SQLite.js, drop into into a folder, add a bin folder, drop node.exe and sqlite3.exe and you’re good to go.

const SQLite = require('./SQLite.js');

var testdb=new SQLite('.\\mytest.sqlite',[
    {name:"testTable",columns:"X int,LABEL varchar[64],data blob"},
],function(){
    testdb.sql("INSERT INTO ?testTable VALUES(#X,$LABEL,&DATA)",
    {
        testTable:"testTable",
        X:123,
        LABEL:"Hello World",
        DATA:new Buffer("DEADBEEF","hex")
    },
    function(data){
        testdb.sql("SELECT * from ?testTable",
        {
            testTable:"testTable"
        },
        function(data){
            console.log(JSON.stringify(data,null,"\t"));
            process.exit();
        });
    });
});

is really all you need to see it in action. Just put it into test.js file and launch it with

bin\node.exe test.js

to see it in action.

 


 

 

Commandline: Changing resolution

Just had a common issue this morning that would usually require installing an application, but is very easy to solve using the batch file (GIST) from Thursday’s post:

Changing the resolution from a batch file. Specifically, I wanted to lower my display’s resolution whenever I connect via VNC. The first part is simple: Attach a task to the System Event generated by TightVNC Server (Ok, not that easy… this actually involves using Microsoft’s bizarre XPath subset, since TightVNC’s events are not quite as specific as they should be), then set this task to run a batch file.

Now, for some reason, Microsoft doesn’t include anything to do something as simple as setting the resolution by any other means than calling into USER32.DLL directly… and that call is too complex for little old RunDLL32.exe. .NET can’t do it either without calling into USER32.dll. But at least it makes doing so pretty straightforward.

Declare a struct that matches Windows display properties (no need to declare all fields, I just use dummy byte arrays for any fields that I’m not interested in), then call EnumDisplaySettings to retrieve the current settings into that struct. Change the resolution of the retrieved information and pass it back to ChangeDisplaySettings and voilà.

This is also a good example of how to use arguments with C#.CMD. Just don’t. Save them to environment variables instead and retrieve them via System.Environment.GetEnvironmentVariable . SETLOCAL/ENDLOCAL will keep these environment variables from leaking into other parts of your script.

(GIST)

@ECHO OFF
SETLOCAL
SET RES_X=%1
SET RES_Y=%2
echo @^
    using System.Runtime.InteropServices;^
    [StructLayout(LayoutKind.Sequential)]^
    public struct DispSet {^
        [MarshalAs(UnmanagedType.ByValArray,SizeConst=106)]^
            byte[] padding0;^
        public int width, height;^
        [MarshalAs(UnmanagedType.ByValArray,SizeConst=40)]^
            byte[] padding1;^
    };^
    public class App {^
        [DllImport("user32.dll")] public static extern^
        int EnumDisplaySettings(string a, int b, ref DispSet c);^
        [DllImport("user32.dll")] public static extern^
        int ChangeDisplaySettings(ref DispSet a, int b);^
        public static void Main() {^
            var disp = new DispSet();^
            if ( EnumDisplaySettings(null, -1, ref disp) == 0)^
                return;^
            disp.width=int.Parse(System.Environment^
                .GetEnvironmentVariable("RES_X"));^
            disp.height=int.Parse(System.Environment.^
                GetEnvironmentVariable("RES_Y"));^
            ChangeDisplaySettings(ref disp, 1);^
        }^
    }^
 |c#
ENDLOCAL

 

Assuming you have C#.CMD somewhere in your path, you can now simply call this batch file with horizontal resolution as first argument and vertical as second.

Using C# in Batch files

Batch Files

People usually smile when I say that some parts of our network like the filtering of system events are held together by batch files. It just seems so arcane, but there are some big benefits:

You don’t have to compile anything, you always know where the source code is and you can simply copy them between machines without having to set up anything. And since it’s more or less a legacy technology, Microsoft isn’t really changing a lot anymore, so there’s little chance of an upgrade breaking a batch file.

The only problem is: cmd.exe shell syntax is a horrible, horrible mess and even the most basic string functions can take ages to implement… plus, the code you’ll write look like gibberish to anybody else no matter what you do. Plus there’s the horrible, horrible string escaping behavior and the very strange behavior of variables.

 

PowerShell

So, Microsoft started developing a replacement: PowerShell.exe . And functionality-wise it’s wonderful… it can be run interactively, it doesn’t need compilation, it has useful variables, it can access the system’s .NET libraries… it all sounds wonderful… until you try to run the darn thing. Let’s just say: The syntax is frighteningly bad, never mind the documentation plus the fact that for some bizarre reason you’re allowed to run batch files or EXE files, but you need to set an additional policy before you’re allowed to run PowerShell scripts!

 

The C# Compiler

But enough ranting. Thankfully there’s an alternative that’s preinstalled on all modern Windows System: The C# compiler. Yes, it’s there, even if you don’t have VisualStudio installed. Just enter

dir “%WINDIR%\Microsoft.NET\Framework\v4*”

on the commandline and you’ll see the directory of all installed .NET 4 frameworks, each containing CSC.EXE, which is the C# compiler.

Now, you could just use that, but that means a whole lot of temp files since you can’t pipe to CSC.EXE and you can’t run the code immediately. However there’s another way to access it: Through .NET itself via System.CodeDom.Compiler.CodeDomProvider .

 

Using PowerShell to access the C# Compiler

Thankfully, there’s one thing that PowerShell gets right: Giving you access to .NET . It’s not a pleasant experience, but it is possible. And there’s another thing PowerShell gets right: it allows piping anything to it. So we can use a little PowerShell script that invokes CodeDomProvider.CreateProvider to compile our code on the fly and run it immediately.

It’s really pretty simple:

GIST

$opt = New-Object System.CodeDom.Compiler.CompilerParameters;
$opt.GenerateInMemory = $true;
$cr = [System.CodeDom.Compiler.CodeDomProvider]::CreateProvider
   ("CSharp").CompileAssemblyFromSource($opt,
   "public class App { public static void Main() { "+ $input+" } }");
if($cr.CompiledAssembly){
    $obj = $cr.CompiledAssembly.CreateInstance("App");
    $obj.GetType().GetMethod("Main").Invoke($obj, $null);
}else{
    $cr.errors;
}

It’s really very straight forward. Take STDIN, wrap it in a Main function, compile it, run it, report error if there was one during compilation.Through the magic of horrible cmd.exe paramter escaping, this looks a bit differently when passed directly to PowerShell.exe (3 quotes), but you should still be able to recognize it. Just put it in any old batch file (I’m using c#.cmd which I also added to my system’s PATH variable so that I don’t have to enter the whole path each time), but be sure to put it in a single line, because even escaping the linebreak with “^” won’t work for arguments of PowerShell.exe :

GIST

@PowerShell -Command " $opt = New-Object System.CodeDom.Compiler.
 CompilerParameters; $opt.GenerateInMemory = $true; $cr = [System.
 CodeDom.Compiler.CodeDomProvider]::CreateProvider("""CSharp""").
 CompileAssemblyFromSource($opt,"""public class App { public
 static void Main() { """+ $input+""" } }"""); if(
 $cr.CompiledAssembly) {$obj = $cr.CompiledAssembly.
 CreateInstance("""App"""); $obj.GetType().GetMethod("""Main""").
 Invoke($obj, $null);}else{ $cr.errors; } "

Horrible, I know. But it works.

 

Including C# inline in batch files

Now, if you want to actually include any C# in your batch file, it’s surprisingly straight-forward since the cmd.exe ECHO command actually has very straight forward escaping rules. Well, except for | and & , which you best avoid by using .Equals() . But new lines just need to be escaped with a “^” at the end of the line and a space before the final pipe character. OK, that sounds way worse than it actually is:

@echo ^
var a="Hello";^
var b="World";^
var foo=(a+" "+b).ToUpper();^
System.Console.WriteLine(foo);^
if(System.IO.File.Exists("C#.cmd")){^
    System.Console.WriteLine("Hey, you named it C#.cmd too :)");^
}^
 |c#

That’s what a typical call would look like. Again, note the “^” at the end of each line and the space before “|c#”. Remember this and you will be fine. Of course, you can also put the CSharp code in a separate file and use @TYPE to pipe it directly to C#.CMD, so it won’t need any escaping.

 

Issues

Well, there’s obviously the issue of escaping your code if you use ECHO to include it inline, but I really don’t think there’s any way to avoid it.

There are some issues which are mostly due to the C# code running inside the PowerShell process, rather than the CMD.EXE process. Most importantly: You cannot set environment variables without setting them user- or system-wide. You can set the environment variables of the PowerShell process, but these won’t be visible to the parent CMD.EXE process either. Your only way out is to use STDOUT and STDERR and FOR /F to move it to a variable. If that doesn’t work (which may be the case if you want to include the code inline, because escaping inside a CMD.EXE FOR call is incredibly difficult), you’ll need to transport the information using the filesystem.

And since we’re piping the code to PowerShell, STDIN will obviously not be available… so no ReadLine().

 

TODO

Well, obviously support for commandline arguments would be nice at some point, but I haven’t needed it so far.

It would also be nice if the PowerShell could add the class/main wrapper only if there is no method given in the source code. For now I’m simply using two different batch files, c#.cmd and c#full.cmd

 

Hopefully this will make your life a bit easier 🙂