• Welcome, Guest. Please login.
 
December 10, 2019, 03:47:30 am

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 - cj

721
You've got Questions? We've got Answers! / Re: indexes
December 13, 2011, 12:56:38 pm
/* 'or' should be 'on'  in a couple of statements */
/* is a remark in SQLite, nice  */
create table t1 (f1, f2, unique(f1,f2)); /* comma aftter f2 missing */
Only unique indexes may be created in a  create table statement?





722
Great suggestion!
I was  thinking of filling an array and using a combobox or picklist (if such a thing exists.)
Never used the "LIKE" and need to experiment.   A LIMIT of 10 would probably do it.
I can see all sorts of data entry/selection done this way. 
The user typing is not a concern since I do file access in another thread.


Thank you


723
Anyone have a schema for phone lookups?
Clients may have any number of phone numbers and clients may share a phone number
I started using phone numbers as INTEGER and then switched to 12 characters.
Down the line I would like to be able to hone in on a client as digits are pressed.

I'm wondering if phone numbers should be kept in memory to avoid having to call sql
with each keystroke, but would like to see it done with sql and a  LIMIT as you type method (if possible.).

Any help would be greatly appreciated.
Solving this will help in all sorts of other things that will bring up records between keystrokes.
.

select clientid,phone from phonetable order by clientid;
Create table if not exists PhoneTable(
  ClientID INTEGER NOT NULL CHECK(ClientID >ABS(0)),
  Phone  NOT NULL CHECK(Phone > ABS(0)
                  AND LENGTH(Phone) = 12));
/*
Create index if not exists phone1_idx ON PhoneTable(ClientID,Phone);
Create index if not exists phone2_idx ON PhoneTable(Phone,ClientID);
Insert into PhoneTable values(2,'999-456-7890');
Insert into PhoneTable values(1,'987-654-3210');
Insert into PhoneTable values(2,'800-555-1212');
Insert into PhoneTable values(1,'123-456-7890');
Insert into PhoneTable values(2,'987-654-3210');
Insert into PhoneTable values(1,'800-555-1212');
Insert into PhoneTable values(1,'000-000-0000');  */

724
Is this correct?
slExe("statement","C")  'if update,insert or delete?
Only use "C" if we know something must change to avoid error 12?
Never combine statements passed to slEXE?


From ExampleC.bas
Don't normally see statements combined

slExe "Begin; " & _
            "Insert into Archive.Orders Select * from Orders where OrderID = " & READ$(llDo) & ";" & _
            "Delete from Orders where OrderID = " & READ$(llDo) & ";" & _
            "Insert into Archive.LineItems Select * from LineItems where OrderID = " & READ$(llDo) & ";" & _
            "Delete from LineItems where OrderID = " & READ$(llDo) & ";" & _
            "End"     


From ExampleC.bas
This will return an error 12 after adding "C" to the combined statement usng a single slEXE.
I have been doing this type of combining of statements.
I believe you mentioned I should quit combining statements.
slExe("Begin; " & _
            "Insert into Archive.Orders Select * from Orders where OrderID = " & READ$(llDo) & ";" & _
            "Delete from Orders where OrderID = " & READ$(llDo) & ";" & _
            "Insert into Archive.LineItems Select * from LineItems where OrderID = " & READ$(llDo) & ";" & _
            "Delete from LineItems where OrderID = " & READ$(llDo) & ";" & _
            "End","C")





725
1) slGetChangeCount indicates the records changed from the last slEXE?
2) slGetChangeCount may not be trusted if statements are combined?
Error 12 is returned if multiple statements are combined in ExampleC.bas.with slExe("statement","C")

Quote
You normally would check after each slExe.  slGetChangeCount returns the
number of rows that were changed.  If no ModChar T then returns the number
of rows that were changed or inserted or deleted by the most recently
completed SQL statement.  Changes caused by triggers are not counted.  If
ModChar T then returns the number of row changes since open.  Changes
caused by triggers are included.


726
I now see the difference with AUTOINCREMENT when using NULL.
The next highest value is not destroyed (kept in an internal table) when all records are deleted.
I didn't see the difference with AUTOINCREMENT until inserting records using NULL after deleting all records.

create table t1(f1 integer primary key autoincrement);
insert into t1 values(null);
insert into t1 values(null); 
delete from t1;
insert into t1 values(null);
select rowid from t1;
3
727
Thank  you.  I will change the logic of my system to never reuse a rowid.
It was only done this way because client folders are pre-numbered and  reused.
I never liked the way it worked by having blank records in the btrieve table.
It did have the advantage of prealloating disk space and everything was an update.

Trying to figure out if  AUTOINCREMENT is worth the extra overhead.
728
1) How do I get the last rowid inserted?
The rowid was inserted using NULL as ClientNumber where ClientNumber is INTEGER PRIMARY KEY.

Answer to 1:
1) slGetInsertID returns a QUAD of the last rowid.
Hopefully, slGetChangeCount indicates that the last insert was successful?
Just tested  count and equals 1 if multiple records inserted within a transaction.

2) To reuse a deleted rowid should a list of deleted rowid's be kept?
I could mark records as deleted and always update them, but that adds to complication.
In the past I just negated the clientnumber, but it doesn't seem like a good choice.
729
I have gone back to using slSel(s).
Thank you!

I'm trying to create a database manager where all information is read from disk at startup.
The "AS" should really help.

Pretty sure others have already done this.


730
This code appears to work.
Is using slSel(sql,0,"D") okay if only using column numbers?
'Not recommended if using slFN or slFN per docs.


#COMPILE EXE
#DIM ALL
#INCLUDE "SQLitening.INC"
FUNCTION PBMAIN () AS LONG
  LOCAL result AS LONG
  slOpen "test123.db3","C"
  slEXE  "Create Table if not exists Table_Client(ClientNumber INTEGER PRIMARY KEY, LastName Text)"
  DO
    result = MSGBOX("Insert Watson,Thomas,Appleton and show results?",%MB_OKCANCEL,EXE.FULL$)
    IF result = %IDCANCEL THEN EXIT DO
    slEXE  "Insert into Table_Client values(null,'Watson')"
    slEXE  "Insert into Table_Client values(null,'Thomas')"
    slEXE  "Insert into Table_Client values(null,'Appleton')"
    ? Results("Select LastName,ClientNumber from Table_Client order by LastName")
  LOOP
  ? Results("Select rowid,rowid,rowid from Table_Client")
  REM slExe "Drop table Table_Client"
END FUNCTION
'----------------------------------------------------------------------------------------------------
FUNCTION Results(s AS STRING) AS STRING
  LOCAL COL,columns      AS LONG
  LOCAL sRow, sRecordSet AS STRING

  'Not recommended if using slFN or slFNX
  slSel s,0,"D"
 
  columns = SlGetColumnCount
  IF columns = 0 THEN BEEP:EXIT FUNCTION
  DO WHILE slGetRow
    sRow= ""
    FOR COL = 1 TO columns
      sRow = sRow + slF(COL)+ $TAB
    NEXT
    sRecordSet = sRecordSet + LEFT$(sRow,-1) + $CR
  LOOP
  FUNCTION = sRecordSet
END FUNCTION




731
Thanks again.
I definitely missed UNIQUE in the books!
Column constraints:
Not null
Unique
Primary key
Check
Collate


732
How fast is SQLite without indexes?
Does using 'unique' on a non-indexed field take a considerable amount of time?
Not sure if  'unique' on a non-indexed field, would ever be used.

Disk space is not a problem and  inserts are only a couple of records in a few tables while invoicing.


Drop Table if exists Table1;
Create Table if not exists Table1(F1 unique);
begin;
Insert into Table1 values('how many before needing an index?');
Insert into Table1 values('how many before needing an index?');
end;


Would it make more sense to do something like this before user starts searching?

Create index if not exists Table1_F1_Index on Table1(F1);
User does lots of searches
Drop index Table1_F1_Index


Trying to decide whether to use lots of indexes (some infrequenty used.)
Would it make sense to give the user the ability to create/drop indexes?


Curious if it  increases performance or reliability having multiple databases?
It  might allow searching a database without locking other databases.

Table1  Client
Table2  Patient
Table 3 Transaction
Table 4 Totals

I don't know if I have wasted a lot of time, but all calls go through a single function.
The function issues a 'rollback' on any error  and also handles a disconnect with:


CASE %BTN_EXECUTE
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            CONTROL GET TEXT CB.HNDL, %SQLBOX TO s
            IF LEN(s) THEN
              CONTROL DISABLE CB.HNDL, %BTN_EXECUTE
              Execute CB.HNDL, s
              CONTROL ENABLE CB.HNDL, %BTN_EXECUTE
              CONTROL SET FOCUS CB.HNDL, %SQLBox
            END IF
          END IF     



SUB DisplayError(hDlg AS DWORD)
  BEEP
  LOCAL x AS LONG
  x = slGetErrorNumber
  LB hDlg, slGetError  'display in list box
  ScrollListBox hDlg   'scroll list box up
  IF x= -18 THEN
    x=SHELL(EXE.FULL$,1)
    ExitProcess 0
  END IF
END SUB
                 

This appears to be a bit different than most code I've seen and seems to have the
advantage of not having to hard-code values into the program.  Strings are not built.
All statements are either sent to a textbox or read from disk and placed into a textbox.
The contents of the textbox is called with slExe or slSel.

FUNCTION Execute(hDlg AS DWORD, sTemp AS STRING) AS LONG

  LOCAL COL, columns, result AS LONG
  LOCAL s, sRow AS STRING

  IF VARPTR(sTemp) THEN 'use parameter passed
     s = LTRIM$(sTemp,ANY CHR$(0 TO 32))
  ELSE    'nothing passed so read textbox control
    CONTROL GET TEXT hDlg, %SQLBox TO s 'get text
    s = LTRIM$(s,ANY CHR$(0 TO 32))
  END IF
  IF LEN(s) = 0 THEN EXIT FUNCTION 'no input

  'Select statement?
  IF UCASE$(LEFT$(s,7))= "SELECT " OR UCASE$(LEFT$(s,8)) = "EXPLAIN " OR UCASE$(LEFT$(s,7)) = "PRAGMA " THEN
    IF slSel(s) THEN                  'slSel error
      FUNCTION = slGetErrorNumber     'get/set return value
      displayerror hDlg
      slEXE("Rollback")               'rollback?
      EXIT FUNCTION                   'exit function
    'valid select statement
    ELSEIF slGetRow THEN              'recordset
      columns = SlGetColumnCount      'get column count
      IF columns < 1 THEN             'should not occur
        ? "No columns in recordset"   'why, oh, why?
       EXIT FUNCTION
      END IF
      DO                              'process recordset
        sRow= ""
        FOR COL = 1 TO columns
          sRow = sRow + slF(COL)+ $Delimiter
        NEXT
        LB hDlg, LEFT$(sRow,LEN(sRow)-1)
      LOOP WHILE slGetRow
      REM Listbox1.ListIndex = Listbox1.ListCount - 1  'VB6 way
      ScrollListBox hDlg

      'CONTROL HANDLE  gDlg, %LISTBOX TO hList
      'ListboxCount = SendMessage(hList, %LB_GETCOUNT,  0, 0) 'Ask listbox for items count
      'SendMessage hList, %lb_setcaretindex, ListboxCount+1, 0  'yay!!
      'LB hDlg,STRING$(80,"-")
    END IF
  REM Exe statement
  ELSEIF slEXE(s) THEN            'slEXE error
    BEEP
    FUNCTION = slGetErrorNumber   'set error value
    displayerror hDlg
    slEXE("Rollback")             'rollback?
  ELSE
     LB hDlg, "ok - " + s
     ScrollListBox hDlg
  END IF
END FUNCTION

SUB LB(hDlg AS DWORD, BYVAL sText AS STRING)
  LISTBOX ADD hDlg, %LISTBOX, sText
  CONTROL REDRAW hDlg, %LISTBOX
END SUB

SUB ScrollListBox(hDlg AS DWORD)
  LOCAL ListboxCount AS LONG
  LISTBOX GET COUNT hDlg, %LISTBOX TO ListboxCount
  LISTBOX SELECT    hDlg, %LISTBOX,   ListboxCount
  LISTBOX UNSELECT  hDlg, %LISTBOX,   ListboxCount
END SUB
               
733
These might be better (for now) if using older operating systems.
It works with Windows 7.  Not tested with earlier operating systems.


@REM Syntax: PortOn.bat 51234
netsh firewall add portopening protocol=TCP port=%1 "%1 SQLitening"



@REM Syntax:  PortOff.bat 51234
netsh firewall delete portopening protocol=TCP port=%1
734
Just a thought for perhaps some later date.
An option to create a temp file without returning a record set.
The client would browse the temp file using slGet.



See post below. 
Thanks for examples, Fred!
735
Thank you!
The  answers really helped.
It looks like the blob for transactions is  just another column with variable length data in it.
There are so many transactions it  tremendously reduces the number of index entries.

The pictures will go into another table and I won't create another database (good news.)
It is great being able to prototype so easily without a single input form.

I'm  considering dynamic html input forms which would make everything dynamic and give
the ability to  modify the system on the fly remotely by uploading html code.
Html forms are dynamic and allow so much more than dialogs.

The dropped connections test in another thread shows that it is very simple to just reconnect, reopen
and reissue  SQL statements from a single function if an error occurs.
No need to stay connected.