Ok, I couldnt find the script but heres something started.
On Fri, Aug 22, 2008 at 6:02 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> That script would be great :)
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker
> Sent: Friday, August 22, 2008 1:31 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
>
> At one point I wrote some vbscript to generate a table declaration and
> insert statements for a csv. I might be able to dig it up if you dont
> mind vbscript.
>
> On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
>> I just ran Dennis's test databases through the test application and
>> we're getting similar results:
>> 1k Pages (17.4 MB) used 18102 KB High 20416 KB
>> 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why
>> the high is higher?)
>> My test database however with the same test application produces the
>> following:
>> 1k Pages (7.46 MB) used 22735 KB, High 25138 KB.
>>
>> So it looks my issue could be data related if my test database going
>> through the same app is coming out so large, Dennis's database is
>> expanding to about 101.6% of its original size but mine is expanding
> to
>> 297.6% of its original size. This begs the question is the 3rd party
>> tool (SQLite Analyzer) I'm using to import from an excel file causing
>> this expansion with bad data type choices? And is there any other way
>> to import table structure and contents from xls (or csv) to sqlite?
>>
>> -----Original Message-----
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
>> Sent: Friday, August 22, 2008 7:10 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
>>
>> Brown, Daniel wrote:
>>> 2. And the other thing to try would be if anyone has a fairly meaty
>> test
>>> database they don't mind sharing that I could fling at my test
>>> application to try and rule out the data?
>>>
>>
>> Daniel,
>>
>> I can send you copies of the databases I am using for my testing, both
>> the version with the 1K page size (17.4 MB) and the one with the 4K
> page
>>
>> size (12.2 MB).
>>
>> Where would you like me to send them? The zipped versions are each
> about
>>
>> 1.3 MB in size.
>>
>> Dennis Cote
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Class FieldInfo
Public Name
private m_TextCount
private m_BlobCount
private m_IntCount
private m_FloatCount
private m_DateCount
Public Property Get DataType
dim t, c
t = "NULL"
c = 0
if(m_DateCount > c) then
t = "Date"
c = m_DateCount
end if
if(m_BlobCount > c) then
t = "Binary"
c = m_BlobCount
end if
if(m_FloatCount > c) then
t = "Real"
c = m_FloatCount
end if
if(m_IntCount > c) then
t = "Integer"
c = m_IntCount
end if
if(m_TextCount > c) then
t = "Text"
c = m_TextCount
end if
if count = 0 then
t = "Text"
end if
DataType = t
End Property
Private Sub Class_Initialize
m_TextCount = 0
m_BlobCount = 0
m_IntCount = 0
m_FloatCount = 0
m_DateCount = 0
End Sub
Private Sub Class_Terminate
End Sub
Public Sub ScanForType(data)
if IsDate(data) then
m_DateCount = m_DateCount + 1
elseif(IsNumeric(data)) then
if(instr(data,".") <> -1) then
m_FloatCount = m_FloatCount + 1
else
m_IntCount = m_IntCount+1
end if
else
if(left(data,2) = "0x") then
m_BlobCount = m_BlobCount + 1
else
m_TextCount = m_TextCount + 1
end if
end if
End Sub
Public Sub WriteValue(ts, data)
if data is nothing or UCase(CStr(data)) = "NULL" Then
ts.Write "NULL"
elseif DataType = "Text" then
ts.Write "'" & Replace(CStr(data),"'","''") & "'"
elseif DataType = "Date" then
ts.Write "julianday('" & Replace(CStr(data),"'","''") &
"')"
elseif DataType = "Binary" then
ts.Write "X'" & Right(CStr(data),Len(CStr(data)) -2) &
"'"
elseif DataType = "Integer" Then
ts.Write "cast('" & Replace(CStr(data),"'","''") & "'
as integer)"
elseif DataType = "Real" then
ts.Write "cast('" & Replace(CStr(data),"'","''") & "'
as Real)"
else
ts.Write "NULL"
end if
End Sub
End Class
Class Importer
Private m_Fields
private m_InputFile
private m_FoundPrimaryKey
private m_TableName
Private Sub Class_Initialize
End Sub
Private Sub Class_Terminate
dim i
for i = lbound(m_Fields) to ubound(m_Fields)
set m_Fields(i) = Nothing
next
Set m_Fields = Nothing
End Sub
public sub Analyze(fileName)
m_FoundPrimaryKey = False
m_InputFile = fileName
dim conn, rs, fso, fields(1), i, tableName, fieldCount
set fso = CreateObject("Scripting.FileSystemObject")
set conn = CreateObject("ADODB.Connection")
tableName = "" & fso.GetFileName(fileName) &""
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
m_InputFile & "; Extended Properties=""text;HDR=YES;FMT=Delimited"""
set rs = conn.Execute("Select * from [" & tableName &"]" )
fieldCount = rs.Fields.Count
redim m_Fields(fieldCount)
for i=0 to fieldCount -1
set m_Fields(i) = new FieldInfo
m_Fields(i).Name = rs.Fields(i).Name
next
Do While (not rs.EOF)
For i=0 to fieldCount-1
m_Fields(i+1).ScanForType rs(i).Value
Next
Loop
rs.Close
conn.Close
set fso = nothing
set conn = nothing
end sub
Private Function IsPrimaryKey (index)
if m_FoundPrimaryKey = False and
lcase(right(m_Fields(index).Name,2)) = "id" then
m_FoundPrimaryKey = True
IsPrimaryKey = True
else
IsPrimaryKey = False
end if
end function
public sub WriteDeclaration(ts)
dim i
ts.Write "Create Table """
ts.Write m_TableName
ts.Write """"& vbCrLf & "( "
for i = lbound(m_Fields) to ubound(m_fields)
ts.Write vbTab
ts.Write """"
ts.Write m_Fields(i).Name
ts.Write """ "
ts.Write m_Fields(i).DataType
ts.Write " "
if IsPrimaryKey(i) then
ts.Write " PRIMARY KEY "
end if
if i <> ubound(m_Fields) then
ts.Write ", "
end if
ts.Write vbCrLf
next
ts.Write "); "
end sub
public sub WriteInsert (ts)
dim conn, rs, fso, i, tableName, fieldCount
set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
m_InputFile & "; Extended Properties=""text;HDR=YES;FMT=Delimited"""
set rs = conn.Execute("Select * from [" & tableName &"]")
Do While (not rs.EOF)
ts.Write "Insert into """
ts.Write m_TableName
ts.Write """ ("
for i = lbound(m_Fields) to ubound(m_Fields)
ts.Write """"
ts.Write m_Fields(i).Name
ts.Write """ "
if i <> ubound(m_Fields) then
ts.Write ", "
end if
next
ts.Write ")"
ts.Write " VALUES "
ts.Write "("
for i = lbound(m_Fields) to ubound(m_Fields)
m_Fields(i).WriteValue ts, rs.Fields(i).Value
if i <> ubound(m_Fields) then
ts.Write ", "
end if
next
ts.Write ");"
ts.Write vbCrLf
Loop
end sub
End Class
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
