mysql | Motions-Media

-- Copyleft Yarra Valley Software Ltd, Oxford, UK 2003
-- AppleScript mysql interface. 7 October 2003
-- Version Beta 0.3
--
-- This interface is freeware. It can be copied, duplicated used/modified.
-- This header must remain in place. Modified versions must acknowledge 
-- the original indicate where modifications have been made. I would
-- really appreciate if improvements, additions could be passed through me
-- so that I can make them available within one version.
-- http://www.yvs.eu.com
-- ktam@yvs.eu.com

-- If you wish to include this script in your own AppleScript file, copy
-- everything to your script and then uncomment the last line and
-- the line that follows this text.
-- script mysql

property gPathToMySQL : missing value
property gmySQLCommand : missing value
property gTheUser : ""
property gTheUsersPassword : ""
property gTheHost : ""
property gTheDataBase : ""
property gUsePasswordInMyCnfFile : false

-- Determines where mysql is located. Returns true if successful else false.
on SetLocationOfmySQL()
    if gmySQLCommand is not equal to missing value then
        -- try
        -- The command which is likely to cause least problems is get version.
        -- set theResult to do shell script gmySQLCommand & " -V"
        -- return true
        -- end try
        return true
    end if

    try
        -- The command which is likely to cause least problems is get version.
        set theResult to do shell script "mysql -V"
        set gPathToMySQL to ""
    on error
        set mySQLList to {"/usr/local/mysql/bin/", "/Library/MySQL/bin/"}
        set haveFound to false
        repeat with i from 1 to the count of mySQLList
            try
                set theResult to do shell script (item i of mySQLList) & "mysql -V"
                set gPathToMySQL to (item i of mySQLList)
                set haveFound to true
                exit repeat
            end try
        end repeat
        if haveFound is equal to false then
            try
                try
                    set searchResults to do shell script "find /usr/local -name mysql -type f 2>/dev/null"
                on error errorString
                    set searchResults to errorString
                end try
                -- display dialog searchResults
                set tids to AppleScript's text item delimiters
                set AppleScript's text item delimiters to return
                set theResults to text items of searchResults
                set AppleScript's text item delimiters to tids
                set theCommand to "mysql"
                repeat with i from 1 to (the count of theResults)
                    set theCommand to item i of theResults
                    if theCommand contains "mysql" then
                        try
                            -- The following line will throw an error if theCommand is not a valid mysql.
                            set theResult to do shell script theCommand & " -V"
                            -- display dialog "Command found: " & theCommand
                            exit repeat
                        end try
                    end if
                end repeat
                set theResult to do shell script theCommand & " -V"
                set AppleScript's text item delimiters to "/"
                set gPathToMySQL to (text items 1 thru -2 of theCommand) as string
                set AppleScript's text item delimiters to tids
            on error
                try
                    set dialogResult to display dialog "Could not find your installation of mysql, could you please specify the directory that contains mysql" default answer "/Library/MySQL/bin/"
                on error
                    set dialogResult to {button returned:"Cancel"}
                end try
                if the button returned of dialogResult is equal to "OK" then
                    set thePath to the text returned of dialogResult
                    try
                        -- The following line will throw an error if theCommand is not a valid mysql.
                        set theResult to do shell script thePath & "mysql -V"
                        set gPathToMySQL to thePath
                    on error
                        set gPathToMySQL to missing value
                        set gmySQLCommand to missing value
                        return false
                    end try
                end if
            end try
        end if
    end try
    set gmySQLCommand to gPathToMySQL & "mysql"
    return true
end SetLocationOfmySQL

on SetMySQLPath(thePath)
    if thePath is equal to missing value then
        set gPathToMySQL to missing value
        set gmySQLCommand to missing value
    else
        set gPathToMySQL to thePath
        set gmySQLCommand to thePath & "mysql"
    end if
end SetMySQLPath

on GetMySQLPath()
    return gPathToMySQL
end GetMySQLPath

on GetMySQLCommand()
    return gmySQLCommand
end GetMySQLCommand

-- GetmySQLVersion will clear the mysql command and path variables if
-- an error occured when trying to get the version number.
on GetmySQLVersion()
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    try
        set finalResult to do shell script gmySQLCommand & " -V"
    on error
        set gPathToMySQL to missing value
        set gmySQLCommand to missing value
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
        try
            set finalResult to do shell script gmySQLCommand & " -V"
        on error
            set gPathToMySQL to missing value
            set gmySQLCommand to missing value
        end try
    end try
    return finalResult
end GetmySQLVersion

-- Returns false if there is not a valid mysql to run.
on ClearMySQLCommandIfNotValid()
    if GetmySQLVersion() begins with "Error:" then
        return false
    else
        return true
    end if
end ClearMySQLCommandIfNotValid

-- Attempts to redo the command after seeing if we have a valid mysql executable
on RedoMySQLCommand(theCommand)
    set theResult to GetmySQLVersion()
    if theResult begins with "Error" then
        return theResult
    end if
    try
        set theResult to do shell script theCommand
    on error theError
        set theResult to "Error: " & theError
    end try
    return theResult
end RedoMySQLCommand

-- If originalPassword is an empty string then assume that the password
-- has not previously been set.
on SetUserPassword(userName, originalPassword, newPassword)
    set finalResult to "Error: mysql not found, have you installed it?"
    if SetLocationOfmySQL() is equal to false then
        return finalResult
    end if
    set mySQLAdminCommand to gPathToMySQL & "mysqladmin -u " & userName

    if gUsePasswordInMyCnfFile is false then
        set passwordComponent to " --password='" & originalPassword & "'"
    end if
    try
        set theResult to do shell script mySQLAdminCommand & passwordComponent & " password " & quoted form of newPassword
    on error theErrorString
        set theResult to theErrorString
    end try
    return "" & theResult
end SetUserPassword

on SetRootPassword(originalPassword, newPassword)
    return SetUserPassword("root", originalPassword, newPassword)
end SetRootPassword

on SetCurrentUser(userName)
    set gTheUser to userName
end SetCurrentUser

on SetCurrentPassword(usersPassword)
    set gTheUsersPassword to usersPassword
end SetCurrentPassword

on SetCurrentUserAndPassword(userName, usersPassword)
    set gTheUser to userName
    set gTheUsersPassword to usersPassword
end SetCurrentUserAndPassword

on SetTheHost(theHost)
    set gTheHost to theHost
end SetTheHost

-- usePasswordInCnfFile is either true or false
on SetUsePasswordInMyCnfFile(usePasswordInCnfFile)
    set gUsePasswordInMyCnfFile to usePasswordInCnfFile
end SetUsePasswordInMyCnfFile

-- If you are using the .my.cnf file to configure your login for mysql 
-- then do not use this command as it will remove all entries in your 
-- .my.cnf file and replace it with the password entry only.
on SetPasswordInMyCnfFile(thePassword)
    try
        set theCommand to "echo " & quoted form of ("[client]" & (ASCII character 10) & "password=" & thePassword) & " > ~/.my.cnf"
        do shell script theCommand & ";chmod 600 ~/.my.cnf"
    end try
end SetPasswordInMyCnfFile

--private function, should only be called by the handlers in mysql script   
on CreateLogInString(userName, usersPassword, theHost)
    set logInString to ""
    if theHost is not equal to "" then
        set logInString to logInString & " -h " & theHost
    end if

    if userName is not equal to "" then
        set logInString to logInString & " -u " & userName
    end if

    if gUsePasswordInMyCnfFile is false then
        set logInString to logInString & " --password='" & usersPassword & "'"
    end if
    return logInString
end CreateLogInString

on SetTheDataBase(theDataBase)
    set gTheDataBase to theDataBase
end SetTheDataBase

on GetTheDataBase()
    return gTheDataBase
end GetTheDataBase

on CreateDataBaseRaw(userName, usersPassword, theHost, dataBaseName)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set theLoginString to CreateLogInString(userName, usersPassword, theHost)
    set theCommand to gmySQLCommand & theLoginString & " -e \"" & "CREATE DATABASE IF NOT EXISTS " & "\`" & dataBaseName & "\`" & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to "" & (do shell script theCommand)
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end CreateDataBaseRaw

on CreateDataBase(dataBaseName)
    return CreateDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, dataBaseName)
end CreateDataBase

on DropDataBaseRaw(userName, usersPassword, theHost, dataBaseName)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set theLoginString to CreateLogInString(userName, usersPassword, theHost)
    set theCommand to gmySQLCommand & theLoginString & " -e \"" & "DROP DATABASE IF EXISTS " & "\`" & dataBaseName & "\`" & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end DropDataBaseRaw

on DropDataBase(dataBaseName)
    return DropDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)
end DropDataBase

on ShowDataBasesRaw(userName, usersPassword, theHost)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set theLoginString to CreateLogInString(userName, usersPassword, theHost)
    set theCommand to gmySQLCommand & theLoginString & " -e \"" & "SHOW DATABASES" & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end ShowDataBasesRaw

on ShowDataBases()
    return ShowDataBasesRaw(gTheUser, gTheUsersPassword, gTheHost)
end ShowDataBases

on ShowTablesRaw(userName, usersPassword, theHost, theDataBase)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set theLoginString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & theLoginString & dataBaseString & " -e \"" & "SHOW TABLES" & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end ShowTablesRaw

on ShowTables()
    return ShowTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)
end ShowTables

on CreateTableRaw(userName, usersPassword, theHost, theDataBase, tableName, tableStructure)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "CREATE TABLE IF NOT EXISTS " & "\`" & tableName & "\`" & " (" & tableStructure & ")\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end CreateTableRaw

on CreateTable(tableName, tableStructrue)
    return CreateTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName, tableStructrue)
end CreateTable

on DeleteTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DROP TABLE IF EXISTS " & "\`" & tableName & "\`" & "\""
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end DeleteTablesRaw

on DeleteTables(tableName)
    return DeleteTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)
end DeleteTables

on DescribeTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DESCRIBE " & "\`" & tableName & "\`" & "\""
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end DescribeTablesRaw

on DescribeTables(tableName)
    return DescribeTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)
end DescribeTables

on InsertDataIntoTableRaw(userName, usersPassword, theHost, theDataBase, insertOptions, tableName, theInsertCommand)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"INSERT"
    if insertOptions is not equal to "" then
        set theCommand to theCommand & " " & insertOptions
    end if
    set theCommand to theCommand & " INTO " & "\`" & tableName & "\`" & " " & theInsertCommand & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end InsertDataIntoTableRaw

on InsertDataIntoTable(insertOptions, tableName, theInsertCommand)
    return InsertDataIntoTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, insertOptions, tableName, theInsertCommand)
end InsertDataIntoTable

on DeleteDataFromTableRaw(userName, usersPassword, theHost, theDataBase, deleteOptions, tableName, theDeleteCommand)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"DELETE"
    if deleteOptions is not equal to "" then
        set theCommand to theCommand & " " & deleteOptions
    end if
    set theCommand to theCommand & " FROM " & "\`" & tableName & "\`" & " " & theDeleteCommand & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end DeleteDataFromTableRaw

on DeleteDataFromTable(tableName, deleteOptions, theDeleteCommand)
    return DeleteDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, deleteOptions, tableName, theDeleteCommand)
end DeleteDataFromTable

on SelectDataFromTableRaw(userName, usersPassword, theHost, theDataBase, selectOptions, selectExpression, theTables, selectCommand)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""
    set theCommand to theCommand & "SELECT"
    if selectOptions is not equal to "" then
        set theCommand to theCommand & " " & selectOptions
    end if
    set theCommand to theCommand & " " & selectExpression
    if theTables is not equal to "" then
        set theCommand to theCommand & " FROM " & theTables
    end if
    if selectCommand is not equal to "" then
        set theCommand to theCommand & " " & selectCommand
    end if
    set theCommand to theCommand & "\""
    -- display dialog theCommand
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    -- display dialog theResult
    return theResult
end SelectDataFromTableRaw

on SelectDataFromTable(selectOptions, selectExpression, theTables, selectCommand)
    return SelectDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, selectOptions, selectExpression, theTables, selectCommand)
end SelectDataFromTable

-- If gUsePasswordInMyCnfFile is true then this function will ignore the
-- password passed in.
on ExecuteMySQLCommandRaw(userName, usersPassword, theHost, theDataBase, mySQLCommand)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & mySQLCommand & "\""
    set theResult to ""
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end ExecuteMySQLCommandRaw

on ExecuteMySQLCommand(mySQLCommand)
    return ExecuteMySQLCommandRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, mySQLCommand)
end ExecuteMySQLCommand

on UpdateTablesRaw(userName, usersPassword, theHost, theDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
    set finalResult to "Error: mysql not found, have you installed it?"
    if gmySQLCommand is equal to missing value then
        if SetLocationOfmySQL() is equal to false then
            return finalResult
        end if
    end if
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set dataBaseString to ""
    if theDataBase is not equal to "" then
        set dataBaseString to " -D " & "'" & theDataBase & "'"
    end if
    set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""
    set theCommand to theCommand & "UPDATE"
    if updateOptions is not equal to "" then
        set theCommand to " " & theCommand & updateOptions
    end if
    set theCommand to theCommand & " " & theTables & " " & "SET " & setExpression
    if whereDefinition is not equal to "" then
        set theCommand to theCommand & " WHERE " & whereDefinition
    end if
    if orderBy is not equal to "" then
        set theCommand to theCommand & " ORDER BY " & orderBy
    end if
    if limitTo is not equal to "" then
        set theCommand to theCommand & " LIMIT " & limitTo
    end if
    set theCommand to theCommand & "\""
    set theResult to ""
    -- display dialog theCommand
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end UpdateTablesRaw

on UpdateTables(updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
    return UpdateTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
end UpdateTables

on DumpMySQLRaw(userName, usersPassword, theHost, theDataBase, theTables, pathToFile)
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    if theDataBase is equal to "" then
        set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " --all-databases"
    else
        set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " '" & theDataBase & "'"
        if theTables is not equal to "" then
            -- set theCommand to theCommand & " '" & theTables & "' > " & quoted form of pathToFile
            set theCommand to theCommand & " " & theTables & " > " & quoted form of pathToFile
        else
            set theCommand to theCommand & " > " & quoted form of pathToFile
        end if
    end if
    -- display dialog theCommand
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end DumpMySQLRaw

on DumpMySQL(tableNames, pathToFile)
    return DumpMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableNames, pathToFile)
end DumpMySQL

on ImportMySQLRaw(userName, usersPassword, theHost, theDataBase, pathToFile)
    set logInString to CreateLogInString(userName, usersPassword, theHost)
    set theCommand to gmySQLCommand & logInString & " '" & theDataBase & "' < " & quoted form of pathToFile
    -- display dialog theCommand
    try
        set theResult to do shell script theCommand
    on error
        set theResult to RedoMySQLCommand(theCommand)
    end try
    return theResult
end ImportMySQLRaw

on ImportMySQL(pathToFile)
    return ImportMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, pathToFile)
end ImportMySQL

--end script

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

-- Copyleft Yarra Valley Software Ltd, Oxford, UK 2003

-- AppleScript mysql interface. 7 October 2003

-- Version Beta 0.3

-- This interface is freeware. It can be copied, duplicated used/modified.

-- This header must remain in place. Modified versions must acknowledge

-- the original indicate where modifications have been made. I would

-- really appreciate if improvements, additions could be passed through me

-- so that I can make them available within one version.

-- http://www.yvs.eu.com

-- ktam@yvs.eu.com

-- If you wish to include this script in your own AppleScript file, copy

-- everything to your script and then uncomment the last line and

-- the line that follows this text.

-- script mysql

property gPathToMySQL : missing value

property gmySQLCommand : missing value

property gTheUser : ""

property gTheUsersPassword : ""

property gTheHost : ""

property gTheDataBase : ""

property gUsePasswordInMyCnfFile : false

-- Determines where mysql is located. Returns true if successful else false.

on SetLocationOfmySQL()

if gmySQLCommand is not equal to missing value then

-- try

-- The command which is likely to cause least problems is get version.

-- set theResult to do shell script gmySQLCommand & " -V"

-- return true

-- end try

return true

end if

try

-- The command which is likely to cause least problems is get version.

set theResult to do shell script "mysql -V"

set gPathToMySQL to ""

on error

set mySQLList to {"/usr/local/mysql/bin/", "/Library/MySQL/bin/"}

set haveFound to false

repeat with i from 1 to the count of mySQLList

try

set theResult to do shell script (item i of mySQLList) & "mysql -V"

set gPathToMySQL to (item i of mySQLList)

set haveFound to true

exit repeat

end try

end repeat

if haveFound is equal to false then

try

set searchResults to do shell script "find /usr/local -name mysql -type f 2>/dev/null"

on error errorString

set searchResults to errorString

end try

-- display dialog searchResults

set tids to AppleScript's text item delimiters

set AppleScript's text item delimiters to return

set theResults to text items of searchResults

set AppleScript's text item delimiters to tids

set theCommand to "mysql"

repeat with i from 1 to (the count of theResults)

set theCommand to item i of theResults

if theCommand contains "mysql" then

try

-- The following line will throw an error if theCommand is not a valid mysql.

set theResult to do shell script theCommand & " -V"

-- display dialog "Command found: " & theCommand

exit repeat

end try

end if

end repeat

set theResult to do shell script theCommand & " -V"

set AppleScript's text item delimiters to "/"

set gPathToMySQL to (text items 1 thru -2 of theCommand) as string

set AppleScript's text item delimiters to tids

on error

try

set dialogResult to display dialog "Could not find your installation of mysql, could you please specify the directory that contains mysql" default answer "/Library/MySQL/bin/"

on error

set dialogResult to {button returned:"Cancel"}

end try

if the button returned of dialogResult is equal to "OK" then

set thePath to the text returned of dialogResult

try

-- The following line will throw an error if theCommand is not a valid mysql.

set theResult to do shell script thePath & "mysql -V"

set gPathToMySQL to thePath

on error

set gPathToMySQL to missing value

set gmySQLCommand to missing value

return false

end try

end if

end try

end if

end try

set gmySQLCommand to gPathToMySQL & "mysql"

return true

end SetLocationOfmySQL

on SetMySQLPath(thePath)

if thePath is equal to missing value then

set gPathToMySQL to missing value

set gmySQLCommand to missing value

else

set gPathToMySQL to thePath

set gmySQLCommand to thePath & "mysql"

end if

end SetMySQLPath

on GetMySQLPath()

return gPathToMySQL

end GetMySQLPath

on GetMySQLCommand()

return gmySQLCommand

end GetMySQLCommand

-- GetmySQLVersion will clear the mysql command and path variables if

-- an error occured when trying to get the version number.

on GetmySQLVersion()

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

try

set finalResult to do shell script gmySQLCommand & " -V"

on error

set gPathToMySQL to missing value

set gmySQLCommand to missing value

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

try

set finalResult to do shell script gmySQLCommand & " -V"

on error

set gPathToMySQL to missing value

set gmySQLCommand to missing value

end try

return finalResult

end GetmySQLVersion

-- Returns false if there is not a valid mysql to run.

on ClearMySQLCommandIfNotValid()

if GetmySQLVersion() begins with "Error:" then

return false

else

return true

end if

end ClearMySQLCommandIfNotValid

-- Attempts to redo the command after seeing if we have a valid mysql executable

on RedoMySQLCommand(theCommand)

set theResult to GetmySQLVersion()

if theResult begins with "Error" then

return theResult

end if

try

set theResult to do shell script theCommand

on error theError

set theResult to "Error: " & theError

end try

return theResult

end RedoMySQLCommand

-- If originalPassword is an empty string then assume that the password

-- has not previously been set.

on SetUserPassword(userName, originalPassword, newPassword)

set finalResult to "Error: mysql not found, have you installed it?"

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set mySQLAdminCommand to gPathToMySQL & "mysqladmin -u " & userName

if gUsePasswordInMyCnfFile is false then

set passwordComponent to " --password='" & originalPassword & "'"

end if

try

set theResult to do shell script mySQLAdminCommand & passwordComponent & " password " & quoted form of newPassword

on error theErrorString

set theResult to theErrorString

end try

return "" & theResult

end SetUserPassword

on SetRootPassword(originalPassword, newPassword)

return SetUserPassword("root", originalPassword, newPassword)

end SetRootPassword

on SetCurrentUser(userName)

set gTheUser to userName

end SetCurrentUser

on SetCurrentPassword(usersPassword)

set gTheUsersPassword to usersPassword

end SetCurrentPassword

on SetCurrentUserAndPassword(userName, usersPassword)

set gTheUser to userName

set gTheUsersPassword to usersPassword

end SetCurrentUserAndPassword

on SetTheHost(theHost)

set gTheHost to theHost

end SetTheHost

-- usePasswordInCnfFile is either true or false

on SetUsePasswordInMyCnfFile(usePasswordInCnfFile)

set gUsePasswordInMyCnfFile to usePasswordInCnfFile

end SetUsePasswordInMyCnfFile

-- If you are using the .my.cnf file to configure your login for mysql

-- then do not use this command as it will remove all entries in your

-- .my.cnf file and replace it with the password entry only.

on SetPasswordInMyCnfFile(thePassword)

try

set theCommand to "echo " & quoted form of ("[client]" & (ASCII character 10) & "password=" & thePassword) & " > ~/.my.cnf"

do shell script theCommand & ";chmod 600 ~/.my.cnf"

end try

end SetPasswordInMyCnfFile

--private function, should only be called by the handlers in mysql script

on CreateLogInString(userName, usersPassword, theHost)

set logInString to ""

if theHost is not equal to "" then

set logInString to logInString & " -h " & theHost

end if

if userName is not equal to "" then

set logInString to logInString & " -u " & userName

end if

if gUsePasswordInMyCnfFile is false then

set logInString to logInString & " --password='" & usersPassword & "'"

end if

return logInString

end CreateLogInString

on SetTheDataBase(theDataBase)

set gTheDataBase to theDataBase

end SetTheDataBase

on GetTheDataBase()

return gTheDataBase

end GetTheDataBase

on CreateDataBaseRaw(userName, usersPassword, theHost, dataBaseName)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set theLoginString to CreateLogInString(userName, usersPassword, theHost)

set theCommand to gmySQLCommand & theLoginString & " -e \"" & "CREATE DATABASE IF NOT EXISTS " & "\`" & dataBaseName & "\`" & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to "" & (do shell script theCommand)

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end CreateDataBaseRaw

on CreateDataBase(dataBaseName)

return CreateDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, dataBaseName)

end CreateDataBase

on DropDataBaseRaw(userName, usersPassword, theHost, dataBaseName)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set theLoginString to CreateLogInString(userName, usersPassword, theHost)

set theCommand to gmySQLCommand & theLoginString & " -e \"" & "DROP DATABASE IF EXISTS " & "\`" & dataBaseName & "\`" & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end DropDataBaseRaw

on DropDataBase(dataBaseName)

return DropDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)

end DropDataBase

on ShowDataBasesRaw(userName, usersPassword, theHost)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set theLoginString to CreateLogInString(userName, usersPassword, theHost)

set theCommand to gmySQLCommand & theLoginString & " -e \"" & "SHOW DATABASES" & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end ShowDataBasesRaw

on ShowDataBases()

return ShowDataBasesRaw(gTheUser, gTheUsersPassword, gTheHost)

end ShowDataBases

on ShowTablesRaw(userName, usersPassword, theHost, theDataBase)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set theLoginString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & theLoginString & dataBaseString & " -e \"" & "SHOW TABLES" & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end ShowTablesRaw

on ShowTables()

return ShowTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)

end ShowTables

on CreateTableRaw(userName, usersPassword, theHost, theDataBase, tableName, tableStructure)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "CREATE TABLE IF NOT EXISTS " & "\`" & tableName & "\`" & " (" & tableStructure & ")\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end CreateTableRaw

on CreateTable(tableName, tableStructrue)

return CreateTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName, tableStructrue)

end CreateTable

on DeleteTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DROP TABLE IF EXISTS " & "\`" & tableName & "\`" & "\""

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end DeleteTablesRaw

on DeleteTables(tableName)

return DeleteTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)

end DeleteTables

on DescribeTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DESCRIBE " & "\`" & tableName & "\`" & "\""

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end DescribeTablesRaw

on DescribeTables(tableName)

return DescribeTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)

end DescribeTables

on InsertDataIntoTableRaw(userName, usersPassword, theHost, theDataBase, insertOptions, tableName, theInsertCommand)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"INSERT"

if insertOptions is not equal to "" then

set theCommand to theCommand & " " & insertOptions

end if

set theCommand to theCommand & " INTO " & "\`" & tableName & "\`" & " " & theInsertCommand & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end InsertDataIntoTableRaw

on InsertDataIntoTable(insertOptions, tableName, theInsertCommand)

return InsertDataIntoTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, insertOptions, tableName, theInsertCommand)

end InsertDataIntoTable

on DeleteDataFromTableRaw(userName, usersPassword, theHost, theDataBase, deleteOptions, tableName, theDeleteCommand)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"DELETE"

if deleteOptions is not equal to "" then

set theCommand to theCommand & " " & deleteOptions

end if

set theCommand to theCommand & " FROM " & "\`" & tableName & "\`" & " " & theDeleteCommand & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end DeleteDataFromTableRaw

on DeleteDataFromTable(tableName, deleteOptions, theDeleteCommand)

return DeleteDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, deleteOptions, tableName, theDeleteCommand)

end DeleteDataFromTable

on SelectDataFromTableRaw(userName, usersPassword, theHost, theDataBase, selectOptions, selectExpression, theTables, selectCommand)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""

set theCommand to theCommand & "SELECT"

if selectOptions is not equal to "" then

set theCommand to theCommand & " " & selectOptions

end if

set theCommand to theCommand & " " & selectExpression

if theTables is not equal to "" then

set theCommand to theCommand & " FROM " & theTables

end if

if selectCommand is not equal to "" then

set theCommand to theCommand & " " & selectCommand

end if

set theCommand to theCommand & "\""

-- display dialog theCommand

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

-- display dialog theResult

return theResult

end SelectDataFromTableRaw

on SelectDataFromTable(selectOptions, selectExpression, theTables, selectCommand)

return SelectDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, selectOptions, selectExpression, theTables, selectCommand)

end SelectDataFromTable

-- If gUsePasswordInMyCnfFile is true then this function will ignore the

-- password passed in.

on ExecuteMySQLCommandRaw(userName, usersPassword, theHost, theDataBase, mySQLCommand)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & mySQLCommand & "\""

set theResult to ""

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end ExecuteMySQLCommandRaw

on ExecuteMySQLCommand(mySQLCommand)

return ExecuteMySQLCommandRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, mySQLCommand)

end ExecuteMySQLCommand

on UpdateTablesRaw(userName, usersPassword, theHost, theDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)

set finalResult to "Error: mysql not found, have you installed it?"

if gmySQLCommand is equal to missing value then

if SetLocationOfmySQL() is equal to false then

return finalResult

end if

set logInString to CreateLogInString(userName, usersPassword, theHost)

set dataBaseString to ""

if theDataBase is not equal to "" then

set dataBaseString to " -D " & "'" & theDataBase & "'"

end if

set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""

set theCommand to theCommand & "UPDATE"

if updateOptions is not equal to "" then

set theCommand to " " & theCommand & updateOptions

end if

set theCommand to theCommand & " " & theTables & " " & "SET " & setExpression

if whereDefinition is not equal to "" then

set theCommand to theCommand & " WHERE " & whereDefinition

end if

if orderBy is not equal to "" then

set theCommand to theCommand & " ORDER BY " & orderBy

end if

if limitTo is not equal to "" then

set theCommand to theCommand & " LIMIT " & limitTo

end if

set theCommand to theCommand & "\""

set theResult to ""

-- display dialog theCommand

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end UpdateTablesRaw

on UpdateTables(updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)

return UpdateTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)

end UpdateTables

on DumpMySQLRaw(userName, usersPassword, theHost, theDataBase, theTables, pathToFile)

set logInString to CreateLogInString(userName, usersPassword, theHost)

if theDataBase is equal to "" then

set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " --all-databases"

else

set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " '" & theDataBase & "'"

if theTables is not equal to "" then

-- set theCommand to theCommand & " '" & theTables & "' > " & quoted form of pathToFile

set theCommand to theCommand & " " & theTables & " > " & quoted form of pathToFile

else

set theCommand to theCommand & " > " & quoted form of pathToFile

end if

-- display dialog theCommand

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end DumpMySQLRaw

on DumpMySQL(tableNames, pathToFile)

return DumpMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableNames, pathToFile)

end DumpMySQL

on ImportMySQLRaw(userName, usersPassword, theHost, theDataBase, pathToFile)

set logInString to CreateLogInString(userName, usersPassword, theHost)

set theCommand to gmySQLCommand & logInString & " '" & theDataBase & "' < " & quoted form of pathToFile

-- display dialog theCommand

try

set theResult to do shell script theCommand

on error

set theResult to RedoMySQLCommand(theCommand)

end try

return theResult

end ImportMySQLRaw

on ImportMySQL(pathToFile)

return ImportMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, pathToFile)

end ImportMySQL

--end script

Motions-Media

Schlagwort-Archive: mysql

AppleScript: MySQL via do shell script

AppleScript: MySQL via MySQLBridge

Web, Coding, Mac, Snippets, a Blog and more…