public int?GenerateItemId(ItemType itemType)
{
ISQLiteConnection conn = null;
try {
conn = database.GetSqliteConnection();
int affected = conn.ExecuteLogged("INSERT INTO Item (ItemType, Timestamp) VALUES (?, ?)", itemType, DateTime.UtcNow.ToUnixTime());
if (affected >= 1)
{
try {
int rowId = conn.ExecuteScalar <int>("SELECT last_insert_rowid()");
if (rowId != 0)
{
return(rowId);
}
} catch (Exception e) {
logger.Error(e);
}
}
} catch (Exception e) {
logger.Error("GenerateItemId ERROR: ", e);
} finally {
database.CloseSqliteConnection(conn);
}
return(null);
}
// Note: this extension class exists so that we may provide more concise methods for accessing objects
// using simple database queries, without having to complicate the interface.
// Retrieve a single object of type T using query string and prepared arguments
public static T GetSingle <T>(this IDatabase database, string query, params object[] args) where T : new()
{
ISQLiteConnection conn = null;
try
{
// Get database connection, use query to generate object of type T
conn = database.GetSqliteConnection();
var result = conn.DeferredQuery <T>(query, args);
// If result found, return it
foreach (T obj in result)
{
return(obj);
}
}
catch (Exception e)
{
logger.Error("query: " + query);
logger.Error(e);
}
finally
{
database.CloseSqliteConnection(conn);
}
// If no result, return blank instance
return(new T());
}
public void RemoveMediaItemAtIndex(int index)
{
if (PlaylistId == 0)
{
return;
}
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition > ?", PlaylistId, index);
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public DemoMigrator(
SQLiteConnectionManager sqliteConnectionManager,
IPasswordManager passwordManager)
{
_passwordManager = passwordManager;
_sqliteConnection = sqliteConnectionManager.Connection;
}
public string CalculateHash()
{
StringBuilder itemIds = new StringBuilder();
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT ItemId FROM PlaylistItem WHERE PlaylistId = ?", PlaylistId);
foreach (PlaylistItem playlistItem in result)
{
itemIds.Append(playlistItem.ItemId);
itemIds.Append("|");
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(itemIds.ToString().MD5().Replace("-", string.Empty));
}
public void DeletePlaylist()
{
if (ReferenceEquals(PlaylistId, null))
{
return;
}
ISQLiteConnection conn = null;
try
{
// Delete the entry from the Playlist table
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.ExecuteLogged("DELETE FROM Playlist WHERE PlaylistId = ?", PlaylistId);
// Clear the playlist items
ClearPlaylist();
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private bool VideoNeedsUpdating(string filePath, int?folderId, out bool isNew, out int?itemId)
{
string fileName = Path.GetFileName(filePath);
long lastModified = System.IO.File.GetLastWriteTime(filePath).ToUnixTime();
bool needsUpdating = true;
isNew = true;
itemId = null;
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <Video>("SELECT * FROM Video WHERE FolderId = ? AND FileName = ?", folderId, fileName);
foreach (Video video in result)
{
isNew = false;
itemId = video.ItemId;
if (video.LastModified == lastModified)
{
needsUpdating = false;
}
break;
}
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(needsUpdating);
}
private void InitializeSettingTable()
{
string dbName = "receptionist.db3";
ILocalStorageService storageService = ServiceProvider.GetService <ILocalStorageService>();
IActivatorService activatorService = ServiceProvider.GetService <IActivatorService>();
var factory = activatorService.CreateInstance <Func <string, ISQLiteConnection> >();
ISQLiteConnection db = factory(storageService.GetFilePath(dbName, LocalFolderKind.Data));
if (!db.TableExists <GeneralSetting>())
{
db.CreateTable <GeneralSetting>();
}
//else
// db.MigrateTable<Setting>();
if (db.Table <GeneralSetting>().ToList().Count == 0)
{
List <GeneralSetting> generalsetting = new List <GeneralSetting>();
generalsetting.Add(new GeneralSetting()
{
SettingId = Guid.NewGuid(), GeneralName = "barcode"
});
db.InsertAll(generalsetting);
}
}
public SQLiteConnectionManager()
{
var databaseName = "MobileWarehouseDemo.db";
var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), databaseName);
Connection = new SiteSQLiteConnection(databasePath);
}
// Retrieve a list of objects of type T using query string and prepared arguments
public static IList <T> GetList <T>(this IDatabase database, string query, params object[] args) where T : new()
{
ISQLiteConnection conn = null;
try
{
// Get database connection, use query to generate object of type T
conn = database.GetSqliteConnection();
// If result found, return it
return(conn.Query <T>(query, args));
}
catch (Exception e)
{
logger.Error("query: " + query);
logger.Error(e);
}
finally
{
database.CloseSqliteConnection(conn);
}
// If no result, return blank instance
return(new List <T>());
}
public IMediaItem MediaItemAtIndex(int index)
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ? LIMIT 1", PlaylistId, index);
foreach (PlaylistItem playlistItem in result)
{
switch (playlistItem.ItemType)
{
case ItemType.Song:
return(Injection.Kernel.Get <ISongRepository>().SongForId((int)playlistItem.ItemId));
case ItemType.Video:
return(Injection.Kernel.Get <IVideoRepository>().VideoForId((int)playlistItem.ItemId));
default:
break;
}
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(new MediaItem());
}
// Execute a query on the database, with optional logging
private static int InternalExecuteQuery(this IDatabase database, bool logging, string query, params object[] args)
{
ISQLiteConnection conn = null;
try {
// Get database connection, execute and log query
conn = database.GetSqliteConnection();
if (logging)
{
return(conn.ExecuteLogged(query, args));
}
else
{
return(conn.Execute(query, args));
}
} catch (Exception e) {
logger.Error("execute failed: " + query);
logger.Error(e);
} finally {
database.CloseSqliteConnection(conn);
}
// Return 0 on exception, no rows affected
return(0);
}
public Repository(ISQLiteConnection connectionService)
{
_connectionService = connectionService;
_connection = _connectionService.GetConnection();
_connection.CreateTableAsync <T>();
//_connection.DropTableAsync<T>();
}
public ISQLiteConnection GetSqliteConnection()
{
lock (connectionPoolLock) {
if (getConnectionsAllowed)
{
ISQLiteConnection conn = null;
if (availableConnections.Count > 0)
{
// Grab an existing connection
conn = availableConnections.Pop();
}
else if (usedConnections < maxConnections)
{
// There are no available connections, and we have room for more open connections, so make a new one
conn = new SQLite.SQLiteConnection(databasePath);
conn.Execute("PRAGMA synchronous = OFF");
// Five second busy timeout
conn.BusyTimeout = new TimeSpan(0, 0, 5);
}
if (!ReferenceEquals(conn, null))
{
// We got a connection, so increment the counter
usedConnections++;
return(conn);
}
}
}
// If no connection available, sleep for 50ms and try again
Thread.Sleep(50);
// Recurse to try to get another connection
return(GetSqliteConnection());
}
public void ShouldThrowArgumentExceptionOnCreateGivenOptionsWithNullAddressAndTypeFile()
{
// Items Needing Cleanup
ISQLiteConnection conn = null;
try
{
// Arrange
#if __IOS__
ISQLiteConnectionFactoryEx factory = new MvxTouchSQLiteConnectionFactory();
#elif __ANDROID__
ISQLiteConnectionFactoryEx factory = new MvxDroidSQLiteConnectionFactory();
#else
ISQLiteConnectionFactoryEx factory = new MvxWpfSqLiteConnectionFactory();
#endif
SQLiteConnectionOptions options = new SQLiteConnectionOptions {
Address = null, Type = SQLiteConnectionOptions.DatabaseType.File
};
// Act
conn = factory.CreateEx(options);
}
finally // Cleanup in Finally
{
if (conn != null) // In case test fails and connection was created
{
conn.Close();
}
}
}
public virtual IDocumentStore Initialize()
{
Connection = Factory.Create(FileName);
InitializeInternal();
return(this);
}
public bool Delete()
{
if (ReferenceEquals(UserId, null))
{
return(true);
}
// Delete the user
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
int affected = conn.Execute("DELETE FROM User WHERE UserId = ?", UserId);
if (affected > 0)
{
// Delete associated sessions
Injection.Kernel.Get <ISessionRepository>().DeleteSessionsForUserId((int)UserId);
return(Injection.Kernel.Get <IUserRepository>().DeleteFromUserCache(this));
}
return(true);
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(false);
}
public bool UpdatePassword(string password)
{
string salt = GeneratePasswordSalt();
string hash = ComputePasswordHash(password, salt);
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
int affected = conn.Execute("UPDATE User SET PasswordHash = ?, PasswordSalt = ? WHERE UserId = ?", hash, salt, this.UserId);
if (affected > 0)
{
this.PasswordHash = hash;
this.PasswordSalt = salt;
return(Injection.Kernel.Get <IUserRepository>().UpdateUserCache(this));
}
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(false);
}
private DBHelper(string sqliteDb)
{
SoupNameToTableNamesMap = new Dictionary <string, string>();
SoupNameToIndexSpecsMap = new Dictionary <string, IndexSpec[]>();
DatabasePath = sqliteDb;
_sqlConnection = (ISQLiteConnection)Activator.CreateInstance(_sqliteConnectionType, sqliteDb);
}
public IList <IMediaItem> ListOfMediaItems()
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId);
IList <IMediaItem> items = new List <IMediaItem>();
foreach (PlaylistItem playlistItem in result)
{
if (!ReferenceEquals(playlistItem.ItemId, null))
{
IMediaItem item = Injection.Kernel.Get <IMediaItemRepository>().MediaItemForId((int)playlistItem.ItemId);
if (!ReferenceEquals(item, null))
{
items.Add(item);
}
}
}
return(items);
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(new List <IMediaItem>());
}
// TODO: Rewrite this to be more efficient
public IList <Song> SinglesForAlbumArtistId(int albumArtistId)
{
ISQLiteConnection conn = null;
try {
conn = database.GetSqliteConnection();
IList <Song> songs;
songs = conn.Query <Song>("SELECT ItemId FROM Song WHERE AlbumArtistId = ? AND AlbumId IS NULL", albumArtistId);
if (songs.Count > 0)
{
IList <int> songIds = new List <int>();
foreach (Song song in songs)
{
songIds.Add((int)song.ItemId);
}
return(songRepository.SongsForIds(songIds));
}
else
{
return(new List <Song>());
}
} catch (Exception e) {
logger.Error(e);
} finally {
database.CloseSqliteConnection(conn);
}
// We had an exception somehow, so return an empty list
return(new List <Song>());
}
public App()
{
// The root page of your application
ISQLiteConnection connection = DependencyService.Get <ISQLiteConnection>();
if (connection.GetConnection() != null)
{
Debug.WriteLine("SQLite connection is ready!");
}
MainPage = new ContentPage
{
Content = new StackLayout
{
VerticalOptions = LayoutOptions.Center,
Children =
{
new Label {
XAlign = TextAlignment.Center,
Text = connection.GetDataBasePath()
}
}
}
};
}
public void ShouldThrowArgumentNullExceptionOnCreateGivenNullOptions()
{
// Items Needing Cleanup
ISQLiteConnection conn = null;
try
{
// Arrange
#if __IOS__
ISQLiteConnectionFactoryEx factory = new MvxTouchSQLiteConnectionFactory();
#elif __ANDROID__
ISQLiteConnectionFactoryEx factory = new MvxDroidSQLiteConnectionFactory();
#else
ISQLiteConnectionFactoryEx factory = new MvxWpfSqLiteConnectionFactory();
#endif
// Act
conn = factory.CreateEx(null);
}
finally // Cleanup in Finally
{
if (conn != null) // In case test fails and connection was created
{
conn.Close();
}
}
}
public async Task BeginDropDatabase(Action sucessCallback, Action <Exception> failureCallback)
{
await Task.Run(() =>
{
try
{
FileInfo fileInfo = new FileInfo(this.DatabaseFileName);
ISQLiteConnection con = this.Connection as ISQLiteConnection;
if (null != con)
{
con.Dispose();
GC.Collect();
}
fileInfo.Delete();
this.Connection = null;
sucessCallback();
}
catch (Exception ex)
{
Telemetry.TrackException(ex);
failureCallback(ex);
}
});
}
/// <summary>
/// Metoda dodająca nowy komentarz dla danego produktu. Używana w procesie ETL.
/// </summary>
/// <param name="db"></param>
/// <param name="deviceID">Id urządzenia z bazy danych</param>
/// <param name="zalety">Zalety wymienione w komentarzu, pobrane w procesie ETL</param>
/// <param name="wady">Wady wymienione w komentarzu, pobrane w procesie ETL</param>
/// <param name="autor">Autor komentarza, pobrany w procesie ETL</param>
/// <param name="tekstOpinii">Tekst Opinii pobrany w procesie ETL</param>
/// <param name="gwiazdki">Ocena wystawiona przez autora opinii. (Zaokrąglana do pełnych liczb w dół)</param>
/// <param name="data">Data dodania opinii na stonę</param>
/// <param name="polecam">Czy produkt jest polecany przez kupującego. Pole dotyczy jedynie strony Ceneo</param>
/// <param name="przydatnosc">Ocena przydatności komentarza.</param>
/// <param name="pochodzenie">Pochodzenie komentarza. Dzięki temu łatwo można odróżnić komentarze pobrane z Ceneo, od komentarzy pobranych ze Skąpca.</param>
/// <returns></returns>
public static Task InsertComment(ISQLiteConnection db, long deviceID, string zalety, string wady, string autor, string tekstOpinii, string gwiazdki, string data, string polecam, string przydatnosc, string pochodzenie)
{
return(Task.Run(() =>
{
using (var commentmt = db.Prepare("INSERT INTO Comment (DeviceId, Zalety, Wady, Autor, TekstOpinii, Gwiazdki, Data, Polecam, Przydatnosc, Pochodzenie) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
{
// Reset the prepared statement so we can reuse it.
commentmt.ClearBindings();
commentmt.Reset();
commentmt.Bind(1, deviceID);
commentmt.Bind(2, zalety);
commentmt.Bind(3, wady);
commentmt.Bind(4, autor);
commentmt.Bind(5, tekstOpinii);
commentmt.Bind(6, gwiazdki);
commentmt.Bind(7, data);
commentmt.Bind(8, polecam);
commentmt.Bind(9, przydatnosc);
commentmt.Bind(10, pochodzenie);
commentmt.Step();
Debug.WriteLine("INSERT Project completed OK for comment " + deviceID);
}
}
));
}
public SQLiteEventPersistence(
ILog log,
ISQLiteConnection connection)
{
_log = log;
_connection = connection;
}
/// <summary>
/// Metoda ma na celu zwrócenie ID dowolnego produktu - zależnie od potrzeb.
/// Używana w czasie dodawania komentarzy. Dzięki niej produkty nie dublują się.
/// </summary>
/// <param name = "db" ></param>
///<param name = "deviceName" > Nazwa produktu</param>
/// <param name="manufacturer">Producent produktu</param>
/// <param name="others">Inne dane produktu</param>
/// <returns></returns>
public async static Task <long> GetDeviceId(ISQLiteConnection db, string deviceName, string manufacturer, string others)
{
long devIdReturn = 0;
try
{
await Task.Run(() =>
{
using (var deviceId = db.Prepare("SELECT Id FROM Device WHERE Name = ? AND Manufacturer = ? AND Others = ?"))
{
deviceId.Bind(1, deviceName);
deviceId.Bind(2, manufacturer);
deviceId.Bind(3, others);
deviceId.Step();
Debug.WriteLine(" GET ID For " + deviceName);
try
{
return(devIdReturn = (long)deviceId[0]);
}
catch
{
return(devIdReturn = 0);
}
}
});
Debug.WriteLine("ID is: " + devIdReturn);
return(devIdReturn);
}
catch
{
return(devIdReturn);
}
}
/// <summary>
/// Metoda dodająca nowy Produkt do bazy danych. Metoda jest używana w procesie ETL.
/// Dodatkowo zwracany jest ID dodanego urządzenia.
/// </summary>
/// <param name="db"></param>
/// <param name="deviceName">Nazwa produktu pobrana w procesie ETL</param>
/// <param name="manufacturer">Producent produktu pobrany w procesie ETL</param>
/// <param name="others">Inne dane produktu pobrane w procesie ETL</param>
/// <returns></returns>
public async static Task <long> InsertDevice(ISQLiteConnection db, string deviceName, string manufacturer, string others)
{
try
{
await Task.Run(() =>
{
using (var devicemt = db.Prepare("INSERT INTO Device (Name, Manufacturer, Others) VALUES (?, ?, ?)"))
{
devicemt.Bind(1, deviceName);
devicemt.Bind(2, manufacturer);
devicemt.Bind(3, others);
devicemt.Step();
Debug.WriteLine("INSERT completed OK for device " + deviceName);
}
}
);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return(0);
}
using (var idstmt = db.Prepare("SELECT last_insert_rowid()"))
{
idstmt.Step();
{
Debug.WriteLine("INSERT ID for device " + deviceName + ": " + (long)idstmt[0]);
return((long)idstmt[0]);
}
throw new Exception("Couldn't get inserted ID");
};
}
/// <summary>
/// Executes a single statement on a connection.
/// </summary>
/// <param name="connection"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static SQLiteResult Execute(this ISQLiteConnection connection, string sql)
{
using (var statement = connection.Prepare(sql))
{
return(statement.Step());
}
}
public MeetupManagerDatabase(ISQLiteConnectionFactory factory)
{
this.m_Connection = factory.Create(SQLiteDataBaseLocation);
//create taables
this.m_Connection.CreateTable <EventRSVP>();
this.m_Connection.CreateTable <NewMember>();
}
private DBHelper(string sqliteDb)
{
SoupNameToTableNamesMap = new Dictionary<string, string>();
SoupNameToIndexSpecsMap = new Dictionary<string, IndexSpec[]>();
DatabasePath = sqliteDb;
_sqlConnection = (ISQLiteConnection) Activator.CreateInstance(_sqliteConnectionType, sqliteDb);
}
public void Dispose()
{
if (dbConnection != null)
{
dbConnection.Close();
dbConnection = null;
}
}
public SQLiteTransaction(ISQLiteConnection connection)
{
_connection = connection;
using (var statement = connection.Prepare("BEGIN TRANSACTION"))
{
statement.Step().ThrowOnFailure("Failed to initiate a transaction");
}
}
public BowTieService(ISQLiteConnectionFactory factory)
{
_connection = factory.Create("BowTiesDB.sqlite");
_connection.CreateTable<BowTie>();
}
public FlashCardManager(ISQLiteConnection connection, IMvxMessenger messenger)
{
_messenger = messenger;
_connection = connection.connection;
_connection.CreateTable<FlashCardSet>();
_connection.CreateTable<FlashCard>();
}
public DataService(ISQLiteConnectionFactory factory, IKittenGenesisService genesis)
{
_connection = factory.Create("one.sql");
_connection.CreateTable<Kitten>();
if (!_connection.Table<Kitten>().Any())
{
FillWithKittens(genesis);
}
}
public InboxRepository(ISQLiteConnectionFactory sqlConnectionFactory)
{
// use factory to get the connection to SQLite database
// pass create the name of the SQLite database
_sqlConnection = sqlConnectionFactory.Create("btwgtd.sqlite");
// Create or ensure the dbase Table is created for our ItemOfStuff
_sqlConnection.CreateTable<ItemOfStuff>();
}
public TeamsService (ISQLiteConnectionFactory factory, IMvxMessenger messenger)
{
_messenger = messenger;
_connection = factory.Create ("teams.sql");
_connection.CreateTable<Team> ();
_connection.CreateTable<Answer> ();
}
protected BaseDatabaseHandler(string databaseName)
{
var factory = Mvx.Resolve<ISQLiteConnectionFactory>();
string basePath = FileUtils.GetQuranDatabaseDirectory(false, true);
if (basePath == null) return;
string path = FileUtils.Combine(QuranApp.NativeProvider.NativePath, basePath, databaseName);
dbConnection = CreateDatabase(factory, path);
}
public void CloseSqliteConnection(ISQLiteConnection conn)
{
if (ReferenceEquals(conn, null))
{
return;
}
lock (connectionPoolLock)
{
// Make the connection available and decrement the counter
availableConnections.Push(conn);
usedConnections--;
}
}
public DatabaseConnector(string DatabaseName)
{
var factory = new ConnectionFactory();
#if UNITY_EDITOR
var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#else
// check if file exists in Application.persistentDataPath
var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);
if (!File.Exists(filepath))
{
Debug.Log("Database not in Persistent path");
// if it doesn't ->
// open StreamingAssets directory and load the db ->
#if UNITY_ANDROID
var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName); // this is the path to your StreamingAssets in android
while (!loadDb.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
// then save to Application.persistentDataPath
File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
var loadDb = Application.dataPath + "/Raw/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#elif UNITY_WP8
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#elif UNITY_WINRT
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#endif
Debug.Log("Database written");
}
var dbPath = filepath;
#endif
databaseExist = true;
_connection = factory.Create(dbPath);
Debug.Log("Final PATH: " + dbPath);
}
private int SelectedSetId; // make this a global property
#endregion Fields
#region Constructors
public StudySettingsService(ISQLiteConnection connection)
{
_connection = connection.connection;
_connection.CreateTable<StudySettings>();
if (!(DefaultSettingsExist()))
{
_connection.InsertOrReplace(new StudySettings()
{
Id = 1,
CanShowCharacters = true,
FirstSide = "English",
MaxCardsInStudySet = 10000,
Name = "Default Settings",
SelectedSetId = 0,
ShowSimplified = true,
ShowDefinition = true,
ShowPinyin = true,
ShowTraditional = true
});
}
}
public DatabaseService(ISQLiteConnectionFactory sqliteConnectionFactory)
{
_sqliteConnectionFactory = sqliteConnectionFactory;
_connection = _sqliteConnectionFactory.Create(DatabaseName);
_connection.CreateTable<Movie>();
}
public ChineseManager(ISQLiteConnection connection)
{
_connection = connection.connection;
}
public ANoteData(DatabaseInfo info)
{
con = info.Connection;
}
/// <summary>
/// Metoda dodająca nowy komentarz dla danego produktu. Używana w procesie ETL.
/// </summary>
/// <param name="db"></param>
/// <param name="deviceID">Id urządzenia z bazy danych</param>
/// <param name="zalety">Zalety wymienione w komentarzu, pobrane w procesie ETL</param>
/// <param name="wady">Wady wymienione w komentarzu, pobrane w procesie ETL</param>
/// <param name="autor">Autor komentarza, pobrany w procesie ETL</param>
/// <param name="tekstOpinii">Tekst Opinii pobrany w procesie ETL</param>
/// <param name="gwiazdki">Ocena wystawiona przez autora opinii. (Zaokrąglana do pełnych liczb w dół)</param>
/// <param name="data">Data dodania opinii na stonę</param>
/// <param name="polecam">Czy produkt jest polecany przez kupującego. Pole dotyczy jedynie strony Ceneo</param>
/// <param name="przydatnosc">Ocena przydatności komentarza.</param>
/// <param name="pochodzenie">Pochodzenie komentarza. Dzięki temu łatwo można odróżnić komentarze pobrane z Ceneo, od komentarzy pobranych ze Skąpca.</param>
/// <returns></returns>
public static Task InsertComment(ISQLiteConnection db, long deviceID, string zalety, string wady, string autor, string tekstOpinii, string gwiazdki, string data, string polecam, string przydatnosc, string pochodzenie)
{
return Task.Run(() =>
{
using (var commentmt = db.Prepare("INSERT INTO Comment (DeviceId, Zalety, Wady, Autor, TekstOpinii, Gwiazdki, Data, Polecam, Przydatnosc, Pochodzenie) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
{
// Reset the prepared statement so we can reuse it.
commentmt.ClearBindings();
commentmt.Reset();
commentmt.Bind(1, deviceID);
commentmt.Bind(2, zalety);
commentmt.Bind(3, wady);
commentmt.Bind(4, autor);
commentmt.Bind(5, tekstOpinii);
commentmt.Bind(6, gwiazdki);
commentmt.Bind(7, data);
commentmt.Bind(8, polecam);
commentmt.Bind(9, przydatnosc);
commentmt.Bind(10, pochodzenie);
commentmt.Step();
Debug.WriteLine("INSERT Project completed OK for comment " + deviceID);
}
}
);
}
public EnglishManager(ISQLiteConnection connection)
{
_connection = connection.connection;
}
/// <summary>
/// Metoda pobierająca ID dowolnego komentarza. Jest używana w czasie dodawania nowych komentarzy.
/// Dzięki niej komentarze nie są dublowane. W celu przyśpieszenia zapytania, nie są sprawdzane wszystkie pola.
/// </summary>
/// <param name="db"></param>
/// <param name="deviceId">Id urządzenia</param>
/// <param name="zalety">Zalety wymienione w komentarzu</param>
/// <param name="wady">Wady wymienione w komentarzu</param>
/// <param name="autor">Autor komentarza</param>
/// <param name="tekstOpinii">Tekst Opinii</param>
/// <returns></returns>
public async static Task<long> GetCommentId(ISQLiteConnection db, int deviceId,
string zalety, string wady,
string autor, string tekstOpinii)
{
long commIdReturn = 0;
try
{
await Task.Run(() =>
{
using (var commId = db.Prepare("SELECT Id FROM Comment WHERE DeviceId = ? AND Zalety = ? AND Wady = ? AND Autor = ? AND TekstOpinii = ?"))
{
commId.Bind(1, deviceId);
commId.Bind(2, zalety);
commId.Bind(3, wady);
commId.Bind(4, autor);
commId.Bind(5, tekstOpinii);
commId.Step();
Debug.WriteLine(" GET ID For " + autor);
try
{
return commIdReturn = (long)commId[0];
}
catch
{
return commIdReturn = 0;
}
}
});
Debug.WriteLine("Comment ID is: " + commIdReturn);
return commIdReturn;
}
catch
{
return commIdReturn;
}
}
/// <summary>
/// Metoda ma na celu zwrócenie ID dowolnego produktu - zależnie od potrzeb.
/// Używana w czasie dodawania komentarzy. Dzięki niej produkty nie dublują się.
/// </summary>
/// <param name = "db" ></param>
///<param name = "deviceName" > Nazwa produktu</param>
/// <param name="manufacturer">Producent produktu</param>
/// <param name="others">Inne dane produktu</param>
/// <returns></returns>
public async static Task<long> GetDeviceId(ISQLiteConnection db, string deviceName, string manufacturer, string others)
{
long devIdReturn = 0;
try
{
await Task.Run(() =>
{
using (var deviceId = db.Prepare("SELECT Id FROM Device WHERE Name = ? AND Manufacturer = ? AND Others = ?"))
{
deviceId.Bind(1, deviceName);
deviceId.Bind(2, manufacturer);
deviceId.Bind(3, others);
deviceId.Step();
Debug.WriteLine(" GET ID For " + deviceName);
try
{
return devIdReturn = (long)deviceId[0];
}
catch
{
return devIdReturn = 0;
}
}
});
Debug.WriteLine("ID is: " + devIdReturn);
return devIdReturn;
}
catch
{
return devIdReturn;
}
}
public ACategoryData(DatabaseInfo info)
{
con = info.Connection;
}
/// <summary>
/// Metoda dodająca nowy Produkt do bazy danych. Metoda jest używana w procesie ETL.
/// Dodatkowo zwracany jest ID dodanego urządzenia.
/// </summary>
/// <param name="db"></param>
/// <param name="deviceName">Nazwa produktu pobrana w procesie ETL</param>
/// <param name="manufacturer">Producent produktu pobrany w procesie ETL</param>
/// <param name="others">Inne dane produktu pobrane w procesie ETL</param>
/// <returns></returns>
public async static Task<long> InsertDevice(ISQLiteConnection db, string deviceName, string manufacturer, string others)
{
try
{
await Task.Run(() =>
{
using (var devicemt = db.Prepare("INSERT INTO Device (Name, Manufacturer, Others) VALUES (?, ?, ?)"))
{
devicemt.Bind(1, deviceName);
devicemt.Bind(2, manufacturer);
devicemt.Bind(3, others);
devicemt.Step();
Debug.WriteLine("INSERT completed OK for device " + deviceName);
}
}
);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return 0;
}
using (var idstmt = db.Prepare("SELECT last_insert_rowid()"))
{
idstmt.Step();
{
Debug.WriteLine("INSERT ID for device " + deviceName + ": " + (long)idstmt[0]);
return (long)idstmt[0];
}
throw new Exception("Couldn't get inserted ID");
};
}
public MvxQueue(ISQLiteConnectionFactory factory, IMvxJsonConverter jsonConverter)
{
_jsonConverter = jsonConverter;
_connection = factory.Create("queue.db");
_connection.CreateTable<QueueEntity>();
}
public FriendRepository(ISQLiteConnectionFactory connectionFactory)
{
_connection = connectionFactory.Create("one.sql");
_connection.CreateTable<Friend>();
}
public Repository(ISQLiteConnectionFactory factory)
{
_connection = factory.Create("collect.sql");
_connection.CreateTable<CollectedItem>();
}
internal void ResetConnection()
{
_sqlConnection.Dispose();
_sqlConnection = new SQLiteConnection(DatabasePath);
}
public DataService(ISQLiteConnectionFactory factory)
{
_connection = factory.Create("one.sql");
_connection.CreateTable<Kitten>();
}
public void CloseQueryLogSqliteConnection(ISQLiteConnection conn)
{
if (isPoolingEnabled)
{
logPool.CloseSqliteConnection(conn);
}
else
{
conn.Close();
}
}
public void CloseSqliteConnection(ISQLiteConnection conn)
{
if (isPoolingEnabled)
{
mainPool.CloseSqliteConnection(conn);
}
else
{
conn.Close();
}
}
public Repository(ISQLiteConnectionFactory factory)
{
_connection = factory.Create("phoneword.sql");
_connection.CreateTable<Call>();
}
public void CloseConnection()
{
if (_connection != null)
{
lock (_connectionLock)
{
if (_connection != null)
{
_connection.Dispose();
_connection = null;
}
}
}
}
