SQLite
Re: SQLite
Any plan to have boxscore into SQLite ?
-
- STHS Owner / Propriétaire du STHS
- Posts: 14798
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
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 Manual - Manuel Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English Manual - Manuel Français
Re: SQLite
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.
Re: SQLite
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).
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).
Re: SQLite
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
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
Re: SQLite
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.
I can help on table design for this if needed.
Re: SQLite
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 ![Smile :)](./images/smilies/icon_smile.gif)
![Smile :)](./images/smilies/icon_smile.gif)
-
- STHS Owner / Propriétaire du STHS
- Posts: 14798
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
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.
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 Manual - Manuel Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English Manual - Manuel Français
Re: SQLite
I was thinking that's the way it's process. That way, no database "export" will work for reason mentionned by Simon.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.
Re: SQLite
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.
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.
-
- STHS Owner / Propriétaire du STHS
- Posts: 14798
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
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 Manual - Manuel Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English Manual - Manuel Français
Re: SQLite
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.
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.
Re: SQLite
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.
-
- STHS Owner / Propriétaire du STHS
- Posts: 14798
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
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.
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 Manual - Manuel Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English Manual - Manuel Français
-
- The Addict / Le Drogué
- Posts: 508
- Joined: Tue Nov 29, 2005 2:30 am
- Location: Summerside, PEI, Canada
- Contact:
Re: SQLite
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](http://www.anhs.qc.ca/images/banner_400.png)
Steff Thouin
Président ANHS Commish
www.anhs.qc.ca
21 ans / 30 saisons de simulation
21 years / 30 sim seasons