Full support of PostgreSQL engine comes to Logica

Logica is a logic programming language designed for intuitive and efficient data manipulation, which we open sourced in 2020. It compiles to SQL, providing access to the power of SQL engines with the convenience of a logic programming syntax.

When it was open sourced, Logica's only fully supported engine was BigQuery, a powerful data warehouse, executing queries with high parallelization and processing terabytes of data within seconds.

Modern machines can store and process significant amounts of data, even within a single computer. Thus relational SQL databases are as popular as ever. They contain a lot of data and its analysis is important. Among open source database options, PostgreSQL and SQLite are some of the most popular database engines (example1, example2). Logica added support for SQLite in 2021.

Now we are pleased to announce a new release of Logica that adds support for PostgreSQL.

As Logica compiles to SQL, it is natural to extend the language to use PostgreSQL as the engine. However, there are nuances in the SQL dialect of Postgres which require addressing. The biggest distinction is that PostgreSQL requires types of records to be explicitly spelled out in your query, while BigQuery determines the types automatically.

For example, consider a Logica predicate where for each user we collect a list of records with information about their purchases.

UserPurchases(     user_id:,     user_name:,     purchases? List= {item_name:, item_price:}) distinct :-   Purchase(purchase_id:, user_id:, item_name:, item_price:),   UserInfo(user_id:, user_name:);

We can translate this Logica predicate to GoogleSQL to run on BigQuery as follows:

SELECT   user_id,   user_name,   ARRAY_AGG(STRUCT(item_name as item_name, item_price as item_price)) as purchases FROM   Purchases INNER JOIN   UserInfo USING (user_id) GROUP BY 1, 2;

Logica's record {item_name:, item_price:} simply compiles into GoogleSQL's STRUCT(item_name as item_name, item_price as item_price).

However, in the dialect of PostgreSQL composite types must be explicitly defined and specified. In our example, we need to define the type PurchaseRecord with fields item_name and item_price. We should also specify in the query that the purchases column is aggregating records of type PurchaseRecord. Thus PostgreSQL query for our predicate would be written like so.

CREATE TYPE PurchaseRecord as (item_name text, item_price numeric); SELECT   user_id,   user_name,   ARRAY_AGG(ROW(item_name,                 item_price)::PurchaseRecord) AS purchases FROM   Purchase INNER JOIN   UserInfo USING (user_id) GROUP BY UserInfo.user_id, UserInfo.user_name;

Records and lists are also useful as intermediates in calculations, even if the input and output data are normalized. For example, we have a table called ItemSales and want to find a list of most sold items in each of the stores that the table describes. Specifically, we want to assemble a table with information about the top three most sold items among all of the stores. For each of the items, we may want to list the department of the store where the item is being sold. This can be achieved intuitively using the ArgMax3 aggregate function, which accumulates all the information about the items that we need, and no extra join is needed.

# Collecting information of top 3 most sold items for each store. StoreTopItemsCollection(store) ArgMax3= {item:,                                          department:} -> sales_volume :-   ItemSales(store:, item:, department:, sales_volume:); # Flattening top items collection. StoreTopItems(store:, item:, department:) :-   {item:, department:} in StoreTopItemsCollection(store);

(PostgreSQL colab link, online SQLite playground link)

To support the PostgreSQL engine, we extended the Logica compiler with type inference. Logica now infers data types for all expressions that a user employs. For records and arrays, Logica specifies their type in the produced SQL, just as PostgreSQL requires. Commands to create necessary types are produced as part of the compiled SQL. In this collab, we show an example of a program that writes a PostgreSQL table, and in this collab, we show how to give type hints when the program does not have enough information for complete inferences.

As a byproduct of type inference, we were able to improve error messages. Now that we know the types, we can point to the user where a mistake is made within the Logica program, rather than the user having to debug the generated SQL statement.

PostgreSQL is a popular and powerful engine. It is easy to start your own instance (maybe just in CoLab!), or use a serverless option. We are excited to provide users of Logica with the option to run on Postgres. If you already use PostgreSQL, we encourage you to give Logica a try, it is a joy to write data analysis with logic programming! If you have any feedback or questions, please share at the discussion section of Logica repository.

By Evgeny Skvortsov, Software Engineer – Google

Showing Our Work: A Study In Understanding Open Source Contributors

In 2022, the research team within Google’s Open Source Programs Office launched an in-depth study to better understand open source developers, contributors, and maintainers. Since Alphabet is a large consumer of and contributor to open source, our primary goals were to investigate the evolving needs and motivations of open source contributors, and to learn how we can best support the communities we depend on. We also wanted to share our findings with the community in order to further research efforts and our collective understanding of open source work.

Key findings from this work suggest that community leaders should:

• Value your time together and apart: Lack of time was cited as the leading reason ‘not to contribute’ as well as motivation to ‘leave a community’. This should encourage community leaders to adopt practices that ensure that they are making the most of the time they have together. One example: some projects have planned breaks, no-meeting weeks, or official slowdowns during holidays or popular conference weeks.

• Invest in documentation: Contributors and maintainers expressed that task variety, delegation, and onboarding new maintainers could help to reduce burnout in open source. Documentation is one way to make individual knowledge accessible to the community. In addition to technical and procedural overviews, documentation can also be used to clarify roles, tasks, expectations, and a path to leadership.

• Always communicate with care: Contributors prefer projects that have welcoming communities, clear onboarding paths, and a code of conduct. Communication is the primary way for community leaders to promote welcoming and inclusive communities and set norms around language and behavior (as documented in a Code of Conduct). Communication is also how we build relationships, trust, and respect for each other.
  
  
• Create spaces for anonymous feedback: Variable answers between demographic subsets in our research suggest that while systematic approaches can be taken to reduce burnout, there is no one-size-fits-all approach. Feedback is a valuable tool for any project to adjust to the evolving needs of their contributor and user communities. When designed appropriately, surveys can serve as safe, anonymous, retaliation-free spaces for individuals to provide honest feedback.

How do contributors select projects?

Base: 517 international OSS developers, contributors, maintainers and students who worked on open source in their personal time

Within Google’s Open Source Programs office, we are constantly looking for ways to improve support for contributors inside and outside of Google. Studies such as this one provide guidance to our programs and investments in the community. This work helps us to see we should continue to:

• Invest in documentation competency: Google Season of Docs provides support for open source projects to improve their documentation and gives professional technical writers an opportunity to gain experience in open source.

• Document roles and promote tactics that recognize work within communities: The ACROSS project continues to work with projects and communities to establish consistent language to define roles, responsibilities, and work done within open source projects.

• Exercise and discuss ‘better’ practices within the community: While we continually seek to improve our engagement practices within communities, we will also continue to share these experiences with the broader community in hopes that we can all learn from our successes and challenges. For example, we’ve published documentation around our release process, including resources for the creation and management of a code of conduct.

This research, along with other articles authored by the OSPO research team is now available on our site.

By Sophia Vargas – Researcher, Google Open Source Programs Office

GSoC 2023: project results and feedback part 1

In 2023, Google Summer of Code brought 966 new contributors into open source software development to work with open source organizations on a 12+ week project. We had 168 participating open source organizations with mentors and contributors from over 75 countries this year.

For 19 years, Google Summer of Code has thrived due to the enthusiasm of our open source communities and the 19k+ volunteer mentors that spend from 50-150 hours mentoring each of our 20k contributors since 2005! This year, there are 168 mentoring organizations and over 1,950 mentors participating in the 2023 program. A sincere thank you to our mentors and organization administrators for guiding and supporting our contributors this year. We are also looking forward to hosting many of the 2023 GSoC Mentors on campus this fall for the annual Mentor Summit.

September 4th concluded the standard 12-week project timeline and we are pleased to announce that 628 contributors have successfully completed this year’s program as of today, September 5th, 2023. Congratulations to all the contributors and mentors that have wrapped up their summer coding projects!

2023 has shown us that GSoC continues to grow in popularity with students and developers 19 years after the program began. GSoC had a record high 5,679 contributor applicants from 106 countries submit their project proposals this year. We also had huge interest in the program with over 43,765 registrants from 160 countries applying to the program during the two week application period.

The final step of every GSoC program is to hear back from mentors and contributors on their experiences through evaluations. This helps GSoC Admins continuously improve the program and gives us a chance to see the impact the program has on so many individuals! Some notable results and comments from the standard 12-week project length evaluations are below:

• 95.63% of contributors think that GSoC helped their programming skills
• 99.06% of contributors would recommend their GSoC mentors
• 97.81% of contributors will continue working with their GSoC organization
• 99.84% of contributors plan to continue working on open source
• 82.81% of contributors said they would consider being a mentor
• 96.25% of contributors said they would apply to GSoC again

Here’s some of what our GSoC 2023 Contributors had to say about the program!

At the suggestion of last year’s contributors, we added multiple live talks throughout the coding period to bring contributors together, providing tips to help them make the most of their GSoC experience. Each of these talks were attended on average by 42% of the 2023 GSoC contributors.

Another request from our previous contributors was to hear more about the cool projects their colleagues did over the summer and the opportunity to talk about their own projects with others. Over the coming weeks we are hosting three lightning talk sessions where over 40 of the 2023 contributors will have the opportunity to present their project learnings to the other contributors and their mentors.

We’ll be back in a couple of months to give a final update on the GSoC projects that will conclude later this year. Almost 30% of contributors (286 contributors) are still completing their projects, so please stay tuned for their results in part two of this blog post later this year!

By Perry Burnham – Google Open Source

ChromeOS EC testing suite in Renode for consumer products

Besides main application cores that are directly exposed to the users, many industrial and consumer devices include embedded controllers, which, although fairly invisible to the user, perform critical system tasks such as power management, receiving and processing user input, or signals from sensors like thermal. Given their role in the system, those MCUs need to be rigorously tested in CI. This is why the ChromeOS team has collaborated with Antmicro to simulate the ChromeOS FPMCU (Fingerprint Firmware) module, based on the ChromeOS EC (Embedded Controller) firmware in Antmicro’s Renode open source simulation framework.

This enables automated testing of embedded controllers in CI at scale, in a deterministic manner, and with complete observability. It also streamlines the developer feedback loop for faster development of microcontroller firmware that ChromeOS uses to drive peripherals, such as fingerprint readers or touchpads. To make this possible, we have improved the simulation capabilities for two of the microcontrollers used in FPMCU modules, popular in consumer electronics like Chromebooks and wearables, but also in many industrial applications: STM32F412 and STM32H743.

Testing consumer-grade products with Renode

Continuous testing for embedded systems

The project required implementing continuous testing of the FPMCU module against tens of binaries that test the controller in the most common operations and scenarios to ensure maximum reliability at all times. A traditional approach would require reflashing the physical microcontroller memory with each binary, which is time-consuming and error-prone. To scratch that itch we developed the CrOS EC Tester, which runs all EC tests in a Renode simulation and uses GitHub Actions to handle building and executing test binaries for a truly automated workflow—useful both in CI and in an interactive development environment.

Renode has broad support for architectures such as (but not limited to) RISC-V, ARM Cortex-M, (recently added) Cortex-R or Cortex-A, and runs binary-compatible software. Thus, it is not limited to testing embedded controllers but entire multi-CPU systems. You can easily add Renode to an existing workflow without any major changes for testing in real-life scenarios. By moving all testing efforts into an interactive and deterministic environment of Renode, you can implement a fully CI-driven testing approach in your projects and benefit from advanced debugging, tracing, and prototyping capabilities.

Comprehensive simulation of STM32 microcontrollers

The Renode models of the STM32F412 and STM32H743 microcontrollers give you access to a broad range of peripherals, allowing you to run various scenarios you’d typically test on hardware. As a result of our collaboration with Google, we have added or improved models of ST peripherals like UART, EXTI, GPIO, DMA, ADC, SPI, flash controllers, timers, watchdogs, and more.

The need for in-depth testing has led to the introduction of many enhancements to ARM Cortex-M support in general, such as the MPU (Memory Protection Unit), which allows you to protect certain memory areas from unauthorized modification or access or FPU interrupts. These features can now be used by other Cortex-M-based projects to further extend their test coverage with Renode.

Renode for rapid, interactive prototyping

One of the tests from our test suite used the microcontroller's MPU module to test address space security. When you run the test-rollback test case, you can see that the MPU simulated in Renode successfully protected the OS from unauthorized memory access:

Testing consumer-grade products with Renode

Another Renode feature that allowed us to increase our test coverage of the EC ecosystem is support for dummy SPI and I2C devices. While Renode supports a recently added advanced framework for time-controlled feeding of sensor data, many scenarios require much simpler interaction with the external device. For this purpose, we developed a dummy SPI device that simply returns pre-programmed data to the controller, which allowed us to pass initialization tests for a sensor without modeling the sensor itself. From the functional point of view of the simulation, the dummy sensor data is identical to the real data, which is useful when the specific component is difficult to model or lacks documentation.

Build a CI-driven test workflow with Renode

Renode is a powerful tool for automating and simplifying the test workflow in the project at any stage of development, even pre-silicon. It helps you reduce the tedium typically associated with embedded software testing by providing a fully controllable environment that can lead to fewer bugs and vulnerabilities, which is naturally important for mass-market products such as Chromebooks.

By Michael Gielda – Antmicro

A vision for more efficient media management

Petit Press’ new open source, cloud-based DAM platform helps publishers get rich media content in front of their audience at pace and scale.

Picture the scene: You’re an investigative journalist that has just wrapped up a new piece of video content that offers incisive, timely commentary on a pressing issue of the day. Your editor wants to get the content in front of your audience as quickly as possible and you soon find yourself bogged down in a laborious, manual process of archiving and uploading files. A process that is subject to human error, and involves repeating the same tasks as you prepare the content for YouTube and embedding within an article.

With the development of a new open source digital asset management (DAM) system, Slovak publishing house, Petit Press, is hoping to help the wider publishing ecosystem overcome these types of challenges.

Striving towards a universal, open source solution

Like many publishers in today’s fast-paced, fast-changing news landscape, Petit Press was feeling the pressure to be more efficient and do more with less, while at the same time maximizing the amount of high-quality, rich media content its journalists could deliver. “We wanted to find a solution to two main asset delivery issues in particular,” says Ondrej Podstupka, deputy editor in chief of SME.sk. “Firstly, to reduce the volume of work involved in transferring files from our journalists to our admin teams to the various platforms and CMS we use. Secondly, to avoid the risk of misplacing archived files or losing them entirely in an archive built on legacy technologies.”

As a publisher of over 35 print and digital titles, including one of Slovakia’s most-visited news portal, SME.sk, Petit Press also had a first-hand understanding of how useful the solution might be if it could flex to the different publishing scales, schedules, and platforms found across the news industry. With encouragement and support from GNI, Petit Press challenged themselves to build an entirely open source, API-based DAM system that flexes beyond their own use cases and can be easily integrated with any CMS, which means that other publishers can adapt and add functionality with minimal development costs.

Getting out of the comfort zone to overcome complexity

For the publisher, creating an open source project requires collaboration, skill development, and a strong sense of purpose. GNI inspired our team members to work together in a positive, creative, and supportive environment. Crucial resources from GNI also enabled the team to broaden the scope of the project beyond Petit Press’ direct requirements to cover the edge use cases and automations that a truly open source piece of software requires.

“GNI has enabled our organization to make our code open source, helping to create a more collaborative and innovative environment in the media industry.” 

– Ondrej Podstupka, deputy editor in chief of SME.sk

Building and developing the tool was difficult at times with a team of software engineers, product managers, newsroom managers, UX designers, testers, and cloud engineers all coming together to see the project to completion. For a team not used to working on GitHub, the open source aspect of the project proved the primary challenge. The team, however, also worked to overcome everything from understanding the complexities of integrating a podcast feature, to creating an interface all users felt comfortable with, to ensuring compliance with YouTube’s security requirements.

Unburdening the newsroom and minimizing costs

The hard work paid off though, when the system initially launched in early 2023. Serving as a unified distribution platform, asset delivery service and long term archive, the single solution is already unburdening the newsroom. It also benefits the tech/admin teams, by addressing concerns about the long-term costs of various media storage services.

On Petit Press’ own platforms, the DAM system has already been successfully integrated into SME.sk’s user-generated content (UGC) blog. This integration allows for seamless content management and curation, enhancing the overall user experience. The system also makes regulatory compliance easier, thanks to its GDPR-compliant user deletion process.

In addition to the UGC Blog system, the DAM system has now launched for internal Petit Press users—specifically for managing video and podcast content, which has led to increased efficiency and organization within the team. By streamlining the video and podcast creation and distribution processes, Petit Press has already seen a 5-10% productivity boost. The new DAM system saves an estimated 15-20 minutes of admin time off every piece of video/podcast content Petit Press produces.

Working towards bigger-picture benefits

Zooming out, the DAM system is also playing a central part in Petit Press’ year-long, org-wide migration to the cloud. This transformation was set in motion to enhance infrastructure, streamline processes, and improve overall efficiency within the department.

Podstupka also illustrates how the system might benefit other publishers. “It could be used as an effective standalone, automated archive for videos and podcasts,” he says. For larger publishing houses, “if you use [the DAM system] to distribute videos to YouTube and archive podcasts, there is minimal traffic cost and very low storage cost. But you still have full control over the content in case you decide to switch to a new distribution platform or video hosting service.”

As the team at Petit Press continues to get to grips with the new system, there is a clear goal in mind: To have virtually zero administrative overhead related to audio or video.

Beyond the automation-powered efficiency savings, the team at Petit Press are also exploring the new monetisation opportunities that the DAM system presents. They are currently working on a way to automatically redistribute audio and image assets to their video hosting platform, to automatically create video from every podcast they produce. This video is then pushed to their CMS and optimized for monetisation on the site with very little additional development required.

Ultimately, though, the open source nature of the system makes the whole team excited to see where other publishers and developers might take the product. “It’s a futureproof way to leverage media content with new services, platforms and ideas that emerge in technology or media landscapes,” says Igor, Head Of Development & Infrastructure. A succinct, but undeniably compelling way of summing up the system’s wide-ranging potential.

A guest post by the Petit Press team