shared web app

Scriptcase 8 Discussion
#1

hello everyone.
Information on how you solved the following scenario, then that is definitely a scenario used by many of you.

  • web app shared by different customers.
  • Database with tables common to all customers, database name: <appname>_admin (eg.: label_admin):
    Ex: table ‘menu’, countries, provinces, other common tables (not editable by customers), sec_apps, sec_groups (of course, logically, the groups are the same for everyone), sec_groups_apps
  • Database data owners for each customer: table1, table2, … tablex, sec_users, sec_user_groups
    All work properly in development, but in production can not work because the database connection ‘label_admin’ (declared in the FROM clause, see below) can not occur.
    In OnValidate of app_login, change the connection to the customer’s database and event OnValidateSuccess app_Login in the active connection, of course, is that of the client:
    $sql = “SELECT app_name,
    priv_access,
    priv_insert,
    priv_delete,
    priv_update,
    priv_export,
    priv_print,
    group_id
    FROM label_admin.sec_groups_apps <-------
    WHERE group_id IN
    (SELECT
    group_id
    FROM
    sec_users_groups
    WHERE
    login = '”. [usr_login] ."’)";
    sc_select(rs, $sql);

In production: error in the database connection label_admin (access denied to the user with the credentials to the database client)
the rest of the web app works correctly.
I have not encountered further problems or issues.

How do you solve the situation?
or
how you have developed a web app can be used by multiple customers with proprietary data and database with common tables?

I have now solved by duplicating the table sec_groups_apps in each customer’s database, but … it’s not a good thing. With each new form, grid, etc, you must update ‘remember’ to copy the table.
If I do not find a better solution will develop a small app to copy it automatically, but logic would require to use that common! :slight_smile:

Thank You.
Ciao!
enrico ferretti

#2

sorry, last week was tough but i still think that i can’t understand where you wanna reach dude!

#3

[QUOTE=henry1311;34659]hello everyone.
Information on how you solved the following scenario, then that is definitely a scenario used by many of you.

  • web app shared by different customers.
  • Database with tables common to all customers, database name: <appname>_admin (eg.: label_admin):
    Ex: table ‘menu’, countries, provinces, other common tables (not editable by customers), sec_apps, sec_groups (of course, logically, the groups are the same for everyone), sec_groups_apps
  • Database data owners for each customer: table1, table2, … tablex, sec_users, sec_user_groups
    All work properly in development, but in production can not work because the database connection ‘label_admin’ (declared in the FROM clause, see below) can not occur.
    In OnValidate of app_login, change the connection to the customer’s database and event OnValidateSuccess app_Login in the active connection, of course, is that of the client:
    $sql = “SELECT app_name,
    priv_access,
    priv_insert,
    priv_delete,
    priv_update,
    priv_export,
    priv_print,
    group_id
    FROM label_admin.sec_groups_apps <-------
    WHERE group_id IN
    (SELECT
    group_id
    FROM
    sec_users_groups
    WHERE
    login = '”. [usr_login] ."’)";
    sc_select(rs, $sql);

In production: error in the database connection label_admin (access denied to the user with the credentials to the database client)
the rest of the web app works correctly.
I have not encountered further problems or issues.

How do you solve the situation?
or
how you have developed a web app can be used by multiple customers with proprietary data and database with common tables?

I have now solved by duplicating the table sec_groups_apps in each customer’s database, but … it’s not a good thing. With each new form, grid, etc, you must update ‘remember’ to copy the table.
If I do not find a better solution will develop a small app to copy it automatically, but logic would require to use that common! :slight_smile:

Thank You.
Ciao!
enrico ferretti[/QUOTE]

OK, the way that we do this:

We have ONE database for user login. We have added some hidden fields to the sc_users as follows:

sql_server
sql_user
sql_password
sql_database

These fields contain which of the 80 identical databases should be used for this particular user.

It is pretty easy to implement, just split your database into two pieces, with the login related stuff in one (“system”), and all the other stuff into another database (“db1”). Create two connections, “system” and “account”. Set up all the login related apps to use the “system” connection and all the other apps to use the “account” connection. At login time, once the validation is complete, do a sc_connection_edit() to change to the “account” connection to point to the user’s database, then sc_redir to your main menu.

The majority of the work is in dynamically creating new databases. For testing, you can simply copy “db1” to “db2”. We keep a new, clean, starter database as “db0”, and copy that as needed to create new databases.

Hope this helps somewhat,

Dave

#4

good idea Dave
thanks for the reply
Ciao
enrico

#5

Thanks Dave for your assistance with this topic. Your last post has been very helpful.

I have done exactly what you mention above, and apart from a problem with users changing their password, when a weird message box opens asking to confirm the user of the change, it seems to be working OK.

Just one point though. I asked support yesterday about a related issue, about images on pdf reports that would be different for each database user, and they suggested I use sc_connection_new and not edit? Do you have any idea why they would say that? I really do not fully understand the difference between edit and new so this is the reason for asking.

Thanks

Tony