[Info-vax] Coding with/without RDBMS
Dave Froble
davef at tsoft-inc.com
Thu Oct 14 01:43:43 EDT 2021
Was getting way off topic, so new topic
Time to spam c.o.v
:-)
On 10/13/2021 10:54 PM, Dave Froble wrote:
> On 10/13/2021 8:31 PM, Arne Vajhøj wrote:
>> On 10/13/2021 4:53 PM, Dave Froble wrote:
>>> On 10/13/2021 1:59 PM, Arne Vajhøj wrote:
>>>> On 10/13/2021 11:09 AM, Dave Froble wrote:
>>>>> On 10/13/2021 10:04 AM, Arne Vajhøj wrote:
>>>>>> On 10/12/2021 9:52 PM, Dave Froble wrote:
>>>>>>> On 10/12/2021 5:10 PM, Arne Vajhøj wrote:
>>>>>>>> On 10/12/2021 4:42 PM, Dave Froble wrote:
>>>>>>>>> On 10/12/2021 3:55 PM, Arne Vajhøj wrote:
>>>>>>>>>> But the money math has changed.
>>>>>>>>>>
>>>>>>>>>> I would say that over the last 30 years:
>>>>>>>>>>
>>>>>>>>>> RDBMS license cost changed from expensive to free options
>>>>>>>>>> available
>>>>>>>>>>
>>>>>>>>>> RDBMS hardware resource cost changed from expensive to
>>>>>>>>>> insignificant
>>>>>>>>>>
>>>>>>>>>> writing and maintaining code to manage IDX file cost is more or
>>>>>>>>>> less
>>>>>>>>>> constant
>>>>>>>>>
>>>>>>>>> Are you suggesting writing and maintaining code for RDBMS is any
>>>>>>>>> different?
>>>>>>>>
>>>>>>>> You need much less code because the database software does
>>>>>>>> so much.
>>>>>>>>
>>>>>>>> It is a tradeoff - you write much less code but the generic code
>>>>>>>> in the RDBMS use more CPU and memory.
>>>>>>>
>>>>>>> Excuse me, I'm just a dummy, come down out of the hills. But:
>>>>>>>
>>>>>>> 1) Open file
>>>>>>> 2) Access data
>>>>>>> 3) Do some work
>>>>>>> 4) Write/Update data
>>>>>>> 5) Done
>>>>>>>
>>>>>>> and
>>>>>>>
>>>>>>> 1) Access database
>>>>>>> 2) Access data
>>>>>>> 3) Do some work
>>>>>>> 4) Write/Update data
>>>>>>> 5) Done
>>>>>>>
>>>>>>> Guess I don't see much difference.
>>>>>>
>>>>>> That is because you describe *what* is being done not *how* it is
>>>>>> done.
>>>>>>
>>>>>> In general you can expect:
>>>>>>
>>>>>> data maintenance - replacing a lot of application code with few
>>>>>> lines of
>>>>>> SQL
>>>>>
>>>>> You claim that, but I just don't see it.
>>>>>
>>>>>> applications with simple queries - slightly less code
>>>>>>
>>>>>> application code with complex queries - a lot less code
>>>>>>
>>>>>> adhoc just get some numbers - replacing a lot of application code with
>>>>>> few lines of SQL
>>>>>
>>>>> Don't see that.
>>>>>
>>>>> Also, I've noticed that doing some things with SQL can be much more
>>>>> complex.
>>>>
>>>> Here is an example of some (Python) code using SQL to
>>>> join, select, aggregate, sort and limit some data.
>>>>
>>>> import sqlite3
>>>>
>>>> con = sqlite3.connect('test.db')
>>>> c = con.cursor()
>>>> c.execute('''SELECT customers.name,SUM(orderlines.price) AS totalsales
>>>> FROM customers
>>>> LEFT JOIN orders ON customers.customer_id =
>>>> orders.customer_id
>>>> LEFT JOIN orderlines ON orders.order_id =
>>>> orderlines.order_id
>>>> WHERE orders.status = 'Delivered'
>>>> GROUP BY customers.name
>>>> HAVING totalsales > 10
>>>> ORDER BY totalsales DESC
>>>> LIMIT 3''')
>>>> for row in c.fetchall():
>>>> print('%s : %d' % (row[0], row[1]))
>>>> con.commit()
>>>> con.close()
>>>>
>>>> I believe that it would take a lot more code to
>>>> retrieve data from index-sequential files and
>>>> do the work on the data.
>>>
>>> Not a lot, however the sorting by customer name would entail some
>>> additional code.
>>>
>>> However, reading the order file, doing the selection, look-up customer
>>> record, and release to record sort, then read back sorted records is
>>> not "a lot more" code.
>>
>> I tried writing it in Pascal using index-sequential files.
>>
>> program cmplx(input,output);
>>
>> const
>> MAX_ORDERLINES_PER_ORDER = 100;
>>
>> type
>> orderline = record
>> item : packed array [1..50] of char;
>> price : integer;
>> end;
>> customer = record
>> customer_id : [key(0)] integer;
>> name : packed array [1..50] of char;
>> address : packed array [1..100] of char;
>> end;
>> order = record
>> order_id : [key(0)] integer;
>> customer_id : [key(1)] integer;
>> ref : packed array [1..50] of char;
>> status : packed array [1..25] of char;
>> no_orderlines : integer;
>> orderlines : array [1..MAX_ORDERLINES_PER_ORDER] of
>> orderline;
>> end;
>>
>> const
>> MAX_CUSTOMERS = 10000;
>>
>> type
>> extcustomer = record
>> basis : customer;
>> total_sales : integer;
>> end;
>> extcustomerlist = array [1..MAX_CUSTOMERS] of extcustomer;
>>
>> var
>> no_xc : integer;
>> xc_list : extcustomerlist;
>>
>> procedure load_data;
>>
>> var
>> fc : file of customer;
>> fo : file of order;
>> c : customer;
>> o : order;
>> i : integer;
>>
>> begin
>> open(fc, 'customer.isq', old, organization := indexed, access_method
>> := keyed);
>> open(fo, 'order.isq', old, organization := indexed, access_method :=
>> keyed);
>> resetk(fc, 0);
>> no_xc := 0;
>> while not eof(fc) do begin
>> c := fc^;
>> no_xc := no_xc + 1;
>> xc_list[no_xc].basis := c;
>> xc_list[no_xc].total_sales := 0;
>> resetk(fo, 1);
>> findk(fo, 1, c.customer_id);
>> while not(ufb(fo)) and not(eof(fo)) and (fo^.customer_id =
>> c.customer_id) do begin
>> o := fo^;
>> if o.status = 'Delivered' then begin
>> for i := 1 to o.no_orderlines do begin
>> xc_list[no_xc].total_sales := xc_list[no_xc].total_sales
>> + o.orderlines[i].price;
>> end;
>> end;
>> get(fo); ;
>> end;
>> get(fc);
>> end;
>> close(fo);
>> close(fc);
>> end;
>>
>> procedure sort_data;
>>
>> var
>> i, j : integer;
>> tmp : extcustomer;
>>
>> begin
>> for i := 1 to no_xc do begin
>> for j := (i + 1) to no_xc do begin
>> if xc_list[i].total_sales < xc_list[j].total_sales then begin
>> tmp := xc_list[i];
>> xc_list[i] := xc_list[j];
>> xc_list[j] := tmp;
>> end;
>> end;
>> end;
>> end;
>>
>> procedure dump_data;
>>
>> var
>> i : integer;
>>
>> begin
>> for i := 1 to min(no_xc, 3) do begin
>> if xc_list[i].total_sales > 10 then begin
>> writeln(xc_list[i].basis.name,' ',xc_list[i].total_sales);
>> end;
>> end;
>> end;
>>
>> begin
>> load_data;
>> sort_data;
>> dump_data;
>> end.
>>
>> Arne
>>
>>
>
> Gee, I'm sure glad I never tried Pascal.
>
> Ok, the gauntlet has been thrown down. I accept. I will attempt to better define the task, then implement it, in Basic, using my tools, and my coding style. Give me a day or two.
>
> Task definition
>
> Select customers with shipped orders
> Select orders with total dollars shipped >= 10
> Sort by customer name, then by dollars, descending
> Limit output to 3 orders per customer
> print customer name and total dollars per order
>
> Is that it?
>
Ok, didn't take that day or two.
Some notes:
Program produced with IDE, lots of boilerplate stuff. Maybe 85%-90% produced by IDE.
274 lines, including blank lines and comments, most produced by IDE.
The sorting greatly increased code, the "ORDER BY" and "LIMIT" in SQL sure is nice.
No order lines file used, since any decent software architect would keep the total in order record.
If Arne can use RDBMS, I can use IDE and external library routines ...
Re-reading the program I can see two errors, but they are not pertinent to the issue of code required.
The data files:
DAS listing of: custmr.DAS on 13-Oct-2021 10:57 PM
IDX LEN POS TYPE RMASK WMASK ----------NAME----------
1 8 0 L-string 0 0 Customer number
2 30 8 L-string 0 0 Customer Name
KEY # 1
FIELD 1 =< 1,Customer number>
DAS listing of: order.DAS on 13-Oct-2021 10:57 PM
IDX LEN POS TYPE RMASK WMASK ----------NAME----------
1 8 0 L-string 0 0 Order number
2 8 8 L-string 0 0 Customer number
3 4 16 DI2 0 0 Total dollars shipped
KEY # 1
FIELD 1 =< 1,Order number>
KEY # 2
FIELD 1 =< 2,Customer number>
The program:
2 !********************************************************************
!
! Program: ARNE.BAS
! Function: Display customer sales
! Version: 1.00
! Created: 13-Oct-2021
! Author(s): DFE
!
! Purpose/description:
!
! This program will read customer records, and for
! each customer look-up order records, selecting
! sales of $10 or more. Data will be sorted by
! customer name, then descending order sales.
! Only 3 orders for each customer will be printed.
!
!********************************************************************
!
! I/O channel specifications:
!
! -Ch--Ch var--------File var------Description------Type Keys-
! 1 CUSTMR.DAS% CUSTMR.DAS$ Customer file 1
! 2 ORDER.DAS% ORDER.DAS$ Order file 1
! 9 LP% LP$ Output file
! 99 KB% Command keyboard Ascii
!
!********************************************************************
!
! Copyright 2021 by Dave Froble Enterprises, Inc.
!
! This program is the sole property of Dave Froble
! Enterprises, Inc. and may not be copied in whole
! or in part without the express written permission
! of Dave Froble Enterprises, Inc.
!
!********************************************************************
!
! Modification history:
!
!********************************************************************
Option Size = ( Integer Word , Real Double )
%Include "FL:SUPLIB.INC"
%Include "FL:KBCOMM.INC"
%Include "FL:KBPCOM.INC"
%Include "FL:SRTCOM.INC"
Declare Long Rec% , ! Longwords &
D9%
Dim CUSTMR$(2) , ! Label arys &
ORDER$(3)
MAP (S) S_KEY$=16, S_IDX$=34
MAP (S) S_CUST$=8, &
S_AMT$=8, &
S_NAME$=30, &
S_SALES$=4
200 !**************************************************
! Program Initialization
!**************************************************
GoSub 4990
Print #KB% ! Display banner
Print #KB%, P8$; " "; DATEL(D9%,4%); " "; TIME$(0%)
Print #KB%
500 !**************************************************
! Open Data Files
!**************************************************
GoSub 5100 ! Open files
600 !**************************************************
! Get Output Filename
!**************************************************
LP% = 9%
LP$ = "ARNE.LST"
GoSub 9000
GoTo 4950 IF E%
900 !**************************************************
! Init for Sort
!**************************************************
S.KEY% = LEN(S_KEY$)
S.IDX% = LEN(S_IDX$)
Call RSORTW5( 0% , S.KEY% , S.IDX% , 0% , "" )
1000 !************************************************************
! Pre-sort Processing
!************************************************************
For Rec% = 1% To FDB(CUSTMR.DAS%,0%) ! Loop thru file
Call FMREAD( CUSTMR.DAS% , Rec% , 0% , E% , CUSTMR$() )
If E% ! Read rec
Then Iterate If E%=-3%
Call KBEMSG("Unable to read rec!",E%)
GoTo 4900
End If
CustNum$ = CUSTMR$(1%) ! Get cust #
Call FMKPOS( ORDER.DAS% , CustNum$ , 2% , Z$ , E% )
If E% ! Init order search
Then Iterate If E%=-3% Or E%=-15%
Call KBEMSG("Unable to position for search!",E%)
GoTo 4900
End If
OrderLoop:
Call FMNEXT( ORDER.DAS% , CustNum$ , 2% , 0% , ORDER$() , E% )
If E% ! Read order recs
Then Iterate If E%=-3% Or E%=-15%
Call KBEMSG("Unable to look-up rec!",E%)
GoTo 4900
End If
Amt = DIOUT(ORDER$(3%),2%) ! Get amount
GoTo OrderLoop If Amt < 10. ! Select
Call SORTW7( -Amt , Z$ ) ! Make amt sortable
S_CUST$ = CustNum$ ! Build sort rec
S_AMT$ = Z$
S_NAME$ = CUSTMR$(2%)
S_SALES$ = ORDER$(3%)
Call RSORTW3( S_KEY$ , S_IDX$ ) ! Release sort rec
GoTo OrderLoop
Next Rec% ! End loop
3000 !************************************************************
! Post-sort Processing
!************************************************************
Call RSORTW5( 0% , 0% , 0% , 0% , Z$ ) ! Init post-sort
Print #KB%, "Finished sorting on "; DATEL(L_SYDATE,4%); " @ "; TIME$(0%)
Print #KB%
Call FAOPEN( LP% , LP$ , 2% , 132% , E% ) ! Open output file
GoTo 4900 If FNE%("open <"+LP$+">") If E%
L.Cust$=""
CustCnt% = 0%
!**************************************************
! Process Sort Records
!**************************************************
For SREC = 1. To W5 ! Loop thru sort recs
Call RSORTW4( SREC , S_KEY$ , S_IDX$ ) ! Read a sort rec
If S_CUST$ = L.Cust$ ! If same cust
Then CustCnt% = CustCnt% + 1% ! Count recs for cust
Iterate If CustCnt% > 3% ! Skip if more than 3
Else CustCnt% = 1% ! New cust
End If
Print #LP%, S_CUST$; &
Tab(10%); S_NAME$; &
Tab(41%); Format$(DIOUT(S_SALES$,2%),"###,###.##")
Next SREC ! End of loop
Call RSORTW6( "" , 0% , "" , 4% , E% ) ! Terminate sort
GoTo 4950
4900 !************************************************************
! Exit Point
!************************************************************
Print #KB%
E% = KBINPT( "Type <CR> to exit ...." , 0% , I0$ , E% )
4950 Print #KB%, "End of "; P8$
GoTo 32760
!************************************************************
! Subroutines
!************************************************************
4990 !**************************************************
! Program Initialization
!**************************************************
P9$ = "ARNE" ! Program name
P8$ = "Display customer sales V 1.00" ! Function name
On Error GoTo 32000 ! Enable error trapping
KB% = 99% ! Keyboard channel
KB.MODE% = 0% ! Mode for keyboard ope
n
Call KBOPEN( KB% , KB.MODE% ) ! Open keyboard
X3% = 0% ! Cursor addressing swi
tch
E1$ = " not a valid " ! Std error text
E2$ = "Unable to " ! Std error text
Return
5100 !************************************************************
! Open Data Files
!************************************************************
CUSTMR.DAS% = 1%
ORDER.DAS% = 2%
CUSTMR.DAS$ = "CUSTMR.DAS"
ORDER.DAS$ = "ORDER.DAS"
Call FMOPEN( CUSTMR.DAS% , CUSTMR.DAS$ , 1% , 1% , E% , E$ )
CALL FMOPEN( ORDER.DAS% , ORDER.DAS$ , 1% , 1% , E% , E$ ) Unless E%
GoTo 4900 If FNE%("open <"+E$+">") If E%
Return
9000 !**************************************************
! Report Output Filename Prompt
!**************************************************
Print #KB%
E% = KBINPT( "Output to <"+LP$+"> " , 0% , I0$ , E% )
Return If E%
I0$ = LP$ Unless LEN(I0$)
LP$ = I0$
Print #KB%
Print #KB%, " Output will be to < "; LP$; " >"
Print #KB%
Return
20000 %Include "FL:FNE0I4.INC"
%Include "FL:FNEI4.INC"
%Include "FL:FNCS4.INC"
%Include "FL:FNPRI4.INC"
%Include "FL:FNSI4.INC"
32000 !******************** ERROR TRAPS ********************
32700 Print ! Final error trap
Print "Unforseen error detected in <"; P9$; ">"
On Error GoTo 0
32760 Call CLRFLD( CUSTMR$(),ORDER$() )
32767 End
--
David Froble Tel: 724-529-0450
Dave Froble Enterprises, Inc. E-Mail: davef at tsoft-inc.com
DFE Ultralights, Inc.
170 Grimplin Road
Vanderbilt, PA 15486
More information about the Info-vax
mailing list