Showing posts with label IronPython. Show all posts
Showing posts with label IronPython. Show all posts

Sunday, 13 May 2012

SQL Management Studio (SSMS) 2008 R2

Sometime ago I created an Add-In for SQL Management Studio based on IronPython. I have received some feedback and found that there were missing dependencies for the project. In any case I have wanted to correct these dependencies and therefore have created the repositories with all the dependencies here:

http://code.google.com/p/ssms-2008-r2-addins/source/checkout

http://code.google.com/p/procedurelist/source/checkout

http://code.google.com/p/acsr-libraries/source/checkout

Please follow these links to find the source code for the previous Add-Ins that will hopefully contain everything that is required.

These are mercurial repositories so follow the instructions on the page on how to download from these repositories.

Stay tuned for the following post on my new Add-In framework for SQL Management Studio…

Tuesday, 31 May 2011

Sql Server Management Studio: IronPython Extensible add-in

I wanted to write an add-in that helps me with the complicated tasks in SSMS like navigating large procedures or inserting some text like comments.

You can find the download details at the bottom of this document, but first some more info.

The SSMS add-in environment can be a daunting task to figure out so I decided to use my favourite scripting language IronPython to create a framework that I can explore the SSMS environment.

image

I created a control Sql Navigator:
 

To refresh the navigator press: CTRL+SHIFT+F5
(Can be changed in the script file, can also be hooked up to automatic changing of the active window)

The navigator will parse all query windows and extract the different objects and line locations so you can quickly jump to any of the objects in the document.

Take the following code:
   1: CREATE TABLE #TempTable1(
   2:     Id INT NOT NULL
   3:  
   4: )
   5: CREATE UNIQUE CLUSTERED INDEX IX_Clustered ON #TempTable1
   6:  
   7: -- #region Region1
   8: -- some block of code here
   9: -- #endregion
  10:  
  11: DECLARE @Variable1 INT = 0
This is represented above in the navigator.

Want to add more support for different parts of the query window, edit the file:

c:\Program Files (x86)\ACSR\IronPythonAddIn\Scripts\Expressions.txt
Add the name and expressions:

SelectStatements,SELECT.*?FROM[\s]+[\[]?([#\w\.]+)[\]]?
InsertStatements,INSERT[\s]+INTO[#\s]+([\w\[\]\.]+)

And the SQL:

  SELECT * FROM SomeTable
  INSERT INTO SomeTable ....

I also created additional supportive windows:

A Logger Window.

But what is the most useful is the Interactive Window

But lets skip over it for a moment.

All scripts are in the scripts folder:

image

To create a new command, create a new script in this directory:

SSMSSampleCommany.py


Locate the Script SSMSAddin.py

Import your script:


   1: try:
   2:     from SSMSSampleCommand import *
   3:     from SSMSCore import *



Find and create your command:


   1: class DefaultAddIn(SqlAddIn):
   2:     def InternalHookEvents(self, Control):
   3:         
   4:         commandBar = self.CreateCommandBar("IronPython")
   5:         self._sqlNavigator = SqlNavigator(self, commandBar)
   6:         FormattingCommands(self, commandBar)
   7:         sampleCommand = SampleCommand(self, commandBar,  "IronPythonSampleCommand", "Sample Command", "Sample Command ToolTip", 13)       
   8:         sampleCommand.GetCommandInstance().Bindings = "Global::ctrl+Shift+F12";   


Code your command:


   1: from SSMSCore import *
   2: from SSMSUtilityFunctions import *
   3: from System.Windows.Forms import *
   4:  
   5: class SampleCommand(CommandHandler):
   6:     def InternalExecute(self, ExecuteOption, VariantIn, VariantOut, Handled):
   7:         print "MyCommand Executed"
   8:         MessageBox.Show("My Command Executed") 
   9:         self._DTE.ExecuteCommand("File.NewQuery") # opens a new query
  10:         self._DTE.ActiveWindow.Selection.Insert("SELECT GETDATE()")
  11:         self._DTE.ExecuteCommand("Query.Execute")


The sample prints that the command was executed

image

Shows a dialog

image

The opens a new query


image

And Executes it.


Also the Add procedure comment command:


image


The first time:


image

Outputs the comments:

Now make sure the comment is selected and run it again:


image

Regular expressions parses the document and picks up the old history.

Add your comment:

image


Maintains history by appending each comment.


The initials are hard coded, please see the file:


SSMSFormattingCommands.py


If you’d like to change it.

Exploring the environment:



This is one of the exciting parts where you can explore the IDE for objects and method and write test code for your commands or add ins. Or if you just want to manipulate the windows and text.

Here we can explore the SQL Server DTE object get all the methods members and call them in an explorative way.


image


This is enough for now, in a later post I will describe how I built it.

The installation package including all source code can be downloaded from GIT Hub:


https://github.com/TheWalkingDev/Projects/tree/master/SQL%20Server/Add-Ins

 Click on download, and find the package file:

 image

Friday, 13 May 2011

PyPad: Exploring .NET with IronPython

So I have been doing lots of extending with Visual Studio that I’m still about to write about, and wanted an easier way to explore the environmnet.

Enter PyPad I got this idea (bear in mind) you can do this with IronPython console. But something that with a few lines of code I can enter an IDE and explore the objects, inspect them and also the .NET environment.

Consider the following .NET Winform App:

   1: public Form1()
   2:        {
   3:            InitializeComponent();
   4:            Text = "PyPad";
   5:            _pypad = new UcPyPad();
   6:            _pypad.SetVariable("HostForm", this);
   7:            _pypad.Dock = DockStyle.Fill;
   8:            Controls.Add(_pypad);
   9:            Width = 800;
  10:            Height = 600;
  11:            _pypad.RunScript("");
  12:        }

 I am hosting my PyPad control on this form, so it will look like this:

image


So we want to change the caption?

image

Use either the Script Editor

Or the interactive window

Notice each time you run the script you can assign new objects and see them in the Globals list as well as any members they may have.


This way you can “explore” the environment and fix problems bit by bit without restarting the application. I will be looking at more and more creative ways to use PyPad for future projects.
How its built

See my earlier post on Hosting IronPython scripts to get the ScriptController. Then for a beautiful script editor see Schintilla.NET and some controls and Library I put together.

Then

   1: public void RunInteractiveCode(string Code)
   2: {
   3:     LogMessage(">> " + Code + System.Environment.NewLine);
   4:     RunScript(Code);
   5: }
   6:  
   7: public void RunScript(string Code)
   8: {
   9:     try
  10:     {
  11:        
  12:         Compile(Code);
  13:         var retval = _context.Execute();
  14:         if (retval != null)
  15:         {
  16:             retval = _context.CreateLocalScope()
  17:                 .SetVariable("expressionToEvaluate", retval)
  18:                 .Evaluate("repr(expressionToEvaluate)");
  19:           
  20:             LogMessage(retval.ToString() + System.Environment.NewLine);
  21:         }
  22:         UpdateVariables();
  23:  
  24:     }
  25:     catch (Exception e)
  26:     {
  27:         LogMessage(e.Message + System.Environment.NewLine);
  28:     }
  29: }

Get the code
Hosting Project:

https://github.com/TheWalkingDev/Projects/tree/master/DevTools/PyPad/PyPad

Component Library:

https://github.com/TheWalkingDev/ACSR

Sunday, 8 May 2011

Building a Reusable IronPython Hosting Controller

Prerequisitives:
Visual Studio 2010
IronPython 2.7
.NET C# 4.0
So I wanted to host some Iron Python scripts, but always found I had to figure out the sequences and events to host the engine, I therefore came up with a controller and spent quite some time to get it just right in my quest to create extensibility in each and every application with just a few lines of code.
This article assumes that you have downloaded IronPython 2.7
These are the imports I used:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using IronPython.Hosting;
using IronPython.Runtime;
using Microsoft.Scripting;
using Microsoft.Scripting.Hosting;
using Microsoft.Scripting.Runtime;
using System.Collections;
Define these Delegates: (for events from our class)
public delegate void FlushBufferEvent(byte[] Data);
public delegate void MessageEvent(object sender, string Message);
Define these variables:
public event MessageEvent OnMessage;

private ScriptRuntimeSetup _setup;
private ScriptRuntime _runtime;
private ScriptEngine _engine;
protected ScriptScope _scope;

First the Constructor:
_setup = new ScriptRuntimeSetup();
_setup.DebugMode = true;
_setup.LanguageSetups.Add(Python.CreateLanguageSetup(null));
_setup.DebugMode = ADebug;

_runtime = new ScriptRuntime(_setup);
_engine = _runtime.GetEngineByTypeName(typeof(PythonContext).AssemblyQualifiedName);
var bufOut = new BufferedStream(256, 100);
_outputStream = bufOut;
bufOut.OnFlushBuffer +=new FlushBufferEvent(bufOut_OnFlushBuffer);
_engine.Runtime.IO.SetOutput(bufOut, ASCIIEncoding.ASCII);
_engine.Runtime.IO.SetErrorOutput(bufOut, ASCIIEncoding.ASCII);

_SearchPaths = _engine.GetSearchPaths();

In ScriptRuntimeSetup you can specify the debug flag that will allow you to put breakpoints in script files and you can Debug it in Visual Studio. Quite impressive when it works, the functionality though when running in host mode is a bit limited.

The first thing you need to understand when using the IronPython hosting objects is the ScriptScope and ScriptSource.

Think of the Script source as just code and the script Scope as the data/variables.

Therefore you can create one scope and run as many arbitrary scripts against that scope and share all the global variables. This is a really powerful form of introspection and you will see this later as I generate python code to inspect other objects. So lets create some simple usable functions:


ScriptSource getScriptSourceFromString(string Script)
{
_engine.SetSearchPaths(SearchPaths);
return _engine.CreateScriptSourceFromString(Script);
}

public IScriptContext CreateScriptContext()
{
var ctx = new ScriptContext(_engine.CreateScope(), null,
_outputStream, _engine);
return ctx;
}

public IScriptContext CreateScriptContextFromString(string Script)
{
var script = getScriptSourceFromString(Script);
var ctx = CreateScriptContext();
ctx.Script = script;
return CreateScriptContextFromString(Script, ctx);
}

public IScriptContext CreateScriptContextFromString(string script, IScriptContext existingContext)
{

if (existingContext == null)
{
return CreateScriptContextFromString(script);
}
var returnScript = getScriptSourceFromString(script);
existingContext.Script = returnScript;
return existingContext;
//return new ScriptContext(Ctx.GetScriptScope(), script, _outputStream);
}

public IScriptContext CreateScriptContextFromFile(string FileName)
{
// we could use usefile here.
_engine.SetSearchPaths(SearchPaths);
var script = _engine.CreateScriptSourceFromFile(FileName);
var scope = _engine.CreateScope();
return new ScriptContext(scope, script, _outputStream, _engine);
}



These 4 Functions are to create my script context. This will simply be the ScriptSource and the ScriptScope.



When you search the internet for samples of using IronPython in .NET 4, You see plenty of examples of the usefile method, this is really handy because its very few lines and you directly get the ScriptScope that is a dynamic object back. However I wanted additional ways to create code objects. So I have one to create a script from a file, string, or a blank context to use for evaluating on the fly expressions.



Creating the script context object
public class ScriptContext : IScriptContext

Add these variables to the class:
private ScriptScope _scope;
private ScriptSource _script;
private BufferedStream _outputBuffer;
internal ScriptEngine _engine;



Create the following constructor:
public ScriptContext(ScriptScope Scope, ScriptSource Script, 
BufferedStream outputBuffer,
ScriptEngine Engine)
{
this._engine = Engine;
this._outputBuffer = outputBuffer;
this._scope = Scope;
this._script = Script;


}

Make the following objects accesible (public)
public dynamic Scope
{
get
{
return _scope;
}
}
public ScriptSource Script
{
get
{
return _script;
}
set
{
_script = value;
}
}
 
As you can see I made the ScriptScope a dynamic object, so that externally you can directly call methods against it that will only evaluate at run time.
But first we need a reusable way of adding IO support to our scripts, for this we need to redirect output to a stream. I created a class to handle the stream IO and conveniently raise events back to our class for the messages like print statements that we can handle:
I wont go into detail of how to implement stream classes so here is just the class definition of my stream, I called it buffered stream as with any sort of output like this, you may get any amount of characters you need to consume up to a point before you output the string, for now this is just time based, or until you call flushbuffer at the end. The usage of how to assign this class is defined above.
public class BufferedStream : Stream
{


public event FlushBufferEvent OnFlushBuffer;
//private static readonly Peresys.AtMarket.Interfaces.Logging.ILog Logger = Peresys.AtMarket.Logging.LogManager.GetLogger(typeof(GuiStream));

private Stopwatch _sw;
private MemoryStream _ms;
private int _bufferSize;
private int _bufferTime;
public BufferedStream(int BufferSize, int MaxBufferTime)
: base()
{
_bufferSize = BufferSize;
_bufferTime = MaxBufferTime;
_sw = new Stopwatch();
_sw.Start();
_ms = new MemoryStream();
}
#region Ignore Read and Seek
public override bool CanRead { get { return false; } }
public override bool CanSeek { get { return false; } }
public override void Flush() { } // do nothing
public override long Length { get { throw new NotSupportedException(); } }

public override long Position
{
get { throw new NotSupportedException(); }
set { throw new NotSupportedException(); }
}

public override int Read(byte[] buffer, int offset, int count)
{
throw new NotSupportedException();
}

public override long Seek(long offset, SeekOrigin origin)
{
throw new NotSupportedException();
}

public override void SetLength(long value)
{
throw new NotSupportedException();
}
#endregion

public override bool CanWrite { get { return true; } }
public void FlushBuffer()
{
_ms.Position = 0;
var buf = new byte[_ms.Length];
_ms.Read(buf, 0, buf.Length);
_sw.Reset();
if (OnFlushBuffer != null)
{
OnFlushBuffer(buf);
}
_ms = new MemoryStream();
_sw.Start();
}
public override void Write(byte[] buffer, int offset, int count)
{
_ms.Write(buffer, offset, count);
if (_sw.ElapsedMilliseconds >= _bufferTime || _ms.Length >= _bufferSize)
{
FlushBuffer();
}

}
}
Then I wanted to evaluate code against the context that would have a local only scope. For instance If i call python code and assign a variable against the global context. Those variables will appear in the global context, this is not what I wanted so I created a local only scope:
I added a method to the Context class:
 
public ILocalisedScope CreateLocalScope()
{
return new LocalisedScope(this, _engine.CreateScope());
}
 
This class I defined as follows:
public class LocalisedScope : ACSR.PythonScripting.ILocalisedScope
{
ScriptContext _scriptContext;
ScriptScope _scope;
public LocalisedScope(ScriptContext scriptContext, ScriptScope scope)
{
_scriptContext = scriptContext;
_scope = scope;
}
public ILocalisedScope SetVariable(string name, object value)
{
_scope.SetVariable(name, value);
return this;
}
public dynamic Evaluate(string expression)
{
return _scriptContext._engine.Execute(expression, _scope);
}
}

I can now create a local scope like this:
var scope = context.CreateLocalScope();
scope.SetVarialbe("Form", Form1)
scope.Evaluate("formMembers = dir(Form)")
You will notice i can create the scipe evaluate the code get a list back of all the properties of the form assigned to formMembers however the variable will be limited to the scope we created.
But back to the Context class, i added these other methods:
public dynamic GetGlobals()
{
var tempScope = CreateLocalScope();
tempScope.SetVariable("g", _engine.Execute("globals().items()", _scope));
return tempScope.Evaluate("g.sort()\ng");

}

I used the local scope functionality to get the globals from the scope  sort it and return the sorted list. Note that for the engine to return the last statment I hade to put a newline with \n and then just specify the variable to return it.

Add 2 execute methods:
public dynamic ExecuteString(string script)
{
return _engine.Execute(script, _scope);
}

public dynamic Execute()
{         
var result = _script.Execute(_scope);
_outputBuffer.FlushBuffer();
return result;
}

One executes code as string and returns the resulting scope. The other executes the code that was created by CreateScriptSource earlier.