On Friday 28 January 2005 05:28 pm, Joerg Ostertag wrote:
[...blah blah blah not needing to find out the secret mystery id blah blah...]
> Wouldn't an index on the desired fields (MACADDR,WEP) do the same job?
[...]
> > ID#. This makes the one advantage I see to "mystery number" ID's
> > (automatic assignment of the ID)
>
> If you make the id autoincrement it will be assigned by mysql.
That's what I meant - that's the one useful "trick" that you can get with the
"secret mystery integer" key that you just can't get with a more flexible
unique ID - on the other hand, the INTENT for the meaningful unique ID would
be that the ID is predictable (e.g. you always know it's the MAC address if
the type it "WLAN" or "WLAN-WEP", which is a piece of information that you
have before you even go to update the database. Being predictable, there'd
never be a NEED to "auto-assign" a random ID code, since you'd always know
what the key should be before you even contact the database server.
The issue of what to use for unique id's of other feature types is,
definitely, a non-trivial one admittedly. For some there are obvious ones
("macaddr" for WLAN*, "waypoint" for geocaches on Navicache.com) but for some
of the more generic ones (e.g. GNIS and "geonames" data) it's a bit more
problematic. I'm hoping you're about to point out a much easier way to do
what I'm trying to do...
[...]
> If I use an integer (which is automatically gernerated by the DB) i have a
> short easy to handle and fast index for the relational database. This way I
> can easily make joins between the tables. And I have no problems findung a
> unique key, because the database itself handles generating a unique PRIMARY
> key for me. This won't stop us from generating other index on different
> fields for updates of specific data.
[...]
> As of my understanding, you want the 'meaningfull' index for updating
> purpose. So you can replace existing entries by more acurate/corrected
> ones. And you don't want to fiddle with getting the ID and then replacing
> the values?
>
> I would think we can accomplish this by creating an index on the field you
> want (MAC-Address for Example). This should enable you to do an update of
> your keys the same way you plan it.
>
> What do you think about this.
I think that perhaps you're about to point out some replace/update syntax that
I have stupidly missed that does the "insert or update as appropriate" with a
single query without needing to replace the "secret mystery number" ID...
This issue for me isn't just updating, but importing an updated set of data
that may contain some NEW data that needs to be added AND some updated
versions of existing data. I'm looking to be able to tell the database (for
example) "here's the current location of GEOCACHE 'N012345'" and have the
database able to either update the existing N012345 cache information if it's
already there OR add a new row for N012345, without having to do multiple
queries for each row. (The current Navicache data includes around 5200 rows,
which isn't all THAT big, but it's growing...)
I had THOUGHT that "indexing" just created an ordered file for faster
searching, rather than for finding unique rows (i.e. you can have duplicate
values for multiple rows in an indexed field) - therefore you still need to do
either two queries for every insert (select to see if the row already exists,
then another query to either insert or update) or one massive select and store
the results in memory to determine whether to insert or update. In other
words, you still have to do a "pre-insert check" on every row, the database
just answers each initial "select" a bit more quickly...or so I thought.
The short version of what I'm trying to get to is that I'd like to be able to
tell the database "if this row's feature (i.e. WLAN, TOWER, GEOCACHE...)
already exists, just update it with this information, otherwise add a new
row" without having to do multiple queries for reach row (going back to the
WLAN example, using the "macaddr" field as the unique id, I don't NEED to
know in advance if the record is already in the table - I just issue a
"REPLACE" and that's the end of it. In it's current "secret mystery number"
structure, GPSKismet does two queries - a select to see if the row exists,
then a branch to either an insert or update depending on what the first query
returned...)
Incidentally, in case it's not obvious, at this stage I'm not really that
worried about convincing people to implement the concept. It's pretty clear
that I'm the only one worried about it, which to me implies that I'm just
missing something. Besides, there ARE workarounds (it's not THAT big of a
deal for me to just do a "DELETE" for all rows from a particular dataset and
then INSERT all of the updated rows...). Just trying to cure my own
ignorance at this point, that's all...
--
This message was part of the gpsdrive mailinglist
unsubscribing can be done by sending a mail containing a body of:
-quote--
unsubscribe gpsdrive
-unquote--
to majordomo-ahljRfkWE0EwtzFM8z8+AfP6llvjuJOh@xxxxxxxxxxxxxxxx
|
