Rafi Cohen wrote:
Dennis, I really appreciate your patience and willingness to help.
Unfortunately, this still did not bring me to the expected solution. I
will give you a small algorithm of what I need to do and I'm sure after
this you'll know how to assist me.
1. I read a .csv file into a linked list of structures.
2. I examine eacch structure one after the other:
Compare the datetime filed of the structure aginst the current date. If
bigger (future), I skup to the next structure.
If smaller or equal, I check if a row with the same id field already
exists in the table:
Select * from tbl where id = id-in-struct.
If no such row exists, I insert a row according to the structure's
field, otherwise, based on another criteria I EITHER UPDATE THE row with
a new value on the second column or delete the row.
3. After this process, I free the structure from the linked list and
move to the next structure.
Because of the last section, I thought I need to make the date
comparison in C, but I may be wrong here.
However, if the comparison is made thru sqlite, how can I know if indeed
an insert, update or delete was processed so that I can free the
structures?
All the sql statements I use above are, of course, prepared statements
which I execute with wqlite3_step for the fields of each structure.
I hope I'm clear, this time.
Rafi,
I think I have followed your description and I agree that you could do
this by iterating in C as you have described. If you want to do that and
all you need is an expedient way to get a date and time string for the
current time for the comparison in step 2 you have a couple of approaches.
One simple way is to let sqlite do it for you by executing a single SQL
statement "select datetime('now')". This will return a ISO date and time
string that you can compare with the concatenated strings that are in
your structure. The other is to use the standard C library routines to
build an ISO formatted date and time string. These two examples are
shown below in a mix of C an pseudo code comments (you have to fill in
the blanks).
Get current date and time from sqlite:
sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, &get_now, NULL);
char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));
// read csv into list of structures
//for each structure in the list
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);
if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table
}
//free the structure
Get current date and time from C library:
char now_datetime[20];
time_t now = time(NULL);
struct tm *now_tm = localtime(&now);
sprintf(now_datetime, "%4d-%02d-%02d %02d:%02d:%02d",
now_tm->tm_year + 1900, now_tm->tm_mon + 1, now_tm->tm_mday,
now_tm->tm_hour, now_tm->tm_min, now_tm->tm_sec);
// read csv into list of structures
//for each structure in the list
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);
if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table
}
//free the structure
Both of these approaches use the fact that ISO format date and time
strings can be compared using a normal string comparison.
I think another approach might be worth considering though. Instead of
reading your csv data into a list of structures, import it into an
sqlite table. You could use the sqlite shell to do this, or you could
copy the code from the import routine in the sqlite shell directly into
your application (it's free open source code). With your csv records in
a temporary table you can do the manipulations in SQL.
// read csv into temporary table csv
system("sqlite3 mydb \"create temp table csv(...);.import myfile.csv
csv\"");
// delete the records with future dates (instead of skiping them)
delete from csv
where (date || ' ' || time) > datetime('now');
// split the csv table into new and existing records
create temp table new_csv as
select * from csv where id not in (select id from perm_table);
delete from csv
where id in (select id from new_csv);
// update the existing records in the permanent table
update perm_table
set field2 = (select field2 from csv where csv.id = perm_table.id)
where //update condition is true;
// delete the records that we updated from the csv
delete from csv where //update condition is true;
// delete the remaining records from the permanent table since the
// update condition must have been false for these records
delete from perm_table
where perm_table.id in (select id from csv);
// insert the new records into the permanent table
insert into perm_table select * from new_csv;
// drop temp tables
drop table csv;
drop table new_csv;
Note that all the SQL in this script (after the C system call) can be
executed by a single call to sqlite3_exec() from your C code. It will
execute each SQL statement in the script in turn before returning. When
it returns your table will be updated. In this case all the
manipulations are done in "parallel" by SQL statements rather than
iterating through a loop as you would in C.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
