| Author |
 Topic  |
|
|
h20
Starting Member
39 Posts |
 Posted - 15 November 2000 : 16:57:50
|
Yes Mike... just take a record count of the table first... then just increment by one ... and there is your cross-platform autonumber... no autonumber data type(MS) and no trigger (Oracle) needed. You would need to change the data type of the message id field to number. There are really only 2 types of management the Database server should perform. They are:
1) User management
2) Concurrency Control Management
For a cross-platform forum, everything else should be managed in the ASP code. I know this will create a lot of up front work. However, if you want a forum that works correctly against MS and Oracle, you have no choice but to perform all but the 2 management processes mentioned earlier in the ASP code.
< |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 15 November 2000 : 21:28:29
|
This would not work, counting the records would be no good, if you delete some the count is less than the last id, doing a max(id) on the table would be ok. The best way is to change id to a string, and write a function which creates a unique numberstring, generaly based on time to create a unique string (similar to the way snitz stores the datetime).
'Resistance is futile'< |
 |
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 16 November 2000 : 03:01:40
|
We use the topic-id to link to topics, so using a large unique identifier wouldn't be that nice.
But I aggree that using something like a string with YYYYMMMDDDhhmmss + maybe some random number added to be absolutely sure it is unique might work.
I think the count number thing would have another problem though: locking to make sure that after you collect the currently highest number add one and then want to store nobody has beaten you to it.
Because otherwise you would be risking having two or more records with the same number.
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 16 November 2000 : 12:48:54
|
I use a COUNTERS table in many DB's with two columns, name and number. Then use a transactional SQL to retrieve the next counter number for a given counter name and update the counter. Normally I use a sp in SQL Server for each id I need, like this:
create proc increment_idcounter_
as
begin tran
update COUNTERS set number = number + 1
where name = 'whatever_idname'
select number from COUNTERS
where name = 'whatever_idname'
commit tran
return
This method works quite well for me. An ASP function could easily do what the sp does for cross-db functionality.
======
Doug G
======< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 16 November 2000 : 14:25:23
|
I understand that MySql only partly supports transactions.
Does this code in case of SQL Server and Access mean that it can't happen in situations with heavy trafic that two topics get the same id ? in other words: does starting the transaction also lock the COUNTERS table until the transaction is committed ?
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 16 November 2000 : 14:58:04
|
I would use something like this
define a global variable intIDCounter=0
function GetNewID()
GetNewID= DateToStr(Now()) + doublenum(intIDCounter)
intIDCounter = intIDCounter + 1
if intIDCounter > 99 then
intIDCounter = 0
end function
obviously if you expect to get more than 100 posts a second, you would need to adjust intIDCounter and the padding
'Resistance is futile'< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 16 November 2000 : 15:53:29
|
Gor-
AFAIK yes, transactioning will prevent collisions on busy sites, I've always assumed it did but never dug real deep to verify the assumption. I've never had a problem on moderate usage sites, and I've seen similar code described from admins at real busy sites. Graz's site would probably give a definitive answer.
This method also solves the problem of returning the autonumber ID to your code, a task that can be problematic in some environments.
A date-based function like HuwR's should work fine, but you don't get the nicely incremented by one values like autonumbers or manual counters. In most cases, I prefer sequential numbers because in many tables a missing number is an indication of a problem.
BTW, I don't know Oracle and I have no idea how Oracle does transactions and sp's.
======
Doug G
======< |
|
|
|
h20
Starting Member
39 Posts |
Posted - 16 November 2000 : 19:03:25
|
quote:
This would not work, counting the records would be no good, if you delete some the count is less than the last id, doing a max(id) on the table would be ok. The best way is to change id to a string, and write a function which creates a unique numberstring, generaly based on time to create a unique string (similar to the way snitz stores the datetime).
'Resistance is futile'
True... you could store the next ID in a table somewhere... then increment that as new topics are posted...
Gor...
about locking... I think you are referring to concurrency control... all DB servers worth their salt... (SQL , Oracle) handle this on the backend... (although I must say Oracle does a much better job... Oracle automatically locks the record.. no need for the lock and unlock statements... however SQL and MS transaction server require you to manually all the lock and unlock methods (I think). you can use the lock and unlock statements against Oracle using the newest MADC (2.6).
We may have to create 2 procedures that post topics... one that manually locks using MS transaction server (SQL) ... and one procedure that does not (Oracle). However, they can access the same DB field that stores the next available ID. You will also find that querying against a table without an auto number as the primary key is much faster...
Another idea is to use MMDDYYYYhhmmss+username as the topic id... the only way to duplicate this is if there was a double posting... and we do not want that anyways...
< |
|
|
| |
Topic |
|
Forum Locked
Topic Locked
