| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 18 August 2002 : 23:18:57
|
Brian,
I have no experience with the actual implementation of this. What I've posted about comes from my knowledge of Snitz.
Both strategies seem possible. If you're on SQL Server you can just create a view to join both your main table and an additional one. If you're on Access a query could do the same (although you need to be careful to ensure that you can edit and add records with it.
Given your preference for not mucking with your user table, creating and additional one can, thus, be a good choice. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
bax
Junior Member
141 Posts |
Posted - 18 August 2002 : 23:41:01
|
Ruirib:
I'm just starting to play with Snitz, so really have no experience with the code base - how often is FORUM_MEMBERS used? Is it just used for the profiles page, and maybe a bit for the members page? This is in relation to users making updates to stuff, etc. - how often in the code, with either approach, am I going to have to change the way updates occur (since obviously it wouldn't just be a straight write to one table anymore).
Sincerely,
Bryan |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 18 August 2002 : 23:51:08
|
FORUM_MEMBERS is widely used. If you create a a view (SQL Server) or a query (Access) named FORUM_MEMBERS, I don't see why it's not like a straight write to a table. Looks like it will be the same to me...
If you do this, it is very likely you won't need to change code. I never tried it, but sure looks very possible to do it this way.
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
bax
Junior Member
141 Posts |
Posted - 19 August 2002 : 00:18:29
|
| I didn't think views were updateable with SQL Server. In other words, if I try to do an INSERT into a view FORUM_MEMBERS, or an UPDATE into a view FORUM_MEMBERS, I don't believe SQL Server will let me. Selects should be fine, but views I don't think can handle updates across more than one table. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
bax
Junior Member
141 Posts |
Posted - 19 August 2002 : 00:58:00
|
| Not necessary - I'll explore INSTEAD OF on google. Thanks, sleep well (for those of us in Japan, it's the middle of the day). |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
bax
Junior Member
141 Posts |
Posted - 20 August 2002 : 03:31:14
|
OK, Ruirib, I lied. I can't seem to find a good sample on doing this. Now, posting seems to work just fine, because it is only updating the one table - FORUM_MEMBER_PROPS (increments m_post). However, the profile pages don't work so well for updates right now, as you can imagine.
Here are my two tables - user table I can't really modify, and the extended forum_member_props table.
CREATE TABLE [dbo].[FORUM_MEMBER_PROPS] (
[MEMBER_ID] [int] NOT NULL ,
[M_STATUS] [smallint] NOT NULL ,
[M_USERNAME] [nvarchar] (75) NULL ,
[M_COUNTRY] [nvarchar] (50) NULL ,
[M_HOMEPAGE] [nvarchar] (255) NULL ,
[M_SIG] [ntext] NULL ,
[M_VIEW_SIG] [smallint] NULL ,
[M_SIG_DEFAULT] [smallint] NULL ,
[M_DEFAULT_VIEW] [int] NULL ,
[M_LEVEL] [smallint] NOT NULL ,
[M_AIM] [nvarchar] (150) NULL ,
[M_ICQ] [nvarchar] (150) NULL ,
[M_MSN] [nvarchar] (150) NULL ,
[M_YAHOO] [nvarchar] (150) NULL ,
[M_POSTS] [int] NOT NULL ,
[M_DATE] [nvarchar] (14) NULL ,
[M_LASTHEREDATE] [nvarchar] (14) NULL ,
[M_LASTPOSTDATE] [nvarchar] (14) NULL ,
[M_TITLE] [nvarchar] (50) NULL ,
[M_SUBSCRIPTION] [smallint] NULL ,
[M_HIDE_EMAIL] [smallint] NULL ,
[M_RECEIVE_EMAIL] [smallint] NULL ,
[M_LAST_IP] [nvarchar] (15) NULL ,
[M_IP] [nvarchar] (15) NULL ,
[M_AGE] [nvarchar] (10) NULL ,
[M_HOBBIES] [ntext] NULL ,
[M_LNEWS] [ntext] NULL ,
[M_QUOTE] [ntext] NULL ,
[M_BIO] [ntext] NULL ,
[M_MARSTATUS] [nvarchar] (100) NULL ,
[M_LINK1] [nvarchar] (255) NULL ,
[M_LINK2] [nvarchar] (255) NULL ,
[M_PHOTO_URL] [nvarchar] (255) NULL ,
[M_KEY] [nvarchar] (32) NULL ,
[M_NEWEMAIL] [nvarchar] (50) NULL ,
[M_PWKEY] [nvarchar] (32) NULL ,
[M_SHA256] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[users] (
[uid] [int] IDENTITY (1, 1) NOT NULL ,
[coalition_id] [int] NOT NULL ,
[userName] [varchar] (100) NOT NULL ,
[userPassword] [varchar] (100) NOT NULL ,
[userConsent] [smallint] NOT NULL ,
[userSiteAccess] [int] NULL ,
[userAdminLevel] [smallint] NOT NULL ,
[userFirstName] [varchar] (100) NOT NULL ,
[userLastName] [varchar] (100) NOT NULL ,
[userEmail] [varchar] (75) NOT NULL ,
[userOccupation] [nvarchar] (255) NULL ,
[userPhone1] [varchar] (75) NOT NULL ,
[UserPhone1Type] [smallint] NOT NULL ,
[userStreetAddress1] [varchar] (60) NOT NULL ,
[userStreetAddress2] [varchar] (60) NULL ,
[userCity] [varchar] (100) NOT NULL ,
[userState] [varchar] (4) NULL ,
[userZipCode] [varchar] (16) NOT NULL ,
[userAddressType] [smallint] NOT NULL ,
[userDOB] [char] (10) NOT NULL ,
[userEducation] [int] NOT NULL ,
[userGender] [char] (2) NOT NULL ,
[userEthnicity] [int] NOT NULL ,
[userLastLogonDate] [datetime] NOT NULL ,
[UserFirstLogonDate] [datetime] NULL ,
[userRegistrationDate] [datetime] NULL ,
[PretestStartedDate] [datetime] NULL ,
[PretestCompletedDate] [datetime] NULL ,
[uniquePretestID] [nvarchar] (20) NULL
) ON [PRIMARY]
GO
And FORUM_MEMBERS is then a view of these two things:
CREATE VIEW dbo.FORUM_MEMBERS
AS
SELECT u.userName AS M_NAME, u.userPassword AS M_PASSWORD, u.userEmail AS M_EMAIL, u.userFirstName AS M_FIRSTNAME,
u.userLastName AS M_LASTNAME, u.userDOB AS M_DOB, u.userOccupation AS M_OCCUPATION, u.userCity AS M_CITY, u.userState AS M_STATE,
u.userGender AS M_SEX, f.*
FROM dbo.users u INNER JOIN
dbo.FORUM_MEMBER_PROPS f ON u.uid = f.MEMBER_ID
Now, I'm a bit lost on the trigger. I want to make sure I only update the underlying tables when specific columns are updated, using COLUMNS_UPDATED(). Help? How do I specify to only update those columns that have changed within an INSTEAD of trigger? I need an advanced SQL book, that's for dang sure. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 August 2002 : 06:26:12
|
Ok, I'll have a look at this. If you're looking for a good SQL Server book, I can recommend Professional SQL Server 2000 Programming, Robert Vieira, Wrox.
I'll come back in a while with the help you requested. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Dan Martin
Average Member
USA
528 Posts |
Posted - 20 August 2002 : 12:26:08
|
I guess I should've mentioned that I use this method solely in Access. It allows full read, update, and delete. Unless your query is a join, and then you can only read and update, but not delete.
SQL and views are out of my realm of expertise. When I did this with Access, the only things I had to change was the member deletion code (since I used a joined query) and I had to handle nulls in a few places that Snitz doesn't expect nulls.
Sorry to hear that it is causing you trouble. |
|
|
|
bax
Junior Member
141 Posts |
Posted - 21 August 2002 : 00:25:06
|
Really? Access can update multiple tables when you do something like this
update SomeQuery set goo = 1, goo2 = 2
where someQuery is a join between two tables? It would be really fascinating to me if Access can do this, but SQL Server can't (and ironic). |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 21 August 2002 : 02:36:50
|
quote:
Now, I'm a bit lost on the trigger. I want to make sure I only update the underlying tables when specific columns are updated, using COLUMNS_UPDATED(). Help? How do I specify to only update those columns that have changed within an INSTEAD of trigger? I need an advanced SQL book, that's for dang sure.
The trigger has to fire on INSERT, UPDATE or DELETE you can't afaik control a trigger to fire only when a certain field in a table is updated.
To get around this what you do is add to the top of your trigger something like
IF UPDATE(column_name)
BEGIN
....
....
END
|
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
s0110282
Starting Member
USA
20 Posts |
Posted - 21 August 2002 : 08:23:23
|
I just wanted to add my two cents.
I am using the view called FORUM_MEMBERS in SQL2000 and it works great. I have an existing Members table on my site (with just member name and password - but you can have other stuff too) and all other info in a separate table (can be named anything BUT FORUM_MEMBERS since that is the view name).
Reading from that table is not a problem. The only issue is when trying to add new members it doesn't put the 'member id' in both tables. Why is that? It's because the code in Snitz is set up to place the id in one table. Since two tables are in the database(going through one view), only one member id gets inserted.
The registration has to be modified to allow both member id's to be inserted. If this is changed, everything else works without a problem (this is also assuming you start off with a fresh database or you populate fields with values like MemberSince in your existing table).
Scott |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Dan Martin
Average Member
USA
528 Posts |
Posted - 21 August 2002 : 11:04:23
|
quote:
Originally posted by bax
Really? Access can update multiple tables when you do something like this
update SomeQuery set goo = 1, goo2 = 2
where someQuery is a join between two tables? It would be really fascinating to me if Access can do this, but SQL Server can't (and ironic).
Yes, the entire forums at www.councilofgrey.com run this way, and updates work fine. The only thing it can't do is delete from the joined query. |
|
|
|
Topic |
|
|
|
Topic Locked
