Re: [GENERAL] Using database to find file doublettes in my computer
On 18 Nov., 07:40, [EMAIL PROTECTED] (Craig Ringer) wrote: -- Once paths is populated, extract duplicates: SELECT get_filename(path) AS fn, count(path) AS n FROM paths HAVING count(path) 1 INTO TEMPORARY TABLE dup_files; -- Creates UNIQUE index on PATH as well ALTER TABLE dup_files ADD CONSTRAINT PRIMARY KEY (path); -- Now build your side-by-side table of duplicates: SELECT p1.path, p2.path FROM paths p1 INNER JOIN paths p2 ON (get_filename(p1.path) = get_filename(p2.path)) WHERE EXISTS(SELECT 1 FROM dup_files WHERE fn = get_filename(p1.path)) AND p1.path p2.path; You can replace get_filename(fn) with appropriate code, but I'd write a quick SQL function marked IMMUTABLE to neatly wrap up the pathname extraction instead. Hi Craig, I have done the steps as you described. I have about 14000 files with an md5sum. Based on the full filename I have updated the md5sum in my base table 'ECADFiles'. With the following query I see about 2900 files that are available multible times: select Name, count(Pfad) As n from ECADFiles Group by Name having count(Pfad) 1 Using this query I see 13000 double files as a sum: select sum(n) from ( select Name, count(Pfad) As n from ECADFiles Group by Name having count(Pfad) 1) as temp Using the following query I get ~ 129000 records: select p1.Datei, p1.MD5Sum, p1.ID, p2.Datei, p2.MD5Sum, p2.ID from ECADFiles p1 INNER JOIN ECADFiles p2 ON (p1.Name = p2.Name) where EXISTS (select 1 from Datei where Name = p1.Name AND Anzahl 1) and p1.Datei p2.Datei I have expected a smaller amount of records due to the fact that for 4 files each available 2 times (sum = 8) I have 8 records in ECADFiles, but must have 4 in the above result. So for an average of 2 doubles I expected half the files from ECADFiles, because one is exactly right and the other is on the left. In general this results in about the same or less records than 13000 multible files. Why did I get these 129000 records ? I assume a rotating from files on the left to the right, thus about n - 1 times too much records. Thus I have tested this: select sum(n), sum(r) from ( select Name, count(Pfad) As n, count(Pfad) * (count(Pfad) - 1) As r from ECADFiles Group by Name having count(Pfad) 1 ) as temp But I got 259240. This is probably not correct. Testing that with the first left file to search on right I get the n occurences at all as of n double files. Assuming there are n - 1 too much, I have got my expected result by changing the last AND rule to the opposite: select p1.Datei, p1.MD5Sum, p1.ID, p2.Datei, p2.MD5Sum, p2.ID from ECADFiles p1 INNER JOIN ECADFiles p2 ON (p1.Name = p2.Name) where p1.Datei = E'C:\\drefsrechneralt\\Drefs511_2\\EAGLE\ \MW211.SCH' AND EXISTS (select 1 from Datei where Name = p1.Name AND Anzahl 1) and p1.Datei p2.Datei Gives 7 records with 7 different right files and the 8th on the left. select p1.Datei, p1.MD5Sum, p1.ID, p2.Datei, p2.MD5Sum, p2.ID from ECADFiles p1 INNER JOIN ECADFiles p2 ON (p1.Name = p2.Name) where p2.Datei = E'C:\\drefsrechneralt\\Drefs511_2\\EAGLE\ \MW211.SCH' AND EXISTS (select 1 from Datei where Name = p1.Name AND Anzahl 1) and p1.Datei p2.Datei Gives 7 records with 7 different left files and the 8th on the right. Any ideas how to remove these unwanted records ? This seems not to be easy, because I see problems araising when changing the data a cursor runs over. Thanks Lothar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using database to find file doublettes in my computer
Hi, I have a problem to find as fast as possible files that are double or in other words, identical. Also identifying those files that are not identical. My approach was to use dir /s and an awk script to convert it to a sql script to be imported into a table. That done, I could start issuing queries. But how to query for files to display a 'left / right view' for each file that is on multible places ? I mean this: This File;Also here C:\some.txt;C:\backup\some.txt C:\some.txt;C:\backup1\some.txt C:\some.txt;C:\backup2\some.txt but have only this list: C:\some.txt C:\backup\some.txt C:\backup1\some.txt C:\backup2\some.txt The reason for this is because I am faced with the problem of ECAD projects that are copied around many times and I have to identify what files are here missing and what files are there. So a manual approach is as follows: 1) Identify one file (schematic1.sch) and see, where are copies of it. 2) Compare the files of both directories and make a desision about what files to use further. 3) Determine conflicts, thus these files can't be copied together for a cleanup. Are there any approaches or help ? This is a very time consuming job and I am searching for any solution that helps me save time :-) I know that those problems did not arise when the projects are well structured and in a version management system. But that isn't here :-) Thanks Lothar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Am I overseen ?
Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Am I overseen ?
On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? Tanks, Lothar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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: [GENERAL] Am I overseen ?
On 18 Okt., 18:22, [EMAIL PROTECTED] (Scott Marlowe) wrote: On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. Just a copy of my mail. UML is only a hint, where my source is. I transform it to a big SQL query to be executed inside a C/C++ application. So no scripting available yet. Also no XML decoding to issue ODBC API commands to determine existing tables / columns. But I'm not that familiar with UML as to say what's the best approach. Transforming it to SQL queries is best for me, because the application model is stored in SQL database. UML is only a possible input to better model the app. ER diagramming tools that support XML export would also be an option. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. In my opinion the question was clear. How to alter table if the column not already inserted of later of given type. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). Lothar ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Re : [GENERAL] Am I overseen ?
I think this is not the way I should go. My application is a database prototype design tool that also is able to run the design because the designer it self is a design. So I did not only need a DDL to create the target application's database, also I need to feed a model into the system database that specifies the model at GUI level. (Data in general means) Therefore I have choosen the XMI / XML / XSLT approach to get SQL scripts at once. When there is a way to optionally add columns to existing tables via SQL I have very less development needs to get this working. If I do read the XML model into a DOM tree or use extarnal tools I probably have more to work on a solution. But thanks, it may help somehow. I'll have a look. Lothar Am 18.10.2007 um 19:41 schrieb Laurent ROCHE: Hi, You might want to have a look at DdlUtils that does similar things that you want to do: compare databases schemas using XML files and synchronising the schemas. Interesting tool ! ! http://db.apache.org/ddlutils/ Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Scott Marlowe [EMAIL PROTECTED] À : Lothar Behrens [EMAIL PROTECTED] Cc : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 18h22mn 12s Objet : Re: [GENERAL] Am I overseen ? On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. But I'm not that familiar with UML as to say what's the best approach. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de
Re: [GENERAL] Am I overseen ?
On 18 Okt., 19:33, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: On 18 Okt., 18:22, [EMAIL PROTECTED] (Scott Marlowe) wrote: Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. In my opinion the question was clear. How to alter table if the column not already inserted of later of given type. I think Scott meant that if a question--clear or not--is not something one feels one is qualified to answer, then one generally does not. In my opinion, though, the question was not clear. Hmm, ok. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). I don't feel at all qualified to answer this. Sorry. I will ask differently. Is there a tool that keeps track to syncronize database models supporting postgresql (natively) ? Laurent gave me a hint to http://db.apache.org/ddlutils/ but it is only to convert database model to XML representation and vica versa. What I could read, it does not syncronize. Thus I would propably not able to look into it for how it works. I think I'll study the system tables for postgreSQL and do it anyhow. Thanks, Lothar brian ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] conditional alter table add ?
Hi, I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. Is there any way to do this ? Tanks, Lothar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem with database
Hi, I had a crash with my hdd and now I have recovered the database with the directory found in lost and found. Now I get an error, if I try to open the database admin tool PGAdmin III: cannot open relation pg_amop What is missing ? Coul'd this corrected ? Thanks Lothar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Problem with curses
Hello,
the psql tool uses the curses functions.
If I compile psql or all of postgreSQL
I get error messages listed at the end here.
I have installed ncurses. But I don´t know
how to link that. If I am right I must link
that.
What am I doing wrong?
Thanks
Lothar
--
gcc -o psql -L../../interfaces/libpq psql.o stringutils.o -lpq -ldl -lm -lbsd
-lreadline -lhistory -export-dynamic /usr/lib/libreadline.a(display.o): In
function `rl_redisplay': display.o(.text+0x9bf): undefined reference to `tputs'
/usr/lib/libreadline.a(display.o): In function `update_line':
display.o(.text+0xf26): undefined reference to `tputs'
/usr/lib/libreadline.a(display.o): In function `_rl_move_cursor_relative':
display.o(.text+0x11e6): undefined reference to `tputs'
/usr/lib/libreadline.a(display.o): In function `_rl_move_vert':
display.o(.text+0x12c9): undefined reference to `tputs' display.o(.text+0x130a):
undefined reference to `tputs' /usr/lib/libreadline.a(display.o)(.text+0x1798):
more undefined references to `tputs' follow /usr/lib/libreadline.a(display.o):
In function `insert_some_chars': display.o(.text+0x184a): undefined reference to
`tgoto' display.o(.text+0x1857): undefined reference to `tputs'
display.o(.text+0x1887): undefined reference to `tputs' display.o(.text+0x18b2):
undefined reference to `tputs' display.o(.text+0x18e0): undefined reference to
`tputs' /usr/lib/libreadline.a(display.o): In function `delete_chars':
display.o(.text+0x1911): undefined reference to `tgoto' display.o(.text+0x191d):
undefined reference to `tputs' display.o(.text+0x194e): undefined reference to
`tputs' /usr/lib/libreadline.a(display.o): In function `cr':
display.o(.text+0x1a85): undefined reference to `tputs'
/usr/lib/libreadline.a(display.o): In function `_rl_redisplay_after_sigwinch':
display.o(.text+0x1aba): undefined reference to `tputs' display.o(.text+0x1add):
undefined reference to `tputs' /usr/lib/libreadline.a(display.o)(.text+0x1b09):
more undefined references to `tputs' follow /usr/lib/libreadline.a(terminal.o):
In function `_rl_get_screen_size': terminal.o(.text+0x81): undefined reference
to `tgetnum' terminal.o(.text+0xd7): undefined reference to `tgetnum'
/usr/lib/libreadline.a(terminal.o): In function `get_term_capabilities':
terminal.o(.text+0x1b3): undefined reference to `tgetstr'
/usr/lib/libreadline.a(terminal.o): In function `_rl_init_terminal_io':
terminal.o(.text+0x26b): undefined reference to `tgetent'
terminal.o(.text+0x35c): undefined reference to `PC' terminal.o(.text+0x366):
undefined reference to `BC' terminal.o(.text+0x370): undefined reference to `UP'
terminal.o(.text+0x3be): undefined reference to `tgetflag'
terminal.o(.text+0x3cf): undefined reference to `tgetflag'
terminal.o(.text+0x41f): undefined reference to `tgetflag'
terminal.o(.text+0x430): undefined reference to `tgetflag'
/usr/lib/libreadline.a(terminal.o): In function `_rl_backspace':
terminal.o(.text+0x666): undefined reference to `tputs'
/usr/lib/libreadline.a(terminal.o): In function `ding': terminal.o(.text+0x72f):
undefined reference to `tputs' /usr/lib/libreadline.a(terminal.o): In function
`_rl_enable_meta_key': terminal.o(.text+0x7ce): undefined reference to `tputs'
/usr/lib/libreadline.a(terminal.o): In function `_rl_control_keypad':
terminal.o(.text+0x7fb): undefined reference to `tputs' terminal.o(.text+0x822):
undefined reference to `tputs' make[2]: *** [psql] Error 1
--
Lothar Behrens |e-Mail: [EMAIL PROTECTED]
Stader Str. 42 |oder : [EMAIL PROTECTED]
|
21075 Hamburg | Wir lernen aus unseren Fehlern {:-)
--
