Latest Lava SQL for Magma OODB

[Brent Pinkney]

Hi, 

I have just released the latest version of the Lava SQL engine for Chris's Magma OODB. 
This version is distinguished by being able to optimise some queries to use Magma's indexes instead of materialising each element and checking it in the image.

You will need an image with:
	1. Squeak 3.9
	2. MagmaServerLoader-cmm.25 from Squeaksource
	3. Lava-brp.36 from Squeaksource
	4. LavaTesting-brp.34 from Squeaksource


5. Open TestRunner and run LavaTestCase and LavaOptimisedTestCase. 88 tests should pass.
This creates a Magma repository of poeple and their vehicles in ~/lava. It may take some time to create the LavaTestResource.

6. Open the Transcript and a Workspace.

7. Evaluate:

	LavaTestResource current schema explore.

and explore how lava maps Magma collections in the repository to a relational schema.



8. To see the 'raw' Magma collections, evaluate:

	LavaTestResource current vehicles explore.
	LavaTestResource current people explore.


9. Use Magma's complex queries to find and print all the Pink* people who are not too old:

	(LavaTestResource current people 
		where: [ :p | (p familyName match: 'Pink*') & (p age < 50) ]) 
			do: [ :p | Transcript show: p fullName; tab; show: p age; tab; show: p vehicle; cr ]


10. Evalute this to help see how Lava turns SQL into Smalltalk code:

	LavaTestResource current session preferences 
		compileProcessorSilently: false;
		removeProcessorAfterExecution: false;
		tryToOptimiseQueries: true.
 
11. Now try a the same query using Lava instead of Magma:

	(LavaTestResource current session execute: '
		select p.fullName, p.age 
		from people p 
		where p.familyName like ''Pink%'' and p.age < 50
			')
			do: [ :row | Transcript show: row fullName; tab; show: row age; tab; cr ]

12. Browse class LaProcessorLava. This is the class Lava created to execute the SQL. The #executeSubquery1 method contains the logic.
Notice how Lava was able to optimise the (p.familyName like 'Pink%') term using a Magma where: clause.

12. Try a join to get the people with vehicles:

	(LavaTestResource current session execute: '
		select p.fullName, p.age, v.registration
		from people p, vehicles v 
		where p.familyName like ''Pink%'' and p.age < 50 and p.vehicleKey = v.vehicleKey
			')
			do: [ :row | Transcript show: row fullName; tab; show: row age; tab; show: row registration; cr ]

13. Browse LaProcessorLava >> #executeSubquery1 and notice 
	1. how the join is implemented as a nested loop.
	2. that Lava cannot (yet) optimise a join - the  (p.familyName like 'Pink%') term is not longer optimised by Magma.



14. To explore the importance of begin able to optimise Lava queries to use Magma's underlying indices, use the LavaLargeTestResource.
This Lava test case creates a Magma database of electricity reading transactions. The number of transactions stored may be very large:

Evalute:

	LavaLargeTestResource current createTransactions: 5000.	"This may take some time."
and
	LavaLargeTestResource current session preferences 
		compileProcessorSilently: false;
		removeProcessorAfterExecution: false;
		tryToOptimiseQueries: true.

15. Now evaluate this query:

	(LavaLargeTestResource current session execute: '
		select * from transactions where deviceId = 222 and agentName = ''midrak'' and reading > 3000
			')
			do: [ :row | Transcript show: row deviceId; tab; show: row agentName; tab; show: row reading; cr ] 

This should execute in a few seconds.

16. Browse LaProcessorLava >> #executeSubquery1 and notice 
	1. how Lava has optimised the (deviceId = 222) and (agentName = ''midrak'') terms
	2. The unindexed (reading > 3000) term is not optimised.

17. Re-evaluate with tryToOptimiseQueries: false.in 13) above and retry the query in 15) above. It will take a lot longer.


Comments welcomed


Brent


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.squeakfoundation.org/pipermail/magma/attachments/20070331/dac66753/attachment.htm