Can I "fake" a listbox?

[kabing]

I have a birdwatching database for tracking field trips, birds seen, etc. Unfortunately my queries have been hanging/crashing, and I am told that this is in part because the relationships between tables are not based on primary keys, but on other fields. I know how to go back and fix this in some instances, but I'm stuck on how to manage this with one relationship. Here are the three tables involved (only pertinent fields listed):

AOU_Birdlist ( each record one species, between 2 and 3k records)

• AOU_ID (primary key)
  English_Name (currently relates to Bird_Name in Sightings Table)
  etc.

Sightings (one record per species seen on a given field trip)

• Sightings_ID (primary key) auto increment
  FieldTrip_ID (foreign key to Field Trips Table)
  Bird_Name (currently relates to English_Name in AOU_Birdlist table)

etc.

Field Trips (one record per field trip)

• FieldTrip_ID
  Location
  Date
  etc.

My primary data entry form has a main for based on the Field Trips table, with a data sheet style subform based on the Sightings table. Currently the Bird_Name is chosen from a combo box. By rights, I should really be using the AOU_ID field to relate the AOU_Birdlist and Sightings Tables. I didn't do so in part, however, because of the huge number of records in the AOU_Birdlist table. It simply isn't feasible to use a list box to search more than 2000 entries for the right bird. The combo box let me begin typing and then auto completed--or at least got me to the right part of the list and let me choose. This is also why I based the relationship on Bird_Name/English_Name fields.

Now, though, it looks like I need to go back and do this properly. But I need a workable way to select the right AOU_ID number for each sighting. Is there a way I can combine a combo box and a Macro to "fake" a list box. That is, is there a macro that would let me identify the English_Name field from the AOU_BIrdlist table but save the AOU_ID number in the corresponding field of the Sightings Table?

I can make the current database available if that's helpful.

kabing

[DrewJensen]

Who told you that your queries where failing because of the lack of FK relationships. FKs are for referential integrity not for the query optimizer...( actually HSQLdb does not even have an optimizer, so it's a mute point all together ) There is no query you can write that should crash the system, if that is happening then you have found a bug in the system. As for hanging, are you sure, or is it just taking an incredibly long time to run? ( Granted if it takes long enough it can be the functional equivalent )

How about trying to take one more look at those queries before you alter your table structure.

As I recall you let me look at this database once before - can you do that again and point out the problem queries. It may be that you need to create some indexes to help speed it along, it may be that you need to re-write them for performance purposes. ( Like I said HSLQdb does not have an optimizer - but that is not 100% true, rather the optimizer sits between the keyboard and the chair )

[kabing]

It was the main developer for NeoOffice who thought that was the problem. He thought it was related to a number of OR statements in the Query, but also to the issues of how the relationships were developed. But that may have been based on his familiarity with SQL in general and not Base /HSQLdb specifically.

Some of what I thought were hangs were just long running times. But one query does seem to hang. Certainly the length of run time is an issue, especially since most of the tables involved (with the notable exception of AOU_Birdlist) have only a handful of records.

I'll get the most recent version of the database set up for download sometime today--I'll post back when I have.

kabing

[DrewJensen]

OK - I'll wait to hear from you...

By the way - given that birding is an activity enjoyed by a good number of people have you thought about making your database available to the public?

[kabing]

OK, I'm back. I'd intended to setup the file for download as part of my last post, but my son was waiting impatiently for his daily Webkinz fix and I was having trouble determining if I could use an older version of the database to replicate the issue. So I decided I'd better cede the computer until a more opportune time.

The database can now be downloaded from this link.

The query that appears to be hanging is User1LifeList. It's a "Query in a Query," using User1Sightings. User1Sightings takes a long time to pull up, but I will actually get results with it if I wait long enough. I haven't yet left User1LifeList to run all night to see if that makes a difference. I'll try to remember to do so tonight. But even if it does actually run eventually, the time needed is far too long to be usable. So I'd appreciate any tips you can give me on the queries.

I'm essentially self-taught, relying heavily on these forums and my experience with Double Helix years ago. I use the GUI to build the queries, although I've read a little bit about SQL.

And yes, it is my intention to make the database publicly available, once I know it's working right. I've already provided it to several folks from oooforum.org who requested it, knowing it wasn't finished.

My purposes for building the database were:
-to track our bird sightings
-to learn about Base
-to form the basis of a tutorial series on Base for NeoOffice's wiki
-to make the database available for other interested parties using OpenOffice.org/StarOffice/NeoOffice.

kabing

[kabing]

Oh, I forgot to mention, just for clarity's sake. The intention of the User1LifeList Query is to pull up a list of bird species that User1 has identified by either voice (i.e. bird call) or sight. There should be one row for each bird species, even if a given bird species has been seen multiple times. [The database is set up to track sightings for up to four users]

Another potential piece of the puzzle. I carefully set up the AOU Birdlist table so that the primary key numbers would allow me to sort the table in the order used by the AOU. So (AOU) ID does have to be part of the Query, as I want any life lists sorted according to that sort order. Would it make any difference if that number were stored in the sightings table, too? In other words, if the query could pull the AOU ID from the Sightings table instead of having to work with the AOU Birdlist table for this? If so, then I guess that means I'm back to my original question, or at least a variation of it.

kabing

[DrewJensen]

ok - got it...I launched that query...and yes the CPU is at 100% and has been now for over 30 seconds...memory allocation is a bit odd...but slowly ticking up as I watch it...

I'll fiddle a bit and let you know

[DrewJensen]

Alright well, I finally killed the instance of OO.o and looked at that query.

I don't remember the exact Issue number but there is an open issue regarding what happens when you open a SQL statement using multliple OR statements and inner joins, in the GUI query designer. Basically it turns your query into mush...and mush is what you have now...Techincally it is creating a set of cartesian result sets ( that ain't good ). I'm talking about the lower query now User1SightingList that is where the problem is, not in the higher query User1LifeList.

Let me see what I can make of it..

One row for each species the user has recognized - by sight or by call.

Just off the top of my head sounds like a place to use a UNION but I'll try both with a UNION and with a SUBSELECT and see how the performance goes..

[DrewJensen]

Alright - well this is not the only way to do this, but it is one.

NeoBirdDatabase_w_views.odb

(1.56 MiB) Downloaded 200 times

There are now 4 views and 4 queries.

Views:

• vUser1SightHeard
  vUser2SightHeard
  vUser3SightHeard
  vUser4SightHeard

Queries:

• qryUser1LifeList
  qryUser2LifeList
  qryUser3LifeList
  qryUser4LifeList

The views consist of the following statement:

Code: Select all

SELECT 
	"AOU Birdlist"."EnglishName", 
	"Sightings"."Field Trip ID", 
	"Field Trips"."Date", 
	"Field Trips"."Location" 
FROM "Sightings", "Field Trips", "AOU Birdlist" 
WHERE 
	( "Sightings"."Field Trip ID" = "Field Trips"."ID"  AND "AOU Birdlist"."EnglishName" = "Sightings"."Bird Name" ) 
	AND (  "Sightings"."User1Voice" = True  )

UNION

SELECT 
	"AOU Birdlist"."EnglishName", 
	"Sightings"."Field Trip ID", 
	"Field Trips"."Date", 
	"Field Trips"."Location" 
FROM "Sightings", "Field Trips", "AOU Birdlist" 
WHERE 
	( "Sightings"."Field Trip ID" = "Field Trips"."ID" AND "AOU Birdlist"."EnglishName" = "Sightings"."Bird Name" ) 
	AND  ( "Sightings"."User1Sight" = True )

The only difference with each view is that UserXSight or UserXVoice is used.

Each of the queries then uses the corresponding view with this select statement:

Code: Select all

SELECT 
	"EnglishName", 
	CAST( MIN( "Date" ) AS DATE ) AS "FirstSightedDate", 
	COUNT( "Date" ) AS "SightingsCount" 
FROM "vUser1SightHeard" 
GROUP BY "EnglishName"

A couple of points:

The views are not particularly optimized, but good enough for a smallish database. If you are using OO.o 2.4 you can edit view definitions now, if you do be sure to set the SQL Direct flag when you try to save the definition.

The queries also use SQL Direct, because they use the CAST function:

CAST( MIN( "Date" ) AS DATE ) AS "FirstSightedDate"

the reason for this is to work around a glitch with Base. Using the MIN function on a DATE type returns an integer and not a DATE type. So I used cast to push it back to a DATE type and the Base query analyzer does not directly support the CAST function, so SQL Direct to turn the base analyzer ( escape processing ) off.

Performance wise - on my machine, under windows the queries run in < 2 seconds cold and < 1 second on subsequent runs.

Edit: One other thing - your odb file ( and the updated one from me ) crash OO.o 3.00 pre-beta m_2 release..I opened an issue on this and listed the link to the original file you used for the earlier post. The issue can be found at: http://www.openoffice.org/issues/show_bug.cgi?id=88963

[kabing]

Thanks so much, Drew!

The one thing this doesn't give me is the AOU Birdlist ID#, which lets me sort the list by the AOU order. I tried just adding "ID"."AOU Birdlist" to the SELECT statements on either side of the UNION command, but I got an error message saying something along the lines of "The given command is not a select statement." The views you created run just fine, but if I try to recreate one using the code you provided, I get the same error.

I'm wondering if the issue is the UNION Command and the fact that NeoOffice 2.2.3 uses the OpenOffice.org 2.2.1 code. Presumably the reason why I can use your View statements is related to the fact that Views can't be edited?

Thanks for filing the issue in regards to 3.0 pre-Beta pluby (the NeoOffice developer) had mentioned that my odb file crashed 3.0, too. I'll make a note in NeoOffice's Bugzilla about the OpenOffice.org issue number.

kabing

[DrewJensen]

On the query's window the tool button furthest to the right, normally, is the SQL Direct button. You need to click that before you save it.

[DrewJensen]

Now - one more change.

Since you have 2.4, you can now use a CROSS JOIN in the query designer. This is going to allow you to do this without the use of a View, in fact it will be a query in a query.
( Then well come back to a view and make it only one view and one query for all 4 users, using replaceable parameters )

OK - for the query: Here is the finished query

A couple of points when you do this with 2.4 and your database as it currently is

• all the joins are created for you automatically, except for the first CROSS JOIN.
  no need to use the CAST function for the date field, because the query is running with Escape Processing enabled

To start the query

• Add the Sightings table twice
  JOIN the two tables on the ID field
  Select the JOIN line and open the join properties dialog ( Double click, or pop up menu )
  Set the join type to Cross Join
  The join line in the GUI designer will move to the top of the two tables

Then ADD the other tables and fields as needed.
This gives this result then.

[kabing]

You're right, I missed your point about the SQL Direct button. I just didn't read clearly enough. It works fine now.

Unfortunately, I don't have access to 2.4. The NeoOffice developers will be skipping 2.3 and 2.4. They will be porting 3.0 starting this summer with the OpenOffice.org betas of 3.0, I believe. So I'll keep the Cross Join method in mind for then.

I would be interested in the replaceable parameters, if that will work with 2.2.1

Oh, and could you tell me why we need to use a View instead of a Query? I've never quite understood the difference between those two. And I'd like to explain why I'm going the View route when I update the tutorial.

Many thanks

kabing