Empty string as the last item of a list

[sritcp]

There is a thread under "Engine Contributors"
http://forums.livecode.com/viewtopic.ph ... tem#p82865
that discusses this issue. A comment states

I don't have any code that relies on the current weird situation, and I don't have a notion of who might have code that does.

I have a database in a datagrid table that has around 2000 records and 25 columns. It is getting a bit slow, so I wanted to write it to an SQLite database and work hereafter from the database.

Most of the records have the last column value as empty (in fact, many of the later columns are empty). This prevents me from writing to a database using INSERT command.

For example,
put the dgText[true] of group "Master Table" into tList
set itemDel to tab
put line 1 of tList into tCols
put the number of items in tCols -- returns 25, the number of columns
put line 2 of tList into tRecord
put the number of items in tRecord -- returns 24 (if the last col value is an empty string)

So I am unable to construct an SQLite INSERT query easily (the numbers have to match).
(Yes, I can get around by figuring out if the total number of values is less than the column number, etc., but I was looking for a simpler solution).

mwieder mentions

And I forgot to mention that there's another way out as well: a global session property that can be set from a script, something like
set the ignoreEmptyLastItem to false
where the default value is true for existing behavior

What would this script look like?

Thanks,
Sri.

[dave.kilroy]

Hi sritcp

I found it hard to follow the gist of that thread because so many postings in it (by Hermann) had been reduced to a line of dots.

However I would say it should be fairly easy to work with the data that comes out of your datagrid and either amend that to suit a standard INSERT statement or else amend the INSERT statement to suit each row of data from the datagrid - it rather depends how you are populating your SQLite db...

Don't think I've ever taken dgText out of a datagrid with potentially empty final 'cells' in rows, but in such situations I would think that the final char in your tRecord would be a tab - if you test for this eventuality and if found, add a space or other character your standard INSERT statement should still work?

On the other hand it sounds like you have multiple colums that are empty in which case you may need to use something like

Code: Select all

replace tab & tab with tab & space & tab in tRecord

And finally, it's been a while since I built up a SQLite db with code but I seem to remember you only need to specify items in an INSERT statement that you want populated with data and that if you omit to reference a 'column' its cell will still be created but will be empty - but like I say it's been a while since I did this and I could well be mistaken

Kind regards

Dave

[sritcp]

Hi Dave:

Thanks for your suggestions.

Yes, you could use just the needed columns in the INSERT statement.
I was creating and populating the table for the first time, so I was feeding each record in its entirety.

Yes, I could test each record and add a space. I wanted to avoid having to do that and was looking for something like what mwieder had suggested (if I understood him correctly).
Also, putting a space in place of empty makes it difficult to SELECT a row where some column is (or is not) empty.

Regards,
Sri.

[Mikey]

The thread that you are talking about is us discussing ways to fix this problem. It is most certainly a PITA, and, honestly, the easiest way to deal with it, IMHO, is to always append a tab to the end of every line before you walk the lines to manipulate the DB, because if you have an empty last item, it will now be delimited, and the new last item will now be empty, and if the last item is not empty, once you append a tab to the end of the line, it will now be treated as empty and ignored.

Let's do it with commas because I think it's easier to see, and for non DB'ers, who are used to using comma as the delimiter instead of tab, it will make more sense:

a,b,c
a,b,
The number of items in line 1 is 3 and in line 2 is 2. (Insert screaming)

Add a comma to the end of all lines
a,b,c,
a,b,,

The number of items in line 1 is 3 and in line 2 is 3 (Insert gnashing of teeth, because this is stupid that we have to kluge like this, but, we do). The thing that is really silly is that if you read through the engine code, the current behavior is actually slower, because it adds an extra comparison when the engine is looking at items (one line of code is added in the engine to make it do this). Granted it is not significantly slower, but it is silly.

SAY IT WITH ME NOW: "ALL ITEMS WERE CREATED EQUAL, INCLUDING THE LAST ONE"

[sritcp]

Hi Mikey:

.... the easiest way to deal with it, IMHO, is to always append a tab to the end of every line......

Of course! Why didn't I think of that?

I think the current engine behavior is to protect people who forget to strip the last itemDel char after a loop!

Thanks,
Sri.

[Mikey]

Actually, HyperCard, LC's grandfather, had this behavior, as well, and so LC decided to maintain it to protect the senior citizens who didn't want to update their code when they ported it to LC, which was then MetaCard, and then RunRev, some millenia ago. Mind you, I had numerous projects from that era, too, so I'm not just mocking others who remember the 80's, or still have a Mac 512, or an SE, or a II, or a Portable.

I've had the argument with the staff about this, but even though I've gotten agreement from the insiders that the DB routines should change if the base behavior does not, I'm not holding my breath.

[FourthWorld]

Every now and then some folks who express dismay at Apple's decision to define item delimiting as it's been done in xTalks since HyperCard 1.0 premiered in 1987.

While the thousands of articles about code revisions needed to account differences between Python 2 and Python 3 should make us grateful that backward compatibility is a priority with LiveCode, it's also true that it's not the only priority. There are rare occasions when language elements have been changed in ways that break backward compatibility, and while this one would affect an unknowably large amount of code accrued in our community over the LiveCode engine's 20+ year history, I agree that it's generally healthy to reexamine even long-held conventions now and then.

In this case, however, the keeper of the engine, LiveCode lead engineer Mark Waddingham, has given this considerable thought to this issue, with perhaps the best summary of his thinking on this expressed in this post, in which he includes:

It is my present opinion that the current behavior of the engine when manipulating (nullable) string lists is not a bug nor it is not a quirk; it is simply how things have to be if one wants a logical and consistent semantic of working with them. Indeed, I really do think the HyperCard engineers thought very carefully about this over 26 years ago, and it is why they chose the semantic they did.

http://forums.livecode.com/viewtopic.ph ... =15#p82959

The rest of the post explains in detail why he feels that the current implementation is a very good one for LiveCode's language goals.

Maybe I should note that I don't have a horse in this race: I have an unusual view of xTalk in which I just don't find syntax details all that interesting most of the time; for me the draw is the self-evident message hierarchy and integrated GUI elements. So I don't have an opinion on this myself, noting Waddingham's comments here only to foster an appreciation that there are different ways to view this, and the current view of the engine maintainers on this seems unlikely to change.

[FourthWorld]

I've had the argument with the staff about this, but even though I've gotten agreement from the insiders that the DB routines should change if the base behavior does not, I'm not holding my breath.

I think you pegged it there about the biggest downside to the current implementation being specific to its impact on the DB externals.

In the thread linked to earlier Waddingham has already acknowledged that, and a rewrite of the DB externals is on the Road Map for after the Open Language projects is completed, so the new DB routines can take advantage of Open Language.

[Mikey]

...and the problem is exacerbated because the biggest consumers/loopers are going to be DB operations, anyway, so we slow the code (a wee bit) already because of of treating empty last operators as if they were never born, and then we make it worse, because in order to overcome that behavior, we have to either check to see if the last item of each line (record) is empty, or append a delimiter to the end of each of the lines and then deal with them.

All items are created equal, including the last one.

[dunbarx]

Zzzzzzz.

Whup? Old-timers? Huh?

Zzzzzz.

Craig

[dave.kilroy]

Actually I'm hoping that they will at the very least tweak the SQLite db externals long before Open Language is completed!

and a rewrite of the DB externals is on the Road Map for after the Open Language projects is completed

See http://quality.runrev.com/show_bug.cgi?id=14295

[jacque]

I still don't see what's so hard to grasp about this concept:

A delimiter terminates the string it follows.

This is neither stupid nor dated, it's just how it has to work in this language. Any other implementation introduces problems that are harder to solve and which would require more workarounds.

[phaworth]

I'm not going to get involved in the last item discussion but in this particular instance, the problem is solved by using the dgData property of the datagrid to return an array keyed by line number with a subkey for each datagrid column. If you only need the data from one line, use dgDataOfIndex/dgDataOfLine to get an array keyed by column name for the line. If you only need the contents of a single column, use getDataOfLine/getDataOfIndex.
Pete

[Mikey]

Your definition is incomplete, which is how we got here. Item delimiters, (and line feeds, and the ends of files) all delimit the strings they follow, UNLESS that string is the very last "item" before that item delimiter or line feed or end of file AND that item is empty, in which case, what would otherwise be the last item is treated as if it doesn't exist (and the itemDelimiter that precedes the empty string is also treated like it doesn't exist).

I don't believe I referred to the behavior as "stupid", I believe I called it silly, both because the behavior is that convoluted thing I described above, AND, because it breaks LC's own database routines:

SELECT a,b FROM myTable
If b is null then the string that is returned will be "a<tab>"
If I turn around and use that string to try to INSERT INTO myTable, I get an error message, because I have only provided a single value, which is why I have to append a tab to the end of every line before I start inserting records.

If it is not "dated", then why does every argument FOR this behavior start and end with "legacy code"?

Here are are your two choices for delimiter behavior:
The behavior we have now (see the complete definition at top), OR
A delimiter is a sequence of one or more characters used to specify the boundary between separate, independent regions in plain text or other data streams.

I get that I am going to have to tolerate the "it's a terminator except when it isn't". Make that behavior consistent so I can roll my eyes instead of throwing a mouse.

[dave.kilroy]

I neither have a horse in this race nor a dog in this fight - but I like Pete's suggestion of using dgData instead of dgText when dealing with datagrids with potentially empty last items - and thereby swerve around off all racing horses and fighting dogs...