Database Users

[Ruby] SMS Converter (from Pim Backup -> HTML)

I wanted to share a small script I wrote for those who know how to use it. It converts non-binary pim-backup message files to html files.
Usage: ruby <file> <message_file>
Code:
#-sms2html----------------------------------------------------------------#
# ChaosR (<[email protected]>) wrote this file. As long as #
# you do not touch this note and keep this note here you can do whatever #
# you want with this stuff. Also, the author(s) are not responsible for #
# whatever happens using this. #
#-------------------------------------------------------------------------#
require 'jcode' if RUBY_VERSION[0..2] == "1.8"
class SMSConverter
attr_accessor :messages, :raw, :by_folder, :by_name, :by_number
SPEC = [
:msg_id,
:sender_name,
:sender_address,
:sender_address_type,
:prefix,
:subject,
:body,
:body_type,
:folder,
:account,
:msg_class,
:content_length,
:msg_size,
:msg_flags,
:msg_status,
:modify_time,
:delivery_time,
:recipient_nbr,
:recipients,
:attachment_nbr,
:attachments
]
RECIP_SPEC = [
:id,
:name,
:phone,
:var1,
:var2,
:type
]
FOLDERS = {
"\\%MDF1" => "INBOX",
"\\%MDF2" => "OUTBOX",
"\\%MDF3" => "SENT",
"\\%MDF4" => "TRASH",
"\\%MDF5" => "DRAFTS"
}
def initialize(file)
f = File.open(file)
@raw = f.read
f.close
raw2array
clean_messages
sort_messages
end
def raw2array
messages = []
state = { :arg => 0, :escaped => false, :in_string => false }
sms = {}
@raw.each_char do |byte|
arg = SPEC[state[:arg]]
sms[arg] = "" unless sms[arg]
if byte == "\0" or byte == "\r"
next
elsif state[:escaped]
sms[arg] << byte
state[:escaped] = false
elsif state[:in_string]
if byte == "\""
state[:in_string] = false
elsif byte == "\\"
state[:escaped] = true
else
sms[arg] << byte
end
elsif byte == "\\"
state[:escaped] = true
elsif byte == "\""
state[:in_string] = true
elsif byte == ";"
state[:arg] += 1
elsif byte == "\n"
raise "Faulty conversion or corrupt file" if state[:escaped] or state[:in_string] or state[:arg] != 20
messages << sms
sms = {}
state[:arg] = 0
else
sms[arg] << byte
end
end
@messages = messages
end
def clean_messages
@messages.map! do |sms|
sms[:modify_time] = Time.local( *sms[:modify_time].split(",") )
unless sms[:delivery_time] == ""
sms[:delivery_time] = Time.local( *sms[:delivery_time].split(",") )
else
sms[:delivery_time] = sms[:modify_time]
end
sms[:recipient_nbr] = sms[:recipient_nbr].to_i
sms[:body_type] = sms[:body_type].to_i
sms[:msg_flags] = sms[:msg_flags].to_i
sms[:msg_status] = sms[:msg_status].to_i
sms[:attachment_nbr] = sms[:attachment_nbr].to_i
sms[:content_length] = sms[:content_length].to_i
sms[:msg_size] = sms[:msg_size].to_i
sms[:folder] = FOLDERS[sms[:folder]]
if sms[:recipient_nbr] > 0
recipients = {}
sms[:recipients].split(";").each_with_index { |var, index| recipients[RECIP_SPEC[index]] = var }
recipients[:id] = recipients[:id].to_i
recipients[:var1] = recipients[:var1].to_i
recipients[:var2] = recipients[:var2].to_i
sms[:recipients] = recipients
end
sms
end
end
def sort_messages
@messages = @messages.sort { |a, b| a[:delivery_time] <=> b[:delivery_time] }
@by_folder = {}
@messages.each do |sms|
@by_folder[sms[:folder]] = [] unless @by_folder[sms[:folder]]
@by_folder[sms[:folder]] << sms
end
@by_name = {}
@messages.each do |sms|
if sms[:recipient_nbr] > 0
if sms[:recipients][:name] != ""
name = sms[:recipients][:name]
else
name = sms[:recipients][:phone]
end
else
if sms[:sender_name] != ""
name = sms[:sender_name]
else
name = get_number_from_address(sms[:sender_address])
end
end
@by_name[name] = [] unless @by_name[name]
@by_name[name] << sms
end
@by_number = {}
@messages.each do |sms|
if sms[:recipient_nbr] > 0
name = sms[:recipients][:phone]
else
name = get_number_from_address(sms[:sender_address])
end
@by_number[name] = [] unless @by_number[name]
@by_number[name] << sms
end
end
def get_number_from_address(address)
if address =~ /^(\+?\d+)$/
return address
elsif address =~ /\<(\+?\d+)\>/
return $1
else
return false
end
end
end
if $0 == __FILE__
require 'fileutils'
dir = "./messages"
my_name = "yourname"
sms = SMSConverter.new(ARGV[0])
FileUtils.mkdir_p(dir)
sms.by_name.each { |name, messages|
filename = File.join(dir, "#{name}.html")
f = File.open(filename, "w")
f << "<html><body>"
messages.each { |sms|
sent = sms[:recipient_nbr] > 0
message = sms[:subject]
f << "<b>--- #{sent ? my_name : name} (#{sms[:delivery_time].strftime("%H:%M:%S, %a %d-%b-%Y")}) ---</b><br/>"
f << "<pre>#{message.strip}</pre><br/><br/>"
}
f << "</body></html>"
f.close
}
end

I'm a little new to this, but am really interested.
I installed Ruby and then created your ruby script. I then run from command prompt:
convertsms.rb pim.vol convertedsms.htm
but I get the following error. What have I done wrong?
C:\Users\mjt\Desktop>convertsms.rb pim.vol convertedsms.htm
C:/Users/mjt/Desktop/convertsms.rb:67:in `raw2array': undefined method `each_cha
r' for #<String:0x24ee9d4> (NoMethodError)
from C:/Users/mjt/Desktop/convertsms.rb:57:in `initialize'
from C:/Users/mjt/Desktop/convertsms.rb:199:in `new'
from C:/Users/mjt/Desktop/convertsms.rb:199
I'm just supposed to run it on a copy of my pim.vol correct?
Thanks!

You have to install pimbackup on your phone, make a non-binary backup. Download the backup to your computer, unzip it, and run this script on the msgs_<date>.csm file. This script will then create a folder called messages and store all the messages per user in it.

ChaosR said:
You have to install pimbackup on your phone, make a non-binary backup. Download the backup to your computer, unzip it, and run this script on the msgs_<date>.csm file. This script will then create a folder called messages and store all the messages per user in it.
Click to expand...
Click to collapse
Ok, I installed pim backup backed up the messages, copied the file to my computer, extracted the csm file and the following happened when I tried to run it.
C:\Users\mjt\Desktop>ruby convertsms.rb msgs.csm
convertsms.rb:67:in `raw2array': undefined method `each_char' for #<String:0x33e
94c> (NoMethodError)
from convertsms.rb:57:in `initialize'
from convertsms.rb:199:in `new'
from convertsms.rb:199
Thanks!

Heh, it seems on Windows you need to include jcode for each_char to work. Put the new code in. Cheers.

how to convert the binary backup to a non binary backup? other than restoring and backing up.

Relaying SMS
Would it be possible to relay the SMS to another phone when backing up, perhaps as a transfer operation, or by sending SMS via a webservice?

Sip Registration on Android

Have anyone used Jain Sip to do a Sip registration on Android?
I'm currently using Jain Sip on Android and I'm trying to get a SIP registration working.
I can put the registration SIP message together ok but after sending the message it seems to just get sent back to my application and my applications processRequest() method is run.
Here is the code I'm using :
Code:
public void init(TextView tv) throws Exception {
SipFactory sipFactory = null;
sipStack = null;
sipFactory = SipFactory.getInstance();
sipFactory.setPathName("gov.nist");
Properties properties = new Properties();
properties.setProperty("javax.sip.OUTBOUND_PROXY", getLocalIpAddress()+":8002" + "/"
+ ListeningPoint.UDP);
properties.setProperty("javax.sip.STACK_NAME", "Sip Test");
// Create SipStack object
sipStack = sipFactory.createSipStack(properties);
tv.setText("sipStack = " + sipStack);
headerFactory = sipFactory.createHeaderFactory();
addressFactory = sipFactory.createAddressFactory();
messageFactory = sipFactory.createMessageFactory();
lp = sipStack.createListeningPoint(getLocalIpAddress(),
8002, ListeningPoint.UDP);
sipProvider = sipStack.createSipProvider(lp);
sipOnOffFlag = true;
tv.append("\n jain sip stack started on " + getLocalIpAddress() + ":" + myPort + "/" + ListeningPoint.UDP);
sipProvider.addSipListener(this);
String fromName = "019078020";
String fromSipAddress = "216.234.148.28";
String fromDisplayName = "Donal";
String toSipAddress = "216.234.148.28";
String toUser = "16784732970";
String toDisplayName = "Server";
// create >From Header
SipURI fromAddress = addressFactory.createSipURI(fromName,
getLocalIpAddress());
Address fromNameAddress = addressFactory.createAddress(fromAddress);
fromNameAddress.setDisplayName(fromDisplayName);
FromHeader fromHeader = headerFactory.createFromHeader(
fromNameAddress, null);
// create To Header
SipURI toAddress = addressFactory
.createSipURI(toUser, toSipAddress);
Address toNameAddress = addressFactory.createAddress(toAddress);
toNameAddress.setDisplayName(toDisplayName);
ToHeader toHeader = headerFactory.createToHeader(toNameAddress,
null);
// create Request URI
SipURI requestURI = addressFactory.createSipURI(toUser,
"216.234.148.28");
// Create ViaHeaders
List<ViaHeader> viaHeaders = new ArrayList<ViaHeader>();
String ipAddress = lp.getIPAddress();
ViaHeader viaHeader = headerFactory.createViaHeader(ipAddress,
lp.getPort(),
lp.getTransport(), null);
// add via headers
viaHeaders.add(viaHeader);
// Create ContentTypeHeader
ContentTypeHeader contentTypeHeader = headerFactory
.createContentTypeHeader("application", "sdp");
// Create a new CallId header
CallIdHeader callIdHeader = sipProvider.getNewCallId();
// Create a new Cseq header
CSeqHeader cSeqHeader = headerFactory.createCSeqHeader(1L,
Request.REGISTER);
// Create a new MaxForwardsHeader
MaxForwardsHeader maxForwards = headerFactory
.createMaxForwardsHeader(70);
// Create the request.
Request request = messageFactory.createRequest(requestURI,
Request.REGISTER, callIdHeader, cSeqHeader, fromHeader,
toHeader, viaHeaders, maxForwards);
// Create contact headers
SipURI contactUrl = addressFactory.createSipURI(fromName, getLocalIpAddress());
contactUrl.setPort(8002);
contactUrl.setLrParam();
// Create the contact name address.
SipURI contactURI = addressFactory.createSipURI(fromName, getLocalIpAddress());
contactURI.setPort(sipProvider.getListeningPoint(lp.getTransport())
.getPort());
Address contactAddress = addressFactory.createAddress(contactURI);
// Add the contact address.
contactAddress.setDisplayName(fromName);
contactHeader = headerFactory.createContactHeader(contactAddress);
request.addHeader(contactHeader);
// You can add extension headers of your own making
// to the outgoing SIP request.
// Add the extension header.
Header extensionHeader = headerFactory.createHeader("Expires",
"0");
request.addHeader(extensionHeader);
Log.d("SIP", "" + request.toString());
// Create the client transaction.
registerTid = sipProvider.getNewClientTransaction(request);
// send the request out.
registerTid.sendRequest();
dialog = registerTid.getDialog();
}
So the message gets built ok but when sendRequest() is run it doesn't appear to get sent to the server but rather back to my application and the applications processRequest method is run.
Should I be doing something extra with inviteTid or the dialog?
Do I need to create a socket or something to sent the request out?

Scratch that, got it working

Can the name of internal flashdrive programmatically found?

Hi,
I want to get out via a MortScript-routine the folder name of the internal flashdrive, e.x. \My Flash Drive. I wrote a routine that looks for all installed drives that are FAT/EXFAT formatted, but that down't help:
Code:
#TestDrivesFATFS.mscr
###########################################################################
#Searches all installed FATFS/exFAT drives except the one this script resides,
#because only those drives MioPocket can be installed on
###########################################################################
#
#Each filesystem is described in registry as
# [HKEY_LOCAL_MACHINE\System\StorageManager\Profiles\<MyProfileName>]
# "DefaultFileSystem"="<MyFileSystem>"
# "Folder"="<MyFolderName>"
Local()
#Obligatory coding as to be found in some MioPocket's scripts
Drive = "\" & Part(SystemPath("ScriptPath"), "\", 2)
#Test starts here
ExcludeDrive = Substr(Drive, 2)
DrivesFATFS = Array()
RegKey = "System\StorageManager\Profiles"
Idx = 0
#Walk the chain of profiles
ForEach variable In RegSubkeys("HKLM", RegKey)
ForEach value, contents In Regvalues("HKLM", RegKey & "\" & variable)
#Some newer CE devices know of exFAT format
If((value eq "DefaultFileSystem") AND (contents ne "FATFS") AND (contents ne "EXFAT"))
Break
EndIf
If((value eq "Folder") AND (contents ne ExcludeDrive))
Idx += 1
DrivesFATFS[Idx] = "\" & contents
EndIf
EndForEach
EndForEach
If(Debug)
ForEach drv in Array(DrivesFATFS)
Message(drv)
EndForeach
EndIf
Any help appriciated. TIA

It can be found in the Registry (at least in my WinMo 6.1... don't know if it's the same way in CE releases or other brands). Look in "HKLM\System\StorageManager\INAND" (or MMC instead of INAND...). The drive name is the "folder" string value).

Thanks for your reply. What I'm searching for is a hint how I can determine per software (MortScript), already having got ( with a piece of code as postet above ) a list of drive-names, which of those is the Storage Card and which is the Flash Drive, not by default knowing of the drive-names, because those differ from device to device.
Running the code above on my device (Medion PNA with CE 5) returns among others the names \Storage Card and/or \My Flash Disk, but this doesn't really help me a lot.

Thanks to all for your interest.
=> SOLVED IT BY MYSELF!
If you are interested in how I did it, here the code to detect the name of SD(HC), MMC, etc card:
Code:
ErrorLevel("warn")
sd = @FindSDDrives()
ForEach drv in Array(sd)
Message(drv)
EndForEach
Sub FindSDDrives()
Local()
SDProfiles = Array()
SDDrives = Array()
SDDrives2 = Array()
#All CE filesystem profiles related to Storage Cards are listet under
#[HKEY_LOCAL_MACHINE\Drivers\SDCARD\ClientDrivers\Class\******]
#E.x. MMC_Class, SDMemory_Class
idx = 0
RegKey = "Drivers\SDCARD\ClientDrivers\Class"
ForEach sdcard In RegSubkeys("HKLM", RegKey)
ForEach value, contents In RegValues("HKLM", RegKey & "\" & sdcard)
If(value eq "Profile")
idx += 1
SDProfiles[idx] = contents
EndIf
EndForEach
EndForEach
#Each CE filesystem is described in registry as
# [HKEY_LOCAL_MACHINE\System\StorageManager\Profiles\<MyProfileName>]
# "DefaultFileSystem"="<MyFileSystem>"
# "Folder"="<MyFolderName>"
# "Name"="<MyName>
idx = 0
RegKey = "System\StorageManager\Profiles"
ForEach profile in Array(SDProfiles)
ForEach value, contents In RegValues("HKLM", RegKey & "\" & profile)
If(value eq "Folder")
idx += 1
SDDrives[idx] = "\" & contents
EndIf
EndForeach
EndForEach
Clear(SDProfiles)
#Remove duplicates
idx = 0
idx2 = 0
prevdrv = ""
ForEach drv in Array(SDDrives)
idx += 1
If(SDDrives[idx] ne prevdrv)
idx2 += 1
prevdrv = SDDrives[idx]
SDDrives2[idx2] = prevdrv
EndIf
EndForEach
Clear(SDDrives)
Return(SDDrives2)
EndSub

Thanks
jwoegerbauer said:
Thanks to all for your interest.
=> SOLVED IT BY MYSELF!
If you are interested in how I did it, here the code to detect the name of SD(HC), MMC, etc card:
Click to expand...
Click to collapse
Usefull for XDA_UC build in like EnergyROMs. Don't try the German ROMs because of copying files from "Storage Crad" vs. "Speicherkarte" . Now thats solved.
Thanks.

[Q] Databases again

Currently i can create a database with following lines:
Code:
final String MY_DB_NAME = "Test";
final String MY_DB_TABLE = "Autos";
SQLiteDatabase myDB = null;
myDB = this.openOrCreateDatabase(MY_DB_NAME, MODE_PRIVATE, null);
myDB.execSQL("CREATE TABLE IF NOT EXISTS " + MY_DB_TABLE + " (_id integer primary key autoincrement, name varchar(100), pos int(4))");
myDB.execSQL("INSERT INTO " + MY_DB_TABLE + " (name)" + " VALUES ('Audi TT')");
myDB.execSQL("INSERT INTO " + MY_DB_TABLE + " (name)" + " VALUES ('Honda Civic');");
Now this creates only name.
But i need to add a value too, what must i change to make it possible?

The table only has 3 fields..the autoincrement _id field which the database handles.
Then you have name and pos.
Not sure what you want to do, but to insert data to both columns, it's:
insert into autos ("name", "pos") values ("Chevy Camaro", 1);
If you need more information, you'll have to recreate (or alter) the table to add columns.

Currently i can create a database with the following lines:
Code:
private void onCreateDB () {
final String MY_DB_NAME = "settings";
SQLiteDatabase myDB = this.openOrCreateDatabase(MY_DB_NAME, MODE_PRIVATE, null);
Toast.makeText(set.this, "PATH: " + myDB.getPath(), Toast.LENGTH_SHORT).show();
myDB.execSQL("CREATE TABLE IF NOT EXISTS system (_id integer primary key autoincrement, name varchar(100), value int(4))");
myDB.execSQL("INSERT INTO system (name, value)" + " VALUES ('wifi_http_proxy', 'proxy')");
myDB.execSQL("INSERT INTO system (name, value)" + " VALUES ('wifi_http_port', '3128');");
myDB.close();
}
now, i must replace the entry by the name, not by the id. how can i do it?

ilendemli said:
Currently i can create a database with the following lines:
Code:
private void onCreateDB () {
final String MY_DB_NAME = "settings";
SQLiteDatabase myDB = this.openOrCreateDatabase(MY_DB_NAME, MODE_PRIVATE, null);
Toast.makeText(set.this, "PATH: " + myDB.getPath(), Toast.LENGTH_SHORT).show();
myDB.execSQL("CREATE TABLE IF NOT EXISTS system (_id integer primary key autoincrement, name varchar(100), value int(4))");
myDB.execSQL("INSERT INTO system (name, value)" + " VALUES ('wifi_http_proxy', 'proxy')");
myDB.execSQL("INSERT INTO system (name, value)" + " VALUES ('wifi_http_port', '3128');");
myDB.close();
}
now, i must replace the entry by the name, not by the id. how can i do it?
Click to expand...
Click to collapse
UPDATE system SET value = XX WHERE name = 'xxxx'
Do some googling for SQL. There is TONS of help for SQL out there, and it sounds like your problem isn't Android, it's SQL. There's some great learning resources out there.

i already got it, thx anyways.

Single quote in database

Can't get rid of this error:
android.database.sqlite.SQLiteException: near "s": syntax error: , while compiling:
Select correct from answers where correct = 'Between the airplane's climb angle and the horizon.'
Obviously, it's finding the single quote in ( airplane's ) and considering that the end of the statement.
I've tried:
correct.replaceAll(" ' ", " ''' "); //replace 1 with 3
correct.replaceAll(" ' ", " '' "); // replace 1 with 2
correct.replaceAll(" ' ", " "); // replace 1 with space
(NOTE: the spaces are NOT in the code, I just did that to make it readable)
I have no idea what's going on, IMO, it should work. Maybe I need to try:
String single = "'"; // single '
String double = "''" // double ''
correct.replaceAll(single, double); // ????
Everything I"ve read about sqlite3 is to replace one with two....
TIA,
Roots

\'
\ is the escape character for most languages
so airplane's would be airplane\'s
Also, are you binding your queries with the "question mark" bind?

I'll try the escape and post back later. There are 1,000 rows in the database and I"m pulling a random subset of that, so it's not that often I get one of those situations.
I'm not sure what you mean by "binding with ?" Isn't that what you use for bind variable unknown at runtime? I know my bind variables and just use it in my dbquery. Please enlighten me...always happy to learn something new
Sample code...answerOne would contain the single quote that's killing me
Code:
Cursor c;
c = myDataBase.rawQuery("Select correct from answers where correct = '" + answerOne + "'", null);
if(c.moveToFirst())
answer = "1";
c.close();

binding with question marks should take care of escaping for you.
Basically the question mark is a place holder for a variable in the query.
What you are doing is manually creating the query string. This is considered bad practice these days especially with regards to security. Mostly because it opens up the DB to a SQL injection attack.
So instead of using the rawQuery just use query and you can put a ? in and android will substitute the value for you, all properly escaped:
Code:
String tableName = "answers";
String selectArgs = "correct=[COLOR="Red"]?[/COLOR]";
// if answerOne is string dont need String.valueOf
String[] selectVals = { String.valueOf ( answerOne ) };
String[] columnsProjection= new String[] {"correct" };
Cursor c = db.query(tableName, columnsProjection, selectArgs,selectVals,null);
So in that code the OS will replace the ? in selectArgs with the values in selectVals
This may seem like more writing at first but once you get in the habit it will be easy, reliable and more secure. It also allows you to bind multiple variables to mutiple question marks. It just binds then in the order it gets them.
so something like this:
Code:
String answerOne= "one";
String selectArgs = "correct=? AND age=? AND smiling=?";
String[] selectVals = { answerOne, "21", "yes" };

Ok, I'll try it. There are about 50 different queries in this program...for some reason I just decided to do a rawQuery on this one. I'll change it to "db.query(table name, new String[] {}....yada, yada).
Because, it just crashed and I decided to come back here and check for a solution.
Thank you very much!!!
Roots

Glad to be of help, just remember to hit the thanks booton ya Rooster

Still getting the error
Example: column is in table as text. Say it's equal to:
The driver's last name
Error comes back as "syntax error near 's' when compiling select correct from answers where correct = 'The driver's last name'
That single quote in driver's is killing my SQL.