The clean & modern RSS server that doesn't give you any crap. https://thearsse.com/
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

0.sql 12KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
  1. -- SPDX-License-Identifier: MIT
  2. -- Copyright 2017 J. King, Dustin Wilson et al.
  3. -- See LICENSE and AUTHORS files for details
  4. create table arsse_meta(
  5. -- application metadata
  6. key text primary key not null, -- metadata key
  7. value text -- metadata value, serialized as a string
  8. );
  9. create table arsse_users(
  10. -- users
  11. id text primary key not null, -- user id
  12. password text, -- password, salted and hashed; if using external authentication this would be blank
  13. name text, -- display name
  14. avatar_type text, -- internal avatar image's MIME content type
  15. avatar_data blob, -- internal avatar image's binary data
  16. admin boolean default 0, -- whether the user is a member of the special "admin" group
  17. rights integer not null default 0 -- temporary admin-rights marker FIXME: remove reliance on this
  18. );
  19. create table arsse_users_meta(
  20. -- extra user metadata (not currently used and will be removed)
  21. owner text not null references arsse_users(id) on delete cascade on update cascade,
  22. key text not null,
  23. value text,
  24. primary key(owner,key)
  25. );
  26. create table arsse_folders(
  27. -- folders, used by NextCloud News and Tiny Tiny RSS
  28. -- feed subscriptions may belong to at most one folder;
  29. -- in Tiny Tiny RSS folders may nest
  30. id integer primary key, -- sequence number
  31. owner text not null references arsse_users(id) on delete cascade on update cascade, -- owner of folder
  32. parent integer references arsse_folders(id) on delete cascade, -- parent folder id
  33. name text not null, -- folder name
  34. modified text not null default CURRENT_TIMESTAMP, -- time at which the folder itself (not its contents) was changed; not currently used
  35. unique(owner,name,parent) -- cannot have multiple folders with the same name under the same parent for the same owner
  36. );
  37. create table arsse_feeds(
  38. -- newsfeeds, deduplicated
  39. -- users have subscriptions to these feeds in another table
  40. id integer primary key, -- sequence number
  41. url text not null, -- URL of feed
  42. title text, -- default title of feed (users can set the title of their subscription to the feed)
  43. favicon text, -- URL of favicon
  44. source text, -- URL of site to which the feed belongs
  45. updated text, -- time at which the feed was last fetched
  46. modified text, -- time at which the feed last actually changed
  47. next_fetch text, -- time at which the feed should next be fetched
  48. orphaned text, -- time at which the feed last had no subscriptions
  49. etag text not null default '', -- HTTP ETag hash used for cache validation, changes each time the content changes
  50. err_count integer not null default 0, -- count of successive times update resulted in error since last successful update
  51. err_msg text, -- last error message
  52. username text not null default '', -- HTTP authentication username
  53. password text not null default '', -- HTTP authentication password (this is stored in plain text)
  54. size integer not null default 0, -- number of articles in the feed at last fetch
  55. scrape boolean not null default 0, -- whether to use picoFeed's content scraper with this feed
  56. unique(url,username,password) -- a URL with particular credentials should only appear once
  57. );
  58. create table arsse_subscriptions(
  59. -- users' subscriptions to newsfeeds, with settings
  60. id integer primary key, -- sequence number
  61. owner text not null references arsse_users(id) on delete cascade on update cascade, -- owner of subscription
  62. feed integer not null references arsse_feeds(id) on delete cascade, -- feed for the subscription
  63. added text not null default CURRENT_TIMESTAMP, -- time at which feed was added
  64. modified text not null default CURRENT_TIMESTAMP, -- time at which subscription properties were last modified
  65. title text, -- user-supplied title
  66. order_type int not null default 0, -- NextCloud sort order
  67. pinned boolean not null default 0, -- whether feed is pinned (always sorts at top)
  68. folder integer references arsse_folders(id) on delete cascade, -- TT-RSS category (nestable); the first-level category (which acts as NextCloud folder) is joined in when needed
  69. unique(owner,feed) -- a given feed should only appear once for a given owner
  70. );
  71. create table arsse_articles(
  72. -- entries in newsfeeds
  73. id integer primary key, -- sequence number
  74. feed integer not null references arsse_feeds(id) on delete cascade, -- feed for the subscription
  75. url text, -- URL of article
  76. title text, -- article title
  77. author text, -- author's name
  78. published text, -- time of original publication
  79. edited text, -- time of last edit by author
  80. modified text not null default CURRENT_TIMESTAMP, -- time when article was last modified in database
  81. content text, -- content, as (X)HTML
  82. guid text, -- GUID
  83. url_title_hash text not null, -- hash of URL + title; used when checking for updates and for identification if there is no guid.
  84. url_content_hash text not null, -- hash of URL + content, enclosure URL, & content type; used when checking for updates and for identification if there is no guid.
  85. title_content_hash text not null -- hash of title + content, enclosure URL, & content type; used when checking for updates and for identification if there is no guid.
  86. );
  87. create table arsse_enclosures(
  88. -- enclosures (attachments) associated with articles
  89. article integer not null references arsse_articles(id) on delete cascade, -- article to which the enclosure belongs
  90. url text, -- URL of the enclosure
  91. type text -- content-type (MIME type) of the enclosure
  92. );
  93. create table arsse_marks(
  94. article integer not null references arsse_articles(id) on delete cascade, -- article associated with the marks
  95. subscription integer not null references arsse_subscriptions(id) on delete cascade on update cascade, -- subscription associated with the marks; the subscription in turn belongs to a user
  96. read boolean not null default 0, -- whether the article has been read
  97. starred boolean not null default 0, -- whether the article is starred
  98. modified text not null default CURRENT_TIMESTAMP, -- time at which an article was last modified by a given user
  99. primary key(article,subscription) -- no more than one mark-set per article per user
  100. );
  101. create table arsse_editions(
  102. -- IDs for specific editions of articles (required for at least NextCloud News)
  103. -- every time an article is updated by its author, a new unique edition number is assigned
  104. -- with NextCloud News this prevents users from marking as read an article which has been
  105. -- updated since the client state was last refreshed
  106. id integer primary key, -- sequence number
  107. article integer not null references arsse_articles(id) on delete cascade, -- the article of which this is an edition
  108. modified datetime not null default CURRENT_TIMESTAMP -- tiem at which the edition was modified (practically, when it was created)
  109. );
  110. create table arsse_categories(
  111. -- author categories associated with newsfeed entries
  112. -- these are not user-modifiable
  113. article integer not null references arsse_articles(id) on delete cascade, -- article associated with the category
  114. name text -- freeform name of the category
  115. );
  116. -- set version marker
  117. pragma user_version = 1;
  118. insert into arsse_meta("key",value) values('schema_version','1');