- main - earth.li

RE: [ALUG] Running total from a database table - how?
by Keith Watson 22 Nov '04

22 Nov '04

> From: Chris Green Sent: 22 November 2004 13:40 > > I'm steadily progressing with my conversion of my Access accounting > system to a Linux based system. > > I've moved from using mysql to postgresql which has made a few things > easier by having VIEWs in the database. > > Rekall seems to have most of what I need on the forms and reports > front except for one thing - I can't see how to implement a 'running > total' column. > > The Access97 report tool has a specific 'running total' column type > which does exactly what I need. Rekall doesn't have anything like > this (that I can see). > > Has anyone got any bright ideas of how I can do this, it's rather > vital on things like a Bank Reconciliation report to enable me to > check my accounts against the bank statement. > > -- > Chris Green (chris(a)areti.co.uk) > can you give a specific example as 'running total' covers a lot of ground :o) Regards, Keith ____________ Whatever thou sayest of God is untrue. - Meister Eckhart

4 6

RE: [ALUG] Re: Running total from a database table - how?
by Keith Watson 22 Nov '04

22 Nov '04

Works for me! :o) Regards, Keith ____________ Sometimes the highest form of action is inaction. - Jerry Brown > -----Original Message----- > From: main-admin(a)lists.alug.org.uk > [mailto:main-admin@lists.alug.org.uk]On Behalf Of Brett Parker > Sent: 22 November 2004 16:48 > To: main(a)lists.alug.org.uk > Subject: Re: [ALUG] Re: Running total from a database table - how? > > > On Mon, Nov 22, 2004 at 04:17:56PM +0000, Chris Green wrote: > > Yes, of course, I want a report that looks like:- > > > > Date Description Amount Balance > > 01/09/04 Initial balance +1000.00 1000.00 > > 03/09/04 Stamps -14.58 985.42 > > 06/09/04 Network Card -9.99 975.43 > > 12/09/04 Interest +0.10 975.53 > > date | description | amount | balance > ----------------------------+------------------+---------+--------- > 2004-11-22 16:39:52.046068 | Starting Balance | 1000.00 | 1000.00 > 2004-11-22 16:40:11.58727 | Booze | -50.00 | 950.00 > 2004-11-22 16:40:30.539512 | Poker Winnings | 50.00 | 1000.00 > 2004-11-22 16:40:59.067902 | CD | -10.99 | 989.01 > > > > It's a little more complicated than the above but that shows the > > essentials. (I don't guarantee my arithmetic is correct) > > > The Date, Description and Amount columns are directly from > a database > > table. The Balance column needs to ba calculated as the report is > > created. > > Yup, that's how that's done... Here's the *MAGIC* ;) > > create view testwithrunningtotal as select > date,description,amount,(select sum(amount) from test as > test2 where test2.date <= test.date) as balance from test > order by date; > > select * from testwithrunningtotal; > > > OK - so it's not pretty, but it does appear to work ;) > Obviously, if you're not > really using timestamps, like I was, and you can *assume* > that your IDs are in > the right order, then sort by those for the subselect. > > If you need to be able to 'mess' with this, then you'll also > need to set up > some insert and delete rules on the view, probably ;) > > Cheers, > -- > Brett Parker > web: http://www.sommitrealweird.co.uk/ > email: iDunno(a)sommitrealweird.co.uk > > _______________________________________________ > main(a)lists.alug.org.uk > http://www.alug.org.uk/ > http://lists.alug.org.uk/mailman/listinfo/main > Unsubscribe? See message headers or the web site above! >

1 0

Running total from a database table - how?
by Chris Green 22 Nov '04

22 Nov '04

I'm steadily progressing with my conversion of my Access accounting system to a Linux based system. I've moved from using mysql to postgresql which has made a few things easier by having VIEWs in the database. Rekall seems to have most of what I need on the forms and reports front except for one thing - I can't see how to implement a 'running total' column. The Access97 report tool has a specific 'running total' column type which does exactly what I need. Rekall doesn't have anything like this (that I can see). Has anyone got any bright ideas of how I can do this, it's rather vital on things like a Bank Reconciliation report to enable me to check my accounts against the bank statement. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

1 0

RE SUSE Network problem
by RICHARD FLETCHER 22 Nov '04

22 Nov '04

Thanks for the replies helping me to sort out this problem. few answers to questions asked. While the reinstall goes on! ( going to look at the SAMBA config files before i start the samba server this time. see if anything changes in there. slow way of doing things but i'm learning all the time! when i tried to find other computers with smb://IP, konquorer just sat there and did nothing for a while and then came back with the same message. (network unreachable) (found the "internet server" though with its static IP) my apologies to not getting back Laurie: >Check DNS, dhcp and WINS. >Do smbclient and/or smbmount work? If not, what are the synptoms? being the floundering newbie that i said i was this made my head pop! i did have a look in the control centre and alas "play with" (not check) dchp, DNS and wins. For some reason this killed konquorer and my ability to browse anything including email :-( hence the reinstall (getting really good at that now!). smbclient/smb mount will take some more investigation by me as i dont know what to do with them (I assume they are console commands). might be up and running this afternoon with a fresh beast to slaughter. I somehow think that i'll be back with other symptoms later. thankyou all for now i'll be in touch..... Fletch. ___________________________________________________________ Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo! Mail to make your dream a reality. Get Yahoo! Mail www.yahoo.co.uk/10k

1 0

SUSE 9.1 SAMBA NETWORK ERROR.
by RICHARD FLETCHER 22 Nov '04

22 Nov '04

This is probably a really simple one but as a floundering newbie, I is really stuck now. I'm was running SUSE 9.1 after a fresh install and all was OK. The install included ALL available packages that came with SUSE 9.1. My Linux box was the only one in a sea of Win XP machines on our network but I was able to see them and browse their shares with confidence using konqueror. I did ALL of the on-line updates with YAST and all was still OK. I then Enabled the SAMBA Server/client in YAST so the they could browse my shares and it went all pear shaped. I am now unable to browse the network using konqueror as I used to be able to do. After clicking on the "windows network" icon as I always have i get the following message. "Internal Error Please send a full bug report at http://bugs.kde.org Unknown error condition in stat: Network is unreachable" It is reachable though. Our proxy server has a fixed IP and I am able to browse its files if I put "smb://IP ADDRESS" in the address bar. The other WIN XP boxes in the network are using DHCP and I'm unable to find them. The thing is that after an extensive google search for the answer and finding several different "definate causes" for the problem, none of them work and I am still no nearer to stopping all the WIN XP moomins from laughing at my sorry attempts at running a different OS. It seems like there are a lot of people out there with the same problem but no-one knows how to sort this. I feel thick. I hope someone can help me.... my brain hurts :-( Fletch ___________________________________________________________ Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo! Mail to make your dream a reality. Get Yahoo! Mail www.yahoo.co.uk/10k

3 2

Weekly IRC reminder
by Neill Newman 22 Nov '04

22 Nov '04

This is the automated ALUG IRC meeting reminder sent to remind you that there is an IRC meeting tonight (Monday) at 8.00pm. The meeting takes place on the irc server irc.alug.org.uk in the #alug channel. Most IRC clients should allow you to get there with the commands "/server irc.alug.org.uk" and "/join #alug". Popular IRC clients include tkirc, bitchx, xchat and EPIC on Unix, and Mirc under windows. Here are some links to help you get started with irc. Unix http://www.xchat.org/ http://freshmeat.net/ http://www.epicsol.org/ Windows http://www.mirc.com (loads of help with irc for newbies!)

1 0

Linux
by Jeremy Dales 18 Nov '04

18 Nov '04

As requested, I'll try and outline why I am interested in Linux, along with a few first impressions, although I am certainly not a technical person, so I am more interested in what it does rather than how it does it. I like the idea of Linux being open source - in fact I would say it is something I believe in quite strongly, and I used several open source applications while I was running Windows. I was also fed up with the numerous security flaws I encountered in every version of Windows I used, and despite the best protection around, was still plagued with spam, spyware, trojans and viruses. It seems that Linux, for whatever reason, has largely avoided this. If I have a concern about using Linux, it is less to do with it as an operating system and more to do with my inability to comprehend it. While I suspect that this is not uncommon in the first week of use, I have to say that it has seemed fiendishly complicated to do even very basic things like install applications or get the hardware configured - this has necessitated ploughing through long lines of unfathomable code of the kind I usually try to avoid. I am hoping that I will either become used to it, or more likely, that having got the system set up the way I like it, I can have as little to do with opening the terminal window as possible. In short, I'm looking for a smooth and reliable graphical interface that is simple to use, relatively secure, and has the additional benefit of being open source. Jeremy

5 11

mysql users question
by Chris Green 18 Nov '04

18 Nov '04

How do I delete a mysql user? ... and how do I stop a mysql user requiring a password? -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

3 5

SuSE 9.1 and flash cards
by Wayne Stallwood 18 Nov '04

18 Nov '04

I am sure this topic has come up before, but I can't find a solution in the archives. Running SuSE 9.1 I would like the same automount functionality for my multi format card reader as SuSE/KDE provides for CD/DVD drives. I have a "My Computer" icon on my desktop, inside that I have an icon for each removable drive on my system. If I insert a CD/DVD with data content then it is mounted and a konq file browser window appears on the desktop. Is it possible to get the same functionality for a USB card reader (each card type appears as a different device) I can mount various card types at will using the mount command, but I have to tail /var/log/messages to check what device that card is assigned to and then manually mount/unmount it. After I have that bit working I would really like it to detect a card insertion without me having to detach/reattach the reader from the USB ports. But I am not even sure if that last bit is possible. Anyone fixed this ? Wayne

1 2

Fonts without X
by Richard Lewis 15 Nov '04

15 Nov '04

Hello ALUG, I'm just working on adding a PDF 'data sheet' function to one of my databases. (Served using PHP and the FPDF class <http://www.fpdf.org/>). At the moment it doesn't work because it can't find any fonts (even with names like "Times") and, of course, the obvious answer is to install some. The Debian package for FPDF suggests installing 'ttf2pt1'. This package depends on a whole load (~20MB, OK not that much) of packages from X which I'm slightly reluctant to install because the machine is supposed to be a web/database server, not an X server. It doesn't, of course, need an X server and maybe (suggestions?) I'm being silly by not wanting to install 'xfree86-common', 'xlibs', etc. (I just don't like the idea of X clutter everywhere). So, can anyone suggest a way of installing a nice selection of fonts without any X packages or will someone reassure me that installing X packages (not server) is not something to worry about? ;-) Cheers, Richard -- Richard Lewis richardlewis(a)fastmail.co.uk

2 1

[ALUG Announce]Debian Bug squashing in Cambridge, November 27th to 28th
by MJ Ray 15 Nov '04

15 Nov '04

ALUG Announce Please send announcements to Announce(a)lists.alug.org.uk Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce ----- Maybe we can discuss this at Syleham on the 28th too? ---------- Forwarded message ---------- Date: 2004-11-11 16:53:05 +0000 From: Steve McIntyre <steve(a)einval.com> Subject: [Debian-uk] Bug squashing in Cambridge, November 27th to 28th Guys, we can't let the Germans and Australians have all the fun! Let's get together in Cambridge and show them how to close those bugs. If we all pull together, we can get the RC bugs killed and help make sarge a great (if late) release. Everyone's welcome to join us in person, and we'll be active on irc.oftc.net, #debian-uk too if you want to help but can't make it physically. If you want to come either day, please let me know. There's even (limited) crash space available. See the wiki page at http://wiki.earth.li/DebianBugSquashPartyNov2004 Location: 87 Kendal Way, Cambridge CB4 1LP (aka my house, as usual) Equipment: We have wired and wireless networking, a full local mirror and several architectures. Bring a laptop, power cables and ethernet cables if you need them. I hope to see you there... -- Steve McIntyre, Cambridge, UK. steve(a)einval.com "It's actually quite entertaining to watch ag129 prop his foot up on the desk so he can get a better aim." [ seen in ucam.chat ] -- MJR/slef My Opinion Only and not of any group I know Creative copyleft computing - http://www.ttllp.co.uk/ Unsolicited attachments to the pipex address deleted Will HLF fund tree-killings? http://www.thewalks.co.uk/ ----- Send discussion replies to main(a)lists.alug.org.uk (Reply-To set) Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce

1 0

Syleham Meet on 28th November
by Keith Watson 15 Nov '04

15 Nov '04

If anyone needs a lift let me know. I live in Cringleford near the UEA but I'm happy to pick people up from Norwich rail station (or anywhere else in Norwich for that matter) or anywhere along the route between Norwich and Syleham. As it's getting near the festive season (<GRUMPY_OLD_MAN> which all the local and national retailers seem to think started back in September!</GRUMPY_OLD_MAN>) I'll be bringing along a small selection of comestibles (mince pies etc.) I thought it might be useful if I brought along my old PC from the back of the cupboard and demo'd a Debian install using the Knoppix or Gnoppix live CDS. (John - do you have an internet feed at Syleham?). Anyone be interested in that? In my opinion the combination of these live CDS and Debian makes a Linux install as easy (easier) then Windows if you just want a usable PC system and are not particularly interested in becoming an expert in the nuts and bolts. So if there's anyone on the list who's new to Linux and can make it then you can see how really really easy installation of the OS with Knoppix/Gnoppix and how easy it is adding (and removing) new software with Debian (forget that tarball, configure, make crap and learn apt-get! or even drive it all with a GUI like synaptic :o) ) Regards, Keith -------------- Not one, not two; not both, not neither. - Zen saying

4 3

Weekly IRC reminder
by Neill Newman 15 Nov '04

15 Nov '04

This is the automated ALUG IRC meeting reminder sent to remind you that there is an IRC meeting tonight (Monday) at 8.00pm. The meeting takes place on the irc server irc.alug.org.uk in the #alug channel. Most IRC clients should allow you to get there with the commands "/server irc.alug.org.uk" and "/join #alug". Popular IRC clients include tkirc, bitchx, xchat and EPIC on Unix, and Mirc under windows. Here are some links to help you get started with irc. Unix http://www.xchat.org/ http://freshmeat.net/ http://www.epicsol.org/ Windows http://www.mirc.com (loads of help with irc for newbies!)

1 0

Converting DVD to audio
by Graham 14 Nov '04

14 Nov '04

Having replaced my video with a DVD recorder, I record radio programs from Sky then take the DVD to my computer and convert it to WAV for editing and writing to CD. The script I use is below. As you can see it goes round the houses, creating a VCD image first then stripping out the sound. Can anyone suggest a simpler approach (that doesn't involve physically moving equipment)? Note: I don't want to go via MP3 or OGG. The DVD audio format is AC3, BTW. -- GT ----------------------------------------------------------------------------- #!/bin/sh if [ $# .eq 0 ] then echo "Usage: dvd2wav <output filename>" exit fi mencoder dvd://1 -o dvd2wav_temp.mpg -ovc \ lavc -lavcopts vcodec=mpeg1video -oac pcm transcode -i dvd2wav_temp.mpg -y null,wav -o "$1".tmp resample -to 44100 "$1".tmp "$1" >/dev/null rm -f dvd2wav_temp.mpg "$1".tmp exit

2 2

[ALUG Announce]ChelmerLUG: FireFox 1.0 Party / Meeting
by Matthew Saunders 13 Nov '04

13 Nov '04

ALUG Announce Please send announcements to Announce(a)lists.alug.org.uk Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce ----- Hi, I would like to invite you to a ChelmerLUG FireFox 1.0 Party. It will be taking place on 19th November 2004 from 5.30pm onwards. The location is at the Writtle College Student Bar, Lordship Road, Chelmsford. It would be great to meet people from the area, share ideas, and with the student bar's prices - it won't break the bank either! ChelmerLUG is starting up in Chelmsford and open to anyone who has an interest in computing. Our aim to to promote Linux, and Open Source software on any platform. FireFox 1.0 is a fast, secure and safe web browser - even on Windows! FireFox can be downloaded from http://getfirefox.com. Any comments / questions? Just send me an email. Kind Regards Matthew Saunders ChelmerLUG http://chelmer.lug.org.uk http://spreadfirefox.com ----- Send discussion replies to main(a)lists.alug.org.uk (Reply-To set) Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce

1 0

MMC card woes
by Wayne Stallwood 13 Nov '04

13 Nov '04

Is there any way to bypass a write protected disk when dealing with MMC cards ? I have a really odd problem with one of my cards (nicked from my camera to put some music on to my phone for a long tube journey in a couple of days) The card works fine in my camera, I can delete/format/take pictures all fine. Put into the card reader on my SuSE 9.1 box I issue a mount -rw /dev/sdd1 /home/wayne/media I can then see the current contents of the card by looking at /home/wayne/media Deleting anything causes no error, but the deletion is only temporary if I come out of and go back into the directory the files have reappeared. Unmounting it and doing a mkfs -t vfat /dev/sdd1 again results in no error, but after either mounting it or doing a fsck I can see that the original files are still there. If I use the camera to format it and then try and copy files to it (I now have an empty disk according to df) I get an error telling me it is read only. After a while I gave up and tried it in a Win2000 box downstairs. Copy to, Delete and format all give me a "disk is write protected" error. Yet the camera can still write to it. There are no write protect tabs on either the card or the reader, and I have tried other cards in the same reader on the same machines with no issues. However all my other cards are SD (which have little write lock switches on them) Is it possible that my camera is setting the write protect tab on this card when it formats it. and if so how do I use the card in anything else. What I am wanting to do is force a format regardless, is this possible ?

3 4

PostgrSQL row ID column
by Chris Green 12 Nov '04

12 Nov '04

Re: my comment earlier today about PostgrSQL databases and row indentification, here's the section of the Rekall manual I remembered:- For some databases, this is always possible. For instance, in PostgreSQL every row has associated with it a unique indeitifier called the oid, which can be retrieved along with the row data; after a new row is inserted, the oid of that row can be ascertained. Similarly, Oracle has a rownum. (... and that indeitifier typo isn't mine!) -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

2 2

Reminder: Norwich City Gate meeting Thursday 10th November!
by adam＠thebowery.co.uk 11 Nov '04

11 Nov '04

Begin boilerplate meeting reminder txt. ------------------------------------------------- As per usual, this Thursday (10th November) we'll be congregating in the City Gate from around 8pm. Adam, Brett, Kirsten and Jonathan should be there, and Eli might also make it. We should have a penguin in attendance so you know who we are, if not look for the Linux Magazines on the tables, sometimes we lurk at the far end of the building in the restaurant area. If you want to do gpg keysigning then at least a few of us should be there with sigs and id. Full details can be found at: http://www.alug.org.uk/meetings/2004/all-NorThu.html Directions at: http://www.alug.org.uk/venues/citygate.html Pictures from August of people so you know who to look out for: http://www.earth.li/~noodles/Photos/2004-08-ALUG/ ------------------------------------------------ Adam -- jabberid = quinophex(a)jabber.earth.li AFFS || http://www.affs.org.uk/ || Not a filesystem

4 3

Adding an auto column to an existing database
by Chris Green 11 Nov '04

11 Nov '04

If I add an 'auto' column to an existing database to provide a unique key (I don't believe this should really be necessary, see below) will it get automatically populated with a sequence of integers as required? Access can manage without a unique key so why can't any of the mysql front ends do without it? I had a look at my Access database and, although the rows as a whole are unique it turns out that I *don't* have a multi-column primary key to give a unique key. However the database copes quite happily with multiple purchases of stamps with identical descriptions and with pension payments of the same amount every month. It certainly works and I can delete one pension payment without affecting the others for example. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

5 9

[ALUG Announce][ALUG] Reminder: Norwich City Gate meeting Thursday 10th November!
by Brett Parker 11 Nov '04

11 Nov '04

ALUG Announce Please send announcements to Announce(a)lists.alug.org.uk Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce ----- As per usual, this Thursday (11th November) we'll be congregating in the City Gate from around 8pm. Adam, Brett, Kirsten and Jonathan should be there, and Eli might also make it. We should have a penguin in attendance so you know who we are, if not look for the Linux Magazines on the tables, sometimes we lurk at the far end of the building in the restaurant area. If you want to do gpg keysigning then at least a few of us should be there with sigs and id. Full details can be found at: http://www.alug.org.uk/meetings/2004/all-NorThu.html Directions at: http://www.alug.org.uk/venues/citygate.html Pictures from August of people so you know who to look out for: http://www.earth.li/~noodles/Photos/2004-08-ALUG/ -- Brett Parker web: http://www.sommitrealweird.co.uk/ email: iDunno(a)sommitrealweird.co.uk ----- Send discussion replies to main(a)lists.alug.org.uk (Reply-To set) Unsub, change settings via http://lists.alug.org.uk/mailman/listinfo/announce

1 0

Re: [ALUG] Skype is pretty good
by Wayne Stallwood 10 Nov '04

10 Nov '04

On Tuesday 09 November 2004 12:08 pm, you wrote: > On Mon, 2004-11-08 at 23:58 +0100, Wayne Stallwood wrote: > > But my headset is evil, it digs into my ears and the sound quality is > > terrible. > > I've ordered a Sennheiser PC 155 USB today. > I'll post my experiences to the list... > > -- Martijn oooh Sennheiser, I like their kit. Got a lovely pair of their headphones plugged into my Hi-Fi downstairs, fantastic things. Didn't realise that they made computer headsets though, I'll have to check out their stuff. Let us know what happens with the PC155's when you get them. Following on from the Plantronics post I mailed their customer support, I give them 3 points for responding in a day and 2 points for even knowing what Linux is. "Wayne, Currently Plantronics has no active driver development plan for Linux. The DSP headsets have been found to work as an audio input and output device under Linux if the kernel version is 2.4.2 or higher compiled with USB support. The inline volume controls and mute switch will not work however, since these would require a compatible HID driver for Linux." I have since mailed them back asking if they would ever consider opening up the device specs so that the community can sort out Linux support for them. I am guessing that I'll either get no response or "no" but hey it's worth a shot. What with skype seeming to get some sort market penetration and having Linux support surely the momentum for things such as this to be supported must be there now.

2 1

Online calender - any suggestions?
by Dave Briggs 10 Nov '04

10 Nov '04

I am looking for an online calender for a website, largely so that I can access it from any computer wherever I am. Has anyone had a happy experience of using any such system on their sites? Google and Sourceforge reveal there are quite a few about so I thought I would fish for some recommendations before going through them all myself. While I am just after a calendering system at the moment, anythingt that offers more features than that wouldn't be a problem. I have PHP and a MySQL database to spare, so that's no problem. Thanks Dave -- Dave Briggs http://www.palimpsest.org.uk - The Home of Informed Debate on the Web

3 3

Mandrake Community is not intended to be stable...
by syd 09 Nov '04

09 Nov '04

Brief reminder/warning/explanation re Mandrake Community editions: the 'community' editions are an ongoing beta-testing exercise by the mandrake community. This is s/w that is very likely to be broken. It is not going to give an stable mandrake install. Unless you really want to be part of a beta-testing exercise - including frequent large updates- don't use the Community edition - use the major 'Official' releases. Syd

7 6

Primary keys in mysql databases
by Chris Green 09 Nov '04

09 Nov '04

Lots of the GUI front ends for mysql that I have been looking at are very insistent that the tables one uses have a unique primary key to guarantee uniqueness. I don't quite understand why this is so necessary but this isn't particularly important at the moment as the database tables I've imported have unique rows. However neither mySql nor the front-ends seem to know about unique keys made up of more than one column, is this because such a thing is regarded as a rather advanced approach or because it's actually not possible in mySql? My unique key is the combination of date (the date of the transaction) and the transaction description. This means that there can be the same transaction on different dates, e.g. if I buy some stamps on two different dates the description can be 'Stamps' for both of them. The advantage is that I am protected against entering the same transaction twice by mistake as this will transgress the unique key requirement. If I really *did* buy stamps twice on the same day then I just put 'Stamps again' or some such. The oft recommended 'Auto' column seems a bad way to do things because it doesn't prevent duplicate entries, you could inadvertently put 'Fred Bloggs' into your address book lots of times, each with a different value in the auto 'ID' column. It really seems to me that the auto column approach is a quick and dirty bodge which loses some (most?) of the advantages of a unique key and wastes space as well. Joins between tables can just as easily be done on the 'real' values as on an articifial unique key. My transaction types are held in a separate table and the join to the transactions table (when needed) is simply done with the transaction type code. This again guarantess what one wants which is that one can't enter a non-existent transaction type in the transactions table. I can see no advantage at all in having an additional, numeric, ID column in the transaction types table. Have I missed something here or is it just that the documentation I'm looking at is trying to make things 'simple' and using an auto column is the 'simple' way to do it? -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

4 5

Re: [ALUG] Primary keys in mysql databases
by Richard Lewis 09 Nov '04

09 Nov '04

On Tue, 9 Nov 2004 13:04:49 +0000, "Chris Green" <chris(a)areti.co.uk> said: > Lots of the GUI front ends for mysql that I have been looking at are > very insistent that the tables one uses have a unique primary key to > guarantee uniqueness. > > I don't quite understand why this is so necessary but this isn't > particularly important at the moment as the database tables I've imported > have unique rows. > > However neither mySql nor the front-ends seem to know about unique > keys made up of more than one column, is this because such a thing is > regarded as a rather advanced approach or because it's actually not > possible in mySql? > > My unique key is the combination of date (the date of the > transaction) and the transaction description. This means that there > can be the same transaction on different dates, e.g. if I buy some > stamps on two different dates the description can be 'Stamps' for both > of them. The advantage is that I am protected against entering the > same transaction twice by mistake as this will transgress the unique > key requirement. If I really *did* buy stamps twice on the same day > then I just put 'Stamps again' or some such. > You can do this: CREATE TABLE transactions (date DATE, description TEXT, ...) PRIMARY KEY(date, description) but this will still be true: SELECT date, description FROM transactions WHERE (date=...) AND (description=...) and as for updates, well you handle them using your wrapper code: e.g. in PHP $result = mysql_query("SELECT trans_id FROM transactions WHERE date=" . $_GET['date'] . " AND description LIKE " . $_GET['description'] . " LIMIT 1"); if mysql_num_rows($result) = 0 { mysql_query("INSERT transactions (date, description) VALUES (\"" . $_GET['date'] . "\", \"" . . $_GET['description'] . "\""); } else { echo "<p class=\"error\">Transaction not unique!</p>\n"; } > The oft recommended 'Auto' column seems a bad way to do things because > it doesn't prevent duplicate entries, you could inadvertently put 'Fred > Bloggs' into your address book lots of times, each with a different > value in the auto 'ID' column. It really seems to me that the auto > column approach is a quick and dirty bodge which loses some (most?) of > the advantages of a unique key and wastes space as well. > I think the point of having a field like: CREATE TABLE table (id INT NOT NULL PRIMARY KEY auto_increment, ...) is for when the other fields of your table are not necessarily going to be unique. Like an address book: none of the fields name, address, postcode, telephone etc. *have* to be unique, so an automatic unique, key column is useful, even though the value in it is unrelated to the data itself. It allows you to say things like: SELECT date, subject FROM messages, address_book WHERE (name LIKE "%smith%") AND (messages.from_id=address_book.record_id) > Joins between tables can just as easily be done on the 'real' values > as on an articifial unique key. My transaction types are held in a > separate table and the join to the transactions table (when needed) is > simply done with the transaction type code. This again guarantess what > one wants which is that one can't enter a non-existent transaction type > in the transactions table. I can see no advantage at all in having an > additional, numeric, ID column in the transaction types table. > Yes, if you can guarantee uniqueness in some meaningful field then you don't use an auto_increment field as primary key. > Have I missed something here or is it just that the documentation I'm > looking at is trying to make things 'simple' and using an auto column > is the 'simple' way to do it? > Richard -- Richard Lewis richardlewis(a)fastmail.co.uk

1 0