Possible bug on Linux?

[reidrr]

Code looks fantastic, really useful. I've tried it out on a mac laptop to check out the features and everything worked perfectly. However, when migrating to a linux machine I ran into an issue I don't understand. I'm a complete novice to SQL so I may be overlooking something obvious. The error comes up testing the basic example from the documentation. Any thoughts?

ERROR: LoadError: SQLiteException("near \"/\": syntax error")
Stacktrace:
  [1] sqliteerror(handle::Ptr{Nothing})
    @ SQLite ~/.julia/packages/SQLite/5DdLp/src/SQLite.jl:20
  [2] sqliteerror(db::SQLite.DB)
    @ SQLite ~/.julia/packages/SQLite/5DdLp/src/SQLite.jl:114
  [3] macro expansion
    @ ~/.julia/packages/SQLite/5DdLp/src/consts.jl:24 [inlined]
  [4] sqliteprepare
    @ ~/.julia/packages/SQLite/5DdLp/src/SQLite.jl:190 [inlined]
  [5] SQLite._Stmt(db::SQLite.DB, sql::String)
    @ SQLite ~/.julia/packages/SQLite/5DdLp/src/SQLite.jl:122
  [6] Stmt
    @ ~/.julia/packages/SQLite/5DdLp/src/SQLite.jl:152 [inlined]
  [7] prepare
    @ ~/.julia/packages/SQLite/5DdLp/src/tables.jl:104 [inlined]
  [8] execute(conn::SQLite.DB, sql::String, params::Tuple{})
    @ DBInterface ~/.julia/packages/DBInterface/qcAwq/src/DBInterface.jl:122
  [9] query_local_db
    @ ~/.julia/packages/HITRAN/fRuff/src/database.jl:209 [inlined]
 [10] query_local_db(sql::String, params::Tuple{}) (repeats 2 times)
    @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/database.jl:210
 [11] (::HITRAN.var"#25#26"{Vector{String}})()
    @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/profiles.jl:160
 [12] get
    @ ./namedtuple.jl:298 [inlined]
 [13] get
    @ ./iterators.jl:273 [inlined]
 [14] parse_kwargs(tables::Vector{String}; kwargs::Base.Iterators.Pairs{Symbol, Dict{Tuple{Int64, Int64}, Float64}, Tuple{Symbol}, NamedTuple{(:components,), Tuple{Dict{Tuple{Int64, Int64}, Float64}}}})
    @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/profiles.jl:151
 [15] α(tables::Vector{String}, profile::Symbol; kwargs::Base.Iterators.Pairs{Symbol, Dict{Tuple{Int64, Int64}, Float64}, Tuple{Symbol}, NamedTuple{(:components,), Tuple{Dict{Tuple{Int64, Int64}, Float64}}}})
    @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/profiles.jl:210
 [16] top-level scope
    @ ~/Desktop/Science/GUPPE/LaserModRepo/hitran_tests.jl:6
 [17] include(fname::String)
    @ Base.MainInclude ./client.jl:444
 [18] top-level scope
    @ REPL[4]:1

[TacHawkes]

Hi,

just checking: Did you use a different name for the Table name or are you executing exactly the quickstart example?

[reidrr]

In the example above I was using a .sqlite file that I had created on the mac. Just to check the syntax I was using was:

db = pwd()*"/hitran/from_mac.sqlite"
fetch!(db, iso_id(["N2", "O2", "CO2", "H2O", "CH4"]), 12900, 13200, [:standard, :ht_self]);
wavenumbers, absorption_coefficient = α([db];
        components=default_environments[:dry_air]        
    )

I'm trying to use the table from the other computer because running HITRAN.jl from a totally clean install fails with a different error, copied below. The code successfully creates HITRAN.sqlite, the error originates on the call to alpha.

ERROR: LoadError: MethodError: Cannot `convert` an object of type Missing to an object of type Float64
Closest candidates are:
  convert(::Type{T}, ::Base.TwicePrecision) where T<:Number at twiceprecision.jl:250
  convert(::Type{T}, ::AbstractChar) where T<:Number at char.jl:180
  convert(::Type{T}, ::CartesianIndex{1}) where T<:Number at multidimensional.jl:136
  ...
Stacktrace:
 [1] parse_kwargs(tables::Vector{String}; kwargs::Base.Iterators.Pairs{Symbol, Dict{Tuple{Int64, Int64}, Float64}, Tuple{Symbol}, NamedTuple{(:components,), Tuple{Dict{Tuple{Int64, Int64}, Float64}}}})
   @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/profiles.jl:163
 [2] α(tables::Vector{String}, profile::Symbol; kwargs::Base.Iterators.Pairs{Symbol, Dict{Tuple{Int64, Int64}, Float64}, Tuple{Symbol}, NamedTuple{(:components,), Tuple{Dict{Tuple{Int64, Int64}, Float64}}}})
   @ HITRAN ~/.julia/packages/HITRAN/fRuff/src/profiles.jl:210
 [3] top-level scope
   @ ~/Desktop/Science/proto/hitran_test.jl:6
 [4] include(fname::String)
   @ Base.MainInclude ./client.jl:444
 [5] top-level scope
   @ REPL[9]:1
in expression starting at /home/me/Desktop/Science/GUPPE/proto/hitran_test.jl:6

[TacHawkes]

Ok, that explains something (but can be still considered a bug in a sense). The table names should not contain a slashes or other SQL-breaking chars at the moment.

In your example you are using the database parameter not as intended.

Try this instead:

db = open_database(pwd()*"/hitran/from_mac.sqlite")

# sets db as the database to use for this session:
current_db(db)

# note the table name argument "StdAtm", you can use a different name but do not use SQL breaking chars for now
fetch!("StdAtm", iso_id(["N2", "O2", "CO2", "H2O", "CH4"]), 12900, 13200, [:standard, :ht_self]);

# note that db has been swapped to the table name. The db is already specified
wavenumbers, absorption_coefficient = α(["StdAtm"];
        components=default_environments[:dry_air]        
    )

[reidrr]

Got it! You're right it is only kind of a bug, if I'd done a better job looking through the documentation I probably could have figured out that I needed a database and not just the path to the database.

Now that I have the ability to hit a local database, something that looks odd is happening. As long as I am connected to the internet everything works, but it looks like fetch!() is overwriting the existing database. For example is I initially call a range from 20 microns to 1 micron I get a spectrum and it produces .sqlite file that's about 150 Mb. If I then call a subset of that range for the same molecules, say 15 microns to 1 micron the the old .sqlite file is deleted and replaced by a 75 Mb file. Trying the same experiment but disconnecting the internet replaces the 150 Mb file with a 33 kb file and throws an error about converting a missing to a Float64 which looks like fetch!() is always deleting the old database and trying to download a new one. Am I overlooking something again?

[reidrr]

I played around with the problem and I think I've got a working solution. I've made a database with a large quantity of data and renamed the "StdAtm" table to "Master".

fetch!("StdAtm", iso_id(["N2", "O2", "CO2", "H2O", "CH4"]), 20e-6, 300e-9, [:standard, :ht_self]);
wavenumbers, absorption_coefficient = α(["StdAtm"];
        components=default_environments[:dry_air]        
    )

db = open_database(pwd()*"/HITRAN.sqlite")
DBInterface.execute(db," ALTER TABLE StdAtm RENAME TO Master")

From there I can create a temporary table "StdAtm" from "Master" using a subset of the data without changing the database on all future calls on an offline machine.

DBInterface.execute(db,"CREATE TEMPORARY TABLE StdAtm As SELECT * FROM MASTER WHERE nu BETWEEN "*string(ν_min)*" AND "
    *string(ν_max))

and everything picks up from there as normal.