SQLite
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
SQLite
Hello.
I’m proud to announce that I implemented in the STHS a new functionality that I was thinking about for a long time, a flat file SQL database! It’s based on SQLite (http://www.sqlite.org/), a software library that implements a self-contained, server less, zero-configuration, transactional SQL database engine. Using php code (http://php.net/manual/en/book.sqlite.php), you can easily read this flat file SQL database and create a custom website with it. Since there is no MySQL database to run in the backend, it easy to setup, to maintain and to support (Ok, this part is for me only). Another advantage is that the database will get recreated from scratch by the STHS each time you output it. This means that to update your website, you simply need to upload the STHS database file each day like the other HTML file and STHS Client file; no funky database update with time-out or error.
You can find the information about the database here: http://sths.simont.info/DatabaseInfo.php
You’ll see the name of the database tables, their corresponding schema (Field Name & Data Type) and some extra information about some field name.
In the version 2.2.7, the databases will be available to output via manual command because I’m not 100% sure the code is bug prove. Doing SQL Insert command when you have over 60 columns is very error prone work. In 2.2.7, I want to give you the possibility to look at the DB and report errors or missing information (Data or Field Type) in the database.
Note: There is multiple of Windows Software that allows you to open a SQLite database. I’m using this one:http://sqlitedbrowser.sourceforge.net/
In the next months, I want to create a brand new STHS Website based on this STHS SQL database in PHP and offer it to everyone who used the STHS. The PHP code will be free of charge and you’ll be able to edit yourself after you download it from the STHS website. If anyone in interested in collaborating with me on this project, please contact me.
Thank you.
I’m proud to announce that I implemented in the STHS a new functionality that I was thinking about for a long time, a flat file SQL database! It’s based on SQLite (http://www.sqlite.org/), a software library that implements a self-contained, server less, zero-configuration, transactional SQL database engine. Using php code (http://php.net/manual/en/book.sqlite.php), you can easily read this flat file SQL database and create a custom website with it. Since there is no MySQL database to run in the backend, it easy to setup, to maintain and to support (Ok, this part is for me only). Another advantage is that the database will get recreated from scratch by the STHS each time you output it. This means that to update your website, you simply need to upload the STHS database file each day like the other HTML file and STHS Client file; no funky database update with time-out or error.
You can find the information about the database here: http://sths.simont.info/DatabaseInfo.php
You’ll see the name of the database tables, their corresponding schema (Field Name & Data Type) and some extra information about some field name.
In the version 2.2.7, the databases will be available to output via manual command because I’m not 100% sure the code is bug prove. Doing SQL Insert command when you have over 60 columns is very error prone work. In 2.2.7, I want to give you the possibility to look at the DB and report errors or missing information (Data or Field Type) in the database.
Note: There is multiple of Windows Software that allows you to open a SQLite database. I’m using this one:http://sqlitedbrowser.sourceforge.net/
In the next months, I want to create a brand new STHS Website based on this STHS SQL database in PHP and offer it to everyone who used the STHS. The PHP code will be free of charge and you’ll be able to edit yourself after you download it from the STHS website. If anyone in interested in collaborating with me on this project, please contact me.
Thank you.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Re: SQLite
dit moi ce que tu veux comme aide
-
- The Addict / Le Drogué
- Posts: 770
- Joined: Mon Aug 14, 2006 5:15 pm
- Location: Vancouver, BC
- Contact:
Re: SQLite
wow that seems awesome! I keep having on the back of my mind to learn xpath to somehow read the xml files... I didn't feel like somehow loading csv's into MySQL... hope sqlite will be easier to deal with! Just don't have many minutes in the day to simply run the sim and upload files for my GMs
I keep promising these guys a better website, hopefully it will work out!
Can't wait to try it out.
I keep promising these guys a better website, hopefully it will work out!
Can't wait to try it out.
BFHL Admin/Commish
http://www.thebfhl.ca/bfhl
Now in Season 15, using BRHL Player v2.1 Ratings modified.
http://www.thebfhl.ca/bfhl
Now in Season 15, using BRHL Player v2.1 Ratings modified.
Re: SQLite
Simon,
This is absolutely fantastic. Well done. I am very excited to test this out and see what kind of results I can get.
I have gone through all the data tables, and the only addition request I could possibly see is to add the intProRankingOrder field that matches the League.xml file to one of the tables like the TeamProInfo or the TeamProStat. But this is very awesome
Thank you for your hard work
This is absolutely fantastic. Well done. I am very excited to test this out and see what kind of results I can get.
I have gone through all the data tables, and the only addition request I could possibly see is to add the intProRankingOrder field that matches the League.xml file to one of the tables like the TeamProInfo or the TeamProStat. But this is very awesome
Thank you for your hard work
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
Done and HTML Link has been updated.RPHALabs wrote:I have gone through all the data tables, and the only addition request I could possibly see is to add the intProRankingOrder field that matches the League.xml file to one of the tables like the TeamProInfo or the TeamProStat.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
Hey guy.
With 2.2.7, you can now output the SQLLite Database with this manual command: 'Output SQLite Database’
If nobody report issue in the next 3 months, I'll put the option to allow the database to be output automatically in the next version.
With 2.2.7, you can now output the SQLLite Database with this manual command: 'Output SQLite Database’
If nobody report issue in the next 3 months, I'll put the option to allow the database to be output automatically in the next version.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
Note : I just find out the command will not work if you don't have the DLL available on your computer.
You can download the DLL here : http://sths.simont.info/DownloadFiles.p ... iteDll.zip and copy it in the same folder that your current .exe file.
You can download the DLL here : http://sths.simont.info/DownloadFiles.p ... iteDll.zip and copy it in the same folder that your current .exe file.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Re: SQLite
How is the testing going with this? Do you have a demo website based on the SQLite data?
STHS Version 3.4.0.0
-
- The Accomplished One / L'Accompli
- Posts: 101
- Joined: Sat May 01, 2010 11:55 am
- Location: P.E.I. Canada
- Contact:
Re: SQLite
I have outputted the STHS.db file and it is now in the hands of my RPHALabs people for analysis. I am waiting to hear the feedback. That's where the RPHA stands so far. Not sure about Simon's testing or anyone else.
-
- The Addict / Le Drogué
- Posts: 752
- Joined: Fri Oct 28, 2005 2:42 pm
- Location: Ste-Julie
- Contact:
Re: SQLite
all my website is based on the CSV files so again .....please keep the CSV files up to date
and add the new info
and add the new info
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
I have 2 php file based on this (Pro and Farm Schedule) but I'll probably take me months to do this. The fact that I suck in CSS and design (As you can see by the STHS lack of design) doesn't help either.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Re: SQLite
SimonT offering a relationnal database will help many developper but I don't think that would change what is actually do with XML and CSV as the code is already there and running.
There's two things that would really, really help any web developpers :
- adding the season year (or id, or any unique season identification). Now, the way that work, importing a CSV, using XML or SQLlite ask to have as much table or file that you have simulated seasons. That's tough to maintain to any non or occasionnal developper.
- in the same way, be sure that ANY ID in all table/structure will be unique over time.
My 2 cents.
There's two things that would really, really help any web developpers :
- adding the season year (or id, or any unique season identification). Now, the way that work, importing a CSV, using XML or SQLlite ask to have as much table or file that you have simulated seasons. That's tough to maintain to any non or occasionnal developper.
- in the same way, be sure that ANY ID in all table/structure will be unique over time.
My 2 cents.
Re: SQLite
Proof of concept don't ask to be beautifull, just provide an "how to" to the community is enough for now I think. As the STHS developper, listen to the community, provide new stuff like that, it's already a big job.SimonT wrote:I have 2 php file based on this (Pro and Farm Schedule) but I'll probably take me months to do this. The fact that I suck in CSS and design (As you can see by the STHS lack of design) doesn't help either.
Don't put on your shoulder to provide a full web portal. As a STHS user, I would prefer that energy is put to STHS over a portal.
Badly, I don't have enough time to build a team to create a portal or any site template but I'm sure somebody will be able to doing this shortly.
-
- New in Town / Le Ptit Nouveau
- Posts: 13
- Joined: Thu Nov 27, 2014 12:03 pm
Re: SQLite
I would be interested in collaborating with you on this.
-
- STHS Owner / Propriétaire du STHS
- Posts: 14776
- Joined: Sat Oct 08, 2005 4:18 pm
- Location: Montreal, Canada
- Contact:
Re: SQLite
Cool. Send me an email and I'll exchange idea.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français