U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » Multiple fields primary key

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Multiple fields primary key [message #5338]

Thu, 14 September 2006 11:08

unodgs
Messages: 1366
Registered: November 2005
Location: Poland

Ultimate Contributor

Is there any way to create primary kay from more than one fields using schama files?

This not work..

TABLE_(TEST)
INT_ (ID) PRIMARY_KEY
INT_ (VERSION) PRIMARY_KEY
.....
END_TABLE

If from any reason previous form would be hard to implement mayby form below would be easier..

TABLE_(TEST)
INT_ (ID)
INT_ (VERSION)
.....
PRIMARY_KEY (ID, VERSION)
END_TABLE

Report message to a moderator

Re: Multiple fields primary key [message #5340 is a reply to message #5338]

Thu, 14 September 2006 11:48

unodgs
Messages: 1366
Registered: November 2005
Location: Poland

Ultimate Contributor

I didn't write it but I meant OleDB...
Anyway I found that in Oracle schema (as well as in Sqlite3) there is DOUBLE_PRIMARY_KEY macro.
I copied it to OleDBSchema.h and mofied my sch file to look like this:

TABLE_(PERSON)
	INT_ (ID)
	INT_ (VER) DUAL_PRIMARY_KEY(ID, VER)
	STRING_ (NAME, 40)
END_TABLE

I removed all previously generated sql files (and table from db too) and run the test app. Now sql files have inside:

S_SQL-OleDb.sql
--------------------------

create table PERSON (
    ID                   integer,
    VER                  integer, primary key (ID, VER),
    NAME                 varchar(40)
);

U_SQL-OleDb.sql
--------------------------

create table PERSON (
    ID                   integer
);

alter table PERSON add  VER                  integer;
alter table PERSON alter column     VER                  integer, primary key (ID, VER);
alter table PERSON add  NAME                 varchar(40);
alter table PERSON alter column     NAME                 varchar(40);

SD_SQL-OleDb.sql
--------------------------
drop table PERSON;

Other genereated files were empty.

Syntax of S_Sql-OleDb.sql is correct for ms sql server 2005. Unfortunately it seems like upp always executes upgrade script which does not create primary key from two fields.

Code I use to perform schema changes:

	Cout() << "Performing schema changes...\n";
	SqlSchema sch(MSSQL);
	StdStatementExecutor se(db);
	All_Tables(sch);
	if(sch.ScriptChanged(SqlSchema::UPGRADE))
	{
		Cout() << "Upgrading...\n";
		OleDBPerformScript(sch.Upgrade(), se);
	}
	if(sch.ScriptChanged(SqlSchema::ATTRIBUTES)) 
	{
		Cout() << "Attributes...\n";
		OleDBPerformScript(sch.Attributes(), se);
	}
	if(sch.ScriptChanged(SqlSchema::CONFIG)) 
	{
		Cout() << "Configuration...\n";
		OleDBPerformScript(sch.ConfigDrop(), se);
		OleDBPerformScript(sch.Config(), se);
	}
	sch.SaveNormal();

Report message to a moderator

Re: Multiple fields primary key [message #5341 is a reply to message #5340]

Thu, 14 September 2006 20:12

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

unodgs wrote on Thu, 14 September 2006 05:48

I Syntax of S_Sql-OleDb.sql is correct for ms sql server 2005. Unfortunately it seems like upp always executes upgrade script

Because you do not execute it. Use SqlScheam::SCHEMA to execute the "schema" script.

However, I thing that adding dual primary keys (and maybe even single primary keys) should be the property of "ATTRIBUTES" script too. In other words, "underdevelopment". Could you fix that?

BTW, if you have better idea for dual key (and other multi-key) "syntax", do not keep it secret... Smile

Mirek

Report message to a moderator

Previous Topic:	select(*)
Next Topic:	my_sql's charset in linux

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sat Apr 27 17:35:21 CEST 2024

Total time taken to generate the page: 0.02796 seconds