• Welcome, Guest. Please login.
 
May 11, 2021, 07:41:47 PM

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - David Warner

1

Hi Fred

QuoteI get the same speed with Count() and Count(*)

Count(*) is valid SQL and I'm not sure that Count() should be working at all.

James

Does your table have a primary key constraint or a unique index? If not you could give that a try.

David


2
Hi James,

definitely try putting the asterisk in as an argument to the count function.

From the SQLite help page http://www.sqlite.org/lang_aggfunc.html

QuoteThe first form returns a count of the number of times that X is not NULL in a group.

count(X)

The second form (with no argument) returns the total number of rows in the group.

count(*)


I think the 'with no argument' bit is misleading us here and that an asterisk argument is required in the second form.

Best Regards,

David
3
QuoteCan this be adapted to count the number of records in a table matching a certain condition?

You can limit the rows being counted by adding a WHERE clause to the SQL...

SQL$ = "SELECT COUNT(*) As RowCount FROM MyTable WHERE SalesCode = 99;"
slSEL(SQL$,1)
slGetRow(1)
NumberOfRows& = VAL(slFN("RowCount",1))
slCloseSet(1)
4
QuoteYou currently can't do a select with a where clause on encrypted and/or
compressed data.  You uncovered a deficiency in SQLitening --- good catch!

I've always been an awkward so and so.  ;)
 
QuoteYour solution works but requires the use of Base64.  I will work on a
solution that will be built into SQLitening and be available in next
release.  I will keep you posted.

That is brilliant thanks. I only used Base64 on the basis that it seemed well recommended on the PB forum (and it seemed to work).
I'll look forward to the new version.

Regards,

David
5
Hi Folks,

I have been tinkering around with SQLitening, what a great project. Well done to Fred and Paul!

My aim was to set up a database containing encrypted compressed data, which I could search easily without having to de-encrypt the underlying tables. Using something like the following SQL statement for example...

SELECT Encrypted_Col
FROM   My_Table
WHERE  Encrypted_Col = 'Encrypted_Value';

I couldn't see a way to do this with the SQLitening functions as they stand (maybe I missed something). I found that by exposing one of the underlying auxiliary functions and by using the Base64 encoding routines written by Semen Matusovski, I could achieve what I wanted. I think it would be great to have this facility available by default, what do you reckon?

Well, here is the source code for discussion/comments.

Cheers,

David

'-------------------------------------------------------------------------------
'- Program Name : SL_Encrypt.bas                                               -
'- Author       : David Warner                                                 -
'- Date         : 24/10/2008                                                   -
'- Compiler     : PB/CC 5.0                                                    -
'- Purpose      : To test the insertion, querying and retrieval of compressed  -
'-                and encrypted data values from a SQLite 3 database using     -
'-                the SQLitening PowerBasic functions.                         -
'-------------------------------------------------------------------------------
'-                                                                             -
'- Changes required to expose the encryption/compression functionality...      -
'-                                                                             -
'- 1) Edit SQLitening.bas                                                      -
'-                                                                             -
'-   Replace...                                                                -
'-                                                                             -
'-       Function irCallAuxRutsA (byval rlAction as Long, _                    -
'-                                rsText as String, _                          -
'-                                wsText as String) as Long                    -
'-                                                                             -
'-   with...                                                                   -
'-                                                                             -
'-       FUNCTION irCallAuxRutsA ALIAS "irCallAuxRutsA" _                      -
'-                                     (BYVAL rlAction AS LONG, _              -
'-                                      rsText AS STRING, _                    -
'-                                      wsText AS STRING) EXPORT AS LONG       -
'-                                                                             -
'- 2) Edit SQLitening.inc                                                      -
'-                                                                             -
'-   Add the following line...                                                 -
'-                                                                             -
'-       DECLARE FUNCTION irCallAuxRutsA _                                     -
'-                        LIB "SQLitening.Dll" ALIAS "irCallAuxRutsA" _        -
'-                        (BYVAL rlAction AS LONG, _                           -
'-                         rsText AS STRING, _                                 -
'-                         wsText AS STRING) AS LONG                           -
'-                                                                             -
'- 3) Compile SQLitening.bas to re-create SQLitening.dll                       -
'-                                                                             -
'-------------------------------------------------------------------------------

#COMPILE EXE
#DIM ALL

#INCLUDE "..\SQLitening_106\Inc\SQLitening.Inc"
#INCLUDE "base64.inc"

MACRO CONST = MACRO

' Set up 'valid action' control constants
' for irCallAuxRutsA encryption and
' compression function...
CONST lSetCryptKey = 1&            ' 1 = Set crypt key (Empty Text1 will
                                   '     remove key, use for security reasons).
CONST lEncrypt = 2&                ' 2 = Encrypt
CONST lDecrypt = 3&                ' 3 = Decrypt
CONST lCompress = 4&               ' 4 = Compress
CONST lUncompress = 5&             ' 5 = Uncompress
CONST lCompress_and_Encrypt = 6&   ' 6 = Compress and Encrypt
CONST lDecrypt_and_Uncompress = 7& ' 7 = Decrypt and Uncompress

FUNCTION PBMAIN () AS LONG

    ' String to hold SQL commands to be passed to SQLitening
    DIM sSQL AS STRING

    ' Encryption and compression function
    ' return status and data variables
    DIM lCompEncrypStatus AS LONG            ' Compress & encrypt func retval
    DIM sCompressedEncryptedData AS STRING   ' Compressed and encrypted data

    DIM lDecrypUncompStatus AS LONG          ' Decrypt & uncompress func retval
    DIM sDecryptedUncompressedData AS STRING ' Decrypted and uncompressed data

    ' Base64 encoding and decoding functions
    ' return status and data variables
    DIM lEncodeStatus AS LONG                ' Encoding function return value
    DIM sEncodedData AS STRING               ' Base64 encoded binary data

    DIM lDecodeStatus AS LONG                ' Decoding function return value
    DIM sDecodedData AS STRING               ' Decoded binary data

    ' 'Record Set' control variable to be passed
    ' to SQLitening functions
    DIM lRecSet1 AS LONG
    lRecSet1 = 1

    ' open existing or create new SQLite3 database
    slopen "SL_Encrypt.db3", "C"

    ' remove previously used table if necessary
    slexe "DROP TABLE IF EXISTS [Encrypted_Table];"

    ' create a new table with a single data column
    slexe "CREATE TABLE [Encrypted_Table] " _
                     & "([Encrypted_Column] NVARCHAR2(50));"

    ' Put an index on the data column
    slexe "CREATE INDEX [Encrypted_Column_IDX] " _
                      & "ON [Encrypted_Table] ([Encrypted_Column]);"

    ' Insert a row into our newly created table containing
    ' a Base64 encoded, compressed and encrypted value

    ' Set crypt key for subsequent encryption/decryption
    slSetProcessMods "KJustForTesting12"

    ' Compress and encrypt a string literal value
    lCompEncrypStatus = irCallAuxRutsA(lCompress_and_Encrypt, _
                                       "Row 1 Data", _
                                       sCompressedEncryptedData)

    ' Base 64 encode the compressed, encrypted string
    lEncodeStatus = encode_base64(sCompressedEncryptedData, _
                                  19, _
                                  sEncodedData)

    ' Insert the encoded, compressed and encrypted value
    sSQL = "INSERT INTO [Encrypted_Table] VALUES('" & sEncodedData & "');"
    slexe sSQL

    ' Select the previously inserted encoded value from the the database
    sSQL = "SELECT rowid, " _
         & "[Encrypted_Column] " _
         & "from [Encrypted_Table] " _
         & "WHERE [Encrypted_Column] = '" & sEncodedData & "';"

    slsel (sSQL, lRecSet1)

    ' Process the result set one row at a time (only one row expected)
    DO WHILE slGetRow (lRecSet1)

        ' Retrieve the encoded string from the recordset
        sEncodedData = slFN("Encrypted_Column", lRecSet1)

        ' Display the coded, encrypted and compressed data
        ? "Encoded data retreived from database... "
        ?
        ? "     '" & sEncodedData & "'"
        ?

        ' Base64 decode the compressed, encrypted string
        lDecodeStatus = decode_base64(STRPTR(sEncodedData), sDecodedData)

        ' Decrypt and uncompress the string
        lDecrypUncompStatus = irCallAuxRutsA(lDecrypt_and_Uncompress, _
                                             sDecodedData, _
                                             sDecryptedUncompressedData)

        ' Display the decoded, decrypted and uncompressed data
        ? "After decoding, decryption and uncompression becomes..."
        ?
        ? "     '" & sDecryptedUncompressedData & "'"

   LOOP

   slCloseSet (lRecSet1)

   WAITKEY$

END FUNCTION


and the required include file...

'-------------------------------------------------------------------------------
'- Program Name : Base64.inc                                                   -
'- Author       : Semen Matusovski                                             -
'- Date         : 07/07/2001 01:15 am                                          -
'- Purpose      : To Base64 encode/decode binary data                          -
'- URL          : http://www.powerbasic.com/support/pb...ad.php?t=23062        -
'-                                                                             -
'- referenced by...                                                            -
'-                                                                             -
'- URL     : http://www.powerbasic.com/support/pbforums/showthread.php?t=24839 -
'- topic   : A Program for Posting and Reading Posted Binary Files             -
'- name    : Bob Scott, member                                                 -
'- date    : Feb 20th, 2006, 02:14 PM                                          -
'-                                                                             -
'- example calls :                                                             -
'-                                                                             -
'- encoding...                                                                 -
'-                                                                             -
'-      local stext as string, outbuf as string                                -
'-      encode_base64(stext, 19, outbuf)                                       -
'-                                                                             -
'- decoding...                                                                 -
'-                                                                             -
'-      local stext as string, outbuf as string,                               -
'-      decode_base64(strptr(stext), outbuf)                                   -
'-                                                                             -
'-------------------------------------------------------------------------------

   ' outbuf is not necessary to allocate
   ' $crlf after each ngroups per 3 input bytes

   FUNCTION encode_base64(inbuf AS STRING, BYVAL ngroups AS LONG, _
                                         outbuf AS STRING) AS LONG
      #REGISTER NONE

      outbuf = SPACE$(4 * FIX((LEN(inbuf) + 2) / 3) + _
                   2 * FIX(LEN(inbuf) / (3 * ngroups)))

      ! push ebx

      ! lea ebx, encode_base64_trt

      ! mov edi, outbuf
      ! mov edi, [edi]                    ' strptr(outbuf)

      ! mov esi, inbuf
      ! mov esi, [esi]                    ' strptr(inbuf)

      ! mov edx, esi
      ! sub edx, 4
      ! mov edx, [edx]                    ' len(inbuf)

      ! mov ecx, ngroups

encode_base64_lb1:
      ! cmp edx, 0
      ! jle encode_base64_lb4

      ! mov al, [esi]
      ! shr al, 2
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! mov al, [esi + 1]
      ! mov ah, [esi]
      ! shr ax, 4
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! cmp edx, 1
      ! jne encode_base64_lb2

      ! mov al, 61                        ' add ==
      ! mov [edi], al
      ! inc edi
      ! mov [edi], al
      ! inc edi
      ! jmp encode_base64_lb4

encode_base64_lb2:
      ! mov al, [esi + 2]
      ! mov ah, [esi + 1]
      ! shr ax, 6
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! cmp edx, 2
      ! jne encode_base64_lb3

      ! mov al, 61                        ' add =
      ! mov [edi], al
      ! inc edi
      ! jmp encode_base64_lb4

encode_base64_lb3:
      ! mov al, [esi + 2]
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! add esi, 3
      ! sub edx, 3

      ! dec ecx
      ! cmp ecx, 0
      ! jne encode_base64_lb1

      ! mov al, 13                         ' add $crlf
      ! mov [edi], al
      ! mov al, 10
      ! mov [edi + 1], al
      ! add edi, 2
      ! mov ecx, ngroups
      ! jmp encode_base64_lb1

encode_base64_lb4:
      ! pop ebx

      EXIT FUNCTION

encode_base64_trt:
      ! db  65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77
      ! db  78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90
      ! db  97, 98, 99,100,101,102,103,104,105,106,107,108,109
      ! db 110,111,112,113,114,115,116,117,118,119,120,121,122
      ! db  48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 43, 47
   END FUNCTION

'-------------------------------------------------------------------------------

   ' outbuf is not necessary to allocate
   ' function = offset in inbuf for last not recognized character
   FUNCTION decode_base64(BYVAL inbuf AS DWORD, _
                     outbuf AS STRING) AS LONG
      #REGISTER NONE
      DIM i AS LONG

      ! mov edi, 0
      ! mov ah, 0

decode_base64_lb1:
      ! push ebx
      ! lea ebx, i
      ! mov esi, inbuf
      ! push esi
      ! mov cl, 0

decode_base64_lb2:
      ! mov al, [esi]

      ! cmp al, 13                         ' pass $crlf
      ! je decode_base64_lb15
      ! cmp al, 10
      ! je decode_base64_lb15

      ! cmp al, 65                         ' a .. z
      ! jl decode_base64_lb3
      ! cmp al, 90
      ! jg decode_base64_lb3
      ! sub al, 65
      ! jmp decode_base64_lb8

decode_base64_lb3:
      ! cmp al, 97                         ' a .. z
      ! jl decode_base64_lb4
      ! cmp al, 122
      ! jg decode_base64_lb4
      ! sub al, 71
      ! jmp decode_base64_lb8

decode_base64_lb4:
      ! cmp al, 48                         ' 0 .. 9
      ! jl decode_base64_lb5
      ! cmp al, 57
      ! jg decode_base64_lb5
      ! add al, 4
      ! jmp decode_base64_lb8

decode_base64_lb5:
      ! cmp al, 43                         ' +
      ! jne decode_base64_lb6
      ! mov al, 62
      ! jmp decode_base64_lb8

decode_base64_lb6:
      ! cmp al, 47                         ' /
      ! jne decode_base64_lb7
      ! mov al, 63
      ! jmp decode_base64_lb8

decode_base64_lb7:
      ! cmp al, 61                         ' =
      ! jne decode_base64_lb18
      ! mov al, 255

decode_base64_lb8:
      ! inc cl
      ! cmp cl, 1
      ! jne decode_base64_lb9
      ! mov [ebx], al
      ! jmp decode_base64_lb15

decode_base64_lb9:
      ! cmp cl, 2
      ! jne decode_base64_lb10
      ! mov [ebx + 1], al
      ! jmp decode_base64_lb15

decode_base64_lb10:
      ! cmp cl, 3
      ! jne decode_base64_lb11
      ! mov [ebx + 2], al
      ! jmp decode_base64_lb15

decode_base64_lb11:
      ! mov [ebx + 3], al

      ! mov cl, [ebx]
      ! shl cl, 2
      ! mov ch, [ebx + 1]
      ! shr ch, 4
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb12
      ! mov [edi], cl

decode_base64_lb12:
      ! mov cl, [ebx + 2]
      ! cmp cl, 255
      ! je  decode_base64_lb17

      ! mov cl, [ebx + 1]
      ! shl cl, 4
      ! mov ch, [ebx + 2]
      ! shr ch, 2
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb13
      ! mov [edi + 1], cl

decode_base64_lb13:
      ! mov cl, [ebx + 3]
      ! cmp cl, 255
      ! je  decode_base64_lb16

      ! mov cl, [ebx + 2]
      ! shl cl, 6
      ! mov ch, [ebx + 3]
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb14
      ! mov [edi + 2], cl

decode_base64_lb14:
      ! add edi, 3
      ! mov cl, 0

decode_base64_lb15:
      ! inc esi
      ! jmp decode_base64_lb2


decode_base64_lb16:
      ! inc edi

decode_base64_lb17:
      ! inc edi

decode_base64_lb18:
      ! mov ecx, esi
      ! pop esi
      ! sub ecx, esi
      ! mov function, ecx
      ! pop ebx

      ! cmp ah, 0
      ! jne decode_base64_lb19
      ! mov ah, 1

      ! mov i, edi

      outbuf = SPACE$(i)
      ! mov edi, outbuf
      ! mov edi, [edi]
      ! jmp decode_base64_lb1

decode_base64_lb19:
      EXIT FUNCTION
   END FUNCTION
'-------------------------------------------------------------------------------


7
Hi Mike,

QuoteIf a relational key links multiple tables is WHERE the best method to use?
Should/when should a JOIN be used?

Joining tables can be seen as a bit of a box of frogs (but it isn't all that bad really). A 'join' is simply a way of defining the links between tables using related data columns. Although cross/cartesian joins don't define linking columns and subsequently join all rows in all referenced tables to each other, making a huge product/result set.

There are a number of ANSII SQL standards SQL89, SQL92 etc which define various join methods using 'FROM', 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN' etc. Take a look at http://en.wikipedia.org/wiki/Join_(SQL) for a bit more info.

There are the more recent join styles which use 'INNER JOIN', 'OUTER JOIN', 'LEFT JOIN', 'RIGHT JOIN' etc
e.g.

SELECT Table1.id,
       Table1.data,
       Table2.data
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id;


and more traditional ones which use WHERE to construct joins between tables.  For example, this is functionally identical to the previous query but I personally find it easier to read and understand...

SELECT Table1.id,
       Table1.data,
       Table2.data
FROM Table1, Table2
WHERE Table1.id = Table2.id;


SQL databases usually offer the ability to construct 'traditional style' outer joins by using a non ANSII standard SQL syntax. For example Oracle allows the placing of (+) next to table names to define outer joins. I prefer to use this if it is available.


QuoteDoes using a data type for each column optimize?  INTEGER?

I don't know much about the SQLite optimiser to be honest.  I do know that SQLite is fairly loosely typed though and to quote the docs ... http://www.sqlite.org/faq.html#q3

'SQLite does use the declared type of a column as a hint that you prefer values in that format.'

It would seem sensible to explicitly impose data types wherever possible. Even if the optimiser doesn't use them now, it might well in the future.


QuoteCan indexes be concatenated?   BALANCE + LASTNAME + FIRSTNAME?

Most definitely, check out 'compound indexes' on Google and the create index syntax for multi column indexes on the SQLite site. http://www.sqlite.org/lang_createindex.html


QuoteTABLEA    ID=123
TABLEB    ID=123
TABLE3    ID=123

MyID$ = "123"
SELECT FIRSTNAME FROM TABLEA WHERE ID=MyID$
SELECT CHILDNAME FROM TABLEB WHERE ID=MyID$
SELECT MEMOS FROM TABLEC WHERE ID=MYID$

What would be the best way to display these 3 fields from 3 tables?

Here are two ways of doing the join...

1) Newer ANSII style...

SELECT TableA.id,
       TableA.firstname,
       TableB.childname,
       TableC.memos
FROM (TableA INNER JOIN TableB ON TableA.id = TableB.id) INNER JOIN TableC ON TableA.id = TableC.id
WHERE TableA.id=123



2) Traditional WHERE style...

SELECT TableA.id,
       TableA.firstname,
       TableB.childname,
       TableC.memos
FROM   TableA,
       TableB,
       TableC
WHERE  TableA.id=123
AND    TableA.id = TableB.id
AND    TableA.id = TableC.id


I prefer the second style, it is much more readable in my opinion.

Best Regards,

David

8
You're welcome, good luck :)
9
Hi Joe,


QuoteI'm looking at ways to search a number of different fields in my database.
(...) I'm wondering if the WHERE field LIKE clause could be used

The LIKE operator is really useful for pattern matching data in character columns, I have used it in various (non SQLite) SQL databases with great success.

There appears to be a performance overhead incurred when using the SQLite LIKE implementation though. When you use the LIKE operator, SQLite is unable to use an index on the column you are searching and is forced to do a 'full table scan'. Meaning that SQLite processes all rows of a table (one row at a time) thereby incurring a performance hit. This is of course only really an issue if you have a large number of rows in your database.

There seems to be a workaround solution (which BTW I haven't tested), I found this article on the web relating to LIKE optimisation...

http://geographicalmedia.com/africa/gambia/fajara/post/2008/1/28/converting-to-sqlite-and-like-query-optimization

which in turn refers to the following source article...

http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

Additionally, you might want to check out the case sensitivity behaviour of the LIKE operator before using it. The SQLite_optimization_FAQ.html document is based on SQLite 2.8.6 and mentions that 'The LIKE operator is case sensitive'. If you take a look at the 'SQL as Understood by SQLite' web page... http://www.sqlite.org/lang_expr.html you will see that it states 'The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other'.

It would probably be worth testing to see how it behaves.



Quoteis there an option with the WHERE clause to specific "Not Null"? 
So for example, if I wanted to get every row of data except those
where a specific field was blank.

To select where a column is not null you could do something like...

SELECT contactname
FROM   contacttable
WHERE  contactname IS NOT NULL


Regarding 'blank' character/text data in SQL databases you'll see data that matches the following...
1) null values which indicate 'this value is unknown'
2) not null (but empty '') values indicating 'I know this value and it is empty'
3) columns populated with data

So in order to exclude 'blank' entries you might wish to extend the previous example to...

SELECT contactname
FROM   contacttable
WHERE  contactname IS NOT NULL
AND    contactname <> ''


I hope that all makes sense,

Best Regards

David
10
Hi folks,

As there are no posts in this section yet, I thought I would be the first with something (hopefully) useful!

I was referred to this free online SQL resource last year by the Alpha Software company who produce the Alpha 5 database system so I believe it is perfectly legitimate. There are four SQL books available for download at http://www.computer-books.us/sql.php and to quote from the email I received about one of them...

"Teach Yourself SQL in 21 Days.

The fourth edition (which you can find at Amazon.com) lists for $44.95. However the second edition can be downloaded for free and is a wonderful resource for those of you who are interested in using MySQL, SQL Server and other popular SQL backend databases"
http://www.computer-books.us/sql_0003.php

I actually bought the second edition of this book about ten years ago and thought it was pretty good.

I hope you find something useful and interesting there.

Best Regards

David