Where is the outsystems database instance defined?

Where is the outsystems database instance defined?

  
All the entities of my test projects are getting dumped into the default  outsystems  database instance that was created inside my local Sql Server Express
engine at installation time.   Some important questions:

1. Where is the configuration definition of the fact that the repository is  outsystems  on my local Sql Server Express engine?   
2. Can I have different repositories, or does Everything I do inside Agile Platform have to be dumped inside that  outsystems  instance?
3. I created an Entity/Table called  ASSIGNMENTS.   Inside the  outsystems  instance, it's called:     OSUSR_3L8_ASSIGNMENTS.    It might seem unimportant, but some of my customers - and colleagues - are going to have a problem with that.    Comments?

Frank -

1. In your Start Menu, go to the Configuration Tool, and it can let you change what DB server & instance it is pointing at.
2. To the best of my knowledge, a single OSAP install has to point to the same server, so you couldn't have some stuff go to one server and other stuff go to another (without clustering the servers in a manner transparent to OSAP).
3(a). If having a level of control to the point where they want tables named a certain way is going to be a problem, then this isn't the tool for them. It's not *just* the fact that whether or not they can change this is something they can deal with. It's the entire mentality behind it. OSAP allows you to not worry about these kinds of details, but at the same time, you have to accept that it will do things its own way. It's not just DB names, it is a *lot* of stuff. Not everyone is comfortable with that. For those that aren't, they tend to have a tough time accepting the other stuff that OSAP does for them too.
3(b). That being said... you can hack it up if it's an absolute deal breaker, with a few caveats:
i. You need to make the change after the initial deployment to a server (subsequent deploys are fine).
ii. It's ugly, and a total, complete violation of how OSAP likes to do things.
If this is something you STILL want to do (WARNING: don't hold me accountable if this goes horribly wrong in the short term or the long term!), you can go into dbo.ossys_Entity, and change the "PHYSICAL_TABLE_NAME" value to match the name of the table you want, and then rename the table. Giving IIS and the OSAP services a restart may well be a good idea after you do this. Likewise, you can edit dbo.ossys_Entity_Attr to change the column mappings.

I don't mean to sound harsh... I know that you aren't saying, "gee, this sucks!" but you are trying to work with others who might not see it things the way your or I do. OSAP is designed so that you aren't looking at this level of detail. One reason why OSAP is so insanely productive is because you don't need to mess with these details. When I first saw the thing, I revolted. Reality was, I thought I needed that control, because every system I used previously overpromised and underdelivered, and I was forced to wade through this stuff by hand. A few years ago, I was working with a company that sold us a home brew code generation system (like a wannabe OSAP), and I found myself using SQL Server Profiler to find out what SQL was being used, to fix the problems in the data manually caused by their screwy code. It was a nightmare. I've been working in one form or another in this industry close to 20 years now... and I've been around it for 25+ years. I am well familiar with all of the broken promises of systems that swear to take the need for control away... OSAP is the first and only tool I've ever found that delivered on those promises (I have seen some systems, like Alpha Five, that do a great job at simplifying how you get the control, too). It's easy, natural, and normal to be skeptical and not give it full trust, but I tell you, if you close your eyes and take the leap, you will NOT look back.

Now, I am going to say this... despite OSAP's best intentions, I have I found myself rooting around in the DB making changes manually. It is *very* rare that I feel the need. For example, I originally defined a column as being text with a length of 5,000. This causes all sorts of problems (like ToLower() and the equal operator to not work) because it got defined at ntext instead of nvarchar. Fair enough. It turned out that my app didn't really need 5,000 characters anyways... so I made a dummy column of nvarchar(2000), copied the data into it, dropped the original column, re-added it as nvarchar(2000), copied the data back and dropped the dummy column. Why? Because the deployment tool was not thrilled about changing it from text(5000) to text(2000). And you know what? I considered this a bug in the system; it should be able to do this on its own if there was no data longer than 2,000 characters. Every now and then I'll manually peek into and even edit data, because I haven't build an admin screen yet for that data. But it's almost always faster/easier to build the admin screen than it is to work in SQL Server Management Studio! Another time, ages ago, I mangled an install (I didn't read directions) and I had to play with the DB manually and config files to straighten it out.

So... yes, I have had a need on a very rare occassion to do something that was against the OSAP principles, but it is pretty rare. If this is something that you will find yourself doing on a very regular basis, one of three things will need to happen:

1. Figure out what the underlying requirement is; it is almost never technical. The need to have this kind of control over the DB tables is a business/cultural problem. As yourself this: what is the customer going to do with the tables, huh? The fact that they want the table name to be easy to work with is a HUGE RED FLAG! It means that they are going to be working directly with this data! DANGER DANGER DANGER Why? OSAP has a ton of transactioning things built in. Do you REALLY want them working about the transactions? Do you REALLY want them carousing through the data? And let's pretend that data integrity isn't an issue... what happens when you make a change to the entity structure in Service Studio, and the underlying table structure changes? WHOOPS. The fact that they are even *thinking* about directly accessing the data is a red flag that they are about to make a mistake. This isn't an OSAP-specific thing, either. No developer should be thinking about direct DB access when there is an data access layer that's been written, because then you never know if your external app is going to screw up the data access layer... whether it's store procs, views, a Web service, some .NET components, COM DLLs, Java JARs, or whatever... once a DAL has been written, YOU ALWAYS WORK WITH THE DAL. In this case, OSAP is the DAL. If they want DB access, they need to work through an interface you define in OSAP (make a Web Service and expose it!).

2. If they still are under the belief that their itch can only be scratched with direct DB access ("thar be dragons here..."), fine. Make them a view with the name they find acceptable. I highly recommend that this be a read-only view, because trying to work in Service Studio with an eye towards not having the underlying DB structure change is pretty silly. It takes the "Agile" out of "Agile Platform" when you are afraid to so much as change an entity or attribute out of fear of what may happen. That being said... the ossys_Entity_Attr and ossys_Entity tables tend to be additive, not changeable. For example, change an entity name, and the underlying table name will remain the same on existing deployments (but will get the "right" name if you do a fresh deploy to a clean system). By using a view of stored procs to give them access, you will have a "fail fast, fail hard" scenario where you know quickly if something's changed for the worst.

3. Don't use OSAP. If the clients absolutely insist on not working with the system's principles on a regular basis, then don't use the system. It's not for them if they can't get comfortable with it. If they are willing to give up control over things like the DB table name, in exchange for development in 10% - 25% of the time it takes to do the same work in ASP.NET or J2EE, then they should be using OSAP. If shaving *80%* of the development time is not worth the "cost" of losing control over things like DB naming... then OSAP is not for them.

J.Ja
@ Justin - kudos for a great overview

@Frank

2 - Not sure if this is what you are looking for Multiple Database Catalogs and Schemas
3 - Take a look into Forum Post: Configuring the physical table name format for Entities

Also this utility eSpace might come handy: Forum Post: Entity to physical table mapping

Cheers,
Tiago Simões

Justin,

Outstanding post.   You have material there for a whole article.   Thank you so much for taking the time.

A few follow up comments:

> a single OSAP install has to point to the same server, so you couldn't have some stuff go to one server and other stuff go to another (without clustering the servers in a manner transparent to OSAP).

- If true, I'd say that's something that should change.  I Should have a way to partition.

- Your path from revolting to not looking back - is a great story.

> DANGER DANGER DANGER

-  :)   I agree.   I preach this all the time.  Unfortunately, I have customers and colleagues who won't hear the message, and I have to deal with that someway.
   Putting up Views is a reasonable solution when necessary.

My net: nothing would be better than to be able to earn a living with a tool like this - with 100% of the entities of 100% of the apps managed completely inside the system.  I think that's what most or All of us want.   But, there Are real world customer scenarios that don't match to that, and I guess in those cases, the
hard decisions need to be made.   

I would never allow customer access to write/update anything directly.   I Do have a colleague that throws that wrench around  :)   but, we deal with
it the best we can.    :)       But as far as read access, it's a very common scenario in my world to:
- Build a system, controlled by services and a good DAL.
- Give the customer starter summaries of data in the form of charts, grids, etc.
- Give the customer read-only views so that they can do their own  Data Mining.    That's a very common, important scenario that will never go
away in the worlds I live in.

Thanks again for the superb post.   Time for you to turn that into a full fledged magazine or blog article.    :)

Regards,
Frank



> 2 - Not sure if this is what you are looking for Multiple Database Catalogs and Schemas

Tiago - yes, that's it.   Thank you very much.