ubuntu-touch-coreapps team mailing list archive

[Svenn-Arne Dragly <s@xxxxxxxxxx>]

Hi Joey,

Sorry about not getting back to you about the DB model earlier, I've had
a busy week.

This looks very good. The model has many similarities with what was
implemented in databasemodule.js, so I think merging this into that
script could be a nice solution. The way databasemodule.js has been
structured so far seems like a good starting point. (We will of course
have to rename the tables and some functions, but that should be pretty
easy.)

The only thing I'm missing in the model is perhaps a

guid VARCHAR(X) NULL

in the article table. This would let us check if an element from a feed
already exists when we update that feed at a later point. RSS has a guid
element in its specification.

By the way, how do we implement updated feeds? Should we download the
source with regular intervals and populate the article table with items
that are not already there? I think that could work as long as the RSS
"guid" is really used in most feeds. A fallback could be to check if a
combination of title and pubdate already exists.

In any case, great work with the database model!

(PS: Perhaps we should have another table with database properties such
as current_database_version and database_last_updated? This would help
us perform updates on the users databases after the app goes live.)


Svenn-Arne

On 05/02/2013 04:40 PM, Joey Chan wrote:
> Hi Ladies and Gentlemen,
>
> SQL code is generated from the physical diagram, pls check below:
>
> CREATE TABLE article (
>  id  INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
>  title  VARCHAR(99)  NULL,
>  link  VARCHAR(99)  NULL,
>  description  teXT  NULL,
>  pubdate  INTEGER  NULL,
>  status  char(1)  NULL,
>  favourite  char(1)  NULL,
>  image  varCHAR(99)  NULL,
>  feed_id  INTEGER  NULL
> );
>
> CREATE TABLE  feed  (
>  id  INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
>  source  VARCHAR(99)  NULL,
>  title  VARCHAR(99)  NULL,
>  link  VARCHAR(99)  NULL,
>  description  TEXT  NULL,
>  pubdate  INTEGER  NULL,
>  image  VARCHAR(99)  NULL
> );
>
> CREATE TABLE  feed_tag  (
>  id  INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
>  feed_id  INTEGER  NULL,
>  tag_id  INTEGER  NULL,
> FOREIGN KEY(feed_id) REFERENCES feed(id) on delete cascade
> );
>
> CREATE TABLE  tag  (
>  id  INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
>  name  varCHAR(99)  NULL
> );
>
> Description: 
> 1. table article: store every item in an RSS feed;
> 2. table feed: store data of RSS feeds;
> 3. table feed_tag: store relationship of "feed" and "tag", includes a
> foreign key "feed_id", if an feed is deleted, the related row in
> feed_tag will also be deleted;
> 4. table tag: store data of tag;
>
>
> Any advices and comments are welcome :)
>
>
>
>