What’s Your Function?

I can’t say that this code actually returns an accurate value but I was impressed that I could just copy it out of an app I wrote back in July (an app that served as the subject of this blog’s first post, incidentally) and slam it into one of the new ZC function constructs to see it “just work” by returning the string value as one would expect.

The idea is to take whatever zoho.currenttime() is reporting and express it as UTC but I suspect the ZC team has since added features native to the platform that obviate the need for it:


string calculations.CalculateUTCTime()
{
// see http://answers.google.com/answers/threadview?id=560486
grab_time=zoho.currenttime;
epoch_seconds=((grab_time.toLong() / 1000)).toLong();
days=(epoch_seconds / 86400);
days_int=(days.toString().getPrefix(".")).toLong();
day_fraction=(days - days_int);
secs_left_over=(day_fraction * 86400);
hrs=(secs_left_over / 3600);
hrs_int=(hrs.toString().getPrefix(".")).toLong();
hrs_fraction=(hrs - hrs_int);
hours=hrs_int;
secs_left_over=((hrs_fraction * 3600)).toLong();
mins=(secs_left_over / 60);
mins_int=(((mins)).toString().getPrefix(".")).toLong();
mins_fraction=(mins - mins_int);
minutes="";
if((((mins_int).toString()).length() < 2))
{
minutes="0" + (mins_int);
}
else
{
minutes=(mins_int).toString();
}
secs_left_over=((mins_fraction * 60)).toLong();
secs_int=secs_left_over.toLong();
seconds="";
if(((secs_int.toString()).length() < 2))
{
seconds="0" + secs_int;
}
else
{
seconds=secs_int.toString();
}
// see: http://www.jsifaq.com/SF/Tips/Tip.aspx?id=7323
a=(days_int + 2472632);
b=(4 * a + 3);
b=((b / 146097)).toLong();
c=(((0 - b) * 146097)).toLong();
c=((c / 4)).toLong();
c=(c + a);
d=(4 * c + 3);
d=((d / 1461)).toLong();
e=((0 - 1461) * d);
e=((e / 4)).toLong();
e=(e + c);
m=(5 * e + 2);
m=((m / 153)).toLong();
dd=(153 * m + 2);
dd=((dd / 5)).toLong();
day=(0 - dd + e + 1);
mm=(((0 - m) / 10)).toLong();
mm=(mm * 12);
month=(mm + m + 3);
year=((b * 100 + d - 4800 + m / 10)).toLong();
month_str="";
if((month == 1))
{
month_str="Jan";
}
else if((month == 2))
{
month_str="Feb";
}
else if((month == 3))
{
month_str="Mar";
}
else if((month == 4))
{
month_str="Apr";
}
else if((month == 5))
{
month_str="May";
}
else if((month == 6))
{
month_str="Jun";
}
else if((month == 7))
{
month_str="Jul";
}
else if((month == 8))
{
month_str="Aug";
}
else if((month == 9))
{
month_str="Sep";
}
else if((month == 10))
{
month_str="Oct";
}
else if((month == 11))
{
month_str="Nov";
}
else if((month == 12))
{
month_str="Dec";
}
ts=((day + "-" + month_str + "-" + year + " " + hours + ":" + minutes) + ":") + seconds;
return ts;
}


Zynd in the Clowns

Awhile back someone in the Zoho Creator forum asked how to best design a ZC application and I wrote back to the effect that since there isn’t much design involved, it’s best just to suffer through the documentation and play with the interface until you get something close to what you thought you wanted. I’ll now amend that sentiment slightly by stating that it can’t hurt at least to try and write Deluge code that does not induce nausea.

Version 0.01 of Zyndafeed used a kludgy series of shows and hides in conjunction with 50 statically defined form fields to support up to 50 photo uploads. This worked OK from the outside but the implementation is embarrassing and in any case prompting the user with the option to fill out up to 50 text fields on one page is probably ridiculous in the first place.

Version 0.02 takes a different approach (and reduces signficantly the size of our Deluge script) by providing a separate form devoted to image uploads. A one-listing-to-many-photos relationship is established by inserting a listing’s unique_id as a non-unique identifier for rows in the Photos table. When it comes time to generate the feed, any image in the Photos table with a listing id that matches a listing’s unique id gets appended as a a child node. With this change not only are we providing Ted and the BuggyRocket customers with more flexibility (remember that we need to support at least 25 uploads per listing) but we’re also adhering closer to the DRY principle.

Design considerations when hacking out a Zoho application? It can’t hurt. In any case we’ll see what Mark thinks of this new version when he gets a chance to try it out and reply. Some ideas for more features include a monolithic view of listing data instead of views spread over 5 separate screens and some useful filters on whatever views are in place.

Add a Record Hack

Update: This approach won’t work because of a new bug (I believe introduced in the Sept 1 production update) in the way ZC sendmail handles multiline text fields. See the comments to this post for more information. Thanks to Suganya, this is now fixed!

Goal:

Programmatically add a new record to a Zoho Creator application.

Ideas:

The example application asks the user to select a fruit. After the user submits the form, the selected fruit is saved. In addition, after a few moments a new record is created in the Activity Log that shows which user saved a fruit and at what time they saved it.

Blabber:

Lately there have been several requests in the Zoho Creator forum that ask for the ability to programmatically add a new record. Although the feature is not currently supported in Deluge (the ZC team reports that it’s on the way), by using sendmail() you can still get it done provided your application is in no hurry to see that new record written to the corresponding form.

The example application pretty much demonstrates all you need to know. If you look at the Deluge source you can see that the first form is simply calling sendmail() in its on success {} block to write a new record to the second form using that second form’s More Actions >> Import Data >> By Email functionality. It’s a pretty mundane demonstration and is designed to write a new record to a form within the same application but in July I wrote about this technique in more detail and demonstrated how you can use it to effectively send information from one or more applications to a separate application.

The major drawback with this technique (other than it being a shameful hack) is that of course there is a sometimes not-so-brief delay between the time your application sends its emailed record to import and that record actually showing up in the database. If you can deal with the drawback though, other possibilities open up — for instance how about an application or applications that present the form interfaces in several different languages but save all that form data to a single view (along with a field indicating which language was used, for filtering purposes)? That way you could support multilingual applications that all write to the same place for easier data processing, management, etc.

Related Links:

Talking to Zoho Creator with Email

ZC Weirdness (potential bugs in the system):

None! Unfortunately it sometimes takes awhile for the emailed record to actually show up, but that’s just the nature of the beast.

Talk With R.Sriram: Sriram’s Responses

Zoho Creator’s Lead Architect, R.Sriram, responded to LoZC’s battery of incisive and hard-hitting interview questions with all the detail one would expect from a seasoned software engineer. Before he got into specific answers however he pointed me at Hyther’s post from December 2005 that gives some great background on how Zoho Creator came to be:

It all started with the long tail buzz created by Chris Anderson. We were not aware of such theory last year when Chris published his original version in wired. Last march during a casual discussion with my CEO, he introduced me to longtail. That’s when it struck us - what we do here (at AdventNet) to solve some of the internal needs are really building situated applications…Do you know what our HR folks do? Hunt for some developer to make such form-based app for them. Why not HR themself could create such simple web apps?

So with that as a starting point, read on to hear what Sriram had to say about ZC’s technical challenges and arcitectural evolution…

And many thanks to Sriram for making the time to give an interview!

LoZC: How did you get involved with the Zoho Creator project and in simple terms how does the work you do connect to or integrate with LoZC’s last interview topic: the Deluge subsystem?

Sriram: Internal needs have been the foremost reason for the evolution of ZohoCreator. Most of the time our HR, Admin and Marketing guys had to depend on the developers and UI designers to develop simple day to day applications to collect data. It involved developing an Html with PHP/JSP code in it to add/retrieve data from database. Most of the applications are throw away applications.Thats when we came up with the idea of AppCreator (now ZohoCreator).

When we started it was just simple Form Builder. ZohoCreator is completely based on relational model and Deluge was a natural fit. Gradually when we integrated Deluge (which supports action scripts/workflows and formulas), ZohoCreator transformed from a simple Form Builder to a complete Online Database Application creator.

LoZC: What other sites out there do you consider to be Zoho Creator’s competing or kindred products? I understand that you are a developer but if you were to imagine a small business owner who needed to build an application cheaply and quickly, faced with all the “Web 2.0″ options out their that they could choose from, which technical capabilities of ZC do you think might compel him or her to choose ZC over an alternative?

Sriram: Well there are quite a lot of them. Its very difficult to single any single product or service. While each service has a different approach, we believe that the approach we have taken in ZohoCreator is different, ie., enabling even non programmers to create database applications on their own.

And coming to second part of your question, let me list some points which I think as Zoho Creator’s benefit over other competing services:

1. Ease of use: Zoho Creator has very user friendly interface and easy to understand jargons which makes it easy to create applications
2. Deluge Script: Deluge helps users to build powerful applications in ZohoCreator. With ZohoCreator you are not just building a Form or set of related Forms, but a complete Application with support for customized actions. Maybe that’s the reason why our users equate ZohoCreator to VisualBasic/ MsAccess over the web ;-).
3. Embedding: Zoho Creator allows embedding of Forms and Views in personal websites/blogs. This allows users to make their website dynamic.
4. Collaboration: Users can Collaborate with each other using ZohoCreator, irrespective of their geographical separation. The sharing concept in ZohoCreator is well received by the ZohoCreator community and they use it effectively to collaborate within their community.
5. ZohoCreator Evolution: Considering our users need, we make updates every few weeks. We keep adding new features and enhance existing features regularly. ZohoCreator is definitely evolving rapidly.
6. Integration with other Zoho services: Since ZohoCreator is one of the services in Zoho suite, you would be seeing more integration with other services. In future we will be providing a complete solution rather than a standalone service with seamless integration with other services.

LoZC: Several people at Zoho have pointed out numerous times that ZC now runs on a “grid”. Wikipedia describes a grid in part as a system of “parallel computing which relies on complete computers (with onboard CPU, storage, power supply, network interface, etc.) connected to a network…by a conventional network interface…” Without giving too much away or reliving too much past pain, what it was like migrating ZC from a non-grid to grid architecture last year? Can you let us know some details about ZC’s grid architecture such as the rough number of nodes, which operating system and hardware components they feature, and how the grid in general is monitored? Approximately how many concurrent users could Zoho support before migration to the grid and how many after?

Sriram: When we started, we hosted ZohoCreator as a Standalone service. As the number of users increased, the performance started to degrade. Thats when the Grid system went into production. So we planned to migrate ZohoCreator to the Grid Environment. Migrating ZohoCreator to the Grid Environment was a huge effort. Some part of the data model for the ZohoCreator had to be modified. Especially with regards to sharing. With a standalone service, there is only one database and all the users data resides in one database. Whereas in a database cluster the user information is distributed across the clusters. Hence the sharing information is distributed across different databases in different clusters.

At that time there were around 10 thousand users and 40 thousand applications. There were couple of applications which had millions of records. Migrating them were pretty time consuming. Typically the migration involved copying the application to the grid environment along with the data. That is migrating from old data model to new data model along with their relationships and sharing information intact.

Regarding the ZohoCreator’s grid architecture, we have a number of database clusters, file clusters and application servers. In each database cluster the master data is replicated by a slave, so that when the master fails the slave automatically takes over. The Grid monitors the health of each and every cluster and the application servers, and ensures their availability. The Grid takes care of balancing the load between the servers. There are standby components which replaces the failed components in case of failure. In addition we have the ManageEngine Application Manager which monitors the database clusters and application servers for failures. There has been a substantial increase in the number of concurrent users for ZohoCreator when running in Grid, than it was running as a standalone application. While we have not monitored the number of concurrent users, the number of users who login to ZohoCreator has increased substantially over the past few months.

LoZC: In February of this year Sridhar commented on the Zoho Blog that, “Creator is in a class of its own, as far as performance tuning is concerned, because it is a development environment with a lot of dynamic/evolving set of applications; while it has gotten faster than before, much work remains to be done.” (And I think his comment actually comes after migration to your grid architecture!) With that perspective in mind, how difficult is it to manage, maintain, and upgrade Zoho Creator compared to the other offerings in the Zoho family of products? What kinds of adjustments (caching, refactoring, etc.) have you made to increase its performance over time?

Sriram: The complexity is not related to the Grid Architecture. The Grid only specifies certain guidelines for the applications. The complexity is more to do with the nature of the application. There is more dynamism to ZohoCreator as everything is created on the fly. Not only the Forms/Views are dynamic, but the entire application syntax, the deluge script logic and the final database query that is sent to the database are also dynamic.

Deluge which itself is a language of its own, enables users to add logic incrementally to the application. So ZohoCreator along with Deluge can be visualized as a development environment. With the increase in user base the requirements too keep growing and we have to enhance ZohoCreator and the Deluge sub system progressively. Unlike other development environments, we do not have a release in terms of years, but in terms of days/weeks. We need to constantly add/modify our Data Model to support new features and as well ensure its quality and performance. With the growing number of users its becoming more challenging for the team to handle upgrades with minimal/zero down time.

The performance aspect of ZohoCreator is bit unique among all other Zoho services. ZohoCreator hits the database more than any other application. There are more write operations compared to other services and hence the database is heavily loaded. These database queries are very dynamic in nature. We have reconfigured some of the database configuration to speed up the database. We also made some modifications to our Caching system to make it more efficient.

The performance operation is an ongoing process. We are continuously monitoring the database parameters for tuning the database performance. The Caching system with regards to ZohoCreator is pretty tricky as the data changes very frequently in ZohoCreator. We are coming up with new algorithms and we hope to make much more improvements in future.

LoZC: In Suganya’s interview with LoZC she commented that your team is composed of “mostly Java programmers” and noted your use of JavaCC, which is released under the BSD license. Then there is also ZC’s use of JFreeChart (LGPL license) for its charting capabilities. Can you tell us a little about how devoted to open source, Java-based solutions the ZC team is vs. other options and perhaps what other languages, tools, or platforms are involved in the ZC architecture beyond Java? For instance did or would your team consider Dabble DB’s choice for charts, FusionCharts, which requires Flash?

Sriram: Thats a nice question. As Suganya mentioned Java has had a quite an impact on most of us in Zoho. Having said that we are open to other languages too. The main intention of using the Open source solutions and tools is to pass on the benefit to our customers. As such we try hard not to charge our users. So we keep our solutions mostly based on Open Source tools without compromising much on quality. We have a good Flash expertise in http://toondoo.com. You never know we might as well develop a good charting tool using Flash . We are not averse to good commercial tools. We might bundle them as part of our paid version if the cost involved is more.

LoZC: A development team using its own product internally (Paul Maritz’s notion of “eating your own dogfood”) can say a lot about a web application. I notice for instance that the support forum for ZC is not itself a ZC application but an instance of phpBB — so do you personally or does your development team actually use any ZC applications during the work day, perhaps to track small projects or bugs, or do you tend to prefer other database applications? Do you think a ZC application could be built to replace your phpBB forum?

Sriram: “Eating your own dog food”. Well thats the philosophy we follow religiously in Zoho. But again we have the freedom to choose between the tools. Some managers use ZohoProjects, while some use ZohoSheet to track the different projects and some even use ZohoWiki.

There are quite a lot of people who have created some very good applications for their personal use. Most of the marketing persons in AdventNet and Zoho use ZohoCreator for managing their databases. There are quite a lot of situated Applications we had created in the past two years. There are music, sports, books and cultural activities within AdventNet Recreation Club where the people involved predominantly use ZohoCreator. Our HR Department uses a Leave application for tracking Employees leave details. Not only that, they have developed lots of situated applications for New Employee Orientations, Trainings, Blood Camps , Cab bookings, collecting employee details for distributing gifts and planning tours.

We have different versions of Issue Manager which we have developed and used at different points of time for managing the product features and issues. And they are truly situated as the requirements kept changing and hence the application.

While its technically feasible to build a Forums/blogs, our current focus is to allow users to build database applications that are for business and personal use. A Forum/blog requires a very flexibile and cool UI with lots of customization. We believe there is more need out there for creating situated applications rapidly which are otherwise difficult with all the learnings involved. Our focus right now is to enable our users to build more such situated applications by adding new features required to build such applications.

As an alternative we will be providing flexible API’s for the users to customize the applications created in ZohoCreator.

LoZC: This question comes from Kelly, commenting on the LoZC blog. “…I am pretty new to Zoho Creator but was curious what plans you had for supporting SSL past authentication. Also, do you have any plans for supporting encrypted data in the DB, say for instance if I wanted to add a credit card number to a registration page.”

Sriram: Kelly: SSL support and Data Encryption are some of the pending items. We have no definite time frame for doing them, but we will be doing SSL soon.

LoZC: What steps does your team take to make sure users don’t lose their data in case of a catastrophic database failure or prolonged network outage somewhere in the world? Since ZC seems to be targeting small and medium sized businesses, what technical assurances can you offer the skeptical potential customer to ensure they won’t lose access to or simply completely lose their custom applications and critical data?

Sriram: This question stands valid not just for ZohoCreator, but Zoho as a whole. As a first step we are preparing to host and replicate the data in multiple data centers across the world, so that our customers are not affected by any catastrophic database failures or prolonged network outage. This way even if one of the data center is affected, the others would work fine.

For ZohoCreator, in the short term, we are working on providing users with the ability to backup their application along with their data, which can be imported into ZohoCreator at a later point of time.

LoZC: This question comes from Teresa, commenting at the Zoho Creator blog. “…it’s obvious Zoho creator relies heavily on sophisticated client-side technologies, like Ajax and possibly Flex, that have only been introduced in the past two years. Do you think it would have been possible to produce this type of application even three years ago? How much do the capabilities of the UI tools influence the design and architecture of Zoho creator? What kind of backend platform do you use to support the sophisticated UI; Blowfish, Shale?”

Sriram: Teresa: Zoho Creator definitely benefited from a lot of recent Ajax client-side developments. It was a bit of challenge both on the server as well as the client side. While the complexity on the server side is about breaking your application details and storing it in the database on the fly. The client side involves constructing those information back in an user friendly manner. Ajax has definitely helped us in making the client look cool as well reducing the server round trips.

We take a balanced approach so that we do not compromise much on our data model, design and architecture of ZohoCreator owing to the limitations of the UI tools. On the backend we use a Java based grid platform we have custom built to scale Zoho Creator.

LoZC: I’m ridiculously curious about the Creator REST API that several at Zoho have mentioned over the past year or so. If you’re involved with its development at all, when is it coming and what will it look like?

Sriram: We are working on the REST API. We had completed it for CSV/XML. We are working on JSON based REST API’s. We will provide you with an early access by this weekend. We have developed both REST API’s and XML-RPC/CSV-RPC. So stay tuned for the API release

Undocumented Deluge

There are a few methods that Deluge understands but that don’t seem to be documented or prominently featured in the Zoho Creator forum. I’m not 100% sure where they surfaced; I’ve either run across them as a result of examining an error message in the ZC application interface or caught a glimpse of a specific example from some helpful developer on the ZC team.

There might be good reason for not documenting them, using them can be a little daunting and often times there are more straightforward, higher-level ways to accomplish a similar result. For the data type converters for instance, I think most get called implicitly through assignment (e.g., maybe my_date = ‘12-Jan-2007′ implicitly performs a toDate() operation) but sometimes an explicit method call can come in handy. Here are the ones I’ve stumbled across so far:

trim()

EXAMPLE


my_string = " I have unwanted padding on both sides of me. ";
my_string.trim();


RESULT

Removes whitespace padding on the left and right side of the string, i.e., ” foobar ” becomes “foobar”.

NOTES

You likely won’t need this one too often because Deluge seems to do a good job of sensibly trimming strings at the right time without you having to worry about it. Just know it’s there if you need it.

toDate()

EXAMPLE


input.my_date_string = "17-Aug-2006";
set my_date = my_date_string.toDate();


RESULT

Takes a STRING and converts it to a Deluge variable of type TIMESTAMP.

NOTES

I’m not sure when you’d need to use this given Deluge’s other date-handling options.

toLong()

EXAMPLE


employees_billing = 8;
hours_billed_this_month = 1320;
emp_worked = (hours_billed_this_month / employees_billing).toLong();
set msg = "Average time billed per employee this month: " + emp_worked + " hrs.";


RESULT

Converts variable value from type DECIMAL to type BIGINT so just the whole number is displayed. toLong() can also just be used to convert a STRING that contains numerical characters into a value suitable for calculation.

NOTES

This one is helpful when your results are in decimal format but you need an approximate whole number representation for subsequent calculations or display.

toString()

EXAMPLE


whole_number = (((mins.toString()).getPrefix(".")).toLong());
left_over = (((mins.toString()).getSuffix(".")).toLong());


RESULT

Stores only the left hand side of a DECIMAL value in one variable and the right hand side in another.

NOTES

Helpful when you need to isolate the right hand side of a decimal value. toString() probably has other uses, too.

toTime()

EXAMPLE


mse = mse_str.toLong();
time = mse.toTime();
stamp = time.toString();


RESULT

Converts a string that originally held the number of milliseconds since Jan 1, 1970 (epoch) to Deluge’s TIMESTAMP data type, then converts that to a STRING suitable for printing.

NOTES

You can use this one if you need to transport an exact date from one location to another as a string and later reconstitute to be of type TIMESTAMP. In the above example, mse_str would store a value representing the milliseconds since epoch as a string, e.g. “1185292219240″, and then that would be converted into a Deluge BIGINT (number) before being converted to a TIMESTAMP, then a STRING, e.g., “24-Jul-2007 02:50:19″, suitable for display.

plaintext

I should also mention than the field type plaintext gets short shrift in the documentation but is actually pretty powerful. Well-planned use of plaintext fields lets your ZC applications offer the user helpful hints and instructions and they can be updated when certain events happen to give the user context. In the application editor these are referred to as the ability to Add Notes

If I’ve missed anything here, please let me know and I’ll update the post.