Last active
December 21, 2015 16:39
-
-
Save erikmack/6335380 to your computer and use it in GitHub Desktop.
A macro for LibreOffice base for importing a music database from a spreadsheet (which is wrapped as a database)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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