(2013-03-03, 09:51)nickr Wrote: [ -> ]So why do you need mysql to be accessible over the internet?
Approaches:
1. [pretty static] have an application create a file every day that lists your movies, post it on the internet somewhere. pros: simple cons: not always up to date.
2. develop a webapp with perl/python/php/ruby/whatever and expose it to the web via your webserver - mysql is not exposed to the internet. pros: always up to date cons: you need to expose your local webserver to the internet
3. same as 2 but have the webserver on an external service contacting your local mysql over a secure tunnel. pros: someone elses webserver exposed to the net cons: slow mysql access between webserver and your lan over the net? more complex.
coz i wanted the mysql to only accept connection from my ip at home so that i can have it update whenever my library updates. then i will build an interface with php using that database.
approach 1: it wouldn't be difficult to update but the file can get large depending on how big my library is. the big con for me is i wouldn't be able to allow my friends to search or display particular things. for instance, i'll have a list of movies, but what if they want to get a list by genre, or by actor, or by year. i'd either have to compile a file for each type and have to manage each update or i can do some type of xml and write a script that parses for each search type. but depending on how big that xml file will determine load time. i want it to be accessible on mobile phones so that could be a huge web page that loads.
approach 2: out of that list php is the most i'm knowledgeable with. i'd love to learn python but i'm not sure i want to invest the time for this little project. i don't want to turn this project into something larger than what i had anticipated. if i host it at home, i'd have to open up some ports. i would also have to worry about my ip changing - i could setup a dynamic dns which could cost me a few $ if i wanted a good one. my ISP could also be blocking port 80 or send me an email stating i shouldn't be hosting anything from home. in short, more work for me.
approach 3: i don't want to open ports at home than what i need.
what i may end up doing are the following:
1. similar to your approach 2. i will run a local mysql server. have a mysql server on my web host. allow connection from my IP. create a script that does a mysqldump from my local to the server. then create a php front end on that web server. i still may have to deal with my IP changing at home but at least i don't have to open ports at home. it's also easy to manage remote access with cpanel on my hosting provider.
2. similar to my #1 but instead of using mysqldump and 2 database servers, i will use sqlite3 file. create a script that uploads the sqlite3 file on my website and use PHP front end. this is what i'm actually working on now. it's a little slow querying the sqlite3 database but it works so far. once i finish i may have to look into how to optimize it. i'm using PHP codeigniter to connect to the sqlite3 database and create the front end. i also use TMDB's api for info i can't pull from the database.
3. learn python. i have been planning on doing this as i heard great things about it. i started with django but couldn't find any articles with building an app that has an existing database. so i didn't know how to handle the model part of the MVC. i didn't want it to have full access to database since i want it to only do selects and not change the data.
it's been a few months since i've been working on this - part time. even though i didn't want this to be any bigger than it already has, i don't want it to get any larger - what i mean is the time i spend on it. it is a great exercise though. i get to learn about xbmc's database structure, tmdb api, and i was introduced to python and django.
thanks for the suggestions. i just wish someone already has come up with this so i didn't have to build one from scratch.