[SQL] Xml Schemas
Hello, I am attempting to build an xml representation of any database, but I'm having trouble doing so. I was interested in using the existing xml functions, such as "schema_to_xmlschema", but the results are strange and unusable. The resulting schema doesn't even contain the column names, though it doesn't include the table names. Often, the type information is incomplete as well. Are there any examples for using this family of functions, "schema_to_xmlschema", etc? My end goal is any xml representation of the database that includes type information. Thanks, Isaac
Re: [SQL] indexing longish string
Hi, While hashing is certainly a good idea, you really should consider some issues well before you get to that point. Trust me, this could save you some headaches. First, though you're probably already aware, two XML documents can be the same document, but with very different literal representations. By the XML spec, these two DOMs would be equal (readers would treat them somewhat differently): and If you're looking to compare the strings literally, then you have to make sure that you canonicalize both the persisted string as well as the comparison in your queries. I first encountered this problem when storing XML as a vendor's data type of XML rather than varchar. Upon inserting, the database engine parsed the string into DOM for the XML data type so that XPath and XQuery statements could be processed. This is all fine and dandy until I retrieved the literal representation. The document was transformed, as in the above example, to the literal , but my select was comparing using the former string. Adding to the problem was that pretty much every tool that renders XML tends to hide these sort of differences in an effort to be "friendly" (save Vim, of course :). It took a bit of time for me to notice these small differences. Also keep in mind that document order, namespaces, and space normalization are other important considerations. If you can store as DOM, you might experiment a bit to see if the process of canonicalizing is more/less efficient than just XPath'ing. Though, indexing a hash would probably be most efficient but at the risk of all that jibberish I just typed. More info here: http://www.w3.org/TR/xml-c14n11/ http://www.w3.org/TR/2010/WD-xml-c14n2-20100831/ Thanks, and good luck! Isaac On Tue, Nov 30, 2010 at 1:36 PM, Kenneth Marshall wrote: > You can use a hash index for this. It's drawback is that it is not > yet WAL enabled and if your DB crashes you will need to rebuild the > index to fix the corruption. It works well(only) with equality > searches. If it is a scenario where you must have WAL, use a > function index based on the hash of the string. > > Cheers, > Ken
Re: [SQL] indexing longish string
No problem, sir, hopefully I could help. I failed to mention that I've discovered some bugs in the PostgreSQL 8.4 XML implementation that forced me to take pause and ultimately forego XML with Postgres. I haven't looked at 9 yet, but considering the current lack of interest and/or disdain so many have for XML, I'm not anticipating great improvements. Of course, this is not to knock the db - Pg is awesome. But I'm aware that we all have limited time and there are other more important features that far more people desire. So, you are probably better off using string literals with all of the known risks. I've used some lame hashing techniques that simply stripped out everything except [:alnum:] and then simply alphabetized the characters and sometimes sent them through SHA/MD5 to get a unique representation. It seemed to work fine for my purposes. Or take the old, proven approach of shredding documents into normalized tables and serializing them back out when you need them. ... ick !! ... Thanks, Isaac On Tue, Nov 30, 2010 at 2:33 PM, Rob Sargent wrote: > Hello Issac, > > Thanks a ton. > > In this bizarre little corner, I'm pretty sure I would use a text field > rather than any xml-friendly datatype. The xml content itself will be > trivial yet dynamic :). We need just a little more rigour than a hacky > delimited string implementation, but not much. And we guarantee the > repeatability of the string for a given parameter set. > > We could also do a manual tag-value implementation, and likely would if > we thought we were ever going to ask questions like "everything with tag > foo value less than ten", but that's not on the table. (Today...) > > On 11/30/2010 12:21 PM, Isaac Dover wrote: > > Hi, > > > > While hashing is certainly a good idea, you really should consider some > > issues well before you get to that point. Trust me, this could save you > > some headaches. First, though you're probably already aware, two XML > > documents can be the same document, but with very different literal > > representations. By the XML spec, these two DOMs would be equal (readers > > would treat them somewhat differently): > > > > > > and > > > > > > If you're looking to compare the strings literally, then you have to > > make sure that you canonicalize both the persisted string as well as the > > comparison in your queries. I first encountered this problem when > > storing XML as a vendor's data type of XML rather than varchar. Upon > > inserting, the database engine parsed the string into DOM for the XML > > data type so that XPath and XQuery statements could be processed. This > > is all fine and dandy until I retrieved the literal representation. The > > document was transformed, as in the above example, to the > > literal , but my select was comparing using the former > > string. Adding to the problem was that pretty much every tool that > > renders XML tends to hide these sort of differences in an effort to be > > "friendly" (save Vim, of course :). It took a bit of time for me to > > notice these small differences. Also keep in mind that document order, > > namespaces, and space normalization are other important considerations. > > > > If you can store as DOM, you might experiment a bit to see if the > > process of canonicalizing is more/less efficient than just XPath'ing. > > Though, indexing a hash would probably be most efficient but at the risk > > of all that jibberish I just typed. > > > > More info here: > > http://www.w3.org/TR/xml-c14n11/ > > http://www.w3.org/TR/2010/WD-xml-c14n2-20100831/ > > > > Thanks, and good luck! > > Isaac > > > > On Tue, Nov 30, 2010 at 1:36 PM, Kenneth Marshall > <mailto:k...@rice.edu>> wrote: > > > > You can use a hash index for this. It's drawback is that it is not > > yet WAL enabled and if your DB crashes you will need to rebuild the > > index to fix the corruption. It works well(only) with equality > > searches. If it is a scenario where you must have WAL, use a > > function index based on the hash of the string. > > > > Cheers, > > Ken > > >
Re: [SQL] Compare two Data bases Structure
As an alternative to the other tools - I've had luck using a simple query that uses information schema. > How Can I do to compare two structures of data bases ? > > DBA != DBB I need wich functions and wich tables are not equals > > thnks
Re: [SQL] Compare two Data bases Structure
Select from information_schema.columns and full outer join it again on the table and column names, then just look for nulls on either side. You'll need to alias the left and right views differently. I'm assuming that the dbs are next to each other in the same installation. Once the base query is working, you can filter further on the column types. It's pretty simple as long as you understand joins. On Thu, Feb 24, 2011 at 10:30 AM, manuel antonio ochoa < manuel8aalf...@gmail.com> wrote: > > How can I do that ? I dont know how to use the query !! ? > > > 2011/2/24 Isaac Dover > >> As an alternative to the other tools - I've had luck using a simple query >> that uses information schema. >> > How Can I do to compare two structures of data bases ? >> > >> > DBA != DBB I need wich functions and wich tables are not equals >> > >> > thnks >> > >
[SQL] nesting XmlAgg
hi all, i'm coming from the MS SQLXML world and attempting to learn SQL/XML, but i'm having some trouble... my first project, which i thought would be simple, is to create an xml model using the information schema views... i'm simply trying to build a document such as: the problem i'm encountering involves what the function "XmlAgg" claims to solve... using this query, you'll immediately see my dilemna... though, i'm not able to nest XmlAgg in order to use subqueries to resolve the incorrect partitioning at the "Table" element depth... i am working towards implementing postgres for a local non-profit, but if i can't get up to speed on the xml functionality, then i fear that i'll have to revert back to the more familiar sql server 2005 (please don't make me! :)... seeing PS's other numerous features, i can't imagine that this is an impossibility, i just need to right nudge... thanks! Isaac select XmlElement(name "Catalog", XmlElement(name "Tables", XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as "Name"), XmlElement(name "Columns", -- i was attempting to aggregate here as well (select XmlElement(name "Column", C.Column_Name)) ) )) ) ) from information_schema.tables T inner join information_schema.columns C on T.table_name = C.table_name and T.table_schema = C.table_schema where T.table_schema = 'public'
Fwd: [SQL] Comparing two tables of different database
i've not tried this in postgres, but using information_schema makes
comparing structures of databases trivial. i've been using this query for a
while with MSSQL. Maybe this helps to answer the question.
- isaac
select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
from [database].information_schema.Columns ST
full outer join [other database].information_schema.Columns DV
on ST.Table_Name = DV.Table_name
and ST.Column_Name = DV.Column_Name
where ST.Column_Name is null or DV.Column_Name is NULL
On Sat, May 2, 2009 at 11:01 AM, Lucas Brito wrote:
> Nicholas,
>
> To use the dblink:
>
>1. In your postgres server you should find a file *dblink.sql*.
>In my beta installation is in *share/postgresql/contrib*. It is the
>installation for the dblink contrib module that usually is already compiled
>in. It will create a lot of dblink functions.
>
>2. on database2 create a function nammed db_datbase1() which returns
>"dbname=database1" (if you need a login use "dbname=database1
> password=xxx",
>you can also specify host= port= to connect in a remote postgresql
> database)
>
>3. now execute the sql:
>select * from dblink(db_database1(), 'select "id", "name", "time" from
>pr_1') as pr_1("id" integer, "name" text, "time" time)
>then you will see the table "pr_1" on the datbase2
>
> --
> Lucas Brito
Re: [SQL] Comparing two tables of different database
Thank you, Lucas. I'm from the MS world, still learning these PG things.
Though, it appears that the difference is somewhat minor. In my actual
implementation, [other database] would be a linked server, which sounds like
it would be similar to the PG dblink. Regardless, I've found information
schema to be incredibly valuable.
Thanks,
- Isaac
On Sat, May 2, 2009 at 5:25 PM, Lucas Brito wrote:
> 2009/5/2 Isaac Dover
>
>> i've not tried this in postgres, but using information_schema makes
>> comparing structures of databases trivial. i've been using this query for a
>> while with MSSQL. Maybe this helps to answer the question.
>>
>> - isaac
>>
>> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
>> from [database].information_schema.Columns ST
>> full outer join [other database].information_schema.Columns DV
>> on ST.Table_Name = DV.Table_name
>> and ST.Column_Name = DV.Column_Name
>> where ST.Column_Name is null or DV.Column_Name is NULL
>>
>>
>
> Isaac, this query will return "ERROR: cross-database references are not
> implemented".
>
> Postgres does not support queries in databases other than current one. Even
> a simple select * from otherdatabase.information_schema.columns will not
> work and throw the same error.
>
> However this can be done with dblink function like:
>
> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
> from information_schema.Columns ST
> full outer join dblink('dbname=otherdatabase','select Table_Name,
> Column_Name from information_schema.Columns') DV(Table_Name text,
> Column_Name text)
> on ST.Table_Name = DV.Table_name
> and ST.Column_Name = DV.Column_Name
> where ST.Column_Name is null or DV.Column_Name is NULL
>
> --
> Lucas Brito
>
>
Re: [SQL] help with pg_hba.conf
possibly this answers my question, and what i am doing is indeed the most appropriate? "Databases are physically separated and access control is managed at the connection level." from 8.3 docs, section 20.1 thanks, Isaac On Fri, May 22, 2009 at 2:37 AM, Isaac Dover wrote: > Hello, to this point i've been working with pg_hba.conf authentication > defaults as installed with PostgreSQL 8.3. I'm trying to better understand > "best practice" for managing connections to databases (I've grown accustomed > to the MSSQL EM method of assigning user privileges). As far as i can tell, > pg_hba.conf is the only manner in which to prevent users from connecting to > other users' databases. I've restricted roles to connecting only using > sameuser: > > host sameuser all 192.168.168.0/24 md5 > > this works fine until a user connects and creates a new database. Pg shows > that the owner of the database is the currently connected user, but the user > can't connect to it, as the hba.conf file has the sameuser restriction. I > was hoping that (somehow, magically) the owner of the database could always > connect to the databases he/she owns. > > Is hba.conf the only way to restrict users connections to specific > databases? Are there privileges I can grant without having to maintain this > file? > > I've spent quite some time researching this, even with the documentation, > but I'm wondering what I'm missing. > > Thanks, > Isaac >
Re: [SQL] help with pg_hba.conf
thank you _SO_ much. I'm not sure how I overlooked that, but it is far easier to manage using connect. I'm assuming that even if tables have public privileges granted, that they are not visible to people not granted connect privileges to the database? Thanks, Isaac On Fri, May 22, 2009 at 12:31 PM, Tom Lane wrote: > Isaac Dover writes: > >> As far as i can tell, > >> pg_hba.conf is the only manner in which to prevent users from connecting > to > >> other users' databases. I've restricted roles to connecting only using > >> sameuser: > >> > >> host sameuser all 192.168.168.0/24 md5 > > In recent releases (certainly 8.3) the better approach is probably to > use CONNECT privilege to grant or deny access. However that does have > some drawbacks if you intend to let users create their own databases > --- they have to remember to set the privileges properly on new DBs. > >regards, tom lane >
