From 1c07669d3798c1313612360719b8ca1cb559be62 Mon Sep 17 00:00:00 2001
From: Ralph Amissah <ralph@amissah.com>
Date: Sat, 29 Aug 2009 23:55:44 -0400
Subject: sql, fix, different solutions for postgresql and sqlite to populate
 database using ruby-dbi

---
 lib/sisu/v1/db_drop.rb   |  9 +++++++--
 lib/sisu/v1/db_import.rb | 46 ++++++++++++++++++++++++++++++++++++++++++----
 lib/sisu/v1/db_remove.rb |  2 +-
 lib/sisu/v1/sysenv.rb    |  5 +++++
 4 files changed, 55 insertions(+), 7 deletions(-)

(limited to 'lib')

diff --git a/lib/sisu/v1/db_drop.rb b/lib/sisu/v1/db_drop.rb
index bed179b7..258b31c1 100644
--- a/lib/sisu/v1/db_drop.rb
+++ b/lib/sisu/v1/db_drop.rb
@@ -95,7 +95,9 @@ module SiSU_DB_drop
               File.unlink(@db_info.sqlite.db)
             end
           else
-            @conn.do(@drop_table.join(''))
+            @drop_table.each do |d|
+              @conn.execute(d)
+            end
           end
         rescue
           case @sql_type
@@ -105,7 +107,10 @@ module SiSU_DB_drop
             if ans and File.exist?(@db_info.sqlite.db); File.unlink(@db_info.sqlite.db)
             end
           else
-            @conn.do(@drop_table.join(''))
+            @drop_table.each do |d|
+              @conn.execute(d)
+            end
+            #@conn.do(@drop_table.join(''))
           end
         ensure
           #commit
diff --git a/lib/sisu/v1/db_import.rb b/lib/sisu/v1/db_import.rb
index da564639..7268616b 100644
--- a/lib/sisu/v1/db_import.rb
+++ b/lib/sisu/v1/db_import.rb
@@ -138,14 +138,52 @@ module SiSU_DB_import
           puts @conn.driver if defined? @conn.driver
         end
         begin
-          @conn.transaction do |conn|
-            t_d.each do |sql|
-              conn.execute(sql)
+          sql=''
+          if @sql_type=~/sqlite/
+            @conn.transaction do |conn|
+              t_d.each do |sql|
+                conn.execute(sql)
+              end
             end
+            #also 'execute' works for sqlite
+            #@conn.execute("BEGIN")
+            #  t_d.each do |sql|
+            #    @conn.execute(sql)
+            #  end
+            #@conn.execute("COMMIT")
+          else
+            #'do' works for postgresql
+            @conn.do("BEGIN")
+              t_d.each do |sql|
+                @conn.do(sql)
+              end
+            @conn.do("COMMIT")
+          end
+        rescue DBI::DatabaseError => e
+          puts "Error code: #{e.err}"
+          puts "Error message: #{e.errstr}"
+          puts "Error SQLSTATE: #{e.state}"
+          SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error
+          sqlfn="#{@env.path.sql}/#{@md.fnb}.sql"
+          sql=File.new(sqlfn,'w')
+          t_d.each {|i| sql.puts i}
+          p sqlfn
+          if @opt.cmd =~/M/
+            puts sql
+            p @conn.methods.sort
+            puts "#{__FILE__}:#{__LINE__}"
           end
         rescue
           SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error
-          puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/
+          sqlfn="#{@env.path.sql}/#{@md.fnb}.sql"
+          sql=File.new(sqlfn,'w')
+          t_d.each {|i| sql.puts i}
+          p sqlfn
+          if @opt.cmd =~/M/
+            puts sql
+            p @conn.methods.sort
+            puts "#{__FILE__}:#{__LINE__}"
+          end
         ensure
           #@conn.execute("COMMIT")
         end
diff --git a/lib/sisu/v1/db_remove.rb b/lib/sisu/v1/db_remove.rb
index bde5c891..e7ed70a8 100644
--- a/lib/sisu/v1/db_remove.rb
+++ b/lib/sisu/v1/db_remove.rb
@@ -99,7 +99,7 @@ module SiSU_DB_remove
         end
       else
         tell=SiSU_Screen::Ansi.new(@opt.cmd,"no such file in database #{@db.psql.db}::#{@opt.fns}")
-        tell.puts_grey #if @opt.cmd.include? ?v
+        tell.puts_grey unless @opt.cmd =~/q/ #if @opt.cmd.include? ?v
       end
     end
   end
diff --git a/lib/sisu/v1/sysenv.rb b/lib/sisu/v1/sysenv.rb
index 8d4636c1..8b15f957 100644
--- a/lib/sisu/v1/sysenv.rb
+++ b/lib/sisu/v1/sysenv.rb
@@ -1457,6 +1457,11 @@ WOK
         end
         processing
       end
+      def sql
+        pth="#{processing}/sql"
+        mkdir_p(pth) unless FileTest.directory?(pth)
+        pth
+      end
       def composite_file
         pth=path.dal  #"#{processing}/composite"
         mkdir_p(pth) unless FileTest.directory?(pth)
-- 
cgit v1.2.3