Genii Weblog

OpenSesame - Edit view in spreadsheet (50,000 documents in 6 seconds)

Tue 20 Mar 2007, 09:03 AM



by Ben Langhinrichs
One of the tentative products that I have been developing under the umbrella name of OpenSesame is the ability to edit a view in a spreadsheet.  Actually, you are not editing the view, but rather the documents in the view.  It is a bit like "in view editing" on steroids.  You choose the action "Edit view in spreadsheet" and go from this Notes view:

Notes view of data

to this spreadsheet (I am using OpenOffice.org Cal for this screenshot, but it works equally well in the new Notes 8 beta Spreadsheets productivity app):

Spreadsheet view of data

Then, you make whatever changes you like, using whatever macros or tools or tricks you like to use in a spreadsheet, and save.  Back in the Notes view, you pick "Update view from spreadsheet", and voila! the documents are updated.

So, as cool as all that is, it doesn't quite explain the title of this post.  You see, the sample I started with had 377 documents, but when I went to time how long that took, it was too fast to time with the rough notes timer.  So I added a bunch more documents.  A whole frigging bunch.  I stopped when I got to 50,000 documents.  While it is certainly possible that people would want to edit a view with more than 50,000 documents, this seemed like a good first test at scalability, given that I had not tested over 377 before this.

You guessed it.  I gave away the punchline in the title.  Six seconds to generate a spreadsheet with 50,000 rows representing the view.  It took nine seconds to open in OpenOffice.org Calc.  So, in 15 seconds, the user would be in editing even a really, really big view.  I'll have to switch over to my beta machine and test with Notes 8, but that has mostly been faster that OpenOffice.org.  Just to give a bit of perspective, the trial.ods is 208,862 bytes, but the expanded XML file is 39,301,284 (you could say it zips well).

So, how is that for scalability?

Copyright © 2007 Genii Software Ltd.

What has been said:


570.1. Philip Storry (20/03/2007 07:52)

Ben, you're insane.

Insane in a good way, of course. But still insane.

Now I need to think of what I could use this for...

Actually, I already know that. I always wanted to write a Notes C API app that would use a simple query language to make bulk changes on documents. During my meagre spots of developing applications in my career, there's always been a point where someone's handed me an app and said "we've changed our minds about this field and this field - they need to contain this and this".

It's not that the agent is hard to write, nor that it takes long to test. It's that I had to do it too many times - and I dreamt up my C API app as a solution - albeit one I knew I would never have the time to write.

But now, I'd just throw together a view and load it into a spreadsheet, then do a search and replace and update the two.

I'm a little sad that I will now never write that C API app. But the fact that its main purpose is now so easily achievable elsewhere makes me happier than I am saddened by that. :-)

Brilliant work. I hope it gets all the success it deserves!


570.2. Giuseppe Grasso (20/03/2007 08.28)

been there, done that.... or something similar:

notes tabular data editing using ole and excel.

The use of OOo or Productivity Editors is way more interesting.


570.3. Ben Langhinrichs (03/20/2007 08:33 AM)

The basic idea is not new, but this skips all the OLE and COM type calls, or exports and imports, and works directly with the ODF, thus offering a lot more scalability and control. With Notes 8 and the built in ability to know what spreadsheet is available, we can do all sorts of cool new integrations, such as this one.


570.4. Ian Randall (03/20/2007 03:07 PM)

Very interesting idea Ben, now if you could also provide a simple (end user oriented) method to embed the ODF graph object into a Notes document, this could become an ideal platform to create a universal Lotus Notes 8 Executive Dashboard capability.

But why stop there, it would be even better if the integration extended the other way too, by providing a real-time drill down into the underlying Notes source data from the ODF Graph object.


570.5. Richard Schwartz (03/20/2007 06:02 PM)

What happens if you add or delete lines? Or while you are editing the spreadsheet someone else adds or deletes docs, or just edits existing docs?


570.6. Ben Langhinrichs (03/20/2007 06:47 PM)

Richard - Good questions. The first answer is that there is a UNID stored as a key in the row. If the UNID isn't found in the db, the values are kicked out. If the doc was edited by someone else, you have your classic save conflict. Docs that were added to the db don't effect anything, while deleted docs will stay deleted and not get updated. One more thing is that if you don't have edit rights to the document, it won't get updated. Now, if a row in the spreadsheet is deleted, that is a different issue. That could mean that you want to delete the document, or it could mean that you just don't want to deal with those documents. While I have not coded that yet, I think it is likely to prompt the user to decide whether to delete those documents or not. It may not be obvious, but I have change recording turned on, so I can tell which rows have been updated. That way I could even have a new row create a new document, but that raises lots of issues, so I probably won't go there. On the other hand, since it shows which rows have been deleted, I can deal with those.

I am thinking about switching the UNID to a visible link, but my initial testing showed that a Notes URL would not work from the spreadsheet. Shame really, as that would make it even more view like.


570.7. Dan King (21/03/2007 06:48)

I think Ian's point is spot on - if that could be done it really would please the boss (and not just mine I suspect).

With OLE I can do most of what you're saying (although maybe slower) - what you mean by working directly with the ODF?

Is it true OLE isn't possible with the the productivity editors in 8.0?


570.8. Ben Langhinrichs (03/21/2007 07:35 AM)

Dan - I am working with IBM to make sure we can embed the results back into rich text. If that doesn't work, I have other more convoluted ways to turn a graph into an image and embed, so I will likely be able to do what Ian suggests at some point. As for your comments, you can do some of what I am doing with OLE (except that as you say OLE is not available in 8.0 for the productivity apps), but not other parts. For example, can you do this to any view without writing a different agent and have it know the fields and columns to use (and categories and such)? Can you act against a database with a hidden/locked down design? Can you send the spreadsheet to a customer without Notes and let them adjust the values and send it back and update it with a single command? Can you execute your OLE from the web and use the Google calculator to modify the results and then save back to your Notes view? All of those are quite feasible with this tool, if not fully exposed yet (it is still a prototype).


570.9. Stephan H. Wissel (03/23/2007 10:17 AM)

Combine that with webDAV and you automate the spreadsheet part. The sheet would just show up in a web folder and when opened your export magic starts, when closed the import would run.

Can your code be called from Java?

:-) stw


570.10. Ben Langhinrichs (03/23/2007 10:45 AM)

Stephan - The webDAV idea is interesting. As for Java, it can currently be called by using evaluate for the @DbCommand, but I think I will make it directly accessible from Java as well.