SQLite in SketchUp Ruby

mocathe1st · 28 Jul 2008, 11:23

Hi all,

I was just wondering if anyone has successfully managed to get SQLite or any other embedded database working with the SketchUp Ruby API.

I've been trying to get the sqlite-ruby module (http://sqlite-ruby.rubyforge.org/) working in SketchUp but keep getting driver errors after modifying the require paths and was wondering if anyone else has tried this or has any pointers on how to proceed.

Thanks,
Malcolm Murray

Jim · 28 Jul 2008, 19:57

No, but it's worthwhile. I've use SQLite-Ruby in a few non-SketchUp projects and it's just great.

For those who aren't familiar with SQLite. It's perfect for stand-alone applications that need a database.

I'm pretty certain I can get it working, but I won't have time until later this week.

Jim · 3 Aug 2008, 12:33

I tried for half a day yesterday and couldn't get SQLite working. I believe everything is in the correct locations, but when it comes down to requiring sqlite_api.dll, Sketchup, Ruby, or Windoze is being uncooperative.

AdamB · 5 Aug 2008, 05:36

Given things like this are going to use WebDialog, I'd planned to simply use server-side php to talk SQL

Adam

tbd · 5 Aug 2008, 07:24

hmm, tried with SQLite3 using Native driver and it worked (it "hangs" SU until the SQL commands are finished as usual)

here is my directory structure - copied some files from Ruby install that are required by SQLite and you need SQLite.DLL from SQLite distribution (it is not shipped with sqlite3 ruby):


[copied from Ruby install]
\Plugins\susqlite\date.rb
\Plugins\susqlite\parsedate.rb
\Plugins\susqlite\rational.rb
\Plugins\susqlite\time.rb

\Plugins\susqlite\date\format.rb

[SQlite3 files]
\Plugins\susqlite\sqlite3.rb
\Plugins\susqlite\sqlite3_api.dll
\Plugins\susqlite\sqlite3\constants.rb
\Plugins\susqlite\sqlite3\database.rb
\Plugins\susqlite\sqlite3\driver
\Plugins\susqlite\sqlite3\errors.rb
\Plugins\susqlite\sqlite3\pragmas.rb
\Plugins\susqlite\sqlite3\resultset.rb
\Plugins\susqlite\sqlite3\statement.rb
\Plugins\susqlite\sqlite3\translator.rb
\Plugins\susqlite\sqlite3\value.rb
\Plugins\susqlite\sqlite3\version.rb
\Plugins\susqlite\sqlite3\driver\dl
\Plugins\susqlite\sqlite3\driver\native
\Plugins\susqlite\sqlite3\driver\dl\api.rb
\Plugins\susqlite\sqlite3\driver\dl\driver.rb
\Plugins\susqlite\sqlite3\driver\native\driver.rb

AdamB · 6 Aug 2008, 07:37

@unknownuser said:

hmm, tried with SQLite3 using Native driver and it worked (it "hangs" SU until the SQL commands are finished as usual)

Hey, I hadn't thought of that. Because Ruby uses green threads, driving SQL from Ruby in SU is going to give a horrible user experience - very 'lumpy' interaction. I really think its a non-starter because of that.

Adam

iaselle · 28 Aug 2011, 17:41

Hello,

This topic is old but I'm having the same problem and I've just succeeded in solving this problem.
My plugin now works on an old Mac OS 10.5, on Windows 7 and Windows XP. (still to be tested on Mac os 10.6 and 10.7)
Furthermore, no need to install anything on a fresh machine except the plugin directory tree.

On your PC development machine, install ruby, grab the "sqlite3 gem" from http://rubyforge.org/frs/?group_id=254 and install it though "gem install ..."

On my Mac 10.5 development machine, I had to install rubyosx from http://rubyosx.rubyforge.org . I think that on Mac Os 10.6/7 it might be useless.

Get the appropriate files from the following directories (path to be adapted depending on your actual installation)

C:\ruby\lib\ruby\1.8
C:\ruby\lib\ruby\gems\1.8\gems\sqlite3-ruby-1.3.1-x86-mswin32-60\lib
/usr/local/lib/ruby/gems/sqlite3-ruby-1.2.5/lib
And copy those files in your plugin directory. You should have a directory tree as follows :


** main code of the plugin
./test.rb
**** lib files that are used by sqlite3 gem
./lib/date
./lib/date/format.rb
./lib/date.rb
./lib/initpath.rb
./lib/parsedate.rb
./lib/rational.rb
./lib/sqlite3.rb
./lib/time.rb
***** for Mac Os X only ***
./lib/sqlite3_api.bundle
******************
./lib/sqlite3macOs
./lib/sqlite3macOs/constants.rb
./lib/sqlite3macOs/database.rb
./lib/sqlite3macOs/driver
./lib/sqlite3macOs/driver/dl
./lib/sqlite3macOs/driver/dl/api.rb
./lib/sqlite3macOs/driver/dl/driver.rb
./lib/sqlite3macOs/driver/native
./lib/sqlite3macOs/driver/native/driver.rb
./lib/sqlite3macOs/errors.rb
./lib/sqlite3macOs/pragmas.rb
./lib/sqlite3macOs/resultset.rb
./lib/sqlite3macOs/statement.rb
./lib/sqlite3macOs/translator.rb
./lib/sqlite3macOs/value.rb
./lib/sqlite3macOs/version.rb
****** for windows only ********
./lib/sqlite3pc
./lib/sqlite3pc/1.8
./lib/sqlite3pc/1.8/sqlite3_native.so
./lib/sqlite3pc/1.9
./lib/sqlite3pc/1.9/sqlite3_native.so
./lib/sqlite3pc/constants.rb
./lib/sqlite3pc/database.rb
./lib/sqlite3pc/errors.rb
./lib/sqlite3pc/LISEZMOI.txt
./lib/sqlite3pc/pragmas.rb
./lib/sqlite3pc/resultset.rb
./lib/sqlite3pc/statement.rb
./lib/sqlite3pc/translator.rb
./lib/sqlite3pc/value.rb
./lib/sqlite3pc/version.rb

This is the test.rb source code :

# automatic installation  phase
# renaming the directories sqlite3macOs or sqlite3pc depending on the target system
mydir = File.expand_path( File.dirname(__FILE__) ) 

if (!File.exists?(File.join( mydir, "lib/sqlite3" )))
  if (RUBY_PLATFORM.include?('darwin'))
    if File.exists?(File.join( mydir, "lib/sqlite3macOs" ))
       File.rename(File.join( mydir, "lib/sqlite3macOs" ),File.join( mydir, "lib/sqlite3" ))
    end
  else
    if File.exists?(File.join( mydir, "lib/sqlite3pc" ))
       File.rename(File.join( mydir, "lib/sqlite3pc" ),File.join( mydir, "lib/sqlite3" ))
    end
  end
end

# once the sqlite3 directory has been renamed, you can use it
require 'lib/initpath'
require 'sqlite3'

# lauch the test ans see results in the ruby console
Sketchup.send_action "showRubyPanel;"
UI.menu("PlugIns").add_item("testsql") {
  testsql
}

# the tests consists in creating, populating and querying a database
# !!! (quick & dirty) no test is performed to checks existing elements !!!
def testsql

  puts "starting .."

  dbname = File.join( File.expand_path( File.dirname(__FILE__) ), 'test.sqlite' )
  database = SQLite3;;Database.new( dbname )
  database.results_as_hash = true
  
  database.execute( "create table sample_table (id INTEGER PRIMARY KEY, sample_text TEXT, sample_number NUMERIC);" )
   
  database.execute( "insert into sample_table (sample_text,sample_number) values ('Sample Text1', 123)")
  database.execute( "insert into sample_table (sample_text,sample_number) values ('Sample Text2', 456)")
   
  database.execute( "SELECT sample_text,sample_number FROM sample_table;" ) do |ligne|
    puts ligne['sample_text'].to_s  + " " + ligne['sample_number'].to_s
  end

end

Notice 1: On a Windows PC, you have to get sqlite3.dll (eg from http://www.sqlite.org/sqlitedll-3_6_16.zip) and put it in the same directory as SketchUp.exe itself.

Notice 2 : the initpath.rb adds the lib directory to the PATH used for searching required ruby scripts

Minimal working example

Dan Rathbun · 29 Aug 2011, 05:19

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 · 29 Aug 2011, 05:23

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 · 30 Jun 2013, 02:23

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 · 8 Dec 2015, 19:21

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 · 8 Dec 2015, 19:39

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 · 8 Dec 2015, 20:51

@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 · 8 Dec 2015, 20:56

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 · 8 Dec 2015, 21:23

Tommy, start from this

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

TommyK · 8 Dec 2015, 21:48

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 · 9 Dec 2015, 00:58

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...