db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernd Ruehlicke" <BRuehli...@lgc.com>
Subject RE: Inserting values in an identity column
Date Tue, 08 Mar 2005 18:41:19 GMT
Of course you are right and I agree - nothing solved for the migration
problem.

I just saw the opportunity to mention future options, sorry for mixing
this in this thread.

Good luck with your migration !
B-)

> -----Original Message-----
> From: Christian Rodriguez [mailto:crodriguez@gmail.com] 
> Sent: Tuesday, March 08, 2005 12:09 PM
> To: Derby Discussion
> Subject: Re: Inserting values in an identity column
> 
> Bernd, your solution doesnt solve my problem AT ALL. It 
> doesnt solve the problem of migration from other databases to 
> Derby, which seems to be a very important feature.
> 
> Most databases generate "dumps", either as comma separated 
> files or as "inserts". Those inserts contain values, even for 
> identity or "autoincrement" columns. Any reasonable DB system 
> should be able to take those inserts and restore the values 
> in the database as a way to migrate from one RDBMS to another.
> 
> The "sequence" feature is a nice feature, but it wouldnt 
> solve this problem at all: you would have to map every value 
> in the "dump" file with the value given by the "sequence" and 
> 'translate" all the references, i.e. a big mess.
> 
> You are trying to solve the problem of "unique identifiers 
> throughout the database", Im talking about "inserting 
> specific values in identity columns". They may be related, 
> but they are not the same problem.
> 
> I just filed a JIRA. Thanks.
> Xtian
> 
> 
> On Tue, 8 Mar 2005 11:55:45 -0600, Bernd Ruehlicke 
> <BRuehlicke@lgc.com> wrote:
> > Yes, but (in my personal opinion) it would be even better 
> if we had a 
> > global sequence generator like Oracle, MS SQL etc
> > 
> > See also http://issues.apache.org/jira/browse/DERBY-103
> > 
> > Than a user does not need to worry about this and just 
> create a usual 
> > colum which will support altering. The price is of course that you 
> > have to insert the value yourselfe into this coulm but you may than 
> > use a function like  "mySequence.nextval". This makes also 
> sure that 
> > every single row in your database (if each table has an 
> identity) has 
> > a unique key - in all of the database. This becomse pretty 
> handy if a 
> > table holds the key and name of other table(s) - generically. Like
> > 
> > MyTable
> > table_id       VARCHAR
> > ...
> > Source_ID      VARCHAR   (if a text string is used as sequence key)
> > Source_Table   VARCHAR  (name of source table)
> > ...
> > ...
> > 
> > If you only had column identifiers you will have scenarious where 
> > "Source_ID" is the same for 2 differnt tables ! That is not 
> possible 
> > when using a global sequence.
> > 
> > ... anyhow ... just some words for discussion.
> > 
> > B-)
> > 
> > 
> > > -----Original Message-----
> > > From: Kathey Marsden [mailto:kmarsdenderby@sbcglobal.net]
> > > Sent: Tuesday, March 08, 2005 11:38 AM
> > > To: Derby Discussion; Derby Development
> > > Subject: Re: Inserting values in an identity column
> > >
> > > Christian,
> > >
> > > Could you please file a Jira entry for this?  I am hoping there 
> > > someone in the Derby development community that has time 
> to take up 
> > > this issue and propose and implement a solution.  It seems to me 
> > > that this is a serious hindrance to migration to Derby 
> that would be 
> > > good to resolve sooner rather than later.
> > >
> > > You can file it as an "Improvement"  in Jira at:
> > > http://issues.apache.org/jira/secure/CreateIssue!default.jspa
> > >
> > > Thanks
> > >
> > > Kathey
> > >
> > > Trevor Squires wrote:
> > >
> > > > Hi,
> > > >
> > > > I asked this very question last week (or so) for pretty
> > > much the same
> > > > reason.
> > > >
> > > > It doesn't appear that you can alter the table to make it
> > > an identity
> > > > column after creation (which was my first idea).  I was
> > > also unable to
> > > > find any way to switch off the identity feature 
> temporarily either.
> > > >
> > > > One person suggested that derby ought to support something like 
> > > > "generated by default as identity" which makes sense - although 
> > > > I'd rather be able to just switch on and off the 
> current behavior 
> > > > for loading of dumps and pre-population.
> > > >
> > > > In order to get my project out the door I ended up 
> converting the 
> > > > logic to update/select rows in an "identities" table 
> that I manage 
> > > > myself.
> > > >
> > > > HTH,
> > > > Trevor
> > > >
> > > > Christian Rodriguez wrote:
> > > >
> > > >> Hi everyone,
> > > >>
> > > >> I am trying to import data from a mysql dump file.
> > > Basically, I need
> > > >> to insert values in tables. Some of the tables in 
> mysql include 
> > > >> auto_increment columns, that I have translated to
> > > "generate always as
> > > >> identity" in derby. Now... the mysql dump file has values in 
> > > >> those columns, and I get an error when trying to 
> insert a value 
> > > >> into an IDENTITY column in derby. How can I do this?
> > > >>
> > > >> I have a couple of ideas, but none work:
> > > >>
> > > >> 1. I could define the column as a regular column (not
> > > identity) and
> > > >> after the values have been populated I could try to ALTER
> > > the column
> > > >> in the table to make it an identity column, but this last
> > > part doesnt
> > > >> seem posible.
> > > >> 2. Is there another way to implement "autoincrement" in derby?
> > > >> 3. Is it posible to disable the "identity" feature until
> > > all values
> > > >> have been populated?
> > > >>
> > > >> Any other ideas?
> > > >>
> > > >> Of course, I could let derby generate values for that
> > > identity column
> > > >> and then map the original values with those, and touch all the 
> > > >> references in the other tables and convert them to the new
> > > identity
> > > >> values, but that doesnt seem like a lot of work... and Im
> > > getting the
> > > >> feeling there should be a more reasonable way to import
> > > "dumps" from
> > > >> other databases...
> > > >>
> > > >> Thanks for any help you can give me, Xtian
> > > >
> > > >
> > > >
> > >
> > >
> >
> 

Mime
View raw message