Convert Yes/No Boolean field type to Integer in Base tables

English
#1

Split HSQLDB, LO 24.8, Win10Pro
I am trying to optimize my odb by converting all yes/no boolean fields to a simple integer type in populated tables. I have been able to achieve this using a lengthy process using built in LO Base methods. Is there an easier SQL command tool method?

  • Basically, my method has been to copy and paste only the Definitions of table1 to a new table (table2), then change the boolean fields of table2 to varchar type. (edit script file)
  • Append data from table1 into table2. This converts the boolean to varchar (TRUE, FALSE)
  • This is where I would like to use a single SQL command to replace all TRUE values with 1, and all FALSE with “” (null text) or 0 in all fields and records of table2. (more than 20 fields and 5000 records) Can it be done?
  • Instead I have been exporting table2 data to Calc then using its find and replace feature to make the changes.
  • Import Calc sheet (converted table2 data) back into Base by copy and paste method, into an empty, redefined table1, with Integer type fields replacing all Boolean fields. (edit script file)
#2

I would do it this way:

  1. Add a tinyint field for every boolean field, for example “bool1” → “tiny1”
  2. Tools → SQL: UPDATE TABLE "tablename" SET "tiny1" = 1 WHERE "bool1" = TRUE
  3. Tools → SQL: UPDATE TABLE "tablename" SET "tiny1" = 0 WHERE "bool1" = FALSE
  4. Delete the field “bool1”.
  5. Rename the field “tiny1” to “bool1”.

I would never change the field to VARCHAR.

#3

Instead of append, then convert you could use an INSERT INTO-statement for both steps. Something like:

INSERT INTO table2 (SELECT a, b, 
    CASE WHEN  "bool1" THEN 'T' ELSE 'F' END AS Text1,
    d, e FROM table1)

Disadvantage: You have to name your “more than 20 fields”. Therfore I create this kind of statements usually in a text-editor. If I have no list of all fields, it can be easily generated from an export to Calc. (As I have done this mostly with SQLITE, I use Sqlite-studio for changes, which are not supported directly by the database. It creates the necessary SQL …

#4

Thank you Wanderer,
I will try the Sqlite approach, then generate and store the necessary SQL if a simpler method can be produced. I will update here on any success. Otherwise, it sounds like I may be as well off with the status quo method. It is only used occasionally anyway!

  • In LO, is there a way to “search” the entire database for TRUE or FALSE values (like MySQL GUI feature), then conditionally change their values? Or through a Python script or other hsqldb compatible language?
#5

With the help of a little spreadsheet, I generated the following SQL (untested):

CREATE TABLE MONTHS(N VARCHAR(10) NOT NULL, SHORT CHAR(3), ID TINYINT PRIMARY KEY);
Insert into months values('January', 'Jan', 1);
Insert into months values('February', 'Feb',  2);
Insert into months values('March', 'Mar',  3);
Insert into months values('April', 'Apr',  4);
Insert into months values('May', 'May', 5);
Insert into months values('June', 'Jun', 6);
Insert into months values('July', 'Jul', 7);
Insert into months values('August', 'Aug', 8);
Insert into months values('September', 'Sep',  9);
Insert into months values('October', 'Oct',  10);
Insert into months values('November', 'Nov',  11);
Insert into months values('December', 'Dec',  12);
CREATE TABLE SPEC_MONTHS(
    SPECID INTEGER, MID TINYINT,
    PRIMARY KEY (SPECID, MID),
    FOREIGN KEY(SPECID) REFERENCES "species"(ID),
    FOREIGN KEY(MID) REFERENCES MONTHS(ID)
);
insert into spec_months(select id, 1 from "species" where "FloweringJan_" = True);
insert into spec_months(select id, 2 from "species" where "FloweringFeb_" = True);
insert into spec_months(select id, 3 from "species" where "FloweringMar_" = True);
insert into spec_months(select id, 4 from "species" where "FloweringApr_" = True);
insert into spec_months(select id, 5 from "species" where "FloweringMay" = True);
insert into spec_months(select id, 6 from "species" where "FloweringJune" = True);
insert into spec_months(select id, 7 from "species" where "FloweringJuly" = True);
insert into spec_months(select id, 8 from "species" where "FloweringAug_" = True);
insert into spec_months(select id, 9 from "species" where "FloweringSep_" = True);
insert into spec_months(select id, 10 from "species" where "FloweringOct_" = True);
insert into spec_months(select id, 11 from "species" where "FloweringNov_" = True);
insert into spec_months(select id, 12 from "species" where "FloweringDec_" = True);

EDIT: fixed some issues with inconsistent field names.
My “species” table contains a numeric auto-ID column already.

EDIT2: Forgot to upload the helping spreadsheet.
Untitled.ods (18.4 KB)
The following query shows that there are 63 different flowering seasons with no season across year change, such as Nov-Jan.

SELECT DISTINCT 
COALESCE ( "FloweringJan_", FALSE ) AS "JAN", 
COALESCE ( "FloweringFeb_", FALSE ) AS "FEB", 
COALESCE ( "FloweringMar_", FALSE ) AS "MAR", 
COALESCE ( "FloweringApr_", FALSE ) AS "APR", 
COALESCE ( "FloweringMay", FALSE ) AS "MAY", 
COALESCE ( "FloweringJune", FALSE ) AS "JUN", 
COALESCE ( "FloweringJuly", FALSE ) AS "JUL", 
COALESCE ( "FloweringAug_", FALSE ) AS "AUG", 
COALESCE ( "FloweringSep_", FALSE ) AS "SEP", 
COALESCE ( "FloweringOct_", FALSE ) AS "OCT", 
COALESCE ( "FloweringNov_", FALSE ) AS "NOV", 
COALESCE ( "FloweringDec_", FALSE ) AS "DEC" 
FROM "species" 
WHERE "FloweringJan_" = TRUE 
OR "FloweringFeb_" = TRUE 
OR "FloweringMar_" = TRUE 
OR "FloweringApr_" = TRUE 
OR "FloweringMay" = TRUE 
OR "FloweringJune" = TRUE 
OR "FloweringJuly" = TRUE 
OR "FloweringAug_" = TRUE 
OR "FloweringSep_" = TRUE 
OR "FloweringOct_" = TRUE 
OR "FloweringNov_" = TRUE 
OR "FloweringDec_" = TRUE

therefore we may also get a table of flowering seasons like this:

SELECT "Min"||'-'||"Max"AS "Flowering Season", "SPECID" FROM(SELECT DISTINCT  "SPECID", MIN( "MID" )AS"Min", MAX( "MID" ) AS"Max" FROM "SPEC_MONTHS" GROUP BY "SPECID")

The following shrinks the data file down to 30MB:

alter table "species" drop column "FloweringJan_"
alter table "species" drop column "FloweringFeb_"
alter table "species" drop column "FloweringMar_"
alter table "species" drop column "FloweringApr_"
alter table "species" drop column "FloweringMay"
alter table "species" drop column "FloweringJune"
alter table "species" drop column "FloweringJuly"
alter table "species" drop column "FloweringAug_"
alter table "species" drop column "FloweringSep_"
alter table "species" drop column "FloweringOct_"
alter table "species" drop column "FloweringNov_"
alter table "species" drop column "FloweringDec_"
CHECKPOINT DEFRAG
#6

In order to save the more than 60 steps required in your method and keep the conversion process relatively short and simple, converting to varchar first, and then using Calc find and replace to integers is by far the better option.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.