Data help needed

[Petrus Vorster]

Hi Guys

I made this little tool to allow our branches to send multiple SMS's to customers for parcel notification collections.
It is a small tool that simply uses cell number, surname & initials, shelf number and a Customs fee from the user, then it checks for reasonable cellphone number correctness and can validate a tracking number if one is entered.
It then creates an upload txt file which they upload to a website, which broadcasts the notifications.

It works well, and the idea is to make it as fast as possible for the user to send notices and remembers cell numbers previously entered since most customers are repeat customers and get multiple overseas items.

Government bureaucracy will not pay for any development nor new equipment, so i am helping them where i can. But i found one branch with an inflow of roughly 1000+ items a day from China and they have such a backlog that i can anticipate my little flat file i use to store and retrieve cell numbers will run into trouble in less than 6 months.

I need someone who will take a look at my project and show me the methods to do this with sqlite since i am a complete dummy with that. (My files worked fine and i hardly dabbed into any serious database work)

The trick is that a search must be lightning fast and 100% crash proof since the poor guy entering this items is alway working at least 50% staff short and time is of the essence even if there are 400k numbers in there. The specific branch is in a Metro area. Heavy inflow, loads of clients and as per government fumbling, way to few staff.

Anyone willing to take a look at what i did, i will really appreciate the assistance.

-Peter

[David Kenny]

Would breaking the file up be of any help?  All the 503 prefixes stored in 503.txt for instance?  Basically pre-sorting by area code. 

Edit: Pre-Sorting will drastically cut the individual file size and thereby search time.

[Paul Squires]

Changing your application at this point to use SQLite will require a lot of work and re-engineering of your code. Are you sure you want to mess with it and risk it? David's idea is certainly an idea to consider before you go all in and change the entire backend.

[Petrus Vorster]

Hi Guys

Those thoughts had occurred to me.
SMs numbers here range between 060 to 084+9 digits. Perhaps then go on the first 3 numbers part.

It is an immensely small tool, a few Kb, one form only.
Simply work like if a number exist then display client name, if not, add to the db.
(saves only 3 things. Cell number, Surname & initials, that's it.)

I will have to go this way for now, thanks guys.
But an honest thought from you guys please, what do you think would eventually be the fastests?
Those flat files or an SQL database?

Cheers- Peter

[Paul Squires]

Using sqlite would be my preference because the sql language reduces so much procedural code from your application. It also hides all of the data management stuff like adding, editing, updating, deleting,and disk management. I see that your app is in FF on PB, so you should use Jose's sqlite class. You could create the database in code, or more simply, create it using one of the many free sqlite database programs. Once you have the database with the necessary tables created, then using sql to add records and query results is relatively easy. Once again, especially if you use Jose's wrappers. There are older style approaches that use callback functions to deal with results returned from a query but I would stay away from that approach. It is a little more cumbersome.

If you want to go down the sqlite path then I'll try to help as much as I can. I am on FB 100% these days but I could try remembering how things are done in PB  :-)

Step 1 would be for you to get Jose's classes and at least understand how his examples work. Understand how to add records and query the database. It is not overly difficult.

[Petrus Vorster]

As soon as your new visual designer is done, It will be Sayonara from me to Powerbasic permanently.

Current situation at work is like watching Survivor LOL
For now my current stuff will have to do, and if we survive this next few months, and your designer is ready, then I will look into all the new exciting things you are currently working on. I cannot wait!

Regards,

Peter

[David Kenny]

I'm still extremely happy with FF and PB.  I will definitely install FB and your new IDE when it's up and running, mainly because I am still a big fan of your software. But it probably would take a while to make the leap completely away from PB as there is still nothing compelling me to do so. 

[Paul Squires]

FB has its good and bad points just like any compiler however switching over to it was not that painful. Just need to learn the way things are done in the language. With Jose's WinFBX framework, basically just about everything in PB is easily done in FB. I don't see PB being further developed. At least, albeit slowly, FB is advancing forward. I believe that when user Imortis finishes rewriting the FB runtime from C and ASM over to FB itself, then we'll see it easier for developers to jump on board and extend the compiler even further. I am laser focused to finish WinFBE and deliver a full featured Windows experience complete with a great code editor, visual designer, and VB like object language underneath it all to design your code with. That VB like language is next to get my attention. I started a version of it some time ago but I need to revisit it again and polish it up with the many new things I've learned since. It is tightly integrated with Jose's WinFBX and will take full advantage of WinFBE's codetips and autocomplete functionality.

[David Kenny]

That sounds great Paul!  I am looking forward to playing with it.

[Mike Doty]

Petrus,

Wouldn't be much more than this using PowerBASIC and SQLitening.
The correct create and index statements and this is most of it.

SQLite is an embedded database which means feel free to mix code from any language with it.
You are not required to work in all SQLite statements (which I did when I started out.)
I created generic insert, update, delete functions which could be easily modified.
A pure SQL person might hate this code, but after years of working with SQLite,  do anything you like.

You could even run the SQLitening server at the remote location and keep everything updated in real-time using slConnect.
SQLitening has no internet security so that is another subject.

Keep your current system and write a little SQL/SQLitening application that does what you want.
If it works, import some data and do testing.   If you get everything working, great.

http://www.sqlitetutorial.net/

http://sqlitening.com

Another option is using the incredibly fast OptTech sort which can create indexes or sets of records on any data positions.


#INCLUDE "sqlitening.inc"
%DropTable =1
'-----------------------------------------------------------------------------------
FUNCTION PBMAIN () AS LONG

slSetProcessMods "E0"  '0= we will display errors, not SQLitening

'slConnect sIpAddress,PortNumber&   'use remote server is optional
slOpen "sample.db3","C"             'open database to hold tables
IF %DropTable THEN slexe "drop table if exists MyTable" 'only while testing
slexe  "create table if not exists MyTable(cellnum,surname,shelfnumber UNIQUE,fee)"

InsertRecord "393-292-9391", "Paul" , "1", "131.01"
InsertRecord "123-456-7892", "Mike" , "2",  "14.87"
InsertRecord "555-555-1213", "Bob"  , "3",   "3.33"
InsertRecord "999-999-9994", "Heidi", "4",  "11.50"
DeleteRecord "2"
UpdateShelf  "4"
? GetData("select * from MyTable")

END FUNCTION
'-----------------------------------------------------------------------------------
FUNCTION InsertRecord(sCellNum AS STRING, sSurName AS STRING, sShelfNumber AS STRING, sFee AS STRING) AS LONG
LOCAL s AS STRING
LOCAL er AS LONG
s =  "insert into MyTable values('#1','#2','#3','#4')
REPLACE "#1" WITH sCellNum     IN s
REPLACE "#2" WITH sSurName     IN s
REPLACE "#3" WITH sShelfNumber IN s
REPLACE "#4" WITH sFee         IN s
slexe s
er = slGetErrorNumber
IF er THEN ? s,,slGetError
FUNCTION =er
END FUNCTION
'-----------------------------------------------------------------------------------
SUB UpdateShelf(sShelfNumber AS STRING)
'MyTable(cellnum,surname,shelfnumber UNIQUE,fee)"
slEXE "update MyTable Set Cellnum = '000-000-0000' where ShelfNumber = '" + sShelfNumber + "'"
END SUB
'-----------------------------------------------------------------------------------
SUB DeleteRecord(sShelfNumber AS STRING)
LOCAL s AS STRING
s = "delete from MyTable Where ShelfNumber = '#1#'"
REPLACE "#1#" WITH sShelfNumber IN s
slexe s
IF slGetChangeCount THEN ? "Deleted " + sShelfNumber
END SUB
'-----------------------------------------------------------------------------------
FUNCTION GetData(sStatement AS STRING) AS STRING
DIM sArray() AS STRING
slSelAry sStatement,sArray(),"Q9c" 'Q9 =tab delimited, c=no column names
FUNCTION = JOIN$(sArray(),$CR) + $CR 'convert recordset to a string
END FUNCTION

[Mike Doty]

Another way might be to either write your own routine to load all the data into a SQLite table or
buy a SQLite repair/maintenance program that allows you to define and import data into SQLite tables.

Then everytime you update,insert or delete in your current program call update,insert, delete SQLite function.
A flat file and a SQLite table with a single program.

[Mike Doty]

I think you could add about 10-lines of SQLitening code to your current program where you insert/update/delete records
Include screenshots of some results without and without indexes.  The results are impressive in my opinion.
Should also note that SQLitening has a slGet and slPut for use with flat data files and of course can be connected to its remote server if wanted.
End of prototyping.   If you need any help, please post here or on SQLitening site.

Thanks Paul for these forums!!

Table WITH 6 indexes 68,894,702 bytes    400,000 records
Table NO indexes     26,255,360 bytes    400,000 records

Average time to get and display 6 records at end of table using each index .09 seconds
Average time to get and display 6 records at end of table using no indexes .59 seconds

Time to create database and table was under 30-seconds with 6 indexes

slexe  "drop table if exists t1"
slexe "create table if not exists t1(c1,c2,c3,c4,c5,c6)"
slexe "Create index if not exists C1Index ON T1(C1)"
slexe "Create index if not exists C2Index ON T1(C2)"
slexe "Create index if not exists C3Index ON T1(C3)"
slexe "Create index if not exists C4Index ON T1(C4)"
slexe "Create index if not exists C5Index ON T1(C5)"
slexe "Create index if not exists C6Index ON T1(C6)"


#INCLUDE "sqlitening.inc"
%DropTable = 1     'change to 0 after table created
%Records= 400000   'number of records to insert, last rowid=%Records
%CreateIndexes=0    'create an index on each of the 6 columns
'rough estimate junk.db3 will be between 30 megabyte to 40 megabytes without indexes

FUNCTION PBMAIN () AS LONG

LOCAL x                 AS LONG
LOCAL s                 AS STRING
LOCAL c1,c2,c3,c4,c5,c6 AS STRING
LOCAL sArray()          AS STRING
LOCAL StartTime,EndTime AS STRING

slOpen "junk.db3","C"
IF %DropTable THEN
  slexe  "drop table if exists t1"
  slexe "create table if not exists t1(c1,c2,c3,c4,c5,c6)"
  'we will create index when done so writes are many times faster
  StartTime = TIME$
  slexe "Begin Exclusive" 'start transaction, 1-lock at start and 1-unlock at finish

  FOR x = 1 TO %Records  'create 400,000 records very rough estimate
   c1 = USING$("C1-#",x)
   c2 = USING$("C2-#",x)
   c3 = USING$("C3-#",x)
   c4 = USING$("C4-#",x)
   c5 = USING$("C5-#",x)
   c6 = USING$("C6-#",x)
   s  = USING$("insert into t1 values('&','&','&','&','&','&')",c1,c2,c3,c4,c5,c6) 'insert data
   slexe s
  NEXT
  slexe "End" 'end transaction
  EndTime = TIME$
  IF %CreateIndexes THEN
    ? "Click to create 6 indexes"
   slexe "Create index if not exists C1Index ON T1(C1)"
   slexe "Create index if not exists C2Index ON T1(C2)"
   slexe "Create index if not exists C3Index ON T1(C3)"
   slexe "Create index if not exists C4Index ON T1(C4)"
   slexe "Create index if not exists C5Index ON T1(C5)"
   slexe "Create index if not exists C6Index ON T1(C6)"
  END IF

END IF
DO
  s = INPUTBOX$("prompt","title",FORMAT$(%Records))
  IF LEN(s) = 0 THEN EXIT DO 'end program
  Viewer "select * from t1 where C6 = 'C6-"+s + "'" ' example: 'C6-400000'
LOOP
END FUNCTION

FUNCTION Viewer(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
? JOIN$(sArray(),$CR),,sql
END FUNCTION 

After writing the above program, I played with the data in a home-grown SQLite query viewer to get some timings:
When I first began using SQLite  the viewer was handy in typing in all the exercises from the book "The SQL Guide to SQLite" by Rick F. van der Lans
https://www.amazon.com/SQL-Guide-SQLite-Rick-Lans/dp/0557076765/ref=sr_1_5?ie=UTF8&qid=1516629897&sr=8-5&keywords=The+SQLite+Guide+to+sqlite

[Petrus Vorster]

Hi Mike

Thanks a million for all the ideas.
You would not perhaps be interested if i ZIP and mail you the little project to take a look at it?

You could fiddle around with it and see if you could change it to SQL.
I would also learn a great deal by seeing this in action in the project.

I Will gladly add you to the credit list.

I am thinking about future changes to the tool since the more we use it, the more i can think of something else i want to add.

I should have moved to SQL years ago, but being a hobbyist, i always found reasons not too.
I only make little tools to make work easier, barcode generators, labels printing etc....

It would be very cool if i eventually move over to this with some help for you.

[Mike Doty]

Sure.
Remember you can still keep the current system and just use SQLite indexes to get the record number in your flat file.
I think you can get my email address by clicking Denise Richards.

Just need file layout, field statements or type statement

[Petrus Vorster]

Well, good things does seem to come from small beginnings.

Finally, after much struggle and support from many senior people, the Executives had agreed to emulate the the tool i made for SMS's into a website using an external service provider.
The little app was tested in Metro offices running up to 4000 SMs's per day without  a flaw.
Well that would never have happened without Firefly, Paul, Mike, Jose, and the entire bunch of people here through the years.

Whether or not that will actually happen, i do not know, but at least it went to the CEO and all the people with great sounding titles.
Pity they want an external provider, but i assume paid support is better than what i can provide.
But what the heck, Powerbasic and Firefly did make one hell of a difference in the most stuck-up, autocratic, and backwards thinking place on the planet.

Thanks lads