Wikipedia:Reference desk/Archives/Computing/2021 June 29

From Wikipedia, the free encyclopedia
Computing desk
< June 28 << May | June | Jul >> June 30 >
Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.


June 29[edit]

Windows 11 and MBR/GPT[edit]

Windows 11 requires UEFI. So, does it also require GPT, or is MBR still supported on Windows 11? GeoffreyT2000 (talk) 00:44, 29 June 2021 (UTC)[reply]

The answer is "you will probably have to switch to GPT". I say "probably" because Microsoft has indicated that the requirements for Windows 11 Insider Preview builds are not identical to that of the RTM Windows 11 build and they have already changed the requirements at least once.
Just out of curiosity, why would you want to use MBR instead of GPT? --Guy Macon (talk) 00:58, 29 June 2021 (UTC)[reply]
I use MBR because of old hardware. Bubba73 You talkin' to me? 22:09, 2 July 2021 (UTC)[reply]

SQL question[edit]

Let's say I'm using SQL to manage a blog which is a collection of articles, where each article has a title and a (maybe empty) collection of tags. That means there is a many-to-many relation between articles and tags. In 3rd normal form the schema would look like:

CREATE TABLE articles(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE assoc(tag_id INTEGER, article_id INTEGER);

Now suppose I want to add an article titled "what I did in my summer vacation" with tags ["programming","cooking","travel"]. That means I want to:

  • INSERT an article with that title into the articles table, and retrieve the article ID somehow. It's INSERT because there might be multiple articles with the same title.
  • UPSERT(?) the three tags, or alternatively, check for their presence and insert the ones that are absent. Then get the id's of the tags.
  • Insert rows into the association table connecting the tag id's with the article id's.

It really doesn't seem so good to insert the article and retrieve its ID in separate queries, or similarly with the tags, and I believe UPSERT is nonstandard (though widely supported). Can I do it with fewer queries? Is there a reason in general that I can't hold values (like row id's) in temporary variables in SQL, short of CREATE TEMPORARY TABLE? That means hold values from one query to another, as opposed to nested queries where I can use AS.

Context: to the extent that this is problem Y of an XY problem, problem X is basically "get better at SQL" rather than solve a specific application issue, so please feel free to supply any helpful advice or good practices as you see appropriate. I know that Postgres and recent versions of Sqlite have a RETURNING clause in the INSERT statement that can help with this, but is there a better general method? I'm not even sure of the best way to do it with RETURNING.

For personal projects I use sqlite3 but I have had to use other databases (mysql and postgres) at work, so it's good to have a wider understanding of how to do this stuff.

Can anyone suggest a good book or reference to read about these types of things? I have a book on query optimization that is pretty good, but it's not that helpful with this higher level design. And database documentation does a good job of explaining particular statements but again not so much about what I'm asking, as far as I can tell.

Thanks! 2602:24A:DE47:BA60:8FCB:EA4E:7FBD:4814 (talk) 21:38, 29 June 2021 (UTC)[reply]

If you can add unique constraint on name field in tags table, then you can use insert with ignore in MySQL. manya (talk) 08:41, 30 June 2021 (UTC)[reply]