private static Record SqliteStmtToRecord(ISQLiteStatement stmt)
{
return(new Record(stmt.GetText(RecordColumns.KEY),
stmt.DataType(RecordColumns.VALUE) == SQLiteType.NULL ? null : stmt.GetText(RecordColumns.VALUE),
stmt.GetInteger(RecordColumns.SYNC_COUNT),
new DateTime(long.Parse(stmt.GetText(RecordColumns.LAST_MODIFIED_TIMESTAMP), CultureInfo.InvariantCulture.NumberFormat), DateTimeKind.Utc),
stmt.DataType(RecordColumns.LAST_MODIFIED_BY) == SQLiteType.NULL ? string.Empty : stmt.GetText(RecordColumns.LAST_MODIFIED_BY),
new DateTime(long.Parse(stmt.GetText(RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP), CultureInfo.InvariantCulture.NumberFormat), DateTimeKind.Utc),
stmt.GetInteger(RecordColumns.MODIFIED) == 1));
}
/// <summary>
/// 조회된 한 로우에 대한 데이터를 생성한다.
/// </summary>
/// <param name="stmt">DB 구문 객체</param>
/// <returns>파일 정보</returns>
private StorageItemInfo GetRowData(ISQLiteStatement stmt)
{
return(new StorageItemInfo()
{
Path = stmt.GetText("PATH"),
SubType = (SubType)stmt.GetInteger("FOLDER_TYPE"),
Name = stmt.GetText("NAME"),
RootPath = stmt.GetText("ROOT_PATH"),
FalToken = stmt.GetText2("FAL_TOKEN")
});
}
private static DatasetMetadata SqliteStmtToDatasetMetadata(ISQLiteStatement stmt)
{
return(new DatasetMetadata(
stmt.DataType(RecordColumns.DATASET_NAME) == SQLiteType.NULL ? string.Empty : stmt.GetText(DatasetColumns.DATASET_NAME),
new DateTime(long.Parse(stmt.GetText(DatasetColumns.CREATION_TIMESTAMP))),
new DateTime(long.Parse(stmt.GetText(DatasetColumns.LAST_MODIFIED_TIMESTAMP))),
stmt.DataType(DatasetColumns.LAST_MODIFIED_BY) == SQLiteType.NULL ? string.Empty : stmt.GetText(DatasetColumns.LAST_MODIFIED_BY),
stmt.GetInteger(DatasetColumns.STORAGE_SIZE_BYTES),
stmt.GetInteger(DatasetColumns.RECORD_COUNT)
));
}
/// <summary>
/// 조회된 한 로우에 대한 데이터를 생성한다.
/// </summary>
/// <param name="stmt">DB 구문 객체</param>
/// <returns>파일 정보</returns>
private FolderInfo GetRowData(ISQLiteStatement stmt)
{
return(new FolderInfo()
{
Type = (FolderType)stmt.GetInteger("FOLDER_TYPE"),
Level = stmt.GetInteger2("LEVEL"),
Name = stmt.GetText("NAME"),
Path = stmt.GetText("PATH"),
FalToken = stmt.GetText2("FAL_TOKEN"),
Passcode = stmt.GetText2("PASSCODE")
});
}
public JArray Retrieve(string soupName, params long[] soupEntryIds)
{
lock (smartlock)
{
DBHelper db = Database;
string soupTableName = db.GetSoupTableName(soupName);
var result = new JArray();
if (String.IsNullOrWhiteSpace(soupTableName))
{
throw new SmartStoreException("Soup: " + soupName + " does not exist");
}
using (ISQLiteStatement statement = db.Query(soupTableName, new[] { SoupCol }, String.Empty, String.Empty,
GetSoupEntryIdsPredicate(soupEntryIds)))
{
if (statement.DataCount > 0)
{
do
{
string raw = statement.GetText(statement.ColumnIndex(SoupCol));
result.Add(JObject.Parse(raw));
} while (statement.Step() == SQLiteResult.ROW);
}
}
return(result);
}
}
/// <summary>
/// 자막 리스트를 일괄 등록한다.
/// </summary>
/// <param name="mediaInfoList">미디어정보 리스트</param>
/// <returns>DB 처리 결과</returns>
//public SQLiteResult InsertSubtitles(MediaInfo mediaInfo)
//{
// if (mediaInfo.SubtitleFileList != null)
// {
// return Transactional(() =>
// {
// SQLiteResult result = SQLiteResult.EMPTY;
// using (var custstmt = this.conn.Prepare(DML_INSERT_SUBTITLE))
// {
// foreach (var subtitle in mediaInfo.SubtitleFileList)
// {
// custstmt.Bind("@PATH", subtitle.Path);
// custstmt.Bind("@OWNER_SEQ", mediaInfo.Path);
// custstmt.Bind("@FAL_TOKEN", subtitle.FalToken);
// result = custstmt.Step();
// if (result != SQLitePCL.SQLiteResult.DONE)
// {
// return result;
// }
// // Resets the statement, to that it can be used again (with different parameters).
// custstmt.Reset();
// custstmt.ClearBindings();
// }
// }
// return result;
// });
// }
// else
// {
// return SQLiteResult.DONE;
// }
//}
/// <summary>
/// 모든 자막 목록을 삭제한다.
/// </summary>
/// <returns>DB 처리 결과</returns>
//public SQLiteResult DeleteSubtitle(SubtitleInfo sif)
//{
// //모든 자막 FAL 삭제 (특별한 경우를 제외하고는 수행되지 않음 - 상위 폴더가 FAL에 등록되어 있으면 하위 파일은 접근 권한을 갖기 때문)
// foreach (var entry in StorageApplicationPermissions.FutureAccessList.Entries)
// {
// if (entry.Metadata == typeof(SubtitleInfo).ToString())
// {
// StorageApplicationPermissions.FutureAccessList.Remove(entry.Token);
// }
// }
// using (var pstmt = conn.Prepare(DML_DELETE_SUBTITLE))
// {
// pstmt.Bind("@OWNER_SEQ", sif.Path) ;
// return pstmt.Step();
// }
//}
/// <summary>
/// 재생목록 로우 펫치
/// </summary>
/// <param name="stmt"></param>
/// <returns></returns>
private MediaInfo GetRowDataForPlayList(ISQLiteStatement stmt)
{
var mi = new MediaInfo()
{
Path = stmt.GetText("PATH"),
Name = stmt.GetText("NAME"),
FalToken = stmt.GetText2("FAL_TOKEN"),
RunningTime = stmt.GetInteger2("RUNNING_TIME"),
PausedTime = stmt.GetInteger2("PAUSED_TIME"),
};
//재생목록에 추가된 시간 파싱
DateTime AddedDateTime;
if (DateTime.TryParse(stmt.GetText2("ADDED_DATETIME"), out AddedDateTime))
{
mi.AddedDateTime = AddedDateTime;
}
return(mi);
}
private IndexSpec[] GetIndexSpecsFromDb(String soupName)
{
ISQLiteStatement statement = Query(SmartStore.SoupIndexMapTable,
new[] { SmartStore.PathCol, SmartStore.ColumnNameCol, SmartStore.ColumnTypeCol }, null,
null, SmartStore.SoupNamePredicate, soupName);
if (statement.DataCount < 1)
{
throw new SmartStoreException(String.Format("{0} does not have any indices", soupName));
}
var indexSpecs = new List <IndexSpec>();
do
{
String path = statement.GetText(SmartStore.PathCol);
String columnName = statement.GetText(SmartStore.ColumnNameCol);
var columnType = new SmartStoreType(statement.GetText(SmartStore.ColumnTypeCol));
indexSpecs.Add(new IndexSpec(path, columnType, columnName));
} while (statement.Step() == SQLiteResult.ROW);
statement.ResetAndClearBindings();
return(indexSpecs.ToArray());
}
private static List <string> GetAllSoupNames(string databasePath)
{
lock (smartlock)
{
var soupNames = new List <string>();
DBHelper db = DBHelper.GetInstance(databasePath);
using (ISQLiteStatement stmt = db.Query(SoupNamesTable, new[] { SoupNameCol }, String.Empty, String.Empty,
String.Empty))
{
if (stmt.DataCount > 0)
{
do
{
soupNames.Add(stmt.GetText(0));
} while (stmt.Step() == SQLiteResult.ROW);
}
}
return(soupNames);
}
}
/// <summary>
/// Return JSONArray for one row of data from the statement
/// </summary>
/// <param name="statement"></param>
/// <returns></returns>
private JArray GetDataFromRow(ISQLiteStatement statement)
{
var row = new JArray();
int columnCount = statement.ColumnCount;
for (int i = 0; i < columnCount; i++)
{
if (statement.ColumnName(i).EndsWith(SoupCol))
{
string raw = statement.GetText(i);
row.Add(JObject.Parse(raw));
}
else
{
object raw = GetObject(statement, i);
if (raw != null)
{
long value;
if (long.TryParse(raw.ToString(), out value))
{
row.Add(new JValue(value));
}
else
{
double dvalue;
if (double.TryParse(raw.ToString(), out dvalue))
{
row.Add(new JValue(dvalue));
}
else
{
row.Add(raw.ToString());
}
}
}
}
}
return(row);
}
/// <summary>
/// Helper to retrieve data.
/// </summary>
/// <param name="statement"></param>
/// <param name="position"></param>
/// <returns></returns>
private object GetObject(ISQLiteStatement statement, int position)
{
try
{
return(statement.GetText(position));
}
catch (Exception) { }
try
{
return(statement.GetInteger(position));
}
catch (Exception) { }
try
{
return(statement.GetFloat(position));
}
catch (Exception) { }
return(null);
}
public List <GV.DATA_INFO_STRING> GetDataRecord(SQLiteConnection conn, string sql)
{
List <GV.DATA_INFO_STRING> entries = new List <GV.DATA_INFO_STRING>();
using (ISQLiteStatement dbState = GV.connection.Prepare(sql))
{
while (dbState.Step() == SQLiteResult.ROW)
{
entries.Add(
new GV.DATA_INFO_STRING
{
ID = dbState.GetText(0),
EventType = dbState.GetText(1),
EventTime = dbState.GetText(2),
Cadence = dbState.GetText(3),
Power = dbState.GetText(4),
Resistance = dbState.GetText(5),
HandleP = dbState.GetText(6),
SaddleP = dbState.GetText(7),
PedalLeftP = dbState.GetText(8),
PedalLeftAcc = dbState.GetText(9),
PeadlLeftGyro = dbState.GetText(10),
PeadlRightP = dbState.GetText(11),
PedalRightAcc = dbState.GetText(12),
PeadlRightGyro = dbState.GetText(13),
CrankAcc = dbState.GetText(14),
CrankGyro = dbState.GetText(15)
//iiiCadence = dbState.GetText(14),
//iiiResistance = dbState.GetText(15),
//iiiPower = dbState.GetText(16),
//magCadence = dbState.GetText(17),
//magResistance = dbState.GetText(18),
//magPower = dbState.GetText(19)
});
}
}
return(entries.ToList <GV.DATA_INFO_STRING>());
}
/// <summary>
/// Re-index all soup elements for passed indexPaths
/// </summary>
/// <param name="soupName"></param>
/// <param name="indexPaths"></param>
/// <param name="handleTx"></param>
public void ReIndexSoup(string soupName, string[] indexPaths, bool handleTx)
{
lock (smartlock)
{
DBHelper db = DBHelper.GetInstance(DatabasePath);
string soupTableName = db.GetSoupTableName(soupName);
if (String.IsNullOrWhiteSpace(soupTableName))
{
throw new SmartStoreException("Soup: " + soupName + " does not exist");
}
Dictionary <string, IndexSpec> mapAllSpecs = IndexSpec.MapForIndexSpecs(GetSoupIndexSpecs(soupName));
IndexSpec[] indexSpecs =
(from indexPath in indexPaths where mapAllSpecs.ContainsKey(indexPath) select mapAllSpecs[indexPath])
.ToArray();
if (indexSpecs.Length == 0)
{
return; // nothing to do
}
if (handleTx)
{
db.BeginTransaction();
}
using (
ISQLiteStatement stmt = db.Query(soupTableName, new[] { IdCol, SoupCol }, String.Empty, String.Empty,
String.Empty))
{
if (stmt.DataCount > 0)
{
try
{
do
{
string soupEntryId = stmt.GetText(0);
string soupRaw = stmt.GetText(1);
try
{
JObject soupElt = JObject.Parse(soupRaw);
var contentValues = new Dictionary <string, object>();
foreach (IndexSpec indexSpec in indexSpecs)
{
ProjectIndexedPaths(soupElt, contentValues, indexSpec);
}
db.Update(soupTableName, contentValues, IdPredicate, soupEntryId + String.Empty);
}
catch (JsonException e)
{
Debug.WriteLine("SmartStore.ReIndexSoup: Could not parse soup element " +
soupEntryId +
"\n" + e.Message);
}
} while (stmt.Step() == SQLiteResult.ROW);
}
finally
{
if (handleTx)
{
db.CommitTransaction();
}
}
}
}
}
}
/// <summary>
/// Return JSONArray for one row of data from the statement
/// </summary>
/// <param name="statement"></param>
/// <returns></returns>
private JArray GetDataFromRow(ISQLiteStatement statement)
{
var row = new JArray();
int columnCount = statement.ColumnCount;
for (int i = 0; i < columnCount; i++)
{
if (statement.ColumnName(i).EndsWith(SoupCol))
{
string raw = statement.GetText(i);
row.Add(JObject.Parse(raw));
}
else
{
object raw = GetObject(statement, i);
if (raw != null)
{
long value;
if (long.TryParse(raw.ToString(), out value))
{
row.Add(new JValue(value));
}
else
{
double dvalue;
if (double.TryParse(raw.ToString(), out dvalue))
{
row.Add(new JValue(dvalue));
}
else
{
row.Add(raw.ToString());
}
}
}
}
}
return row;
}
/// <summary>
/// Helper to retrieve data.
/// </summary>
/// <param name="statement"></param>
/// <param name="position"></param>
/// <returns></returns>
private object GetObject(ISQLiteStatement statement, int position)
{
try
{
return statement.GetText(position);
}
catch (Exception) { }
try
{
return statement.GetInteger(position);
}
catch (Exception) { }
try
{
return statement.GetFloat(position);
}
catch (Exception) { }
return null;
}
private static Record SqliteStmtToRecord(ISQLiteStatement stmt)
{
return new Record(stmt.GetText(RecordColumns.KEY),
stmt.GetText(RecordColumns.VALUE),
stmt.GetInteger(RecordColumns.SYNC_COUNT),
new DateTime(long.Parse(stmt.GetText(RecordColumns.LAST_MODIFIED_TIMESTAMP))),
stmt.DataType(RecordColumns.LAST_MODIFIED_BY) == SQLiteType.NULL ? string.Empty : stmt.GetText(RecordColumns.LAST_MODIFIED_BY),
new DateTime(long.Parse(stmt.GetText(RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP))),
stmt.GetInteger(RecordColumns.MODIFIED) == 1);
}
private static DatasetMetadata SqliteStmtToDatasetMetadata(ISQLiteStatement stmt)
{
return new DatasetMetadata(
stmt.DataType(RecordColumns.DATASET_NAME) == SQLiteType.NULL ?string.Empty:stmt.GetText(DatasetColumns.DATASET_NAME),
new DateTime(long.Parse(stmt.GetText(DatasetColumns.CREATION_TIMESTAMP))),
new DateTime(long.Parse(stmt.GetText(DatasetColumns.LAST_MODIFIED_TIMESTAMP))),
stmt.DataType(DatasetColumns.LAST_MODIFIED_BY) == SQLiteType.NULL ? string.Empty : stmt.GetText(DatasetColumns.LAST_MODIFIED_BY),
stmt.GetInteger(DatasetColumns.STORAGE_SIZE_BYTES),
stmt.GetInteger(DatasetColumns.RECORD_COUNT)
);
}