Latest Lava SQL for Magma OODB

[Hilaire Fernandes]

Hi, Look's like a great resources of references for indexes use.
Thanks

Hilaire


Brent Pinkney a écrit :
> 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
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Magma mailing list
> Magma at lists.squeakfoundation.org
> http://lists.squeakfoundation.org/mailman/listinfo/magma