CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Postgres db changes

Print topic Send  topic

Author Message
kirchaj

Posts: 215
Posted: 04/25/2013, 7:03 PM

I have two Postgres DBS with tables set up like with separate apps in CCS

Account
Public
Tables

Portfolio
Public
Tables

I want to move to a new setup like

Accountability
Account
Tables
Portfolio
Tables

I can physically get this done but I'm not sure of all the implications this will have with CCS. Anyone done this? Any gotchas that I need to watch for? Suggestions????

Thanks
TK

View profile  Send private message
Lucius

Posts: 220
Posted: 04/26/2013, 10:16 AM

Hi,

All depends on the referencing you are using now, versus new setup. If the Tables and their connections (for example by foreign keys) remain the same, then your apps should still work.

If you have added some new fields to tables, again it should work. If you removed some, changed their names then most likely the apps will generate errors and will not work properly.

Only way to be sure - copy the DB, setup test environment with current apps and change the DB setup. Check all pages and functionality.
View profile  Send private message
kirchaj

Posts: 215
Posted: 04/26/2013, 12:11 PM

All of the tables and structures will remain the same but I will be changing from 2 physical databases with a public schema to 1 physical database with an account and portfolio schemas.

I can setup the odbc client on my machine but don't know how to setup the server as it doesn't have the Same options to select the schema.
View profile  Send private message
Lucius

Posts: 220
Posted: 04/27/2013, 5:01 AM

So in this case I would guess that it will not work - public schema is the default schema, so queries which do not have the schema explicitly used like:

SELECT field FROM table WHERE something

By default fall back to "public" schema, so above query is the same as:

SELECT field FROM public.table WHERE something

The first query falls back by default to public schema, but unless you configure server accordingly it will not fall back to other schemas you might have.

In CCS (in DB connection properties) you can select active schema, so that would be one solution, however last time I checked, a very long ago so it might have been fixed, it was not working properly.

Other solution would be to rewrite queries so they use schema in every table reference - a lot of work.

Last solution would be to use Schema Search Path, to point to another schema if fallback to "public" fails. This might be the easiest solution if you have such privileges on DB server: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html

However all solutions depend on what you need to achieve with new schema setup, for example it might not be a good idea to use Search Path if you set up schemas for better DB security.
View profile  Send private message
kirchaj

Posts: 215
Posted: 04/29/2013, 12:45 PM

Thanks Lucius

Those are some of the issues I was afraid I would run into. I am not so worried about security as I am the ability to do searches and queries across the schemas. I cannot do that now with separate databases.

I may just have to live with it and keep them separate. :(

TK
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.