[Building Sakai] Help on how to query Sakai database

[Virgílio Santos]

Thank you guys. I had the feeling it would not be cool to query the
database directly and I appreciate the help! I will give some thinking
about ways to do that in a clean way.

But I fell down in another problem which is what the best way to use
the Sakai User on my hbm mapping files and my project as a whole. For
now, I'm just putting an id that will reference the Sakai user but I
don't if it's the best way. Is there an official way to use Sakai
entities as foreign keys on my tool database?

Thanks!

On Wed, Jul 9, 2014 at 6:26 PM, Steve Swinsburg
<steve.swinsburg at gmail.com> wrote:
> Reiterating what Mark said, you should not manipulate the db directly. Reads
> are more acceptable though.
>
> SELECT site_id
> FROM sakai_site_tool
> WHERE registration = 'sakai.dropbox'
>
> Will give you the site_ids for all sites that have the dropbox tool.
>
> cheers,
> Steve
>
>
> On Thu, Jul 10, 2014 at 3:01 AM, <markjnorton at earthlink.net> wrote:
>>
>> Generally, it is not recommended to make direct queries against a Sakai
>> database.  The data contained in these records are maintained and managed by
>> the Site Service.  A Java API allows you to access Site information,
>> including some rather powerful search capabilities.  While the database
>> schema has been stable for quite some time, there is no guarantee that it
>> won't change in a future release.  The API protects you against those sorts
>> of changes, even updating table definitions as needed.  I strongly suggest
>> you have a look at the Site Service in the Sakai kernel and see if the
>> objects/methods meet your needs.
>>
>> - Mark Norton
>>
>>
>> -----Original Message-----
>> >From: Virgílio Santos <virgilio.santos at gmail.com>
>> >Sent: Jul 9, 2014 11:04 AM
>> >To: sakai-dev at collab.sakaiproject.org
>> >Subject: [Building Sakai] Help on how to query Sakai database
>> >
>> >Hello,
>> >
>> >I have a query do execute on Sakai database that I could not figured
>> >out how to do with the available services. Basically, I want to query
>> >all Sites that have some specific tool in at least one of its pages.
>> >
>> >For know, I'm trying something like this (It's not the query I will
>> >really use, just an example):
>> >
>> >String sql = "SELECT * from SAKAI_SITE";
>> >List<Site> siteList = null;
>> >
>> >SQLQuery query = this.getSession().createSQLQuery(sql);
>> >query.addEntity(Site.class);
>> >query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
>> >List results = query.list();
>> >
>> >for(Object object : results) {
>> >    Site s = (Site) object;
>> >    // do stuff
>> >}
>> >
>> >And getting this error:
>> >org.sakaiproject.portal.api.PortalHandlerException:
>> >org.sakaiproject.tool.api.ToolException:
>> >org.springframework.web.util.NestedServletException: Request
>> >processing failed; nested exception is org.hibernate.MappingException:
>> >Unknown entity: org.sakaiproject.site.api.Site
>> >
>> >Any tips on making complex data queries on Sakai database will be very
>> >useful as well
>> >
>> >Thanks in advance.
>> >
>> >--
>> >Deserve's Got Nothing To Do With It
>> >_______________________________________________
>> >sakai-dev mailing list
>> >sakai-dev at collab.sakaiproject.org
>> >http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>> >
>> >TO UNSUBSCRIBE: send email to
>> > sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
>> > "unsubscribe"
>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to
>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
>> "unsubscribe"
>
>



-- 
Deserve's Got Nothing To Do With It