View source for 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