After the summer, I’ve picked up work on the interface to Oscar Vermeulen’s PiDP11 console – what was left to do was the virtual settings on the address rotary switch and the actual values on the address and data lights. It mostly works now, and I’ve come to the point that I need to take a step back from it, let it rest for a while and come back to it in a couple of days, maybe a week or so – to avoid getting blind to the things that aren’t right yet. Meantime I’ve sent a preview to a beta tester, and I’m anxiously awaiting his comments…
So now it’s time to just play with the machine! and the first thing on my mind to dive into was Ingres. One of the oldest real relational database systems, and with a long and rich history. I knew it was included in 2.11BSD, but when I tried it out years ago when I first got 2.11BSD to run, it didn’t work… all the commands core dumped. So it needed a bit more work – and after quite a bit of tinkering and experimenting, it turned out to be quite easy – as usual if you know the answer. At first, I tried rebuilding the Ingres sources as root, but that doesn’t work quite right – it can be done, but it’s a lot easier to run the make as the ingres user.
So, what needs to be done is this:
- Reconfigure the kernel to include the Ingres lock driver – in other words, the INGRES option (on the last line of the config file) should be set to YES. And obviously then recompile the kernel, install it and reboot the machine – and all of that using root, as usual.
- Login to the ingres user, change into the source directory, and run make – if you thought the kernel took a bit to recompile, well, this takes a bit longer.
- Change into the demo directory, and create the demo database by running ./demodb demo
And after that, the famous ’emp’ tables are ready for use. One surprise though – I must have known this in the day, but I forgot – this version of Ingres doesn’t use SQL, but it’s own language: QUEL. So ‘select * from emp’ doesn’t work, I had to use some of the examples from the manual.
* range of e is employee * retrieve (e.all) * \g Executing . . . |number|name |salary|manage|birthd|startd| |-------------------------------------------------------| | 157|Jones, Tim | 12000| 199| 1940| 1960| | 1110|Smith, Paul | 6000| 33| 1952| 1973| | 35|Evans, Michael | 5000| 32| 1952| 1974| | 129|Thomas, Tom | 10000| 199| 1941| 1962| | 13|Edwards, Peter | 9000| 199| 1928| 1958| | 215|Collins, Joanne | 7000| 10| 1950| 1971| | 55|James, Mary | 12000| 199| 1920| 1969| | 26|Thompson, Bob | 13000| 199| 1930| 1970| | 98|Williams, Judy | 9000| 199| 1935| 1969| | 32|Smythe, Carol | 9050| 199| 1929| 1967| | 33|Hayes, Evelyn | 10100| 199| 1931| 1963| | 199|Bullock, J.D. | 27000| 0| 1920| 1920| | 4901|Bailey, Chas M. | 8377| 32| 1956| 1975| | 843|Schmidt, Herman | 11204| 26| 1936| 1956| | 2398|Wallace, Maggie J. | 7880| 26| 1940| 1959| | 1639|Choy, Wanda | 11160| 55| 1947| 1970| | 5119|Ferro, Tony | 13621| 55| 1939| 1963| | 37|Raveen, Lemont | 11985| 26| 1950| 1974| | 5219|Williams, Bruce | 13374| 33| 1944| 1959| | 1523|Zugnoni, Arthur A. | 19868| 129| 1928| 1949| | 430|Brunet, Paul C. | 17674| 129| 1938| 1959| | 994|Iwano, Masahiro | 15641| 129| 1944| 1970| | 1330|Onstad, Richard | 8779| 13| 1952| 1971| | 10|Ross, Stanley | 15908| 199| 1927| 1945| | 11|Ross, Stuart | 12067| 0| 1931| 1932| |-------------------------------------------------------| continue *
A little bit more complex example: calculating the average salary for the employees working for each manager:
* range of e is employee * retrieve (e.manager, avgsal=avg(e.salary by e.manager)) * \g Executing . . . |manage|avgsal | |-----------------| | 10| 7000.000| | 0| 19533.500| | 32| 6688.500| | 33| 9687.000| | 13| 8779.000| | 55| 12390.500| | 26| 10356.333| | 199| 11117.556| | 129| 17727.667| |-----------------| continue *
and then of course it would be nice to add another column with the name of the manager. Simple, add another view on the same table and match the number to the manager id:
* range of e is employee * range of m is employee * retrieve (m.name, e.manager, avgsal=avg(e.salary by e.manager)) where e.manager=m.number * \g Executing . . . |name |manage|avgsal | |--------------------------------------| |Ross, Stanley | 10| 7000.000| |Smythe, Carol | 32| 6688.500| |Hayes, Evelyn | 33| 9687.000| |Edwards, Peter | 13| 8779.000| |James, Mary | 55| 12390.500| |Thompson, Bob | 26| 10356.333| |Bullock, J.D. | 199| 11117.556| |Thomas, Tom | 129| 17727.667| |--------------------------------------| continue *
But, oops. Now we’ve lost manager 0 – because there isn’t a row for manager 0 in the table. Maybe 0 means that there isn’t one, and that it’s the big boss who has manager 0 in the table? That would seem right for J.D. Bullock – he fits all the stereotypes, being the oldest, and earning the most of all employees – and he started working in the company the day he was born. But there’s also Stuart Ross, who started a year later, and earns a lot less. So, I’m not sure – maybe the sample data is intentionally confusing.
Anyway, this case of missing rows in the last query is a nice example of what would be easy to lift out of the data with an outer join, but I have no clue how to do that in QUEL, or if it’s even possible. Nothing to be found in the manuals I’ve seen so far.