<div dir="ltr">Thanks Russell,<div><br></div><div>Yes, I created a domain ..</div><div><div><br></div><div> CREATE DOMAIN BOOL</div><div> AS Smallint</div><div> DEFAULT 0</div><div> NOT NULL</div><div> ;</div></div>
<div><br></div><div>And while it is possible to place an index on it, it's my understanding that Firebird will not perform well when placing an index on this type of field where there is limited ranges (e.g. M/F; 1/0; Y/N).</div>
<div><br></div><div>It was my understanding that Firebird may not even create this type of index. Perhaps I am wrong??</div><div><br></div><div class="gmail_extra"><div>Steve Peacocke<br>Mobile: +64 220 612-611<div><div><a href="http://nz.linkedin.com/pub/steve-peacocke/1/a06/489" target="_blank">Linkedin Professional Profile</a></div>
</div></div>
<br><br><div class="gmail_quote">On Sun, Mar 30, 2014 at 7:31 PM, russell <span dir="ltr"><<a href="mailto:russell@belding.co.nz" target="_blank">russell@belding.co.nz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-NZ" link="blue" vlink="purple"><div><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Hi Steve<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Firebird Fb does not have a native boolean type. <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I use a smallint for my Boolean types. 1=True, 0=F. <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">There are many ways to define and use Booleans in Fb.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Eg. CREATE TABLE BCD_CONNECTIONS (<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> CONNECTION_ID ID /*Integer NOT NULL*/ NOT NULL,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> FIRST_TS TIMESTAMP NOT NULL,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> CURRENT_TS TIMESTAMP NOT NULL,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> MACHINE TEXT24 /*Varchar(24) */ DEFAULT 'Unknown' NOT NULL,<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> SECURITY_CODE TEXT48 /*Varchar(48) */ DEFAULT 'illegal' NOT NULL,<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> MACHINE_ID Integer,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> USER_ID Integer,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> USER_NAME TEXT64 /*Varchar(64) */,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> B_IN_WINSLEEP Smallint DEFAULT 0,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> CONSTRAINT BCD_CONNECTIONS_PK PRIMARY KEY (CONNECTION_ID)<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">);<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">So these Booleans can be indexed. Tables can have many Booleans.<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">ID, TEXTNN are my defined Domains in Fb.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Russell<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> <a href="mailto:delphi-bounces@listserver.123.net.nz" target="_blank">delphi-bounces@listserver.123.net.nz</a> [mailto:<a href="mailto:delphi-bounces@listserver.123.net.nz" target="_blank">delphi-bounces@listserver.123.net.nz</a>] <b>On Behalf Of </b>Steve Peacocke<br>
<b>Sent:</b> Sunday, 30 March 2014 3:19 p.m.<br><b>To:</b> NZ Borland Developers Group - Delphi List<br><b>Subject:</b> [DUG] Using Boolean (Char(1)) in Firebird<u></u><u></u></span></p><div><div class="h5"><p class="MsoNormal">
<u></u> <u></u></p><div><p class="MsoNormal">Hi all,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I'm playing around with a Firebird database and wanted to know from you DB experts out there how you handle booleans in a table.<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">These could be as simple as<u></u><u></u></p></div><div><p class="MsoNormal"> ActiveRecord (Y/N)<u></u><u></u></p></div><div><p class="MsoNormal">
AccountTransactionType (I/E) - (Income or Expense)<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">That last I would normally think would be "Income (Y/N)" so that would be a boolean too.<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">My understanding is that this will never be indexed, even if you specifically add an index to it. So how do you handle it. There may be several boolean fields in a table definition.<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">As these tables c an contain several hundred thousand records, this could potentially slow down any query to say total all records last 3 years where Active and Income - as the only index would then be on the date field, there is a possibility that this could potentially be a very slow query.<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I've heard of others creating another table to create, say, non-Avtive record ID's, but this one table could have several booleans, therefore creating several new tables (combining then into a single table with the field name would cause the same problem).<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Any thoughts?<br clear="all"><u></u><u></u></p><div><p class="MsoNormal"><br>Steve Peacocke<br>Mobile: <a href="tel:%2B64%20220%20612-611" value="+64220612611" target="_blank">+64 220 612-611</a><u></u><u></u></p>
<div><div><p class="MsoNormal"><a href="http://nz.linkedin.com/pub/steve-peacocke/1/a06/489" target="_blank">Linkedin Professional Profile</a><u></u><u></u></p></div></div></div></div></div></div></div></div></div><br>_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe<br></blockquote></div><br></div></div>