SQLite in SketchUp Ruby

Dan Rathbun

UPDATE: can't use << append on an ENV element, we get:

%(#BF4000)[Error: #<TypeError: (eval):151:in<<': can't modify frozen string>]`

Need to modify the code above:

if RUBY_PLATFORM.include?('darwin')
  # Mac OSX
  binPath = "/Library/Application Support" # guess!
  # perhaps; "/Applications"
  # or; "/usr/bin"
  sql3path = File.join(binPath,"SQLite3")
  #
else
  # Windows
  binPath = ENV['ProgramFiles']
  sql3path = binPath + "\\SQLite3"
end

(ENV['PATH']= ENV['PATH'] + (File;;PATH_SEPARATOR + sql3path)) unless ENV['PATH'].include?(sql3path)
# if above does NOT change PATH, nil is returned.
# Otherwise the new PATH string is returned.

NOTE that this will change the Embedded Ruby's COPY of the Environment, NOT the system's (or user's) set of Environment vars.

To change the User Environment, use a batch cmd file, or have the user do it themselves through:
Control Panel > System > Advanced (tab) > Environment Variables (button)

The "Environment Variables" dialog pops up. 1. Highlight the PATH var in the lower ("System") pane, 1. and click the "Edit" button. 1. Press the "End" key (on the keyboard,) 1. and add " %(#4040BF)[;C:\Program Files\SQLite3]"

(without quotes) to the end of the PATH string.
Don't forget the " %(#4040BF)[;]" separator at the beginiing.

Now next time Sketchup and it's embedded Ruby are restarted, their COPY of the Enviroment will have the new search path for SQLite3.

Dan Rathbun

It's nice that you got it working under Sketchup.. but...

Taking files out of their proper place in the standard Ruby lib directories, can create confusion, and make version management difficult. Once the files are moved...

How do you know what version Ruby or SQLlite they came from ??
How do you easily update them ?

Why can't you just leave them where they are.. and push the Ruby lib paths into the $LOAD_PATH array, as I do in my !loadpaths.rb example script ??

Why on PC does the sqlite3.dll file need to be in the Sketchup program dir ??

Can't an absolute path be used?* Or better yet, put the files in a "%ProgramFiles%/SQLite3" directory, and add that subpath to the PATH environment variable.
This way the files can be maintained (updated,) and they will not get wiped out if the Sketchup application is uninstalled/re-installed, etc.
In ruby, it's:

#
# see corrected code sample next post...
#

Also the command line utility can be put in that same dir, and now it's ready to call from anywhere you are in the cmd shell.

mptak

I revisit this thread as the folk at http://www.learningequality.org are using sqlite for some awesome Creative Commons work to bring education and learning to the places which can make good use of both Trimble sketchup make and stuff in the creative commons. Anyone know if this sqlite3 stuff is all still possible? And seriously check out their mission if you are interested in some Python mixed with your Ruby.

TommyK

I know this is an old thread, but I wonder if anyone has had any further successes? I have been trying for the last 3 hours to get SQLite3 working on OS X without success. Now that I read this thread, I wonder if it is even worth trying to get a local database in Ruby....

driven

Tommy, what did you try?

it's builtin so

%x(cd #{your_database} && /usr/bin/sqlite3 .help 2>&1)

, should give you the options...

else, if you have a small sample db I could have a look...

john

TommyK

@driven said:

Tommy, what did you try?

it's builtin so

%x(cd #{your_database} && /usr/bin/sqlite3 .help 2>&1)

, should give you the options...

else, if you have a small sample db I could have a look...

john

Thanks for your response.

I was trying to use the Ruby-sqlite3 and had no success - ie require 'sqlite3' returns an error.

What I think you are suggesting is that I should use sqlite3 through shell commands in ruby? Is this a simple way of doing this?

By the way the following returns an empty string (am I doing something wrong):
sql = %x(cd #~/Desktop/sp500data.sqlite && /usr/bin/sqlite3 .help 2>&1)

driven

the # in your call is commenting out the rest... [same as in ruby]

I'm not total sure of the rest of the command as I don't have a db to test against...

driven

Tommy, start from this

%x(sqlite3 --help 2>&1)

TommyK

So I got as far as this, using back ticks:
sql = %x(sqlite3 ~/Desktop/sp500data.sqlite "SELECT * FROM companies;")
I am getting an output.

I have two concerns:

I have to open the database with every call. Isn't this performance heavy? Is there a way to open a database and keep it open for subsequent queries?
I can only get values back from shell as a string. I can parse the string into arrays and objects, but it doesn't feel right - is there a better way?

FYI I am using a database from here: http://ruby.bastardsbook.com/files/projects/sp500/sp-500-historical-stock-prices.zip

Thanks for your engagement - it is getting me somewhere, at least.

driven

you can set the format for the output, so for a webDialog you can use...

html = %x(/usr/bin/sqlite3 -html /private/tmp/sp500-data.sqlite "SELECT * FROM companies;")

or there's -csv -list -line -column...