ASP.NET Provider with dotConnect

ASP.NET Provider with dotConnect

Postby lkeel » Mon 17 Nov 2008 19:19

Has the schema changed for the ASP.NET Provider between PostgreSqlDirect .Net and dotConnect? I have a website that was working just fine until I upgraded to dotConnect. Now I get an error when trying to login: Guid should contain 32 digits with 4 dashes

I have looked all in the samples and there is no install_tables.sql as before, so I am not sure what the expected schema is. If there is a change in the schema, do you provide an upgrade script or am I going to have to upgrade it myself?

Thanks in advance,
Lee
lkeel
 
Posts: 12
Joined: Fri 15 Feb 2008 14:49

Postby Shalex » Wed 19 Nov 2008 17:04

Please check carefully the installation folder of dotConnect for PostgreSQL 4.00. In the root of this folder should be the InstallWebTables.sql file (by default - C:\Program Files\Devart\dotConnect\PostgreSQL\InstallWebTables.sql). Compare its schema with the schema you was using and notify us about the result.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby lkeel » Wed 03 Dec 2008 15:37

The schema between the two versions has changed, but even more importantly is that the functionality of them has changed. The userid column that is just a varchar is now expecting a GUID formatted value in this field. Previously the value was a long string of 1 and 0's. But now when I try to log into my system with the upgraded dotConnect objects, I get the following error. Please advise as to the upgrade method for the new tables and the new usage of the userid column.

ERROR:

Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).]
System.Guid..ctor(String g) +2486
Devart.Data.PostgreSql.Web.Providers.PgSqlMembershipProvider.ReadField(IDataReader reader, Int32 i, DbType dbType) +76
Devart.Common.Web.Providers.DbMembershipProvider.a(String A_0) +373

[ProviderException: An exception occurred. Please contact your administrator.]
Devart.Common.Web.Providers.DbMembershipProvider.a(String A_0) +956
Devart.Common.Web.Providers.DbMembershipProvider.a(String A_0, String A_1) +97
Devart.Common.Web.Providers.DbMembershipProvider.ValidateUser(String username, String password) +41
System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +60
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +119
System.Web.UI.WebControls.Login.AttemptLogin() +115
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053
lkeel
 
Posts: 12
Joined: Fri 15 Feb 2008 14:49

Postby Shalex » Thu 04 Dec 2008 14:17

We recommend to regenerate the values of the userid column for the existing users. E.g., the Guid.NewGuid() function can be used for this purpose. For the new users, the GUID value will be generated automatically.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby lkeel » Thu 04 Dec 2008 14:24

Is there an upgrade script for this? This is not just a simple update statement because this is a foreign key column to several other tables. Plus there are new tables that were not in the previous version. I find it hard to believe that you would change the datamodel out from under people and then not provide a way to upgrade. Please let me know asap about an upgrade script so that I can proceed accordingly.

Thanks,
Lee
lkeel
 
Posts: 12
Joined: Fri 15 Feb 2008 14:49

Postby Shalex » Tue 09 Dec 2008 11:11

We are investigating this problem. You will be notified about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 12 Dec 2008 15:06

We recommend you either regenerate the content for the new membership provider tables, or to return to the previous 3.x version.

It is impossible to support the GUID and string types simultaneously, because mixed values (GUID and string) in the userid columns will result in errors when searching by the key.

There is no upgrade script for this purpose.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby lkeel » Fri 12 Dec 2008 18:50

So just to make sure I got this right... You make a fundamental data model change and don't provide an upgrade script? That is VERY disturbing.

Thanks for all the help.
lkeel
 
Posts: 12
Joined: Fri 15 Feb 2008 14:49

Postby lkeel » Fri 12 Dec 2008 19:58

For all of the other user's out there that are going to run into this same problem, I thought I would provide a better solution that "down grade and use old version". Here is a script that can be run against the previous version and will upgrade to the latest schema and data model.

Thanks for all of your help Devart. :x This took me all of an hour to write and I am sure that I won't be the only one to use it.

Thanks,
Lee

--Create function used to generate UUID
CREATE OR REPLACE FUNCTION uuid_generate_v4()
RETURNS uuid
AS '$libdir/uuid-ossp', 'uuid_generate_v4'
VOLATILE STRICT LANGUAGE C;

--Drop constraints that will cause upgrade to fail.
ALTER TABLE aspnet_membership DROP CONSTRAINT aspnet_membership_userid_ref;
ALTER TABLE aspnet_profiles DROP CONSTRAINT aspnet_profiles_userid_ref;
ALTER TABLE aspnet_usersinroles DROP CONSTRAINT aspnet_usersinroles_userid_ref;
ALTER TABLE aspnet_usersinroles DROP CONSTRAINT aspnet_usersinroles_roleid_ref;

--Upgrade function that will loop through all the userids and roleids and upgrade specific tables.
create or replace function upgrade_aspnet_tables() returns void
AS
$BODY$
DECLARE
user record; --Used to get information from aspnet_users table
role record; --Used to get information from aspnet_roles table
newid uuid; --Used to hold new uuid for user and role;
BEGIN

--Loop through all of the userids
for user in (SELECT userid from aspnet_users)
LOOP
--Generate a new uuid value for the userid
newid = uuid_generate_v4();
--update all tables with the userid in it
update aspnet_users set userid=newid where userid=user.userid;
update aspnet_usersinroles set userid=newid where userid=user.userid;
update aspnet_membership set userid=newid where userid=user.userid;
update aspnet_profiles set userid=newid where userid=user.userid;

END LOOP;
--Loop through all of the roles
for role in (SELECT roleid from aspnet_roles)
LOOP
--Generate a new uuid value for the roleid
newid = uuid_generate_v4();
--update all tables with the roleid in it
update aspnet_roles set roleid=newid where roleid=role.roleid;
update aspnet_usersinroles set roleid=newid where roleid=role.roleid;

END LOOP;

END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

--Call the upgrade function
select upgrade_aspnet_tables();

--Recreate all of the constraints that were dropped in beginning
ALTER TABLE aspnet_usersinroles
ADD CONSTRAINT aspnet_usersinroles_roleid_ref FOREIGN KEY (roleid)
REFERENCES aspnet_roles (roleid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE aspnet_usersinroles
ADD CONSTRAINT aspnet_usersinroles_userid_ref FOREIGN KEY (userid)
REFERENCES aspnet_users (userid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE aspnet_membership
ADD CONSTRAINT aspnet_membership_userid_ref FOREIGN KEY (userid)
REFERENCES aspnet_users (userid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE aspnet_profiles
ADD CONSTRAINT aspnet_profiles_userid_ref FOREIGN KEY (userid)
REFERENCES aspnet_users (userid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;

--Create new tables
CREATE TABLE aspnet_sitemap (
nodeid INT,
applicationname VARCHAR(255) NOT NULL,
url VARCHAR(4096),
name VARCHAR(255),
description VARCHAR(512),
roles VARCHAR(512),
parentnodeid INT DEFAULT NULL,
PRIMARY KEY (nodeid, applicationname),
CONSTRAINT aspnet_sitemap_nodeid_fk FOREIGN KEY (parentnodeid, applicationname) REFERENCES aspnet_sitemap(nodeid, applicationname)
);

CREATE TABLE aspnet_webevent_events (
eventid CHAR(32) PRIMARY KEY DEFAULT '',
eventtimeutc timestamp NOT NULL,
eventtime timestamp NOT NULL,
eventtype VARCHAR(256),
eventsequence DECIMAL(19, 0) NOT NULL,
eventoccurrence DECIMAL(19, 0) NOT NULL,
eventcode INT NOT NULL,
eventdetailcode INT NOT NULL,
message VARCHAR(1024),
applicationpath VARCHAR(256),
applicationvirtualpath VARCHAR(256),
machinename VARCHAR(256),
requesturl VARCHAR(1024),
exceptiontype VARCHAR(256),
details text
);

CREATE TABLE aspnet_personalization (
userid VARCHAR(40) DEFAULT NULL,
path VARCHAR(255) DEFAULT NULL,
applicationname VARCHAR(255) DEFAULT NULL,
personalizationblob bytea DEFAULT NULL,
CONSTRAINT aspnet_personalization_userid_fk FOREIGN KEY (userid) REFERENCES aspnet_users(userid)
);
lkeel
 
Posts: 12
Joined: Fri 15 Feb 2008 14:49


Return to dotConnect for PostgreSQL