Is there a way to get a table's primary key in V11

[Julio Carneiro]

In pre-V11 I used a convention to flag a field as Primary Key: any  
field with indexed+unique+nonenterable+nonmodifiable+displayonly  
attributes is a PK for me.

In V11 I want to use a different combination: autoincrement+unique ==>  
PK.

I checked the documentation and could not find a way to get the 'auto  
increment' field attribute.
Neither Get Field Properties nor Get Field Entry Properties returns  
that attribute.

Does anyone know a way to get that info? Is it available in the System  
Tables, accessible via SQL?

TIA
julio

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Josh Fletcher (4D, Inc)]

Hi Julio,

I would use the SQL System Tables...in this case I think you want
_USER_COLUMNS.

Check the SQL Reference for more information.

Note that you can also simply define Primary keys in SQL but you'd have
to retrofit your structure.

-Josh


Julio Carneiro wrote:

> In pre-V11 I used a convention to flag a field as Primary Key: any  
> field with indexed+unique+nonenterable+nonmodifiable+displayonly  
> attributes is a PK for me.
>
> In V11 I want to use a different combination: autoincrement+unique ==>  
> PK.
>
> I checked the documentation and could not find a way to get the 'auto  
> increment' field attribute.
> Neither Get Field Properties nor Get Field Entry Properties returns  
> that attribute.
>
> Does anyone know a way to get that info? Is it available in the System  
> Tables, accessible via SQL?
>
> TIA
> julio
>

--
Josh Fletcher
Technical Services Team Member
4D, Inc.
**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Julio Carneiro]

Josh,

I do not see any reference to SQL System Tables in the SQL reference  
manual. The only place where those tables are mentioned is in the V11  
Upgrade manual.
And I do not see anything on the 'auto increment' property there.

I know I can set/check a Primary Key constraint via SQL, but there is  
no way to do that via the Structure Editor :( which would be the  
natural place for that.
Hence my plan to use the auto-increment property.

julio

On Jul 2, 2009, at 8:41 PM, Josh Fletcher (4D, Inc.) wrote:

> Hi Julio,
>
> I would use the SQL System Tables...in this case I think you want  
> _USER_COLUMNS.
>
> Check the SQL Reference for more information.
>
> Note that you can also simply define Primary keys in SQL but you'd  
> have to retrofit your structure.
>
> -Josh
>
>
> Julio Carneiro wrote:
>> In pre-V11 I used a convention to flag a field as Primary Key: any  
>> field with indexed+unique+nonenterable+nonmodifiable+displayonly  
>> attributes is a PK for me.
>> In V11 I want to use a different combination: autoincrement+unique  
>> ==>  PK.
>> I checked the documentation and could not find a way to get the  
>> 'auto  increment' field attribute.
>> Neither Get Field Properties nor Get Field Entry Properties  
>> returns  that attribute.
>> Does anyone know a way to get that info? Is it available in the  
>> System  Tables, accessible via SQL?
>> TIA
>> julio
>

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Josh Fletcher (4D, Inc)]

Hi Julio,

Julio Carneiro wrote:
> I do not see any reference to SQL System Tables in the SQL reference  
> manual.

It's on page 81 of "4D_v11_SQL_Reference_r3.pdf".

The SQL keyword is "AUTO_INCREMENT" IIRC.

-Josh

--
Josh Fletcher
Technical Services Team Member
4D, Inc.
**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Julio Carneiro]

I may be blind. I did find reference to System Tables on page 81 of  
4D_v11_SQL_Reference_r4.pdf.

But I cannot find any way to retrieve that AUTO_INCREMENT property. I  
know I can set it via SQL DDL and the Structure Editor.

What I want is to get that property, not set it.

I am writing generic code where all I know is a table name and I need  
to find that table's Primary Key.

julio

On Jul 2, 2009, at 8:59 PM, Josh Fletcher (4D, Inc.) wrote:

> Hi Julio,
>
> Julio Carneiro wrote:
>> I do not see any reference to SQL System Tables in the SQL  
>> reference  manual.
>
> It's on page 81 of "4D_v11_SQL_Reference_r3.pdf".
>
> The SQL keyword is "AUTO_INCREMENT" IIRC.
>
> -Josh
>
>

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Josh Fletcher (4D, Inc)]

Hi Julio,

Julio Carneiro wrote:
> But I cannot find any way to retrieve that AUTO_INCREMENT property. I  
> know I can set it via SQL DDL and the Structure Editor.

Sorry, my bad, I think the table you want _USER_CONS_COLUMNS.  This
table has all of the column constraints.

Note: I don't know what the query is to get the information you want
(otherwise I would post it)...just trying to point you in the right
direction.

Kind regards,

Josh Fletcher

--
Josh Fletcher
Technical Services Team Member
4D, Inc.
**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Julio Carneiro]

Nope... _USER_CONS_COLUMNS does not show auto_increment. I guess that  
property is not exposed in any way...

On Jul 2, 2009, at 9:30 PM, Josh Fletcher (4D, Inc.) wrote:

> Hi Julio,
>
> Julio Carneiro wrote:
>> But I cannot find any way to retrieve that AUTO_INCREMENT property.  
>> I  know I can set it via SQL DDL and the Structure Editor.
>
> Sorry, my bad, I think the table you want _USER_CONS_COLUMNS.  This  
> table has all of the column constraints.
>
> Note: I don't know what the query is to get the information you want  
> (otherwise I would post it)...just trying to point you in the right  
> direction.
>
> Kind regards,
>
> Josh Fletcher

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Peter Jakobsson-2]

Hi Julio

Here's the relevant thread for you...

http://thread.gmane.org/gmane.comp.lang.inug-4d.tech/82658/focus=82728

Looks like a feature request :)

Peter

On 3 Jul 2009, at 04:32, Julio Carneiro wrote:

> Nope... _USER_CONS_COLUMNS does not show auto_increment. I guess  
> that property is not exposed in any way...

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Julio Carneiro]

posted a feature request to bugs.fr

On Jul 3, 2009, at 3:48 AM, Peter Jakobsson wrote:

> Hi Julio
>
> Here's the relevant thread for you...
>
> http://thread.gmane.org/gmane.comp.lang.inug-4d.tech/82658/focus=82728
>
> Looks like a feature request :)
>
> Peter
>
> On 3 Jul 2009, at 04:32, Julio Carneiro wrote:
>
>> Nope... _USER_CONS_COLUMNS does not show auto_increment. I guess  
>> that property is not exposed in any way...

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Arnaud de Montard]

Le 3 juil. 09 à 02:24, Julio Carneiro a écrit :

> I am writing generic code where all I know is a table name and I  
> need to find that table's Primary Key.

I use hard coded field names, for example a field having the same name  
as the table followed by "_ID" is the PK.
   [Table1]Table1_ID -> primary key
   [Table2]Table1_ID -> foreign key
   [Table1]TScreate -> time stamp created
   [Table1]TSmodif -> time stamp modified

--
Arnaud de Montard




**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Julio Carneiro]

well that only works if you have control over the structure.
for a generic component one cannot rely on naming conventions.
I have to use metadata associated to a table/field.

julio

On Jul 3, 2009, at 12:38 PM, arnaud wrote:

>
> Le 3 juil. 09 à 02:24, Julio Carneiro a écrit :
>
>> I am writing generic code where all I know is a table name and I  
>> need to find that table's Primary Key.
>
> I use hard coded field names, for example a field having the same  
> name as the table followed by "_ID" is the PK.
>  [Table1]Table1_ID -> primary key
>  [Table2]Table1_ID -> foreign key
>  [Table1]TScreate -> time stamp created
>  [Table1]TSmodif -> time stamp modified
>
> --
> Arnaud de Montard

**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[MIYAKO]

Hi,

if only there were an API that returned the xml schema of the the  
current structure,
(as in export to xml from the design environment) then things would be  
different...
(incidentally there is an xml to structure element in the plugin api,  
but not the other way round)

miyako

On 2009/07/04, at 0:48, Julio Carneiro wrote:

> well that only works if you have control over the structure.
> for a generic component one cannot rely on naming conventions.
> I have to use metadata associated to a table/field.


**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Arnaud de Montard]

Le 3 juil. 09 à 17:48, Julio Carneiro a écrit :

> well that only works if you have control over the structure.
> for a generic component one cannot rely on naming conventions.
> I have to use metadata associated to a table/field.

Ok, I see. But what happens if one uses unique and autoincrement not  
for a PK? I mean for example a DB where primary keys fields are UUIDs,  
and autoincrement/unique fields are used to sort records as added.

--
Arnaud de Montard




**********************************************************************
Live Data Mapping Services with 4D v11 SQL
See it for yourself - http://www.4d.com/solutions/ndwater.html

4D Internet Users Group (4D iNUG)
FAQ:  http://www.4D.com/support/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************