LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-12-2016, 03:13 AM   #1
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
XBase (DBF) Date-Format


Good morning.

Can someone explain the difference in the storage-formats for calendar-dates in SQLite and XBase DBF?

I am currently writing a converter sqlite2dbase and use at least one library which does not handle dates. Something is working alright in LibreOffice Calc when I do not convert the numerical (Integer-) values of the SQLite dates, but in other contexts, these dates are invalid, or the numbers are...

This does not only look like I knew nothing.
 
Old 11-12-2016, 05:13 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
Not sure I understand where LO Calc comes into this, but if you export a date from both databases can you not simply compare the 2 to see the differences?
 
Old 11-12-2016, 06:49 AM   #3
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Quote:
Originally Posted by grail View Post
Not sure I understand where LO Calc comes into this, but if you export a date from both databases can you not simply compare the 2 to see the differences?
I can and it tells me that they are different. The date from SQlite corresponds to a big number, the one from a DBF-file is smaller. I guessed that one uses the Unix epoch, the other the Lotus epoch but this does not explain the difference. Unfortunately, I face another problem upon converting SQLite to DBF, when I encounter some specific calendar dates:
Quote:
/[path]/prog/sqlite2dbf/lib/sqlite2dbf.rb:101:in `write_integer_attribute': integer 1431595627281000 too big to convert to `int' (RangeError)
1431595627281000 is a date from SQLite (places.sqlite, something from the firefox directory).

Dates from the cookies.sqlite (also firefox) are for example 1478478127, 1541334209, etc.
LibreOffice Calc does not show them as valid dates, but at least tries to format them correctly. For example 19/09/-17479, 02/04/-3277

I still must convert the values to a dBase date format, change the endianness or something, but have no idea, how to procede.

Last edited by Michael Uplawski; 11-12-2016 at 07:00 AM.
 
Old 11-12-2016, 07:15 AM   #4
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Posts: 942

Rep: Reputation: 216Reputation: 216Reputation: 216
In xBase the date is always YYYYMMDD irrespective of the display format.

dBase format.(AFAIR)
First 16 bytes - file header.
Then 16 bytes for each field descriptor.
Then the records
--- For each record, add a byte for the delete mark
Finally an ending <ctrl+z>

Memo field data is stored separately. Maybe the size in the main file was 10 bytes.
OK

Last edited by AnanthaP; 11-12-2016 at 07:29 AM.
 
1 members found this post helpful.
Old 11-12-2016, 07:25 AM   #5
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
Maybe the trick is to use the gem to extract the data from sqlite and then convert it to YYYYMMDD HHSS type of format and then upload that to the DBF? If you have included both gems for each DB
this should be a relatively simple process.
 
1 members found this post helpful.
Old 11-12-2016, 09:10 AM   #6
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Smile

Quote:
Originally Posted by grail View Post
Maybe the trick is to use the gem to extract the data from sqlite and then convert it to YYYYMMDD HHSS type of format and then upload that to the DBF? If you have included both gems for each DB
this should be a relatively simple process.
I use SQlite3 and SHP for the conversion. SHP does not support date-formats and the DBF gem would write nothing at all.
Thank you for your hints, Grail and AnanthaP. I will try that and come back with a possible result.

Here is a preliminary first release of the sqlite2dbf program: https://rubygems.org/gems/sqlite2dbf
Code:
user@machine:~/prog/sqlite2dbf/bin$ export LANG=en_EN.UTF-8
user@machine:~/prog/sqlite2dbf/bin$ ./sqlite2dbf -h

Usage:	sqlite2dbf -s [sqlite-file] [options]
	or sqlite2dbf [Common options]

Specific options:
    -s [FILE.sqlite],                SQLite-file to read.
        --source/sqlite-file
    -t [FILE(0...n).dbf],            Name for the dBase-files (1 per table) to be written.
        --dBase-files

Common options:
    -o, --orig                       Use the table-name as file-name for the DBF-result
    -d, --debug                      Show debug-messages
    -h, --help                       Show this message
    -v, --version                    Show version and program information
A call in the German or French locale uses different parameters, -q in German is -s in English (source).

Code:
user@machine:~/prog/sqlite2dbf/bin$ ./sqlite2dbf -q ../test/cookies.sqlite -d|more
SQLite2DBF: DEBUG 16-14-41: options are: #<OpenStruct debug=true, source="../test/cookies.sqlite">
SQLite2DBF: DEBUG 16-14-41: will transform ../test/cookies.sqlite to  ../test/cookies.dbf
SQLite2DBF: DEBUG 16-14-41: sqlite contains 1 table(s): moz_cookies
SQLite2DBF: DEBUG 16-14-41: moz_cookies: {"cid"=>0, "name"=>"id", "type"=>"INTEGER", "notnull"=>0, "dflt_value"=>nil, "pk"=>1}{"cid"=>1, "name"=>"name", "typ
e"=>"TEXT", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>2, "name"=>"value", "type"=>"TEXT", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>3, "name"=>
"host", "type"=>"TEXT", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>4, "name"=>"path", "type"=>"TEXT", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>
5, "name"=>"expiry", "type"=>"INTEGER", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>6, "name"=>"lastAccessed", "type"=>"INTEGER", "notnull"=>0, "dflt_va
lue"=>nil, "pk"=>0}{"cid"=>7, "name"=>"isSecure", "type"=>"INTEGER", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}{"cid"=>8, "name"=>"isHttpOnly", "type"=>"INTEG
ER", "notnull"=>0, "dflt_value"=>nil, "pk"=>0}
SQLite2DBF: DEBUG 16-14-41: dbf will be ../test/cookies0.dbf
SQLite2DBF: DEBUG 16-14-41: row is [10, "is_human", "true", ".getsatisfaction.com", "/", 1326829414, nil, 0, 0]
SQLite2DBF: DEBUG 16-14-41: field is id, svalue is 10, type is INTEGER
(...)
Multi-table databases are converted, too.

The Exception is caught and null-values replace non-available fields. The dates are thus either not converted or omitted.., I guess. But this will hopefully be rectified in a next release. I am already quite content with the result. The task “sounds” so complicated but turns out to be feasible.

Last edited by Michael Uplawski; 11-12-2016 at 09:23 AM. Reason: m
 
Old 11-12-2016, 10:44 AM   #7
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
More than I wanted to know about SQLite-Dates:
https://www.tutorialspoint.com/sqlit...data_types.htm
https://www.tutorialspoint.com/sqlit..._date_time.htm

In SQLite Text, Real or Integer-fields may contain calendar-dates (and times) and two different epochs are possible. This probably means, that the user of my converter must know which field is a date and also name the epoch or just check in case of a Real type. Else I will have too try for agglomerations of 'reasonable dates' in any column to determine a probable date-field...

Is it just me, or has again something been artificially complicated for lack of foresight? Like the missing char data-type in Ruby, that they had felt necessary to add, later.

Last edited by Michael Uplawski; 11-12-2016 at 10:51 AM.
 
Old 11-12-2016, 11:31 AM   #8
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
Just thought this might be something you might be interested in :- https://github.com/bbatsov/ruby-style-guide

Haven't had a chance to test the converter yet but will let you know
 
Old 11-12-2016, 11:50 AM   #9
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
ummm ... not to be picky, after the gem is installed, should I be able to run the file from bin directory as you have demonstrated?

I get the following when I do:
Code:
$ ./bin/sqlite2dbf -s /rails/udemy/timetracker/db/development.sqlite3
./bin/sqlite2dbf:24:in `require_relative': cannot load such file -- /.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.1/lib/sqlite2dbf (LoadError)
	from ./bin/sqlite2dbf:24:in `<main>'
 
Old 11-13-2016, 02:43 AM   #10
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Had to push two other versions with fixes for much the same kind of problem. I am on a different computer now, only to test if the downloaded gem will finally do the job.

SORRY. In the meantime, I am preparing a new version anyway, which lets the user name fields with calendar-dates. But I will let you know, probably today, if the available gem is functional. And the Internet-connection is crappy.

Last edited by Michael Uplawski; 11-13-2016 at 02:46 AM.
 
Old 11-13-2016, 02:54 AM   #11
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Quote:
Originally Posted by grail View Post
Just thought this might be something you might be interested in :- https://github.com/bbatsov/ruby-style-guide
Thanks for the style-guide. I tend to dislike it but have to re-read it at occasions.
 
Old 11-13-2016, 01:55 PM   #12
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Version 0.1.6 appears to be functional with Ruby 2.3+.

And... version 0.1.7 provides two new options --dates for date/datetime-values and --time for timestamps. I also handle BLOBS from SQLite as text in dBase because do not see an alternative.

Code:
michael@drusus:~$ sqlite2dbf -s /home/example_user/.local/share/zeitgeist/activity.sqlite -t /tmp/test --time timestamp -d
(...)
SQLite2DBF: DEBUG 23-12-50: row is [1, 1340560004000, 1, 1, 1, "", 2, 2, 2, 1, 1, 1, 4, nil, 2]
SQLite2DBF: DEBUG 23-12-50: field is id, svalue is 1, type is INTEGER
SQLite2DBF: DEBUG 23-12-50: field is timestamp, svalue is 1340560004000, type is INTEGER
SQLite2DBF: DEBUG 23-12-50: conv_time is 130712
SQLite2DBF: DEBUG 23-12-50: field is interpretation, svalue is 1, type is INTEGER
SQLite2DBF: DEBUG 23-12-50: field is manifestation, svalue is 1, type is INTEGER
SQLite2DBF: DEBUG 23-12-50: field is actor, svalue is 1, type is INTEGER
And the new usage message:

Code:
michael@drusus:~$ export LANG=en_EN.UTF-8

michael@drusus:~$ sqlite2dbf -h
ArgParser: DEBUG 23-16-10: ArgParser reading logging-configuration from /usr/lib/ruby/gems/2.4.0/gems/sqlite2dbf-0.1.7/lib/log.conf
ArgParser: DEBUG 23-16-10: level is 0

Usage:	sqlite2dbf -s [sqlite-file] [options]
	or sqlite2dbf [Common options]

Specific options:
    -s, --source [FILE.sqlite]       SQLite-file to read.
    -t, --target [FILE(0...n).dbf]   Name for the dBase-files (1 per table) to be written.
        --time[list]                A list of space-separated fields (table-columns) which shall be handled as timestamp values.
        --dates[list]               A list of space-separated fields (table-columns) which shall be handled as date-time values.
    -o, --orig                       Use the table-name as file-name for the DBF-result

Common options:
    -d, --debug                      Show debug-messages
    -h, --help                       Show this message
    -v, --version                    Show version and program information

Last edited by Michael Uplawski; 11-13-2016 at 04:18 PM.
 
Old 11-14-2016, 01:11 AM   #13
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
Some more feedback information for you:
Code:
$ ./sqlite2dbf -s ~/progs/ruby/rails/rtmh/sample_app/db/development.sqlite3 -t ~/tmp -d
ArgParser: DEBUG 15-3-38: ArgParser reading logging-configuration from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/log.conf
ArgParser: DEBUG 15-3-38: level is 0
SQLite2DBF: DEBUG 15-3-38: options are: #<OpenStruct debug=true, table_name=false, source="/home/grail/progs/ruby/rails/rtmh/sample_app/db/development.sqlite3", target="/home/grail/tmp">
SQLite2DBF: DEBUG 15-3-38: will transform /home/grail/progs/ruby/rails/rtmh/sample_app/db/development.sqlite3 to  /home/grail/tmp.dbf
SQLite2DBF: DEBUG 15-3-38: sqlite contains 5 table(s): schema_migrations, users, sqlite_sequence, microposts, relationships
SQLite2DBF: DEBUG 15-3-38: schema_migrations: {"cid"=>0, "name"=>"version", "type"=>"varchar", "notnull"=>1, "dflt_value"=>nil, "pk"=>0}
SQLite2DBF: DEBUG 15-3-38: dbf will be /home/grail/tmp0.dbf
SQLite2DBF: DEBUG 15-3-38: field is {"cid"=>0, "name"=>"version", "type"=>"varchar", "notnull"=>1, "dflt_value"=>nil, "pk"=>0}
/home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:110:in `block (3 levels) in transform': undefined method `[]' for nil:NilClass (NoMethodError)
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:104:in `each'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:104:in `each_with_index'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:104:in `block (2 levels) in transform'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:92:in `each'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:92:in `each_with_index'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:92:in `block in transform'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:89:in `initialize'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:89:in `new'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:89:in `transform'
	from /home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:68:in `initialize'
	from ./sqlite2dbf:25:in `new'
	from ./sqlite2dbf:25:in `<main>'
Looking at the code, I think this issue is a missing 'upcase' to match the keys in tmap. If you fix this one I believe there may be others
 
1 members found this post helpful.
Old 11-14-2016, 11:29 AM   #14
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,114

Original Poster
Blog Entries: 29

Rep: Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637Reputation: 637
Quote:
Originally Posted by grail View Post
Some more feedback information for you:
Code:
SQLite2DBF: DEBUG 15-3-38: field is {"cid"=>0, "name"=>"version", "type"=>"varchar", "notnull"=>1, "dflt_value"=>nil, "pk"=>0}
/home/grail/.gem/ruby/2.3.0/gems/sqlite2dbf-0.1.7/lib/sqlite2dbf.rb:110:in `block (3 levels) in transform': undefined method `[]' for nil:NilClass (NoMethodError)
Looking at the code, I think this issue is a missing 'upcase' to match the keys in tmap. If you fix this one I believe there may be others
This is so cool. 8)

Thank you grail for the effort you put into the test. It had not been my idea to occupy forum-members (to this point) like this, but I can think of more situations, than before, where this “utility” can be nice to have.

And this error reminds me that the transform() method could benefit from eliminating the mapping work, altogether. It will only simplify the code, though... and facilitate tracing such errors, as the one you found!

Okay, I hope that I can correct the bug this evening, but am not sure that I will have the time. Tomorrow, latest.

Edit: Done, 0.1.8. More changes: give a path-name with the --orig parameter, so that files can however be written in arbitrary directories.
Edit 2: I do adapt some of the code to the style-guide referenced above by grail, but will not follow all the recommendations given there. Coming from other languages, my coding style is the one that helps *me* develop, first of all.

Last edited by Michael Uplawski; 11-15-2016 at 12:25 AM. Reason: 0.1.8, Frenglish
 
Old 11-18-2016, 03:43 AM   #15
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
Thought I would throw this in here as other question is about options rather than the dates themselves

I did a little testing on my own cookies.sqlite and came up with the following, for each field designated as an 'integer' I performed a strftime on the number to test if the date returned was equal to
19700101 and if so, determined that this was an actual integer and not a date, otherwise it would return an actual date which could then be easily stored in your new format.

I guess this could potentially come unstuck if the creator is actually storing large numbers which when converted may be closer to real dates, but thought I would let you know as a possible alternate solution.
I am a little surprised that a database in such high use these days does not have some way to test a field with an 'isdate' type of function if they choose not to store the data in a format easily identified.

ADDENDUM: Seems I might have jumped the gun. My solution was working but I found that the lastAccessed and creationTime, which I would assume by the names should be dates, both give me very odd date information although the time looks ok, examples:
Code:
lastAccessed has value 1362416667119159 and is a date which is -1413-03-01 13:07:12
creationTime has value 1362416667107908 and is a date which is -1413-03-01 13:07:12

Last edited by grail; 11-18-2016 at 04:06 AM. Reason: :(
 
1 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Converting date feed into different date format keif Programming 5 07-08-2014 08:39 AM
LXer: How to change Thunderbird's date format in Date column LXer Syndicated Linux News 0 07-18-2012 02:11 AM
Date format Bill Jones Linux - Newbie 1 12-23-2006 11:07 AM
date format kalleanka LQ Suggestions & Feedback 9 11-15-2006 11:36 AM
Perl XBase Joins Hans Zilles Programming 0 08-08-2006 11:40 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 08:28 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration