SQL Examples To Access Data Direct

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.
• Floorplans contain a visual representation of all of the 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

Get list of scenarios

This is the brutal one. It is, what people see on the Orbiters home screen. The buttons that define most of the activity for a given room. Not all scenario buttons are defined in the same places.

List Scenarios For Climate, Lighting, Telecom and Security

SELECT 
   Array.Description, Room.Description, CommandGroup.*
FROM
   CommandGroup
       JOIN
   Array ON CommandGroup.FK_Array = PK_Array
       JOIN
   CommandGroup_Room ON FK_CommandGroup = PK_CommandGroup
       JOIN
   Room ON CommandGroup_Room.FK_Room = PK_Room
WHERE
   Room.Description = 'Sofa' AND Array.PK_Array in (1 , 2, 3, 4)
ORDER BY Room.Description , Array.Description

List Scenarios For Media

SELECT 
   Array.Description, Room.Description, CommandGroup.*
FROM
   CommandGroup
       JOIN
   Array ON CommandGroup.FK_Array = PK_Array
       JOIN
   CommandGroup_EntertainArea ON FK_CommandGroup = PK_CommandGroup
       JOIN
   EntertainArea ON CommandGroup_EntertainArea.FK_EntertainArea = PK_EntertainArea
       JOIN
   Room ON EntertainArea.FK_Room = PK_Room
WHERE
   Room.Description = 'Sofa' AND Array.PK_Array = 5
ORDER BY Room.Description , Array.Description

Get Floorplan Information

The floorplan information is stored in the database, and in the file system. Details are written in Where_Are_Floorplan_Objects_Stored.

Get All Devices For A Specific Floorplan

The statement returns all the devices and the coordinates that the device sits on the floorplan.

SELECT PK_Device,Device.Description,Device_DeviceData.IK_DeviceData,
# Not nice, but the only way to decipher the field, imho.
Substring_Index(Substring_Index(IK_DeviceData,',',3),',',-2) as Floorplan1XY,
Substring_Index(Substring_Index(IK_DeviceData,',',6),',',-2) as Floorplan2XY,
Substring_Index(Substring_Index(IK_DeviceData,',',9),',',-2) as Floorplan3XY,
Substring_Index(Substring_Index(IK_DeviceData,',',12),',',-2) as Floorplan4XY,
Substring_Index(Substring_Index(IK_DeviceData,',',15),',',-2) as Floorplan5XY,
Substring_Index(Substring_Index(IK_DeviceData,',',18),',',-2) as Floorplan6XY,
Substring_Index(Substring_Index(IK_DeviceData,',',21),',',-2) as Floorplan7XY,
Substring_Index(Substring_Index(IK_DeviceData,',',24),',',-2) as Floorplan8XY,
Substring_Index(Substring_Index(IK_DeviceData,',',27),',',-2) as Floorplan9XY,
Substring_Index(Substring_Index(IK_DeviceData,',',30),',',-2) as Floorplan10XY
 FROM Device_DeviceData 
JOIN Device ON FK_Device = PK_Device 
WHERE FK_DeviceData = 10 
# We want all devices for floorplan two.
AND Substring_Index(Substring_Index(IK_DeviceData,',',6),',',-1) > 0
# now we check that we have at least two floorplans
AND (SELECT COUNT(*) FROM Floorplan) >= 2