[Building Sakai] FW: Roles system move

[Dunstall, Christopher]

Hi all,

We're making a change to our in-house authz group provider to use a series of tables we've added to the SAKAI db schema.

I originally patched it to use the existing Sakai DB service, using SqlService and SqlReader. However (see below) Steve Githens suggested that it's not advisable to use this method and that I should use the base DataSource and get a connection that way.

Considering we're using 2.4.x, I'd like to know if this is a shared attitude towards the 2.4.x SqlService and that we really should steer clear of it wherever possible?

Regards,

Chris Dunstall | Service Support - Applications
Technology Integration/OLE Virtual Team
Division of Information Technology | Charles Sturt University | Bathurst, NSW

Ph: 02 63384818 | Fax: 02 63384181


-----Original Message-----
From: Roma, David 
Sent: Monday, 7 February 2011 3:29 PM
To: Dunstall, Christopher
Cc: Walsh, Mark; Bristow, Paul
Subject: Roles system move

Hey Chris, 

I asked mark to do a quick code review of your change for the roles system. Any reason why you went for the SQLReader and SQLservice ?? bit of a worry given steves comment from ages ago..

Cheers,
D.

-----Original Message-----
From: Walsh, Mark 
Sent: Monday, 7 February 2011 3:20 PM
To: Roma, David
Subject: FW: FW: ContentHostingService suggestions.

Hi Dave,

I had a look at the code changes [1] as requested.

I noticed that we appear to be going from using javax.sql.DataSource to using SqlReader and SqlService.

As per the email below from Steve Githens, using SqlReader and SqlService might not be a good idea.
Perhaps continuing to use javax.sql.DataSource might be a better idea.

[1] Code to be reviewed
http://svn.csu.edu.au/svnweb/index.cgi/svn/diff/sakai/interact/trunk/providers/csuGroup/src/java/org/sakaiproject/provider/authzGroup/csuGroupProvider.java?rev1=14060;rev2=14061

Regards,
Mark Walsh.
-----Original Message-----
From: Steven Githens [mailto:swgithen at mtu.edu] 
Sent: Wednesday, 7 April 2010 11:49 AM
To: Walsh, Mark
Cc: Roma, David; swgithen at mtu.edu
Subject: Re: FW: ContentHostingService suggestions.

Hi Mark,

Yeah, the SqlService is old and crappy.  You should just fetch a JDBC 
connection from Sakai's pool.  You can inject the spring bean called 
javax.sql.DataSource ( or use 
ComponentManager.get("javax.sql.DataSource") if what you're working on 
isn't a Spring instantiated bean ).

This is a real DataSource that you can borrowConnection, commit, and 
then close ( you definately still have to close them yourself ).  Thats 
the best way these days to get and do manual SQL in Sakai.

Also, I've been getting over a cold, so I think I'm going to need to hit 
the hay here real soon ( I actually took the day off of work yesterday 
).  Apologies for most likely missing tonight, I'll plan on joining in 
tomorrow during the stand up call.

-Steve

Walsh, Mark wrote:
> Hi Steve,
>
> For my first attempt with ContentHosting service, I tried contentHostingService.getAllResources("/attachment/MSI_PDF/") method, to get back all MSI resources, then looped through all the results to find the pdfs published for a single outline.
>
> I suspect this implementation using ContentHosting might be considered too slow.
>
> Is SqlReader,SqlService the correct way to query the sakai schema for this information ??
> I seem to recall that SqlReader,SqlService might be old (possibly buggy ways) of communicating with the sakai database schema.
>
> Is there any particular sakai way I should be using, to run a query something like the following:
> select * from content_resource where in_collection  ='/attachment/MSI_PDF/' and resource_id like '%ITC702_200970_B_D%'
>
> Regards,
> Mark Walsh.
>
> -----Original Message-----
> From: Steven Githens [mailto:swgithen at mtu.edu]
> Sent: Wednesday, 31 March 2010 9:15 AM
> To: Walsh, Mark
> Cc: Roma, David
> Subject: Re: ContentHostingService suggestions.
>
> Walsh, Mark wrote:
>   
>> Hi Steve,
>>
>> One of the new functions to be added to MSI, is ability to display all
>> previously saved pdf's outlines.
>>
>> For example, in devel environment, if I was interested in outline
>> ITC702_200970_B_D, it would mean finding the following pdf's:
>>
>> /attachment/MSI_PDF/ITC702_200970_B_D-21_December_2009-Version_1.pdf
>>
>> /attachment/MSI_PDF/ITC702_200970_B_D-21_December_2009-Version_2.pdf
>>
>> /attachment/MSI_PDF/ITC702_200970_B_D-21_December_2009-Version_3.pdf
>>
>> /attachment/MSI_PDF/ITC702_200970_B_D-22_December_2009-Version_4.pdf
>>
>> I could write some SQL to do the above, as shown below:
>>
>> select * from content_resource where in_collection
>> ='/attachment/MSI_PDF/' and resource_id like '%ITC702_200970_B_D%'
>>
>>     
>
> Hi Mark,
>
> After a quick browse through the API, it doesn't look like any of the
> find methods do what we need here. I'm guessing it would be slow to do a
> contentHostingService.getCollection("/attachment/MSI_PDF"...) and then
> iterate through it's resources to match the names.
>
> The best way might actually be to do a database query. Assuming it's
> read-only it's not the worst thing in the world.
>
> I'll let you know if I think of something else.
>
> -s
>
>
>
>   
>> I suspect I should be using the ContentHostingService, and not direct
>> sql to find this information.
>>
>> I'm just not to sure what search methods in the ContentHostingService
>> I should be using ??
>>
>> Any suggestions, examples, tips etc... are welcome.
>>
>> Regards,
>>
>> Mark Walsh.
>>
>>     
>
>