*** withdefault has left #ingres | 00:17 | |
*** grantc has joined #ingres | 00:26 | |
*** withdefault has joined #ingres | 00:54 | |
*** ChanServ sets mode: +o withdefault | 00:54 | |
*** KermitTheFragger has joined #ingres | 01:31 | |
KermitTheFragger | hi all | 01:33 |
---|---|---|
KermitTheFragger | stupid question perhaps; but the ingres guide says a clob holds up to 2 GB | 01:33 |
grantc | KermitTheFragger, that is correct | 01:33 |
KermitTheFragger | so when using UTF8, would that mean the CLOB can hold a max of 2097152 characters ? | 01:33 |
KermitTheFragger | no wait 2147483648 chars | 01:34 |
KermitTheFragger | arrgh i shouldnt do this stuff so early :) | 01:35 |
grantc | it all depends on how many bytes you have per utf-8 char | 01:35 |
KermitTheFragger | one utf8 char is one byte, right ? | 01:35 |
grantc | i believe it can go up to 4 or 6 (for more esoteric characters/codepoints) bytes per char | 01:35 |
grantc | err no | 01:35 |
grantc | if you only deal with ascii chars then yes :) | 01:36 |
KermitTheFragger | hm yeah i see your point | 01:38 |
KermitTheFragger | so the max for a character would be 4 bytes | 01:38 |
KermitTheFragger | (its says in the wiki it can have a max of 4 octets) | 01:38 |
grantc | or 6 but yes | 01:39 |
KermitTheFragger | are you sure about 6 grantc ? | 01:40 |
grantc | what languages are you planning on dealing with? | 01:41 |
KermitTheFragger | the spec says In UTF-8, characters from the U+0000..U+10FFFF range (the UTF-16 accessible range) are encoded using sequences of 1 to 4 octets. | 01:41 |
grantc | i think so - perhaps it was an extension | 01:41 |
KermitTheFragger | well im implementing a Ingres Database adapter for an JDO implementation (DataNucleus) | 01:41 |
KermitTheFragger | so therefor i needed to know some bounds | 01:42 |
KermitTheFragger | ...dead silence.... ;-) | 01:43 |
grantc | why not store in nclob ? | 01:44 |
grantc | UTF-16 | 01:44 |
grantc | since Java is UTF-16 anyway | 01:44 |
KermitTheFragger | there is :) | 01:45 |
KermitTheFragger | im just also implementing the mapping to jdbc clob | 01:45 |
KermitTheFragger | just being thorough :) | 01:46 |
grantc | fair enough - I would imagine 4 bytes per code point would be enough | 01:46 |
KermitTheFragger | yeah that should do the trick nicely. Thanks for the insights! | 01:47 |
*** bonsaikitten has joined #ingres | 01:49 | |
grantc | no problem | 01:50 |
*** cthibert has joined #ingres | 03:16 | |
*** Alex| has joined #ingres | 04:02 | |
*** ChanServ sets mode: +o Alex| | 04:02 | |
*** grantc has quit IRC | 04:43 | |
*** zxiiro has quit IRC | 04:55 | |
*** atrofast has quit IRC | 04:57 | |
*** atrofast has joined #ingres | 05:00 | |
*** zxiiro has joined #ingres | 05:39 | |
*** ChanServ sets mode: +v zxiiro | 05:39 | |
*** rossand has joined #ingres | 05:57 | |
*** ChanServ sets mode: +o rossand | 05:57 | |
*** PaulM05 has joined #ingres | 06:08 | |
withdefault | Hello, I'd like to pose a question to this gathering | 06:11 |
withdefault | consider CREATE TABLE t (c1 INTEGER, c2 INTEGER, c3 VARCHAR(10), PRIMARY KEY (c1, c2)); | 06:11 |
withdefault | what would you expect the result to be? | 06:11 |
PaulM05 | I could answer that but since I have an issue on it I'm probably biased ;) let's see what others think first | 06:13 |
toumi01 | is the issue that a nullable key is being defined? | 06:14 |
PaulM05 | no - it's to do with table_auto_structure | 06:14 |
withdefault | toumi01: you're right about nullable key being defined too | 06:15 |
toumi01 | and what's the issue with auto structure? | 06:16 |
toumi01 | do folks want this to be a heap??? | 06:16 |
PaulM05 | if auto structure is on then it creates the constraint with base table structure as the index - but that doesn't survive a copy out/copy in - you end up with a heap with 2ndry index in the new db | 06:17 |
toumi01 | I would have expected btree with a composite key | 06:17 |
toumi01 | guess I never looked at the auto strucuture details | 06:18 |
PaulM05 | yes that's what the create table does | 06:18 |
toumi01 | called away to breakfast - carry on! ;) | 06:19 |
PaulM05 | ok - you're right - I meant btree with 2ndry | 06:19 |
withdefault | but does the copy work if you apply the base table structure outside of the create table? | 06:19 |
PaulM05 | yes | 06:19 |
withdefault | so there's a flag for the alter table | 06:20 |
withdefault | statement that's not there when it's created under the covers | 06:20 |
PaulM05 | yes | 06:21 |
PaulM05 | I'm still trying to figure how and where it gets set | 06:22 |
withdefault | probably written into the catalogs as part of constraint | 06:27 |
PaulM05 | yes that's where I'm looking at the moment | 06:28 |
withdefault | my create table statement doesn't get as far as creating the table. The c1 and c2 columns are nullable and the dbms server kicks out the statement because nullable columns can't be part of a pk or unique constraint | 06:29 |
PaulM05 | ah - that's not good | 06:30 |
PaulM05 | it should either give a syntax error or make it non-nullable for you | 06:31 |
withdefault | right, in the SQL standard for a table constraint like a primary or foreign key in a table definition columns of not null are implied if the clause isn't specified | 06:32 |
withdefault | which option makes more sense? the syntax error or the we'll make a better choice for you? | 06:34 |
PaulM05 | well... I've had this discussion the other day about coercion of datatypes. I think we should avoid making clever choices for people but flag it up with an error and force them to be explicit. Others take a view that that's not user-friendly, or (more relevantly) not migration-friendly | 06:35 |
PaulM05 | what do other DBMSs do? | 06:36 |
*** mull_ has joined #ingres | 06:38 | |
PaulM05 | brb | 06:38 |
*** Dejan has joined #ingres | 06:44 | |
Dejan | hi everyone | 06:47 |
withdefault | hello | 06:48 |
PaulM05 | hi Dejan | 06:50 |
PaulM05 | I just spoke with Keith B in the migration team and he tells me that Oracle and MySQL allow primary/unique keys to have nullable columns! | 06:51 |
PaulM05 | he also said he thought we should be forcing the non-nullability of the column | 06:52 |
Dejan | PaulM05: i have Ingres databases here with tables with nullable primary keys | 06:53 |
PaulM05 | really? | 06:53 |
PaulM05 | what version? | 06:53 |
withdefault | MySQL doesn't allow nullable columns in pk, it switches the column to not null | 06:53 |
Dejan | PaulM05: do not know exactly, i think it is 2.6 | 06:54 |
PaulM05 | ok | 06:54 |
Dejan | i am working on migration of those old databases to new one | 06:54 |
PaulM05 | I think withdefault was saying that's not correct/current behaviour | 06:54 |
Dejan | it is easy to check | 06:55 |
PaulM05 | well I've not actually tested it myself | 06:56 |
PaulM05 | I think I'm in danger of becoming an unreliable middle-man here! | 06:56 |
Dejan | MariaDB changes into not null | 06:57 |
Dejan | just like that | 06:57 |
Dejan | lol | 06:57 |
* withdefault wanders off to a meeting | 06:59 | |
Dejan | http://codepad.org/DoXjaHio | 06:59 |
PaulM05 | withdefault was saying that that's what the SQL Standard specifies | 07:00 |
Dejan | so SQL standard actually allows nullable PKs ? | 07:01 |
PaulM05 | no the SQL standard says you should make the column non-nullable (if it wasn't specified) | 07:26 |
PaulM05 | e.g. CREATE TABLE t (c1 INTEGER, c2 INTEGER, c3 VARCHAR(10), PRIMARY KEY (c1, c2)); - c1 and c2 should be made non-null | 07:27 |
*** PaulM05 is now known as PaulM05_mtg | 07:56 | |
Dejan | PaulM05_mtg: yeah, that is what MariaDB does atm | 08:11 |
Dejan | I use latest RC | 08:11 |
Dejan | no idea what Oracle's MySQL does | 08:12 |
Dejan | should be the same i think | 08:12 |
*** Mud has quit IRC | 08:21 | |
*** grantc has joined #ingres | 08:35 | |
*** Mud has joined #ingres | 08:48 | |
*** atrofast has quit IRC | 08:52 | |
*** atrofast has joined #ingres | 08:55 | |
*** PaulM05_mtg has quit IRC | 09:33 | |
*** Alex| has quit IRC | 09:53 | |
*** Dejan has quit IRC | 10:01 | |
*** cthibert has left #ingres | 10:08 | |
*** cthibert has joined #ingres | 10:15 | |
*** KermitTheFragger has quit IRC | 12:11 | |
*** rossand has quit IRC | 13:04 | |
*** cthibert has left #ingres | 13:13 | |
*** Mud has quit IRC | 13:34 | |
*** withdefault has left #ingres | 13:52 | |
*** zxiiro has quit IRC | 13:58 | |
*** rossand has joined #ingres | 14:17 | |
*** ChanServ sets mode: +o rossand | 14:17 | |
*** rossand has quit IRC | 14:28 | |
*** zxiiro has joined #ingres | 15:26 | |
*** ChanServ sets mode: +v zxiiro | 15:26 | |
*** mull_ has quit IRC | 15:55 | |
*** grantc has quit IRC | 15:57 | |
*** grantc has joined #ingres | 15:58 | |
*** grantc has quit IRC | 16:33 | |
*** mull_ has joined #ingres | 16:41 | |
*** mull_ has quit IRC | 18:02 | |
*** zxiiro_ has joined #ingres | 18:15 | |
*** zxiiro has quit IRC | 18:15 | |
*** atrofast has quit IRC | 18:15 | |
*** zxiiro_ has quit IRC | 19:20 | |
*** cytrinox_ has joined #ingres | 19:20 | |
*** zxiiro has joined #ingres | 19:21 | |
*** ChanServ sets mode: +v zxiiro | 19:21 | |
*** zxiiro has quit IRC | 19:22 | |
*** zxiiro has joined #ingres | 19:23 | |
*** ChanServ sets mode: +v zxiiro | 19:23 | |
*** cytrinox has quit IRC | 19:36 | |
*** cytrinox_ is now known as cytrinox | 19:37 | |
*** Alex| has joined #ingres | 22:40 | |
*** ChanServ sets mode: +o Alex| | 22:40 | |
*** withdefault has joined #ingres | 22:50 | |
*** ChanServ sets mode: +o withdefault | 22:50 | |
*** Alex| has quit IRC | 23:07 | |
*** Alex| has joined #ingres | 23:27 | |
*** ChanServ sets mode: +o Alex| | 23:27 |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!