Re: Select Query on Table with no Primary Key

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

  • Next message: John: "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:38:10 EDT