Re: OT: MySQL question
Eli Kara wrote: firebird is developed under Unix, and have Unix,Linux,Windows,SunOS (Solrais -> also a unix) ports that work the same way on all of them. When I was commenting on the lack of Win32 support I was referring to PostgreSQL which is in beta for Win32 (running it under CYGWIN is not nearly close to being as easy as a MySQL Win32 installation which setups the libraries automatically). Luckily it handles UNICODE and other encodings really well out of the box, as opposed to MySQL :-) firebird support many charset including unicode... And you can define each (var)char field to have it's own charset... Yes, I know :) Now i like firebird because it's been a very long time out-there, you have java, delphi,freepascal,c, c++, php, perl support for it, and it's was made by people who worked with interbase and like it, and convinced Borland to open interbase to community (version 6.1 of interbase was open, and firebird is based on it). The database iteself is very fast and light (if you want more functions inside an sql, you need to use an extention). The licence give you ability to work in commercial enviorment (unlike MySQL), and you can tweak it (if you understand what you are doing offcurse) per record, table and database, witch make it very flexible, and strong soted. It compete on the market of large amount of data (billions of records), you can divide the database itself acording to the database size/amount of records.. witch make it easier to backup your data... You can update the database to new version with two console command only... And thats for start :) I never worked with PostgreSQL, althou i know it's very good... But i'm also known to be un othordox with my proffesional choises... i usualy peak the one that is good for my work rather then popular... usualy it prove itself with the result and time spent on a project... Ido Firebird seems very nice. I have installed it but it still required me to pick my brain about certain operations. The reason I was mentioning Win32 support at all was because I was recently looking for an SQL database that has native Win32 support and can store unicode characters (for hebrew support). PostgreSQL would have been my choice were it not for the annoying installation along with CYGWIN. MySQL is more problematic as it has a *slight* license problem, so I limited myself to version 3.23.58 (which requires an explicit command line argument to start the server in a different encoding). Firebird was nice but I was too annoyed by it's interface, sorry :) In the end I think I'll either go with MySQL or FB, I haven't decided yet. It will also depend on the APIs available and how easy they are to use. Ok, enough ranting.. Eli If you need any help with FB you can email me (well everyone on this list :) ), i had some work to make it more hebrew sutable (like demaon that speaks hebrew ;)) but they added also a russion fork of the project and chainged everything (the version of 1.5x is uses also the russion fork), so for now the work i did was for nothing.. :( Anyway.. again if you need help you can contact me about it :) Cheers Ido -- Experience is that marvelous thing that enables you to recognize a mistake when you make it again. -- F. P. Jones = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
> > firebird is developed under Unix, and have Unix,Linux,Windows,SunOS > (Solrais -> also a unix) ports that work the same way on all of them. > When I was commenting on the lack of Win32 support I was referring to PostgreSQL which is in beta for Win32 (running it under CYGWIN is not nearly close to being as easy as a MySQL Win32 installation which setups the libraries automatically). > > Luckily it handles UNICODE and other encodings really well out of the > > box, as opposed to MySQL :-) > > firebird support many charset including unicode... And you can define each > (var)char field to have it's own charset... Yes, I know :) > > Now i like firebird because it's been a very long time out-there, you have > java, delphi,freepascal,c, c++, php, perl support for it, and it's was made > by people who worked with interbase and like it, and convinced Borland to > open interbase to community (version 6.1 of interbase was open, and > firebird is based on it). The database iteself is very fast and light (if > you want more functions inside an sql, you need to use an extention). The > licence give you ability to work in commercial enviorment (unlike MySQL), > and you can tweak it (if you understand what you are doing offcurse) per > record, table and database, witch make it very flexible, and strong soted. > It compete on the market of large amount of data (billions of records), you > can divide the database itself acording to the database size/amount of > records.. witch make it easier to backup your data... You can update the > database to new version with two console command only... > > And thats for start :) > I never worked with PostgreSQL, althou i know it's very good... But i'm > also known to be un othordox with my proffesional choises... i usualy peak > the one that is good for my work rather then popular... usualy it prove > itself with the result and time spent on a project... > > Ido Firebird seems very nice. I have installed it but it still required me to pick my brain about certain operations. The reason I was mentioning Win32 support at all was because I was recently looking for an SQL database that has native Win32 support and can store unicode characters (for hebrew support). PostgreSQL would have been my choice were it not for the annoying installation along with CYGWIN. MySQL is more problematic as it has a *slight* license problem, so I limited myself to version 3.23.58 (which requires an explicit command line argument to start the server in a different encoding). Firebird was nice but I was too annoyed by it's interface, sorry :) In the end I think I'll either go with MySQL or FB, I haven't decided yet. It will also depend on the APIs available and how easy they are to use. Ok, enough ranting.. Eli -- Eli Kara Beyond Security Ltd. http://www.beyondsecurity.com/ http://www.securiteam.com/ The First Integrated Network and Web Application Vulnerability Scanner: http://www.beyondsecurity.com/webscan-wp.pdf = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Voguemaster wrote: PostgresQL seems to have something like this, see: http://www.sql.org/sql-database/postgresql/manual/arrays.html (for those who need yet another reason to switch :-) ) Interbase/Firebird (firebird is open source based on interbase) also have arrays. It's very fast ad very light, if you need extra things you can search an already made extentions or create your own... Yes, PostgreSQL is a fine DB and there is every good reason to switch :-) It only suffers from two main problems that I can see: 1. It's annoying to create a table with an auto-incremented field (i.e: serial int) For some reason you have to issue 4 commands... go figure their design decision. In Interbase you can impliment it as a trigger... you do not need to start massing things very much... Also stored procedure increase your speed of making things and give you the result as it where a table. 2. It still lacks a native Win32 port (for those who need it). This is a big one as it is currently only available for Unix/Linux. firebird is developed under Unix, and have Unix,Linux,Windows,SunOS (Solrais -> also a unix) ports that work the same way on all of them. Luckily it handles UNICODE and other encodings really well out of the box, as opposed to MySQL :-) firebird support many charset including unicode... And you can define each (var)char field to have it's own charset... Eli Now i like firebird because it's been a very long time out-there, you have java, delphi,freepascal,c, c++, php, perl support for it, and it's was made by people who worked with interbase and like it, and convinced Borland to open interbase to community (version 6.1 of interbase was open, and firebird is based on it). The database iteself is very fast and light (if you want more functions inside an sql, you need to use an extention). The licence give you ability to work in commercial enviorment (unlike MySQL), and you can tweak it (if you understand what you are doing offcurse) per record, table and database, witch make it very flexible, and strong soted. It compete on the market of large amount of data (billions of records), you can divide the database itself acording to the database size/amount of records.. witch make it easier to backup your data... You can update the database to new version with two console command only... And thats for start :) I never worked with PostgreSQL, althou i know it's very good... But i'm also known to be un othordox with my proffesional choises... i usualy peak the one that is good for my work rather then popular... usualy it prove itself with the result and time spent on a project... Ido -- Some mornings, it's just not worth chewing through the leather straps. = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Eli Kara wrote:
create table foo ( serial bar primary key, ...
That does NOT work on my PostgreSQL 7.4.3. What DOES work is:
(assuming you have table foo)
ALTER TABLE foo ADD COLUMN id int;// create an 'id' field, integer
CREATE SEQUENCE foo_id_seq; // create an auto-incrementing seq.
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq');
// set the default value of 'id' to be the next value in the sequence
UPDATE foo SET id = nextval('foo_id_seq'); // update existing 'id' column
Am I missing some simpler way ? I remember googling about this for quite some
time and that is the solution I found.
Regards,
Eli
sun=# create table foo ( bar serial primary key );
NOTICE: CREATE TABLE will create implicit sequence "foo_bar_seq" for
"serial" column "foo.bar"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
Now, if you want to add a serial retroactively, then you will have to do
what you mention. This has to do with the fact that you cannot add a
column and set a default value to it in one go, and the "Serial" type is
really just syntactic sugar to what you want to do.
Then again, this is just syntactic sugar to a rather uncommon operation.
Is that really such a problem?
Shachar
P.S.
I prefer to be CCed on messages sent to the list.
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/
=
To unsubscribe, send mail to [EMAIL PROTECTED] with
the word "unsubscribe" in the message body, e.g., run the command
echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
> create table foo ( serial bar primary key, ...
>
That does NOT work on my PostgreSQL 7.4.3. What DOES work is:
(assuming you have table foo)
ALTER TABLE foo ADD COLUMN id int;// create an 'id' field, integer
CREATE SEQUENCE foo_id_seq; // create an auto-incrementing seq.
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq');
// set the default value of 'id' to be the next value in the sequence
UPDATE foo SET id = nextval('foo_id_seq'); // update existing 'id' column
Am I missing some simpler way ? I remember googling about this for quite some
time and that is the solution I found.
Regards,
Eli
--
Eli Kara
Beyond Security Ltd.
http://www.beyondsecurity.com/
http://www.securiteam.com/
The First Integrated Network and Web Application Vulnerability Scanner:
http://www.beyondsecurity.com/webscan-wp.pdf
=
To unsubscribe, send mail to [EMAIL PROTECTED] with
the word "unsubscribe" in the message body, e.g., run the command
echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Voguemaster wrote: Yes, PostgreSQL is a fine DB and there is every good reason to switch :-) It only suffers from two main problems that I can see: 1. It's annoying to create a table with an auto-incremented field (i.e: serial int) For some reason you have to issue 4 commands... go figure their design decision. create table foo ( serial bar primary key, ... What are you talking about? 2. It still lacks a native Win32 port (for those who need it). This is a big one as it is currently only available for Unix/Linux. Beta now available: http://www.postgresql.org/news/216.html Luckily it handles UNICODE and other encodings really well out of the box, as opposed to MySQL :-) Eli Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
PostgresQL seems to have something like this, see: http://www.sql.org/sql-database/postgresql/manual/arrays.html (for those who need yet another reason to switch :-) ) Yes, PostgreSQL is a fine DB and there is every good reason to switch :-) It only suffers from two main problems that I can see: 1. It's annoying to create a table with an auto-incremented field (i.e: serial int) For some reason you have to issue 4 commands... go figure their design decision. 2. It still lacks a native Win32 port (for those who need it). This is a big one as it is currently only available for Unix/Linux. Luckily it handles UNICODE and other encodings really well out of the box, as opposed to MySQL :-) Eli -- Eli Kara Beyond Security Ltd. http://www.beyondsecurity.com/ http://www.securiteam.com/ The First Integrated Network and Web Application Vulnerability Scanner: http://www.beyondsecurity.com/webscan-wp.pdf = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Omer Zak wrote: Daniel Feiglin wrote: Hello folks! I have a problem with SQL for MySQL (and pre4sumably any other kind): Can anone offer a RTFM do do something like this: create table foo ( booint, bar varchar; baz array[0..9] of int, # illegal, but needed! ... ); I'm not really thrilled about stuffing all the data into a blob or a varchar and using c/c++/whatever text handling. Some questions: 1. Do you always have exactly 10 integers in baz? 2. Does their order in baz matter? 3. Is a value in baz constrained to appear exactly once? At any case: Why not normalize the table into two tables (I am making here the guess that boo is the only key): create table foo_bar ( booint,# This is the key bar varchar); create table foo_baz ( booint,# This is the key baz int); Under Postgres (other databases that are not MySQL have similar constructs), this should really be: create table foo_bar ( boo serial unique, -- This is the key bar varchar ); create table foo_baz ( boo integer references foo_bar(boo), baz int ); This makes foo_baz.boo a foreign key poining at foo_bar.boo. This automatically means: - You can't create an entry in foo_baz that references a non-existing boo entry in foo_bar. - You can't delete an entry from foo_bar if it has entries referencing it in foo_baz If you want to list all the entries, just do: select foo_bar.boo, bar, baz from foo_bar left join foo_baz on foo_bar.boo=foo_baz.boo; Of course, if you want the database to enforce stuff for you, you can add that. For example, if it's not allowed to have two values in the "array" the same, you add a constraint on foo_baz that says that the pair boo, baz has to be unique. Likewise, if you want ordering, and want to enforce no more than ten, say that foo_baz has a field "index" of type int, and put a constraint in that says that boo, index must be unique, and that index is only allowed the range 1-10 (0-9). Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Ido Kanner wrote: Hi, I guess no one will like my suggustion, but here it goes: Use Firebird (http://www.firebirdsql.org), it does support arrays, but to use them you need to write things like stored-procedures or UDF (an extention). Hello folks! I have a problem with SQL for MySQL (and pre4sumably any other kind): Can anone offer a RTFM do do something like this: create table foo ( boo int, bar varchar; baz array[0..9] of int, # illegal, but needed! ... ); I'm not really thrilled about stuffing all the data into a blob or a varchar and using c/c++/whatever text handling. Thanks, Daniel Ido First thanks for the replies. Next, each record consists of some constant stuff (key if you like) followed by a list of integers, say the results of a set of measurements. My current "test" setup produces comma delimited text transaction files - which of course are not very write-sharable and most unsuitable in a web server backend. I'd like to stick with MySQL/PHP/perl etc. My conclusion from the answers received is indeed to produce a text string of the form :n1,n2,ne, ...n and use the ususal C/C++/Perl/whatever text goodies to pack and unpack. Aother neat dirty trick might be to tale advantage of the fact that my integers are small (<100) and pack each number into two bytes like we used to do in the bad old days of limited memory and disk space ... Shabbat shalom, Daniel = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Daniel Feiglin wrote: Hello folks! I have a problem with SQL for MySQL (and pre4sumably any other kind): Can anone offer a RTFM do do something like this: create table foo ( booint, bar varchar; baz array[0..9] of int, # illegal, but needed! ... ); I'm not really thrilled about stuffing all the data into a blob or a varchar and using c/c++/whatever text handling. Apparently (from my old SQL book), arrays other than of characters, are not supported in standard SQL. PostgresQL seems to have something like this, see: http://www.sql.org/sql-database/postgresql/manual/arrays.html (for those who need yet another reason to switch :-) ) I couldn't find an equivalent of this in MySQL. Another option would be to split this array field into a separate table (multiple records associated with table "foo" via a foreign key + "array index" field). I suspect that this is an example of what's called "normalization" in database design parlance. The benefits I see for this are: 1. Unused "array entries" are not allocated. 2. You can take advantage of smart SQL queries to manipulate the array (e.g. sum, count, max, min, average, sort, compare with "arrays" of other records, etc...) 3. It's portable across RDBMS implementations. HTH, --Amos = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: OT: MySQL question
Daniel Feiglin wrote: Hello folks! I have a problem with SQL for MySQL (and pre4sumably any other kind): Can anone offer a RTFM do do something like this: create table foo ( booint, bar varchar; baz array[0..9] of int, # illegal, but needed! ... ); I'm not really thrilled about stuffing all the data into a blob or a varchar and using c/c++/whatever text handling. Some questions: 1. Do you always have exactly 10 integers in baz? 2. Does their order in baz matter? 3. Is a value in baz constrained to appear exactly once? At any case: Why not normalize the table into two tables (I am making here the guess that boo is the only key): create table foo_bar ( booint,# This is the key bar varchar); create table foo_baz ( booint,# This is the key baz int); If duplicates are allowed in baz, then add to foo_baz another field: baz_count int which will count the number of appearances of the value in question. But if their order is important, add: serialint which will keep track of the order of the values. --- Omer My own blog is at http://www.livejournal.com/users/tddpirate/ My opinions, as expressed in this E-mail message, are mine alone. They do not represent the official policy of any organization with which I may be affiliated in any way. WARNING TO SPAMMERS: at http://www.zak.co.il/spamwarning.html = To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
