<html><head><meta name="qrichtext" content="1" /></head><body style="font-size:12pt;font-family:Arial">
<p>Hi, </p>
<p></p>
<p>I have just released the latest version of the Lava SQL engine for Chris's Magma OODB. </p>
<p>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.</p>
<p></p>
<p>You will need an image with:</p>
<p> 1. Squeak 3.9</p>
<p> 2. MagmaServerLoader-cmm.25 from Squeaksource</p>
<p> 3. Lava-brp.36 from Squeaksource</p>
<p> 4. LavaTesting-brp.34 from Squeaksource</p>
<p></p>
<p></p>
<p>5. Open TestRunner and run LavaTestCase and LavaOptimisedTestCase. 88 tests should pass.</p>
<p>This creates a Magma repository of poeple and their vehicles in ~/lava. It may take some time to create the LavaTestResource.</p>
<p></p>
<p>6. Open the Transcript and a Workspace.</p>
<p></p>
<p>7. Evaluate:</p>
<p></p>
<p> <span style="font-family:Courier">LavaTestResource current schema explore.</span></p>
<p></p>
<p>and explore how lava maps Magma collections in the repository to a relational schema.</p>
<p></p>
<p></p>
<p></p>
<p>8. To see the 'raw' Magma collections, evaluate:</p>
<p></p>
<p><span style="font-family:Courier"> LavaTestResource current vehicles explore.</span></p>
<p><span style="font-family:Courier"> LavaTestResource current people explore.</span></p>
<p></p>
<p></p>
<p>9. Use Magma's complex queries to find and print all the Pink* people who are not too old:</p>
<p></p>
<p><span style="font-family:Courier"> (LavaTestResource current people </span></p>
<p><span style="font-family:Courier"> where: [ :p | (p familyName match: 'Pink*') & (p age < 50) ]) </span></p>
<p><span style="font-family:Courier"> do: [ :p | Transcript show: p fullName; tab; show: p age; tab; show: p vehicle; cr ]</span></p>
<p></p>
<p></p>
<p>10. Evalute this to help see how Lava turns SQL into Smalltalk code:</p>
<p></p>
<p><span style="font-family:Courier"> LavaTestResource current session preferences </span></p>
<p><span style="font-family:Courier"> compileProcessorSilently: false;</span></p>
<p><span style="font-family:Courier"> removeProcessorAfterExecution: false;</span></p>
<p><span style="font-family:Courier"> tryToOptimiseQueries: true.</span></p>
<p> </p>
<p>11. Now try a the same query using Lava instead of Magma:</p>
<p></p>
<p><span style="font-family:Courier"> (LavaTestResource current session execute: '</span></p>
<p><span style="font-family:Courier;font-weight:600"> select p.fullName, p.age </span></p>
<p><span style="font-family:Courier;font-weight:600"> from people p </span></p>
<p><span style="font-family:Courier;font-weight:600"> where p.familyName like ''Pink%'' and p.age < 50</span></p>
<p><span style="font-family:Courier"> ')</span></p>
<p><span style="font-family:Courier"> do: [ :row | Transcript show: row fullName; tab; show: row age; tab; cr ]</span></p>
<p></p>
<p>12. Browse class <span style="font-family:Courier">LaProcessorLava</span>. This is the class Lava created to execute the SQL. The <span style="font-family:Courier">#executeSubquery1</span> method contains the logic.</p>
<p>Notice how Lava was able to optimise the <span style="font-family:Courier">(p.familyName like 'Pink%')</span> term using a Magma where: clause.</p>
<p></p>
<p>12. Try a join to get the people with vehicles:</p>
<p></p>
<p><span style="font-family:Courier"> (LavaTestResource current session execute: '</span></p>
<p><span style="font-family:Courier;font-weight:600"> select p.fullName, p.age, v.registration</span></p>
<p><span style="font-family:Courier;font-weight:600"> from people p, vehicles v </span></p>
<p><span style="font-family:Courier;font-weight:600"> where p.familyName like ''Pink%'' and p.age < 50 and p.vehicleKey = v.vehicleKey</span></p>
<p><span style="font-family:Courier"> ')</span></p>
<p><span style="font-family:Courier"> do: [ :row | Transcript show: row fullName; tab; show: row age; tab; show: row registration; cr ]</span></p>
<p></p>
<p>13. Browse <span style="font-family:Courier">LaProcessorLava >> #executeSubquery1</span> and notice </p>
<p> 1. how the join is implemented as a nested loop.</p>
<p> 2. that Lava cannot (yet) optimise a join - the <span style="font-family:Courier">(p.familyName like 'Pink%')</span> term is not longer optimised by Magma.</p>
<p></p>
<p></p>
<p></p>
<p>14. To explore the importance of begin able to optimise Lava queries to use Magma's underlying indices, use the LavaLargeTestResource.</p>
<p>This Lava test case creates a Magma database of electricity reading transactions. The number of transactions stored may be very large:</p>
<p></p>
<p>Evalute:</p>
<p></p>
<p> <span style="font-family:Courier">LavaLargeTestResource current createTransactions: 5000. "This may take some time."</span></p>
<p>and</p>
<p><span style="font-family:Courier"> LavaLargeTestResource current session preferences </span></p>
<p><span style="font-family:Courier"> compileProcessorSilently: false;</span></p>
<p><span style="font-family:Courier"> removeProcessorAfterExecution: false;</span></p>
<p><span style="font-family:Courier"> tryToOptimiseQueries: true.</span></p>
<p></p>
<p>15. Now evaluate this query:</p>
<p></p>
<p><span style="font-family:Courier"> (LavaLargeTestResource current session execute: '</span></p>
<p><span style="font-family:Courier"> </span><span style="font-family:Courier;font-weight:600">select * from transactions where deviceId = 222 and agentName = ''midrak'' and reading > 3000</span></p>
<p><span style="font-family:Courier"> ')</span></p>
<p><span style="font-family:Courier"> do: [ :row | Transcript show: row deviceId; tab; show: row agentName; tab; show: row reading; cr ] </span></p>
<p></p>
<p>This should execute in a few seconds.</p>
<p></p>
<p>16. Browse <span style="font-family:Courier">LaProcessorLava >> #executeSubquery1</span> and notice </p>
<p> 1. how Lava has optimised the <span style="font-family:Courier">(deviceId = 222)</span> and <span style="font-family:Courier">(agentName = ''midrak'') </span>terms</p>
<p> 2. The unindexed <span style="font-family:Courier">(reading > 3000)</span> term is not optimised.</p>
<p></p>
<p>17. Re-evaluate with <span style="font-family:Courier">tryToOptimiseQueries: false.</span>in 13) above and retry the query in 15) above. It will take a lot longer.</p>
<p></p>
<p></p>
<p>Comments welcomed</p>
<p></p>
<p></p>
<p>Brent</p>
<p></p>
<p></p>
<p></p>
</body></html>