Skip to content

Instantly share code, notes, and snippets.

@erikmack

erikmack/Importer.vbs

Last active Dec 21, 2015
Embed
What would you like to do?
A macro for LibreOffice base for importing a music database from a spreadsheet (which is wrapped as a database)
REM ***** BASIC *****
Option Explicit
Option VBASupport 1 ' Enables InStrRev()
Sub Main
' Src database is .odb wrapper around .ods Spreadsheet
DIM oSrcContext AS OBJECT
oSrcContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DIM oSrcDataSource AS OBJECT
oSrcDataSource = oSrcContext.getByName("fuucamusic")
DIM oSrcConnection AS OBJECT
oSrcConnection = oSrcDataSource.GetConnection("","")
' Dest database is this one
DIM oDestDataSource AS OBJECT
oDestDataSource = ThisDatabaseDocument.DataSource
DIM oDestConnection AS OBJECT
oDestConnection = oDestDataSource.GetConnection("","")
DIM oSQLStatement AS OBJECT
DIM stSql AS STRING
DIM oQueryResult AS OBJECT
' Drop Destination tables
oSQLStatement = oDestConnection.createStatement()
stSql = "DROP TABLE Artifact if exists"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "DROP TABLE Person if exists"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "DROP TABLE Work if exists"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "DROP TABLE Voicing if exists"
oSQLStatement.execute(stSql)
' Create Destination tables
oSQLStatement = oDestConnection.createStatement()
stSql = "CREATE TABLE Voicing (" & _
"id int generated by default as identity (start with 1), " & _
"frequency int not null, " & _
"vname varchar(60) not null)"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "CREATE TABLE Work (id int generated by default as identity (start with 1), " & _
"title varchar(200) not null, " & _
"composer varchar(200) null, " & _
"arranger varchar(200) null, " & _
"publisher varchar(200) null, " & _
"catalog_no varchar(200) null, " & _
"song_type varchar(200) null, " & _
"solo_required varchar(200) null, " & _
"voicing_id int null, " & _
"instrumentation varchar(200) null, " & _
"text varchar(200) null, " & _
"language varchar(200) null, " & _
"theme varchar(200) null, " & _
"date_performed varchar(200) null, " & _
"major_work boolean default false not null, " & _
"full_score boolean default false not null, " & _
"current_season boolean default false not null, " & _
"location varchar(200) null, " & _
"constraint fk_voicing_id foreign key (voicing_id) references Voicing (id))"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "CREATE TABLE Person (" & _
"id int generated by default as identity (start with 1), " & _
"first_name varchar(60) not null, " & _
"last_name varchar(60) null, " & _
"email varchar(60) null, " & _
"phone varchar(60) null, " & _
"member boolean default true not null)"
oSQLStatement.execute(stSql)
oSQLStatement = oDestConnection.createStatement()
stSql = "CREATE TABLE Artifact (" & _
"work_id int not null, " & _
"copy_id int not null, " & _
"checked_out boolean default false not null, " & _
"person_id int null, " & _
"constraint fk_work_id foreign key (work_id) references Work (id), " & _
"constraint fk_person_id foreign key (person_id) references Person (id), " & _
"constraint pk_work_id_copy_id primary key (work_id,copy_id))"
oSQLStatement.execute(stSql)
' Read the source spreadsheet
oSQLStatement = oSrcConnection.createStatement()
stSql = "SELECT COMPOSER,TITLE,ARRANGER,""# COPIES"",""PUBLISHER / #"",VOICING,TEXT,THEME,LANGUAGE,INSTRUMENT,NOTES,""DATE PERFM"",""MAJOR WK"" FROM Sheet1"
oQueryResult = oSQLStatement.executeQuery(stSql)
DIM sComposer AS STRING
DIM sTitle AS STRING
DIM sArranger AS STRING
DIM sNumCopies AS STRING
DIM sPublisher AS STRING
DIM sVoicing AS STRING
DIM sText AS STRING
DIM sTheme AS STRING
DIM sLanguage AS STRING
DIM sInstrumentation AS STRING
DIM sNotes AS STRING
DIM sDatesPerformed AS STRING
DIM sMajorWork AS STRING
DIM sPublisherShort AS STRING
DIM sCatalogNo AS STRING
DIM iNumCopies AS INTEGER
DIM oInnerQueryResult AS OBJECT
DIM iLastInsertedId AS INTEGER
DIM bIsMajorWork AS BOOLEAN
DIM iPos AS INTEGER
Dim i, j AS INTEGER
DIM sVoicingId AS STRING
DIM iVoicingId, iVoicingFrequency AS INTEGER
i = 0
IF NOT ISNULL(oQueryResult) THEN
WHILE oQueryResult.Next 'AND i < 12
sComposer = Replace(oQueryResult.getString(1),"'","''")
sTitle = Replace(oQueryResult.getString(2),"'","''")
sArranger = Replace(oQueryResult.getString(3),"'","''")
sNumCopies = oQueryResult.getString(4)
sPublisher = Replace(oQueryResult.getString(5),"'","''")
sVoicing = Replace(oQueryResult.getString(6),"'","''")
sText = Replace(oQueryResult.getString(7),"'","''")
sTheme = Replace(oQueryResult.getString(8),"'","''")
sLanguage = Replace(oQueryResult.getString(9),"'","''")
sInstrumentation = Replace(oQueryResult.getString(10),"'","''")
sNotes = Replace(oQueryResult.getString(11),"'","''")
sDatesPerformed = Replace(oQueryResult.getString(12),"'","''")
sMajorWork = oQueryResult.getString(13)
bIsMajorWork = (Len(sMajorWork) > 0)
' Split "Publisher:CatNo" or "Publisher/CatNo" or "Pub/lisher:CatNo"
sPublisherShort = sPublisher
sCatalogNo = ""
iPos = InStrRev(sPublisher,":")
IF iPos > 0 THEN
sPublisherShort = Trim(Mid(sPublisher,1,iPos-1))
sCatalogNo = Trim(Mid(sPublisher,iPos+1))
ELSE
iPos = InStrRev(sPublisher,"/")
IF iPos > 0 THEN
sPublisherShort = Trim(Mid(sPublisher,1,iPos-1))
sCatalogNo = Trim(Mid(sPublisher,iPos+1))
END IF
END IF
' Build the Voicing lookup table
sVoicingId = "NULL"
sVoicing = Trim(sVoicing)
IF Len(sVoicing) > 0 THEN
oSQLStatement = oDestConnection.createStatement()
' For some reason identifiers have to be ALL CAPS here but nowhere else???
stSql = "select ID from VOICING where VNAME='" & sVoicing & "'"
oInnerQueryResult = oSQLStatement.executeQuery(stSql)
IF oInnerQueryResult.Next THEN
iVoicingId = oInnerQueryResult.getInt(1)
' UPDATE
oSQLStatement = oDestConnection.createStatement()
stSql = "update Voicing set frequency=frequency+1 where id=" & iVoicingId
oSQLStatement.execute(stSql)
ELSE
' INSERT
oSQLStatement = oDestConnection.createStatement()
stSql = "insert into Voicing (frequency,vname) values (1,'" & sVoicing & "')"
oSQLStatement.execute(stSql)
' SELECT for iVoicingId
oSQLStatement = oDestConnection.createStatement()
stSql = "call identity()"
oInnerQueryResult = oSQLStatement.executeQuery(stSql)
oInnerQueryResult.Next
iVoicingId = oInnerQueryResult.getInt(1)
END IF
sVoicingId = iVoicingId
END IF
oSQLStatement = oDestConnection.createStatement()
stSql = "insert into Work (" & _
"composer," & _
"title," & _
"arranger," & _
"publisher," & _
"catalog_no," & _
"voicing_id," & _
"instrumentation," & _
"text," & _
"language," & _
"theme," & _
"date_performed," & _
"major_work" & _
") values (" & _
"'" & sComposer & "'," & _
"'" & sTitle & "'," & _
"'" & sArranger & "'," & _
"'" & sPublisherShort & "'," & _
"'" & sCatalogNo & "'," & _
"" & sVoicingId & "," & _
"'" & sInstrumentation & "'," & _
"'" & sText & "'," & _
"'" & sLanguage & "'," & _
"'" & sTheme & "'," & _
"'" & sDatesPerformed & "'," & _
"" & bIsMajorWork & ")"
oSQLStatement.execute(stSql)
' Need the last-inserted ID. In hindsight, this could just be an integer variable.
' skip the query, but this is more 'correct' I guess
oSQLStatement = oDestConnection.createStatement()
stSql = "call identity()"
oInnerQueryResult = oSQLStatement.executeQuery(stSql)
oInnerQueryResult.Next
iLastInsertedId = oInnerQueryResult.getInt(1)
' Eep! CInt has the old atoi behavior, where CInt("5 Dogs") == 5. Oh, well, roll with it...
iNumCopies = CInt(sNumCopies)
FOR j = 1 To iNumCopies
oSQLStatement = oDestConnection.createStatement()
stSql = "insert into Artifact (work_id,copy_id) values (" & iLastInsertedId & "," & j & ")"
oSQLStatement.execute(stSql)
NEXT
'Msgbox(iNumCopies)
i = i + 1
WEND
END IF
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment