One application with multiple databases

[olu]

Please can anyone one help or point me in the right direction. I have an application in nettalk where by the application owner registers their new client. but now i want when the application owner registers their client, each client gets a username and password but also want the system to create a new database for that client.So that when they login, they can only see their data and use their database. And would happen for every client they create.

[kevin plummer]

Please can anyone one help or point me in the right direction. I have an application in nettalk where by the application owner registers their new client. but now i want when the application owner registers their client, each client gets a username and password

> what I have is a master DB with all users and coys. I make user+coy unique so multiple coys can have say Admin as a user. I filter the coys list when a user logs in so they can only see their choice.


but also want the system to create a new database for that client.


> if using SQL just run the TSQL to create a new DB. If using TPS just copy blank data from a master.

So that when they login, they can only see their data and use their database. And would happen for every client they create.

[rainerwallenius]

Hi,

I did start a project with filtering all data with companyId as first element but it did get a bit clumsy and complicated (and with
my skills also risky - what if the filter fails and displays all data?)

Instead, when  a new customer (with one or more users) register, the customer company gets a unique database path.
At this point the necessary directories get crated and basic data is copied.

I set the path (at login) in a sessionvariable and then let Webhandler rename all tables like "path" & '\mytablename".

I sure hope this technique also workds with SQL, just by letting Webhandler rename Glo:Owner...

:: rainer

[Bruce]

Hi Rainer,

yes, you've got the preferred approach. Create multiple databases, and set glo:owner in the ProcessLink method in the WebHandler procedure to whatever is valid for that user.

just make sure glo:owner is marked as THREADed.

cheers
Bruce

[olu]

Hi,
   Thanks for pointing me in the right direction. Just got one or two issues now. With this system this clients get put into packages, and there are some general configuration settings that applies to every client on the system.

So the question is do I need to create two Glo:Owner and Glo:Owner2. One for the initial login to pick up client information, package information and the general configuration and the other for the path of each client database?

[kevin plummer]

no as you are setting the glo:owner in the process link embed. you just need to know what part of your process your are at for the user so you can set them to the right values. Remember it is a global threaded variable.

[olu]

Hi Kevin,
     But I would need the client to login, so that I can get their database details from their client file to set path. We the processlink be called again after they login?

[Bruce]

Olu,

if you are getting data from 2 different databases - like a shared login database, followed by a client specific database, then I would definitely use 2 global variables.
Indeed if you have tables from any number of databases, It's usually easiest to have a separate connection variable ("owner") for each database.

Cheers
Bruce

[kevin plummer]

I use a shared master login DB to login before connecting to the client DB with 1 glo:dbowner that I set in the process link embed. It works but maybe my code would have been simpler with 2 but then it may have been a pain to the dbowner in the dictionary.

[olu]

Hi Kevin,
     So do you handle a logged in user that decides they want to change their username or password?

[kevin plummer]

Password change by the user no problem. Users are controlled by an Admin so the Admin could change the user login if they wanted. This would then propagate through the clients DB's.

[olu]

I have got it going now so when I create a customer file which in turn creates a new database for that client by creating a whole new database and I have added the tables by executing some predefined scripts. After I do this my app, just stops working and cannot find any data. everthing just goes empty . Then I have to logout and log back in to start working again. Please do you know what moght be causing this?

[Stu]

Hey Kevin,

Got a question for you re this thread.

From what you say above .. You've got a MASTER db, which houses the clients + user logins, then you change the dbowner value based on who logs in.

My question is, the MASTER db .. I'm guessing it looks exactly as the Client databases, except most of the tables are empty, except say CLIENT and LOGINS.

And then the Client databases, they are the same clarion dct, except that the CLIENT and LOGINS tables would be empty?

Cheers,

Stu

[kevin plummer]

Hi Stu,

From what you say above .. You've got a MASTER db,

> correct

which houses the clients

> actually I use a TPS file for this which sits in the exe folder.

 + user logins,

> correct. I use a combination of userlogin and company id to avoid any duplicate logins

then you change the dbowner value based on who logs in.

> correct - I store this in the tps file and save in the sessionQ

My question is, the MASTER db .. I'm guessing it looks exactly as the Client databases, except most of the tables are empty, except say CLIENT and LOGINS.

> correct

And then the Client databases, they are the same clarion dct, except that the CLIENT and LOGINS tables would be empty?

> they could be but I store other information in the Users Table that is specific to a DB as some companies have multiple DB's and some users have access to more than one DB but may have different roles etc.

[Stu]

Thanks Kevin, champion! Very very helpful.

So that spurs me on to a couple more questions.

1. With your sql files .. Do you have one that has the Mars+.. stuff on it, that you purposely open once the dbowner value is set?

2. Pretty sure I read that you use FM3 (maybe in another thread) .. If you do, how do you handle upgrades when the db structures have changed?
  - My thinking atm is to create a separate app which cycles through the tps file company dbowner values and runs the upgrades.

Cheers,

Stu