| Author |
 Topic  |
|
Doug G
Support Moderator
USA
6493 Posts |
 Posted - 07 October 2002 : 20:37:47
|
Yep, upgrades are pricey.
Does anyone know of any problems in the database caused by no RI?
|
======
Doug G
======
Computer history and help at www.dougscode.com |
 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 07 October 2002 : 20:49:04
|
| I can't think of any. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 08 October 2002 : 19:53:31
|
There aren't any problems in the DB because there's code that does the RI stuff...which I think is a waste. This is what is necessary to delete a Category:
'## Forum_SQL - Delete all replys in this category
strSql = "DELETE FROM " & strTablePrefix & "REPLY "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all topics in this category
strSql = "DELETE FROM " & strTablePrefix & "TOPICS "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all archived replys in this category
strSql = "DELETE FROM " & strTablePrefix & "A_REPLY "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all archived topics in this category
strSql = "DELETE FROM " & strTablePrefix & "A_TOPICS "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all moderators and Allowed Members of the forums in this category
set rs = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT FORUM_ID "
strSql = strSql & " FROM " & strTablePrefix & "FORUM "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
rs.Open strSql, my_Conn
do until rs.EOF
my_Conn.Execute ("DELETE FROM " & strTablePrefix & "MODERATOR WHERE FORUM_ID = "
& cLng(rs("FORUM_ID"))),,adCmdText + adExecuteNoRecords
my_Conn.Execute ("DELETE FROM " & strTablePrefix & "ALLOWED_MEMBERS WHERE
FORUM_ID = " & cLng(rs("FORUM_ID"))),,adCmdText + adExecuteNoRecords
rs.movenext
loop
rs.close
set rs = nothing
'## Forum_SQL - Delete this Category from any Group Categories
strSql = "DELETE FROM " & strTablePrefix & "GROUPS "
strSql = strSql & " WHERE GROUP_CATID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all subscriptions to this Category
strSql = "DELETE FROM " & strTablePrefix & "SUBSCRIPTIONS "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete all forums in this category
strSql = "DELETE FROM " & strTablePrefix & "FORUM "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete the actual category
strSql = "DELETE FROM " & strTablePrefix & "CATEGORY "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
With proper RI, we'd just have to have the last one:
'## Forum_SQL - Delete the actual category
strSql = "DELETE FROM " & strTablePrefix & "CATEGORY "
strSql = strSql & " WHERE CAT_ID = " & cLng(delAr(i))
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
I realize that deletes of Categories don't happen that often, but I still don't like seeing that many hits to the DB. 10 my_Conn.Execute's + the moderators + allowed_members |
<-- Eric --> |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 08 October 2002 : 20:19:59
|
quote:
Originally posted by ruirib
I would rarely use Cascade Delete. A small user mistake and...all is gone.
are you talking about opening the database and doing work directly on it? This code is the same thing as a cascade delete, except it's a bunch of independent calls. |
<-- Eric --> |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 October 2002 : 21:12:01
|
| No, that's not what I'm talking about. Instead of deleting a lot of records from several tables I'd rather have the user delete the records from each table at a time. Like that, the likelihood of deleting a whole bunch of related records by accident is much smaller. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Deleted
deleted
4116 Posts |
Posted - 08 October 2002 : 21:37:40
|
quote:
Originally posted by ruirib
No, that's not what I'm talking about. Instead of deleting a lot of records from several tables I'd rather have the user delete the records from each table at a time. Like that, the likelihood of deleting a whole bunch of related records by accident is much smaller.
Do you mean that the current approach is not correct. For example should the forum say "You cannot delete the category with forums inside", "You cannot delete the forum with topics inside" etc?
What e3stone is saying is that "the bunch delete is done by the forum anyway" as far as I understand...
|
 Stop the WAR! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 October 2002 : 21:55:53
|
quote:
Originally posted by bozden
Do you mean that the current approach is not correct. For example should the forum say "You cannot delete the category with forums inside", "You cannot delete the forum with topics inside" etc?
What e3stone is saying is that "the bunch delete is done by the forum anyway" as far as I understand...
I'm not saying that the current way is not correct.
I'm saying I usually prefer it the way I described. In the case of Snitz, that would mean something like what you describe. Anyway I also think that a good rule for designing user interface is that it should allow a user access to an operationto which he really could not use. For example,the icon to delete forum would only show for forums without topics. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 08 October 2002 : 22:29:22
|
someone looking for history? :D
It's very simple actualy. It all comes down to speed.
This confusing? I mean... deletes and such would be so much faster if you had referential integrity right? YES... it would.
Let me ask you this. Who on a forum uses the features that would be speedier by inacting referential integrity? (answer: Admins/Moderators)
Normal users do not use enouph features that would be substantialy increasing performace of the product... in fact... end users would use features that would actualy decrease performance of the product.
End users browse and read and post...
What kind of SQL statements have to be used to do this? With referential integrity on... you would have to call several tables to get the information you need from only one or two tables... you would be calling nearly all data containing tables to make a call to read a topic. In it's current form you have a very basic statement that taxes the database to the very leaste.
Referential integrity would also rule out modifying the forum to be used in a lot of DB types (they may not seem important to some of you, but a lot of companies have bought into old technology, the current structure allows for backwards compatibility).
For the "What if" crowd... for about 1/3'rd of it's life, Snitz Forums 2000 was designed and based on Referential Integrity... it was the basis of how the forum worked... what was the biggest complaint we got in those days? "SPEED"... so we tweaked... twisted... squeeeeeezed... and now you have what works best :) |
Reinsnitz (Mike) |
|
|
|
Deleted
deleted
4116 Posts |
Posted - 08 October 2002 : 23:18:47
|
Thanks for explaining Mike |
Stop the WAR! |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 09 October 2002 : 01:28:12
|
| And a good explantion at that too :) |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 October 2002 : 07:34:58
|
quote:
Originally posted by reinsnitz
What kind of SQL statements have to be used to do this? With referential integrity on... you would have to call several tables to get the information you need from only one or two tables... you would be calling nearly all data containing tables to make a call to read a topic. In it's current form you have a very basic statement that taxes the database to the very leaste.
Sorry Mike, I fail to see how referential integrity would affect the number of tables involved in reading info. Can you please explain that?
quote:
Referential integrity would also rule out modifying the forum to be used in a lot of DB types (they may not seem important to some of you, but a lot of companies have bought into old technology, the current structure allows for backwards compatibility).
For the "What if" crowd... for about 1/3'rd of it's life, Snitz Forums 2000 was designed and based on Referential Integrity... it was the basis of how the forum worked... what was the biggest complaint we got in those days? "SPEED"... so we tweaked... twisted... squeeeeeezed... and now you have what works best :)
I do use RI whenever I can, and I'm not even arguing that Snitz should use it, but without pointing the finger to anyone (that's not why I'm posting this) I would say that the main reason for lack of speed would be the huge number of redundant database calls, which the Dev Team has done a great job eliminating from the current version.
IMHO the overhead imposed by current DBMSes to check for RI, which is only done when you actually change records, is negligible.
In a normalized DB structure the number of foreign keys in each table is, usually, very small. In all my DB apps I don't think I ever needed more than two or three. Again I don't think using RI for these foreign keys brings any noticeable performance loss.
To conclude, let me say once again that I have no desire to criticize what has been done. I can respect the decision not to use RI, but I disagree with most of the reasons given in your message and I thought that I should say that, because they are really general reasons for not using RI in databases, not only with Snitz. I've seen too many problems even on comercial database apps that simply wouldn't be there if RI had been properly implemented to let those general remarks go by without a comment  . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 09 October 2002 : 08:27:03
|
quote:
Sorry Mike, I fail to see how referential integrity would affect the number of tables involved in reading info. Can you please explain that?
prior to DB's supporting cascading it would definately have been an issue for updates, inserts and deletes I think. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 October 2002 : 08:53:18
|
quote:
Originally posted by Gremlin
prior to DB's supporting cascading it would definately have been an issue for updates, inserts and deletes I think.
Sorry, but what has cascading got to do with it?
You can have RI with no cascades and that does not change the tables involved in reading info. IMHO the tables involved in reading that are just dictated by the table structure you use, that is by the way you distribute your info by your tables, regardless of you have RI enforced for those tables or not.
I would also advance that the table structure should not be different if you choose to use RI. You still need the foreign keys in each table, even if you do not "declare" them as such. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 09 October 2002 : 09:13:41
|
| I was specifally NOT talking about reading actually :) before cascading you could very very easily end up with update anomalies if you werent careful. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Topic |
|
Topic Locked
