DB Data Source asset used to update database

Squiz Matrix Support
1

Hi,


we are working on our review process for our website - a very large website with over 30,000 pages/files. The website is broken up into "topics" and each topic has a manager (who isnt trained in the use of matrix).



I have built a html page that shows all the files/pages in a topic in a tree like structure. We send that page to the managers and ask them to review all those files/pages for currency. To assist them we have a staff member's name next to each item (we keep that in a metadata field) so the manager can see who the contact person is for each file/page to help with the review.



With me so far?



Now, what I am trying to work out is how I can get the managers to be able to update the contact names themselves.



I was thinking that I could knock up a simple edit layout with only one field on it and they could click a link next to the persons name to open that simple edit page, fill out the new name and then save it. This would probably work OK but it takes around 14 seconds for the page to appear (I guess its getting locks and frames and all sorts of other guff that the simple editor does) and if they are going to be possibly updating dozens of names on the page, this could be quite annoying for them.



So, I was thinking that I could bastardize a DB Data Source asset where rather than putting a Select statement into the query window, I could put an Update statement in there instead. Then I could simply call the DB Data Source page using ajax and update that way. However, this doesnt seem to work.



Any comments about either method or any idea why an update statement in a DB data source asset doesnt work? Any other ideas on how I can get these contact metadata fields updated quickly.



Thanks,



Steve.

2

You certainly can put update statements into DB Data Source, although you need to be super-careful about security as you are bypassing pretty much everything Matrix gives you besides the the prepared statement the DB Data Source uses to minimise SQL injection.


But you can't call the DB Data Source directly as it doesn't execute the query to print frontend contents (it has no contents). You actually need to request a page that uses the result of the query. The easiest page to use would be an Asset Listing, pointed at the DB Data Source with the update query in it. If you use a dynamic root node, you could even pass in the ID of the DB Data Source and the values you want executed. The listing will ask the DB Data Source for the query result, which runs the query.



If you go with the asset listing, you could even have a few different queries and just use the dynamic root node to select the query you want to run, allowing a single listing to execute all your queries.

3

Nice one Greg. Will give it a try. Understand the issue with security and will keep in mind.

4

You might like to have a look at doing something with the javascriptAPI asset. You can set it up to work on metadata only and then construct your own form for the post. You pass the asset id you want to edit the metadata for, the asset id of the metadata field you want to change and the value you want to change it to and viola, there you have it! You can do some great stuff with asset listings as well.

5

So, if it helps to make it quicker…


You would create a javascriptAPI asset and point it to the root node that holds the assets you want to update. Set the asset type and set it to ignore permissions and setMetadata to yes.



Create an asset listing and point it at the same root node/s, set the same asset type/s, make sure you force permissions on what is listed by setting the "Asset Access to List" option to Write and check "include effective permissions" and set up other stuff the way you might want to.



In the page contents drop something in like the following;


    
    

<!--go to google because it serves it quicker -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<!--path to the javascriptAPI asset you set up for this -->
<script type="text/javascript" src="http://yourdomain.com/_web_services/yourfile.js"></script>
<!--we use this function to expand out the hidden editing form -->
<script type="text/javascript"> function toggleMe(a){ var e=document.getElementById(a); if(!e)return true; if(e.style.display=="none"){ e.style.display="block" } else { e.style.display="none" } return true; } </script>
%asset_listing%</pre><br />




And in the default format put in something like;




    %asset_name%
    
    

    
    
    %asset_metadata_fieldname%

    
    
    
    
    setApiKey(!!!!!!!!!!!!);
    (function ($) {

	$().ready(function(){
		$("#%asset_assetid%_begin").click(function (){
		
			setMetadata("%asset_assetid%", "########",$("#%asset_assetid%metadata_field_text_########_value").val(), function(){
								window.location.reload ()
			})				 
		})
	})
})(jQuery);
</script>
</div><br><hr></pre><br />




This is a quick way to POC what you want to do, there are many others who could give you nicer code than my hackery ;)



Cheers

Shane
6





Thanks Shane, I didnt know that the js api existed and it might just be the exact thing I need.

However I found that I couldnt get the callback function to fire, any clues? The setMetadata call works - the data gets updated perfectly - but the callback isnt called. I think I've been looking at it too long....

<script>

function setSuccess(obj) {
alert("here2");
}


setApiKey(84924425899);

$(document).ready( function () {
alert("here3");
setMetadata(90351,65347, 'Phil Wilk', setSuccess );
});
</script>