I have a fledgling application in delphi XE, using datasnap.
very new to this whole client/server stuff.(existing app accesses database directly)
my new app will be a web server with mysql database and clients written in whatever web clients are written in (most likely java)
so, looking at remobjects/data abstract solution, but can’t seem to ‘get off the ground’.
Understand most of the concepts.
client talks to my service app, my service app talks to the database.
somehow.
Need a simple ‘how to, step by step’ on how to do a query based login for roles, and I am hoping that it will get me started in the right direction.
My application has a multi tenant database.
so my login procedure is in steps -
from the login email and password, log into my subscriber database to get the tenantid.
from that tenantid/email combination, login into the ‘real’ database, using the tenantid as the database user
because of how my multitenant triggers work, the connection has to be ‘really’ logged into the database. Using db connections, I can do that by changing the login user. How can I accomplish this in data abstract? where is the connection string located, and how can I modify it at will (for example if someone in my company smartens up and changes the mysql port from it’s default settings?)
run queries to determine the user role (in some cases, login will be an employee of our tenants, in some cases, the login will be a customer of our tenents, and in some cases, could be both/neither), based on the provided email. If data isn’t found in one complicated join query, then it will run another complicated join query. in either case, it will then run 3 or complicated join queries until it determines all the roles allowed the login user.
4)in future would work out how to use single sign on, but one step at a time.
so, in my datasnap app, using Unidac, I have 3 connections, and run a few queries, set session roles, etc.
How do I accomplish the same thing using dataabstract?
I have looked at several examples for login, but about all I’ve seen is basically loginValid := username = password.
also -
I used the wizard to create a data abstract vcl project, and it put the tables into the client.
how do I get them into the server? Do I need to get them into the server? (since I am planning on putting business logic into the server, I’m betting I need to get them there. somehow)
because of how my multitenant triggers work, the connection has to be 'really' logged into the database. Using db connections, I can do that by changing the login user. How can I accomplish this in data abstract? where is the connection string located, and how can I modify it at will (for example if someone in my company smartens up and changes the mysql port from it's default settings?)
Connection manager holds string connection, You can change at runtime, just load it from a ini file, or whatever else option you like.
3) run queries to determine the user role (in some cases, login will be an employee of our tenants, in some cases, the login will be a customer of our tenents, and in some cases, could be both/neither), based on the provided email. If data isn't found in one complicated join query, then it will run another complicated join query. in either case, it will then run 3 or complicated join queries until it determines all the roles allowed the login user.
You can include that querys on the schema and call from the server. Example:
var
lConnection :IDAConnection;
lCmd : IDASQLCommand;
begin
lConnection := ConnectionManager.NewConnection(fDBConexion);
lCmd := Schema.NewCommand(lConnection,
‘Get_Role’,[‘IDUSER’],[aUser]);
How do I accomplish the same thing using dataabstract?
Cant follow you.
I used the wizard to create a data abstract vcl project, and it put the tables into the client.
how do I get them into the server? Do I need to get them into the server? (since I am planning on putting business logic into the server, I'm betting I need to get them there. somehow)
I hope helps the previous sample to call schema from server.
Hi, I have been thinking about this and I have some possible answers…
My application has a multi tenant database.
so my login procedure is in steps -
from the login email and password, log into my subscriber database to get the tenantid.
My first thought is why not have in the same database?
But the issue is two databases, yes? - so have the two schema in the one server - this is usual practice... One login schema and one data schema. But for you - In the login schema you have two connections - One for Tenant lookup, and one for the login check.
2) from that tenantid/email combination, login into the 'real' database, using the tenantid as the database user
because of how my multitenant triggers work, the connection has to be 'really' logged into the database. Using db connections, I can do that by changing the login user. How can I accomplish this in data abstract? where is the connection string located, and how can I modify it at will (for example if someone in my company smartens up and changes the mysql port from it's default settings?)
Firstly - The connection string is in the schema, but saved externally to a file if you right click on the connection to get menu - you can then add the file your project and edit as xml.
Secondly, Create a login schema, and add two connections - Create two commands, 1 for getting tenant id, the other will check login - set the connection in each on the statement. Now the schema will execute to two databases. Once The login comes back OK as success set required fields in UserInfo and return true as result.
Now client has session.
3) run queries to determine the user role (in some cases, login will be an employee of our tenants, in some cases, the login will be a customer of our tenents, and in some cases, could be both/neither), based on the provided email. If data isn't found in one complicated join query, then it will run another complicated join query. in either case, it will then run 3 or complicated join queries until it determines all the roles allowed the login user.
Now you can this in the login - or now you have session call dataservice to get roles and then from here you access all your other tables until logout.
4)in future would work out how to use single sign on, but one step at a time.
Yes, you can but get the first bits working. Get used to DA!
so, in my datasnap app, using Unidac, I have 3 connections, and run a few queries, set session roles, etc.
How do I accomplish the same thing using dataabstract?
I have looked at several examples for login, but about all I've seen is basically loginValid := username = password.
Think I answered already above.
also -
I used the wizard to create a data abstract vcl project, and it put the tables into the client.
how do I get them into the server? Do I need to get them into the server? (since I am planning on putting business logic into the server, I'm betting I need to get them there. somehow)
OK, your in disconnected architecture here, so on the server side you have dataservice schema with your tables - the tables have DA SQL inside or you call stored procs etc. On client side you have the schema replicated out as your individual tables - ready to be opened after you set any required parameters - and this will only work once a successful login is achieved the security from here to talk to dataservice is taken care of via the session id.
Bit rushed in typing all this but you should get the picture… Just get the login/logout working then go from there.
I think I’m starting to absorb.
My multi tenant scheme is setup such that there are 2 mysql schemas.
one with my tenant information, one with the data being modified per tenant.
For my tenant schema, the login is hard coded, so not seeing an issue with that.
In the data schema, each table has a view, limited by the login ID (which is the tenant)
Because of this, the database login has to be defined per user, so while the general settings can be stored/modified externally, the mysql login can’t, it has to be set per session.
What I am understanding so far (and please correct me if I am wrong)
Server application is the interface between ‘out there’ and ‘in here’, where the data is. Allows a huge variety of connection methods (http, tcp, etc). External functions can be defined/called (calculate order total can be a define function, called from an external source(somehow))
Data Abstract contains the schema of the database initially, plus allows addition of views that are defined external to the database (huge plus), as well as calls to stored (on the database) procedures.
the connection to the database is defined in the connection string, and can be stored externally. It is used by the DA schema to do the actual database connections
Question(1)if this is correct, then how do I define/modify the database connection string at run time, per session? (IOW, getting the database and port from the string, but changing the loginID/password properties based on tenant login?)
Question (2) Can I use standard(UniDac) database connections as well for backend logic (Calculating order totals, for example)
Question (3) Can the front end developer do join queries from the tables that are defined in the DA schema?
Yes
Q1. Pretty sure you have only 1 login for DA schema, then have a user table with email/password - then log specific table/data updates. I don’t see the point in flip flopping a connection for sql for the user - maybe ok on client/server 2 tier - but not 3 tier - the schema is multi threaded to handle many connection/requests at once.
Q2. Find all drivers on this page http://www.remobjects.com/da/delphi.aspx
Q3. You can do SQL on the fly on the client, and/or use the predefined ones you set up in the schema. You can also use predefined ones in the schema but specify the WHERE clause from the client as well. These options plus others are Allowed on the service implementation (DataService_implementation) on the server - options like ‘AllowDynamicSelect’, ‘AllowDynamicWhere’, ‘AllowExecuteSQL’ etc…
conniemh5swcom said: so my login procedure is in steps - 1) from the login email and password, log into my subscriber database to get the tenantid. 2) from that tenantid/email combination, login into the 'real' database, using the tenantid as the database user
You could use IMultiDBLoginService interface for such caseL. Change LoginService ancestor in Service Builder from SimpleLoginService to MultiDBLoginServiceV5. Then manually edit LoginService_Impl file changing TLoginService class description :
{ TLoginService }
TLoginService = class(TMultiDbLoginServiceV5, ILoginService)
…
end;
onLogin event description should be changed too - aConnectionName parameter is added, so you could check your credenials and save in session variables necessary information:
procedure TLoginService.MultiDbLoginServiceV5Login(Sender: TObject;
const aUserID, aPassword, aConnectionName: UTF8String;
out aUserInfo: UserInfo; var aLoginSuccessful: Boolean);
var con:IDAConnection;
ds:IDADataset;
begin
con:=Self.ConnectionManager.NewConnection(aConnectionName);
ds:=con.NewDataset(‘select Tenantid from emails where email ="’+aUserID+
‘" and password= "’+aPassword+‘"’);
ds.Open;
aLoginSuccessful:= not ds.IsEmpty;
if aLoginSuccessful then begin
aUserInfo := UserInfo.Create;
aUserInfo.SessionID := UTF8String(GuidToAnsiString(ClientID));
aUserInfo.UserID := ds.Fields.Fields[0].AsString;
Of course you must call login method from he clien wih three parameters : UserID, Password and ConnectionName.
conniemh5swcom said: Question(1)if this is correct, then how do I define/modify the database connection string at run time, per session? (IOW, getting the database and port from the string, but changing the loginID/password properties based on tenant login?)
You should parse connection string itself replacing UserID and Password set in Schema by yours ones. You could also create new connection on the server with specified userID and Password, but this won’t be saved in ConnectionMaager:
var con:IDAConnection;
begin
con:=Schema.ConnectionManager.NewConnection(aConnectionName, true, userID, Password);
…
end;