Convert Oracle Packages
On this page
You can import and convert your Oracle Packages to MongoDB code with the query converter. The query converter considers the mapping rules and schema transformations defined in your project when converting your SQL code.
About this Task
The query converter uses AI technology which may not be able to convert long or complex queries, triggers, packages, or stored procedures. Some queries may not be converted correctly while others may not be converted at all. For more information, see AI and Data Usage Information.
The query converter uses the relational schema, the MongoDB schema, and the mapping rules in your current project to determine how the queries should be converted. Conversions may fail or be incorrect if the queries reference tables that are not in your relational schema or if they are not mapped to MongoDB collections.
Converted queries, triggers, views, packages, and stored procedures are saved in your project and persist through project import and exports.
SQL queries are limited to 40,000 text characters.
You can view the history of previous conversions in the left-hand Query Converter pane. Each conversion has an icon indicating the result of the conversion. If an object does not have an icon next to it, a conversion has not been attempted yet:
IconDescriptionSpinner
The conversion is now being executed.
Green check mark
The conversion was successful.
Red exclamation mark
The conversion failed on last attempt.
Before you Begin
MongoDB does not have an official synonym object type for Oracle packages. Instead, MongoDB supports custom JavaScript and driver code that hosts the database logic. When migrating your application to MongoDB, you must choose the programming language and hosting option that best serves your application needs. You can host your converted Oracle package code in your application or with Atlas Functions.
Always review and test the code generated by query converter before deploying it in a production environment.
Steps
Connect to your relational database
If you are not already connected to your source database, specify the connection details to your source database and click Connect.
For more information on database connections in Relational Migrator, see Database Connections.
Select Oracle packages
On the Import from database modal, click the icon next to Database.
Click the icon next to your schema.
Click the icon next to Packages.
Tip
To toggle Oracle packages for conversion, click the icon next to the package's name.
Click Save.
The code for each package in your database schema is imported into your project and is visible in the Query Converter pane under Packages.
Convert and test code
Click a packages's name from the left-hand pane under Packages.
The code displays in the Imported Package pane.
Select a Target Language from the drop-down.
Query converter supports Oracle package conversions to Javascript, C#, and Java.
Note
If you use Java, you can also select Return a domain object where possible. When selected, the query converter includes strongly typed Java entities based on POJO classes if the query only operates on a single MongoDB collection.
Click the Convert button. Wait for the query converter to convert your code.
The converted MongoDB code displays on the Converted MongoDB Query pane.
If the query converter has errors, you can view the details in the Converted MongoDB Query pane.
Click the icon on the Converted MongoDB Query pane to copy the MongoDB code to your clipboard.
Test and validate the generated MongoDB code.
Tip
You can use the Filter text box to filter queries, stored procedures, triggers, packages, and views based on object name and SQL syntax.
Examples
The query converter can convert your packages to Javascript, C#, or Java code. The example below shows conversions to each language.
CREATE OR REPLACE EDITIONABLE PACKAGE "TICKETMANAGEMENT" IS procedure sellTickets(person_id IN NUMBER, event_id IN NUMBER, quantity IN NUMBER DEFAULT 1); procedure generateTicketActivity(transaction_delay NUMBER, max_transactions IN NUMBER DEFAULT 1000); procedure generateTransferActivity(transaction_delay IN NUMBER DEFAULT 5, max_transactions IN NUMBER DEFAULT 100); end ticketManagement; / CREATE OR REPLACE EDITIONABLE PACKAGE BODY "TICKETMANAGEMENT" AS -- forward type declaration TYPE eventTab IS TABLE OF sporting_event%ROWTYPE INDEX BY BINARY_INTEGER; TYPE eventRecType IS RECORD ( sport_name sport_type.name%TYPE, home_team_name sport_team.name%TYPE, away_team_name sport_team.name%TYPE, home_field sport_location.name%TYPE, date_time sporting_event.start_date_time%TYPE); -- package variables g_max_person_id person.id%TYPE; g_min_person_id person.id%TYPE; -- forward declarations function get_open_events return eventTab; function get_event_details(event_id IN NUMBER) RETURN eventRecType; procedure sellRandomTickets; -------------------------------- -- sell tickets proc -------------------------------- procedure sellTickets(person_id IN NUMBER, event_id IN NUMBER, quantity IN NUMBER DEFAULT 1) IS not_enough_seats EXCEPTION; p_person_id person.id%TYPE := person_id; p_event_id sporting_event.id%TYPE := event_id; p_quantity NUMBER := quantity; r_seat_level sporting_event_ticket.seat_level%TYPE; r_seat_section sporting_event_ticket.seat_section%type; r_seat_row sporting_event_ticket.seat_row%type; event_rec eventRecType; CURSOR adjacent_seats(p_seat_level NUMBER, p_seat_section VARCHAR2, p_seat_row VARCHAR2) IS SELECT * FROM sporting_event_ticket WHERE sporting_event_id = p_event_id AND seat_level = p_seat_level AND seat_section = p_seat_section AND seat_row = p_seat_row ORDER BY seat_level, seat_section, seat_row FOR UPDATE OF ticketholder_id; cur_ticket sporting_event_ticket%ROWTYPE; BEGIN event_rec := get_event_details(p_event_id); BEGIN SELECT seat_level, seat_section, seat_row INTO r_seat_level, r_seat_section, r_seat_row FROM (SELECT seat_level,seat_section,seat_row FROM sporting_event_ticket WHERE sporting_event_id = p_event_id AND ticketholder_id IS NULL GROUP BY seat_level,seat_section,seat_row HAVING COUNT(*) >= p_quantity) WHERE rownum < 2; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE not_enough_seats; END; OPEN adjacent_seats(r_seat_level,r_seat_section,r_seat_row); FOR i IN 1..p_quantity LOOP FETCH adjacent_seats INTO cur_ticket; UPDATE sporting_event_ticket SET ticketholder_id = p_person_id WHERE CURRENT OF adjacent_seats; INSERT INTO ticket_purchase_hist(sporting_event_ticket_id, purchased_by_id, transaction_date_time, purchase_price) VALUES(cur_ticket.id, p_person_id, SYSDATE, cur_ticket.ticket_price); END LOOP; COMMIT; EXCEPTION WHEN not_enough_seats THEN DBMS_OUTPUT.PUT_LINE('Sorry, there aren''t ' || p_quantity || ' adjacent seats for event:'); DBMS_OUTPUT.PUT_LINE(' ' || event_rec.home_team_name || ' VS ' || event_rec.away_team_name || ' (' || event_rec.sport_name || ')' ); DBMS_OUTPUT.PUT_LINE(' ' || event_rec.home_field || ': ' || TO_CHAR(event_rec.date_time,'DD-MON-YYYY HH:MI')); END; -------------------------------- -- transfer ticket -------------------------------- procedure transferTicket(ticket_id IN NUMBER, new_ticketholder_id IN NUMBER, transfer_all IN BOOLEAN DEFAULT FALSE, price IN NUMBER DEFAULT NULL) IS p_ticket_id NUMBER := ticket_id; p_new_ticketholder_id NUMBER := new_ticketholder_id; p_price NUMBER := price; xferall NUMBER := 0; old_ticketholder_id NUMBER; last_txn_date DATE; CURSOR txfr_cur(p_purchased_by NUMBER, p_txn_date_time DATE) IS SELECT * FROM ticket_purchase_hist WHERE purchased_by_id = p_purchased_by AND transaction_date_time = p_txn_date_time; BEGIN IF transfer_all THEN xferall := 1; END IF; SELECT max(h.transaction_date_time) as transaction_date_time ,t.ticketholder_id as ticketholder_id INTO last_txn_date, old_ticketholder_id FROM ticket_purchase_hist h ,sporting_event_ticket t WHERE t.id = p_ticket_id AND h.purchased_by_id = t.ticketholder_id AND ((h.sporting_event_ticket_id = p_ticket_id) OR (xferall = 1) ) GROUP BY t.ticketholder_id; FOR xrec IN txfr_cur(old_ticketholder_id, last_txn_date) LOOP UPDATE sporting_event_ticket SET ticketholder_id = p_new_ticketholder_id WHERE id = xrec.sporting_event_ticket_id; INSERT INTO ticket_purchase_hist(sporting_event_ticket_id, purchased_by_id, transferred_from_id, transaction_date_time, purchase_price) VALUES(xrec.sporting_event_ticket_id, p_new_ticketholder_id, old_ticketholder_id, SYSDATE, NVL(p_price,xrec.purchase_price)); END LOOP; COMMIT; -- commit the group EXCEPTION WHEN OTHERS THEN ROLLBACK; END; --------------------------------------- -- generate ticket purchase activity --------------------------------------- PROCEDURE generateTicketActivity(transaction_delay NUMBER, max_transactions IN NUMBER DEFAULT 1000) IS txn_count NUMBER := 0; BEGIN WHILE txn_count < max_transactions LOOP sellRandomTickets; txn_count := txn_count +1; dbms_lock.sleep(transaction_delay); END LOOP; END; --------------------------------------- -- generate ticket purchase activity --------------------------------------- PROCEDURE generateTransferActivity(transaction_delay IN NUMBER DEFAULT 5, max_transactions IN NUMBER DEFAULT 100) IS txn_count NUMBER := 0; min_tik_id sporting_event_ticket.id%TYPE; max_tik_id sporting_event_ticket.id%TYPE; tik_id sporting_event_ticket.id%TYPE; new_ticketholder person.id%TYPE; xfer_all BOOLEAN; chg_price BOOLEAN; new_price sporting_event_ticket.ticket_price%TYPE; BEGIN WHILE txn_count < max_transactions LOOP SELECT min(sporting_event_ticket_id), max(sporting_event_ticket_id) INTO min_tik_id, max_tik_id FROM ticket_purchase_hist; SELECT MAX(sporting_event_ticket_id) INTO tik_id FROM ticket_purchase_hist WHERE sporting_event_ticket_id <= dbms_random.value(min_tik_id,max_tik_id); new_ticketholder := TRUNC(dbms_random.value(g_min_person_id,g_max_person_id)); xfer_all := (ROUND(dbms_random.value(1,5)) < 5); -- transfer all tickets 80% of the time new_price := NULL; chg_price := (ROUND(dbms_random.value(1,3)) = 1); -- 30% of the time change price IF chg_price THEN SELECT dbms_random.value(0.8,1.2) * ticket_price INTO new_price FROM sporting_event_ticket WHERE id = tik_id; END IF; transferTicket(tik_id, new_ticketholder, xfer_all, new_price); txn_count := txn_count +1; dbms_lock.sleep(transaction_delay); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('No tickets available to transfer.'); END; -------------------------------- -- get event details -------------------------------- function get_event_details(event_id IN NUMBER) RETURN eventRecType IS eventRec eventRecType; p_event_id sporting_event.id%TYPE := event_id; BEGIN SELECT e.sport_type_name ,h.name ,a.name ,l.name ,e.start_date_time INTO eventRec.sport_name, eventRec.home_team_name, eventRec.away_team_name, eventRec.home_field, eventRec.date_time FROM sporting_event e ,sport_team h ,sport_team a ,sport_location l WHERE e.id = p_event_id AND e.home_team_id = h.id AND e.away_team_id = a.id AND e.location_id = l.id; RETURN eventRec; END; -------------------------------- -- sell random tickets proc -------------------------------- procedure sellRandomTickets IS event_tab eventTab; ticket_holder person.id%TYPE; row_ct NUMBER(8); event_idx NUMBER(8); event_id NUMBER; quantity NUMBER; BEGIN event_tab := get_open_events; row_ct := event_tab.COUNT; event_idx := TRUNC(dbms_random.value(1,row_ct)); event_id := event_tab(event_idx).id; ticket_holder := TRUNC(dbms_random.value(g_min_person_id,g_max_person_id)); quantity := dbms_random.value(1,6); sellTickets(ticket_holder,event_id,quantity); END; --------------------------------------- -- get open events function --------------------------------------- function get_open_events return eventTab IS event_tab eventTab; CURSOR open_events IS SELECT * FROM sporting_event WHERE sold_out = 0 ORDER BY start_date_time; row_num BINARY_INTEGER := 1; BEGIN FOR oe_rec IN open_events LOOP event_tab(row_num) := oe_rec; row_num := row_num +1; END LOOP; return event_tab; END; BEGIN --- initialize select min(id),max(id) INTO g_min_person_id,g_max_person_id from person; END ticketManagement; /
class TicketManagement { constructor(db) { this.db = db; this.g_max_person_id = null; this.g_min_person_id = null; } async sellTickets(person_id, event_id, quantity = 1) { const notEnoughSeats = new Error('not_enough_seats'); const p_person_id = person_id; const p_event_id = event_id; const p_quantity = quantity; let r_seat_level, r_seat_section, r_seat_row; let event_rec; try { event_rec = await this.get_event_details(p_event_id); const seatData = await this.db.collection('sportingEventTicket').aggregate([ { $match: { sportingEventId: p_event_id, ticketholderId: null } }, { $group: { _id: { seatLevel: "$seatLevel", seatSection: "$seatSection", seatRow: "$seatRow" }, count: { $sum: 1 } } }, { $match: { count: { $gte: p_quantity } } }, { $limit: 1 }, { $project: { _id: 0, seatLevel: "$_id.seatLevel", seatSection: "$_id.seatSection", seatRow: "$_id.seatRow" } } ]).toArray(); if (seatData.length === 0) throw notEnoughSeats; r_seat_level = seatData[0].seatLevel; r_seat_section = seatData[0].seatSection; r_seat_row = seatData[0].seatRow; const adjacentSeatsCursor = this.db.collection('sportingEventTicket').find({ sportingEventId: p_event_id, seatLevel: r_seat_level, seatSection: r_seat_section, seatRow: r_seat_row }).sort({ seatLevel: 1, seatSection: 1, seatRow: 1 }); for (let i = 0; i < p_quantity; i++) { const cur_ticket = await adjacentSeatsCursor.next(); if (!cur_ticket) throw notEnoughSeats; await this.db.collection('sportingEventTicket').updateOne( { _id: cur_ticket._id }, { $set: { ticketholderId: p_person_id } } ); await this.db.collection('ticketPurchaseHist').insertOne({ sportingEventTicketId: cur_ticket.id, purchasedById: p_person_id, transactionDateTime: new Date(), purchasePrice: cur_ticket.ticketPrice }); } } catch (err) { if (err === notEnoughSeats) { console.log(`Sorry, there aren't ${p_quantity} adjacent seats for event:`); console.log(` ${event_rec.home_team_name} VS ${event_rec.away_team_name} (${event_rec.sport_name})`); console.log(` ${event_rec.home_field}: ${event_rec.date_time.toLocaleString('en-GB', { day: '2-digit', month: 'short', year: 'numeric', hour: '2-digit', minute: '2-digit' })}`); } else { throw err; } } } async $transferTicket(ticket_id, new_ticketholder_id, transfer_all = false, price = null) { const p_ticket_id = ticket_id; const p_new_ticketholder_id = new_ticketholder_id; const p_price = price; const xferall = transfer_all ? 1 : 0; try { const ticket = await this.db.collection('sportingEventTicket').findOne({ id: p_ticket_id }); if (!ticket) throw new Error('Ticket not found'); const lastTransaction = await this.db.collection('ticketPurchaseHist').aggregate([ { $match: { purchased_by_id: ticket.ticketholderId, $or: [{ sporting_event_ticket_id: p_ticket_id }, { xferall: 1 }] } }, { $group: { _id: '$ticketholderId', last_txn_date: { $max: '$transaction_date_time' } } } ]).toArray(); if (lastTransaction.length === 0) throw new Error('No transaction found'); const old_ticketholder_id = ticket.ticketholderId; const last_txn_date = lastTransaction[0].last_txn_date; const transactions = await this.db.collection('ticketPurchaseHist').find({ purchased_by_id: old_ticketholder_id, transaction_date_time: last_txn_date }).toArray(); for (const xrec of transactions) { await this.db.collection('sportingEventTicket').updateOne( { id: xrec.sporting_event_ticket_id }, { $set: { ticketholderId: p_new_ticketholder_id } } ); await this.db.collection('ticketPurchaseHist').insertOne({ sporting_event_ticket_id: xrec.sporting_event_ticket_id, purchased_by_id: p_new_ticketholder_id, transferred_from_id: old_ticketholder_id, transaction_date_time: new Date(), purchase_price: p_price !== null ? p_price : xrec.purchase_price }); } } catch (error) { console.error(error); throw error; } } async generateTicketActivity(transaction_delay, max_transactions = 1000) { let txn_count = 0; while (txn_count < max_transactions) { await this.sellRandomTickets(); txn_count += 1; await new Promise(resolve => setTimeout(resolve, transaction_delay * 1000)); } } async generateTransferActivity(transaction_delay = 5, max_transactions = 100) { let txn_count = 0; let min_tik_id, max_tik_id, tik_id, new_ticketholder, xfer_all, chg_price, new_price; while (txn_count < max_transactions) { const minMaxResult = await this.db.collection('ticketPurchaseHist').aggregate([ { $group: { _id: null, min_tik_id: { $min: "$sportingEventTicketId" }, max_tik_id: { $max: "$sportingEventTicketId" } } } ]).toArray(); if (minMaxResult.length === 0) { console.log('No tickets available to transfer.'); return; } min_tik_id = minMaxResult[0].min_tik_id; max_tik_id = minMaxResult[0].max_tik_id; const tikResult = await this.db.collection('ticketPurchaseHist').aggregate([ { $match: { sportingEventTicketId: { $lte: Math.random() * (max_tik_id - min_tik_id) + min_tik_id } } }, { $group: { _id: null, tik_id: { $max: "$sportingEventTicketId" } } } ]).toArray(); if (tikResult.length === 0) { console.log('No tickets available to transfer.'); return; } tik_id = tikResult[0].tik_id; new_ticketholder = Math.floor(Math.random() * (this.g_max_person_id - this.g_min_person_id) + this.g_min_person_id); xfer_all = Math.round(Math.random() * 4) < 4; new_price = null; chg_price = Math.round(Math.random() * 2) === 0; if (chg_price) { const priceResult = await this.db.collection('sportingEventTicket').findOne({ id: tik_id }); new_price = Math.random() * (1.2 - 0.8) + 0.8 * priceResult.ticketPrice; } await this.transferTicket(tik_id, new_ticketholder, xfer_all, new_price); txn_count++; await new Promise(resolve => setTimeout(resolve, transaction_delay * 1000)); } } async $get_event_details(event_id) { const p_event_id = event_id; const result = await this.db.collection('sportingEvent').aggregate([ { $match: { id: p_event_id } }, { $lookup: { from: 'sportTeam', localField: 'homeTeamId', foreignField: 'id', as: 'home_team' } }, { $lookup: { from: 'sportTeam', localField: 'awayTeamId', foreignField: 'id', as: 'away_team' } }, { $lookup: { from: 'sportLocation', localField: 'locationId', foreignField: 'id', as: 'location' } }, { $unwind: '$home_team' }, { $unwind: '$away_team' }, { $unwind: '$location' }, { $project: { sport_name: '$sportTypeName', home_team_name: '$home_team.name', away_team_name: '$away_team.name', home_field: '$location.name', date_time: '$startDateTime' } } ]).toArray(); if (result.length === 0) { throw new Error('Event not found'); } return result[0]; } async sellRandomTickets() { const eventTab = await this.get_open_events(); const row_ct = eventTab.length; const event_idx = Math.floor(Math.random() * row_ct); const event_id = eventTab[event_idx].id; const ticket_holder = Math.floor(Math.random() * (this.g_max_person_id - this.g_min_person_id + 1)) + this.g_min_person_id; const quantity = Math.floor(Math.random() * 6) + 1; await this.sellTickets(ticket_holder, event_id, quantity); } async get_open_events() { const openEvents = await this.db.collection('sportingEvent').find({ soldOut: 0 }).sort({ startDateTime: 1 }).toArray(); return openEvents; } }
using MongoDB.Bson; using MongoDB.Driver; using System; public class TicketManagement { private readonly IMongoDatabase db; public TicketManagement(IMongoDatabase database) { db = database; Initialize(); } private decimal g_max_person_id; private decimal g_min_person_id; private void Initialize() { var personCollection = db.GetCollection<BsonDocument>("person"); var result = personCollection.Aggregate() .Group(new BsonDocument { { "_id", BsonNull.Value }, { "minId", new BsonDocument("$min", "$id") }, { "maxId", new BsonDocument("$max", "$id") } }) .FirstOrDefault(); g_min_person_id = result["minId"].AsDecimal; g_max_person_id = result["maxId"].AsDecimal; } public void sellTickets(decimal person_id, decimal event_id, int quantity = 1) { var notEnoughSeats = new Exception("Not enough seats"); var p_person_id = person_id; var p_event_id = event_id; var p_quantity = quantity; int r_seat_level; string r_seat_section; string r_seat_row; var event_rec = get_event_details(p_event_id); try { var seatQuery = db.GetCollection<BsonDocument>("sportingEventTicket") .Aggregate() .Match(new BsonDocument { { "sportingEventId", p_event_id }, { "ticketholderId", BsonNull.Value } }) .Group(new BsonDocument { { "_id", new BsonDocument { { "seatLevel", "$seatLevel" }, { "seatSection", "$seatSection" }, { "seatRow", "$seatRow" } } }, { "count", new BsonDocument("$sum", 1) } }) .Match(new BsonDocument("count", new BsonDocument("$gte", p_quantity))) .Limit(1) .Project(new BsonDocument { { "seatLevel", "$_id.seatLevel" }, { "seatSection", "$_id.seatSection" }, { "seatRow", "$_id.seatRow" } }) .FirstOrDefault(); if (seatQuery == null) { throw notEnoughSeats; } r_seat_level = seatQuery["seatLevel"].AsInt32; r_seat_section = seatQuery["seatSection"].AsString; r_seat_row = seatQuery["seatRow"].AsString; } catch { throw notEnoughSeats; } var adjacentSeatsCursor = db.GetCollection<BsonDocument>("sportingEventTicket") .Find(new BsonDocument { { "sportingEventId", p_event_id }, { "seatLevel", r_seat_level }, { "seatSection", r_seat_section }, { "seatRow", r_seat_row } }) .Sort(new BsonDocument { { "seatLevel", 1 }, { "seatSection", 1 }, { "seatRow", 1 } }) .ToCursor(); using (var session = db.Client.StartSession()) { session.StartTransaction(); try { for (int i = 0; i < p_quantity; i++) { if (!adjacentSeatsCursor.MoveNext()) { throw notEnoughSeats; } var cur_ticket = adjacentSeatsCursor.Current; var updateFilter = Builders<BsonDocument>.Filter.Eq("_id", cur_ticket["_id"]); var update = Builders<BsonDocument>.Update.Set("ticketholderId", p_person_id); db.GetCollection<BsonDocument>("sportingEventTicket").UpdateOne(updateFilter, update); var purchaseHistDoc = new BsonDocument { { "sportingEventTicketId", cur_ticket["id"] }, { "purchasedById", p_person_id }, { "transactionDateTime", DateTime.Now }, { "purchasePrice", cur_ticket["ticketPrice"] } }; db.GetCollection<BsonDocument>("ticketPurchaseHist").InsertOne(purchaseHistDoc); } session.CommitTransaction(); } catch { session.AbortTransaction(); throw; } } } public void generateTicketActivity(decimal transaction_delay, int max_transactions = 1000) { int txn_count = 0; while (txn_count < max_transactions) { sellRandomTickets(); txn_count += 1; System.Threading.Thread.Sleep((int)(transaction_delay * 1000)); } } public void generateTransferActivity(int transaction_delay = 5, int max_transactions = 100) { int txn_count = 0; decimal min_tik_id, max_tik_id, tik_id; decimal new_ticketholder; bool xfer_all; bool chg_price; decimal? new_price; while (txn_count < max_transactions) { var minMaxResult = db.GetCollection<BsonDocument>("ticketPurchaseHist") .Aggregate() .Group(new BsonDocument { { "_id", BsonNull.Value }, { "min_tik_id", new BsonDocument("$min", "$sporting_event_ticket_id") }, { "max_tik_id", new BsonDocument("$max", "$sporting_event_ticket_id") } }) .FirstOrDefault(); if (minMaxResult == null) { Console.WriteLine("No tickets available to transfer."); return; } min_tik_id = minMaxResult["min_tik_id"].AsDecimal; max_tik_id = minMaxResult["max_tik_id"].AsDecimal; var tikResult = db.GetCollection<BsonDocument>("ticketPurchaseHist") .Find(Builders<BsonDocument>.Filter.Lte("sporting_event_ticket_id", new BsonDecimal128(new Random().NextDouble() * (double)(max_tik_id - min_tik_id) + (double)min_tik_id))) .Sort(Builders<BsonDocument>.Sort.Descending("sporting_event_ticket_id")) .FirstOrDefault(); if (tikResult == null) { Console.WriteLine("No tickets available to transfer."); return; } tik_id = tikResult["sporting_event_ticket_id"].AsDecimal; new_ticketholder = (decimal)(new Random().NextDouble() * (double)(g_max_person_id - g_min_person_id) + (double)g_min_person_id); xfer_all = new Random().Next(1, 6) < 5; new_price = null; chg_price = new Random().Next(1, 4) == 1; if (chg_price) { var ticketResult = db.GetCollection<BsonDocument>("sportingEventTicket") .Find(Builders<BsonDocument>.Filter.Eq("id", tik_id)) .FirstOrDefault(); if (ticketResult != null) { new_price = ticketResult["ticket_price"].AsDecimal * (decimal)(new Random().NextDouble() * 0.4 + 0.8); } } transferTicket(tik_id, new_ticketholder, xfer_all, new_price); txn_count++; System.Threading.Thread.Sleep(transaction_delay * 1000); } } public List<BsonDocument> get_open_events() { var event_tab = new List<BsonDocument>(); var open_events = db.GetCollection<BsonDocument>("sportingEvent") .Find(Builders<BsonDocument>.Filter.Eq("soldOut", 0)) .Sort(Builders<BsonDocument>.Sort.Ascending("startDateTime")) .ToList(); foreach (var oe_rec in open_events) { event_tab.Add(oe_rec); } return event_tab; } public eventRecType get_event_details(decimal event_id) { var eventRec = new eventRecType(); var p_event_id = event_id; var pipeline = new[] { new BsonDocument("$match", new BsonDocument("id", p_event_id)), new BsonDocument("$lookup", new BsonDocument { { "from", "sportTeam" }, { "localField", "homeTeamId" }, { "foreignField", "id" }, { "as", "homeTeam" } }), new BsonDocument("$lookup", new BsonDocument { { "from", "sportTeam" }, { "localField", "awayTeamId" }, { "foreignField", "id" }, { "as", "awayTeam" } }), new BsonDocument("$lookup", new BsonDocument { { "from", "sportLocation" }, { "localField", "locationId" }, { "foreignField", "id" }, { "as", "location" } }), new BsonDocument("$unwind", "$homeTeam"), new BsonDocument("$unwind", "$awayTeam"), new BsonDocument("$unwind", "$location"), new BsonDocument("$project", new BsonDocument { { "sport_name", "$sportTypeName" }, { "home_team_name", "$homeTeam.name" }, { "away_team_name", "$awayTeam.name" }, { "home_field", "$location.name" }, { "date_time", "$startDateTime" } }) }; var result = db.GetCollection<BsonDocument>("sportingEvent").Aggregate<BsonDocument>(pipeline).FirstOrDefault(); if (result != null) { eventRec.sport_name = result["sport_name"].AsString; eventRec.home_team_name = result["home_team_name"].AsString; eventRec.away_team_name = result["away_team_name"].AsString; eventRec.home_field = result["home_field"].AsString; eventRec.date_time = result["date_time"].ToUniversalTime(); } return eventRec; } void sellRandomTickets() { var eventTab = getOpenEvents(); var rowCt = eventTab.Count; var eventIdx = (int)Math.Truncate(new Random().NextDouble() * (rowCt - 1)) + 1; var eventId = eventTab[eventIdx].id; var ticketHolder = (int)Math.Truncate(new Random().NextDouble() * (g_max_person_id - g_min_person_id) + g_min_person_id); var quantity = (int)Math.Truncate(new Random().NextDouble() * 5) + 1; sellTickets(ticketHolder, eventId, quantity); } void transferTicket(decimal ticket_id, decimal new_ticketholder_id, bool transfer_all = false, decimal? price = null) { var p_ticket_id = ticket_id; var p_new_ticketholder_id = new_ticketholder_id; var p_price = price; var xferall = transfer_all ? 1 : 0; decimal old_ticketholder_id; DateTime last_txn_date; var txfr_cur = db.GetCollection<BsonDocument>("ticketPurchaseHist") .Find(Builders<BsonDocument>.Filter.And( Builders<BsonDocument>.Filter.Eq("purchased_by_id", old_ticketholder_id), Builders<BsonDocument>.Filter.Eq("transaction_date_time", last_txn_date) )).ToList(); var result = db.GetCollection<BsonDocument>("ticketPurchaseHist").Aggregate() .Match(Builders<BsonDocument>.Filter.And( Builders<BsonDocument>.Filter.Eq("sporting_event_ticket_id", p_ticket_id), Builders<BsonDocument>.Filter.Eq("purchased_by_id", old_ticketholder_id), Builders<BsonDocument>.Filter.Or( Builders<BsonDocument>.Filter.Eq("sporting_event_ticket_id", p_ticket_id), Builders<BsonDocument>.Filter.Eq("xferall", 1) ) )) .Group(new BsonDocument { { "_id", "$ticketholder_id" }, { "transaction_date_time", new BsonDocument("$max", "$transaction_date_time") } }) .FirstOrDefault(); if (result != null) { last_txn_date = result["transaction_date_time"].ToUniversalTime(); old_ticketholder_id = result["_id"].AsDecimal; foreach (var xrec in txfr_cur) { db.GetCollection<BsonDocument>("sportingEventTicket").UpdateOne( Builders<BsonDocument>.Filter.Eq("id", xrec["sporting_event_ticket_id"]), Builders<BsonDocument>.Update.Set("ticketholder_id", p_new_ticketholder_id) ); db.GetCollection<BsonDocument>("ticketPurchaseHist").InsertOne(new BsonDocument { { "sporting_event_ticket_id", xrec["sporting_event_ticket_id"] }, { "purchased_by_id", p_new_ticketholder_id }, { "transferred_from_id", old_ticketholder_id }, { "transaction_date_time", DateTime.UtcNow }, { "purchase_price", p_price ?? xrec["purchase_price"].AsDecimal } }); } } else { throw new Exception("No tickets available to transfer."); } } }
public class TicketManagement { private MongoDatabase db; private Decimal128 g_max_person_id; private Decimal128 g_min_person_id; public TicketManagement(MongoDatabase db) { this.db = db; initialize(); } private void initialize() { MongoCollection<Document> personCollection = db.getCollection("person"); Document minMaxPersonId = personCollection.aggregate(Arrays.asList( Aggregates.group(null, Accumulators.min("minId", "$id"), Accumulators.max("maxId", "$id")) )).first(); if (minMaxPersonId != null) { g_min_person_id = minMaxPersonId.get("minId", Decimal128.class); g_max_person_id = minMaxPersonId.get("maxId", Decimal128.class); } } public void sellTickets(Number person_id, Number event_id, Number quantity) { try { Document eventRec = get_event_details(event_id); Document seatInfo = db.getCollection("sportingEventTicket") .aggregate(Arrays.asList( Aggregates.match(Filters.and( Filters.eq("sportingEventId", event_id), Filters.eq("ticketholderId", null) )), Aggregates.group(new Document("seatLevel", "$seatLevel") .append("seatSection", "$seatSection") .append("seatRow", "$seatRow"), Accumulators.sum("count", 1) ), Aggregates.match(Filters.gte("count", quantity)), Aggregates.limit(1) )).first(); if (seatInfo == null) { throw new Exception("not_enough_seats"); } Number r_seat_level = seatInfo.getInteger("_id.seatLevel"); String r_seat_section = seatInfo.getString("_id.seatSection"); String r_seat_row = seatInfo.getString("_id.seatRow"); MongoCursor<Document> cursor = db.getCollection("sportingEventTicket") .find(Filters.and( Filters.eq("sportingEventId", event_id), Filters.eq("seatLevel", r_seat_level), Filters.eq("seatSection", r_seat_section), Filters.eq("seatRow", r_seat_row) )) .sort(Sorts.ascending("seatLevel", "seatSection", "seatRow")) .iterator(); for (int i = 0; i < quantity; i++) { if (!cursor.hasNext()) { throw new Exception("not_enough_seats"); } Document cur_ticket = cursor.next(); db.getCollection("sportingEventTicket").updateOne( Filters.eq("_id", cur_ticket.getObjectId("_id")), Updates.set("ticketholderId", person_id) ); db.getCollection("ticketPurchaseHist").insertOne(new Document() .append("sportingEventTicketId", cur_ticket.getDecimal("id")) .append("purchasedById", person_id) .append("transactionDateTime", new Date()) .append("purchasePrice", cur_ticket.getDecimal("ticketPrice")) ); } } catch (Exception e) { if (e.getMessage().equals("not_enough_seats")) { System.out.println("Sorry, there aren't " + quantity + " adjacent seats for event:"); System.out.println(" " + eventRec.getString("home_team_name") + " VS " + eventRec.getString("away_team_name") + " (" + eventRec.getString("sport_name") + ")"); System.out.println(" " + eventRec.getString("home_field") + ": " + new SimpleDateFormat("dd-MMM-yyyy HH:mm").format(eventRec.getDate("date_time"))); } else { e.printStackTrace(); } } } public void generateTicketActivity(int transaction_delay, int max_transactions) { int txn_count = 0; while (txn_count < max_transactions) { sellRandomTickets(); txn_count++; try { Thread.sleep(transaction_delay * 1000); } catch (InterruptedException e) { Thread.currentThread().interrupt(); } } } public void generateTransferActivity(double transaction_delay, int max_transactions) { int txn_count = 0; Decimal128 min_tik_id; Decimal128 max_tik_id; Decimal128 tik_id; Decimal128 new_ticketholder; boolean xfer_all; boolean chg_price; Decimal128 new_price; while (txn_count < max_transactions) { Document minMaxResult = db.getCollection("ticketPurchaseHist") .aggregate(Arrays.asList( Aggregates.group(null, Accumulators.min("min_tik_id", "$sportingEventTicketId"), Accumulators.max("max_tik_id", "$sportingEventTicketId") ) )).first(); if (minMaxResult == null) { System.out.println("No tickets available to transfer."); return; } min_tik_id = minMaxResult.get("min_tik_id", Decimal128.class); max_tik_id = minMaxResult.get("max_tik_id", Decimal128.class); Document tikResult = db.getCollection("ticketPurchaseHist") .aggregate(Arrays.asList( Aggregates.match(Filters.lte("sportingEventTicketId", new Decimal128(Math.random() * (max_tik_id.doubleValue() - min_tik_id.doubleValue()) + min_tik_id.doubleValue()))), Aggregates.group(null, Accumulators.max("tik_id", "$sportingEventTicketId")) )).first(); if (tikResult == null) { System.out.println("No tickets available to transfer."); return; } tik_id = tikResult.get("tik_id", Decimal128.class); new_ticketholder = new Decimal128(Math.floor(Math.random() * (g_max_person_id.doubleValue() - g_min_person_id.doubleValue()) + g_min_person_id.doubleValue())); xfer_all = (Math.round(Math.random() * 4) < 4); // transfer all tickets 80% of the time new_price = null; chg_price = (Math.round(Math.random() * 2) == 0); // 30% of the time change price if (chg_price) { Document priceResult = db.getCollection("sportingEventTicket") .find(Filters.eq("id", tik_id)) .projection(Projections.include("ticketPrice")) .first(); if (priceResult != null) { new_price = new Decimal128(priceResult.getDouble("ticketPrice") * (Math.random() * 0.4 + 0.8)); } } transferTicket(tik_id, new_ticketholder, xfer_all, new_price); txn_count++; try { Thread.sleep((long) (transaction_delay * 1000)); } catch (InterruptedException e) { Thread.currentThread().interrupt(); } } } public List<Document> getOpenEvents() { List<Document> eventTab = new ArrayList<>(); MongoCollection<Document> sportingEventCollection = db.getCollection("sportingEvent"); FindIterable<Document> openEvents = sportingEventCollection.find(Filters.eq("soldOut", 0)) .sort(Sorts.ascending("startDateTime")); for (Document oeRec : openEvents) { eventTab.add(oeRec); } return eventTab; } public eventRecType get_event_details(Number event_id) { eventRecType eventRec = new eventRecType(); Number p_event_id = event_id; List<Document> pipeline = Arrays.asList( new Document("$match", new Document("id", p_event_id)), new Document("$lookup", new Document("from", "sportTeam") .append("localField", "homeTeamId") .append("foreignField", "id") .append("as", "home_team")), new Document("$lookup", new Document("from", "sportTeam") .append("localField", "awayTeamId") .append("foreignField", "id") .append("as", "away_team")), new Document("$lookup", new Document("from", "sportLocation") .append("localField", "locationId") .append("foreignField", "id") .append("as", "location")), new Document("$unwind", "$home_team"), new Document("$unwind", "$away_team"), new Document("$unwind", "$location"), new Document("$project", new Document("sport_name", "$sportTypeName") .append("home_team_name", "$home_team.name") .append("away_team_name", "$away_team.name") .append("home_field", "$location.name") .append("date_time", "$startDateTime")) ); MongoCollection<Document> collection = db.getCollection("sportingEvent"); AggregateIterable<Document> result = collection.aggregate(pipeline); Document doc = result.first(); if (doc != null) { eventRec.sport_name = doc.getString("sport_name"); eventRec.home_team_name = doc.getString("home_team_name"); eventRec.away_team_name = doc.getString("away_team_name"); eventRec.home_field = doc.getString("home_field"); eventRec.date_time = doc.getDate("date_time"); } return eventRec; } public void sellRandomTickets() { List<Document> eventTab = getOpenEvents(); int rowCt = eventTab.size(); int eventIdx = (int) Math.floor(Math.random() * rowCt); Document event = eventTab.get(eventIdx); Decimal128 eventId = event.get("id", Decimal128.class); Decimal128 ticketHolder = new Decimal128(Math.floor(Math.random() * (g_max_person_id.doubleValue() - g_min_person_id.doubleValue()) + g_min_person_id.doubleValue())); int quantity = (int) Math.floor(Math.random() * 5) + 1; sellTickets(ticketHolder, eventId, quantity); } public void transferTicket(int ticket_id, int new_ticketholder_id, boolean transfer_all, Double price) { int p_ticket_id = ticket_id; int p_new_ticketholder_id = new_ticketholder_id; Double p_price = price; int xferall = transfer_all ? 1 : 0; int old_ticketholder_id; Date last_txn_date; List<Document> txfr_cur; try { Document result = db.getCollection("ticketPurchaseHist").aggregate(Arrays.asList( Aggregates.match(Filters.eq("sportingEventTicketId", p_ticket_id)), Aggregates.group("$ticketholderId", Accumulators.max("transaction_date_time", "$transactionDateTime")), Aggregates.lookup("sportingEventTicket", "ticketholderId", "ticketholderId", "ticket"), Aggregates.unwind("$ticket"), Aggregates.match(Filters.or( Filters.eq("ticket.id", p_ticket_id), Filters.eq("xferall", 1) )), Aggregates.project(Projections.fields( Projections.computed("transaction_date_time", "$transaction_date_time"), Projections.computed("ticketholder_id", "$ticket.ticketholderId") )) )).first(); if (result != null) { last_txn_date = result.getDate("transaction_date_time"); old_ticketholder_id = result.getInteger("ticketholder_id"); txfr_cur = db.getCollection("ticketPurchaseHist").find(Filters.and( Filters.eq("purchased_by_id", old_ticketholder_id), Filters.eq("transaction_date_time", last_txn_date) )).into(new ArrayList<>()); for (Document xrec : txfr_cur) { db.getCollection("sportingEventTicket").updateOne( Filters.eq("id", xrec.getInteger("sportingEventTicketId")), Updates.set("ticketholder_id", p_new_ticketholder_id) ); db.getCollection("ticketPurchaseHist").insertOne(new Document() .append("sportingEventTicketId", xrec.getInteger("sportingEventTicketId")) .append("purchased_by_id", p_new_ticketholder_id) .append("transferred_from_id", old_ticketholder_id) .append("transaction_date_time", new Date()) .append("purchase_price", p_price != null ? p_price : xrec.getDouble("purchase_price")) ); } } db.getCollection("sportingEventTicket").getDatabase().runCommand(new Document("commitTransaction", 1)); } catch (Exception e) { db.getCollection("sportingEventTicket").getDatabase().runCommand(new Document("abortTransaction", 1)); } } }