SQL Examples To Access Data Direct - Revision history

Marie.o: /* Get All Devices For A Specific Floorplan */

2013-12-28T18:00:00Z

Get All Devices For A Specific Floorplan

Marie.o: First list of floorplan devices. Maximum of ten floorplans are supported.

2013-12-28T17:59:37Z

First list of floorplan devices. Maximum of ten floorplans are supported.

@@ Line 7: / Line 7: @@
 * 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.
@@ Line 93: / Line 94: @@
      Room.Description = 'Sofa' AND Array.PK_Array = 5
   ORDER BY Room.Description , Array.Description

Marie.o: /* List Scenarios For Media */

2013-12-28T16:41:54Z

List Scenarios For Media

←Older revision		Revision as of 16:41, 28 December 2013
Line 79:		Line 79:

	SELECT		SELECT
	Array.Description, Room.Description, CommandGroup . *		Array.Description, Room.Description, CommandGroup.*
	FROM		FROM
	CommandGroup		CommandGroup

Marie.o: wip

2013-12-28T16:41:40Z

wip

←Older revision		Revision as of 16:41, 28 December 2013
Line 60:		Line 60:
	all scenario buttons are defined in the same places.		all scenario buttons are defined in the same places.

	=== List Scenarios For Climate, Lighting, Telecom and Security ==		=== List Scenarios For Climate, Lighting, Telecom and Security ===

	SELECT		SELECT
Line 76:		Line 76:
	ORDER BY Room.Description , Array.Description		ORDER BY Room.Description , Array.Description

	=== List Scenarios For Media ==		=== List Scenarios For Media ===

	SELECT		SELECT

Marie.o: wip

2013-12-28T16:41:11Z

wip

←Older revision		Revision as of 16:41, 28 December 2013
Line 57:		Line 57:

	== Get list of scenarios ==		== 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. ~~One thing where scenarios~~ are ~~different from all~~ the ~~rest is the fact that they provide a relationship without a primary key/foreign key constraint~~. ~~The room association is not done by using a~~ CommandGroup_Room ~~table~~, ~~but by filling the name of the~~ Room ~~in the Hint column for a~~ CommandGroup.		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

Marie.o: wip

2013-12-28T16:21:15Z

wip

←Older revision		Revision as of 16:21, 28 December 2013
Line 55:		Line 55:
	WHERE		WHERE
	NOT Room.HideFromOrbiter		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. One thing where scenarios are different from all the rest is the fact that they provide a relationship without a primary key/foreign key constraint. The room association is not done by using a CommandGroup_Room table, but by filling the name of the Room in the Hint column for a CommandGroup.

Marie.o at 16:05, 28 December 2013

2013-12-28T16:05:00Z

←Older revision		Revision as of 16:05, 28 December 2013
Line 42:		Line 42:

	== Get list of media playback devices ==		== Get list of media playback devices ==
	This is the strange one. Because you do not directly select a media playback device, but you select an location where to play and you select what to play. The rest if being taken care of by the media plugin.		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		SELECT

Marie.o at 16:04, 28 December 2013

2013-12-28T16:04:31Z

←Older revision		Revision as of 16:04, 28 December 2013
Line 40:		Line 40:
	WHERE		WHERE
	NOT HideFromOrbiter		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 an location where to play and you select what to play. The rest if 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

Marie.o: Created page with "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 obje..."

2013-12-28T16:00:11Z

Created page with "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 obje..."

New page

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

←Older revision		Revision as of 18:00, 28 December 2013
Line 101:		Line 101:
	The statement returns all the devices and the coordinates that the device sits on the 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,		SELECT PK_Device,Device.Description,Device_DeviceData.IK_DeviceData,
	# Not nice, but the only way to decipher the field, imho.		# 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,',',3),',',-2) as Floorplan1XY,
	Substring_Index(Substring_Index(IK_DeviceData,',',6),',',-2) as Floorplan2XY,		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,',',9),',',-2) as Floorplan3XY,
	Substring_Index(Substring_Index(IK_DeviceData,',',12),',',-2) as Floorplan4XY,		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,',',15),',',-2) as Floorplan5XY,
	Substring_Index(Substring_Index(IK_DeviceData,',',18),',',-2) as Floorplan6XY,		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,',',21),',',-2) as Floorplan7XY,
	Substring_Index(Substring_Index(IK_DeviceData,',',24),',',-2) as Floorplan8XY,		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,',',27),',',-2) as Floorplan9XY,
	Substring_Index(Substring_Index(IK_DeviceData,',',30),',',-2) as Floorplan10XY		Substring_Index(Substring_Index(IK_DeviceData,',',30),',',-2) as Floorplan10XY
	FROM Device_DeviceData		FROM Device_DeviceData
	JOIN Device ON FK_Device = PK_Device		JOIN Device ON FK_Device = PK_Device
			WHERE FK_DeviceData = 10
	WHERE FK_DeviceData = 10		# We want all devices for floorplan two.
	# We want all devices for floorplan two.		AND Substring_Index(Substring_Index(IK_DeviceData,',',6),',',-1) > 0
	AND Substring_Index(Substring_Index(IK_DeviceData,',',6),',',-1) > 0		# now we check that we have at least two floorplans
	# now we check that we have at least two floorplans		AND (SELECT COUNT(*) FROM Floorplan) >= 2
	AND (SELECT COUNT(*) FROM Floorplan) >= 2