Allowable characters in sqlite3 database file names

Post your Gambas programming questions here.
Post Reply
User avatar
Cedron
Posts: 136
Joined: Thursday 21st February 2019 5:02pm
Location: The Mitten State
Contact:

Allowable characters in sqlite3 database file names

Post by Cedron »

I'm running into a bit of quirky behavior and am wondering if anybody else has experienced it or can confirm it.

My program has an edit field to enter a database name, which in my rough draft I don't edit check, then the program either creates or loads the file depending upon whether the file exists.
'=============================================================================
Public Sub CreateDatabaseFile(ArgDirName As String, ArgFileName As String)

        Dim theDB As Connection = New Connection
        
        With theDB
          .Type = "sqlite3"  
          .Host = ArgDirName
          .Name = ""
          .Open()
          .Databases.Add(ArgFileName)
          .Close
        End With

End
'=============================================================================
Public Sub OpenDatabase(ArgDirName As String, ArgFileName As String)

        With MyDB
          .Type = "sqlite3"  
          .Host = ArgDirName
          .Name = ArgFileName
          .Open()
        End With

End
'=============================================================================
Here is the weird part, trying to add a filename in the create routine with any special character, except underscore, doesn't work. However, if you give it a plain name, rename the file externally, the open routine works just fine.

Before I code a workaround, e.g. create a plain named file and rename it, I'd like to know if there is something going on that I am not aware of. I haven't done that much DB programming in Gambas.

Thanks,
Ced
.... and carry a big stick!
User avatar
Quincunxian
Posts: 146
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Allowable characters in sqlite3 database file names

Post by Quincunxian »

Hi Cedron.
What are the special characters that are giving you the issue ?
Cheers - Quin.
I code therefore I am
User avatar
PJBlack
Posts: 173
Joined: Tuesday 9th June 2020 10:26pm
Location: Florstadt, Hessen, Germany

Re: Allowable characters in sqlite3 database file names

Post by PJBlack »

confirmed Behavior ...

but found this:

Naming Conventions for SQLite
Each database, table, column, index, trigger, or view has a name by which it is identified
and almost always the name is supplied by the developer.The rules governing how a
valid identifier is formed in SQLite are set out in the next few sections.

Valid Characters
An identifier name must begin with a letter or the underscore character, which may be
followed by a number of alphanumeric characters or underscores. No other characters
may be present.
User avatar
thatbruce
Posts: 60
Joined: Saturday 4th September 2021 11:29pm

Re: Allowable characters in sqlite3 database file names

Post by thatbruce »

Generally, and not specifically to SQLite, the engines tend to dislike (with extreme prejudice) anything but actual data with spaces in it. I think that must be because they rely on parsers that detect spaces as word boundaries.

But just as a "schoolroom" example, consider naming some file "a b c d goldfish". This would be a legal filename and would appear in a file manager fine, BUT just consider running

Code: Select all

less a b c goldfish
in a virtual terminal. What is going to happen?
Well, SQLite is generally not even as clever as bash.
b
User avatar
Cedron
Posts: 136
Joined: Thursday 21st February 2019 5:02pm
Location: The Mitten State
Contact:

Re: Allowable characters in sqlite3 database file names

Post by Cedron »

The naming conventions of databases have always been a pain. With sqlite and others, going forward, I am using split_with_underscore, all lower case names, no other special characters. But that is within the database.

This is apparently a Gambas' specific problem. The OS (Ubuntu on Raspberry Pi) has no problem, nor does the sqlite application. The quirk is that I can open a database with these characters, but I can't create one the regular way.

The name pattern that blew up first was: something{secret}goes~here

I am using the name as a four element data row in higher level databases, but prefer symbolically meaningful delimiters. I could also use three underscores instead.

I was thinking maybe my code wasn't proper. Should I report this as a bug?
.... and carry a big stick!
Post Reply