OWASP O2 Platform Blog

The need of supporting REST Services on top of OWASP O2 Platform

I have been talking to Dinis Cruz   about the importance of supporting REST Services on top of OWASP O2 Platform. Web Services, now a days, are a strong platform in software engineering and looking at the future, it is going to be even stronger than now.

But, what is REST and why should we embrace it?

REST stands for Representational State Transfer, a term coined by Roy Thomas Fielding  in 2000 as a part of his dissertation. So as you can see this is not a new topic, but I would say it has been  broadly embraced during the last years by the industry. REST is not an architecture, but it is an architectural style to build services on top of the Web, it is basically a set of constraints based on the core principles of the WEB. REST uses all the strengths of the WEB that have made it the most distributable and interoperable system.

Web Services are not a new topic, people (even those outside of the computing world) have been using this word to refer themselves to interoperability and accessibility, specifically in business, where this topic as been adopted as a platform to provide information around the world.

But we are always looking for a better approach,  a best way to provide solutions, with low-cost and with reasonable responses times to the end-user and of course we are also concerned about security

Interoperatibility, Scalability , Performance and Security :A combination of topics that we would like to achieve.

REST Services  take advantage of the WEB , using the principles defined to make it the most  distributed system, rather than focus on actions (like SOAP), it uses all the HTTP verbs to interact with resources  and it does not relay only in HTTP POST. The need of creating more scalable  services is a challenge everyday, specifically because we are moving to the mobile world and we have some limitations that force us to use a better approach to improve the user experience.

But we are not just concern about performance, we are also concerned about security. If you are thinking why a person would attack a web services, then I would say that due to the interoperability and the data exchange performed in Web Services, the attacker would expect to find any way to get into the back-end system or steal any kind of information.  Having said that, we would like a mechanism  add value with O2 Platform into the REST world.

How could we integrate REST Services on top of O2 Platform?

I’m happy to say that we are currently supporting OData Services in O2 Platform . OData is short for The Open Data Protocol and is consistent with the way the Web works.  Dinis  added support for OData in O2. This is an important step because we are able to work not only with SOAP based Web Services but also with REST Services which increase all our coverage in the Web Services world.

The  next steps would be to write use cases and probably some automation work and  Unit Testings that increases the support of REST in  O2 Platform.

May 12, 2012 Posted by | Uncategorized | , , , | Leave a comment

Managing graphs in O2 Platform using a .Net Graph control

Dinis Cruz wrote a great post that shows how to manipulate graphs using O2 Platform, the complete article can be found at http://diniscruz.blogspot.co.uk/2012/05/visualizing-teammentor-links-structure.html .

That is done using O2’s support for  http://graphsharp.codeplex.com/  which is a great .Net Graph control.

The script that creates it is quite simple (see https://gist.github.com/2640284 ).

May 9, 2012 Posted by | O2 Scripting | | 2 Comments

Using O2 Platform and HacmeBank

Michael from the OWASP Costa Rica chapter wrote a great article about O2 and his first use of using it to automate HacmeBank’s login sequence: Starting with OWASP O2 Platform : a short step in a long journey

In reply I wrote Using O2 with HacmeBank which gives an overview of what you can do today with O2+HacmeBank  (and ideas for where to go next)

May 6, 2012 Posted by | .NET, HacmeBank | Leave a comment

PoC of IronPython REPL Scripting Environment

Based on http://www.ironpython.net/2.7 , here is an O2 script that creates a simple Python REPL scripting environment

Gui Created

Source Code

var topPanel = "PoC - IronPython REPL - Simple".popupWindow(800,400);
topPanel.insert_LogViewer();
//var topPanel = panel.clear().add_Panel();

var code = "print 2+2;";

Action<string> executeScript = null;
new API_ConsoleOut().show_ConsoleOut(topPanel.title("Console Out"));
var codeEditor = topPanel.insert_Left("Python Code").add_SourceCodeViewer().set_Text(code,".cs");

var cmdLine = new PythonCommandLine();
var engine = Python.CreateEngine();
var runtime = engine.Runtime;
executeScript = (script) =>	{
								try
								{
									"Execucuring code".info();
									var source = engine.CreateScriptSourceFromString(script, SourceCodeKind.AutoDetect);
									var mod = engine.CreateScope();
									source.Execute(mod);
								}
								catch(Exception ex)
								{
									ex.log();
								}
							};

codeEditor.onTextChange((text)=> code = text);
codeEditor.parent().insert_Above(20).add_Link("Execute Pyhton code" , ()=> executeScript(code)).click();

//using Microsoft.Scripting
//using IronPython
//using IronPython.Hosting
//O2Ref:IronPython.dll
//O2Ref:Microsoft.Dynamic.dll
//O2Ref:Microsoft.Scripting.dll
//O2File:API_ConsoleOut.cs

April 23, 2012 Posted by | Python | Leave a comment

39 O2 Platform videos with 12k YouTube views

After uploading the O2 Installer video, I took at look at the O2 related videos stats and was amazed to see that there were 20 subscribers with about 12,000 views of O2 related videos 🙂

Looking at the list of those videos, there are some really good gems in there, so for reference here they are:

If you want to see how I created this list, take a look at the first video or at O2 Script to get YouTube videos list

April 21, 2012 Posted by | videos | Leave a comment

O2 Script to get YouTube videos list

Based on the Accessing YouTube via its C# API script , here are two more uses of the YouTube C# API:

Video

Scripts

Create HTML list of videos (used to create this blog post entry)

panel.clear();
var googleDevKey = "AI39si4VZRef7hm9N-RBZ5u-5x5N2lIgyEvv45dGgDxCJjltduhlnF9tTmfWV55tFMUWbnThHybbipiXiEufJueJPpF65lkyjg ";
var settings = new YouTubeRequestSettings("API_YouTube",googleDevKey);
settings.PageSize = 50;
var youTubeRequest = new YouTubeRequest(settings);

Feed<Video> feed = youTubeRequest.GetVideoFeed("DinisCruz");

var webBrowser = panel.add_WebBrowser_Control();

var htmlCode = "<ul>";
var index = 1;
foreach(var entry in feed.Entries)
{
    htmlCode += "<li><a href='{1}'>{0}</a>  - {2:MMM yy}</li>"
                    .format(entry.str(),
                            entry.WatchPage.str(),
                            entry.AtomEntry.Published,
                            index++);
}
htmlCode += "</ul>";
webBrowser.open(htmlCode.saveWithExtension(".html"));

return "done";

//using Google.YouTube
//using Google.GData.Client
//using Google.GData.YouTube
//O2Ref:Google.GData.Client.dll
//O2Ref:Google.GData.YouTube.dll
//O2Ref:Google.GData.Extensions.dll

Mini Gui to View Blog Posts

panel.clear();
var googleDevKey = "AI39si4VZRef7hm9N-RBZ5u-5x5N2lIgyEvv45dGgDxCJjltduhlnF9tTmfWV55tFMUWbnThHybbipiXiEufJueJPpF65lkyjg ";
var settings = new YouTubeRequestSettings("API_YouTube",googleDevKey);

var youTubeRequest = new YouTubeRequest(settings);
Feed<Video> feed = youTubeRequest.GetVideoFeed("DinisCruz");
var treeView = panel.clear().add_Panel().add_TreeView();
var webBrowser = treeView.insert_Right().add_WebBrowser_Control();
treeView.afterSelect<Video>(
	(video)=>{
				webBrowser.open(video.WatchPage.str());
			 });
foreach(var entry in feed.Entries)
	treeView.add_Node(entry);
treeView.selectFirst();
//feed.details();
//panel.add_TableList().show(feed.Entries);

return "done";

//using Google.YouTube
//using Google.GData.Client
//using Google.GData.YouTube
//O2Ref:Google.GData.Client.dll
//O2Ref:Google.GData.YouTube.dll
//O2Ref:Google.GData.Extensions.dll

April 21, 2012 Posted by | Interoperability | 1 Comment

Set .NET WebBrowser Control to use latest version of IE

O2 Script to change the IE Script environment to use the latest version of IE

//note you will need to run this wihout UAC
"O2 log Viewer".popupWindow().add_LogViewer();

//for more details:    http://www.west-wind.com/weblog/posts/2011/May/21/Web-Browser-Control-Specifying-the-IE-Version
//                     http://msdn.microsoft.com/en-us/library/ee330730%28VS.85%29.aspx#browser_emulation
try
{
    var IEVAlue =  9000; // can be: 9999 , 9000, 8888, 8000, 7000
    var targetApplication = Processes.getCurrentProcessName() + ".exe";

    var localMachine = Registry.LocalMachine;
    var parentKeyLocation = @"SOFTWARE\Microsoft\Internet Explorer\MAIN\FeatureControl";
    var keyName = "FEATURE_BROWSER_EMULATION";
    "opening up Key: {0} at {1}".info(keyName, parentKeyLocation);
    var subKey = localMachine.getOrCreateSubKey(parentKeyLocation,keyName,true);
    subKey.SetValue(targetApplication, IEVAlue,RegistryValueKind.DWord);
    return "all done, now try it on a new process".info();
}
catch(Exception ex)
{
    ex.log();
    "NOTE: you need to run this under no UAC".info();
}

//using Microsoft.Win32
//O2File:_Extra_methods_Windows.cs

April 17, 2012 Posted by | IE Automation | Leave a comment

Exporting Checkmarx SAST Database into XML files

Once I added VistaDB support to O2, I was able to export the CWE content from CheckMarx database into TeamMentor  (see videos at PoC of integrating TeamMentor with Checkmarx),

This post covers the part where we export the entire Checkmark database into flat XML files (for the script that consumed the CWE.xml see Case Study – Creating a CWE Library from CheckMarx data )

Viewing table data: Configurations

var vistaDB = new API_VistaDB();
vistaDB.ConnectionString = @"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'";

var dataTable = vistaDB.table("Configurations").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs

Viewing table data: CWE

var vistaDB = new API_VistaDB();
var dataTable = vistaDB.table("CWE").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs

Get table as XML

var vistaDB = new API_VistaDB();
var xml = vistaDB.table("Configurations").xml();
return xml;

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files.

View table in DataGridView

var vistaDB = new API_VistaDB();

var dataTable = vistaDB.table("Configurations").dataTable();
panel.clear().add_DataGridView().dataSource(dataTable);

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Saving one table as XML file

var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var tables = vistaDB.tables();

var table = tables[0];
var targetFile = checkMark_XmlDumps.pathCombine(table.Name + ".xml");

return table.xml().saveAs(targetFile);

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Saving all tables

var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
foreach(var table in vistaDB.tables())
{
 "saving table: {0}".info(table);
 var targetFile = checkMark_XmlDumps.pathCombine(table.Name + ".xml");
 table.xml().saveAs(targetFile);
}
return "done";

//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

Consuming  Saved CWE.xml

var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var cwe = checkMark_XmlDumps.pathCombine("CWE.xml").xRoot();

return cwe.elements().size();

Consuming saved CWE.Xml via cache: (faster)

var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);var cwe = "cweData".o2Cache<XElement>(()=> checkMark_XmlDumps.pathCombine("CWE.xml").xRoot());
return cwe.elements().size();
Tool to visualize the CWE data as HTML pages
var vistaDB = new API_VistaDB();
var checkMark_XmlDumps = "_CheckMark_XmlDumps".tempDir(false);
var xRoot = "cweData".o2Cache<XElement>(()=> checkMark_XmlDumps.pathCombine("CWE.xml").xRoot());

var topPanel = panel.clear().add_Panel();
var webBrowser = topPanel.add_WebBrowser_Control();
var treeView= webBrowser.insert_Left(200).add_TreeView();
var codeViewer = webBrowser.insert_Below().add_SourceCodeViewer();

treeView.afterSelect<string>(
 (text) =>
 {
 webBrowser.open(text.saveWithExtension(".html") );
 codeViewer.set_Text(text.htmlDecode().tidyHtml() , ".html");
 });

foreach(var cweEntry in xRoot.elements().remove(0))
{
 var entryData = cweEntry.elements();
 treeView.add_Node(entryData[0].value(),entryData[1].value());
}

treeView.selectFirst();
//O2File:API_VistaDB.cs
//O2File:HtmlAgilityPack_ExtensionMethods.cs

//O2Ref:O2_Misc_Microsoft_MPL_Libs.dll

April 13, 2012 Posted by | CheckMarx | Leave a comment

VistaDB API and GUI (as used by Checkmarx SAST engine)

O2 now supports the VistaDB, which is a pure .NET embeded database used by applications like Checkmarx (who use it to support their SAST Web Application).

After using it a bit, I have to say that VistaDB seams like a really nice solution, since it is really a xcopy-based-relational-database with full SQL support.

To add support to VistaDB in O2, all that was needed was to grab the API_SqlServer.cs script and changed the Sql classes to their equivalent VistaDB classes (for example SqlConnection became VistaDBConnection).

Since VistaDB is not an open source project, you will need to have access to their eval download, or to an application that uses it (note how  used the VistaDB.NET20.dll from the Checkmarx install folder)

Util – VistaDB Browser.h2

//var topPanel = panel.clear().add_Panel();
var topPanel = O2Gui.open<Panel>("Sql Info",900,500);
topPanel.insert_Below(100).add_LogViewer();
var vistaDb = new API_VistaDB();
var show= false;
show = true;
TabControl tabControl = null;
Action loadDataFromCurrentConnection = 
    ()=>{
            tabControl.remove_Tab("Database details viewer");
            tabControl.remove_Tab("Table's Schema");
            tabControl.remove_Tab("Table's Data");
            tabControl.remove_Tab("Stored Procedures");
            vistaDb.add_Viewer_DataBases(tabControl.add_Tab("Database details viewer"));
            vistaDb.add_Viewer_Tables(tabControl.add_Tab("Table's Schema"));
            vistaDb.add_Viewer_TablesData(tabControl.add_Tab("Table's Data"));

        };



if(show)
{
    tabControl = topPanel.add_TabControl();    
    vistaDb.add_ConnectionStringTester(tabControl.add_Tab("Test/Set SqlConnection string"), loadDataFromCurrentConnection);
    vistaDb.add_GUI_SqlCommandExecute(tabControl.add_Tab("Execute SQL Commands"));                        
}



//O2Ref:System.Data.dll
//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

 
API_VistaDB.cs

using System;
using System.Data;
using System.Xml.Serialization;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
using O2.Interfaces.O2Core;
using O2.Kernel;
using O2.Kernel.ExtensionMethods;
using O2.DotNetWrappers.DotNet;
using O2.DotNetWrappers.ExtensionMethods;
using O2.External.SharpDevelop.ExtensionMethods;
using O2.Views.ASCX.ExtensionMethods;
using O2.XRules.Database.Utils;
using VistaDB.Provider;
//O2Ref:C:\Program Files\Checkmarx\Checkmarx Engine Server\VistaDB.NET20.dll</pre>
namespace O2.XRules.Database.APIs
{
    public class API_VistaDB_Test
    {
        public void launchTestGui()
        {
            "Util - VistaDB Browser.h2".local().executeH2Script();
        }
    }

    public class API_VistaDB
    {  
        public string ConnectionString { get;set; }
        public string LastError { get; set; }
       
        public API_VistaDB()
        {
            ConnectionString = @"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'";     //default to this one           
        }       
       
        public API_VistaDB(string connectionString)
        {
            ConnectionString = connectionString;
        }
    }
   
    public class Database
    {
        public API_VistaDB VistaDB { get; set; }
        public string Name { get; set; }       
        public List<Table> Tables { get; set; }
        public List<StoredProcedure> StoredProcedures { get; set; }
       
        public Database(string name)
        {
            Name = name.trim();
            Tables = new List<Table>();
            StoredProcedures = new List<StoredProcedure>();
        }
       
        public Database(API_VistaDB vistaDB, string name) : this (name)
        {
            VistaDB = vistaDB;
       
        }
    }
   
    public class Table
    {
        [XmlIgnore] public API_VistaDB VistaDB { get; set; }               
//        public string Catalog {get;set;}
//        public string Schema {get;set;}
        public string Name {get;set;}
//        public string Type {get;set;}
        public List<Table_Column> Columns {get;set;}
       
        public DataTable TableData { get; set; }
       
        public Table()
        {
            Columns = new List<Table_Column>();
        }
       
        public override string ToString()
        {
            /*return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;*/
            return Name;           
        }
    }
   
    public class Table_Column
    {
        //name, typeId, objectId,options , scriptValue
        public string Name { get; set; }       
        public string TypeId { get; set; }       
        public string ObjectId { get; set; }       
        public string Options { get; set; }       
        public string ScriptValue     { get; set; }       
       
        public override string ToString()
        {
            //return "{0} ({1})".format(Name, DataType);
            return "{0} ({1})".format(Name);
        }
    }
   
    public class StoredProcedure
    {
        public string Schema {get;set;}
        public string Name {get;set;}
        public string Value {get;set;}
       
        public StoredProcedure(string schema, string name, string value)
        {
            Schema = schema;
            Name = name;
            Value = value;
        }
       
        public StoredProcedure(string name, string value) : this("",name, value)
        {
           
        }
       
        public override string ToString()
        {
            return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;
        }
    }
   
    //add these queries should be done using Linq
    public static class API_VistaDB_Helps
    {
        public static Database database(this API_VistaDB vistaDB, string name)
        {
            return new Database(vistaDB, name);
        }
    }
    public static class API_VistaDB_getData
    {
        public static List<string> database_Names(this API_VistaDB vistaDB)
        {
            var sqlQuery = "select * from [database schema] where typeid = 1";
            return (from DataRow row in vistaDB.executeReader(sqlQuery).Rows
                    select row["name"].str()).toList();
        }
       
        public static List<string> column_Names(this Table table)
        {
            return (from column in table.columns()
                       select column.Name).toList();
        }
       
        public static List<Table> tables(this API_VistaDB vistaDb)
        {           
            return vistaDb.database("").tables();
        }
       
        public static List<Table> tables(this Database database)
        {           
            if (database.Tables.size() ==0)
                database.map_Tables();
            return database.Tables;
        }
       
        public static Table table(this API_VistaDB vistaDb, string name)
        {
            return vistaDb.database("").table(name);
        }
       
        public static Table table(this Database database, string name)
        {           
            return (from table in database.tables()
                    where table.Name.trim() == name
                    select table).first();
        }
       
       
        public static List<Table_Column> columns(this Table table)
        {
            return table.Columns;
        }
    }
   
    public static class API_VistaDB_PopulateData
    {
        public static Database map_StoredProcedures(this Database database)
        {       
            var sqlQuery = "select Specific_Schema, Specific_Name, Routine_Definition  from {0}.Information_Schema.Routines".format(database.Name);
            var storedProceduresData = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in storedProceduresData.Rows)
                database.StoredProcedures.Add(new StoredProcedure(row.ItemArray[0].str(),row.ItemArray[1].str(),row.ItemArray[2].str()));
            return database;
        }   
       
        public static Database map_Tables(this API_VistaDB vistaDB)
        {
            return vistaDB.database("").map_Tables();
        }
       
        public static Database map_Tables(this Database database)
        {       
            var sqlQuery = "select * from [database schema] where typeid = 1".format();
            var tables = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in tables.Rows)
                database.Tables.Add(new Table(){
                                                    VistaDB = database.VistaDB,
//                                                    Catalog = row.ItemArray[0].str(),
//                                                    Schema = row.ItemArray[1].str(),
                                                    Name = row["name"].str().trim()
//                                                    Type = row.ItemArray[3].str()
                                                    });
            return database;
        }   
       
        public static Database map_Table_Columns(this Database database)
        {       
            foreach(var table in database.tables())
            {           
                //var sqlQuery = "select Column_Name, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length from {0}.Information_Schema.Columns where table_Schema='{1}' and table_name='{2}'"
                //                    .format(table.Catalog, table.Schema,table.Name);
                               
                var objectId = database.VistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}' ".format(table.Name));
                var sqlQuery = "select name, typeId, objectId,options , scriptValue from [database schema] where foreignReference = '{0}' ".format(objectId);
                
                var columns = database.VistaDB.executeReader(sqlQuery);           
               
                foreach(DataRow row in columns.Rows)
                    table.Columns.Add(new Table_Column(){
                                                        Name =  row.ItemArray[0].str().trim(),
                                                        TypeId = row.ItemArray[1].str().trim(),
                                                        ObjectId = row.ItemArray[2].str().trim(),
                                                        Options = row.ItemArray[3].str().trim(),
                                                        ScriptValue = row.ItemArray[4].str().trim()
                                                        });
            }
            return database;
        }
       
        public static API_VistaDB map_Table_Data(this API_VistaDB vistaDB, Table table)
        {
//            var sqlQuery = "select * from [{0}].[{1}].[{2}]".format(table.Catalog,table.Schema, table.Name);               
            var sqlQuery = "select * from {0}".format(table.Name);                           
            table.TableData = vistaDB.executeReader(sqlQuery);
            return vistaDB;
        }
       
        public static Database map_Table_Data(this Database database, Table table)
        {
            database.VistaDB.map_Table_Data(table);
            return database;
        }
        public static Database map_Table_Data(this Database database)
        {
            "Mapping table data".info();
            var timer = new O2Timer("Mapped tabled data").start();
            foreach(var table in database.tables())                   
                database.map_Table_Data(table);
            timer.stop();
            return database;               
        }
       
        public static DataTable dataTable(this Table table)
        {
            if (table.isNull())
                return null;
            table.VistaDB.map_Table_Data(table);
            return table.TableData;
        }       
       
        public static string xml(this Table table)
        {           
            var dataSet = new DataSet();
            dataSet.Tables.Add(table.dataTable());
            return dataSet.GetXml();
        }
    }
   
    public static class API_VistaDB_Queries
    {
        public static VistaDBConnection getOpenConnection(this API_VistaDB vistaDB)
        {                       
            "[API_VistaDB] Opening Connection".info();
            try
            {
                var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);           
                sqlConnection.Open();
                return sqlConnection;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }           
            return null;
        }
       
        public static VistaDBConnection closeConnection(this API_VistaDB vistaDB, VistaDBConnection sqlConnection)
        {                       
            "[API_VistaDB] Closing Connection".info();
            try
            {               
                sqlConnection.Close();
                return sqlConnection;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }           
            return null;
        }
       
        public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, VistaDBConnection sqlConnection, string command)
        {           
            "[API_VistaDB] Executing Non Query: {0}".info(command);
            try
            {
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            return vistaDB;
        }
       
        public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, string command)
        {       
            "[API_VistaDB] Executing Non Query: {0}".info(command);
            VistaDBConnection sqlConnection = null;
            try
            {
                sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);           
                sqlConnection.Open();
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                    sqlConnection.Close();
            }
            return vistaDB;
        }
       
        public static object executeScalar(this API_VistaDB vistaDB, string command)
        {   
            "[API_VistaDB] Executing Scalar: {0}".info(command);
            VistaDBConnection sqlConnection = null;
            try
            {
                sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
                sqlConnection.Open();
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                return sqlCommand.ExecuteScalar();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                sqlConnection.Close();
            }
            return null;
        }
       
        public static DataTable executeReader(this API_VistaDB vistaDB, string command)
        {
            var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
            sqlConnection.Open();
            try
            {
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                var reader =  sqlCommand.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);
                return dataTable;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                    sqlConnection.Close();
            }
            return null;
        }
    }
       
    public static class API_VistaDB_GUI_Controls
    {
        public static T add_ConnectionStringTester<T>(this API_VistaDB vistaDB , T control, Action afterConnect)
            where T : Control
        {
            control.clear();
            var connectionString = control.add_GroupBox("Connection String").add_TextArea();
            var connectionStringSamples = connectionString.parent().insert_Left<Panel>(200).add_GroupBox("Sample Connection Strings")
                                                          .add_TreeView()
                                                          .afterSelect<string>((text)=> connectionString.set_Text(text));
            var connectPanel = connectionString.insert_Below<Panel>(200);
            var button = connectPanel.insert_Above<Panel>(25).add_Button("Connect").fill(); 
            var response = connectPanel.add_GroupBox("Response").add_TextArea();                       
           
            button.onClick(()=>{
                                    try
                                    {
                                        var text = connectionString.get_Text();
                                        vistaDB.ConnectionString = text;
                                        response.set_Text("Connecting using: {0}".format(text));
                                        var sqlConnection = new VistaDBConnection(text);
                                        sqlConnection.Open();
                                        response.set_Text("Connected ok");
                                        afterConnect();
                                    }
                                    catch(Exception ex)
                                    {
                                        vistaDB.LastError = ex.Message;
                                        response.set_Text("Error: {0}".format(ex.Message));
                                    }                       
                                   
                                });
           
            //connectionString.set_Text(@"Data Source=.\SQLExpress;Trusted_Connection=True");
            var sampleConnectionStrings = new List<string>();
            //from <a href="http://www.connectionstrings.com/sql-server-2005">http://www.connectionstrings.com/sql-server-2005</a>
            sampleConnectionStrings.add(@"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'")
                                   .add(@"Data Source=.\SQLExpress;Trusted_Connection=True")
                                   .add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI")                                                  
                                   .add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
                                   .add(@"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;")
                                   .add(@"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;")
                                   .add(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;")
                                   .add(@"Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;");
                                  
            connectionStringSamples.add_Nodes(sampleConnectionStrings).selectFirst();             
           
            button.click();
            return control;
           
        }
               
       
        public static API_VistaDB add_Viewer_QueryResult<T>(this API_VistaDB vistaDB , T control, string sqlQuery)
            where T : Control
        {    
            control.clear();
            var dataTable = vistaDB.executeReader(sqlQuery);            
            var dataGridView = control.add_DataGridView();
            dataGridView.DataError+= (sender,e) => { // " dataGridView error: {0}".error(e.Context);
                                                   };
            dataGridView.invokeOnThread(()=> dataGridView.DataSource = dataTable );           
            return vistaDB;
        }
       
        public static API_VistaDB add_Viewer_DataBases<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            var sqlQuery = "select * from [database schema] where typeid = 1";
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_Tables_Raw<T>(this API_VistaDB vistaDB , T control, string databaseName)
            where T : Control
        {
            var objectId = vistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}'".format(databaseName));
           
            var sqlQuery = "select * from [database schema] where typeid = 3 and foreignReference ='{0}'".format(objectId);
           
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_StoredProcedures_Raw<T>(this API_VistaDB vistaDB , T control, string databaseName)
            where T : Control
        {
       
            var sqlQuery = "select * from {0}.Information_Schema.Routines".format(databaseName);
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_StoredProcedures<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            control.clear();
            Database currentDatabase = null;
            var value = control.add_TextArea();   
            var storedProcedure_Names = value.insert_Left<Panel>(200).add_TreeView().sort();
            var database_Names = storedProcedure_Names.insert_Above<Panel>(100).add_TreeView().sort();
           
            var filter = storedProcedure_Names.insert_Above(20)
                                              .add_TextBox("Filter:","")
                                                .onTextChange((text)=>{
                                                                            storedProcedure_Names.clear();
                                                                            var result = (from storedProcedure in currentDatabase.StoredProcedures
                                                                                          where storedProcedure.Name.regEx(text)
                                                                                          select storedProcedure);
                                                                            storedProcedure_Names.add_Nodes(result);
                                                                        });
           
            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    value.set_Text("");
                                    currentDatabase = new Database(vistaDB, database_Name);
                                    currentDatabase.map_StoredProcedures();                                   
                                    storedProcedure_Names.clear();                       
                                    storedProcedure_Names.add_Nodes(currentDatabase.StoredProcedures);
                                    storedProcedure_Names.selectFirst();
                                 });
           
            storedProcedure_Names.afterSelect<StoredProcedure>(
                (storedProcedure) => value.set_Text(storedProcedure.Value) );
           
            database_Names.add_Nodes(vistaDB.database_Names());
           
            database_Names.selectFirst();
            return vistaDB;
        }
       
       
        public static API_VistaDB add_Viewer_Tables<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {       
            control.clear();
            var value = control.add_TableList();   
            var tables_Names = value.insert_Left<Panel>(200).add_TreeView().sort();            
//            var database_Names = tables_Names.insert_Above<Panel>(100).add_TreeView().sort();
/*            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    tables_Names.backColor(Color.Salmon);
                                    O2Thread.mtaThread(
                                        ()=>{
                                                value.set_Text("");
                                                var database = new Database(vistaDB, database_Name);                                   
                                                database.map_Tables()
                                                        .map_Table_Columns();
                                                tables_Names.clear();                       
                                                tables_Names.add_Nodes(database.Tables);
                                                tables_Names.selectFirst();
                                                tables_Names.backColor(Color.White);
                                            });    
                                 });
*/           
            tables_Names.afterSelect<Table>(
                (table) => value.show(table.Columns) );
           
//            database_Names.add_Nodes(vistaDB.database_Names());
           
//            database_Names.selectFirst();

            var database = new Database(vistaDB, "");                                   
            database.map_Tables()
                    .map_Table_Columns();
            tables_Names.clear();                       
            tables_Names.add_Nodes(database.Tables);
            tables_Names.selectFirst();
            tables_Names.backColor(Color.White);

            return vistaDB;
        }
       
        public static API_VistaDB add_Viewer_TablesData<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {       
            control.clear();
            var dataGridView = control.add_DataGridView();
           
            dataGridView.DataError+= (sender,e) => {}; //" dataGridView error: {0}".error(e.Context);};
            var tables_Names = dataGridView.insert_Left<Panel>(200).add_TreeView().sort();            
            var database_Names = tables_Names.insert_Above<Panel>(100).add_TreeView().sort();
            var preloadAllData = false;
            tables_Names.insert_Below(20).add_CheckBox("Preload all data from database",0,0,(value)=>preloadAllData = value).autoSize();//.check();
            var rowData = dataGridView.insert_Below<Panel>(100).add_SourceCodeViewer();
            var rowDataField = rowData.insert_Left<Panel>(100).add_TreeView();
            var selectedField = "";
           
            rowDataField.afterSelect<DataGridViewCell>(
                (cell)=>{
                            selectedField = rowDataField.selected().get_Text();
                            var fieldContent = cell.Value.str().fixCRLF();
                            if (fieldContent.starts("<?xml"))
                            {   
                                "mapping xml".info();
                                fieldContent = fieldContent.xmlFormat();
                                rowData.set_Text(fieldContent,"a.xml");
                            }
                            else
                                rowData.set_Text(fieldContent);
                        });
           
            dataGridView.afterSelect(
                (row)=> {                                                                                   
                            rowDataField.clear();
                            //rowData.set_Text("");
                            foreach(DataGridViewCell cell in row.Cells)
                            {
                                var fieldName = dataGridView.Columns[cell.ColumnIndex].Name;
                                var node = rowDataField.add_Node(fieldName,cell);
                                if (fieldName == selectedField)
                                    node.selected();
                            }
                            if (rowDataField.selected().isNull())
                                rowDataField.selectFirst();                                                                                       
                        });
                       
            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    tables_Names.backColor(Color.Salmon);
                                    O2Thread.mtaThread(
                                        ()=>{
                                                var database = new Database(vistaDB, database_Name);                                   
                                                database.map_Tables();
                                                if (preloadAllData)                                                                                           
                                                    database.map_Table_Data();                                               
                                                tables_Names.clear();                       
                                                tables_Names.add_Nodes(database.Tables);
                                                tables_Names.selectFirst();
                                                tables_Names.backColor(Color.White);
                                               
                                                database_Names.splitContainer().panel1Collapsed(true);
                                            });
                                 });
           
            Action<Table> loadTableData =
                (table)=>{
                            tables_Names.backColor(Color.Salmon);
                            O2Thread.mtaThread(
                                        ()=>{
                                                rowDataField.clear();
                                                rowData.set_Text("");   
                                                dataGridView.remove_Columns();                           
                                                if (table.TableData.isNull())                           
                                                    vistaDB.map_Table_Data(table);                               
                                                dataGridView.invokeOnThread(()=>dataGridView.DataSource= table.TableData);       
                                                tables_Names.backColor(Color.White);
                                            });
                         };
            tables_Names.afterSelect<Table>(
                (table)=>{
                            loadTableData(table);
                         });
           
            database_Names.add_Nodes(vistaDB.database_Names());
           
            database_Names.selectFirst(); 
                       
           
            tables_Names.add_ContextMenu().add_MenuItem("reload data",
                ()=>{
                        var selectedNode = tables_Names.selected();
                        if (selectedNode.notNull())
                        {
                            var table = (Table)tables_Names.selected().get_Tag();
                            table.TableData = null;
                            loadTableData(table);
                        }
                    });
            return vistaDB;
        }
       
        public static API_VistaDB add_GUI_SqlCommandExecute<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            Action<string> executeNonQuery=null;
            Action<string> executeReader =null;
            var resultsPanel = control.add_GroupBox("Result") ; 
            var sqlCommandToExecute = resultsPanel.insert_Above("Sql Command to execute").add_TextArea();
            var sampleQueries = sqlCommandToExecute.insert_Left(300, "Sample Queries")
                                                   .add_TreeView()
                                                   .afterSelect<string>((text)=>sqlCommandToExecute.set_Text(text));
 
            sqlCommandToExecute.insert_Right(200)
                               .add_Button("Execute Non Query")
                               .fill()
                               .onClick(()=>{
                                                 "Executing Non Query".info();                                    
                                                 executeNonQuery(sqlCommandToExecute.get_Text());
                                             })
                              .insert_Above()
                              .add_Button("Execute Reader")
                              .fill()
                              .onClick(()=> {
                                                 "Executing Reader".info();
                                                 executeReader(sqlCommandToExecute.get_Text());
                                             });;
           
            executeReader = (sqlQuery)=>{
                                            vistaDB.add_Viewer_QueryResult(resultsPanel, sqlQuery);
                                            "done".info();
                                        };   
                                       
            executeNonQuery = (sqlText)=> {           
                                                var    log = resultsPanel.control<TextBox>();
                                                if (log.isNull())
                                                    log = resultsPanel.clear().add_TextArea();
                                                if (sqlText.contains("GO".line()))
                                                {                                       
                                                    var sqlTexts = sqlText.line().split("GO".line());
                                                    log.append_Line("[{0}]Found a GO, so breaking it into {1} queries".format(DateTime.Now,sqlTexts.size()));                                         
                                                    var sqlConnection = vistaDB.getOpenConnection();
                                                    foreach(var text in sqlTexts)                                                                                   
                                                    {               
                                                        vistaDB.executeNonQuery(sqlConnection, text);                                                                               
                                                       
                                                        if (vistaDB.LastError.valid())
                                                        {
                                                            log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
                                                            log.append_Line("ERROR: stoping execution since there was an error which executing the query: {0}".format(text).lineBeforeAndAfter());
                                                            break;
                                                        }           
                                                    }
                                                    vistaDB.closeConnection(sqlConnection);
                                                }
                                                else
                                                    {
                                                        log.append_Line("Executing as Non Query: {0}".format(sqlText));
                                                        vistaDB.LastError = "";
                                                        vistaDB.executeNonQuery(sqlText);
                                                        if (vistaDB.LastError.valid())
                                                            log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
                                                    }
                                                "done".info();
                                           };           
               
            sampleQueries.add_Nodes(new string[] {
                                                    "select * from master..sysDatabases",
                                                    "select * from master.Information_Schema.Tables",
                                                    "select * from master.Information_Schema.Routines"
                                                });
            sampleQueries.selectFirst();
            return vistaDB;
        }
    }       
}

 

April 13, 2012 Posted by | .NET, CheckMarx | 1 Comment

New version of O2, GitHub based, running in OSX and in VisualStudio

I just wrote a number of blog posts about the new version of O2:

March 24, 2012 Posted by | O2 Scripting, OSX, VisualStudio | , | Leave a comment