Re: altering data structure
Il mar, 2002-12-31 alle 12:53, David T-G ha scritto: Well, yeah, but I want to try to write portable code; just because I like mysql doesn't meant that my customer (or some future contract employer) will... Hi, I am reading a bit late, but let me suggest you Metabase. Metabase does a very good job expecially dealing with db schemas. Metabase consists of a set of PHP classes to access and manage databases using an API that assures DBMS independence. http://freshmeat.net/projects/metabase/ I am using it for my free software project, so if you want more insight you're welcome. Here is my project XML Metabase schema: http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/obliquid/obliquid/common/configs/ Here is the same schema after an XSLT transform http://demo.obliquid.com/index.php?page=core_dbdoc This is the project home page by the way http://dev.obliquid.com/ bye, Stefano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi again. And again :-) % % On Sun 2002-12-29 at 15:21:33 -0500, [EMAIL PROTECTED] wrote: % ...and then Benjamin Pflugmann said... % [...] % % Of course, that's only possible if you can live with being % % MySQL-specific. % % Hmmm... so it's a mysql thing and not a SQL thing. Well, I'd like to % avoid that, even though I like mysql... % % Well, setting up a database (or updating it) is always very % vendor-specific, isn't it? Well, yeah, but I want to try to write portable code; just because I like mysql doesn't meant that my customer (or some future contract employer) will... % % % Besides, I have yet to see production-level SQL that works on several % databases without change (except for stuff that is developed that That's my general goal, though I hardly know enough to know what's specific versus standard [yet]. % way). The problem is that the SQL standard is too unspecific in some Ahhh... % areas and the vendors have to fill in the details and it becomes a % PITA to work without the vendor-specific features. Yeah. Well, mysql, postgresql, and oracle are enough for me; now I only have to become expert, or even fluent, in three. Ha ha :-) % % IMHO, the most common example is the lack of sequence support. % AUTO_INCREMENT is MySQL-specific. To be portable you have to simulate Oops! :-) % sequences yourself, which is possible, but you won't do except if you % *know* you will need to run on a different RDBMS later. Yeah. Well, or if you plan to be able to run on one later... % % More info here: % % http://www.mysql.com/doc/en/Compatibility.html % % % Thanks HAND HH % % % Hehe. I wondered about the non-abbriviation in your first mail % already. ;-) *grin* I figure the first one in the thread will define it for later since it's not too standard... :-) % % HTH, % % Benjamin. % % % -- % [EMAIL PROTECTED] Thanks again HAND HH mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EYVJGb7uCXufRwARAuwYAKDCffmOYnUM2G81hel5l9vLOoShqwCeOhzz /sb2qzOkmS0TYBUCwsSFW3E= =/oTU -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
Hi again. On Sun 2002-12-29 at 15:21:33 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] % Of course, that's only possible if you can live with being % MySQL-specific. Hmmm... so it's a mysql thing and not a SQL thing. Well, I'd like to avoid that, even though I like mysql... Well, setting up a database (or updating it) is always very vendor-specific, isn't it? Besides, I have yet to see production-level SQL that works on several databases without change (except for stuff that is developed that way). The problem is that the SQL standard is too unspecific in some areas and the vendors have to fill in the details and it becomes a PITA to work without the vendor-specific features. IMHO, the most common example is the lack of sequence support. AUTO_INCREMENT is MySQL-specific. To be portable you have to simulate sequences yourself, which is possible, but you won't do except if you *know* you will need to run on a different RDBMS later. More info here: http://www.mysql.com/doc/en/Compatibility.html Thanks HAND HH Hehe. I wondered about the non-abbriviation in your first mail already. ;-) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
On Sat 2002-12-28 at 05:05:45 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] % If you choose to go the SQL route, have a look at TEMPORARY TABLES, % CREATE TABLE ... SELECT and INSERT ... SELECT. Don't forget that you You mean a CREATE TABLE command that has SELECT as part of it, and an INSERT command that has SELECT as part of it? Yes and no. Yes, it is a INSERT with SELECT. No, if I consider what you write below. I did not mean sub-selects or derived tables. Actually I wanted to know how to do the latter but didn't think it could be done! Briefly: create table ccards ( # ID number id smallint not null default 0 auto_increment primary key , ... hash tinyblob # hash of the card: have we seen this one before? ) ; create table clientcards ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id card smallint , # references ccards.id (but must be disconnected) type smallint , # references ccardtypes.id ### need this here? hash tinyblob # references ccards.hash (but must be disconnected) ) ; ... insert into ccards (type,name,number,expdate) values ( '1' , 'david thorburn-gundlach' , '1234 5678 9abc def0' , '2003-06-00') ; update ccards set hash = md5(number) where id = last_insert_id() ; insert into clientcards values ( '' , '1' , '1' , '1' , select ccards.hash where ccards.id = last_insert_id() ) ; That would be something like a derived table, which are only supported since v4.1 (not considering the fact that the FROM clause is missing :-). The INSERT ... SELECT which I referred to is a special syntax which is supported since quite a while (in v3.23 for sure): http://www.mysql.com/doc/en/INSERT_SELECT.html http://www.mysql.com/doc/en/ANSI_diff_SELECT_INTO_TABLE.html In your case it would be something like INSERT INTO clientcards SELECT '', 1, 1, 1, ccards.hash FROM ccard WHERE ccards.id = LAST_INSERT_ID() And you have variables. If the above wouldn't work you could write: SELECT @card_hash := hash FROM ccard WHERE ccards.id = LAST_INSERT_ID(); INSERT INTO clientcards VALUES ( '' , 1, 1, 1, @card_hash ); Of course, that's only possible if you can live with being MySQL-specific. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- Thanks for all of the help and info. Lots to learn and read :-) ...and then Benjamin Pflugmann said... % ... % % And you have variables. If the above wouldn't work you could write: % % SELECT @card_hash := hash FROM ccard WHERE ccards.id = LAST_INSERT_ID(); % INSERT INTO clientcards VALUES ( '' , 1, 1, 1, @card_hash ); Aha! This is exactly what I sought. So that's how it's done :-) Again, in the specific case in mind I'll be entering the single number and can easily stuff the md5 sum [which I'll calculate just as easily as mysqld would] into both tables, but ... % % % Of course, that's only possible if you can live with being % MySQL-specific. Hmmm... so it's a mysql thing and not a SQL thing. Well, I'd like to avoid that, even though I like mysql... % % HTH, % % Benjamin. % % % -- % [EMAIL PROTECTED] Thanks HAND HH :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+D1lMGb7uCXufRwARAtDZAJ97FHKovuMjDmRUuEkKX4whVYXhHQCfWowu oIN0lJ56xASB3HZgvGgN3T4= =YF2l -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi. Hi again! % % On Fri 2002-12-27 at 20:37:58 -0500, [EMAIL PROTECTED] wrote: % Hi, all -- % % What's the best way to change your database schema around if you have % data in it? How do you know when to simply ALTER TABLE and when to dump % the whole thing (perhaps doing specific SELECTs to spit out the data % you'd like to have in the end to be processed by some script that will % make the new table structure)? % % Well, I guess it depends. I would start with making a backup. :-) Of course :-) % % Personally, I prefer to change the database with ALTER TABLE and % UPDATEs, because I can get immediate feedback and I am comfortable Hmmm... OK. % enough with writing such SQL queries from head. Do not forget to save % the SQL commands in a file in case you have to start from scratch % (backup). Makes sense. My inclination is to script it anyway, though for a real one-time change maybe not. Maybe we'd want to apply the changes to a restored copy from before tonight's new backup, for instance... % % I suppose if you are quite comfortable with e.g. Perl and/or the % changes are going to be complex, writing a script is the better idea, I don't know how complex things have to be before they're complex enough for a script, but it sounds like it's possible. OK; so maybe one needs a script. But, meanwhile, you can do this just within the database? Wow. % because it is more forgiving if you make errors. OTOH, you have a % bigger overhead, depending on how well you know your scripting % language. Fairly well -- and I could always stand to learn more :-) % % I don't have a good example, but suppose I have a few tables with some % fields each and I realize that I should make a new table with data from % some columns of some tables. What to do? % % If you choose to go the SQL route, have a look at TEMPORARY TABLES, % CREATE TABLE ... SELECT and INSERT ... SELECT. Don't forget that you You mean a CREATE TABLE command that has SELECT as part of it, and an INSERT command that has SELECT as part of it? Actually I wanted to know how to do the latter but didn't think it could be done! Briefly: create table ccards ( # ID number id smallint not null default 0 auto_increment primary key , ... hash tinyblob # hash of the card: have we seen this one before? ) ; create table clientcards ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id card smallint , # references ccards.id (but must be disconnected) type smallint , # references ccardtypes.id ### need this here? hash tinyblob # references ccards.hash (but must be disconnected) ) ; ... insert into ccards (type,name,number,expdate) values ( '1' , 'david thorburn-gundlach' , '1234 5678 9abc def0' , '2003-06-00') ; update ccards set hash = md5(number) where id = last_insert_id() ; insert into clientcards values ( '' , '1' , '1' , '1' , select ccards.hash where ccards.id = last_insert_id() ) ; Everything up to the clientcards insert works just fine. How can I insert into clientcards the info including the hash from ccards? [I'm just doing this for the testing phase, since not only will the ccards table be locked away in a real database but I'll be inserting the values one at a time from my front end where I'll calculate the md5 sum myself, but it works as an example.] % can test with the SELECT and only add CREATE TABLE / INSERT part at % the end. And LIMIT is your friend. OK :-) % % HTH, % % Benjamin. % % -- % [EMAIL PROTECTED] Thanks HAND Happy Holidays :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+DXd5Gb7uCXufRwARAmtVAJ4i8dpmQz8yd/okzgTLdwXLn1HyiwCggIts AOGgeFdZYnxRWLmgF6FDmVE= =JheJ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
David, I don't have a good example, but suppose I have a few tables with some fields each and I realize that I should make a new table with data from some columns of some tables. What to do? Do you want to CREATE TABLE tbl_new SELECT (col1, col2, ...) FROM tbl_old? Or do I miss something which would make it a more complicated task? If you don't want all rows from the source table you can use WHERE and/or LIMIT. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Sent: Saturday, December 28, 2002 2:37 AM Subject: altering data structure -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- What's the best way to change your database schema around if you have data in it? How do you know when to simply ALTER TABLE and when to dump the whole thing (perhaps doing specific SELECTs to spit out the data you'd like to have in the end to be processed by some script that will make the new table structure)? I don't have a good example, but suppose I have a few tables with some fields each and I realize that I should make a new table with data from some columns of some tables. What to do? TIA HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+DQB2Gb7uCXufRwARAoiYAJ9ImPYzudn5iVy6a2VaxF9hZpT0QQCgm5A8 V9U37FV1f/Xk6VVRbPa8A9I= =XKu3 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
