Revision as of 17:05, 28 December 2013

This page exemplifies SQL statements to access data in LinuxMCE direct, instead of via the regular means of MessageSend, DataGrids etc.

Preface

LinuxMCE has a couple of objects

• Lights, drapes and other kind of ON/OFF/SETLEVEL type of equipment
• Rooms and Entertainment Areas (EA) representing the location where equipment is stored
• Media playback devices that need media control, like play/pause/stop and media selection
• Scenarios which group together device and action to these devices.

All devices decent from device templates, which are categorized by device categories. Lights et all, and media playback devices are those devices that always decent from device templates. Rooms and EA are not devices and all their information is stored in specific tables called Room and EntertainmentArea. Scenarios group devices and commands for those devices together, and are grouped together in CommandGroups.

Tables all follow a generic naming scheme which is described in detail (<-- to do: Link to the wiki page describing primary keys and stuff) .

Get all lighting devices and other ON/OFF/SETLEVEL type devices

Lights, wall-outlets and drapes all work the same way. You can turn the off (lights go off, wall-outlets don't provide power, the drapes don't provide any shading), you can turn them on, and some you can set to something inbetween on and off.

SELECT 
   PK_Device, Device.Description, PK_DeviceTemplate, PK_DeviceCategory
   ,DeviceTemplate.Description
FROM
   Device
       JOIN
   DeviceTemplate ON FK_DeviceTemplate = PK_DeviceTemplate
       JOIN
   DeviceCategory ON FK_DeviceCategory = PK_DeviceCategory
WHERE
   DeviceCategory.Description LIKE '%light%' AND
   DeviceCategory.Description NOT LIKE '%interface%' AND
   DeviceCategory.Description NOT LIKE '%plug-in%'

Get list of selectable rooms

Rooms can have different types. A room type is used to determine auto generated scenarios. For example, the Bedroom (Master) type always receives a House To Sleep scenario.

SELECT 
   PK_Room, Room.Description, PK_RoomType, RoomType.Description
FROM
   Room
       JOIN
   RoomType ON FK_RoomType = PK_RoomType
WHERE
   NOT HideFromOrbiter

Get list of media playback devices

This is the strange one. Because you do not directly select a media playback device, but you select a location where to play and you select what to play. The rest is being taken care of by the media plugin.

SELECT 
   PK_EntertainArea,
   EntertainArea.Description,
   PK_Room,
   Room.Description
FROM
   EntertainArea
       JOIN
   Room ON FK_Room = PK_Room
WHERE 
   NOT Room.HideFromOrbiter