Skip to content

DB4S non-compliant with RFC-4180 #2445

New issue
New issue
Open
#4246
Open
DB4S non-compliant with RFC-4180#2445
#4246
Labels
bugConfirmed bugs or reports that are very likely to be bugs.import

Description

@rgward611
rgward611
opened on Oct 17, 2020

Details for the issue
#--------------------
Background: Reference closed bug report #2442
After placing a bug report on SQLite Forum on the SQLite CLI on issue with .import command, it appears the issue (stripping of spaces in header column names when constructing the CREATE TABLE command) is with DB4S not compliant with RFC-4180 and stripping spaces from the header column names when they should be ignored.

RFQ-4180 says "Within the header ... spaces are considered part of a field and should not be ignored." It seems like DB4S is the one that is not following the spec, not the SQLite CLI.

What did you do?

Details in bug report #2442

What did you expect to see?

What did you see instead?

Useful extra information

The info below often helps, please fill it out if you're able to. :)

What operating system are you using?

  • [x ] Windows: ( version: 10_ )
  • Linux: ( distro: ___ )
  • macOS: ( version: ___ )
  • Other: ___

What is your DB4S version?

  • [ x] 3.12.0
  • 3.11.x
  • Other: ___

DB4S v3.12.0 [built for x86_64-little_endian-llp64] on Windows 10 (10.0) (winnt/10.0.19041) [x86_64]
using SQLite Version 3.32.2
and Qt 5.12.8
SQLite Tools Versions 3.33

Activity

  1. chrisjlocke commented on Oct 17, 2020

    @chrisjlocke
    chrisjlocke
    on Oct 17, 2020
    Member

    Can you email me (or post here) the headers from your CSV? So the first line showing the field names (as they are in the CSV). I can see how the end schema is, but just want to see what the import file looks like.
    For the avoidance of doubt, you've used DB4S to import a CSV and got column headers without spaces (dateofbirth instead of 'date of birth')
    You've also used the .import tool, and that's created correct field names ('date of birth')

    When importing, do you recall the settings used?

    image

    So a field separator of a comma, for example, quote character of a double-quote, etc.

  2. chrisjlocke
    added
    bugConfirmed bugs or reports that are very likely to be bugs.
    on Oct 17, 2020

  3. chrisjlocke commented on Oct 17, 2020

    @chrisjlocke
    chrisjlocke
    on Oct 17, 2020
    Member

    Confirmed that the import of a table ignores spaces in the CSV, and adding quotes doesn't do anything either.
    https://streamable.com/i5s72j

  4. rgward611 commented on Oct 17, 2020

    @rgward611
    rgward611
    on Oct 17, 2020
    Author

    Here are DB4S Import settings I get on Import CSV file...note the preview of column names without spaces.
    image

    Below is header row in CSV file. Not sure how I can attach/link to my source CSV file (HOAexportusers_100620_test.csv) referenced in my script.

    Chapter Group Chapter Name Last Login Date Membership Title Property Acct. # Property Address Property Group Property Name Property Unit User Status First Name Last Name Company Name Access Level Add Date Address Admin Notes Chapter ID Email Address Expiration Date Group ID(s) Join Date Last Modified Membership Type ID Profile Photo Show Profile Status User ID User Level Primary User Username 2019 Audit Completed 2020 Audit Completed 2020 RIF Received Alt Address Alt Phone Cell Phone Date of Birth Emergency Contacts Emergency Notes Garage Number Home Phone No Home Computer Pet 1 AACo TagNumber Pet 1 AACo Validated Pet 1 Breed Pet 1 Classification Pet 1 Name Pet 1 Notes Pet 1 Remove Pet Pet 1 Tag Expiration Pet 1 Type Pet 1 Weight Pet 2 AACo TagNumber Pet 2 AACo Validated Pet 2 Breed Pet 2 Classification Pet 2 Name Pet 2 Notes Pet 2 Remove Pet Pet 2 Tag Expiration Pet 2 Type Pet 2 Weight Pet 3 AACo TagNumber Pet 3 AACo Validated Pet 3 Breed Pet 3 Classification Pet 3 Name Pet 3 Notes Pet 3 Remove Pet Pet 3 Tag Expiration Pet 3 Type Pet 3 Weight Profile Updated Reserved Parking Resident Status Unit Number Vehicle 1 CC Sticker Vehicle 1 Color Vehicle 1 Make Vehicle 1 Model Vehicle 1 NeedStickr Vehicle 1 Notes Vehicle 1 Remove Vehicle 1 State Vehicle 1 Tag Number Vehicle 1 Verified Vehicle 1 Year Vehicle 2 CC Sticker Vehicle 2 Color Vehicle 2 Make Vehicle 2 Model Vehicle 2 NeedStickr Vehicle 2 Notes Vehicle 2 Remove Vehicle 2 State Vehicle 2 Tag Number Vehicle 2 Verified Vehicle 2 Year Vehicle 3 CC Sticker Vehicle 3 Color Vehicle 3 Make Vehicle 3 Model Vehicle 3 NeedStickr Vehicle 3 Notes Vehicle 3 Remove Vehicle 3 State Vehicle 3 Tag Number Vehicle 3 Verified Vehicle 3 Year Work Phone

  5. rgward611 commented on Oct 17, 2020

    @rgward611
    rgward611
    on Oct 17, 2020
    Author

    Woah! sorry for the huge image. not sure how to edit and resize.

  6. chrisjlocke commented on Oct 17, 2020

    @chrisjlocke
    chrisjlocke
    on Oct 17, 2020
    Member

    Thanks for posting them. Its OK about the CSV file, as I've sort-of proved there is an issue using a simple 'field 1, field 2, field 3' example.
    Thanks for posting the issue. :)

  7. mgrojo commented on Oct 18, 2020

    @mgrojo
    mgrojo
    on Oct 18, 2020
    Member

    We're removing a set of "problematic" characters:

    ` "\,;

    But they wouldn't be problematic for us, since we are supposed to quote properly all the table identifiers. So I guess it was included as a convenience for the user. Should we remove this automatism, so we don't remove anything which is in the field name according to the used separators? I guess that removing space from that set would be the safest approach. I don't see why anyone would like to use the others in a column name.

    But maybe some users are already used to that convenience...

  8. MKleusberg commented on Oct 25, 2020

    @MKleusberg
    MKleusberg
    on Oct 25, 2020
    Member

    Should we remove this automatism, so we don't remove anything which is in the field name according to the used separators? I guess that removing space from that set would be the safest approach. I don't see why anyone would like to use the others in a column name.

    But maybe some users are already used to that convenience...

    Let's definitely remove the space character. But I would also vote for removing this automatism altogether. I can't imagine that many people rely on it and it's kind of random anyway (why remove the comma but keep the dot? Why remove double quotes but keep square brackets? etc.). If somebody complains about us removing this feature I'd suggest implementing it properly. What do you think, @mgrojo?

  9. sky5walk commented on Oct 25, 2020

    @sky5walk
    sky5walk
    on Oct 25, 2020

    True, DB4S is a SQLite browser with features beyond the underlying database.
    The DB4S import gui should encompass the base rules of CSV, but not limit them.
    My use of Trim may differ from the standard.
    To accomplish both, extend the gui.

    Please ADD:
Import CSV file -> ADVANCED -> [±] Trim endpoints only
I prefer Trim only operate on the endpoints of data between the delimiters.

    Ex. 
IMPORT -> "1,2,   hello world   , blah ,Z"
RETURN -> "1,2,hello world,blah,Z"
NOT    -> "1,2,helloworld,blah,Z"
  10. mgrojo
    added a commit that references this issue on Feb 26, 2021

    Import: do not remove characters from CSV header fields

    248a630

  11. mgrojo commented on Feb 26, 2021

    @mgrojo
    mgrojo
    on Feb 26, 2021
    Member

    I've followed @MKleusberg suggestion, and now we don't remove any character from the header field names. This should be good enough for the time being.

  12. chrisjlocke commented on Apr 1, 2021

    @chrisjlocke
    chrisjlocke
    on Apr 1, 2021
    Member

    @rgward611 - Can you confirm if you're happy with this change, and all is now working OK?

  13. chrisjlocke
    added
    import
    response requested
    on Apr 1, 2021

  14. rgward611 commented on Apr 1, 2021

    @rgward611
    rgward611
    on Apr 1, 2021
    Author

    Not seeing any change. Spaces still removed. See screenshot. Notice HOAexportusers table was created using IMPORT table via bat script. HOAexportusers-test table was created using IMPORT via GUI.

    sqlitebrowser_2021-04-01

  15. chrisjlocke commented on Apr 1, 2021

    @chrisjlocke
    chrisjlocke
    on Apr 1, 2021
    Member

    I'm more interested in how the 'about' box got so squished.... 😯
    Frustrating that the fix didn't fix it. Thanks for confirming.

  16. chrisjlocke
    removed
    response requested
    on Apr 1, 2021

  17. rgward611 commented on Apr 1, 2021

    @rgward611
    rgward611
    on Apr 1, 2021
    Author

    Yep, that's how it pops up when I click on 'About'. It was quite convenient though for my screenshot.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugConfirmed bugs or reports that are very likely to be bugs.import

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions