v3: db, review, directory structure changed, database prefix renamed

* language_document_char field made larger [consider renaming]
* title, src_filename and language_document_char fields mandatory
  (title, title_main, src_filename, language_document_char)
* review of unique key setting, now based on combination of filename
  and language (filename no longer sufficient with intro of language
  subdirectories)
* [retest sqlite3]

5 files changed, 53 insertions, 32 deletions

diff --git a/data/doc/sisu/CHANGELOG_v3 b/data/doc/sisu/CHANGELOG_v3
index dea1dd0b..ba783a33 100644
--- a/data/doc/sisu/CHANGELOG_v3
+++ b/data/doc/sisu/CHANGELOG_v3
@@ -32,6 +32,12 @@ http://www.jus.uio.no/sisu/pkg/src/sisu_3.0.8.orig.tar.gz
 
   * prog_text_translation, renamed from what was i18n, i18n name reused
 
+  * db, directory structure changed, database prefix renamed
+    * language_document_char field made larger
+      [consider renaming language_document_code or ..._id]
+    * title, src_filename and language_document_char fields mandatory
+      (title, title_main, src_filename, language_document_char)
+
   * NOTE: cgi sample search form caught by Ruby/DBI libdbi-ruby1.9.1 reported
     reported as libdbd-pg-ruby1.9.1 bug #596103, where "select_all fails". The
     cgi sample search scripts for postgresql and sqlite3 are almost identical,
diff --git a/lib/sisu/v3/constants.rb b/lib/sisu/v3/constants.rb
index d94da654..57491a7c 100644
--- a/lib/sisu/v3/constants.rb
+++ b/lib/sisu/v3/constants.rb
@@ -147,20 +147,18 @@ Px[:lng_lst]=%w[am bg bn br ca cs cy da de el en eo es et eu fi fr ga gl he hi h
 Px[:lv1]=     '*'
 Px[:lv2]=     '='
 Px[:lv3]=     '='
-#Px[:lv2_3]=   '='
 Px[:lv4]=     '-'
 Px[:lv5]=     '.'
 Px[:lv6]=     '.'
-#Px[:lv5_6]=   '.'
-Db[:name_prefix]="SiSU#{SiSU_version_dir}c_"
-Db[:name_prefix_db]="sisu_#{SiSU_version_dir}c_"
+Db[:name_prefix]="SiSU#{SiSU_version_dir}d_"
+Db[:name_prefix_db]="sisu_#{SiSU_version_dir}d_"
 Db[:col_title]=800
 Db[:col_title_part]=400
 Db[:col_title_edition]=10
 Db[:col_name]=600
 Db[:col_creator_misc_short]=100
 Db[:col_language]=100
-Db[:col_language_char]=3
+Db[:col_language_char]=6
 Db[:col_date_text]=10
 Db[:col_classify_txt_long]=600
 Db[:col_classify_txt_short]=600
@@ -312,7 +310,7 @@ see polyglossia for subset
 * es_GA (Spanish, as spoken in Gabon)
 * es_GT (Spanish, as spoken in Guatemala)
 * es_HN (Spanish, as spoken in Honduras)
-* es_LA (Spanish, as spoken in Lao People's Democratic Republic)
+* es_LA (Spanish, as spoken in Lao People''s Democratic Republic)
 * es_MX (Spanish, as spoken in Mexico)
 * es_NI (Spanish, as spoken in Nicaragua)
 * es_PA (Spanish, as spoken in Panama)
diff --git a/lib/sisu/v3/db_columns.rb b/lib/sisu/v3/db_columns.rb
index 8b8a0b3c..d8a6f3cf 100644
--- a/lib/sisu/v3/db_columns.rb
+++ b/lib/sisu/v3/db_columns.rb
@@ -64,6 +64,7 @@ module SiSU_DB_columns
     def initialize(md=nil)
       @md=md
       @db=SiSU_Env::Info_db.new #watch
+      @lang ||=SiSU_i18n::Languages.new
       if defined? md.opt.mod \
       and md.opt.mod.inspect=~/import|update/ \
       and FileTest.exist?(md.fns)
@@ -98,7 +99,7 @@ module SiSU_DB_columns
           'title'
         end
         def create_column
-          "#{name}                VARCHAR(#{Db[:col_title]}) NULL,"
+          "#{name}                VARCHAR(#{Db[:col_title]}) NOT NULL,"
         end
         def column_comment
           %{COMMENT ON COLUMN metadata_and_text.#{name}
@@ -120,7 +121,7 @@ module SiSU_DB_columns
           'title_main'
         end
         def create_column
-          "#{name}                VARCHAR(#{Db[:col_title_part]}) NULL,"
+          "#{name}                VARCHAR(#{Db[:col_title_part]}) NOT NULL,"
         end
         def column_comment
           %{COMMENT ON COLUMN metadata_and_text.#{name}
@@ -237,9 +238,8 @@ module SiSU_DB_columns
             IS 'metadata document language [DC12]';}
         end
         def tuple
-          t=if defined? @md.title.language \
-          and @md.title.language=~/\S+/
-            txt=@md.title.language
+          t=if @lang.list[@md.opt.lng][:n]
+            txt=@lang.list[@md.opt.lng][:n]
             special_character_escape(txt)
             ["#{name}, ","'#{txt}', "]
           else ['','']
@@ -259,9 +259,9 @@ module SiSU_DB_columns
             IS 'metadata document language iso code';}
         end
         def tuple
-          t=if defined? @md.title.language_char \
-          and @md.title.language_char=~/\S+/
-            txt=@md.title.language_char
+          t=if defined? @md.opt.lng \
+          and @md.opt.lng=~/\S+/
+            txt=@md.opt.lng
             special_character_escape(txt)
             ["#{name}, ","'#{txt}', "]
           else ['','']
@@ -599,9 +599,8 @@ module SiSU_DB_columns
            IS 'metadata document language';}
         end
         def tuple
-          t=if defined? @md.language.document \
-          and @md.language.document=~/\S+/
-            txt=@md.language.document
+          t=if @lang.list[@md.opt.lng][:n]
+            txt=@lang.list[@md.opt.lng][:n]
             special_character_escape(txt)
             ["#{name}, ","'#{txt}', "]
           else ['','']
@@ -614,7 +613,7 @@ module SiSU_DB_columns
           'language_document_char'
         end
         def create_column
-          "#{name}                VARCHAR(#{Db[:col_language_char]}) NULL,"
+          "#{name}                VARCHAR(#{Db[:col_language_char]}) NOT NULL,"
         end
         def column_comment
           %{COMMENT ON COLUMN metadata_and_text.#{name}
@@ -1833,7 +1832,7 @@ module SiSU_DB_columns
           'src_filename'
         end
         def create_column
-          "#{name}                VARCHAR(#{Db[:col_filename]}) NULL,"
+          "#{name}                VARCHAR(#{Db[:col_filename]}) NOT NULL,"
         end
         def column_comment
           %{COMMENT ON COLUMN metadata_and_text.#{name}
diff --git a/lib/sisu/v3/db_import.rb b/lib/sisu/v3/db_import.rb
index 45bb37f3..541399a9 100644
--- a/lib/sisu/v3/db_import.rb
+++ b/lib/sisu/v3/db_import.rb
@@ -96,17 +96,17 @@ module SiSU_DB_import
         @col[:lid] ||=0
         @col[:lid]=@driver_sqlite3 \
         ? @conn.execute( sql ).join.to_i \
-        : @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+        : @conn.execute( sql ) { |x| x.fetch_all.flatten[0] }
       rescue
         puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/
       end
       @col[:lid]=0 if @col[:lid].nil? or @col[:lid].to_s.empty?
       sql='SELECT MAX(nid) FROM endnotes'
       begin
-        @id_n ||=0
         @id_n=@driver_sqlite3 \
         ? @conn.execute( sql ).join.to_i \
-        : @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+        : @id_n=@conn.execute( sql ) { |x| x.fetch_all.flatten[0] }
+        @id_n ||=0
       rescue
         puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/
       end
@@ -121,7 +121,12 @@ module SiSU_DB_import
       SiSU_Screen::Ansi.new(@opt.cmd,"#{@db.psql.db}::#{@opt.fns}").puts_blue if @opt.cmd =~/vVM/
       SiSU_Screen::Ansi.new(@opt.cmd,'Marshal Load',@fnc).puts_grey if @opt.cmd =~/v/
 #%
-      select_first_match=%{ SELECT metadata_and_text.tid FROM metadata_and_text WHERE metadata_and_text.src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; }
+      select_first_match=%{
+        SELECT metadata_and_text.tid
+        FROM metadata_and_text
+        WHERE metadata_and_text.src_filename = '#{@opt.fns}'
+        AND metadata_and_text.language_document_char = '#{@opt.lng}'
+      ;}
       file_exist=@sql_type=~/sqlite/ \
       ? @conn.get_first_value(select_first_match) \
       : @conn.select_one(select_first_match)
@@ -188,7 +193,7 @@ module SiSU_DB_import
       else
         if file_exist
           @db=SiSU_Env::Info_db.new
-          puts "\n#{@cX.grey}file #{@cX.off} #{@cX.blue}#{@opt.fns}#{@cX.off} #{@cX.grey}already exists in database#{@cX.off} #{@cX.blue}#{@db.psql.db}#{@cX.off} #{@cX.brown}update instead?#{@cX.off}"
+          puts "\n#{@cX.grey}file #{@cX.off} #{@cX.blue}#{@opt.fns}#{@cX.off} in language code #{cX.blue}#{@opt.lng}#{cX.off} #{@cX.grey}already exists in database#{@cX.off} #{@cX.blue}#{@db.psql.db}#{@cX.off} #{@cX.brown}update instead?#{@cX.off}"
         end
       end
     end
@@ -216,18 +221,18 @@ module SiSU_DB_import
         #@tp[:title]=@md.title.full
         #special_character_escape(@tp[:title])
         #@tp[:title_f],@tp[:title_i]='title, ',"'#{@tp[:title]}', "
-        sql='SELECT MAX(tid) FROM metadata_and_text'
+        sql='SELECT MAX(tid) FROM metadata_and_text;'
         begin
           @@id_t ||=0
           id_t=if @driver_sqlite3
             @conn.execute( sql ).join.to_i # { |x| id_t=x.join.to_i }
-          else @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+          else
+            @conn.execute( sql ) { |x| x.fetch_all.flatten[0] }
           end
           @@id_t=id_t if id_t
         rescue
           puts "#{__FILE__} #{__LINE__}" if @opt.cmd =~/M/
         end
-        @@id_t =0 if @col[:lid].nil? or @col[:lid].to_s.empty?
         @@id_t+=1 #bug related, needs to be performed once at start of file, but consider moving, as, placed here it means program will fail if document header lacks @title:
         puts %{\n#{@cX.grey}Processing file number#{@cX.off}: #{@cX.green}#{@@id_t}#{@@cX.off}} if @opt.cmd =~/vVM/
       end
@@ -422,7 +427,8 @@ module SiSU_DB_import
                     txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                   end
                   if txt
-                    en={ type: 'endnotes',
+                    en={
+                      type: 'endnotes',
                       id:      @id_n,
                       lid:     @col[:lid],
                       nr:      nr,
@@ -460,7 +466,8 @@ module SiSU_DB_import
                     txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                   end
                   if txt
-                    en={ type: 'endnotes_asterisk',
+                    en={
+                      type: 'endnotes_asterisk',
                       id:      @id_n,
                       lid:     @col[:lid],
                       nr:      nr,
@@ -498,7 +505,8 @@ module SiSU_DB_import
                     txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                   end
                   if txt
-                    en={ type: 'endnotes_plus',
+                    en={
+                      type: 'endnotes_plus',
                       id:      @id_n,
                       lid:     @col[:lid],
                       nr:      nr,
diff --git a/lib/sisu/v3/db_remove.rb b/lib/sisu/v3/db_remove.rb
index 7644ad52..cfa19c9f 100644
--- a/lib/sisu/v3/db_remove.rb
+++ b/lib/sisu/v3/db_remove.rb
@@ -72,9 +72,19 @@ module SiSU_DB_remove
         : false
       end
       del_id=if driver_sqlite3
-        @conn.get_first_value(%{ SELECT tid FROM metadata_and_text WHERE src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; }).to_i
+        @conn.get_first_value(%{
+          SELECT tid
+          FROM metadata_and_text
+          WHERE src_filename = '#{@opt.fns}'
+          AND metadata_and_text.language_document_char = '#{@opt.lng}'
+        ;}).to_i
       else
-        x=@conn.select_one(%{ SELECT metadata_and_text.tid FROM metadata_and_text WHERE metadata_and_text.src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; })
+        x=@conn.select_one(%{
+          SELECT metadata_and_text.tid
+          FROM metadata_and_text
+          WHERE metadata_and_text.src_filename = '#{@opt.fns}'
+          AND metadata_and_text.language_document_char = '#{@opt.lng}'
+        ;})
         x ? (x.join.to_i) : nil
       end
       if del_id