Re: Select Query on Table with no Primary Key

From: John (mmmmmmmmm5..eb.de)
Date: Thu Jun 07 2007 - 11:41:51 EDT

  • Next message: Peter Schröder: "AW: Cayenne Generated Classes in Web Service API"

    opps forgot a part from the SQL

    SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
    rownumber
    FROM MyView e
    ORDER BY name

    Order by must be added

    Sako.

    Signature IT-Consult Armainak Sarkis
    ----- Original Message -----
    From: "John" <mmmmmmmmm5..eb.de>
    To: <use..ayenne.apache.org>
    Sent: Thursday, June 07, 2007 5:38 PM
    Subject: Re: Select Query on Table with no Primary Key

    > Hello Dave,
    >
    >
    > You said you use a view and you will NOT modify the data, then try this:
    >
    > Your View name : MyView.
    >
    > --------------------------------------------------------------------------------------------------------------------------
    > create another view call MyViewWithPK
    > -------------------------------------------------
    >
    >
    > SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
    > rownumber
    > FROM MyView e
    >
    > // remember that name must be included in the view MyView
    > // remember that this technique is slow, but i suppose this is your last
    > problem :)
    > ---------------------------------------------------------------------------------------------------------------------------
    > let me know if this work :)
    >
    >
    > Sako
    >
    > Signature IT-Consult Armainak Sarkis
    > ----- Original Message -----
    > From: "Dave Merrin" <dmerri..pasystems.co.uk>
    > To: <use..ayenne.apache.org>
    > Sent: Thursday, June 07, 2007 3:36 PM
    > Subject: Re: Select Query on Table with no Primary Key
    >
    >
    >> Hi Andrus,
    >>
    >> I was playing about with putting in my own EntityResolver. For now I'm
    >> going to take your approach and see how I get on.
    >>
    >> Cheers,
    >>
    >> Dave
    >>
    >> Andrus Adamchik wrote:
    >>> To give an example - if you have an EMPLOYEE table, you can make an
    >>> assumption that it is highly unlikely that there are two people with the
    >>> same name, born on the same date, and working in the same department. On
    >>> that assumption you can mark these 4 columns as PK in the Modeler:
    >>> FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
    >>>
    >>> Works well with views or tables (updateable or read-only) on any DB.
    >>>
    >>> Andrus
    >>>
    >>>
    >>> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
    >>>
    >>>> Tore is right - for Cayenne to handle an object (whether read-only on
    >>>> read/write), it needs to know which column or columns uniquely identify
    >>>> each row. Now... you can fake a PK in your model, even if there's none
    >>>> in the db - just select a really unique combination of columns, and
    >>>> mark those columns as the PK in the modeler. I've mapped tables with
    >>>> such "imaginary PK" a lot.
    >>>>
    >>>> If it is not possible (i.e. duplicate rows are expected to be fetched),
    >>>> you will have to use DataRows.
    >>>>
    >>>> Andrus
    >>>>
    >>>>
    >>>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
    >>>>
    >>>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
    >>>>>
    >>>>>> I'm trying to run a SelectQuery on a table with no primary key.
    >>>>>> Unfortunately it's not working. Can anybody help? I have no control
    >>>>>> over the database so I can't add in primary keys.
    >>>>>
    >>>>> As you know the PK are essential not only to update a row, but to make
    >>>>> sure a single row maps to a single DataObject in your context.
    >>>>>
    >>>>> Some database engines do have a unique invisible column. If your
    >>>>> database does this, then perhaps you could map that column as your
    >>>>> primary key? What database engine are you using?
    >>>>>
    >>>>> Regards,
    >>>>> - Tore.
    >>>>>
    >>>>
    >>>>
    >>>
    >>>
    >>
    >>
    >
    >



    This archive was generated by hypermail 2.0.0 : Thu Jun 07 2007 - 11:41:59 EDT