[Building Sakai] Help on how to query Sakai database

[Steve Swinsburg]

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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140710/c250e6b9/attachment.html