Re: [mart-dev] MartBuilder question
Richard, I certainly did not set out with Biomart with the idea of learning more about Postgres but that seems to be what's happening. That's life. I've added a few lines to Biosql's Postgres script that allows you to create Biosql in a specified schema, and I've modified that load_taxonomy.pl script to load using a given database and schema. This all seems to work. Last step, modify bioperl-db's load_seqdatabase.pl script to do the same... BIO On 5/25/07 6:08 AM, Richard Holland [EMAIL PROTECTED] wrote: Let me know how you get on with trying to do this within a non-public schema.
Re: [mart-dev] MartBuilder question
On 25 May 2007, at 11:08, Richard Holland wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
To be honest I'm not a Postgres expert. From what I understand 'public'
is some kind of special schema that shouldn't normally be used, but I
might be wrong!
'public' is just a default postgress schema. If you setup
a new database and you do not specify otherwise, your tables will
go by default to a public schema. Assuming that your source schema
is in public, it would be probably best to set target schema in MBuilder
as other than public to keep mart and source schema separated
(as we normally do anyway)
a.
Let me know how you get on with trying to do this within a non-public
schema.
cheers,
Richard
Brian Osborne wrote:
Richard,
I think I see the problem, not a surprising one. Biosql's Postgres
script
does not create a schema within the Biosql database, you're just
populating
the db with tables. Within MartBuilder you must specify a schema,
entering
database as 'bioseqdb' and schema as 'bioseqdb' does not work ('schema
connection failed'). So I tried other possibilities and 'public'
worked for
some reason. Perhaps it shouldn't have?
However, since 'public.bioentry' doesn't exist the mart is not built.
Looks
like I have to modify the Biosql Postgres script to create a schema,
not
just a database (it's based on Mysql).
BIO
On 5/24/07 10:36 AM, Richard Holland [EMAIL PROTECTED] wrote:
Yes, I know BioSQL well! :)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGVrWt4C5LeMEKA/QRAgBZAJ0QJYP39xRn+WABo/k2gXvRBjfHqQCfWinl
ENmJDju5IJkaFLKbwtVkRoo=
=O74g
-END PGP SIGNATURE-
---
Arek Kasprzyk
EMBL-European Bioinformatics Institute.
Wellcome Trust Genome Campus, Hinxton,
Cambridge CB10 1SD, UK.
Tel: +44-(0)1223-494606
Fax: +44-(0)1223-494468
---
Re: [mart-dev] MartBuilder question
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
To be honest I'm not a Postgres expert. From what I understand 'public'
is some kind of special schema that shouldn't normally be used, but I
might be wrong!
Let me know how you get on with trying to do this within a non-public
schema.
cheers,
Richard
Brian Osborne wrote:
Richard,
I think I see the problem, not a surprising one. Biosql's Postgres script
does not create a schema within the Biosql database, you're just populating
the db with tables. Within MartBuilder you must specify a schema, entering
database as 'bioseqdb' and schema as 'bioseqdb' does not work ('schema
connection failed'). So I tried other possibilities and 'public' worked for
some reason. Perhaps it shouldn't have?
However, since 'public.bioentry' doesn't exist the mart is not built. Looks
like I have to modify the Biosql Postgres script to create a schema, not
just a database (it's based on Mysql).
BIO
On 5/24/07 10:36 AM, Richard Holland [EMAIL PROTECTED] wrote:
Yes, I know BioSQL well! :)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGVrWt4C5LeMEKA/QRAgBZAJ0QJYP39xRn+WABo/k2gXvRBjfHqQCfWinl
ENmJDju5IJkaFLKbwtVkRoo=
=O74g
-END PGP SIGNATURE-
Re: [mart-dev] MartBuilder question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hmmm interesting idea. I'll certainly think about it. cheers, Richard Guðmundur Árni Þórisson wrote: Of course, of course (silly me!). However, it may prove useful to have MBuilder optionally produce the table-definition SQL + SQL-script to dump out the data to textfiles, to support heterogenous db-engine setups. The tables could then be created in the target database and textfiles subsequently loaded. Mummi On 24 May 2007, at 16:59, Richard Holland wrote: Hello. The quick answer is 'no'. The slightly longer answer is because it builds marts by executing 'create X as select Y from Z' statements against a single database - and so both source and target schemas need to be living in the database for this to work. cheers, Richard Guðmundur Árni Þórisson wrote: Richard, if I may pitch in a question: can one use MartBuilder to pull data out of say PostgreSQL and create + load into a mart in MySQL? The answer might be in the manual somewhere but I'm sure you have the quick answer! It's possible I might for my project end up having the master database in PostgreSQL (for various reasons), but then have the corresponding Mart in MySQL + read-only MyISAM tables for speed. Mummi --- Gudmundur A. Thorisson, PhD student, Brookes lab Department of Genetics University of Leicester University Road Leicester, LE1 7RH, UK E-mail: [EMAIL PROTECTED] Tel: +44 (0)116 252-3055 On 24 May 2007, at 16:34, Richard Holland wrote: The public thing is interesting. When you originally connected MartBuilder to your source schema, I'm assuming you used database=postgres and schema=bioseqdb? What target schema did you give it when you asked it to generate the SQL? Was is bioseqdbmart? Are both the bioseqdb and bioseqdbmart schemas in the same database, ie. postgres? cheers, Richard Brian Osborne wrote: Richard, So the SQL file's name is bioseqdbmart.sql, the source is bioseqdb and the mart or target is bioseqdbmart. OK, so: psql bioseqdbmart postgres bioseqdbmart.sql might not work, I should be logged in. Let's log in... Hmm. I see: bioseqdbmart=# \i bioseqdbmart.sql psql:bioseqdbmart.sql:1: ERROR: schema bioseqdbmart already exists SET psql:bioseqdbmart.sql:6: ERROR: relation public.bioentry does not exist SET psql:bioseqdbmart.sql:9: ERROR: relation bioseqdbmart.temp__1 does not exist SET ...and more errors. public.bioentry is troubling, it should be public.bioseqdb.bioentry or bioseqdb.bioentry, I think. That error comes from this: bioseqdbmart=# create table bioseqdbmart.TEMP__1 as select a.accession as accession,a.biodatabase_id as biodatabase_id, bioseqdbmart-# a.bioentry_id as bioentry_id_key,a.description as description,a.division as division,a.identifier as bioseqdbmart-# identifier,a.name as name,a.taxon_id as taxon_id,a.version as version,b.authority as authority, bioseqdbmart-# b.description as description_biodatabase,b.name as name_biodatabase from public.bioentry as a left join bioseqdbmart-# public.biodatabase as b on a.biodatabase_id=b.biodatabase_id; ERROR: relation public.bioentry does not exist bioseqdbmart=# Brian O. On 5/24/07 10:36 AM, Richard Holland [EMAIL PROTECTED] wrote: run whilst logged in as the target schema owner. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGVurZ4C5LeMEKA/QRAu0rAJ9dtmWc0L3424M/kQUARy0uB9ZLWgCdHd+H YYHMaAxBpruQm1Nv8Q2PZz4= =+PJW -END PGP SIGNATURE-
Re: [mart-dev] MartBuilder question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The public thing is interesting. When you originally connected MartBuilder to your source schema, I'm assuming you used database=postgres and schema=bioseqdb? What target schema did you give it when you asked it to generate the SQL? Was is bioseqdbmart? Are both the bioseqdb and bioseqdbmart schemas in the same database, ie. postgres? cheers, Richard Brian Osborne wrote: Richard, So the SQL file's name is bioseqdbmart.sql, the source is bioseqdb and the mart or target is bioseqdbmart. OK, so: psql bioseqdbmart postgres bioseqdbmart.sql might not work, I should be logged in. Let's log in... Hmm. I see: bioseqdbmart=# \i bioseqdbmart.sql psql:bioseqdbmart.sql:1: ERROR: schema bioseqdbmart already exists SET psql:bioseqdbmart.sql:6: ERROR: relation public.bioentry does not exist SET psql:bioseqdbmart.sql:9: ERROR: relation bioseqdbmart.temp__1 does not exist SET ...and more errors. public.bioentry is troubling, it should be public.bioseqdb.bioentry or bioseqdb.bioentry, I think. That error comes from this: bioseqdbmart=# create table bioseqdbmart.TEMP__1 as select a.accession as accession,a.biodatabase_id as biodatabase_id, bioseqdbmart-# a.bioentry_id as bioentry_id_key,a.description as description,a.division as division,a.identifier as bioseqdbmart-# identifier,a.name as name,a.taxon_id as taxon_id,a.version as version,b.authority as authority, bioseqdbmart-# b.description as description_biodatabase,b.name as name_biodatabase from public.bioentry as a left join bioseqdbmart-# public.biodatabase as b on a.biodatabase_id=b.biodatabase_id; ERROR: relation public.bioentry does not exist bioseqdbmart=# Brian O. On 5/24/07 10:36 AM, Richard Holland [EMAIL PROTECTED] wrote: run whilst logged in as the target schema owner. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGVbCJ4C5LeMEKA/QRAr1lAKCfLBi6HpDwCo1liCikscGmVxxdjQCeKljT zHNwNkyL8Y8DDVlSjGaKJeY= =6VHt -END PGP SIGNATURE-
Re: [mart-dev] MartBuilder question
Richard, if I may pitch in a question: can one use MartBuilder to pull data out of say PostgreSQL and create + load into a mart in MySQL? The answer might be in the manual somewhere but I'm sure you have the quick answer! It's possible I might for my project end up having the master database in PostgreSQL (for various reasons), but then have the corresponding Mart in MySQL + read-only MyISAM tables for speed. Mummi --- Gudmundur A. Thorisson, PhD student, Brookes lab Department of Genetics University of Leicester University Road Leicester, LE1 7RH, UK E-mail: [EMAIL PROTECTED] Tel: +44 (0)116 252-3055 On 24 May 2007, at 16:34, Richard Holland wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The public thing is interesting. When you originally connected MartBuilder to your source schema, I'm assuming you used database=postgres and schema=bioseqdb? What target schema did you give it when you asked it to generate the SQL? Was is bioseqdbmart? Are both the bioseqdb and bioseqdbmart schemas in the same database, ie. postgres? cheers, Richard Brian Osborne wrote: Richard, So the SQL file's name is bioseqdbmart.sql, the source is bioseqdb and the mart or target is bioseqdbmart. OK, so: psql bioseqdbmart postgres bioseqdbmart.sql might not work, I should be logged in. Let's log in... Hmm. I see: bioseqdbmart=# \i bioseqdbmart.sql psql:bioseqdbmart.sql:1: ERROR: schema bioseqdbmart already exists SET psql:bioseqdbmart.sql:6: ERROR: relation public.bioentry does not exist SET psql:bioseqdbmart.sql:9: ERROR: relation bioseqdbmart.temp__1 does not exist SET ...and more errors. public.bioentry is troubling, it should be public.bioseqdb.bioentry or bioseqdb.bioentry, I think. That error comes from this: bioseqdbmart=# create table bioseqdbmart.TEMP__1 as select a.accession as accession,a.biodatabase_id as biodatabase_id, bioseqdbmart-# a.bioentry_id as bioentry_id_key,a.description as description,a.division as division,a.identifier as bioseqdbmart-# identifier,a.name as name,a.taxon_id as taxon_id,a.version as version,b.authority as authority, bioseqdbmart-# b.description as description_biodatabase,b.name as name_biodatabase from public.bioentry as a left join bioseqdbmart-# public.biodatabase as b on a.biodatabase_id=b.biodatabase_id; ERROR: relation public.bioentry does not exist bioseqdbmart=# Brian O. On 5/24/07 10:36 AM, Richard Holland [EMAIL PROTECTED] wrote: run whilst logged in as the target schema owner. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGVbCJ4C5LeMEKA/QRAr1lAKCfLBi6HpDwCo1liCikscGmVxxdjQCeKljT zHNwNkyL8Y8DDVlSjGaKJeY= =6VHt -END PGP SIGNATURE-
