Liberty BASIC Community Forum
« Eye On SQL »

Welcome Guest. Please Login or Register.
Sep 21st, 2017, 12:16am


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1 2 3  Notify Send Topic Print
 veryhotthread  Author  Topic: Eye On SQL  (Read 892 times)
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 59
xx Re: Eye On SQL
« Reply #30 on: Jul 26th, 2017, 07:25am »

If anyone is interested I added:
- CSV export
- CSV import
- sorts any field on browsing a table.

More on the way...

Dan..
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 59
xx Re: Eye On SQL
« Reply #31 on: Jul 30th, 2017, 10:50am »

You can now generate a LB program from sql commands.
When you select Sql you can enter a sql command.
3 options are available;
1. do SQL will execute the sql command.
2. do CSV will produce a CSV file
3. do GEN will create a LB program.
The output LB program has the structure to change the variable types and their sizes.

Tested as follows:
1. downloaded the cal.db file form the site.
2. Loaded it i to the system.
3. Selected it from the Database selection
4. Entered the following sql command
Code:
SELECT  
s.schNum, 
s.assetNum, 
s.schEnd as preEnd, 
s1.schBeg as nxtBeg, 
((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps, 
(strftime('%w',s.schEnd))   as dowBeg, 
asset.descr as aDescr, 
asset.assetType 
FROM  schTime as s 
JOIN  asset 
ON    asset.assetNum = s.assetNum 
JOIN  schTime as s1 
ON    s1.assetNum = s.assetNum 
AND   s1.schBeg > s.schEnd 
AND   ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= 100 
AND   s1.schBeg = (SELECT min(s2.SchBeg) 
FROM  schTime   as s2 
WHERE s2.assetNum = s.assetNum 
AND   s2.schBeg > s.schEnd) 
AND   s.schBeg >= '2009-05' LIMIT 10
 


What does it do.
The cal.db system has "assets" and "schTime" .
Assets are things you want to schedule such as conference rooms, projectors etc.
schTime is a list of time the assets are already scheduled.
This sql command hunts for open slots available for the amount of time you need the asset, starting on a particular time and listing only a certain number of open slots.

You could easily modify the LB program to request from the user what asset they want and when they want it and how long they need it and how many openings to report. This can be done by making the 100 value a variable such as needTime$. Change the limit of 10 to a variable as numSlots$. Change '2009-05' to a variable of begDate$



User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 194
xx Re: Eye On SQL
« Reply #32 on: Jul 30th, 2017, 11:11pm »

Not sure if its something I've done huh
downloaded cal.db and latest version of ionSQL, the db will load in ionSQL, however the tables are not shown when the db is selected.
Same with 2 previous versions of ionSQL
all other db's will display table schema within ionSQL.

good news though... query result is printed
User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 59
xx Re: Eye On SQL
« Reply #33 on: Jul 31st, 2017, 07:07am »

OOPS!

Thanks for pointing that out.

I was playing and altering cal.db. I thought I was using the one located at the download, but I was using a copy located somewhere else.

The correct one should be there now. If that still has problems let me know.

BTW.. The asset file has a assetNum and a asset type. So you could schedule by type, or a specific asset. For example if you wanted a conference room but didn't care what conference room it was you could ask for all conference rooms by searching the type instead of the asset number.

Thanks again for the heads up..

How is your forecasd for stock picks going?
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 59
xx Re: Eye On SQL
« Reply #34 on: Aug 9th, 2017, 5:48pm »

Bug Fix.

I'm always surprised by sqlite options.
As many of you know, Firefox maintains information in sqlite tables.
They use a primary key to keep track of their rowid.
So if you load some of their files into the system it will give you an error if you try to delete/change their data. This has been fixed.

You may be interested to see what information Firefox has in their files:
cookies.sqlite
places.sqlite
content-prefs.sqlite
formhistory.sqlite
webappsstore.sqlite


Any problems... Let me know..
User IP Logged

Pages: 1 2 3  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls