Mercurial > libervia-pubsub
comparison db/sat_pubsub_update_0_1.sql @ 301:05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
- database creation/update files include the new table
- item data are now stored in a ItemData namedtuple
author | Goffi <goffi@goffi.org> |
---|---|
date | Wed, 25 Nov 2015 18:33:38 +0100 |
parents | df1edebb0466 |
children | 3c2705199108 |
comparison
equal
deleted
inserted
replaced
300:c5acb4995fde | 301:05c875a13a62 |
---|---|
10 value text | 10 value text |
11 ); | 11 ); |
12 | 12 |
13 INSERT INTO metadata VALUES ('version', '1'); | 13 INSERT INTO metadata VALUES ('version', '1'); |
14 | 14 |
15 CREATE TABLE item_categories ( | |
16 item_categories_id serial PRIMARY KEY, | |
17 item_id integer NOT NULL references items ON DELETE CASCADE, | |
18 category text NOT NULL, | |
19 UNIQUE (item_id,category) | |
20 ); | |
21 | |
15 UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%'; | 22 UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%'; |
16 | 23 |
17 /* This is to update namespaces, SàT was bugguy before 0.6 and didn't set the atom namespace in <entry/> */ | 24 /* This is to update namespaces, SàT was bugguy before 0.6 and didn't set the atom namespace in <entry/> */ |
18 /* But yeah, this is a crazy query */ | 25 /* But yeah, this is a crazy query */ |
19 UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id), | 26 UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id), |