Ionic 3中Sqlite本地存储相机照片及服务器同步实现问询

Got it, let's walk through how to implement offline storage and online sync for your camera images using your existing Media table. Here's a step-by-step approach tailored to your setup:

First, create reusable functions to interact with your Media table. These will handle inserting new media, fetching unsynced records, and updating sync status.

import { SQLite, SQLiteObject } from '@ionic-native/sqlite/ngx';

constructor(private sqlite: SQLite) {}

// Initialize the table (run once on app startup)
async initMediaTable(): Promise<void> {
  const db = await this.sqlite.create({ name: 'app_db.db', location: 'default' });
  await db.executeSql(`
    CREATE TABLE IF NOT EXISTS Media (
      MediaId TEXT NOT NULL PRIMARY KEY,
      MediaType INTEGER,
      MediaUrl TEXT,
      Flag INT
    )`, []);
}

// Insert new media into SQLite (Flag = 0 means unsynced)
async insertOfflineMedia(mediaId: string, mediaType: number, mediaData: string): Promise<void> {
  const db = await this.sqlite.create({ name: 'app_db.db', location: 'default' });
  await db.executeSql(
    'INSERT INTO Media (MediaId, MediaType, MediaUrl, Flag) VALUES (?, ?, ?, ?)',
    [mediaId, mediaType, mediaData, 0]
  );
}

// Fetch all unsynced media records
async getUnsyncedMedia(): Promise<any[]> {
  const db = await this.sqlite.create({ name: 'app_db.db', location: 'default' });
  const result = await db.executeSql('SELECT * FROM Media WHERE Flag = 0', []);
  const mediaList = [];
  for (let i = 0; i < result.rows.length; i++) {
    mediaList.push(result.rows.item(i));
  }
  return mediaList;
}

// Update media status to synced (Flag = 1) and update server URL
async markMediaAsSynced(mediaId: string, serverUrl: string): Promise<void> {
  const db = await this.sqlite.create({ name: 'app_db.db', location: 'default' });
  await db.executeSql(
    'UPDATE Media SET MediaUrl = ?, Flag = 1 WHERE MediaId = ?',
    [serverUrl, mediaId]
  );
}

Update your existing camera code to generate a unique ID for each image, then save it to SQLite immediately after capture.

// Add a UUID generator (or use the `uuid` npm package for reliability)
generateUniqueId(): string {
  return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, c => {
    const r = Math.random() * 16 | 0;
    return (c === 'x' ? r : (r & 0x3 | 0x8)).toString(16);
  });
}

// Modified camera code
this.camera.getPicture({
  quality: 100,
  destinationType: this.camera.DestinationType.DATA_URL,
  encodingType: this.camera.EncodingType.JPEG,
  mediaType: this.camera.MediaType.PICTURE,
  allowEdit: true
}).then(async (imageData) => {
  try {
    // Generate unique ID for the media
    const mediaId = this.generateUniqueId();
    // Store base64 image data in SQLite (Flag = 0 = unsynced)
    await this.insertOfflineMedia(mediaId, 1, `data:image/jpeg;base64,${imageData}`);
    
    // Continue with your existing gallery logic
    this.OpenCustomerGallery(this.imageService.b64toFile(...));
  } catch (err) {
    alert(err.message);
  }
}, (err) => {
  console.error('Camera error:', err);
});

Use network status to trigger sync when the app goes online. For Ionic apps, use the @ionic-native/network plugin:

import { Network } from '@ionic-native/network/ngx';

constructor(private network: Network) {
  // Listen for network connection
  this.network.onConnect().subscribe(() => {
    setTimeout(() => {
      if (this.network.type !== 'none') {
        this.syncOfflineMediaToServer();
      }
    }, 1000); // Wait a second to ensure stable connection
  });
}

// Helper to check if device is online
isOnline(): boolean {
  return this.network.type !== 'none';
}

Create a function to upload unsynced media to your server, then update SQLite once successful.

async syncOfflineMediaToServer(): Promise<void> {
  const unsyncedMedia = await this.getUnsyncedMedia();
  
  for (const media of unsyncedMedia) {
    try {
      // Convert base64 image to Blob for upload
      const base64Data = media.MediaUrl.split(',')[1];
      const blob = this.base64ToBlob(base64Data, 'image/jpeg');
      
      // Prepare FormData for server upload
      const formData = new FormData();
      formData.append('image', blob, `media-${media.MediaId}.jpg`);
      
      // Send POST request to your server
      const response = await fetch('https://your-server-endpoint.com/upload', {
        method: 'POST',
        body: formData
      });
      
      if (response.ok) {
        const serverResponse = await response.json();
        const serverMediaUrl = serverResponse.imageUrl; // Adjust based on your server's response
        
        // Mark media as synced in SQLite
        await this.markMediaAsSynced(media.MediaId, serverMediaUrl);
        console.log(`Media ${media.MediaId} synced successfully`);
      } else {
        throw new Error('Server upload failed');
      }
    } catch (err) {
      console.error(`Failed to sync media ${media.MediaId}:`, err);
      // Optionally add retry logic here (e.g., increment a retry count column)
    }
  }
}

// Helper to convert base64 to Blob
base64ToBlob(base64: string, contentType: string): Blob {
  const byteChars = atob(base64);
  const byteArrays = [];
  
  for (let offset = 0; offset < byteChars.length; offset += 512) {
    const slice = byteChars.slice(offset, offset + 512);
    const byteNumbers = Array.from(slice, char => char.charCodeAt(0));
    byteArrays.push(new Uint8Array(byteNumbers));
  }
  
  return new Blob(byteArrays, { type: contentType });
}

Ensure sync runs when the app starts or resumes (in case the user was offline before):

import { App } from '@ionic-native/app/ngx';

constructor(private app: App) {
  // Sync on app startup if online
  if (this.isOnline()) {
    this.syncOfflineMediaToServer();
  }
  
  // Sync on app resume
  this.app.resume.subscribe(() => {
    if (this.isOnline()) {
      this.syncOfflineMediaToServer();
    }
  });
}

Key Considerations #

• Efficient Storage: Storing base64 in SQLite can bloat your database. For better performance, save images as local files and store the file path in MediaUrl instead (change destinationType to FILE_URI and use the @ionic-native/file plugin to manage local files).
• Retry Logic: Add a RetryCount column to your Media table to handle failed uploads and retry them later.
• UUID Reliability: Replace the custom generateUniqueId function with the uuid npm package (npm install uuid --save) for more robust IDs.

内容的提问来源于stack exchange，提问作者Missak Boyajian