Add Queued Items' Info to MySql DB

All things .jsx

Moderator: Paul Tuersley

Post Reply
cob906
Posts: 28
Joined: August 8th, 2006, 3:25 pm
Location: Lewisville, TX
Contact:

As an animator, I make a lot of animations every day. One of the problems I run into is coming back the next day and figuring out whether or not an animation’s rendered file was the most up-to-date. Sure, I could look at the logs made by AE but they aren’t easy on the eyes :shock:. So, in order to become more organized, I created a renderlog in excel, printed it out and wrote down the information about what I rendered. I’d record things like the name, start and stop time, output location, the date, composition, comments, etc.

However, since I’ve got better things to do than to write down all of these things, I figured it was time for a change. I’ve got an extensive background in PHP with MySql so I decided that I’d write a .jsx file for AE that would add the Queued item’s information in a database which could then be accessed by PHP.

Basically, this script collects the information of each item in the queue before it renders and creates a text file with the queries needed in order to add this information to the database. Then, the script calls a batch file which calls mysql.exe which in turn uses the text file’s queries to add to the DB. After doing so, the script renders out the items in the queue and then updates the items in the DB using the same method above (except instead of INSERT it UPDATES).

I did not write this script with other users in mind since I don’t plan on distributing it. Therefore, it will not work on a Mac and certain things need to be customized per user. That being said, you can do whatever you want to with the following code. Pick it apart, add it to your own code, make it better, use and abuse it, whatever.

Comments, questions, and suggestions are welcome and appreciated. I hope this script helps you guys like it has helped me.

You will need to create two files called “queryToToss.txt” and “tossQuery.bat” as well as creating a MySql DB and table. The queryToToss.txt file is the dummy file that holds the queries we are sending to our batch file. The tossQuery.bat file contains commands to send the queries to mysql.exe. As it stands, my “tossQuery.bat” file has the following info:

<b>
path C:\Program Files\xampp\mysql\bin
mysql --user <INSERT USERNAME HERE> --password=<INSERT PASSWORD HERE> --database=renderlog < E:\eclipse_workspaces\renderlog\queryToToss.txt
</b>


You will need to change “<b>E:\eclipse_workspaces\renderlog\queryToToss.txt</b>” to the location of your “queryToToss.txt” file and the database to whatever you named your database. Also, you’ll need to change the path of the location of your <b>mysql\bin</b> folder.

Here is the Sql Query to create the renderlog DB and the logs table:


CREATE DATABASE `renderlog` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
CREATE TABLE `logs` (
`id` varchar(20) collate latin1_general_ci NOT NULL,
`status` varchar(12) collate latin1_general_ci NOT NULL,
`type` set('part','full') collate latin1_general_ci NOT NULL,
`method` set('bg','ae','rf') collate latin1_general_ci NOT NULL,
`fileName` text collate latin1_general_ci NOT NULL,
`comp` varchar(75) collate latin1_general_ci NOT NULL,
`start_date` varchar(10) collate latin1_general_ci NOT NULL,
`start` varchar(11) collate latin1_general_ci NOT NULL,
`stop_date` varchar(10) collate latin1_general_ci NOT NULL,
`stop` varchar(11) collate latin1_general_ci NOT NULL,
`is_start` mediumint(9) NOT NULL,
`is_stop` mediumint(9) NOT NULL,
`comment` text collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='contains all of the log information';


Here is the file with loads of comments so you can follow along easier:

Code: Select all

//MADE BY COLLIN BROOKS
//collin.brooks@gmail.com

//SEND THE CONTENTS OF THE RENDER QUE TO THE DATABASE
clearOutput();
writeLn("Beginning toRenderLog script.");

//FIND THE NUMBER OF ITEMS IN THE QUEUE
var theProj = app.project;
var theQ = theProj.renderQueue;
var qItems = theQ.items;
var qSize = qItems.length;
//DUMMY SQL FILE THAT HOLDS THE QUERIES
var sqlFile = new File("E:\\eclipse_workspaces\\renderlog\\queryToToss.txt"); 
//BATCH FILE THAT RUNS OUR QUERIES
var batchFile = "E:\\eclipse_workspaces\\renderlog\\tossQuery.bat";


/********FUNCTIONS********/
function canContinue(items, queueSize)
{
//FUNCTION CHECKS TO SEE IF THE SCRIPT CAN CONTINUE WITH THE RENDERING BASED UPON STATUS OF QUEUED ITEMS
	for(x=1;x<=queueSize;x++)
	{
		currItem = items[x];
		if(currItem.render) //IF WILL RENDER WHEN RENDER BEGINS
		{
			//MAKE SURE CURRENT ITEM HAS AN OUTPUT
			if(currItem.status != RQItemStatus.NEEDS_OUTPUT)
			{
			return true;
			}else{
			return false;
			}
		}
	}	
}

function getStartTime(item)
{
//RETURNS THE ITEM'S STARTING RENDER TIME
	myTime = item.startTime;
	myTime.setHours(myTime.getHours()-1); //FOR SOME REASON, AE ADDS AN HOUR TO THE item.startTime. THIS FIXES IT.
	return formatTime(myTime);
}

function getEndTime(item)
{
//RETURNS THE END TIME FOR THE CURRENT ITEM
	myTime = item.startTime;
	addSeconds = item.elapsedSeconds; //ELAPSED SECONDS
	myTime.setSeconds(myTime.getSeconds()+addSeconds);//ADD THE ELAPSED SECONDS TO GET END TIME
	myTime.setHours(myTime.getHours()-1); //FOR SOME REASON, AE ADDS AN HOUR TO THE item.startTime. THIS FIXES IT.
	return formatTime(myTime);
}

function toTwoDigit(theNum)
{
//ADDS A ZERO TO NUMBERS BELOW TEN
	if(theNum < 10)
	{
		theNum = "0"+theNum;
	}
	return theNum;
}

function formatTime(timeGiven)
{
var timeArray = new Array(); //ARRAY FILLED WITH [0] DATE [1] TIME [2] UNIQUE ID
var currentTime = timeGiven;
	var month = toTwoDigit(currentTime.getMonth() + 1);
	var day = toTwoDigit(currentTime.getDate());
	var year = currentTime.getFullYear();
	var hour = currentTime.getHours();
		//CONVERT TO 12 HOUR TIME
		if(hour > 12)
		{
		hour = hour-12;
		ampm = "PM";
		}else{
		ampm = "AM";
		}
	hour = toTwoDigit(hour);
	var minutes = toTwoDigit(currentTime.getMinutes());
	var seconds = toTwoDigit(currentTime.getSeconds());
	timeArray[0] = month+"/"+day+"/"+year;
	timeArray[1] = hour+":"+minutes+":"+seconds+" "+ampm;
	timeArray[2] = ""+month+day+year+hour+minutes+seconds;
	return timeArray;
}

function updateSql(query)
{
//UPDATES THE SQL FILE
	sqlFile.writeln(query);
}

function runSql()
{
//EXECUTES THE BATCH FILE THAT EXECUTES THE SQL QUERY
		File(batchFile).execute();
}


/********SCRIPT SAFE TO RUN SECTION********/
if(theProj == null)
{
	alert("You do not have a project open. Cannot Continue.");
}else if(qSize == 0){
	alert("There are no items in the Render Queue. Cannot Continue.");
}else if(!canContinue(qItems, qSize)){
	//RUN CHECK TO SEE IF QUEUE STATUS IS OK
	alert("One of your queued items lacks a valid output path. Cannot continue.");
}else{

/********BEGIN SCRIPT********/
var uniqueIds = new Array();
var outputs;

writeLn("Going through Queue.");
	sqlFile.open("w","TEXT","Collin Brooks");		
	for(i=1;i<=qSize;i++)
	{
		currentItem = qItems[i]; //THE CURRENT RENDER QUEUE ITEM
		if(currentItem.status == RQItemStatus.QUEUED) //IF WILL RENDER WHEN RENDER BEGINS
		{
		newDate = new Date();
		getTheTime = formatTime(newDate);
		uniqueIds[i] = getTheTime[2]+i+""; //2 IS THE INDEX FOR THE UNIQUE ID IN THE RETURNED ARRAY
		today = getTheTime[0];
		theTime = getTheTime[1];
			currentName = currentItem.comp.name;
				//LIST ALL OUTPUTS OF THIS CURRENT ITEM
				outputs = currentItem.outputModules.length;
				fileSqlName = ""; //RESET FILENAME FOR SQL
				// * IS ADDED BEFORE ANY OUTPUT MODULE SO WE CAN EASILY SEPERATE THEM LATER
				//   IF THERE ARE MULTIPLE OUTPUT MODULES.
				for(o=1;o<=outputs;o++)
				{
					fileName = currentItem.outputModules[o].file.fsName; //THE LOCATION OF THE OUTPUTFILE
					fileSqlName += "\*"+fileName.replace(/\\/g, "\\\\"); //ADD DOUBLE SLASH SO THE SQL WILL INCLUDE THE SLASH
				}
				
			is_start = Math.round(currentItem.timeSpanStart*29.97); //CONVERTING TO FRAMES
			is_stop = is_start+Math.round(currentItem.timeSpanDuration*29.97)-1; //CONVERTING TO FRAMES
	
			//FIND OUT IF THIS IS A PARTIAL OR FULL RENDER
			if((Math.round(currentItem.comp.duration*29.97)-1) == is_stop && is_start == 0)
			{
			type = "full";
			}else{
			type = "part";
			}
			
			//ASK FOR A COMMENT
			writeLn("Enter a comment for queue item #"+i);
			userComment = prompt("Enter a comment for queue item #"+i); 
			
			//MAKE COMMENT SQL SAFE
			userComment = userComment.replace(/\'/g, "\\'"); // '
			userComment = userComment.replace(/\"/g, "\\\"");// "
			userComment = userComment.replace(/\\/g, "\\\\"); // \
			userComment = userComment.replace(/\//g, "\\/"); // /
			
			//BUILD SQL QUERY
			sql = "INSERT INTO `logs`";
			sql += "(fileName, comp, start_date, start, is_start, is_stop, comment, status, type, id)";
			sql += " VALUES ";
			sql += "(\""+fileSqlName+"\", '"+currentName+"', '"+today+"', '"+theTime+"', '"+is_start+"', '"+is_stop+"', '"+userComment+"', 'Queued', '"+type+"', '"+uniqueIds[i]+"');";
			
			writeLn("Adding query.");
			updateSql(sql); //WRITE QUERY TO THE SQL FILE
		}//END IF WILL RENDER
	}//END FOR LOOP	
	sqlFile.close(); //CLOSE THE SQL FILE
	writeLn("Running Query.");
	runSql(); //RUN THE BATCH FILE TO INSERT THE ROWS
	
	//RUN THE RENDER
	writeLn("Rendering.");
	app.project.renderQueue.render();
	writeLn("Rendering complete. Beginning sql update.");
	
	//NOW THAT RENDERING IS DONE OR ERRORED OUT, GO BACK AND UPDATE DB
	sqlFile.open("w","TEXT","Collin Brooks");
	for(i=1;i<=qSize;i++)
	{
		currentItem = qItems[i]; //CURRENT QUEUE ITEM
		myId = uniqueIds[i];
		if(myId != null)//SEE IF THE CURRENT ITEM HAS UNIQUE ID
		{
			//GET THE STATUS OF THE CURRENT ITEM
			myStatus = currentItem.status;
			if(myStatus == RQItemStatus.USER_STOPPED)
			{
			status = "User Stopped";
			endTime = getEndTime(currentItem);
			startTime = getStartTime(currentItem);
			updateSql("UPDATE `logs` SET status=\""+status+"\", stop = \""+endTime[1]+"\", stop_date = \""+endTime[0]+"\", start = \""+startTime[1]+"\", start_date = \""+startTime[0]+"\" WHERE id=\""+myId+"\";");
			}else if(myStatus == RQItemStatus.ERR_STOPPED){
			status = "Error";
			endTime = getEndTime(currentItem);
			startTime = getStartTime(currentItem);
			updateSql("UPDATE `logs` SET status=\""+status+"\", stop = \""+endTime[1]+"\", stop_date = \""+endTime[0]+"\", start = \""+startTime[1]+"\", start_date = \""+startTime[0]+"\" WHERE id=\""+myId+"\";");
			}else if(myStatus == RQItemStatus.DONE){
			status = "Done";
			endTime = getEndTime(currentItem);
			startTime = getStartTime(currentItem);
			updateSql("UPDATE `logs` SET status=\""+status+"\", stop = \""+endTime[1]+"\", stop_date = \""+endTime[0]+"\", start = \""+startTime[1]+"\", start_date = \""+startTime[0]+"\" WHERE id=\""+myId+"\";");
			}else if(myStatus == RQItemStatus.QUEUED){
			//AT THIS POINT, IF THE STATUS IS EQUAL TO QUEUED THEN THERE WAS AN ERROR.
			//DELETE THE CURRENT ITEM FROM THE LOG. IT WILL BE ADDED LATER WHEN WE
			//START THE RENDER AGAIN
			updateSql("DELETE FROM `logs` WHERE id=\""+myId+"\";");
			}//END STATUS CHECK
		}//END IF WILL RENDER
	}//END FOR LOOP	
	sqlFile.close(); //CLOSE THE SQL FILE
	writeLn("Sql update complete. Running Query.");
	runSql(); //RUN THE BATCH FILE TO UPDATE DB
	writeLn("Queue successfully added to renderlog.");
}//END OF IF CAN CONTINUE
I believe that's all you'll need in order to run this script. I realize the batch file reveals your username and password for your sql server. If there is a better way to connect without having to reveal the username and password, I'm open to suggestions. However, since all the information in my DB is not secret, I found no need to hide this. Once again, any comments, questions, or suggestions are welcome.

Collin
Darkmoon_UK
Posts: 62
Joined: September 5th, 2006, 3:45 am
Location: Chiswick, London, UK
Contact:

That's what we like to see :-) Interfacing with other programs and standards can be really powerful in the right setting. I don't have any need for this yet but as a fellow gfx pipeline manager myself, dealing with literally thousands of files, I'll certainly keep these demonstrated techniques in mind. Thanks for sharing, Collin.

Regards,

Chris
cob906
Posts: 28
Joined: August 8th, 2006, 3:25 pm
Location: Lewisville, TX
Contact:

This script has helped me keep track of all of my renders. The only problem is that I don't have enough time right now to create the website to interface with the database :lol:

I've got a couple more scripts that I'm writing right now. I'll post them when I'm finished. :D
User avatar
Disciple
Posts: 137
Joined: June 5th, 2004, 8:05 am
Location: Los Angeles, CA
Contact:

Hey Colin

Pretty amazing stuff!
Take a look at this thread. It seems you may be breaking ground for this to happen!

Alex
Post Reply