The Heap™ – Consultancy ©®

Faheem Mitha

5:23 AM

I seem to remember this room being chattier.

hot2use

5:58 AM

Morning

Tom V

6:31 AM

Morning

Tom V

6:58 AM

@Lamak Pity

Andriy M

7:20 AM

Morning

@TomV What did he do?

Tom V

@AndriyM Chile lost the semifinals in the Copa América

Andriy M

Ah, I see. My commiserations @Lamak

George.Palacios

8:00 AM

Morning

McNets

8:21 AM

Morning

Paul White

Morning

Andriy M

Copa América, yes. That's the true commiseration spirit

ConcernedOfTunbridgeWells

8:38 AM

Morning all. Do we want to do drinkies this Saturday - especially @Phil?

3

George.Palacios

Question: If I find an answer from a new user that has a syntax error, do I edit and fix the answer or just rely on them doing it themselves?

In fact. I'll look / post on meta like a good DBA.SE citizen

Faheem Mitha

I have a general question, probably not concrete enough to be on-topic on the site.

I have a table, which represents correspondence. There is a From field and a To field.

Which are columns. I use abbreviations in the From and To fields, for brevity.

Abbreviations has its own table. Col 1 is Abbreviation, Col 2 is the actual name.

I was thinking of creating a new table where the abbreviations were replaced by the actual name, but there isn't enough space.

(I should mention that I'm using SQLite with this, and it's in the context of LuaTeX.)

Tom V

@George.Palacios if you are sure, just fix it, they can roll back if they disagree

Faheem Mitha

Anyway, my question is whether I given a subset of my correspondence table, whether I could get SQLite to provide the subset of the abbreviation table which corresponds to the abbreviations used in that subset? Or would I be better of just using Lua to pull out those values?

So, the idea would be to provide a separate companion table (which would be very short), and would contain just those abbreviations which were used.

George.Palacios

@TomV That is certainly one option

Faheem Mitha

8:51 AM

I'm not sure if it's worth posting this on the site. Comments welcome.

Andriy M

@TomV You are essentially discouraging George from being a good DBA.SE citizen

hot2use

@FaheemMitha Might be better suited for Stack Overflow.

Andriy M

@FaheemMitha What would the purpose of that companion table be? And I'm not sure how you would be able to provide just the relevant subset of the abbreviation table anyway

Faheem Mitha

@hot2use I can handle the Lua option myself. The DB option, on the other hand, not so much.

@AndriyM To tell the reader what those abbreviations mean.

Andriy M

Ah, so the contents would be used just for displaying, not for on-the-fly replacement in Lua code, I see

Faheem Mitha

8:55 AM

Ok, I'm missing some details. I'm using the correspondence table in future correspondence, by building at the bottom of every letter, which provides details of correspondence referred to in that letter.

But, also, at the bottom of such a better, I'd like a mini-abbreviations table to be provided, separate from the mini-correspondence table.

Master Database

0

Q: Should syntax errors / script errors be corrected via edits?

Today I found an answer from a new poster that has a syntax error in it. As far as I can tell, I have a few routes to attempt to deal with this: Edit the post directly without commenting - this means the original poster of the answer doesn't get an attempt to fix the issue themselves, but does...

discussion

Faheem Mitha

So, if the letter refers to a letter of 9th Sep from FOO to BAR, The mini-correspondence table would include a line for that letter, as well as lines in the mini-abbreviations table for FOO and BAR.

The question is whether constructing that mini-abbreviations table would be better done using SQLite/SQL or Lua (by extracting the relevant information from the SQL DB).

There's a certain appeal in getting the DB to handle such things (say by constructing a temporary table), but there may be no clean solution.

I know SQL isn't a general programming language.

Andriy M

@George.Palacios I don't know why but for some reason I thought it was about (English) grammar, not about syntax in code. That's a whole other story then

George.Palacios

@AndriyM Yeah. Grammar is important to fix but the code side of it is a bit different

or could be at least

@FaheemMitha When you say "better", what do you mean? Best practice? Performance? Future usability?

Faheem Mitha

@George.Palacios Good question. I suppose simplicity would be one criterion. When SQL methods work, they are usually quite simple. And I also like it when DB type stuff is handled by the DB. Assuming it's a reasonable thing to do.

George.Palacios

9:06 AM

I think at that point it becomes fairly opinion based

For me, I'd like the DB to do that, potentially even persist those datas in a table with triggers or otherwise

But I'm a DBA. I'm always going to point to the database for answers

Faheem Mitha

As far as best practices go, sure, it's always good to follow best practices, though in this case, I'm not sure what the best practice would be.

@George.Palacios Well, the question is whether it's even possible without excessive gyrations.

George.Palacios

@FaheemMitha I'm not sure what you mean by excessive gyrations?

Faheem Mitha

@George.Palacios Compiex manipulations.

Like, say calling a function from inside the DB. At that point, the Lua solution would probably look better.

Though actually, I don't know if SQLite provides such functionality. I know PG does.

Functionality meaning, writing functions in some language to be called from inside the DB. Not sure what the technical term for that is.

Andriy M

9:23 AM

What I don't understand is how you would go about producing both the relevant portion of the conversation table and the relevant portion of the abbreviation table, in SQLite. I mean doing that without a lot of duplication of the logic.

I know how I could to that in SQL Server, for instance. But that's because T-SQL allows you to have an arbitrary number of statements in a script, so I can generate one subset, store it as a temporary table, use it to generate the other subset, store it as another temporary table. And then I'll have my client code to just open both temporary tables and work with them as required.

ypercubeᵀᴹ

@George.Palacios I would edit anyway. Perhaps add a comment under the answer, too.

@ConcernedOfTunbridgeWells i'm definitely in, especially if Phil is coming all the way down here!

ConcernedOfTunbridgeWells

9:47 AM

Paging @Phil ...

@JackDouglas @MarkStoreySmith @MartinC @MartinSmith See ^^^^^^^^

@JamesLupolt - you're back in Murica, aren't you?

Master Database

0

Q: flagging clarification for low level answers

on this answer which basically all it says: Edit the connection and be sure to select the version of mysql installed on your PC in the "Local Client" section I believe it is a low level answer, but it might help someone, so I am in doubt whether or not to flag it. How can I get this cla...

discussion flagging answers moderation

Josh Darnell

12:19 PM

@hot2use Thanks!

Faheem Mitha

12:39 PM

@AndriyM For simplicity, let's say that I have a list of the RowIDs for the correspondence table. Then the question is whether I could use that to get the relevant subset of the abbreviation table. Not sure what you mean by "a lot of duplication of the logic".

BTW, when I tried doing an outer join in SQLite, I got an error message says it was not currently implemented.

George.Palacios

@FaheemMitha I think you need to post a question. It's quite complex what you're asking and having the requirements laid out in a post would really help as I'm struggling to understand the question at the moment

Andriy M

@FaheemMitha I was assuming that the starting point for both subsets would be the same. As you explained, it would be some (presumably new) conversation referring to an older conversation. So you would use that to pull the older conversation, for display purposes. Then you would need to do that again but this time to get the relevant abbreviation subset based on that older conversation.

Since I was discussing SQLite-only option, I didn't think you would be using client code to pass the ID(s) of the found conversation to get the abbreviations

Basically, your remark means SQLite-only solution doesn't seem practical and you do need to include client code into the equation.

Faheem Mitha

1:30 PM

@George.Palacios Ok, will do. It's not really that complex, but I suppose discussing without a concrete example makes it seems more complex than it really is.

But can anyone tell me whether SQLite really doesn't implement an outer join? Isn't that a basic thing?

@AndriyM I thought that some kind of join between the correspondence and the abbreviations table might work. But I'd post a question.

I'd -> I'll.

Andriy M

2:02 PM

@FaheemMitha That join may well be possible to implement. But you seem to need the conversation subset both to be returned to the application and to be used for finding abbreviations, which you also need to return to the app. That's what makes me doubt if you can do that in SQLite only, without client code mediating in-between and without duplicating pieces of logic.

Faheem Mitha

@AndriyM Hmm. Well, I'll try to confine my question to the database as much as possible.

This probably isn't a conventional DB question. I'm guessing you don't get a lot of LuaTeX related questions here.

Andriy M

I don't know if it matters much whether it's LuaTeX or any other client-side language. It will probably be down to what you are happy to live with. Duplication of logic might not be too bad if you are not duplicating much. On the other hand, if you are comfortable juggling data in client code fetching it from one query's output and passing it as another query's input, then go for it. I know you asked about best practices, but I agree with others that this specific case is a matter of opinion.

ypercubeᵀᴹ

2:24 PM

@FaheemMitha regarding OUTER joins, SQLite surely has LEFT outer joins: sqlitetutorial.net/sqlite-left-join

I think it doesn't have FULL outer joins but who cares about them ;) You can always emulate them.

George.Palacios

FULL outer joins are weird

much prefer FULL inner joins

ypercubeᵀᴹ

@George.Palacios this needs JEAGL ;)

@George.Palacios you are near London, right? We might have a meeting next Saturday, see discussion above (and below). You are most welcome to join us.

George.Palacios

@ypercubeᵀᴹ Leeds unfortunately.

But yeah I'm hoping to make one of them soon :)

Andriy M

@ypercubeᵀᴹ So that it could be a full inner join

George.Palacios

ha!

JEAGL: All inner joins are FULL

Andriy M

2:40 PM

Full joins, on the other hand, are often very cross

5

George.Palacios

You're doing well today with these

Andriy M

Thank you. Usually when that happens I've got a lot of work to do. And guess what, I've got a lot of work to do now. But instead I'm doing well with these

Paul White

Let's hope you don't find yourself FULLy OUTER work

(fully out of work - sacked due to spending too much time on puns in The Heap JEAGL)

Faheem Mitha

@ypercubeᵀᴹ I should review what all those terms mean. If I had to take a test on it right now, I would fail.

Andriy M

@PaulWhite If it happened, it would be RIGHTly so. But it's unlikely. Appreciate the concern though. And in any event I shouldn't abuse their trust, of course.

Paul White

2:57 PM

I was pretty sure it wouldn't APPLY in your case

George.Palacios

@AndriyM you've really APPLY'd yourself today

Damn.

Faheem Mitha

Is it possible to do a left join on more than one column? Or would one need to use two copies of a table?

Andriy M

You can reference however many columns in the joining condition. Whether you need to join one instance or two is another matter altogether.

Lamak

@TomV thanks….that was such a terrible game from Chile

ypercubeᵀᴹ

Faheem, yes, it is possible. The ON condition is arbitrary. It usually is a. Col = b. Col but it can be anything.

Paul White

3:20 PM

ON 😎 = 🙄

Faheem Mitha

I should have been more precise. Let's say there are two joins, between table A and B. Can both the columns for A that are joining with B, be the same?

BTW, historically it wasn't possible for TeX to talk to databases. It only became possible relatively recently, with the advent of LuaTeX. Which is pretty cool, really.

Paul White

dbfiddle.uk/…

4

Josh Darnell

That's delightful.

George.Palacios

@PaulWhite I don't want to see that database

Andriy M

Then use just the 😎

George.Palacios

3:28 PM

Emojis as column names is like some 7th level of hell situation

JOIN 😀 ON 😒=😢

Actually that's probably Twitters schema

Andriy M

JOIN 🐦, then

George.Palacios

hehehe

ConcernedOfTunbridgeWells

@PaulWhite 😲

🤮

Paul White

lovely

ConcernedOfTunbridgeWells

You're welcome.

In related news, my suggestion for allowing emoji in Lloyd's underwriting references to work around the 12 character limit was not accepted.

George.Palacios

3:47 PM

12 character limit? That's pretty small

ConcernedOfTunbridgeWells

@George.Palacios Legacy EDI system, plus they have to get 60-odd managing agencies to update their systems in order to change it.

George.Palacios

Ha yeah I get that. I worked in Energy recently and the whole industry still uses non-encrypted FTP to send all your usage details around the country

There's also an app that is required by all companies to interface with... Elexon I think. And the app only works on XP hahahaha

Or it might be national grid

either way it's slightly comical that this is how our energy infrastructure is run

Paul White

4:08 PM

New dba.se help page How to create a Minimal, Complete, and Verifiable Example for database-related questions

7

You can also refer to it as dba.stackexchange.com/help/mcve

magic links require code changes so there will be a delay on that

Lamak

nice

Max Vernon

4:57 PM

@PaulWhite by "magic" link, do you mean referring to it in comments, etc, as [mcve] or something?

Paul White

@MaxVernon yes

Max Vernon

perfect

thanks for making that stuff happen Paul - much appreciated!

Paul White

379

A: Add data.SE style "magic links" to comments

Most of these now work. My comment below has the following markdown source: On [main], you are expected to write proper English (as advertised on [english.se]), but here on [meta.se] it's more important to have freehand circles, so please [edit] your post, otherwise I'll have to flag you (see...

we should probably update it fairly soon to match the recent changes to the SO page stackoverflow.com/help/minimal-reproducible-example

well not necessarily match, more take account of

MDCCL

@PaulWhite very neat. much appreciated!

Paul White

5:12 PM

16

Q: Minimal, Complete, and Verifiable Example

How can I best ask my question so people will be able to understand it and reproduce the problem if necessary?

discussion faq asking-questions

Faheem Mitha

6:58 PM

I seem recall something in SQL where you can give a query a name, and then you can select from it using that name. Something like select foo.x from (select x, y from d) as foo.

Can someone give me a reference for this? I'm not finding it right now. With SQLite, if that makes a difference.

ConcernedOfTunbridgeWells

Not a squeak from @Phil. Hm.

Faheem Mitha

Never mind, I don't think that was a well-posed question. Please ignore.

ypercubeᵀᴹ

9:15 PM

@FaheemMitha that is called a derived table. foo is the alias.

There are also views, which have a more permanent life. Derived tables live inside the query only.

Paul White

@ConcernedOfTunbridgeWells He's taking a break from chat

Jun 18 at 10:20, by Philᵀᴹ

I realise that I'm probably being overly negative on here as of late, so I'm going to take a break. Ta ta.

ConcernedOfTunbridgeWells

@PaulWhite I've just messaged him on Linkedin.

Paul White

Ok cool

ConcernedOfTunbridgeWells

Probably best to put it off unless he replies quickly.

Andriy M

10:40 PM

@FaheemMitha Latest versions also support CTEs (Common Table Expressions). If you define a query as a CTE, you can call multiple times in the same query. It's basically a locally defined view. Finally, you can define the query as an actual view too.

WITH
  cte AS
  (
    SELECT
      ...
  )
SELECT
  ...
FROM
  cte AS t1
  INNER JOIN ...
  INNER JOIN cte AS t2 ON ...
WHERE
  ...
;

Transcript for

The Heap™ – Consultancy ©®