Sort order not always kept when querying the database
Gear Guys, This mail is a kind of follow-up to the thread started here: https://www.mail-archive.com/picolisp@software-lab.de/msg09124.html . Based on Alex's tips I have set up my database like this: (class +Agenda +Entity) (rel id (+Key +Number)) (rel date (+Ref +Date)) (rel mag (+Idx +String)) (rel year (+Aux +Ref +Number) (week)) (rel monthnum (+Ref +Number)) (rel monthtxt (+Idx +String)) (rel week (+Ref +Number)) (rel daynum (+Ref +Number)) (rel daytxt (+Idx +String)) (rel status (+Idx +String)) I read the data from a text file which is in reverse order and looks like this for the current month: # = Agenda = "date" "mag" "status" # = 2019 = # - october 2019-10-31 XYZ ? 2019-10-30 XYZ ? 2019-10-29 XYZ ? 2019-10-28 XYZ ? 2019-10-27 XYZ Rh 2019-10-26 XYZ Rh 2019-10-25 XYZ Cp 2019-10-24 XYZ Cp 2019-10-23 XYZ Cp 2019-10-22 XYZ Cp 2019-10-21 XYZ Cp 2019-10-20 XYZ Rh 2019-10-19 XYZ J 2019-10-18 XYZ J 2019-10-17 XYZ O 2019-10-16 XYZ F 2019-10-15 XYZ Rh 2019-10-14 XYZ F 2019-10-13 XYZ F 2019-10-12 XYZ Rh 2019-10-11 XYZ Liv 2019-10-10 XYZ O 2019-10-09 XYZ Rh 2019-10-08 XYZ O 2019-10-07 XYZ F 2019-10-06 XYZ Rh 2019-10-05 XYZ J 2019-10-04 XYZ J 2019-10-03 XYZ Rdm 2019-10-02 XYZ J 2019-10-01 XYZ F I read and feed the database with the following function: (de feedAgendaDB (X) (in (list "grep" "-v" "#" X) (line) # skip the first line. (until (eof) # file schema: "date" "mag" "status" (let (Dat (read) Mag (read) Stat (read)) (new! '(+Agenda) # DB schema: "id" "date" "mag" "year" "monthnum" "monthtxt" "week" "daynum" "daytxt" "status" 'id (format (pack (split (chop Dat) "-"))) 'date ($dat Dat "-") 'mag Mag 'year (format (car (mapcar pack (split (chop Dat) "-" 'monthnum (format (cadr (mapcar pack (split (chop Dat) "-" 'monthtxt (get *MonFmt (format (cadr (mapcar pack (split (chop Dat) "-") 'week (week ($dat Dat "-")) 'daynum (format (pack (get (split (chop Dat) "-") 3))) 'daytxt (day ($dat Dat "-")) 'status Stat ) ) ) ) ) where X is the file path. In order to display the results of data queries I use these functions: (setq *Params (-10 -9 -8 -7 -11 -11 -7 -8 -11 -8)) (de agendaShowOne (This) (let Fmt *Params (with This (tab Fmt (: id) (: date) (: mag) (: year) (: monthnum) (: monthtxt) (: week) (: daynum) (: daytxt) (: status) ) ) ) ) (de agendaShowAll (X) (mapcar agendaShowOne X) ) So far my main use of the data is to query the database to see what happen during one week and I do it this way: (agendaShowAll (reverse (collect 'year '+Agenda (2019 41 with this result for the week 41: 20191007 737645 mag150 2019 10 October 41 7 Monday F 20191008 737646 mag150 2019 10 October 41 8 Tuesday O 20191009 737647 mag150 2019 10 October 41 9 Wednesday Rh 20191010 737648 mag150 2019 10 October 41 10 Thursday O 20191011 737649 mag150 2019 10 October 41 11 Friday Liv 20191012 737650 mag150 2019 10 October 41 12 Saturday Rh 20191013 737651 mag150 2019 10 October 41 13 Sunday F **My question is about the week 42 for which the result of the query is:** (agendaShowAll (reverse (collect 'year '+Agenda (2019 42 20191016 737654 mag150 2019 10 October 42 16 Wednesday F 20191017 737655 mag150 2019 10 October 42 17 Thursday O 20191018 737656 mag150 2019 10 October 42 18 Friday J 20191019 737657 mag150 2019 10 October 42 19 Saturday J 20191020 737658 mag150 2019 10 October 42 20 Sunday Rh 20191014 737652 mag150 2019 10 October 42 14 Monday F 20191015 737653 mag150 2019 10 October 42 15 Tuesday Rh The result starts with "Wednesday" instead of "Monday" as for the week 41 or other weeks! Is there any reason explaining this strange behaviour? What am I doing wrong? For what is worth I'm using PIL: (version) 18.9.5 on Manjaro linux. Thanks, best, Eric -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: Sort order not always kept when querying the database
Hi Eric, thanks for sharing your code! As far I could see on a first scan, it looks very good. > The result starts with "Wednesday" instead of > "Monday" as for the week 41 or other weeks! The values have no inherent ordering, as they all have the same key (the combination of year and day). So they may appear in any order, just by chance starting on Monday (probably due to the order the objects were imported). If you want to collect the days in order, you could just use the 'date' index, and calculate the range from the week (I don't know that algorithm at the moment. Assuming we had such functions, we could do (collect 'date '+Agenda (weekStart 2019 42) (weekEnd 2019 42)) This would be the most efficient way. Otherwise you could sort it (by '((This) (: date)) sort (collect 'year '+Agenda (2019 42))) with a little more overhead. ☺/ A!ex -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
