| After publishing my previous article Programming
for pipeline here, I got few requests about further discussion on this topic
with some code example. In this article I have tried to fulfill them at my
level best. Hope you will enjoy it. Last time I have discussed this topic where
I took 3dsMax as example application. This time I choose Maya. Well, we know
that Maya has native scripting language MEL. Beside that we also can use
Python. Here we will use Maya API, .net library and MEL. To demonstrate how we
can mix different programming languages to solve any pipeline development
related issue here I have chosen one example task. It is database integration
with content creation tools. Database Integration is very much necessary for
flexible large scale production pipeline architecture. By default we not have
any default command to communicate with database directly from MEL. So we will
make a command plug-in using Maya API and .net library. You can do this through
python but it is a good example to show how we can mix native C++ with C++.net
when required. So I have made one command plugin for
MEL named DataBay. Based on flag it can be used to
get or set data to any type database like Access, Oracle, MSSQL, PostgreSQL, MySQL etc. This
command plug-in will carry our SQL queries from MEL to database through ADO.net
and vice-versa. This command also can be used instead of default forward only
file parsing commands. we can treat a simple comma
separated text file also as data source like regular database. It can be useful
and alternative way when we need to load huge data in one goal like
import/export mocap data for a given time range or
particular morph channel column etc. Proposed command prototype is like this: |
MEL
To
get values from database through DataBay -Getter:
We will use Getter flag if we
want to fetch some data from database
means when we are using SQL select
queries. Return type string array where every array element is one data row.
Data row string contains all data cells which are separated by the separator
string provided from mel using s flag. One data row can be fragmented in data cells, using stringToStringArray Mel command. See the example section
below. We are using separator because Unfortunately MEL have no
multidimensional string array what we can we use to return the whole recordset from database to MEL. One recordset is consists of multiple rows and columns. |
|
DataBay -Getter -c $ConnectionString -q "Command" -s"Separator"
|
|
Connection string is the string
which contains information about database location, security info etc. We will
use OLEDB to connect database, because of that according to connection string
we can communicate different types of database. Thus we can use same code to
work with different database provider. Here are the some OLEDB connection
strings for popular database providers.
|
Microsoft Access
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
Microsoft SQL Server
Provider=SQLNCLI; Server=myServerAddress; Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Oracle
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
PostGre
Provider=PostgreSQL OLE DB Provider;Data Source=myServerAddress;location=myDataBase;
User ID=myUsername;password=myPassword;timeout=1000;
MySQL
Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;
|
|
To do
non return operations through DataBay -Setter:
Setter flag
we will use when we want to modify/insert or delete data using Update, Insert
or Delete SQL queries. It returns true on success and false if operation fails.
This command is optimized for any SQL injection attack or hacking from user
input. It will deal with any user input contains SQL keyword. Here with SQL
command we are also passing parameter array, Array of their values and array of
parameter types |
|
DataBay -Setter -c $ConnectionString -q "Command"
-p{"@Param1","@Param2"}
-v{"val1","val2"} -t{0,1};
|
Data
Types:
0 = string
1 = double
2 = Integer
3 = date
Example
|
string $ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\\Pipeline.mdb;";
string $Separator = ",";
//getting all data rows from datasource
string $Rows[] = `DataBay -Getter -c $ConnectionString -q "SELECT * FROM Project" -s $Separator `;
//split a data row in to data cells
string $Cells[] = stringToStringArray ($Rows[0] , $Separator);
//print a specific data cell
print $Cells[0];
DataBay -Setter -c $ConnectionString -q "INSERT INTO Project (ProjectName,ProjectStarter) VALUES (@ProjectName,@ ProjectStarter)" -p {"@ProjectName","@ProjectStarter"} -v{"DemoProject","John Hinglay"} -t{0,0};
|
C++ implimenataion of DataBay
To shorten coding time I have used DeclareSimpleCommand macro from Maya API. DeclareSimpleCommand macro
consists of three parameters: class name, author or vendor name and the version
string. |
|
#include <maya/MSimple.h>
DeclareSimpleCommand( DataBay, "Susanta
Dutta", "8.5");
|
| Now inside the doIt function of DataBay class we are accepting all incoming arguments from
MEL. |
|
MStatus DataBay::doIt( const MArgList& args )
{
MStatus stat =
MS::kSuccess;
MString connectionString;
MString SqlQuery;
MString valueSeparator(",");
MStringArray paramArray;
MStringArray valueArray;
MIntArray typeArray;
..
..
}
|
| We can extract our necessary data either using MSyntax or through simple iteration. This is something like
this way |
|
for (uint i = 0;i<args.length();i++)
{
if ( MString( "-Getter" ) == args.asString( i, &stat ) )
{
for (i;i<args.length();i++)
{
if ( MString( "-c" ) == args.asString( i ) )
{
connectionString = args.asString(++i);
}
if ( MString( "-q" ) == args.asString( i ) )
{
SqlQuery = args.asString(++i);
}
if ( MString( "-s" ) == args.asString( i ) )
{
valueSeparator = args.asString(++i);
}
}
setResult(DataBayCore::Getter(connectionString,SqlQuery,valueSeparator));
return stat;
}
else if ( MString( "-Setter" ) == args.asString( i, &stat ) )
{
.
.
}
}
|
|
Now I have made one Wrapper class named DataBayCore which contains two important static functions what we are calling from DoIt method. We need to compile this particular class
implementation file (.cpp) with CLR support because
it is using .net library.
|
|
MStringArray DataBayCore::Getter(MString connectionString, MString query,MString valueSeparator)
|
|
bool DataBayCore::Setter(MString connectionString, MString query,
MStringArray paramArray,MStringArray valueArray, MIntArray typeArray)
|
|
These two functions are actually
communicating with database based on what flag we have used in MEL side. It is calling DataBayCore::Getter Method from DoIt
when MEL command is DataBay Getter and DataBayCore::Setter when we are using DataBay Setter. But before going to the implementation of
these two methods we need two utility functions which are responsible for MString to .net String conversion and vice-versa.
|
MString ToMayaString(String^ netString)
{
char * ch = (char *)(void *)Marshal::StringToHGlobalAnsi(netString->ToString());
MString var(ch);
Marshal::FreeHGlobal(IntPtr((void*)ch));
return var;
}
String^ ToNetString(MString mayaString)
{
return (gcnew String(mayaString.asWChar()));
}
|
| Inside Getter we have used DataTable class which is the memory representation of
Database table. OleDbDataAdapter helps to retrieve
from or saving data to database. We have used Fill method from this class
object to fill the DataTable object based on provided
SQL query. For OleDbDataAdapter we not have to do
opening or closing the connection exclusively. It will do that for us. After
filling data in DataTable object we iterate though
every data cell of every data row to build Maya APIs MStringArray.
You can see how we are using the separator string to concatenate all data cell
inside one row so later they from them we can build MEL string array using stringToStringArray MEL command. |
MStringArray DataBayCore::Getter(MString connectionString, MString query,MString valueSeparator)
{
MStringArray result;
try
{
String^ nConnectionString = ToNetString(connectionString);
String^ nQuery = ToNetString(query);
String^ nSeparator = ToNetString(valueSeparator);
MGlobal::displayInfo(ToMayaString(nConnectionString));
MGlobal::displayInfo(ToMayaString(nQuery));
MGlobal::displayInfo(ToMayaString(nSeparator));
DataTable^ varTable = gcnew DataTable();
OleDbConnection^ varCon = gcnew OleDbConnection(nConnectionString);
OleDbCommand^ varCommand = gcnew OleDbCommand(nQuery,varCon);
OleDbDataAdapter^ varAdap = gcnew OleDbDataAdapter(varCommand);
varAdap->Fill(varTable);
for(int i = 0; i<varTable->Rows->Count;i++)
{
String^ rowContent = "";
for(int c = 0; c<varTable->Columns->Count;c++)
{
rowContent+= ( varTable->Rows[i][c]->ToString() + nSeparator) ;
}
rowContent->TrimEnd(nSeparator->ToCharArray());
result.append(ToMayaString(rowContent));
}
}
catch(Exception^ ex)
{
MGlobal::displayError(ToMayaString(ex->Message));
}
return result;
}
|
| In case of setter we did
connection opening and closing exclusively. Here to execute the SQL query we
have used ExecuteNonQuery from OleDbCommand
class object. Here one thing need to be noticed is the way we fill the
parameters for OleDbCommand object. This will help us to prevent SQL injection
type hacking attack.
|
bool DataBayCore::Setter(MString connectionString, MString query, MStringArray paramArray,MStringArray valueArray, MIntArray typeArray)
{
String^ nConnectionString = ToNetString(connectionString);
String^ nQuery = ToNetString(query);
OleDbConnection^ varCon = gcnew OleDbConnection(nConnectionString);
OleDbCommand^ varCommand = gcnew OleDbCommand(nQuery,varCon);
for(unsigned int i = 0; i<paramArray.length();i++)
{
String^ nParamName = ToNetString(paramArray[i]);
String^ nVal = ToNetString(valueArray[i]);
Object^ valueToadd;
switch(typeArray[i])
{
case 1: //double
valueToadd = Double::Parse(nVal);
break;
case 2: //Integer
valueToadd = Int32::Parse(nVal);
break;
case 3: //Date
valueToadd = DateTime::Parse(nVal);
break;
default: //string
valueToadd = nVal;
break;
}
varCommand->Parameters->Add(nParamName,valueToadd);
}
varCon->Open();
try
{
varCommand->ExecuteNonQuery();
varCon->Close();
return true;
}
catch(Exception^ ex)
{
MGlobal::displayError(ToMayaString(ex->Message));
}
varCon->Close();
return false;
}
|
| It is just an example how we can
expose Database related functionality to MEL or any 3D software using there SDK
and thus can make a potential pipeline model which has tightly integrated
modules like task tracker, team switcher, modified or in-house source control,
integrated animation/model bank along with content development 3D tools. In
advance it can be based on distributed client-server architecture with transact
SQL support. Here we have used ADO.net but if we want to write only native or
cross platform code there are other couple of good OLEDB or ODBC libraries
available or database specific libraries like oracle client etc. Non-blocking
data flow support is one of the challenging aspect of pipeline development but
there are several areas as well where programming will help us to make a
comfortable production pipeline where artists friends can perform there
creative roles to make a successful project. Cheers! |
|
|
