[SQL] How do I connect postgres table structures and view structures to an existing svn repository?
Hi, How do I connect postgres table structures and view structures to an existing svn repository? Thanks, -- John J. Mitchell
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
On Wed, 1 Aug 2007 10:07:31 -0400 "John Mitchell" <[EMAIL PROTECTED]> wrote: > How do I connect postgres table structures and view structures to an > existing svn repository? I'm not sure that I understand the connection between SQL and SVN. Can you be a bit clearer about what you are trying to do? -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
I am trying to store schema definitions in version-control which I can do by saving the definition and then importing into svn, but I would like it to be automatic , so that when an update occurs to a table or view within postgres then that table or view is flagged within svn. This would be similar to what I currently do with source code that I have for a web app within eclipse. On 8/1/07, D'Arcy J.M. Cain <[EMAIL PROTECTED]> wrote: > > On Wed, 1 Aug 2007 10:07:31 -0400 > "John Mitchell" <[EMAIL PROTECTED]> wrote: > > How do I connect postgres table structures and view structures to an > > existing svn repository? > > I'm not sure that I understand the connection between SQL and SVN. Can > you be a bit clearer about what you are trying to do? > > -- > D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > -- John J. Mitchell
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
On Wed, 1 Aug 2007 11:45:21 -0400 "John Mitchell" <[EMAIL PROTECTED]> wrote: > I am trying to store schema definitions in version-control which I can do by > saving the definition and then importing into svn, but I would like it > to be automatic , so that when an update occurs to a table or view within > postgres then that table or view is flagged within svn. This would be > similar to what I currently do with source code that I have for a web app > within eclipse. Not really an SQL question, in fact it is barely a PostgreSQL question at all but I will try to give a pointer. I don't know of any automated tools. I would suggest running a cron script that dumps the database and then does an svn commit. If there are no changes then the commit will do nothing. If there are changes then svn will be updated. Don't forget to pipe a log message into svn (-F or --file option) and you may need to check for irrelevant changes in the dump before calling svn. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
On 8/1/07, John Mitchell <[EMAIL PROTECTED]> wrote: > I am trying to store schema definitions in version-control which I can do by > saving the definition and then importing into svn, but I would like it > to be automatic , so that when an update occurs to a table or view within > postgres then that table or view is flagged within svn. This would be > similar to what I currently do with source code that I have for a web app > within eclipse. OK, the first decision point is whether you want to store whole schemas and blow away the old one each time, or if you want to store incremental changes. I recommend creating an initial schema of version 1.0, and then creating delta files after that as you change your schema. Your first schema can have a table in it called schemaversion(version text primary key, notes text); with an entry there something like '1.0','initial schema'. Each delta .sql file you create would then have an insert into the schemaversion table with the version number it brings the schema up to and a note of possibly the change ticket or bug number or even a brief overview of the changes it implements. That way you can have a production schema at some stable version, say 1.0.8, while your development schema can move ahead to 2.0.4. then you can simply apply the delta files to take you from 1.0.8 to 2.0.4 when you want to upgrade production. Store each of these files in some directory in svn and you're done. My point being that you need to drive the changes from the svn side, not the database side. I've seen no reliable way to do it otherwise, but would love to be pleasantly surprised. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
On Wed, 1 Aug 2007 11:46:11 -0500 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > On 8/1/07, John Mitchell <[EMAIL PROTECTED]> wrote: > > I am trying to store schema definitions in version-control which I can do by > > saving the definition and then importing into svn, but I would like it > > to be automatic , so that when an update occurs to a table or view within > > postgres then that table or view is flagged within svn. This would be > > similar to what I currently do with source code that I have for a web app > > within eclipse. > > My point being that you need to drive the changes from the svn side, > not the database side. I've seen no reliable way to do it otherwise, > but would love to be pleasantly surprised. You are right. It is always difficult to add structured control to unstructured development methods. The method that I suggested was the best I could think of to handle a database that is changed ad hoc with no design work beforehand. If you work from the design side first you can create schema documents that feed your database creation and use svn to save those versions and document exactly why things changed. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?
On Aug 1, 2007, at 1:17 PM, D'Arcy J.M. Cain wrote: On Wed, 1 Aug 2007 11:46:11 -0500 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: On 8/1/07, John Mitchell <[EMAIL PROTECTED]> wrote: I am trying to store schema definitions in version-control which I can do by saving the definition and then importing into svn, but I would like it to be automatic , so that when an update occurs to a table or view within postgres then that table or view is flagged within svn. This would be similar to what I currently do with source code that I have for a web app within eclipse. My point being that you need to drive the changes from the svn side, not the database side. I've seen no reliable way to do it otherwise, but would love to be pleasantly surprised. You are right. It is always difficult to add structured control to unstructured development methods. The method that I suggested was the best I could think of to handle a database that is changed ad hoc with no design work beforehand. If you work from the design side first you can create schema documents that feed your database creation and use svn to save those versions and document exactly why things changed. Another good idea is to include in these delta (or migration) scripts the necessary sql to rollback the change. Then it's not too hard to write a tool that you can give db connection params and a version # to sync to. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Authentification failed
Hello everybody!! I'm trying in SUSE to connect to a postgres db and this is the error: Ident Authentification failed for user <> I'm already created the user with createuser root, but the error persist, I would aprecciate some help, thanks in advanced ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Authentification failed
On 8/1/07, Judith <[EMAIL PROTECTED]> wrote: > Hello everybody!! > > I'm trying in SUSE to connect to a postgres db and this is the error: > > Ident Authentification failed for user <> > > I'm already created the user with createuser root, but the error > persist, I would aprecciate some help, thanks in advanced you need to change auth methods in your pg_hba.conf file: http://www.postgresql.org/docs/8.2/static/auth-pg-hba-conf.html basically identd means that you are who you are on the unix box. no -U flag, you can't log in as someone else. note that a "root" user is a bit of a mysqlism. The postgres user is the equivalent in postgresql. might you sudo su - postgres and try to connect? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Authentification failed
> I'm trying in SUSE to connect to a postgres db and this is the > error: > > Ident Authentification failed for user <> > others will guide better, but for now, in pg_hba.conf # "local" is for Unix domain socket connections only local all all ident sameuser also, as postgres user, createuser this will let you into pg as yourself Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545433 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to INTERSECT
I don't know if this is more efficient but an alternative can be
something like this
SELECT t.id
FROM test t
JOIN test t2 ON t2.id = t.id AND t2.field = 'firstname' AND t2.value
LIKE 'jose%'
JOIN test t3 ON t3.id = t2.id AND t3.field = 'lastname' AND t3.value
LIKE 'kro%'
WHERE t.field = 'firstname' AND t.value LIKE 'andrea%'
Hope this helps
Andreas Joseph Krogh wrote:
On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote:
On Tue, 31 Jul 2007 17:30:51 +
Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
Hi all. I have the following schema:
CREATE TABLE test (
id integer NOT NULL,
field character varying NOT NULL,
value character varying NOT NULL
);
ALTER TABLE ONLY test
ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
CREATE INDEX test_like_idx ON test USING btree (id, field, value
varchar_pattern_ops);
Using INTERSECT I want to retrieve the rows matching (pseudo-code)
"firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
Why not:
WHERE (t.field = lastname AND t.value LIKE 'kro%')
OR (t.field = firsname AND (
t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
)
Not tested. If you're having performance problems is probably less
like that the INTERSECT is the problem with all those LIKE's in
there? Is t.value indexed?
Yes, as I wrote:
CREATE INDEX test_like_idx ON test USING btree
(id, field, value varchar_pattern_ops);
And I'm observing that it uses that index.
Your query doesn't cut it, let me try to explain what I'm trying to achieve:
Suppose I have the following data:
INSERT INTO test VALUES (1, 'firstname', 'andreas');
INSERT INTO test VALUES (1, 'firstname', 'joseph');
INSERT INTO test VALUES (1, 'lastname', 'krogh');
INSERT INTO test VALUES (2, 'firstname', 'andreas');
INSERT INTO test VALUES (2, 'lastname', 'noname');
The reason for why I use INTERSECT is that I want:
SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'kro%';
To return only id 1, and the query:
SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'non%';
To return no rows at all (cause nobydy's name is "andreas joseph noname").
Your suggestion doesn't cover this case.
--
AJK
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
---(end of broadcast)---
TIP 6: explain analyze is your friend
