Thread: Scheduling data input from tab delimited file via php
Hi, I have a client comprising of a group of car dealerships. Thier accounting software uploads via ftp a tab delimited file of their updated stock whenever the stock is updated. What I need to do is execute a php scipt that imports data from the file into the database at a regular interval, ie. every hour. Does postgres have an ability handle this in a different way, maybe by removing the need to use php at all? Can postgres be scheduled for actions like this? Or will I need to have the php file schedule the action itself? Basically, I need to know if postgres can be scheduled, and if it can open/execute files on a server. Thankyou, I hope this was written in an understandable way... -Dylan
"Dylan Fogarty-MacDonald" <dylan.fm@gmail.com> writes: > Can postgres be scheduled for actions like this? Or will I need to > have the php file schedule the action itself? Postgres has no built-in job scheduling facility --- our standard answer is "use cron, we see no need to reinvent that wheel". If PHP offers something comparable, and you're more familiar with it than with cron, by all means use PHP. regards, tom lane
Dylan Fogarty-MacDonald wrote: > Hi, > > I have a client comprising of a group of car dealerships. Thier > accounting software uploads via ftp a tab delimited file of their > updated stock whenever the stock is updated. > > What I need to do is execute a php scipt that imports data from the > file into the database at a regular interval, ie. every hour. > > Does postgres have an ability handle this in a different way, maybe by > removing the need to use php at all? > > Can postgres be scheduled for actions like this? Or will I need to > have the php file schedule the action itself? > > Basically, I need to know if postgres can be scheduled, and if it can > open/execute files on a server. > > Thankyou, > I hope this was written in an understandable way... > > -Dylan Dylan, We load files that are sent to us randomly. We did it by creating a shell script that among other things checks to see if a copy of itself is already running and pipes data into the sql COPY command. We also created a cron entry that launches the shell script every minute. HTH -- Kind Regards, Keith
Keith Worthington wrote: > Dylan Fogarty-MacDonald wrote: >> Hi, >> >> I have a client comprising of a group of car dealerships. Thier >> accounting software uploads via ftp a tab delimited file of their >> updated stock whenever the stock is updated. >> >> What I need to do is execute a php scipt that imports data from the >> file into the database at a regular interval, ie. every hour. >> >> Does postgres have an ability handle this in a different way, maybe by >> removing the need to use php at all? >> >> Can postgres be scheduled for actions like this? Or will I need to >> have the php file schedule the action itself? >> >> Basically, I need to know if postgres can be scheduled, and if it can >> open/execute files on a server. >> >> Thankyou, >> I hope this was written in an understandable way... >> >> -Dylan > > Dylan, > > We load files that are sent to us randomly. We did it by creating a > shell script that among other things checks to see if a copy of itself > is already running and pipes data into the sql COPY command. We also > created a cron entry that launches the shell script every minute. > > HTH > For simply importing data, psql will work just fine. One thing to be aware of is that if you are using a cron-activated process to check for files the process may, depending on the server and your script, see the file as available when it is still being uploaded and eventually you may end up triggereing a process that fails or only imports part of the data. I've found pure-ftpd to be an excellent and secure FTP server and it has a feature that allows you to trigger a process whenever a file upload completes so you don't have to wait for cron to pick it up and you don't worry about in-progress uploads. Oh, and to answer the original question, no, PostgreSQL does not have its own scheduler - that's the job of cron (or your OS's equivalent). Cheers, Steve