Getting running queries in SQL Server

Today I needed to get running queries in SQL server (you can kill them with the command KILL followed by the session id). After 'binging' it, got to the following query :). Need to study more about the metaschema in sql server.

SELECT session_id,
       request_id,
       Db_name(database_id),
       start_time,
       status,
       command,
       Substring(txt.TEXT, ( statement_start_offset / 2 ) + 1,
       ( ( CASE statement_end_offset
       WHEN -1 THEN Datalength(txt.TEXT)
       ELSE statement_end_offset
                                                                   END
           - statement_start_offset ) / 2 ) + 1) AS statement_text,
       wait_type,
       wait_time,
       blocking_session_id,
       percent_complete,
       cpu_time,
       reads,
       writes,
       logical_reads,
       row_count
FROM   sys.dm_exec_requests
       CROSS APPLY sys.Dm_exec_sql_text([sql_handle]) AS txt
WHERE  session_id <> @@SPID
       AND session_id > 50

Automatas (finite state machines): A programmer's perspective

Finite state machines are simple and nifty; when I was teaching theory, I saw students, even good programmers, had trouble with them when presented as math, so I figure showing them as programs could make it click for some people.

From http://cg.scs.carleton.ca/~michiel/TheoryOfComputation/ (a free ToC book)

Definition 2.2.1 A finite automaton is a 5-tuple M = (Q, Σ, δ, q, F), where
1. Q is a finite set, whose elements are called states,
2. Σ is a finite set, called the alphabet; the elements of Σ are called symbols,
3. δ : Q × Σ → Q is a function, called the transition function,
4. q is an element of Q; it is called the start state,
5. F is a subset of Q; the elements of F are called accept states.

Doesn't that sound fancy ? It means we have an object with 5 fields (a 5-tuple), Q, a set of things called states, Σ, a set of symbols called the alphabet (we'll use characters as our symbols), δ, a function that takes a state and a symbol and returns a state (conceptually we are 'moving' from one state to another when we see a symbol), q, an element of Q , called the start state and F a subset of Q, the set of accepting states.

In Scala, we can represent it as:


We can then define the extended transition function, which says which state we end up at by following a string, recursively as

This means if the string is empty, we stay at the state we're at; if not, we follow the first character (by using delta), and then follow the rest of the string from there; this recursive definition maps nicely to a List in scala (or any other language with cons-style lists :), so we end up with the following code:


And the automata accepts a string if we would go from the initial state to the final state by following that string; in Scala
To use it we would first define a delta function, say:
And then we can use like this:
The full code is at https://github.com/okaram/scala/tree/master/automata, including non-deterministic automata (which I will probably get around to blog about some other time :)

Accessing Databases with R (RStudio)

R has libraries for accessing several databases, but if you're on Windows, you'd probably prefer the RODBC package; with this, you can access almost any db through ODBC. You can create a DSN and use odbcConnect, or use odbcDriverconnect ; the problem is figuring out the connection string, especially since you need to specify the driver. For SQL Server, this works (assuming db is in localhost):

library(RODBC)

c=odbcDriverConnect('Driver=SQL Server;Server=localhost;Database=......;Trusted_Connection=True')

And then you can do odbcquery like:

res=sqlQuery(c,"SELECT * FROM ....")

And you get a data frame !

http://www.connectionstrings.com/ has odbc connection strings for many dbs

Super quick Linq to XML

I was trying to figure out how to get started with Linq to XML, and found this article. I figured I'd simplify it even more for myself :)
Imagine we have the following file, saved in c:\contacts.xml

Then we can access it as follows
Notice:

• on an XDocument, we can call descendants and pass it an element name, to get all the elements with that name (we can also call the no-args version, and get all descendants)
• What we're getting is an XElement, on which we can call methods like Element or Attribute
• We can directly cast an element into a string, and it gets us all the text inside that element
• We can cast an attribute as a string, or an int (or even a float, double or many other types)

C# is pretty expresive too

I just saw this post , about how to add methods to types in F# (and referencing another in rust), so you can make words like Months and Ago have meaning and allow you to say things like '3 years ago'. Since C# now has extension methods, I figured I'd try to see how it could be done in C#


So, what do you think ?

BTW, I tested this in LinqPad, may need something extra to become a full program

Personal mongodb Cheatsheet

I'm trying to learn more mongodb (taking one of their free courses), so I figured I'll keep here the things that I use often (and maybe it will be useful to others).

Printing

You can use the print method to print in the shell print('hello')

Iterating over a cursor

The value returned by find and findOne is not a collection but a 'cursor', so you can't just do 

for(var obj in db.xyz.find())

you need to do

var cursor=db.xyz.find()

while(cursor.hasNext()) {

  obj=cursor.next();

  ...

}

or define a function and use forEach()

Azure for hosting HTML files

I'll be teaching a friend some HTML, and was looking for an easy, free host for playing sites; Azure has free websites, but I was afraid I'd need to teach them webmatrix of VS express (nothing against them, but it is one more thing to learn). I just discovered Azure can deploy sites from a git repo !! yipee !