SQLite – Does COLLATE Preserve on Tables in a View?

collationsqlite

In sqlite3 collations are defined per-column. If you CREATE TEMPORARY TABLE foo AS SELECT ... the resulting temporary table foo does not inherit any custom collations from the columns in the SELECT. What happens if you CREATE VIEW foo AS SELECT ...? Does the view "table" keep column collations or lose them?

Best Answer

In sqlite3 VIEWs keep the column collation from the underlying table. Proof: http://sqlfiddle.com/#!5/52669/3

Related Solutions

Select column from ALL tables

You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.

SQLite3 simple database design question

After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:

Table MRindex - All details about a entry will be listed here: key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
Table MRtwitter - All twitter related data will be stored here: key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
Table MRrss - All rss data will be stored here: key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text

Any comments & hints would be more than welcomed :-)

EDIT: To clarify a bit...

MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up

MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.

MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.

For MRindex:

description: short user description date: date of creation blob_picture: a picture if any (jpg, png, etc) rss_feed: An RSS_feed URL belonging to this user twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag

Best Answer

Related Solutions

Select column from ALL tables

SQLite3 simple database design question

Related Question