writing and reading at same time

dideas · 11-10-2006, 07:45 PM

Hi...

i got a file in which i'm writing sql sentences continuosly.
i want each minute a script read that file and execute them, and therefore, delete them, but meanwhile new sentences can be coming again.

how can i do it?
if i read the file and then i empty it, between that 2 process new sentences can be inserted and i would lose them.

if i "block" the file for writing, the process that insert new sentences would give an error... and i would lose sql's also...

help please

kr4m3r · 11-10-2006, 09:38 PM

Perhaps you should rotate files? Have one being writen to and then being read, after the read file is read, start writing to it and read from the other.

If you don't want to keep the sql statments around then you could just write and read from a buffer.

dideas · 11-11-2006, 03:46 AM

Hi:

1) if i rotate files, how can i do it ? the problem is that while i do something with the file (emptying it, storing or rotating it, ect...) new sentences can be coming in that "short" space of time...

2) in that case (don't need to store them)... tell me about the buffer... i do not know that option...

tip: the reason about executing that script (reading the sql sentences) each minute is to save time opening/closeing mysql connection, because it's a huge load of sentences.

thanx

kr4m3r · 11-11-2006, 04:04 AM

Well, if you allocated a space in memory you could write to it until 1 minute has passed, then copy the contents to another location in memory, clear the first buffer, and then, on another thread if you like, execute them. That should be fast enough to retain all of your sql infomation.

To do that you would probably have to use a programming language instead of scripting.

dideas · 11-11-2006, 04:21 AM

yeah, i'm doing in C... do you know any example i can see ?
any tutorial of this ?

i'd appreciate it !!!

Although... i'm wondering... between your steps "then copy the contents to another location in memory" and "clear the first buffer" , if other sentences goes to the buffer to be written... would they be lost ?

jlinkels · 11-11-2006, 07:00 AM

Well, the pure nature of a file is such that it is a bit difficult what you want. Isn't possible for the program that generates the SQL statements to redirect the output to another progran?

In that you could simply write a piece of C which read from stdin, puts statements in a ring buffer, and read out the ring buffer periodically.

jlinkels

Wim Sturkenboom · 11-11-2006, 07:06 AM

As there are two independent processes, you have to implement a synchronisation mechanism.

For files, you can look at file locking (I don't have man pages available on this machine to check the exact usage and terms (try man flock and man fcntl)). I used it before for a C program, I don't know if it can be done with a shell script. If the latter is the case, it can set a lock on the file so your C-program can't write to it till the lock is released.

dideas · 11-11-2006, 07:46 AM

Wim Sturkenboom: if i do that, while locking, the program that writes new sql sentences to that file couldn't do it, and i would lose sql queries.

--------

jlinkels:

Quote:

Isn't possible for the program that generates the SQL statements to redirect the output to another progran?

Yes, it's possible...

Quote:

In that you could simply write a piece of C which read from stdin, puts statements in a ring buffer, and read out the ring buffer periodically.

hmm, i do not understand that part exactly... ring buffer ¿?

please, explain me, ty in advance !

dideas · 11-11-2006, 11:47 AM

is this ?
http://en.wikipedia.org/wiki/Ring_Buffer
http://elonen.iki.fi/code/misc-notes...fer/index.html

what i do not understand is how to implement in my case

kr4m3r · 11-11-2006, 03:34 PM

I beleive a circular buffer could look something like this:

Code:

char * pPointer, pRead, pWrite = (char *) malloc(27);

Writing:

Code:

while (!feof())
{
  while (pWrite == pRead); // Buffer is full
  *pWrite = fgetc(stdin);
  pWrite = pPointer + (((pWrite - pPointer) + 1) % sizeof(pPointer));
}

Reading:

Code:

while (true)
{
  while (pRead == pWrite) if (feof(stdin)) break; // Buffer is empty
  fprintf(stderr,"%c",*pRead);
  pRead = pPointer + (((pRead - pPointer) + 1) % sizeof(pPointer));
}

Both of these loops would be running at the same time, and of course, you would be reading in a sql statment and writing it into the buffer, and instead of printing out the letters you would be executing the statments.

I would think that a linear buffer would be more appropriate for your case, unless you want to proccess sql statments as they come in.

Sorry, now I have a question:
How do you create threads in linux?

dideas · 11-11-2006, 07:27 PM

Quote:

Originally Posted by kr4m3r

I think that a linear buffer would be more appropriate for your case, unless you want to proccess sql statments as they come in.

No, precisely... i'm doing all this stuff because i want to execute sql's in groups of "1 minute", i mean, to execute all the "INSERT's" of the last minute, because in that way i "save" time opening/closing mysql connection just 1 time per minute.

That's because i'm looking a way to "buffer" sql's and process one time each minute, but that "buffer" is always receiving new stuff, that's because i sill have the problem

Quote:

Originally Posted by kr4m3r

Sorry, now I have a question:
How do you create threads in linux?

I do not what u're talking about... threads? i don't know!

kr4m3r · 11-12-2006, 01:08 AM

You could read all of the statments one at a time and execute them one at a time until there are no more, and do that again in one minute.

If you have to read them all at once and you dont know the total size then you will just have to use a large linear buffer.

If you were using a file on an ext2 file system you would be able to store 4GB of sql data. So if you are expecting a LARGE amount of data files would probly be a better idea.

How are you reading the sql data into your c program?

dideas · 11-12-2006, 04:13 AM

Not reading them yet, that's i'm looking for a nice way here

Yes, i want to load all data from "the last minute" and execute them... How can i do that "linear" buffer then?

I'm not expecting too much size, because the data in the buffer is tiny "compressed" in a way that i generate the sql's from them, i mean, i do not send all "insert" sql, but just the values ordered, and i create the sql after.

anyway, i need a way to load "the last minute" of that "sql feed", deleting it and meanwhile the feed does not stop to receive more.

Wim Sturkenboom · 11-12-2006, 06:06 AM

Quote:

Originally Posted by dideas

Wim Sturkenboom: if i do that, while locking, the program that writes new sql sentences to that file couldn't do it, and i would lose sql queries.

Sorry, maybe I misunderstood you. I assumed that the program that is writing to the file is written by you in C and that the program that reads is a script written by you.

If the first is the case, you can modify the code to wait till the lock is cleared and in the meantime buffer the data.

dideas · 11-12-2006, 12:02 PM

let's see... all is written by me in C, that's right

I got a program in C that (after doing stuff from the web page) generates necessary SQL (inserts, updates, deletes, etc...). Now i'm in the point of just print the sql's in stdout...

Now, because it's a huge ammount of sql, i decided to execute them by "groups" instead executing one by one (changing the printf by mysql_query function :P )

I want to do it in this way because i will optimize and get more performance with sql statements doing by groups... so it will be just one connection, a lot of queries nice optimized, and then close connection.

I decided to do it each minute, so in any way i have to save that sql's in a file, buffer, whatever, to read it with a another program (in C by me, with your help

). The problem as i described is that while i'm reading and emptying that sql's from the "place", the first program is still writing.

that's the point :P

any help ?