/// <summary> /// Generates an "UPDATE tablename SET columns = ? WHERE pkColumns = ?" statement for a POCO of Type T. /// </summary> public string GenerateUpdate <T>() { var pocoData = _pocoDataFactory.GetPocoData <T>(); if (pocoData.Columns.Count == 0) { throw new InvalidOperationException(string.Format(CqlGenerator.NoColumns, "UPDATE", typeof(T).Name)); } if (pocoData.MissingPrimaryKeyColumns.Count > 0) { throw new InvalidOperationException(string.Format(CqlGenerator.MissingPkColumns, "UPDATE", typeof(T).Name, pocoData.MissingPrimaryKeyColumns.ToCommaDelimitedString())); } var nonPkColumns = pocoData.GetNonPrimaryKeyColumns().Select(CqlGenerator.EscapeFunc(pocoData, "{0} = ?")).ToCommaDelimitedString(); var pkColumns = string.Join(" AND ", pocoData.GetPrimaryKeyColumns().Select(CqlGenerator.EscapeFunc(pocoData, "{0} = ?"))); return($"UPDATE {CqlGenerator.CqlIdentifierHelper.EscapeTableNameIfNecessary(pocoData, pocoData.KeyspaceName, pocoData.TableName)} SET {nonPkColumns} WHERE {pkColumns}"); }
/// <summary> /// Adds "SELECT columnlist" and "FROM tablename" to a CQL statement if they don't already exist for a POCO of Type T. /// </summary> public void AddSelect <T>(Cql cql) { // If it's already got a SELECT clause, just bail if (CqlGenerator.SelectRegex.IsMatch(cql.Statement)) { return; } // Get the PocoData so we can generate a list of columns var pocoData = _pocoDataFactory.GetPocoData <T>(); var allColumns = pocoData.Columns.Select(CqlGenerator.EscapeFunc(pocoData)).ToCommaDelimitedString(); var suffix = cql.Statement == string.Empty ? string.Empty : " " + cql.Statement; // If it's got the from clause, leave FROM intact, otherwise add it cql.SetStatement(CqlGenerator.FromRegex.IsMatch(cql.Statement) ? $"SELECT {allColumns}{suffix}" : $"SELECT {allColumns} FROM " + $"{CqlGenerator.CqlIdentifierHelper.EscapeTableNameIfNecessary(pocoData, pocoData.KeyspaceName, pocoData.TableName)}" + $"{suffix}"); }
/// <summary> /// Gets the CQL queries involved in a table creation (CREATE TABLE, CREATE INDEX) /// </summary> public static List <string> GetCreate(Serializer serializer, PocoData pocoData, string tableName, string keyspaceName, bool ifNotExists) { if (pocoData == null) { throw new ArgumentNullException(nameof(pocoData)); } if (pocoData.MissingPrimaryKeyColumns.Count > 0) { throw new InvalidOperationException(string.Format(CqlGenerator.MissingPkColumns, "CREATE", pocoData.PocoType.Name, pocoData.MissingPrimaryKeyColumns.ToCommaDelimitedString())); } var commands = new List <string>(); var secondaryIndexes = new List <string>(); var createTable = new StringBuilder("CREATE TABLE "); tableName = CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, tableName); if (keyspaceName != null) { //Use keyspace.tablename notation tableName = CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, keyspaceName) + "." + tableName; } createTable.Append(tableName); createTable.Append(" ("); foreach (var column in pocoData.Columns) { var columnName = CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, column.ColumnName); createTable .Append(columnName) .Append(" "); var columnType = CqlGenerator.GetTypeString(serializer, column); createTable .Append(columnType); createTable .Append(", "); if (column.SecondaryIndex) { secondaryIndexes.Add(columnName); } } createTable.Append("PRIMARY KEY ("); if (pocoData.PartitionKeys.Count == 0) { throw new InvalidOperationException("No partition key defined"); } if (pocoData.PartitionKeys.Count == 1) { createTable.Append(CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, pocoData.PartitionKeys[0].ColumnName)); } else { //tupled partition keys createTable .Append("(") .Append(string.Join(", ", pocoData.PartitionKeys.Select(CqlGenerator.EscapeFunc(pocoData)))) .Append(")"); } if (pocoData.ClusteringKeys.Count > 0) { createTable.Append(", "); createTable.Append(string.Join( ", ", pocoData.ClusteringKeys.Select( k => CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, k.Item1.ColumnName)))); } //close primary keys createTable.Append(")"); //close table column definition createTable.Append(")"); var clusteringOrder = string.Join(", ", pocoData.ClusteringKeys .Where(k => k.Item2 != SortOrder.Unspecified) .Select(k => CqlGenerator.CqlIdentifierHelper.EscapeIdentifierIfNecessary(pocoData, k.Item1.ColumnName) + " " + (k.Item2 == SortOrder.Ascending ? "ASC" : "DESC"))); var clusteringOrderIsDefined = !string.IsNullOrEmpty(clusteringOrder); if (clusteringOrderIsDefined) { createTable .Append(" WITH CLUSTERING ORDER BY (") .Append(clusteringOrder) .Append(")"); } if (pocoData.CompactStorage) { createTable.Append($" {(clusteringOrderIsDefined ? "AND" : "WITH")} COMPACT STORAGE"); } commands.Add(createTable.ToString()); //Secondary index definitions commands.AddRange(secondaryIndexes.Select(name => "CREATE INDEX ON " + tableName + " (" + name + ")")); return(commands); }
/// <summary> /// Generates an "INSERT INTO tablename (columns) VALUES (?...)" statement for a POCO of Type T. /// </summary> /// <param name="insertNulls">When set to <c>true</c>, it will only generate columns which POCO members are not null</param> /// <param name="pocoValues">The parameters of this query, it will only be used if <c>insertNulls</c> is set to <c>true</c></param> /// <param name="queryParameters">The parameters for this query. When insertNulls is <c>true</c>, the <c>pocoValues</c> /// is the <c>queryParameters</c>, when set to <c>false</c> the <c>queryParameters do not include <c>null</c> values</c></param> /// <param name="ifNotExists">Determines if it should add the IF NOT EXISTS at the end of the query</param> /// <param name="ttl">Amount of seconds for the data to expire (TTL)</param> /// <param name="timestamp">Data timestamp</param> /// <param name="tableName">Table name. If null, it will use table name based on Poco Data</param> /// <returns></returns> public string GenerateInsert <T>(bool insertNulls, object[] pocoValues, out object[] queryParameters, bool ifNotExists = false, int?ttl = null, DateTimeOffset?timestamp = null, string tableName = null) { var pocoData = _pocoDataFactory.GetPocoData <T>(); if (pocoData.Columns.Count == 0) { throw new InvalidOperationException(string.Format(CqlGenerator.NoColumns, "INSERT", typeof(T).Name)); } string columns; string placeholders; var parameterList = new List <object>(); if (!insertNulls) { if (pocoValues == null) { throw new ArgumentNullException("pocoValues"); } if (pocoValues.Length != pocoData.Columns.Count) { throw new ArgumentException("Values array should contain the same amount of items as POCO columns"); } //only include columns which value is not null var columnsBuilder = new StringBuilder(); var placeholdersBuilder = new StringBuilder(); var encounteredNonNull = false; for (var i = 0; i < pocoData.Columns.Count; i++) { var value = pocoValues[i]; if (value == null) { continue; } if (encounteredNonNull) { columnsBuilder.Append(", "); placeholdersBuilder.Append(", "); } encounteredNonNull = true; columnsBuilder.Append(CqlGenerator.EscapeFunc(pocoData)(pocoData.Columns[i])); placeholdersBuilder.Append("?"); parameterList.Add(value); } columns = columnsBuilder.ToString(); placeholders = placeholdersBuilder.ToString(); } else { //Include all columns defined in the Poco columns = pocoData.Columns.Select(CqlGenerator.EscapeFunc(pocoData)).ToCommaDelimitedString(); placeholders = Enumerable.Repeat("?", pocoData.Columns.Count).ToCommaDelimitedString(); parameterList.AddRange(pocoValues); } var queryBuilder = new StringBuilder(); queryBuilder.Append("INSERT INTO "); queryBuilder.Append(tableName ?? CqlGenerator.CqlIdentifierHelper.EscapeTableNameIfNecessary(pocoData, pocoData.KeyspaceName, pocoData.TableName)); queryBuilder.Append(" ("); queryBuilder.Append(columns); queryBuilder.Append(") VALUES ("); queryBuilder.Append(placeholders); queryBuilder.Append(")"); if (ifNotExists) { queryBuilder.Append(" IF NOT EXISTS"); } if (ttl != null || timestamp != null) { queryBuilder.Append(" USING"); if (ttl != null) { queryBuilder.Append(" TTL ?"); parameterList.Add(ttl.Value); if (timestamp != null) { queryBuilder.Append(" AND"); } } if (timestamp != null) { queryBuilder.Append(" TIMESTAMP ?"); parameterList.Add((timestamp.Value - CqlGenerator.UnixEpoch).Ticks / 10); } } queryParameters = parameterList.ToArray(); return(queryBuilder.ToString()); }