Database changes

Old Reflector
1

There have been a few changes to the database.

  1. The menu structure is different. I don’t know if this is right yet so I thought I’d let my users test it and see what they think. The “View Results” menu is changed. Everything specific to you “My Activator Log”, “My Chaser Log” etc. is on a sub-menu.

  2. My Awards. Some non-award items have moved to a new “My Statistics” page. I’ve changed the Mountain Hunter results. This was due to the fact that ABSOLUTELY NOBODY ever read the rules for the Mountain Hunter awards and kept asking the same question “why doesn’t this association show.” Now the page shows the number of times you have worked each QUALIFYING association. It also shows the non-qualifying associations. Go read the rules if you don’t know what a qualifying association is! :wink:

  3. My Statistics. This shows some guff calculated form the database. I was just noodling with some ideas. This can be expanded and/or changed. Beer/whisky/vodka is an effective bargaining tool if you want specifics doing.

  4. My Activator Uniques. This has gained a “Show breakdown by Association” button at the top. The normal view shows your unique summit activations sorted by date. Click the button to see the view sorted by Summit Reference.

Of course there will be bugs and features. There’ll be some splendid bugs that will fill your screen with red exception traces. Why should I test it when you chaps (and chapesses) will do a much better job for me!

So please feel free to comment on things in this thread. If you think you’ve found a bug then you can check the thread to see if anyone has found it and described it. If not, write it in here.

Andy
MM0FMF

(No whisky was consumed during this software update)

3 Likes
2

In reply to MM0FMF:

Very good; I like the statistics page.
Only thing still missing is the ability to sort the chaser log by most recent contact first! (hint)

Pete

3

In reply to G4ISJ:

Dec-Jan as opposed to Jan-Dec in the dropdown?

Andy
MM0FMF

1 Like
4

In reply to MM0FMF:
NO.
Most recent at the top, as in descending DTG order.
If I select Dec-Jan my most recent is currently 8 rows down :slight_smile:

It would be nice to sort by time as well as date…

5

In reply to G4ISJ:

I always thought it did sort by time & date. I wrote it to sort by time & date. And now I know why it doesn’t. That fix will roll out automagically on the next update of whatever.

Thanks Pete.

Andy
MM0FMF

1 Like
6

In reply to MM0FMF:
Andy,Good work. Looks fine on my office computer even if the shack one leaves out some vertical divider lines.

I also note with disappointment that my score has not improved as a result of the upgrade. How many points for a litre of whisky???

Thanks for the effort and the extra features. Very much appreciated.

73
VK3AFW/AX3AFW

1 Like
7

In reply to MM0FMF:

(No whisky was consumed during this software update)

Lots of coffee? :wink:

I’d be inclined to make the “My *” sub-menu into a main menu instead, possibly one that only appears when you’re logged in, but that might be more complication than you’d want to deal with…

Thanks for the extras under statistics. Interesting. :wink:

73, Rick M0LEP

1 Like
8

Statistics list is very useful Andy - Thanks, a useful enhancement to the database.

I found I had used the activator callsigns of DF2GN/P (2 contacts) and G4ERP/P (1 contact) on 27/01/2008! These were the result of wrongly entered data which had gone unnoticed until I viewed the new function provided. These wrongly entered activator contacts I entered have now been deleted and re-entered as chaser contacts.

If you are a multitask SOTAist (Activator and Chaser) and have used different callsigns it is well worth a look down that statistics list…

73 Phil

9

I don’t like to be greedy and ask for too much Andy as we are doing very well, thank you very much…

but would it be possible to order ones list of SOTA Completes for viewing based on the first complete made being shown at the top of the list and the last complete being shown at the bottom? At present the list is ordered by reference.

I imagine it may be difficult to arrange this alternative display.

73 Phil

10

In reply to MM0FMF:
I like the new My Results with sub menu. The main menu looks a lot less cluttered now. Thumbs up from me.

73 Andrew G4AFI

11

In reply to G4OBK:

It’s a reasonable request Phil, just damn hard to do!

The current code works by asking for a distinct list of summits chased and distinct list summits activated and “unioning” them. You may need to refer to your high school set-theory and Venn diagrams to see how that works. The nice thing is that is a single query to the database, though the SQL optimiser may split it into more requests. The result is a list of distinct summits that you have activated and chased.

What is lacking is that it just takes the 1st occurance of each summit from the database and the first item returned is not necessarily the oldest item. A way to do what you wat is to extract a list of distinct summits activated ordered by activation date into a temporay table and likewise for chased summits ordered by chased date and then union them. Not terribly hard, but it just needs doing. Did I say it was damn hard at the start. Well it fouled up last time I tried it. So it shouldn’t be hard but was in practice!

Barry wants this for the awards as well which pushes it up the list of jobs. The stats and things came as a result of cleaning up some code elsewhere whoch had got a bit messy and dirty. Given I was going to remove some crud and change things, adding the stats page only took about an hour on top of the work I was doing. It’s part of some dynamic menus I want to try.

Andy
MM0FMF

1 Like
12

In reply to MM0FMF:
Well that gives me some idea of how difficult a job you have with this monster of a database / user interface…if in time it happens all well and good but for just one extra feature it probably isn’t worth risking spoiling other features or spending oodles of time on it by the sound of it.

73 Phil

1 Like
13

Thanks Andy! Ssh... the best Belgian Blonde beer in the world  | Duvel

1 Like
14

In reply to MM0FMF:

It’s a reasonable request Phil, just damn hard to do!

I had exactly this problem when trying to extract the dates to put into our retrospective “Complete” applications. Our QSOs are recorded in MS Access. It took me a while to work out how to do it in SQL, but in the end it was fairly easy. I would hope that the same basic technique would work in MSSQL.

What is lacking is that it just takes the 1st occurance of each summit
from the database and the first item returned is not necessarily the
oldest item.

Aggregate queries are your friend here.

Assume you have a query which generates a simple list of activation dates and summit references. You can get the first activation date for each summit with something like:

SELECT T.Ref, Min(T.Date) AS [First]
FROM [Activations] AS T
GROUP BY T.Ref;

(in my actual query, I generate “Last” and “Count” too, but we don’t need that here).

Call this query UniqueAct.

Then do exactly the same for the list of chases, call it UniqueChase.

You can then do an inner join on these two queries to find the references that are in common between them, and simultaneously compute a new column “Completed” which is the later of the two “First” dates. This column can then be used to sort the result:

SELECT A.Ref, A.Name, A.First AS Activated, C.First AS Chased, IIf([A].[First]>[C].[First],[A].[First],[C].[First]) AS Completed
FROM [UniqueAct] AS A INNER JOIN [UniqueChase] AS C ON A.Ref = C.Ref
ORDER BY Completed;

This neatly gives you table of references in the order that they were completed, along with the dates of first activation and first chase.

The key insight was to pick out the “first” event of each type before trying to combine the two lists. If you try to work on a consolidated list of all activations and all chases you can get into a terrible mess.

–
Martyn M1MAJ

1 Like
15

In reply to MM0FMF:

Andy,

thanks for all your hard work on the database.

I generally like the changes you have made to the way we interact with the database. I do have one question. How is the “My Top Activated Summits” list on the user statistics page determined? I have activated 59 unique summits (I live a long way from any summits and I have yet to activate any summit twice.) The “My Top Activated Summits” list is always the same and in the same order, so however they are chosen gives consistent results. They are not listed in chronological order, nor are they sorted by summit code. I suspect they are chosen and sorted by some key in the database that most of us never see.

Anyway, I was just curious about it. Thanks again for your work on SOTA.

73,
Doug, N7NGO

1 Like
16

In reply to MM0FMF:

How about making all tables sortable? I didn’t know how this could be done, but here’s one (of several) small javascript libraries that I found to do it:

http://www.kryogenix.org/code/browser/sorttable/

73,

Andrew

17

In reply to M1MAJ:

Thanks for the code snippets. When Barry asked for an date ordered list I thought “easy” and then tweaked the code which ended up with dupes in it. The we started having lots of database issues with the host and I forgot it wasn’t complete. Now the host has moved us to a super fast server I’m happy to tweak again. In fact coupled with a new Windows PC at home, development has become fun again. Of course I uploaded the “work in progress” code which didn’t work and then had to back track quickly.

Also my inbox had quite a few suggestions for routines to handle this query. I’ll email replies to everyone as I can’t remember who suggested what. That’s one of things I do like about this project. I’ve spent nearly all my professional career writing software at the boundary between hardware and software, device drivers etc. Or hard real time systems. So developing for web apps and databases is really different. What I find fascinating is how there are so many different ways of framing SQL queries, I’m just a beginner at this but the suggestions show different ways of achieving what we want. All of them are wonderful brain food!

In reply to N7NGO:

I’ve not looked! But I think you’re right. The search does an ORDER BY on the counted field but when all the counts are 1 you get them in the order the query returns. As an intellectual curiosity I might run a local copy of the query with more fields and see just which key is being used.

In reply to HL5ZBA:

The tables which can be ordered are the ones it was considered valuable. Or, Gary, who did the original code, made some where you can change the order and I’ve copied that template for some others. Given that it’s a lower priority job, are there any particular tables that you’d more like to have sortable than others?

Andy
MM0FMF

1 Like
18

In reply to MM0FMF:

Thank you for considering what I wrote. Most recently the thing that made me say “I really wish this list had clickable headings for sorting” was the summits list: Hiking in the mountains: tips for beginner hikers - Mountain Day

I can enter my QTH and get a list of all summits within a certain radius. Which is great! They are sorted by distance, which is not unreasonable, but what I really want to do is sort them by number of points, or elevation, or code, or distance again. If the headings were clickable, which caused the list to be sorted by that column (which it seems can be done with a bit of Javascript) then I’d like that.

Probably some of the other results tables I look at would make me think “I wish this was sortable” but only the summit list comes to mind. It might be that it’s trivial to add sortability to any table.

73,

Andrew

1 Like
19

In reply to MM0FMF:

I also suggested in a direct email that the S2S list could include a column with the calculation of the line-of-sight distance between the two summit points. I’m repeating it here in case anyone else thinks it’s a good idea. The maths is not too complicated.

73,

Andrew

20

In reply to HL5ZBA:

The distance display is easy to do. One of the 1st programs I wrote was for my TI59 calculator back in 1979 which claculated the distance between 2 points on earth using lat&long. That routine has served me well, I used it a VHF contest scoring programme written in 1996-ish and again in 2009. So I’ve written version in TI59 code, GW-Basic, C, C++ & Python so far. Now it’s converting to C#.

It’s a useful thing but again lower down the list than others.

There is a TSV bug in V2 files which someone mailed me. But I cannot find the mail. That is something that needs fixing 1st should anyone have spotted it.

Andy
MM0FMF

1 Like