Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
[no subject]
=20 So I am looking for a way to get the keys diabled and enabled without violating the sql spec or atleast not giving an error if there is no foreign key on the table. =20 Can anyone help me with this? =20 Thanks in advance, =20 Robert =20 =20 =20 ------_=_NextPart_001_01C67051.FF0566A6 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns=3D"http://www.w3.org/TR/REC-html40";> <head> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)"> <style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman";} a:link, span.MsoHyperlink {color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {color:purple; text-decoration:underline;} span.EmailStyle17 {mso-style-type:personal-compose; font-family:Arial; color:windowtext;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in;} div.Section1 {page:Section1;} --> </style> </head> <body lang=3DEN-US link=3Dblue vlink=3Dpurple> <div class=3DSection1> <p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DNL = style=3D'font-size:10.0pt; font-family:Arial'>Hello all,<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DNL = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'>I am in a project that uses DBUnit(in combination = with JUnit) to run some tests on our sourcecode.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'>For those of you who do not know DBUnit it works: = <o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'>You define a xml file with the tables and data you = want to insert and every test case the = database-data<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'>In the xml file gets inserted into the database. = Since DBUnit has now knowledge of foreign keys it doesn’t know what data = it should insert or delete before other data to not get a Foreign key = violation. For this reason I want to temporary disable the foreign keys when DBUnit = updates the test-database.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'>To do this I used some code I found on a = postgres mailing list (don’t know witch one from memory = tough).<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'> The code is as = follows:<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> <p class=3DMsoNormal style=3D'text-autospace:none'><b><font size=3D2 = color=3D"#7f0055" face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:#7F0055;background:white;font-weight:bold'>for</span></font></b><fo= nt size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'> (</span></font><b><font = size=3D2 color=3D"#7f0055" face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier = New";color:#7F0055;background:white;font-weight:bold'>int</span></font></= b><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'> i =3D 0; i < = tables.</span></font><font size=3D2 color=3D"#0000c0" face=3D"Courier New"><span = style=3D'font-size:10.0pt; font-family:"Courier = New";color:#0000C0;background:white'>length</span></font><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'>; i++) {</span></font><font = size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal = style=3D'text-indent:.5in;text-autospace:none'><b><font size=3D2 color=3D"#7f0055" face=3D"Courier New"><span = style=3D'font-size:10.0pt; font-family:"Courier = New";color:#7F0055;background:white;font-weight:bold'>if</span></font></b= ><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'> (enabled) = {</span></font><font size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal = style=3D'margin-left:1.0in;text-autospace:none'><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:black;background:white'>statement.addBatch(</span></font><font = size=3D2 color=3D"#2a00ff" face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:#2A00FF;background:white'>"UPDATE pg_class SET reltriggers =3D COUNT(*) FROM pg_trigger WHERE pg_class.oid =3D tgrelid = AND relname =3D '"</span></font><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black;background:white'> + tables[i] + </span></font><font size=3D2 color=3D"#2a00ff" = face=3D"Courier New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:#2A00FF;background: white'>"';"</span></font><font size=3D2 color=3Dblack = face=3D"Courier New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black;background:white'>);</span></font><font size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal style=3D'text-autospace:none'><font size=3D2 = color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:black;background:white'> } = </span></font><b><font size=3D2 color=3D"#7f0055" face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier = New";color:#7F0055;background:white;font-weight:bold'>else</span></font><= /b><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'> {</span></font><font = size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal style=3D'text-autospace:none'><font size=3D2 = color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:black;background:white'> = statement.addBatch(</span></font><font size=3D2 color=3D"#2a00ff" face=3D"Courier New"><span = style=3D'font-size:10.0pt; font-family:"Courier New";color:#2A00FF;background:white'>"UPDATE = pg_class SET reltriggers=3D0 WHERE relname =3D '"</span></font><font = size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:black;background:white'> + tables[i] + </span></font><font = size=3D2 color=3D"#2a00ff" face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier = New";color:#2A00FF;background:white'>"';"</span></font><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black;background:white'>);</span></font><font = size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal style=3D'text-autospace:none'><font size=3D2 = color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier New"; color:black;background:white'> = }</span></font><font size=3D2 face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family:"Courier = New"'><o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black;background:white'>}</span></font><font size=3D2 color=3Dblack face=3D"Courier New"><span = style=3D'font-size:10.0pt;font-family: "Courier New";color:black'><o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>Where = the tables array is filled with the tables DBUnit updates from the xml files, and = enables is a Boolean witch tells the function if it should enable or disable the foreign keys.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>Tough = when I run this query on a table that has no foreign keys I get a “ctid is = NULL” error.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>From = browsing the bugs mailing list I found this is caused when you do a UPDATE = statement with a aggregate function on the top level that has no results, and that = those functions should be considered illegal as it is against the sql = spec.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>So I am = looking for a way to get the keys diabled and enabled without violating the sql = spec or atleast not giving an error if there is no foreign key on the = table.<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>Can = anyone help me with this?<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier New";color:black'>Thanks = in advance,<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier = New"><span style=3D'font-size:10.0pt;font-family:"Courier = New";color:black'>Robert<o:p></o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> <p class=3DMsoNormal><font size=3D2 face=3DArial><span = style=3D'font-size:10.0pt; font-family:Arial'><o:p> </o:p></span></font></p> </div> </body> </html> ------_=_NextPart_001_01C67051.FF0566A6--
- Prev by Date: Re: searching multiple tables
- Next by Date: [no subject]
- Previous by thread: SQL Count Magic Required....
- Next by thread: [no subject]
- Indexes: