Hi James,
As I said, I agree with the principle but that doesn't alter the fact that I
can make use of pre-sorted tables. Even I would soon realise that some
future change in the way the data gets pulled out of the database using some
other method might change that.
Maybe somebody comes up with a method that starts in the middle and then
works alternately towards each end that is quicker than whatever happens
now. Fine, if that's more efficient, and I'll lose a by-product that
currently helps me.
I don't see any reason why I shouldn't make use of an ability to get what I
want while the method I use works for me. If that method fails at some later
date then I can go down the road of writing single table views - or
whatever - to replace it.
If anybody else can also make use of a feature/by product then they should
be able to use it too. If I'm the only person in the world that finds a use
then I don't mind that either - it's still better for me. It just shows how
wonderfully versatile R:Base is in my opinion.
So, in no particular sequence:
Alastair.
Regards.
<g>
Thanks &
----- Original Message -----
From: "James Bentley" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Thursday, February 07, 2008 11:19 PM
Subject: [RBASE-L] - Re: Build PK from command file
Alistair,
While the concept of pre-sorted tables seem appealing users
should be aware that the SQL standards states explicitly that
programming should not assume that a SELECT statements without
an ORDER BY clause will consistently return data in a specific
sequence. While current implementations may do so, it is not
wise, in view of SQL standards, to assume that this will occur
in a future release. When you deal with multi-table selects the
syntax analyzer and the optimizer along with PRIMARY KEYS,
SECONDARY KEYS, and indexes may return data in a different
order.
It should be a "best practice" that when you require data to be
returned in a particular sequence you include and ORDER BY
clause. As RBase includes more advanced features of the SQL
1999 or SQL 2003 standard ignoring such standards could have
unintended consequences.
Jim Bentley
--- Alastair Burr <[EMAIL PROTECTED]> wrote:
Whilst agreeing with others who have indicated that the SQL
ORDER BY clause should be used in the vast majority of cases
and especially in multi-user environments there are exceptions
where having tables pre-sorted can be useful.
For instance, in my case, I have a table of music tracks with
an idnum, tracknumber, title, etc. I like to have this table
pre-sorted by tracknumber within idnumber. I can think of no
scenario where I might want the tracknumbers jumbled up. I
might want the title sorted alphabetically or by playing time
but never "unsorted".
Generally, I like to have my tables in order of their idnum -
usually the primary key. I find that when looking for errors
(mine) or problems it can be easier to see where there might
be a point at issue: missing idnums, perhaps.
With pre-sorted tables when using the Data Browser there is
less need to have to sort the display before it can be useful.
Obviously not always but often enough to make it worthwhile -
particularly with the more static data.
I also like to re-build my databases at the turn of each year.
As briefly discussed here earlier this year I do this by
unloading the tables in a particular sequence (of tables) so
that they can be reloaded to a new database without conflicts
arising due to missing keys from tables that should have been
loaded first.
Since I have to unload the data anyway I do so with the ORDER
BY clause that best suits each table for my needs.
This, effectively, kills two birds with one stone: I get a
clean database and its tables are pre-sorted.
As a single user there is sometimes a chance that I know what
I have done so I am aware that the tables are only partially
sorted where data is added regularly. My application takes
care to sort as applicable. However, when at the R:> or in the
Data Browser my data is often presented to me without having
to sort it. Also, it so happens, that much of the data will be
added in the "correct" order in any case via the application.
(For some reason which I have never really understood, I tend
to add the tracks, for example, in the order that they appear
- I guess that it is just easier than thinking about which one
I should enter next <g>. I usually only get caught out when
artists think it fun not to number tracks and print the titles
in some obscure order!)
That said, and even though the process is very quick, I would
not like to have to rely on re-building the database regularly
in order to avoid having to use the ORDER BY clause. It's a
question of horses for courses: I can make use of pre-sorted
tables in many instances and I lose nothing by having to sort
those where there is a need.
Regards,
Alastair.
----- Original Message -----
From: Fred C Kopp
To: RBASE-L Mailing List
Sent: Wednesday, February 06, 2008 11:45 PM
Subject: [RBASE-L] - Build PK from command file
I like to sort tables frequently (LastName,FirstName,etc.)
so that we don't always have to add an ORDER BY clause. Just
lazy, I guess. This is probably not great practice but...
From a command file I can RENAME a table to, say, TEMP (I
learned the hard way to use NOCHECK to avoid messing up forms
and reports based on that table). Then I can PROJECT
<original table name> from TEMP USING * ORDER BY LastName
FirstName. Then I can DROP TABLE TEMP.
NOW, how do I build a PK for ColName from the command file?
ALTER TABLE is part of it BUT it wants me to declare UNIQUE
VALUE for ColName before it will build the key. I know it's
in the syntax somewhere but I just can't find it. I think
it's more than one command.
This doesn't seem all that unusual a thing to do. Could
someone please help me with the syntax? Or, is there a better
way to get the same result? (There usually is). I'm also
interested in how to build FK and index from a command file.
I realize that RELOAD or PACK would be a good thing to do
after swelling up the database.
Thanks,
Fred
Fred C. Kopp
Authorized R:Base Developer
19 Teri Lane
Washington, PA 15301
P 724-222-7376
F 724-222-7376
C 724-413-5534
E [EMAIL PROTECTED]
------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.20/1261 - Release
Date: 05/02/2008 20:57
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date:
06/02/2008 20:14
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
