Sequence for PostgresSQL

[amel.jakupovic]

Hello,
first sorry for issue #30. :)

I checkout the latest source code (readonly) just see structure and
points where I can do the changes.

The goal is next:
1. I add column to model (ID, type integer, notnull, PK)
2. Here will be nice that I have one column with header Sequence where
i put name "SEQ_ID"
3. This column is editable only if primary key && not null && type is
number(int, double, long...)
4. After I do DDL he automaticly generates Sequence

If you can give me some info about what class will be interesting for
this or you have some other workflow please inform me.

I have now some time and can help with this. My collegue will join
too, but after summer breaks :).



p.s. I have 11 years experience in programming (from PB over .NET to
Java - especilly rcp, osgi, riena, virgo, spring...)

[Christian Sell]

Hello amel. Welcome on board then. Right now i am on vacation and
cannot look into the code. I will get back to you after aug, 3.

regards, chris

[Christian Sell]

Hello Amel,

finally back from my holiday, and cleaning up my mailbox..

I've had a quick look into the code. First I would like give the usual
introduction: this project has been going on for a few years now, and
started out as an exercise in eclipse programming. Not everything
always makes perfect sense - but it works most of the time. In any
case, implementing the stuff you envision will require you to
understand the inner workings of rmbench (and of my and Hannes's
brain, 4 years ago). So much for that.

My fist take at the sequence issue is this:

I think the best way would be to implement sequences through a new
datatype. That type would be derived from NUMBER (or so), and add
certain restrictions an/or properties. We already have a facility for
adding custom datatypes that are configured through a separate dialog
which can be opened from within the table details view. You may want
to have a look at com.byterefinery.rmbench.database.mssql.XMLDataType,
and how it is integrated into the architecture. Note that the
configuration dialog for a data type must be configured in XML using
the "extraTypeEditor" sub-element under the "database" extension
point. You can also look at the different database objects (e.g.,
SQLServer2008.java) to see how such types are integrated.

For DDL code generation, you should look at IDataType#getDDLName() and
IDataType#get/setExtra(). If I remember correctly, the former does the
DDL output, and the "extra" stuff is about saving to and reading from
the model file.

Another issue that would need to be handled is database import (via
JDBC).

I hope this gives you something to start from. If you have more
questions, dont hesitate. If you have code changes that you are
confident about, you can send them to me as patches, so I can have a
look at them before committing. If all works out well, I will assign
you commit rights after a while (usual procedure).

regards, Christian

[amel.jakupovic]

Hello Christian,

this year I don't have holidays...but 2 days ago I got son :).

I follow your instructions and I did new datatype SEQUENCE with dialog
where you put options for your SEQUENCE.
Now I will see how to integrate in DDL generator this part for
SEQUENCE.

I develop on Ubuntu 11.04, Eclipse Indigo and everything is working
fine with RMBench on this platform.

Every change I did in java code or xml I put comment (name - date -
comment).
If everything is good, you will tell mi how to send you changed files.

After my work on Sequence in postgre, I can make changes for other
types od DB.

And one thing....Greate project :)

p.s. Sorry for my English, I'm Croatian :)

[Christian Sell]

Hello Amel, and congratulations to your son!

once you are confident about your changes, just (multi-)select the
projects you have worked on in the package manager, open the context
menu, choose "Team"->"Create Patch", accept all the default options
except for the output, which would go to a file. Send that file to me
(this list). I can do "Apply Patch" and will then see all the changes
you made.

your platform is fine.

regards,
Chris

[amel.jakupovic]

Hello Cris,

I forget that Postgre is serius DB :).

If you add column with type SERIAL (you in rmbench have that type) and
do DDL you will get:

CREATE TABLE example
(
column_test SERIAL,
column22_test char
);

after execute that script, postgre DB will automaticly add sequence
SEQ_EXAMPLE_COLUMN_TEST.

After table is create, you can simple do INSERT example(column22_test)
values('test') and postgre will call nextval on sequence and put new
value in column COLUMN_TEST.

And this is what I need.

Because of that, I think that add SEQUENCE directly on column doesn't
have sense (this is for postgre, but I will check other DBs).
But maybe this will be interesting:
1. Create SEQUENCE but on tree node where is list of SEQUENCES
2. Create VIEWS on tree node where is list of VIEWS
3. Check how sequence is used and created on others DBs

thanks,
Amel

[Christian Sell]

Hello Amel,

sometimes we are even better than we remember ourselves ;)..

you should create issues for the items you think are missing. As far
as I am concerned, RMBench does not have a focus on (technical)
database administration features, but more on (conceptual) modeling.
Therefore, I dont think having a node for sequences (and triggers,
stored procedures, packages) is that important.

In case of views, it would make more sense to show them , but then you
would also have to distinguish views in the diagram, show their
definition in the properties view, and add a capability to create new
view definitions...

also, views dont have foreign keys, and thus dont make much sense to
show in a diagram. I am getting back to the thought that views, as
well, are technical database artifacts which dont fit well into a
modeling tool.. anyone?

regards,
Chris