SQLite

If you need help building your website, this is the place to hang out. If you're an HTML, PHP, Perl or web database geek, check it out. / Si vous avez besoin d'aide pour construire votre site web, c'est le forum à regarder! Si vous êtes un expert en HTML, PHP, Perl ou en base de données, c'est le forum pour vous!
JimToupet
STHS Dynamic Website Contributor
Posts: 363
Joined: Wed Jul 02, 2014 10:55 am

Re: SQLite

Post by JimToupet »

Any plan to have boxscore into SQLite ?
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14755
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

I'm not sure if SQLite is best location to save box score information. The structure of the box score information doesn’t really fit well into a database. I think PHP reading XML Games data is probably better even if it’s slower to load.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

I agree with Simon that the XML schemas are best for this. And as for load time, its pretty fast. I can load a boxscore page with an XML file in approximately 0.1 seconds which is well below any standard loading time.
JimToupet
STHS Dynamic Website Contributor
Posts: 363
Joined: Wed Jul 02, 2014 10:55 am

Re: SQLite

Post by JimToupet »

ok.

It's not for a performance issue but for a pratical thing, a league portal or website will be able to run by only uploading one file per simulation (the SQLite database).
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

There is merit to that I think, a 1 file upload site, however, Simon you can back me up on this, the complication becomes normailzing tables in the database to house the data properly. There would be tables upon tables upon tables.

Simon, just a random thought I have, something I just came up with so there is no additional thought to this at all. Just looking for an opinion, what if in the Schedule table there was a large text field with the XML Schema for that game in it. I have no idea how this would work with PHP or anything else yet, but its something I would be willing to test if it would be usable on a website. If you think this could be a good idea, I would begin testing for sure. If not, I wont think of this any further
JimToupet
STHS Dynamic Website Contributor
Posts: 363
Joined: Wed Jul 02, 2014 10:55 am

Re: SQLite

Post by JimToupet »

You can use a CLOB (Character Large OBject) or BLOB (Binary Large OBject) datafield in SQLite. (Can store 1 billion of byte of data)

I can help on table design for this if needed.
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

I am still fairly new to SQLite, but if LOBs can be used and can attach the Game XML files to the schedule tables as such, then a 1 file upload feels very possible. This is very exciting to think about :)
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14755
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

Hi.

The box score data is not keep in the STHS. It’s created on the fly, output in HTML and XML (if you select the option), the stats are compiled and the data is erased for the next game.

The SQLite database file is erased and recreate every time you output it. Adding all the box score data into the file would take a while with all the Insert command.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
JimToupet
STHS Dynamic Website Contributor
Posts: 363
Joined: Wed Jul 02, 2014 10:55 am

Re: SQLite

Post by JimToupet »

SimonT wrote:The box score data is not keep in the STHS. It’s created on the fly, output in HTML and XML (if you select the option), the stats are compiled and the data is erased for the next game.
I was thinking that's the way it's process. That way, no database "export" will work for reason mentionned by Simon.
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

Simon, I have been playing around with the SQLite file and BLOB fields and I believe it could work quite well. I understand that the XML files are generated on the fly.

Since I don't know what your output process is like, I feel I need to ask the question:

Is possible that if the option is selected to output the XML and HTML, that once the file is created, then insert the file into the SQLITE Schedule Table as a BLOB? So every time the stats are compiled, it will check for the file, and if it exists, Update the schedule table field? You could have 2 fields, one for XML games and one for HTML games.

I ask this because I have been successful in my testing today, INSERTing an XML Game File as a BLOB into the SQLite database, writing it to the server and using that XML file to display its content. And even deleting the file afterwards so it doesnt take up additional space, pretty much gives the look and feel of it being read from the DB.
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14755
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

What the idea in inserting the XML games into the SQLite file? What does it bring to the table?
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

I believe there should be a conscieous effort in data access and location. When you have a folder with a bunch of XML files, there is no direct linkage between the files and the schedule except for the ID number in the file name. Which then you have to do extra PHP to see if a file exists on the server but still its a soft link that you have to concatenate ID numbers with file names. However, with an XML file directly stored in the database, it does a few things:

1) Makes the hard data link between the XML(and/or HTML file) and the Scheduled game.
2) No looking at the file server in code, it would be checking if that field is Null in your result/recordset.
3) Not needing the exact filename, or can export on the server as your own name.
4) At the end of the season, all files would be attached to the DB making it great for archiving purposes and wanting to see the games in the future. Imagine archiving an entire season in 1 DB file instead of hundreds (maybe thousands) of other data files.

Of course, it doesnt have to be directly in the schedule table either, separate tables like ProScheduleFiles using the ScheduleID as a primary key would work just as great.

Just my current thoughts.
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

Back to the TeamLog and LeagueLog tables. Would it be a good idea to add the actual STHS Day in there that the transaction happened along with the DateTime field? Since STHS is based on a Day system and not the actual date, this to me would seem to have more merit to use.
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14755
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

2) An FileExist Check or a Field is Null Check is the same in my view.

Do you imagine how large the SQLite database would become with 2460 XML files (82 Pro and Farm Games for a 30 Team League)? With a game XML size of around 555 KB, it would end at 1.3 GO. It’s not logic at all in a DB.

The idea of day is not bad but day are reset to 1 in playoff and do not exist in off-season.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Président ANHS
The Addict / Le Drogué
Posts: 508
Joined: Tue Nov 29, 2005 2:30 am
Location: Summerside, PEI, Canada
Contact:

Re: SQLite

Post by Président ANHS »

The day # is less important in the off season but a lot more important in the regular season. I dont use the date/time in my league, only the day # from the schedule. It woukd be oractical to be able to use that info across the different sections of our site through the db.
Image
Steff Thouin
Président ANHS Commish
www.anhs.qc.ca

21 ans / 30 saisons de simulation
21 years / 30 sim seasons
Post Reply